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

1、最全的c#操作excel表格技巧本文將演示使用c控制excel中一些基本技能。包括創(chuàng)建、打開、保存excel文件，添加/ delet表，添加/刪除/隱藏行和列、以及設單元格的格式。準備工作，導入命名空間using microsoft.office.core;using microsoft.office.interop.excel;using system.io;using system.reflection;1。 如何創(chuàng)建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和后續(xù)進程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后續(xù)進程：退出并釋放/_wbk.cl