NPOI是很老牌的可以讓你產出xls與xlsx的元件
但可能就是因為他太老牌了 許多操作應用上並不便利
通常程式碼中只要用了NPOI 都會看起來落落長很醜
很像.net 以前 大家寫 webform 用 datatable + gridview 在塞資料的那種味道
程式碼中 會有超多創建格子、給格式、給值.......幾百行、千行無限循環
跟現在大家喜歡寫程式習慣並不太符合
所以今天我們試著來包裝NPOI讓他變的更易用些
完整程式碼詳見 https://gitlab.com/jesperlai/NpoiHelper
當然先看看最後我們希望怎麼叫用
假設我要輸出的資料長這樣
class Sample
{
public string Id { get; set; } //故意在這裡定義為string 但輸出到Excel中需轉為數字
public string Name { get; set; }
public DateTime Birthday { get; set; }
}
static void Main(string[] args)
{
var data = new List<Sample>
{
new Sample { Id = "1", Name = "A", Birthday = DateTime.Parse("2001/1/1 12:00") },
new Sample { Id = "2", Name = "B", Birthday = DateTime.Parse("2001/1/2 12:00") },
new Sample { Id = "3", Name = "C", Birthday = DateTime.Parse("2001/1/3 12:00") },
};
}
而希望Excel的結果長這樣
1. 原本流水號這個欄位是文字，但在輸出時我希望他變成數字且格式化為0.00
2. 原本生日是有時分秒的，但在輸出時我希望只有年-月-日
叫用的程式碼如下
var param = GetParam(data);
NpoiHelper.ExportExcel(param);
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;
}
如果你有試著自己使用過NPOI 你看到這裡應該會理解
這與傳統作法 每支程式各自在繞迴圈 [0] [1] [2]... 給值給格式的程式碼看起來乾淨許多
當然我同事的需求不僅於此 他們還有許多更特別的需求
有天有人問我 可不可以我自訂好一些表頭或側欄 你只幫我在中間塞資料進去就好
例如我自己做好一個範本
中間框起來的地方才是我資料要放的地方
也就是結果要像這樣
如此一來他的參數就會變得像
private static NpoiParam<Sample> GetParam(List<Sample> data)
{
var param = new NpoiParam<Sample>
{
Workbook = new XSSFWorkbook(@"D:\template.xlsx"), //要用一個新的或是用你自己的範本
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"},
},
ShowHeader = false, //是否畫表頭
ColumnStartFrom = 2,
RowStartFrom = 1,
};
return param;
}
那最後就來看一下程式碼
首先當然別忘了到Nuget安裝NPOI
Model相關
using NPOI.SS.UserModel;
using System.Collections.Generic;
namespace MySample.Npoi
{
public enum NpoiDataType
{
String,
Number,
Date
}
public class FontStyle
{
public string FontName { get; set; }
public short? FontHeightInPoints { get; set; }
public FontStyle()
{
FontName = null;
FontHeightInPoints = null;
}
}
public class ColumnMapping
{
public string ModelFieldName { get; set; }
/// <summary>
/// 若以範本初始化 Excel 則此欄可不填
/// </summary>
public string ExcelColumnName { get; set; }
public NpoiDataType DataType { get; set; }
/// <summary>
/// 如果是 String 則這個欄位不生效
/// </summary>
public string Format { get; set; }
}
public class NpoiParam<T>
{
private int? _RowStartFrom;
private int? _ColumnStartFrom;
private bool? _ShowHeader;
private bool? _IsAutoFit;
private FontStyle _fontStyle;
/// <summary>
/// 請用 HSSFWorkbook 或 XSSFWorkbook 實體化 IWorkbook
/// </summary>
public IWorkbook Workbook { get; set; }
/// <summary>
/// 最後excel檔要被寫出到哪裡
/// </summary>
public string FileFullName { get; set; }
/// <summary>
/// 資料
/// </summary>
public List<T> Data { get; set; }
/// <summary>
/// 欲新增(或已存在)的 Sheet Name
/// </summary>
public string SheetName { get; set; }
/// <summary>
/// 與 Excel 檔間的欄位對應
/// </summary>
public List<ColumnMapping> ColumnMapping { get; set; }
/// <summary>
/// 預設從第 1 行開始塞資料 ( 第 0 行為標題欄位 )
/// </summary>
public int RowStartFrom
{
get { return _RowStartFrom ?? 1; }
set { _RowStartFrom = value; }
}
/// <summary>
/// 預設從第 0 欄開始塞資料
/// </summary>
public int ColumnStartFrom
{
get { return _ColumnStartFrom ?? 0; }
set { _ColumnStartFrom = value; }
}
/// <summary>
/// 是否excel要畫表頭 (預設畫表頭 = true)
/// </summary>
public bool ShowHeader
{
get { return _ShowHeader ?? true; }
set { _ShowHeader = value; }
}
/// <summary>
/// 是否自動調整欄寬 (預設不自動調整欄寬 = false)
/// </summary>
public bool IsAutoFit
{
get { return _IsAutoFit ?? false; }
set { _IsAutoFit = value; }
}
/// <summary>
/// 自己決定文字預設格式
/// </summary>
public FontStyle FontStyle
{
get { return _fontStyle ?? new FontStyle(); }
set { _fontStyle = value; }
}
}
}
NpoiHelper
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
namespace MySample.Npoi
{
public static class NpoiHelper
{
/// <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 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);
}
}
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;
}
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);
}
}
}
private static ICellStyle[] InitialColumnStyle(IWorkbook wb, List<ColumnMapping> columnMapping, FontStyle fontStyle)
{
ICellStyle[] styleArr = new ICellStyle[columnMapping.Count];
for (int i = 0; i < columnMapping.Count; i++)
{
//取通用格式
ICellStyle cellStyle = GetBaseCellStyle(wb, fontStyle);
//處理格式輸出
if (!String.IsNullOrWhiteSpace(columnMapping[i].Format))
{
cellStyle.DataFormat = GetCellFormat(wb, columnMapping[i].Format);
}
styleArr[i] = cellStyle;
}
return styleArr;
}
private static void CreateNewRowOrNot(ref ISheet sht, int line, int columnOffset)
{
//如果是從自定範本來則不能重畫格子 例如他給我範本 只要我畫後面三格 前兩格他自己做好了 如果我整行重畫 他自己畫的兩格也會消失
if (columnOffset == 0 || line > sht.LastRowNum)
{
sht.CreateRow(line);
}
}
private static void SetCellValue(string value, ref ICell cell, NpoiDataType type)
{
switch (type)
{
//字串沒有格式
case NpoiDataType.String:
if (!String.IsNullOrWhiteSpace(value)) cell.SetCellValue(value);
break;
//轉日期
case NpoiDataType.Date:
if (!String.IsNullOrWhiteSpace(value)) cell.SetCellValue(Convert.ToDateTime(value));
break;
//轉數字
case NpoiDataType.Number:
if (!String.IsNullOrWhiteSpace(value)) cell.SetCellValue(Convert.ToDouble(value));
break;
//不會發生;
default:
break;
}
}
private static ICellStyle GetBaseCellStyle(IWorkbook wb, FontStyle fontStyle)
{
//畫線
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
//預設字型大小
IFont font1 = wb.CreateFont();
font1.FontName = (fontStyle.FontName == null) ? "Arial" : fontStyle.FontName;
font1.FontHeightInPoints = (fontStyle.FontHeightInPoints == null) ? (short)10 : fontStyle.FontHeightInPoints.Value;
cellStyle.SetFont(font1);
return cellStyle;
}
private static short GetCellFormat(IWorkbook wb, string formatStr)
{
IDataFormat dataFormat = wb.CreateDataFormat();
return dataFormat.GetFormat(formatStr);
}
private static string GetValue<T>(T obj, List<ColumnMapping> columnMapping, int order)
{
var fieldName = columnMapping[order].ModelFieldName;
var prop = typeof(T).GetProperties().Where(q => q.Name == fieldName).First();
var value = prop.GetValue(obj, null);
return (value == null) ? "" : value.ToString();
}
private static void Flush(IWorkbook wb, string fullName)
{
//若檔案已存在先刪除
if (File.Exists(fullName)) File.Delete(fullName);
using (FileStream targetFs = File.Create(fullName))
{
wb.Write(targetFs);
wb = null;
}
}
}
}
使用範例
using MySample.Console.Models;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System;
namespace MySample.Console
{
class Program
{
class Sample
{
public string Id { get; set; } //故意在這裡定義為string 但輸出到Excel中需轉為數字
public string Name { get; set; }
public DateTime Birthday { get; set; }
}
static void Main(string[] args)
{
var data = new List<Sample>
{
new Sample { Id = "1", Name = "A", Birthday = DateTime.Parse("2001/1/1 12:00") },
new Sample { Id = "2", Name = "B", Birthday = DateTime.Parse("2001/1/2 12:00") },
new Sample { Id = "3", Name = "C", Birthday = DateTime.Parse("2001/1/3 12:00") },
};
var param = GetParam(data);
NpoiHelper.ExportExcel(param);
}
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;
}
}
}