读取Excel

本文导出Excel表格是基于NPOI

NPOI是指构建在POI 3.x版本之上的一个程序，NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。

NPOI的下载与引用:https://www.kochan.cn/2020/12/30/ExportExcel/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
      /// <summary>  
      /// 将excel导入到datatable  
      /// </summary>  
      /// <param name="filePath">excel路径</param>  
      /// <param name="isColumnName">第一行是否是列名</param>  
      /// <returns>返回datatable</returns>  
      public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
      {
          DataTable dataTable = null;
          FileStream fs = null;
          DataColumn column = null;
          DataRow dataRow = null;
          IWorkbook workbook = null;
          ISheet sheet = null;
          IRow row = null;
          ICell cell = null;
          int startRow = 0;
          try
          {
              fs = File.OpenRead(filePath);
              // 2007版本  
              if (filePath.IndexOf(".xlsx") > 0)
                  workbook = new XSSFWorkbook(fs);
              // 2003版本  
              else if (filePath.IndexOf(".xls") > 0)
                  workbook = new HSSFWorkbook(fs);

              if (workbook != null)
              {
                  sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet  
                  dataTable = new DataTable();
                  if (sheet != null)
                  {
                      int rowCount = sheet.LastRowNum;//总行数  
                      if (rowCount > 0)
                      {
                          IRow firstRow = sheet.GetRow(0);//第一行  
                          int cellCount = firstRow.LastCellNum;//列数  

                          //构建datatable的列  
                          if (isColumnName)
                          {
                              startRow = 1;//如果第一行是列名，则从第二行开始读取  
                              for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                              {
                                  cell = firstRow.GetCell(i);
                                  if (cell != null)
                                  {
                                      if (cell.StringCellValue != null)
                                      {
                                          column = new DataColumn(cell.StringCellValue);
                                          dataTable.Columns.Add(column);
                                      }
                                  }
                              }
                          }
                          else
                          {
                              for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                              {
                                  column = new DataColumn("column" + (i + 1));
                                  dataTable.Columns.Add(column);
                              }
                          }

                          //填充行  
                          for (int i = startRow; i <= rowCount; ++i)
                          {
                              row = sheet.GetRow(i);
                              if (row == null) continue;

                              dataRow = dataTable.NewRow();
                              for (int j = row.FirstCellNum; j < cellCount; ++j)
                              {
                                  cell = row.GetCell(j);
                                  if (cell == null)
                                  {
                                      dataRow[j] = "";
                                  }
                                  else
                                  {
								//是否是单元格合并
								if (cell.IsMergedCell)
								{
									cell = MergedCell(cell);
								}
                                      //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
                                      switch (cell.CellType)
                                      {
                                          case CellType.Blank:
                                              dataRow[j] = "";
                                              break;
                                          case CellType.Numeric:
                                              short format = cell.CellStyle.DataFormat;
                                              //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                              if (format == 14 || format == 31 || format == 57 || format == 58)
                                                  dataRow[j] = cell.DateCellValue;
                                              else
                                                  dataRow[j] = cell.NumericCellValue;
                                              break;
                                          case CellType.String:
                                              dataRow[j] = cell.StringCellValue;
                                              break;
                                          //单元格函数计算
                                          case CellType.Formula:
                                              dataRow[j] = cell.NumericCellValue;
                                              break;
                                      }	
                                  }
                              }
                              dataTable.Rows.Add(dataRow);
                          }
                      }
                  }
              }
              return dataTable;
          }
          catch (Exception)
          {
              if (fs != null)
              {
                  fs.Close();
              }
              return null;
          }
      }
/// <summary>
/// 读取合并单元格的值
/// </summary>
/// <param name="cell">查询的单元格</param>
/// <returns>返回有数值的单元格</returns>
private ICell MergedCell(ICell cell)
{
	if (cell.IsMergedCell)//是否是合并单元格
	{
		for (int i = 0; i < cell.Sheet.NumMergedRegions; i++)//遍历所有的合并单元格
		{
			var cellRange = cell.Sheet.GetMergedRegion(i);
			if (cell.ColumnIndex >= cellRange.FirstColumn && cell.ColumnIndex <= cellRange.LastColumn
				&& cell.RowIndex >= cellRange.FirstRow && cell.RowIndex <= cellRange.LastRow)//判断查询的单元格是否在合并单元格内
			{
				return cell.Sheet.GetRow(cellRange.FirstRow).GetCell(cellRange.FirstColumn);
			}
		}
	}
	return cell;
}

本文源于 https://download.csdn.net/detail/coderk2014/9328779