/// <summary> /// 透過DataTable取得Excel二進位資料 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="param"></param> public static byte[] DataTableExportBinary <T>(NpoiParam <T> param) { try { //依情況決定要建新的 Sheet 或是用舊的 (即來自範本) 主要由SheetName來決定有幾個Sheet ISheet[] sheets = GetSheet(param); for (int i = 0; i < sheets.Length; i++) { if (param.DataTable.Rows.Count != 0) { DataTableSetSheetValue(ref param, ref sheets[i], i); } else { // 若沒資料在起點寫入No Data ! sheets[i].CreateRow(0); sheets[i].GetRow(0).CreateCell(0).SetCellValue("No Data !"); } } MemoryStream MS = new MemoryStream(); param.Workbook.Write(MS); return(MS.ToArray()); } catch (Exception ex) { GetCustomErrorCodeDescription(ex); return(null); } }
/// <summary> /// 透過DataTable 匯出Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="param"></param> public static void DataTableExportExcel <T>(NpoiParam <T> param) { try { //依情況決定要建新的 Sheet 或是用舊的 (即來自範本) 主要由SheetName來決定有幾個Sheet ISheet[] sheets = GetSheet(param); for (int i = 0; i < sheets.Length; i++) { if (param.DataTable.Rows.Count != 0) { DataTableSetSheetValue(ref param, ref sheets[i], i); } else { // 若沒資料在起點寫入No Data ! sheets[i].CreateRow(0); sheets[i].GetRow(0).CreateCell(0).SetCellValue("No Data !"); } } Export(param.Workbook, param.FileFullName); } catch (Exception ex) { GetCustomErrorCodeDescription(ex); } }
private static NpoiParam <Sample> GetParam(List <Sample> data) { var param = new NpoiParam <Sample> { Workbook = new XSSFWorkbook(), //要用一個新的或是用你自己的範本 Data = data, //資料 FileFullName = @"D:\result.xlsx", //Excel檔要存在哪 SheetName = "Data", //Sheet要叫什麼名子 ColumnMapping = new List <ColumnMapping> //欄位對應 (處理Excel欄名、格式轉換) { new ColumnMapping { ModelFieldName = "Id", ExcelColumnName = "流水號", DataType = NpoiDataType.Number, Format = "0.00" }, new ColumnMapping { ModelFieldName = "Name", ExcelColumnName = "名子", DataType = NpoiDataType.String }, new ColumnMapping { ModelFieldName = "Birthday", ExcelColumnName = "生日", DataType = NpoiDataType.Date, Format = "yyyy-MM-dd" }, }, FontStyle = new FontStyle //是否需自定Excel字型大小 { FontName = "Calibri", FontHeightInPoints = 11, }, ShowHeader = true, //是否畫表頭 IsAutoFit = true, //是否啟用自動欄寬 }; return(param); }
/// <summary> /// DataSetExportExcel 使用參數設定 /// </summary> /// <param name="data"></param> /// <returns></returns> public static NpoiParam <Sample> GetDataSetParam(DataSet data) { var param = new NpoiParam <Sample> { Workbook = new XSSFWorkbook(), //Workbook = new HSSFWorkbook(), //要用一個新的或是用你自己的範本 DataSet = data, //資料 FileFullName = @"C:\Users\011714\Desktop\result1.xlsx", //Excel檔要存在哪 //FileFullName = @"C:\Users\011714\Desktop\result.xls", SheetName = new string[] { "data", "data1" }, //Sheet要叫什麼名子 ColumnMapping = new List <ColumnMapping>[] //欄位對應 (處理Excel欄名、格式轉換) { new List <ColumnMapping>() { new ColumnMapping { ModelFieldName = "Id", ExcelColumnName = "流水號", DataType = NpoiDataType.String }, new ColumnMapping { ModelFieldName = "Name", ExcelColumnName = "名子", DataType = NpoiDataType.String }, new ColumnMapping { ModelFieldName = "Birthday", ExcelColumnName = "生日", DataType = NpoiDataType.DateTime, Format = "yyyy-MM-dd" } }, new List <ColumnMapping>() { new ColumnMapping { ModelFieldName = "Id", ExcelColumnName = "流水號", DataType = NpoiDataType.String }, new ColumnMapping { ModelFieldName = "Name", ExcelColumnName = "名子", DataType = NpoiDataType.String }, new ColumnMapping { ModelFieldName = "Birthday", ExcelColumnName = "生日", DataType = NpoiDataType.DateTime, Format = "yyyy-MM-dd" } } }, HeaderFontStyle = new FontStyle //是否需自定Excel字型大小 { FontName = "Calibri", FontHeightInPoints = 11, }, DataFontStyle = new FontStyle { FontName = "新細明體", FontHeightInPoints = 10, }, ShowHeader = true, //是否畫表頭 IsAutoFit = true, //是否啟用自動欄寬 }; return(param); }
private static ISheet GetSheet <T>(NpoiParam <T> param) { //在 workbook 中以 sheet name 尋找 是否找得到sheet if (param.Workbook.GetSheet(param.SheetName) == null) { //找不到建一張新的sheet ISheet sht = param.Workbook.CreateSheet(param.SheetName); sht = CreateColumn(param); return(sht); } else { //找得到即為要塞值的目標 return(param.Workbook.GetSheet(param.SheetName)); } }
/// <summary> /// 創造 Excel 檔 /// </summary> public static void ExportExcel <T>(NpoiParam <T> p) { //依情況決定要建新的 Sheet 或是用舊的 (即來自範本) ISheet sht = GetSheet(p); if (p.Data.Any()) { //有資料塞格子 SetSheetValue(ref p, ref sht); } else { //若沒資料在起點寫入No Data ! CreateNewRowOrNot(ref sht, p.RowStartFrom, p.ColumnStartFrom); sht.GetRow(p.RowStartFrom).CreateCell(p.ColumnStartFrom).SetCellValue("No Data !"); } Flush(p.Workbook, p.FileFullName); }
private static void SetSheetValue <T>(ref NpoiParam <T> p, ref ISheet sht) { //要從哪一行開始塞資料 (有可能自定範本 可能你原本範本內就有好幾行表頭 2行 3行...) int line = p.RowStartFrom; //有可能前面幾欄是自訂好的 得跳過幾個欄位再開始塞 int columnOffset = p.ColumnStartFrom; //根據標題列先處理所有Style (對Npoi來說 '創建'Style在workbook中是很慢的操作 作越少次越好 絕對不要foreach在塞每行列實際資料時重覆作 只通通在標題列做一次就好) ICellStyle[] cellStyleArr = InitialColumnStyle(p.Workbook, p.ColumnMapping, p.FontStyle); foreach (var item in p.Data) { //如果 x 軸有偏移值 則表示這行他已經自己建了某幾欄的資料 我們只負責塞後面幾欄 所以並非每次都create new row CreateNewRowOrNot(ref sht, line, columnOffset); for (int i = 0; i < p.ColumnMapping.Count; i++) { //建立格子 (需考量 x 軸有偏移值) var cell = sht.GetRow(line).CreateCell(i + columnOffset); //綁定style (記得 綁定是不慢的 但建新style是慢的 不要在迴圈裡無意義的反覆建style 只在標題處理一次即可) cell.CellStyle = cellStyleArr[i]; //給值 string value = GetValue(item, p.ColumnMapping, i); //reflection取值 SetCellValue(value, ref cell, p.ColumnMapping[i].DataType); //幫cell填值 } line++; } //處理AutoFit (必定是在最後做的 因為你得把所有格子都塞完以後才知道每欄多寬是你需要的) if (p.IsAutoFit) { for (int i = 0; i < p.ColumnMapping.Count; i++) { sht.AutoSizeColumn(i); } } }
/// <summary> /// 取得Sheet /// </summary> /// <typeparam name="T"></typeparam> /// <param name="param"></param> /// <returns></returns> private static ISheet[] GetSheet <T>(NpoiParam <T> param) { ISheet[] sheets = new ISheet[param.SheetName.Length]; for (int i = 0; i < param.SheetName.Length; i++) { // 在 workbook 中以 sheet name 尋找 是否找得到sheet if (param.Workbook.GetSheet(param.SheetName[i]) == null) { ISheet sheetTmp = param.Workbook.CreateSheet(param.SheetName[i]); sheetTmp = CreateColumn(param, i); sheets[i] = sheetTmp; } else { sheets[i] = param.Workbook.GetSheet(param.SheetName[i]); // 找得到即為要塞值的目標 } } return(sheets); }
private static ISheet CreateColumn <T>(NpoiParam <T> p) { var sht = p.Workbook.GetSheet(p.SheetName); sht.CreateRow(0); ICellStyle columnStyle = GetBaseCellStyle(p.Workbook, p.FontStyle); if (p.ShowHeader) { for (int i = 0; i < p.ColumnMapping.Count; i++) { var offset = i + p.ColumnStartFrom; sht.GetRow(0).CreateCell(offset); //先創建格子 sht.GetRow(0).GetCell(offset).CellStyle = columnStyle; //綁定基本格式 sht.GetRow(0).GetCell(offset).SetCellValue(p.ColumnMapping[i].ExcelColumnName); //給值 } } return(sht); }
/// <summary> /// 建立表頭內容 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="param"></param> /// <param name="sheetIndex"></param> /// <returns></returns> private static ISheet CreateColumn <T>(NpoiParam <T> param, int sheetIndex) { var sheet = param.Workbook.GetSheet(param.SheetName[sheetIndex]); // 建立表頭Row sheet.CreateRow(0); ICellStyle columnStyle = GetBaseCellStyle(param.Workbook, param.HeaderFontStyle); if (param.ShowHeader) { for (int j = 0; j < param.ColumnMapping[sheetIndex].Count; j++) { // 建立欄位 sheet.GetRow(0).CreateCell(j); // 設定欄位Style sheet.GetRow(0).GetCell(j).CellStyle = columnStyle; // 給欄位值 sheet.GetRow(0).GetCell(j).SetCellValue(param.ColumnMapping[sheetIndex][j].ExcelColumnName); } } return(sheet); }
/// <summary> /// 設定sheet裡面的資料 /// DataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="param"></param> /// <param name="sheet"></param> /// <param name="sheetIndex"></param> private static void DataTableSetSheetValue <T>(ref NpoiParam <T> param, ref ISheet sheet, int sheetIndex) { int line = 1; //根據標題列先處理所有Style (對Npoi來說 '創建'Style在workbook中是很慢的操作 作越少次越好 絕對不要foreach在塞每行列實際資料時重覆作 只通通在標題列做一次就好) ICellStyle[] cellStyleArr = InitialColumnStyle(param.Workbook, param.ColumnMapping, param.DataFontStyle, sheetIndex); var table = param.DataTable; for (int i = 0; i < table.Rows.Count; i++) { sheet.CreateRow(line); for (int j = 0; j < param.ColumnMapping[sheetIndex].Count; j++) { // 建立欄位 var cell = sheet.GetRow(line).CreateCell(j); // 綁定欄位Style cell.CellStyle = cellStyleArr[i]; // 給欄位值 reflection取值 string value = table.Rows[i][j].ToString(); // 幫cell填值 SetCellValue(value, ref cell, param.ColumnMapping[sheetIndex][i].DataType); } line++; } if (param.IsAutoFit) { for (int i = 0; i < param.ColumnMapping[sheetIndex].Count; i++) { sheet.AutoSizeColumn(i); } } }