/// <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);
}
}
}
