最全的C#操作Excel表格技巧

1、最全的c#操作excel表格技巧本文将演示使用c控制excel中一些基本技能。包括创建、打开、保存excel文件，添加/ delet表，添加/删除/隐藏行和列、以及设单元格的格式。准备工作，导入命名空间using microsoft.office.core;using microsoft.office.interop.excel;using system.io;using system.reflection;1。 如何创建excel文件application app = new application();workbooks wbks = app.workbooks;_workbook _wb

2、k = wbks.add(xxx);2. 读取, 删除和添加sheetsheets shs = _wbk.sheets;2.1 读取/i is index of the sheet which you want get:_worksheet _wsh = (_worksheet)shs.get_item(i)2.2 删除/must do when deleteapp.displayalerts = false;_wsh.delete();2.3 添加/a(before)，b(after)：besure position；c：amount；d：typeapp.worksheets.add(a，b

3、，c，d);2.4 重命名_wsh.name = xxx3. 删除行和列3.1 删除行 (range)_wsh.rows3, missing.value).delete(xldeleteshiftdirection.xlshiftup);3.2 删除列_wsh.get_range(_wsh.cells1, 2,_wsh.cells_wsh.rows.count, 2).delete(xldeleteshiftdirection.xlshifttoleft);4. 添加行和列4.1 添加行 (range)_wsh.rows11, missing.value).insert(missing.val

4、ue, xlinsertformatorigin.xlformatfromleftorabove);4.2 添加列_wsh.get_range(_wsh.cells1, 1, _wsh.cells_wsh.rows.count, 1).insert(missing.value, xlinsertshiftdirection.xlshifttoright);5. 格式化单元格5.1 获取单元格/get cell object_wsh.cellsrow, cell5.2 设置公式/input formula in the cell_wsh.cellsrow, cell = =sum(a1/b1);

5、5.3 合并单元格 (range)_wsh.rows1, missing.value).merge(missing.value);5.4设置行高和列宽 (range)_wsh.rows3, missing.value).rowheight = 5;(range)_wsh.rows3, missing.value).columnwidth = 5;5.5设置单元格颜色 (range)_wsh.rows1, missing.value).interior.colorindex = 3;download excel_color_table - 6.22 kb5.6 设置字体大小 (range)_ws

6、h.cells1, b).font.size = 8;5.7 设置字体加粗 (range)_wsh.rows1, missing.value).font.bold = false;5.8 设置水平对齐方式 (range)_wsh.cells2, 1).horizontalalignment = xlvalign.xlvaligncenter;5.9 设置字段边界 (range)_wsh.cells3, 3).borders.linestyle = 3;5.10 设置上下左右边界线/左边界_wsh.get_range(_wsh.cells2, 1, _wsh.cells2, 2).borders

7、xlbordersindex.xledgeleft.weight = xlborderweight.xlthick;/ /右边界_wsh.get_range(_wsh.cells2, 1, _wsh.cells2, 2).bordersxlbordersindex.xledgeright.weight = xlborderweight.xlthick;/ /上边界_wsh.get_range(_wsh.cells2, 1, _wsh.cells2, 2).bordersxlbordersindex.xledgetop.weight = xlborderweight.xlthick; /下边界_

8、wsh.get_range(_wsh.cells2, 1, _wsh.cells2, 2).bordersxlbordersindex.xledgebottom.weight = xlborderweight.xlthick;6. 复制选定字段_worksheet _wsh = (_worksheet)shs.get_item(1);/ copy selected field content range range = _wsh.get_range(_wsh.cells7, 1, _wsh.cells10, _wsh.columns.count); range.select();range.c

9、opy(type.missing); /select paste starting positionrange test = (range)_wsh.cells11, 1);test.select(); / shield alert, default confirm pasteapp.displayalerts = false;test.parse(missing.value, missing.value);7. 保存excel和后续进程7.1 保存excel文件/ shield alert popped up from sysytemapp.alertbeforeoverwriting = false; / save to selected file pathsaveas(filepath, missing.value, missing.value, missing.value, missing.value, missing.value, microsoft.office.interop.excel.xlsaveasaccessmode.xlnochange, missing.value, missing.value, missing.value, missing.value, missing.value);7.2后续进程：退出并释放/_wbk.cl