前端上传excel文件利用npoi读取数据转换成datatable(netcore坑爹啊，用的vs2017竟然不能可视化)
前端界面
@{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Xlsx</title> </head> <body> <form enctype="multipart/form-data" method="post" asp-action="ImportToDataTable"> <input type="file" name="excelfile" /> <input type="submit" value="上传" /> </form> </body> </html>
后台
/// <summary> /// 导入EXCEL数据 /// </summary> /// <param name="filePath">文件路径</param> /// <returns></returns> public void ImportToDataTable(IFormFile excelfile) { DataTable dt = new DataTable(); var filePath = excelfile.FileName.Split('.'); if (filePath[1].ToLower()==".xls") {//.xls #region .xls文件处理:HSSFWorkbook HSSFWorkbook hssfworkbook; try { using (MemoryStream ms = new MemoryStream()) { excelfile.CopyTo(ms); ms.Seek(0, SeekOrigin.Begin); hssfworkbook = new HSSFWorkbook(ms); } ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); var maxRowIndex = 0; while (rows.MoveNext()) { IRow row = (HSSFRow)rows.Current; var r = row.Cells.Select(p => p.StringCellValue).ToList(); var _result = row.Cells.Any(t => !string.IsNullOrEmpty(t.StringCellValue)); if (_result) { maxRowIndex = row.RowNum; break; } } HSSFRow headerRow = (HSSFRow)sheet.GetRow(maxRowIndex);//取第二行 var _ColumnIndex = new List<int>(); //一行最后一个方格的编号 即总的列数 for (int j = 0; j < (headerRow.LastCellNum); j++) { //SET EVERY COLUMN NAME HSSFCell cell = (HSSFCell)headerRow.GetCell(j); if (!string.IsNullOrEmpty(cell.StringCellValue)) { dt.Columns.Add(cell.ToString()); _ColumnIndex.Add(cell.ColumnIndex); } } while (rows.MoveNext()) { IRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); if (row.RowNum <= headerRow.RowNum) continue; for (int i = 0; i < _ColumnIndex.Count; i++) { if (i >= dt.Columns.Count) { break; } ICell cell = row.GetCell(_ColumnIndex[i]); if ((i == 0) && cell == null)//每行第一个cell为空,break { break; } if (cell == null) { dr[i] = null; } else { switch (cell.CellType) { case CellType.String: dr[i] = cell.StringCellValue; break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(cell)) { dr[i] = cell.DateCellValue; } else { dr[i] = cell.NumericCellValue; } break; default: dr[i] = null; break; } } } dt.Rows.Add(dr); } } catch (Exception e) { throw new Exception(e.Message, e); } #endregion } else {//.xlsx #region .xlsx文件处理:XSSFWorkbook XSSFWorkbook hssfworkbook; try { using (MemoryStream ms = new MemoryStream()) { excelfile.CopyTo(ms); ms.Seek(0, SeekOrigin.Begin); hssfworkbook = new XSSFWorkbook(ms); } ISheet sheet = hssfworkbook.GetSheetAt(0); // ISheet sheet = hssfworkbook.GetSheet(sheetName); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); var maxRowIndex = 0; while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; var _result = row.Cells.Any(t => !string.IsNullOrEmpty(t.StringCellValue)); if (_result) { maxRowIndex = row.RowNum; break; } } XSSFRow headerRow = (XSSFRow)sheet.GetRow(maxRowIndex);//取第二行 var _ColumnIndex = new List<int>(); //一行最后一个方格的编号 即总的列数 for (int j = 0; j < (headerRow.LastCellNum); j++) { //SET EVERY COLUMN NAME XSSFCell cell = (XSSFCell)headerRow.GetCell(j); if (!string.IsNullOrEmpty(cell.StringCellValue)) { dt.Columns.Add(cell.ToString()); _ColumnIndex.Add(cell.ColumnIndex); } } while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); if (row.RowNum == 0) continue; for (int i = 0; i < _ColumnIndex.Count; i++) { if (i >= dt.Columns.Count) { break; } ICell cell = row.GetCell(_ColumnIndex[i]); if ((i == 0) && (cell == null))//每行第一个cell为空,break { break; } if (cell == null) { dr[i] = null; } else { switch (cell.CellType) { case CellType.String: dr[i] = cell.StringCellValue; break; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(cell)) { dr[i] = cell.DateCellValue; } else { dr[i] = cell.NumericCellValue; } break; default: dr[i] = null; break; } } } dt.Rows.Add(dr); } } catch (Exception e) { throw new Exception(e.Message, e); } #endregion } var a = dt.Rows.Count;//测试看是否有数据 }