引用Microsoft.Office.Interop.Excel.dll文件
添加using1
2using Microsoft.Office.Interop.Excel;
using Excel=Microsoft.Office.Interop.Excel;
设置程序运行语言
1 | System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; |
创建并使用Application
1 | //创建Application |
获取最后一行、一列的两种方法
1 | //获取已用的范围数据 |
将Excel数据存入二维数组
1 | //rowsCount:最大行 colsCount:最大列 |
在第一列的左边插入一列
1 | Excel.Range xlsColumns = (Excel.Range)xlsWorkSheet.Columns[1, System.Type.Missing]; |
删除行
1 | Range deleteRng = (Range)xlsWorkSheetSapExcel.Rows[2, System.Type.Missing]; |
删除一列数据
1 | ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 11]).Select(); |
设置背景色为红色
1 | xlsWorkSheet.get_Range("A1", "A1").Interior.ColorIndex = 3; |
设置Format属性 属性值可以通过在vba中录宏得到
1 | Microsoft.Office.Interop.Excel.Range range1 = xlsWorkSheetAdd.get_Range("J1", "J65535"); |
替换
1 | Range Drng = xlsWorkSheetTemplate.get_Range("D1", "D65535"); |
分列处理 设置为文本
1 | Range TextToColumnRng = xlsWorkSheet.get_Range("E1", "E65535"); |
设置公式
1 | rng = xlMergeFileWorkSheet.get_Range("D2", "D" + rowcount);//设置列范围 |
筛选
1 | //确定筛选范围 |
复制粘贴
1 | D2_rng.Copy(Type.Missing); |
Find查找
1 | rng = mySheet.get_Range("A1", "IV10").Find(arrLabel[j], Type.Missing, |
文字占满单元格
1 | range.EntireColumn.AutoFit(); |
另存
1 | xlsWorkBook.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); |
关闭对象
1 | System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkSheet); |
关闭Excel进程
1 | /// <summary> |