C#中NPOI操作excel之读取和写入excel数据

 http://blog.csdn.net/coderk2014/article/details/50185163

一、下载引用

下载需要引用的dll，即：NPOI.dll，NPOI.OOXML.dll，NPOI.OpenXml4Net.dll，ICSharpCode.SharpZipLib.dll（office2007版需要此dll）。

二、excel转datatable类

[csharp] view plain copy

1. using System;  
2. using System.Data;  
3. using System.IO;  
4. using NPOI.SS.UserModel;  
5. using NPOI.XSSF.UserModel;  
6. using NPOI.HSSF.UserModel;  
7.   
8. namespace NPOIOprateExcel  
9. {  
10.     public class ExcelUtility  
11.     {  
12.         /// <summary>  
13.         /// 将excel导入到datatable  
14.         /// </summary>  
15.         /// <param name="filePath">excel路径</param>  
16.         /// <param name="isColumnName">第一行是否是列名</param>  
17.         /// <returns>返回datatable</returns>  
18.         public static DataTable ExcelToDataTable(string filePath, bool isColumnName)  
19.         {  
20.             DataTable dataTable = null;  
21.             FileStream fs = null;  
22.             DataColumn column = null;  
23.             DataRow dataRow = null;  
24.             IWorkbook workbook = null;  
25.             ISheet sheet = null;  
26.             IRow row = null;  
27.             ICell cell = null;  
28.             int startRow = 0;  
29.             try  
30.             {  
31.                 using (fs = File.OpenRead(filePath))  
32.                 {  
33.                     // 2007版本  
34.                     if (filePath.IndexOf(".xlsx") > 0)  
35.                         workbook = new XSSFWorkbook(fs);  
36.                     // 2003版本  
37.                     else if (filePath.IndexOf(".xls") > 0)  
38.                         workbook = new HSSFWorkbook(fs);  
39.   
40.                     if (workbook != null)  
41.                     {  
42.                         sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet  
43.                         dataTable = new DataTable();  
44.                         if (sheet != null)  
45.                         {  
46.                             int rowCount = sheet.LastRowNum;//总行数  
47.                             if (rowCount > 0)  
48.                             {  
49.                                 IRow firstRow = sheet.GetRow(0);//第一行  
50.                                 int cellCount = firstRow.LastCellNum;//列数  
51.   
52.                                 //构建datatable的列  
53.                                 if (isColumnName)  
54.                                 {  
55.                                     startRow = 1;//如果第一行是列名，则从第二行开始读取  
56.                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)  
57.                                     {  
58.                                         cell = firstRow.GetCell(i);  
59.                                         if (cell != null)  
60.                                         {  
61.                                             if (cell.StringCellValue != null)  
62.                                             {  
63.                                                 column = new DataColumn(cell.StringCellValue);  
64.                                                 dataTable.Columns.Add(column);  
65.                                             }  
66.                                         }  
67.                                     }  
68.                                 }  
69.                                 else  
70.                                 {  
71.                                     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)  
72.                                     {  
73.                                         column = new DataColumn("column" + (i + 1));  
74.                                         dataTable.Columns.Add(column);  
75.                                     }  
76.                                 }  
77.   
78.                                 //填充行  
79.                                 for (int i = startRow; i <= rowCount; ++i)  
80.                                 {  
81.                                     row = sheet.GetRow(i);  
82.                                     if (row == null) continue;  
83.   
84.                                     dataRow = dataTable.NewRow();  
85.                                     for (int j = row.FirstCellNum; j < cellCount; ++j)  
86.                                     {  
87.                                         cell = row.GetCell(j);                                          
88.                                         if (cell == null)  
89.                                         {  
90.                                             dataRow[j] = "";  
91.                                         }  
92.                                         else  
93.                                         {  
94.                                             //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
95.                                             switch (cell.CellType)  
96.                                             {  
97.                                                 case CellType.Blank:  
98.                                                     dataRow[j] = "";  
99.                                                     break;  
100.                                                 case CellType.Numeric:  
101.                                                     short format = cell.CellStyle.DataFormat;  
102.                                                     //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
103.                                                     if (format == 14 || format == 31 || format == 57 || format == 58)  
104.                                                         dataRow[j] = cell.DateCellValue;  
105.                                                     else  
106.                                                         dataRow[j] = cell.NumericCellValue;  
107.                                                     break;  
108.                                                 case CellType.String:  
109.                                                     dataRow[j] = cell.StringCellValue;  
110.                                                     break;  
111.                                             }  
112.                                         }  
113.                                     }  
114.                                     dataTable.Rows.Add(dataRow);  
115.                                 }  
116.                             }  
117.                         }  
118.                     }  
119.                 }  
120.                 return dataTable;  
121.             }  
122.             catch (Exception)  
123.             {  
124.                 if (fs != null)  
125.                 {  
126.                     fs.Close();  
127.                 }  
128.                 return null;  
129.             }  
130.         }  
131.     }  
132. }  

三、结果

四、写入excel类

[csharp] view plain copy

1. public static bool DataTableToExcel(DataTable dt)  
2.         {  
3.             bool result = false;  
4.             IWorkbook workbook = null;  
5.             FileStream fs = null;  
6.             IRow row = null;  
7.             ISheet sheet = null;  
8.             ICell cell = null;  
9.             try  
10.             {  
11.                 if (dt != null && dt.Rows.Count > 0)  
12.                 {  
13.                     workbook = new HSSFWorkbook();  
14.                     sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表  
15.                     int rowCount = dt.Rows.Count;//行数  
16.                     int columnCount = dt.Columns.Count;//列数  
17.   
18.                     //设置列头  
19.                     row = sheet.CreateRow(0);//excel第一行设为列头  
20.                     for (int c = 0; c < columnCount; c++)  
21.                     {  
22.                         cell = row.CreateCell(c);  
23.                         cell.SetCellValue(dt.Columns[c].ColumnName);  
24.                     }                      
25.   
26.                     //设置每行每列的单元格,  
27.                     for (int i = 0; i <rowCount; i++)  
28.                     {  
29.                         row = sheet.CreateRow(i+1);  
30.                         for (int j = 0; j < columnCount; j++)  
31.                         {                              
32.                             cell = row.CreateCell(j);//excel第二行开始写入数据  
33.                             cell.SetCellValue(dt.Rows[i][j].ToString());                              
34.                         }  
35.                     }  
36.                     using (fs = File.OpenWrite(@"D:/myxls.xls"))   
37.                     {  
38.                         workbook.Write(fs);//向打开的这个xls文件中写入数据  
39.                         result = true;  
40.                     }  
41.                 }  
42.                 return result;  
43.             }  
44.             catch (Exception ex)  
45.             {  
46.                 if (fs != null)  
47.                 {  
48.                     fs.Close();  
49.                 }  
50.                 return false;  
51.             }  
52.         }  

结果如下：

源码地址：http://download.csdn.net/detail/coderk2014/9328779