public class ImportExportHelper { /// <summary> /// Excel to DataTable /// </summary> /// <param name="content">内容流</param> /// <param name="fileName">文件名</param> /// <param name="startRow">表头开始行(确定列和数据开始)</param> /// <param name="title">表头第一列(确定列和数据开始)</param> /// <returns></returns> public static DataTable ExcelToDataTable(Stream content, string fileName, int startRow = 0, string title = "") { DataTable dataTable = null; DataColumn column = null; DataRow dataRow = null; IWorkbook workbook = null; ISheet sheet = null; IRow row = null; ICell cell = null; try { // 2007版本 if (fileName.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(content); // 2003版本 else if (fileName.IndexOf(".xls") > 0) workbook = new HSSFWorkbook(content); else return null; if (workbook != null) { sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet dataTable = new DataTable(); if (sheet != null) { int rowCount = sheet.LastRowNum;//总行数 if (startRow < 1 && !string.IsNullOrWhiteSpace(title)) { //找到数据行title所在的行索引 for (int i = startRow + 1; i <= rowCount; ++i) { row = sheet.GetRow(i); if (row != null) { cell = row.GetCell(0); if (cell != null) { if (cell.StringCellValue == title) { startRow = i; break; } } } } } if (startRow == 0) { return null; } if (rowCount > 0) { IRow firstRow = sheet.GetRow(startRow);//数据表的第一行 int cellCount = firstRow.LastCellNum;//列数 //构建datatable的列 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); } } } //填充行 for (int i = startRow + 1; 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 { 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; } } } List<object> listCol = dataRow.ItemArray.ToList(); if (listCol.Any(p => Convert.ToString(p).Trim() != "")) { dataTable.Rows.Add(dataRow); } } } } } return dataTable; } catch (Exception ex) { return null; } } /// <summary> /// DataTable To Excel /// </summary> /// <param name="data"></param> /// <param name="sheetName"></param> /// <param name="isColumnWritten"></param> /// <returns></returns> public static Stream DataTableToExcel(DataTable data, string sheetName = "Sheet1", bool isColumnWritten = true) { int i = 0; int j = 0; int count = 0; IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = null; try { if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } if (isColumnWritten) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } var ms = new NPOIMemoryStream(); ms.AllowClose = false; workbook.Write(ms); //写入到stream ms.Flush(); ms.Seek(0, SeekOrigin.Begin); ms.AllowClose = true; return ms; } catch (Exception ex) { return null; } } /// <summary> /// 保存DataTable为Execl文件到硬盘 /// </summary> /// <param name="dt"></param> /// <param name="directory"></param> /// <returns></returns> public static string SaveExcelFile(DataTable dt, string directory = null) { string excelName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_" + Guid.NewGuid() + ".xlsx"; string path = DateTime.Now.Year + "/" + DateTime.Now.Month + "/"; if (string.IsNullOrEmpty(directory)) { path = $"~/DownloadFile/{path}"; } else { path = $"{directory}/{path}"; } if (!System.IO.Directory.Exists(path)) { System.IO.Directory.CreateDirectory(path); } path += excelName; var file = DataTableToExcel(dt); using (FileStream fs = new FileStream(path, FileMode.Create))//System.IO.File.Create(filefullPath) { file.CopyTo(fs); fs.Flush(); } return path; } } public class NPOIMemoryStream : MemoryStream { public NPOIMemoryStream() { AllowClose = true; } public bool AllowClose { get; set; } public override void Close() { if (AllowClose) base.Close(); } }