如何使用c#在特定单元格的excel文件中写入数据

本文关键字:文件 excel 数据 单元格 何使用 | 更新日期: 2023-09-27 18:19:29

下面是我正在使用的代码。我正在调用Driver方法,并在执行代码成功后传递相应的参数行列等。代码运行成功,但excel文件上没有写入任何内容,是否可以直接发送单元格编号,如b5等。如果可以,我应该做什么修改:

namespace PEPI_Performance.Utility
{
/// <summary>
/// Description of ExcelWriter.
/// </summary>
public class ExcelWriter
{
    /// <summary>
    /// Constructs a new instance.
    /// </summary>
    public ExcelWriter()
    {
        // Do not delete - a parameterless constructor is required!
    }
    Microsoft.Office.Interop.Excel.Application exlApp;
    Microsoft.Office.Interop.Excel.Workbook exlWB;
    Microsoft.Office.Interop.Excel.Worksheet exlWS;
    object misvalue = System.Reflection.Missing.Value;

    public void Driver(int row , int col, string time, int runNumber){
        //GlobalLib.clsExcel objExcel = new GlobalLib.clsExcel();
        //  Report.Info(ApplicationName);
        string sDataFile = "Ranorex_Reports.xls";
        //ClsExcel objExcel = new ClsExcel();
        //ArrayList colTF = null;
        //  int rowCount = 0;
        string sFilePath = Path.GetFullPath(sDataFile);
        Report.Success(sFilePath);
        string sOldvalue = "Vopak_Automation''bin''Debug''" + sDataFile;
         sFilePath=sFilePath.Replace(sOldvalue,"")+
        "PEPI_Performance''ExecutionReport''"+ sDataFile;
        fnOpenExcel(sFilePath,runNumber);
        //colTF = objExcel.fnGetTestFlow();
        writeExcel(row,col,time);
        fnCloseExcel();
    }

    //Open Excel file
    public int fnOpenExcel(string sPath
    [System.Runtime.InteropServices.OptionalAttribute,
    System.Runtime.InteropServices.DefaultParameterValueAttribute(0)]  // ERROR:
    Optional parameters aren't supported in C#
                           int iSheet){
        int functionReturnValue = 0;
        try {
            exlApp = new Microsoft.Office.Interop.Excel.Application();
            exlApp.Visible = true ;
            //exlWB = exlApp.Workbooks.Open(sPath);
            exlWB =
 exlApp.Workbooks.Open(sPath,Type.Missing,Type.Missing,Type.Missing,
 Type.Missing,Type.Missing,Type.Missing,Type.Missing,
 Type.Missing,Type.Missing,Type.Missing,Type.Missing,
 Type.Missing,Type.Missing,Type.Missing);
            exlWS =(Microsoft.Office.Interop.Excel.Worksheet)exlWB.Worksheets[iSheet];
            //((Microsoft.Office.Interop.Excel._Worksheet)exlWS).Activate();
            //((Microsoft.Office.Interop.Excel.Worksheet)exlWS).Cells() = 2;


            functionReturnValue = 0;
            Report.Info(functionReturnValue.ToString());
        }
        catch (Exception ex) {
            functionReturnValue = -1;
            Report.Error(ex.Message);
        }
        return functionReturnValue;
    }

    // Close the excel file and release objects.
    public int fnCloseExcel(){
        //exlWB.Close();
        try{
            exlApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exlWS);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exlWB);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exlApp);
            GC.GetTotalMemory(false);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.GetTotalMemory(true);
        }catch(Exception ex){
            Report.Error(ex.Message);
        }
        Report.Info("closed");
        return 0;
    }
    public void writeExcel(int i, int j , string time){
        Excel.Range exlRange = null;
        exlRange = (Excel.Range)exlWS.UsedRange;
        //exlRange.Select();
        Report.Info("hi");
        //(Excel.Range)exlRange.Cells[i, j]).Value2 != null;
        //(Excel.Range)exlRange.Cells[i , j].
        ((Excel.Range)exlRange.Cells["B5",Type.Missing]).Formula = time;
            //((Excel.Range)exlRange.Cells[i, j]).Value2 = time;
    }
   }
   }

如何使用c#在特定单元格的excel文件中写入数据

我知道两种引用单元格的方法

可以使用带整数的行和单元格,也可以使用单元格引用

下面是的例子

通过CELL参考

public void writeExcel(int i, int j , string time){
        Excel.Range exlRange = null;
        exlRange = (Excel.Range)exlWS.UsedRange;
        //exlRange.Select();
        Report.Info("hi");
       // ((Excel.Range)exlRange.Cells["B5",Type.Missing]).Formula = time;
       //((Excel.Range)exlRange.Cells[i, j]).Value2 = time;
        exlWS.Range["B5"].Formula = time;

    }

按行和单元格作为整数

public void writeExcel(int i, int j , string time){
        Excel.Range exlRange = null;
        exlRange = (Excel.Range)exlWS.UsedRange;
        //exlRange.Select();
        Report.Info("hi");
       // ((Excel.Range)exlRange.Cells["B5",Type.Missing]).Formula = time;
       //((Excel.Range)exlRange.Cells[i, j]).Value2 = time;
       ((Microsoft.Office.Interop.Excel.Range)exlRange.Cells[2, 2]).Formula = time;

    }