首先,需要导入支持Excel的com组件或安装Nuget程序包,如Microsoft.Office.Interop.Excel
//1.创建Application对象
Excel.Application xApp = new Excel.Application();
string filePath = AppDomain.CurrentDomain.BaseDirectory + "Sample.xlsx";
xApp.Visible = false; //表示是否打开再写入,即出现Excel弹出的情况
//2.得到WorkBook对象,可以用两种方式之一:下面的是打开已有的文件
string filePath = AppDomain.CurrentDomain.BaseDirectory + "Sample.xlsx";
Excel.Workbook xBook = xApp.Workbooks._Open(filePath,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//Excel.Workbook xBook = xApp.Workbooks.Add(Missing.Value); //新建文件的代码,该方法打开的文件处于锁定状态,只能进行只读操作
//3.指定要操作的sheet,两种方式
Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];
//Excel.Worksheet xSheet = xApp.ActiveSheet;
//4.读取数据,通过Range对象
//Excel.Range rng1 = xSheet.get_Range("A1",Type.Missing);
int iRowCount = xSheet.get_Range("A65535",Type.Missing).get_End(Excel.XlDirection.xlUp).Row;
//获取当前已存在的行数
/*//读取,通过Range对象,但使用不同的接口得到Range
Excel.Rangerng2 = (Excel.Range)xSheet.Cells[3, 1];
Console.WriteLine(rng2.Value2);*/
int[] arr = { 1, 2, 3, 4 };
//5.写入数据
for (int i = iRowCount; i <= iRowCount+5; i++)
{
Excel.Range rng2 = xSheet.get_Range("A" + i, Missing.Value);
rng2.Value2 = arr[0];
//rng2.Interior.ColorIndex = 2; //设置Range的背景色
Excel.Range rng3 = xSheet.get_Range("B" + i, Missing.Value);
rng3.Value2 = arr[1];
//rng3.Interior.ColorIndex = 3; //设置Range的背景色
Excel.Range rng4 = xSheet.get_Range("C" + i, Missing.Value);
rng4.Value2 = arr[2];
//rng4.Interior.ColorIndex = 4; //设置Range的背景色
Excel.Range rng5 = xSheet.get_Range("D" + i, Missing.Value);
rng5.Value2 = arr[3];
//rng5.Interior.ColorIndex = 6; //设置Range的背景色
Excel.Range rng6 = xSheet.get_Range("E" + i, Missing.Value);
rng6.Value2 = DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
}
//6.保存excel,三种方式
//方式一:保存WorkBook,以已存在的Excel为模板,然后复制在下面的Excel中
/*xBook.SaveAs(filePath(可自定义路径),
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);*/
//方式二:保存WorkSheet ,以已存在的Excel为模板,然后复制在下面的Excel中
/*xSheet.SaveAs(filePath(可自定义路径),
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);*/
//方式三
xBook.Save(); //保存当前已存在的文件
//7.Excel对象退出内存
xSheet = null; //可有可无
xBook = null; //可有可无
xApp.Quit(); //必须存在,否则会出现Excel一直被打开的问题
xApp = null; //可有可无