C#读写Excel的几种方法

1 使用office自带的库

前提是本机须安装office才能运行，且不同的office版本之间可能会有兼容问题，从nuget下载 microsoft.office.interop.excel

读写代码如下：

 1 using microsoft.office.interop.excel;
 2 using excel = microsoft.office.interop.excel;
 3 
 4         private void btn_office_click(object sender, eventargs e)
 5         {
 6             string importexcelpath = "e:\\import.xlsx";
 7             string exportexcelpath = "e:\\export.xlsx";
 8             //创建
 9             excel.application xlapp = new excel.application();
10             xlapp.displayalerts = false;
11             xlapp.visible = false;
12             xlapp.screenupdating = false;
13             //打开excel
14             excel.workbook xlsworkbook = xlapp.workbooks.open(importexcelpath, system.type.missing, system.type.missing, system.type.missing,
15             system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing, system.type.missing,
16             system.type.missing, system.type.missing, system.type.missing, system.type.missing);
17 
18             //处理数据过程，更多操作方法自行百度
19             excel.worksheet sheet = xlsworkbook.worksheets[1];//工作薄从1开始，不是0
20             sheet.cells[1, 1] = "test";
21 
22             //另存
23             xlsworkbook.saveas(exportexcelpath, type.missing, type.missing, type.missing, type.missing, type.missing, xlsaveasaccessmode.xlnochange,
24                 type.missing, type.missing, type.missing, type.missing, type.missing);
25             //关闭excel进程
26             closepro(xlapp, xlsworkbook);
27         }
28 
29         public void closepro(excel.application xlapp, excel.workbook xlsworkbook)
30         {
31             if (xlsworkbook != null)
32                 xlsworkbook.close(true, type.missing, type.missing);
33             xlapp.quit();
34             // 安全回收进程
35             system.gc.getgeneration(xlapp);
36             intptr t = new intptr(xlapp.hwnd);   //获取句柄
37             int k = 0;
38             getwindowthreadprocessid(t, out k);   //获取进程唯一标志
39             system.diagnostics.process p = system.diagnostics.process.getprocessbyid(k);
40             p.kill();     //关闭进程
41         }

2. 使用npoi

在不安装office的时候也是可以读写的，速度很快，从nuget下载 npoi

 

读写代码如下：

 1 using system.io;
 2 using npoi;
 3 using npoi.ss.usermodel;
 4 
 5         private void btn_npoi_click(object sender, eventargs e)
 6         {
 7             string importexcelpath = "e:\\import.xlsx";
 8             string exportexcelpath = "e:\\export.xlsx";
 9             iworkbook workbook = workbookfactory.create(importexcelpath);
10             isheet sheet = workbook.getsheetat(0);//获取第一个工作薄
11             irow row = (irow)sheet.getrow(0);//获取第一行
12 
13             //设置第一行第一列值,更多方法请参考源官方demo
14             row.createcell(0).setcellvalue("test");//设置第一行第一列值
15 
16             //导出excel
17             filestream fs = new filestream(exportexcelpath, filemode.create, fileaccess.readwrite);
18             workbook.write(fs);
19             fs.close();
20         }

3. 使用closedxml  从nuget下载 closedxml

 

读写代码如下：

 1 using closedxml;
 2 using closedxml.excel;
 3 
 4         private void btn_closedxml_click(object sender, eventargs e)
 5         {
 6             string importexcelpath = "e:\\import.xlsx";
 7             string exportexcelpath = "e:\\export.xlsx";
 8             var workbook = new xlworkbook(importexcelpath);
 9 
10             ixlworksheet sheet = workbook.worksheet(1);//这个库也是从1开始
11             //设置第一行第一列值,更多方法请参考官方demo
12             sheet.cell(1, 1).value = "test";//该方法也是从1开始，非0
13 
14             workbook.saveas(exportexcelpath);
15         }

4. 使用 spire.xls，spire分免费和收费，无特殊需求用免费即可

从nuget下载 free spire.xls for .net

 

读写代码如下：

 1 using spire.xls;
 2 
 3         private void btnspire_click(object sender, eventargs e)
 4         {
 5             string importexcelpath = "e:\\import.xlsx";
 6             string exportexcelpath = "e:\\export.xlsx";
 7 
 8             spire.xls.workbook workbook = new spire.xls.workbook();
 9             workbook.loadfromfile(importexcelpath);
10             //处理excel数据，更多请参考官方demo
11             spire.xls.worksheet sheet = workbook.worksheets[0];
12             sheet.range[1,1].text = "test";//该方法也是从1开始，非0
13 
14             workbook.savetofile(exportexcelpath);
15         }

5. epplus，没用过这个，暂时就不做介绍了