asp.net core導出導入excel
- 2019 年 10 月 10 日
- 筆記
使用NPOI導入導出excel,已經封裝好Action可以直接調用
導出
效果圖
使用方法
定義導出實體
class Student { public int Id { get; set; } public string Name { get; set; } public bool IsBanZhang { get; set; } public static IEnumerable<Student> GetStudents() { return new List<Student> { new Student{Name="小強",Id=1,IsBanZhang=false}, new Student{Name="小文",Id=2,IsBanZhang=true}, new Student{Name="小黃",Id=3,IsBanZhang=false}, new Student{Name="小剛",Id=3,IsBanZhang=false}, }; } }
導出Action
public IActionResult DownExcel() { var list = Student.GetStudents(); var excelHeper = new ExcelHelper(); var config = new List<ExcelGridModel> { new ExcelGridModel{name="Id",label="學號", align="left",}, new ExcelGridModel{name="Name",label="姓名", align="left",}, new ExcelGridModel{name="IsBanZhang",label="是否班長", align="left",}, }; var fileName = "a.excel"; return excelHeper.ExcelDownload(list, config, fileName); }
主要程式碼
導入導出程式碼
/// <summary> /// 描 述：NPOI Excel DataTable操作類 /// </summary> public class ExcelHelper { #region Excel導出方法 ExcelDownload /// <summary> /// Excel導出下載 /// </summary> /// <typeparam name="T">數據類型</typeparam> /// <param name="list">數據源</param> /// <param name="columnJson">導出列</param> /// <param name="fileName">下載文件名稱</param> public ActionResult ExcelDownload<T>(IEnumerable<T> list, IEnumerable<ExcelGridModel> columnList, string fileName) { var excelConfig = ConvertExcelGridModelToConfig(columnList, fileName); var rowData = list.ToDataTable(columnList.Select(i => i.name)); var stream = ExportMemoryStream(rowData, excelConfig); return new FileStreamResult(stream, MIMEType.xls) { FileDownloadName = JointXls(fileName) }; } /// <summary> /// Excel導出下載 /// </summary> /// <param name="dtSource">DataTable數據源</param> /// <param name="excelConfig">導出設置包含文件名、標題、列設置</param> public ActionResult ExcelDownload(DataTable dtSource, IEnumerable<ExcelGridModel> columnList, string fileName) { var excelConfig = ConvertExcelGridModelToConfig(columnList, fileName); var stream = ExportMemoryStream(dtSource, excelConfig); return new FileStreamResult(stream, MIMEType.xls) { FileDownloadName = JointXls(fileName) }; } /// <summary> /// Excel導出下載 /// </summary> /// <param name="dtSource">DataTable數據源</param> /// <param name="excelConfig">導出設置包含文件名、標題、列設置</param> public ActionResult ExcelDownload(DataTable dtSource, ExcelConfig excelConfig, string fileName) { var stream = ExportMemoryStream(dtSource, excelConfig); return new FileStreamResult(stream, MIMEType.xls) { FileDownloadName = JointXls(fileName) }; } #endregion Excel導出方法 ExcelDownload #region DataTable導出到Excel文件excelConfig中FileName設置為全路徑 /// <summary> /// DataTable導出到Excel文件 Export() /// </summary> /// <param name="dtSource">DataTable數據源</param> /// <param name="excelConfig">導出設置包含文件名、標題、列設置</param> public string ExcelExportToFile(DataTable dtSource, ExcelConfig excelConfig, string fileName) { fileName = JointXls(fileName); using (MemoryStream ms = ExportMemoryStream(dtSource, excelConfig)) { using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } return fileName; } #endregion DataTable導出到Excel文件excelConfig中FileName設置為全路徑 #region DataTable導出到Excel的MemoryStream /// <summary> /// DataTable導出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable數據源</param> /// <param name="excelConfig">導出設置包含文件名、標題、列設置</param> public MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig) { var columnEntity = excelConfig.ColumnEntity; if (columnEntity == null || columnEntity.Count == 0) { if (columnEntity == null) { columnEntity = new List<ColumnModel>(); } foreach (DataColumn dc in dtSource.Columns) { columnEntity.Add(new ColumnModel { Alignment = "center", Column = dc.ColumnName, ExcelColumn = dc.ColumnName }); } } else { int colint = 0; for (int i = 0; i < dtSource.Columns.Count;) { DataColumn column = dtSource.Columns[i]; if (excelConfig.ColumnEntity[colint].Column != column.ColumnName) { dtSource.Columns.Remove(column.ColumnName); } else { i++; if (colint < excelConfig.ColumnEntity.Count - 1) { colint++; } } } } HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 右擊文件 屬性資訊 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = ""; //填加xls文件作者資訊 si.ApplicationName = ""; //填加xls文件創建程式資訊 si.LastAuthor = ""; //填加xls文件最後保存者資訊 si.Comments = ""; //填加xls文件作者資訊 si.Title = ""; //填加xls文件標題資訊 si.Subject = "";//填加文件主題資訊 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion 右擊文件 屬性資訊 #region 設置標題樣式 ICellStyle headStyle = workbook.CreateCellStyle(); int[] arrColWidth = new int[dtSource.Columns.Count]; string[] arrColName = new string[dtSource.Columns.Count];//列名 ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count];//樣式表 headStyle.Alignment = HorizontalAlignment.Center; // ------------------ if (excelConfig.Background != new Color()) { if (excelConfig.Background != new Color()) { headStyle.FillPattern = FillPattern.SolidForeground; headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background); } } IFont font = workbook.CreateFont(); font.FontHeightInPoints = excelConfig.TitlePoint; if (excelConfig.ForeColor != new Color()) { font.Color = GetXLColour(workbook, excelConfig.ForeColor); } font.Boldweight = 700; headStyle.SetFont(font); #endregion 設置標題樣式 #region 列頭及樣式 ICellStyle cHeadStyle = workbook.CreateCellStyle(); cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont cfont = workbook.CreateFont(); cfont.FontHeightInPoints = excelConfig.HeadPoint; cHeadStyle.SetFont(cfont); #endregion 列頭及樣式 #region 設置內容單元格樣式 foreach (DataColumn item in dtSource.Columns) { ICellStyle columnStyle = workbook.CreateCellStyle(); columnStyle.Alignment = HorizontalAlignment.Center; arrColWidth[item.Ordinal] = Encoding.UTF8.GetBytes(item.ColumnName.ToString()).Length; arrColName[item.Ordinal] = item.ColumnName.ToString(); if (excelConfig.ColumnEntity != null) { ColumnModel columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName); if (columnentity != null) { arrColName[item.Ordinal] = columnentity.ExcelColumn; if (columnentity.Width != 0) { arrColWidth[item.Ordinal] = columnentity.Width; } if (columnentity.Background != new Color()) { if (columnentity.Background != new Color()) { columnStyle.FillPattern = FillPattern.SolidForeground; columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background); } } if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color()) { IFont columnFont = workbook.CreateFont(); columnFont.FontHeightInPoints = 10; if (columnentity.Font != null) { columnFont.FontName = columnentity.Font; } if (columnentity.Point != 0) { columnFont.FontHeightInPoints = columnentity.Point; } if (columnentity.ForeColor != new Color()) { columnFont.Color = GetXLColour(workbook, columnentity.ForeColor); } columnStyle.SetFont(font); } columnStyle.Alignment = getAlignment(columnentity.Alignment); } } arryColumStyle[item.Ordinal] = columnStyle; } if (excelConfig.IsAllSizeColumn) { #region 根據列中最長列的長度取得列寬 for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { if (arrColWidth[j] != 0) { int intTemp = Encoding.UTF8.GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } } #endregion 根據列中最長列的長度取得列寬 } #endregion 設置內容單元格樣式 int rowIndex = 0; #region 表頭及樣式 if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(rowIndex); rowIndex++; if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------ } #endregion 表頭及樣式 #region 列頭及樣式 { IRow headerRow = sheet.CreateRow(rowIndex); rowIndex++; #region 如果設置了列標題就按列標題定義列頭，沒定義直接按欄位名輸出 foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]); headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle; //設置列寬 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } #endregion 如果設置了列標題就按列標題定義列頭，沒定義直接按欄位名輸出 } #endregion 列頭及樣式 ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表頭，填充列頭，樣式 if (rowIndex == 65535) { sheet = workbook.CreateSheet(); rowIndex = 0; #region 表頭及樣式 { if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(rowIndex); rowIndex++; if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------ } } #endregion 表頭及樣式 #region 列頭及樣式 { IRow headerRow = sheet.CreateRow(rowIndex); rowIndex++; #region 如果設置了列標題就按列標題定義列頭，沒定義直接按欄位名輸出 foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]); headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle; //設置列寬 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } #endregion 如果設置了列標題就按列標題定義列頭，沒定義直接按欄位名輸出 } #endregion 列頭及樣式 } #endregion 新建表，填充表頭，填充列頭，樣式 #region 填充內容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); newCell.CellStyle = arryColumStyle[column.Ordinal]; string drValue = row[column].ToString(); SetCell(newCell, dateStyle, column.DataType, drValue); } #endregion 填充內容 rowIndex++; } //using (MemoryStream ms = new MemoryStream()) { MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } } #endregion DataTable導出到Excel的MemoryStream #region 設置表格內容 private void SetCell(ICell newCell, ICellStyle dateStyle, Type dataType, string drValue) { switch (dataType.ToString()) { case "System.String"://字元串類型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期類型 System.DateTime dateV; if (System.DateTime.TryParse(drValue, out dateV)) { newCell.SetCellValue(dateV); } else { newCell.SetCellValue(""); } newCell.CellStyle = dateStyle;//格式化顯示 break; case "System.Boolean"://布爾型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮點型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值處理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion 設置表格內容 #region 從Excel導入 /// <summary> /// 讀取excel ,默認第一行為標頭 /// </summary> /// <param name="strFileName">excel文檔路徑</param> /// <returns></returns> public DataTable ExcelImport(string strFileName) { return ExcelImport(strFileName, 0); } /// <summary> /// 讀取excel ,默認第一行為標頭 /// </summary> /// <param name="strFileName">excel文檔路徑</param> /// <param name="headerRowNo">標題行號，以0開始</param> /// <returns></returns> public DataTable ExcelImport(string strFileName, int headerRowNo) { ISheet sheet; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (strFileName.IndexOf(".xlsx", StringComparison.Ordinal) == -1)//2003 { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); sheet = hssfworkbook.GetSheetAt(0); } else//2007 { XSSFWorkbook xssfworkbook = new XSSFWorkbook(file); sheet = xssfworkbook.GetSheetAt(0); } } return ReadSheetToDataTable(headerRowNo, sheet); } /// <summary> /// 讀取excel ,默認第一行為標頭 /// </summary> /// <param name="fileStream">文件數據流</param> /// <returns></returns> public DataTable ExcelImport(Stream fileStream, string flieType) { return ExcelImport(fileStream, flieType, 0); } /// <summary> /// 讀取excel ,默認第一行為標頭 /// </summary> /// <param name="fileStream">文件數據流</param> /// <param name="headerRowNo">標題行號從0開始</param> /// <returns></returns> public DataTable ExcelImport(Stream fileStream, string flieType, int headerRowNo) { DataTable dt = new DataTable(); ISheet sheet = null; if (flieType == ".xls") { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileStream); sheet = hssfworkbook.GetSheetAt(0); } else { XSSFWorkbook xssfworkbook = new XSSFWorkbook(fileStream); sheet = xssfworkbook.GetSheetAt(0); } return ReadSheetToDataTable(headerRowNo, sheet); } /// <summary> /// 從sheet中讀取數據到DataTable /// </summary> /// <param name="headerRowNo">標題行號（數據行號=標題行號+1）</param> /// <param name="sheet"></param> /// <returns></returns> private DataTable ReadSheetToDataTable(int headerRowNo, ISheet sheet) { var dt = new DataTable(); IRow headerRow = sheet.GetRow(headerRowNo); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (headerRowNo + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = 0; j < cellCount; j++) { if (row.GetCell(j) == null) { continue; } ICell cell = row.GetCell(j); if (cell.CellType == CellType.Error) { throw new Exception($"第{i + 1}行，列【{dt.Columns[j].ColumnName}】,單元格格式錯誤"); } else if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell)) { dataRow[j] = cell.DateCellValue; } else if (cell.CellType == CellType.Numeric) { dataRow[j] = cell.NumericCellValue; } else if (cell.CellType == CellType.Blank) { dataRow[j] = ""; } else { dataRow[j] = cell.StringCellValue; } //dataRow[j] = row.GetCell(j).ToString(); } bool existsValue = false; foreach (DataColumn column in dt.Columns) { if (dataRow[column.ColumnName] == null || string.IsNullOrEmpty(dataRow[column.ColumnName].ToString())) { continue; } existsValue = true; break; } if (existsValue) { dt.Rows.Add(dataRow); } } return dt; } #endregion 從Excel導入 #region RGB顏色轉NPOI顏色 private short GetXLColour(HSSFWorkbook workbook, Color SystemColour) { short s = 0; HSSFPalette XlPalette = workbook.GetCustomPalette(); NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B); if (XlColour == null) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255) { XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); s = XlColour.Indexed; } } else { s = XlColour.Indexed; } return s; } #endregion RGB顏色轉NPOI顏色 #region 設置列的對齊方式 /// <summary> /// 設置對齊方式 /// </summary> /// <param name="style"></param> /// <returns></returns> private HorizontalAlignment getAlignment(string style) { switch (style) { case "center": return HorizontalAlignment.Center; case "left": return HorizontalAlignment.Left; case "right": return HorizontalAlignment.Right; case "fill": return HorizontalAlignment.Fill; case "justify": return HorizontalAlignment.Justify; case "centerselection": return HorizontalAlignment.CenterSelection; case "distributed": return HorizontalAlignment.Distributed; } return NPOI.SS.UserModel.HorizontalAlignment.General; } #endregion 設置列的對齊方式 #region 輔助方法 /// <summary> /// 如果文件名中沒有後綴名，增加文件後綴名 /// </summary> /// <param name="fileName"></param> /// <returns></returns> private string JointXls(string fileName) { if (!fileName.EndsWith(".xls")) { fileName += ".xls"; } return fileName; } private ExcelConfig ConvertExcelGridModelToConfig(IEnumerable<ExcelGridModel> columnList, string fileName) { ExcelConfig excelconfig = new ExcelConfig(); excelconfig.Title = fileName; excelconfig.TitleFont = "微軟雅黑"; excelconfig.TitlePoint = 15; excelconfig.IsAllSizeColumn = true; excelconfig.ColumnEntity = new List<ColumnModel>(); foreach (ExcelGridModel columnModel in columnList) { excelconfig.ColumnEntity.Add(new ColumnModel() { Column = columnModel.name, ExcelColumn = columnModel.label, Alignment = columnModel.align, }); } return excelconfig; } /// <summary> /// MIME文件類型 /// </summary> class MIMEType { public const string xls = "application/ms-excel"; } #endregion }
配置類型
ExcelConfig
/// <summary> /// 描 述：Excel導入導出設置 /// </summary> public class ExcelConfig { /// <summary> /// 標題 /// </summary> public string Title { get; set; } /// <summary> /// 前景色 /// </summary> public Color ForeColor { get; set; } /// <summary> /// 背景色 /// </summary> public Color Background { get; set; } private short _titlepoint; /// <summary> /// 標題字型大小 /// </summary> public short TitlePoint { get { if (_titlepoint == 0) { return 20; } else { return _titlepoint; } } set { _titlepoint = value; } } private short _headpoint; /// <summary> /// 列頭字型大小 /// </summary> public short HeadPoint { get { if (_headpoint == 0) { return 10; } else { return _headpoint; } } set { _headpoint = value; } } /// <summary> /// 標題高度 /// </summary> public short TitleHeight { get; set; } /// <summary> /// 列標題高度 /// </summary> public short HeadHeight { get; set; } private string _titlefont; /// <summary> /// 標題字體 /// </summary> public string TitleFont { get { if (_titlefont == null) { return "微軟雅黑"; } else { return _titlefont; } } set { _titlefont = value; } } private string _headfont; /// <summary> /// 列頭字體 /// </summary> public string HeadFont { get { if (_headfont == null) { return "微軟雅黑"; } else { return _headfont; } } set { _headfont = value; } } /// <summary> /// 是否按內容長度來適應表格寬度 /// </summary> public bool IsAllSizeColumn { get; set; } /// <summary> /// 列設置 /// </summary> public List<ColumnModel> ColumnEntity { get; set; } }
ColumnModel
/// <summary> /// 描 述：Excel導入導出列設置模型 /// </summary> public class ColumnModel { /// <summary> /// 列名 /// </summary> public string Column { get; set; } /// <summary> /// Excel列名 /// </summary> public string ExcelColumn { get; set; } /// <summary> /// 寬度 /// </summary> public int Width { get; set; } /// <summary> /// 前景色 /// </summary> public Color ForeColor { get; set; } /// <summary> /// 背景色 /// </summary> public Color Background { get; set; } /// <summary> /// 字體 /// </summary> public string Font { get; set; } /// <summary> /// 字型大小 /// </summary> public short Point { get; set; } /// <summary> ///對齊方式 ///left 左 ///center 中間 ///right 右 ///fill 填充 ///justify 兩端對齊 ///centerselection 跨行居中 ///distributed /// </summary> public string Alignment { get; set; } }
ExcelGridModel
public class ExcelGridModel { /// <summary> /// 屬性名稱 /// </summary> public string name { get; set; } /// <summary> /// excel列名 /// </summary> public string label { get; set; } /// <summary> /// 寬度 /// </summary> public string width { get; set; } /// <summary> /// 對其方式 /// </summary> public string align { get; set; } /// <summary> /// 高度 /// </summary> public string height { get; set; } /// <summary> /// 是否隱藏 /// </summary> public string hidden { get; set; } }
示例下載地址： 程式碼打包下載
