如何使用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;
}
}
}
我知道两种引用单元格的方法
可以使用带整数的行和单元格,也可以使用单元格引用
下面是的例子
通过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;
}