项目中常常会涉及到对Excel的读写,设置格式的操作,通常的情况下,我会使用开源的第三方组件NPOI,因为它高效轻便。当然,Excel的读写还有其他很多方式,这里不再一一列举。
但遇到一些稍微复杂的情况时,还得用COM组件才能完成。Excel COM组件是以接口形式向客户端提供服务的一个dll。经过一段时间的使用,想把经常使用的用法,做一下归纳总结。
!!本文不定时更新……!!
【VS中dll引用】
首先要引入相应的Office->Excel的dll。在VS中依次点击引用->添加引用->COM->Microsoft Excel xx Object Library。(安装的Office的版本不同,xx部分会不同,我电脑上安装的是Office 2019,所以是16.0。但代码写法都一样)
如果在运行时,创建ExcelApplication报错,则将嵌入互操作类型改为false
然后引用命名空间,当然也可以给该命名空间起一个别名:
using Excel = Microsoft.Office.Interop.Excel;
注意,下标都是从1开始的,从1开始的,从1开始的!!
【基础知识】
要使用Excel COM组件,我们需要了解4个对象:Application, Workbook, Worksheet, Range
1. Application: Excel实例,创建此对象,会在任务管理器中多出一个EXCEL.EXE的进程
2. WorkBook:Excel文件,相当于工作薄。
3. Worksheet:Excel文件中的工作表。
4. Range:区域块,相当于在excel中用鼠标选中一个或多个单元格。
【工作簿的操作】
- 新建一个工作薄,并以“test.xlsx”保存至指定目录
Excel.Application xlApp = new Excel.Application();
Excel.Workbook workbook = null;
workbook = xlApp.Workbooks.Add();
workbook.SaveAs(@"D:\test.xlsx");
xlApp.Quit();
xlApp = null;
此时生成的test.xlsx中有一个空白的工作表
- 打开工作薄
往一个已经定义好的空白模板中写入数据等情况
Excel.Application xlApp = new Excel.Application();
Excel.Workbook workbook = null;
workbook = xlApp.Workbooks.Open(@"D:\test.xlsx");
- 关闭工作薄
workbook.Close(SaveChanges: true);
写文件内容有修改时,写法如上。
- 保存工作簿的副本(另存为)
workbook.SaveCopyAs(@"D:\123.xlsx");
workbook.Close();
【工作表的操作】
- 打开工作薄,并获取某个工作表
Excel.Application xlApp = new Excel.Application();
Excel.Workbook workbook = null;
workbook = xlApp.Workbooks.Open(filePath);//文件有绝对路径
Excel.Worksheet worksheet = workbook.Worksheets["sheetName"];//按工作表名称获取
//or
Excel.Worksheet worksheet = workbook.Worksheets[1];//按下标获取
- 新建工作表
与在excel中操作一样,新建的工作表后,选中的工作表为刚刚新建的
//在excel文件中处于选中状态的工作表前添加
Excel.Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Name = "new sheet";
//**结果:new sheet,Sheet1**
//在指定工作表前添加
Excel.Worksheet w1 = workbook.Worksheets["Sheet1"];
Excel.Worksheet worksheet = workbook.Worksheets.Add(Before: w1);
worksheet.Name = "sheet2";
//**结果:new sheet,sheet2,Sheet1**
//在指定工作表后添加
Excel.Worksheet w1 = workbook.Worksheets["Sheet1"];
Excel.Worksheet worksheet = workbook.Worksheets.Add(After: w1);
worksheet.Name = "sheet2";
//**结果:new sheet,Sheet1,sheet2**
//一次添加多个工作表
Excel.Worksheet worksheet = workbook.Worksheets.Add(Count:2);
- 设置工作表标签颜色
Excel.Worksheet worksheet = workbook.Worksheets["Sheet1"];
worksheet.Tab.Color = Excel.XlRgbColor.rgbYellow;
- 设置页边距
worksheet.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
worksheet.PageSetup.RightMargin = Application.InchesToPoints(0.75)
worksheet.PageSetup.TopMargin = Application.InchesToPoints(1.5)
worksheet.PageSetup.BottomMargin = Application.InchesToPoints(1)
worksheet.PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
worksheet.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
- 设置页眉页脚
worksheet.PageSetup.LeftHeader = "左侧页眉";
worksheet.PageSetup.CenterHeader = "中部页眉";
worksheet.PageSetup.RightHeader = "右侧页眉";
worksheet.PageSetup.LeftFooter = "左侧页脚";
worksheet.PageSetup.CenterFooter = "中部页脚";
worksheet.PageSetup.RightFooter = "右侧页脚";
【单元格的操作】
用到最多的在这里~
- 获取某个单元格的值
string val = worksheet.Cells[1, 1].Value;//这种写法的Value属性没有提示的(.不出来)
//or
Excel.Range range = worksheet.Cells[1, 1];
string val1=range.Value;//这种写法的Value是有提示的(能.出来)
- 设置某个单元格的值
worksheet.Cells[1, 2] = "123";//B1
//或
Excel.Range range = worksheet.Cells[1, 3];//C1
range.Value = "234";
Cells属性是一个二维数组,即Cells[行号,列号]
- 获取多个单元格
Excel.Range range = worksheet.Cells.get_Range("A2:B3");
在对多个单元格读写时,通常会用两层循环来完成。用NPOI时,一般很快能执行完成,不会有明显的效率问题,但用COM组件时,在循环中反复操作Excel的Range,这就造成十分影响性能。
其实,Range对象可以简单理解成两数组,这样就可以对通过一个两维数组来实现一次性读写。
- 获取多个单元格的值★
Excel.Range range = worksheet.Rows.get_Range("A3:B4");
object[,] vals = new object[2, 2];
vals = range.Value;
- 设置多个单元格的值★
Excel.Range range = worksheet.Rows.get_Range("A3:B4");
string[,] vals = new string[2, 2];
vals[0, 0] = "0";
vals[0, 1] = "1";
vals[1, 0] = "2";
vals[1, 1] = "3";
- 设置文字颜色
我比较喜欢第二种方法,原因就两字:直观
range.Font.ColorIndex = 5;//这种方法不直观,颜色的值不清楚对应的具体什么颜色
//or
range.Font.Color = Excel.XlRgbColor.rgbRed;//内置的RGB颜色
- 设置字体、字号等其他属性
range.Font.Name = "Microsoft YaHei";//字体
range.Font.Size = 13;//字号
range.Font.Bold = true;//加粗
range.Font.Italic = true;//倾斜
range.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle;//下划线(XlUnderlineStyle枚举类还有其他的选项)
range.WrapText = true;//折行
range.Font.Strikethrough = true;//删除线
- 设置单元格背景色
range.Interior.Color = Excel.XlColorIndex.xlColorIndexNone;//无填充色
range.Interior.Color = Excel.XlRgbColor.rgbRed;//内置的RGB颜色
- 设置单元格文字方向
Excel.Range range = worksheet.Cells[1, 1];
range.Orientation = Excel.XlOrientation.xlVertical;
XlOrientation的4个枚举值,结果如下图
- 设置单元格边框
//斜线,对区域中的所有单元格设置
range.Borders.Item[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlDouble;//左上至右下斜线
range.Borders.Item[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlDouble;//左下至右上斜线
//横竖边框,区域的边框,并非对所有单元格设置
range.Borders.Item[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlDouble;//上边框
range.Borders.Item[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;//下边框
range.Borders.Item[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;//左边框
range.Borders.Item[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;//右边框
//区域内所有单元格横竖边框
range.Borders.Item[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlDouble;//区域内水平边框
range.Borders.Item[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlDouble;//区域内垂直边框
边框线型、颜色和粗细
Excel.Border border_top = range.Borders.Item[Excel.XlBordersIndex.xlEdgeTop];
border_top.LineStyle = Excel.XlLineStyle.xlDouble;//线型
border_top.Color = Excel.XlRgbColor.rgbRed;//颜色
border_top.Weight = Excel.XlBorderWeight.xlThin;//粗细
Excel.XlLineStyle枚举值https://docs.microsoft.com/zh-cn/office/vba/api/excel.xllinestylehttps://docs.microsoft.com/zh-cn/office/vba/api/excel.xllinestyle
Excel.XlBorderWeight枚举值https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlborderweighthttps://docs.microsoft.com/zh-cn/office/vba/api/excel.xlborderweight
- 合并/取消合并单元格
Excel.Range range = worksheet.Cells.get_Range("A2:B3");
range.Merge();
range.Value = 123;//合并的单元格赋值
range.UnMerge();//取消合并单元格
- 单元格对齐方式
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
Excel.XlHAlign枚举值https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlhalignhttps://docs.microsoft.com/zh-cn/office/vba/api/excel.xlhalign
Excel.XlVAlign枚举值https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlvalignhttps://docs.microsoft.com/zh-cn/office/vba/api/excel.xlvalign
- 设置单元格格式
通常情况下,设置Value属性即可,但对于如身份证号等不希望以科学计数法显示的,要设置Value2属性
range.Value2 = "320112000000000000";
range.NumberFormat = "@";//设置为文本格式
Value2属性和 Value 属性的唯一区别在于 Value2 属性不使用 Currency 和 Date 数据类型。通过使用 Double 数据类型,可将用这些数据类型设置格式的数据按浮点数字返回。
自定义格式,如以下代码在单元格中显示为123.0,但实际数据为123
range.Value = "123";
range.NumberFormat = "#.0";
其他自定义格式的写法,Excel中怎么写,NumberFormat就怎么写,不再赘述。
- 设置行高
//单行
Excel.Range range = worksheet.Rows[1];
range.RowHeight = 30;//单位pt
//多行
Excel.Range range = worksheet.Rows.get_Range("2:3");
range.RowHeight = 30;
- 设置列宽
//单列
Excel.Range range = worksheet.Columns[1];
range.ColumnWidth = 30;//单位 1/10in
//多列
Excel.Range range = worksheet.Columns.get_Range("B:C");
range.ColumnWidth = 30;
- 插入行
//在第二行上方,插入1行
Excel.Range range = worksheet.Rows[2];//第二行
range.Insert(Excel.XlDirection.xlDown);
//在第二行上方,插入2行
Excel.Range range = worksheet.Rows.get_Range("2:3");//第2、3行
range.Insert(Excel.XlDirection.xlDown);
- 删除行
Excel.Range range = worksheet.Rows.get_Range("1:100");//第1~100行
range.Delete(Excel.XlDirection.xlDown);//100之后的行向上填充
- 插入单元格
//在E1上方插入单元格
Excel.Range range = worksheet.Cells[1, 5];//E1
range.Insert(Excel.XlDirection.xlDown);
- 数据排序
比如对如下数据,按B2升序排序
Excel.Range range = worksheet.Columns.get_Range("A:C");//要排序的数据范围
//key:排序字段(可设多个)
//order:排序方式,升序或降序(可设多个)
//header:数据是否有标题
//orientation:排序类型(行排序or列排序,简单理解为横向表格和纵向表格),默认行排序
range.Sort(Key1: worksheet.Cells[1, 2], Order1: Excel.XlSortOrder.xlAscending, Header: Excel.XlYesNoGuess.xlYes, Orientation: Excel.XlSortOrientation.xlSortColumns);
数据筛选
1)按值筛选
Excel.Range range = worksheet.Cells.get_Range("A1:C4");
//单条件
range.AutoFilter(Field: 2, Criteria1: ">=26", VisibleDropDown: true);
//多条件
range.AutoFilter(Field: 2, Criteria1: ">=26", Operator: Excel.XlAutoFilterOperator.xlAnd, Criteria2: "<30", VisibleDropDown: true);
2)按颜色筛选
Excel.Range range = worksheet.Cells.get_Range("A1:C4");
//按字体颜色筛选
range.AutoFilter(Field: 2, Criteria1: Excel.XlRgbColor.rgbRed, Operator: Excel.XlAutoFilterOperator.xlFilterFontColor, VisibleDropDown: true);
//按单元格颜色筛选
range.AutoFilter(Field: 2, Criteria1: Excel.XlRgbColor.rgbRed, Operator: Excel.XlAutoFilterOperator.xlFilterCellColor, VisibleDropDown: true);
- 单元格设置数据有效性验证(下拉选项)
//E1~E3是用于下拉框的选项
Excel.Range range_pulldown = worksheet.Cells.get_Range("E1:E3");
range_pulldown.Name = "pull";
//对B2~B3设置下拉框
Excel.Range cell = worksheet.Cells.get_Range("B2:B3");
cell.Validation.Delete();//删除数据有效性校验,如果单元格无如果已数据有效性校验,无需此行;;如有必须有此行,否则下一步报错
cell.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "=pull", Type.Missing);
//如果不想对选项区域设置名称,则
cell.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "=$E$1:$E$3", Type.Missing);
后续有新的再添加