使用NPOI读取Excel到DataTable

一、NPOI介绍：

使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作

二、安装NPOI

新建控制台应用程序>管理NuGet程序包>搜索NPOI>安装NPOI

三.下面是我需要的读取的Excel文件，数据格式如下：

四.添加ExcelHelper类：

[csharp]  view plain  copy

1. using System;  
2. using NPOI.SS.UserModel;  
3. using NPOI.XSSF.UserModel;  
4. using NPOI.HSSF.UserModel;  
5. using System.IO;  
6. using System.Data;  
7. namespace NPOI.ReadExcel  
8. {  
9.     public class ExcelHelper : IDisposable  
10.     {  
11.         private string fileName = null; //文件名  
12.         private IWorkbook workbook = null;  
13.         private FileStream fs = null;  
14.         private bool disposed;  
15.         public ExcelHelper(string fileName)  
16.         {  
17.             this.fileName = fileName;  
18.             disposed = false;  
19.         }  
20.         /// <summary>  
21.         /// 将DataTable数据导入到excel中  
22.         /// </summary>  
23.         /// <param name="data">要导入的数据</param>  
24.         /// <param name="isColumnWritten">DataTable的列名是否要导入</param>  
25.         /// <param name="sheetName">要导入的excel的sheet的名称</param>  
26.         /// <returns>导入数据行数(包含列名那一行)</returns>  
27.         public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)  
28.         {  
29.             int i = 0;  
30.             int j = 0;  
31.             int count = 0;  
32.             ISheet sheet = null;  
33.             fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);  
34.             if (fileName.IndexOf(".xlsx") > 0) // 2007版本  
35.                 workbook = new XSSFWorkbook();  
36.             else if (fileName.IndexOf(".xls") > 0) // 2003版本  
37.                 workbook = new HSSFWorkbook();  
38.             try  
39.             {  
40.                 if (workbook != null)  
41.                 {  
42.                     sheet = workbook.CreateSheet(sheetName);  
43.                 }  
44.                 else  
45.                 {  
46.                     return -1;  
47.                 }  
48.                 if (isColumnWritten == true) //写入DataTable的列名  
49.                 {  
50.                     IRow row = sheet.CreateRow(0);  
51.                     for (j = 0; j < data.Columns.Count; ++j)  
52.                     {  
53.                         row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);  
54.                     }  
55.                     count = 1;  
56.                 }  
57.                 else  
58.                 {  
59.                     count = 0;  
60.                 }  
61.                 for (i = 0; i < data.Rows.Count; ++i)  
62.                 {  
63.                     IRow row = sheet.CreateRow(count);  
64.                     for (j = 0; j < data.Columns.Count; ++j)  
65.                     {  
66.                         row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());  
67.                     }  
68.                     ++count;  
69.                 }  
70.                 workbook.Write(fs); //写入到excel  
71.                 return count;  
72.             }  
73.             catch (Exception ex)  
74.             {  
75.                 Console.WriteLine("Exception: " + ex.Message);  
76.                 return -1;  
77.             }  
78.         }  
79.         /// <summary>  
80.         /// 将excel中的数据导入到DataTable中  
81.         /// </summary>  
82.         /// <param name="sheetName">excel工作薄sheet的名称</param>  
83.         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>  
84.         /// <returns>返回的DataTable</returns>  
85.         public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)  
86.         {  
87.             ISheet sheet = null;  
88.             DataTable data = new DataTable();  
89.             int startRow = 0;  
90.             try  
91.             {  
92.                 fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);  
93.                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本  
94.                     workbook = new XSSFWorkbook(fs);  
95.                 else if (fileName.IndexOf(".xls") > 0) // 2003版本  
96.                     workbook = new HSSFWorkbook(fs);  
97.                 if (sheetName != null)  
98.                 {  
99.                     sheet = workbook.GetSheet(sheetName);  
100.                     if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet  
101.                     {  
102.                         sheet = workbook.GetSheetAt(0);  
103.                     }  
104.                 }  
105.                 else  
106.                 {  
107.                     sheet = workbook.GetSheetAt(0);  
108.                 }  
109.                 if (sheet != null)  
110.                 {  
111.                     IRow firstRow = sheet.GetRow(0);  
112.                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数  
113.                     if (isFirstRowColumn)  
114.                     {  
115.                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)  
116.                         {  
117.                             ICell cell = firstRow.GetCell(i);  
118.                             if (cell != null)  
119.                             {  
120.                                 string cellValue = cell.StringCellValue;  
121.                                 if (cellValue != null)  
122.                                 {  
123.                                     DataColumn column = new DataColumn(cellValue);  
124.                                     data.Columns.Add(column);  
125.                                 }  
126.                             }  
127.                         }  
128.                         startRow = sheet.FirstRowNum + 1;  
129.                     }  
130.                     else  
131.                     {  
132.                         startRow = sheet.FirstRowNum;  
133.                     }  
134.                     //最后一列的标号  
135.                     int rowCount = sheet.LastRowNum;  
136.                     for (int i = startRow; i <= rowCount; ++i)  
137.                     {  
138.                         IRow row = sheet.GetRow(i);  
139.                         if (row == null) continue; //没有数据的行默认是null　　　　　　　  
140.                         DataRow dataRow = data.NewRow();  
141.                         for (int j = row.FirstCellNum; j < cellCount; ++j)  
142.                         {  
143.                             if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null  
144.                                 dataRow[j] = row.GetCell(j).ToString();  
145.                         }  
146.                         data.Rows.Add(dataRow);  
147.                     }  
148.                 }  
149.                 return data;  
150.             }  
151.             catch (Exception ex)  
152.             {  
153.                 Console.WriteLine("Exception: " + ex.Message);  
154.                 return null;  
155.             }  
156.         }  
157.         public void Dispose()  
158.         {  
159.             Dispose(true);  
160.             GC.SuppressFinalize(this);  
161.         }  
162.         protected virtual void Dispose(bool disposing)  
163.         {  
164.             if (!this.disposed)  
165.             {  
166.                 if (disposing)  
167.                 {  
168.                     if (fs != null)  
169.                         fs.Close();  
170.                 }  
171.                 fs = null;  
172.                 disposed = true;  
173.             }  
174.         }  
175.     }  
176. }  

五，读取文件到DataTable:

[csharp]  view plain  copy

1. using System;  
2. using System.Collections.Generic;  
3. using System.Data;  
4. using System.Linq;  
5. using System.Text;  
6. using System.Threading.Tasks;  
7. namespace NPOI.ReadExcel  
8. {  
9.     class Program  
10.     {  
11.         static void Main(string[] args)  
12.         {  
13.             ExcelHelper excel_helper = new ExcelHelper(AppDomain.CurrentDomain.BaseDirectory + "test.xlsx");  
14.             DataTable dt = excel_helper.ExcelToDataTable("",true);  
15.             List<string> tableList=GetColumnsByDataTable(dt);  
16.             for (int i = 0; i < tableList.Count; i++)  
17.             {  
18.                 foreach (DataRow item in dt.Rows)  
19.                 {  
20.                     try  
21.                     {  
22.                         if (item[0].ToString() != "")  
23.                         {  
24.                             if (i < tableList.Count - 1 & item[i + 1].ToString()!="")  
25.                             {  
26.                                 Console.WriteLine(item[0].ToString()+"\t"+item[i+1].ToString());  
27.                             }  
28.                         }  
29.                     }  
30.                     catch (Exception ex)  
31.                     { }  
32.                 }  
33.                 Console.WriteLine("");  
34.             }  
35.             Console.ReadKey();  
36.         }  
37.         /// <summary>  
38.         /// 根据datatable获得列名  
39.         /// </summary>  
40.         /// <param name="dt">表对象</param>  
41.         /// <returns>返回结果的数据列数组</returns>  
42.         public static List<string> GetColumnsByDataTable(DataTable dt)  
43.         {  
44.             List<string> strColumns =new List<string> ();  
45.             if (dt.Columns.Count > 0)  
46.             {  
47.                 int columnNum = 0;  
48.                 columnNum = dt.Columns.Count;;  
49.                 for (int i = 0; i < dt.Columns.Count; i++)  
50.                 {  
51.                     strColumns.Add(dt.Columns[i].ColumnName);  
52.                 }  
53.             }  
54.             return strColumns;  
55.         }   
56.     }  
57. }  

版权声明：可自由转载、引用，请署名作者注明文章出处！ https://blog.csdn.net/heyangyi_19940703/article/details/52292755