使用NOPI导入Excel文档
NOPI版本：2.3.0,依赖于NPOI的SharpZipLib版本：0.86,经测试适用于.net4.0+
记录遇到的几个问题
1.NOPI中的IWorkbook接口：xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现
2.日期转换，判断row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)
不能直接使用row.GetCell(j).DateCellValue,这玩意会直接抛出异常来~
1. 将文件流转换为DataTable
|
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
|
/// <summary>/// 根据Excel格式读取Excel/// </summary>/// <param name="stream">文件流</param>/// <param name="type">Excel格式枚举类型，xls/xlsx</param>/// <param name="sheetName">表名，默认取第一张</param>/// <returns>DataTable</returns>private static DataTable ImportExcel(Stream stream, ExcelExtType type, string sheetName){ DataTable dt = new DataTable(); IWorkbook workbook; try { //xls使用HSSFWorkbook类实现，xlsx使用XSSFWorkbook类实现 switch (type) { case ExcelExtType.xlsx: workbook = new XSSFWorkbook(stream); break; default: workbook = new HSSFWorkbook(stream); break; } ISheet sheet = null; //获取工作表 默认取第一张 if (string.IsNullOrWhiteSpace(sheetName)) sheet = workbook.GetSheetAt(0); else sheet = workbook.GetSheet(sheetName); if (sheet == null) return null; IEnumerator rows = sheet.GetRowEnumerator(); #region 获取表头 IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); if (cell != null) { dt.Columns.Add(cell.ToString()); } else { dt.Columns.Add(""); } } #endregion #region 获取内容 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { //判断单元格是否为日期格式 if (row.GetCell(j).CellType == NPOI.SS.UserModel.CellType.Numeric && HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) { if (row.GetCell(j).DateCellValue.Year >=1970) { dataRow[j] = row.GetCell(j).DateCellValue.ToString(); } else { dataRow[j] = row.GetCell(j).ToString(); } } else { dataRow[j] = row.GetCell(j).ToString(); } } } dt.Rows.Add(dataRow); } #endregion } catch (Exception ex) { dt=null; } finally { //if (stream != null) //{ // stream.Close(); // stream.Dispose(); //} } return dt;} |
2. 文件上载导入
|
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
|
/// <summary>/// 上传Excel导入/// </summary>/// <param name="file">上载文件对象</param>/// <param name="errorMsg">错误信息</param>/// <param name="sheetName">表名，默认取第一张</param>/// <returns></returns>public static DataTable Import(System.Web.HttpPostedFileBase file, ref string errorMsg, string sheetName = ""){ if (file == null || file.InputStream == null || file.InputStream.Length == 0) { errorMsg = "请选择要导入的Excel文件"; return null; } var excelType = GetExcelFileType(file.FileName); if (excelType == null) { errorMsg = "请选择正确的Excel文件"; return null; } using (var stream = new MemoryStream()) { file.InputStream.Position = 0; file.InputStream.CopyTo(stream); var dt = ImportExcel(stream, excelType.Value, sheetName); if (dt == null) errorMsg = "导入失败,请选择正确的Excel文件"; return dt; }} |
3. 本地路径读取导入
|
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
|
/// <summary>/// 根据文件路径导入Excel/// </summary>/// <param name="filePath"></param>/// <param name="errorMsg">错误信息</param>/// <param name="sheetName">表名，默认取第一张</param>/// <returns>可能为null的DataTable</returns>public static DataTable Import(string filePath, ref string errorMsg, string sheetName = ""){ var excelType = GetExcelFileType(filePath); if (GetExcelFileType(filePath) == null) { errorMsg = "请选择正确的Excel文件"; return null; } if (!File.Exists(filePath)) { errorMsg = "没有找到要导入的Excel文件"; return null; } DataTable dt; using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { dt = ImportExcel(stream, excelType.Value, sheetName); } if (dt == null) errorMsg = "导入失败,请选择正确的Excel文件"; return dt;} |
4.完整demo
附赠一个winform导入Excel的Demo。
https://github.com/yimogit/NopiExcelDemo
