1         /// <summary>
 2         /// 将excel文件内容读取到DataTable数据表中
 3         /// </summary>
 4         /// <param name="fileName">文件完整路径名</param>
 5         /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
 6         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
 7         /// <returns>DataTable数据表</returns>
 8         public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
 9         {
10             //定义要返回的datatable对象
11             DataTable data = new DataTable();
12             //excel工作表
13             Npoi.SS.UserModel.ISheet sheet = null;
14             //数据开始行(排除标题行)
15             int startRow = 0;
16             try
17             {
18                 if (!File.Exists(fileName))
19                 {
20                     return null;
21                 }
22                 //根据指定路径读取文件
23                 FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
24                 //根据文件流创建excel数据结构
25                 Npoi.SS.UserModel.IWorkbook workbook = Npoi.SS.UserModel.WorkbookFactory.Create(fs);
26                 //IWorkbook workbook = new HSSFWorkbook(fs);
27                 //如果有指定工作表名称
28                 if (!string.IsNullOrEmpty(sheetName))
29                 {
30                     sheet = workbook.GetSheet(sheetName);
31                     //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
32                     if (sheet == null)
33                     {
34                         sheet = workbook.GetSheetAt(0);
35                     }
36                 }
37                 else
38                 {
39                     //如果没有指定的sheetName，则尝试获取第一个sheet
40                     sheet = workbook.GetSheetAt(0);
41                 }
42                 if (sheet != null)
43                 {
44                     Npoi.SS.UserModel.IRow firstRow = sheet.GetRow(0);
45                     //一行最后一个cell的编号 即总的列数
46                     int cellCount = firstRow.LastCellNum;
47                     //如果第一行是标题列名
48                     if (isFirstRowColumn)
49                     {
50                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
51                         {
52                             Npoi.SS.UserModel.ICell cell = firstRow.GetCell(i);
53                             if (cell != null)
54                             {
55                                 string cellValue = cell.StringCellValue;
56                                 if (cellValue != null)
57                                 {
58                                     DataColumn column = new DataColumn(cellValue);
59                                     data.Columns.Add(column);
60                                 }
61                             }
62                         }
63                         startRow = sheet.FirstRowNum + 1;
64                     }
65                     else
66                     {
67                         startRow = sheet.FirstRowNum;
68                     }
69                     //最后一列的标号
70                     int rowCount = sheet.LastRowNum;
71                     for (int i = startRow; i <= rowCount; ++i)
72                     {
73                         Npoi.SS.UserModel.IRow row = sheet.GetRow(i);
74                         if (row == null) continue; //没有数据的行默认是null　　　　　　　
75 
76                         DataRow dataRow = data.NewRow();
77                         for (int j = row.FirstCellNum; j < cellCount; ++j)
78                         {
79                             if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
80                                 dataRow[j] = row.GetCell(j).ToString();
81                         }
82                         data.Rows.Add(dataRow);
83                     }
84                 }
85                 return data;
86             }
87             catch (Exception ex)
88             {
89                 throw ex;
90             }
91         }

  1         /// <summary>
  2         /// 将文件流读取到DataTable数据表中
  3         /// </summary>
  4         /// <param name="fileStream">文件流</param>
  5         /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
  6         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
  7         /// <returns>DataTable数据表</returns>
  8         public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
  9         {
 10             //定义要返回的datatable对象
 11             DataTable data = new DataTable();
 12             //excel工作表
 13             Npoi.SS.UserModel.ISheet sheet = null;
 14             //数据开始行(排除标题行)
 15             int startRow = 0;
 16             try
 17             {
 18                 //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
 19                 Npoi.SS.UserModel.IWorkbook workbook = Npoi.SS.UserModel.WorkbookFactory.Create(fileStream);
 20                 //如果有指定工作表名称
 21                 if (!string.IsNullOrEmpty(sheetName))
 22                 {
 23                     sheet = workbook.GetSheet(sheetName);
 24                     //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
 25                     if (sheet == null)
 26                     {
 27                         sheet = workbook.GetSheetAt(0);
 28                     }
 29                 }
 30                 else
 31                 {
 32                     //如果没有指定的sheetName，则尝试获取第一个sheet
 33                     sheet = workbook.GetSheetAt(0);
 34                 }
 35                 if (sheet != null)
 36                 {
 37                     Npoi.SS.UserModel.IRow firstRow = sheet.GetRow(0);
 38                     //一行最后一个cell的编号 即总的列数
 39                     int cellCount = firstRow.LastCellNum;
 40                     //如果第一行是标题列名
 41                     if (isFirstRowColumn)
 42                     {
 43                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
 44                         {
 45                             Npoi.SS.UserModel.ICell cell = firstRow.GetCell(i);
 46                             if (cell != null)
 47                             {
 48                                 string cellValue = cell.StringCellValue;
 49                                 if (cellValue != null)
 50                                 {
 51                                     DataColumn column = new DataColumn(cellValue);
 52                                     data.Columns.Add(column);
 53                                 }
 54                             }
 55                         }
 56                         startRow = sheet.FirstRowNum + 1;
 57                     }
 58                     else
 59                     {
 60                         startRow = sheet.FirstRowNum;
 61                     }
 62                     //最后一列的标号
 63                     int rowCount = sheet.LastRowNum;
 64                     for (int i = startRow; i <= rowCount; ++i)
 65                     {
 66                         Npoi.SS.UserModel.IRow row = sheet.GetRow(i);
 67                         if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null　　　　　　　
 68 
 69                         DataRow dataRow = data.NewRow();
 70                         for (int j = row.FirstCellNum; j < cellCount; ++j)
 71                         {
 72                             //同理，没有数据的单元格都默认是null
 73                             Npoi.SS.UserModel.ICell cell = row.GetCell(j);
 74                             if (cell != null)
 75                             {
 76                                 if (cell.CellType == Npoi.SS.UserModel.CellType.Numeric)
 77                                 {
 78                                     //判断是否日期类型
 79                                     if (Npoi.SS.UserModel.DateUtil.IsCellDateFormatted(cell))
 80                                     {
 81                                         dataRow[j] = row.GetCell(j).DateCellValue;
 82                                     }
 83                                     else
 84                                     {
 85                                         dataRow[j] = row.GetCell(j).ToString().Trim();
 86                                     }
 87                                 }
 88                                 else
 89                                 {
 90                                     dataRow[j] = row.GetCell(j).ToString().Trim();
 91                                 }
 92                             }
 93                         }
 94                         data.Rows.Add(dataRow);
 95                     }
 96                 }
 97                 return data;
 98             }
 99             catch (Exception ex)
100             {
101                 throw ex;
102             }
103         }