.Net下C#針對Excel開發控制元件彙總(ClosedXML,EPPlus,NPOI)

bubsky發表於2019-03-25
C#Excel控制元件XML

最近專案中需要一個匯出Excel報告的功能，假期搜了一下，把其中比較主流的列一下，僅供參考。

功能需求：

  1. 建立並寫入.xlsx Excel2007+版本的電子表格檔案
  2. 不需要office元件支援，終端電腦無需安裝ms office
  3. 簡單的format，style,chart和formula支援（不用過於複雜），並且能夠插入圖片
  4. 速度，保證資料在萬行以上表格寫入速度

效果圖：  

 

一、ClosedXML

主頁：https://github.com/ClosedXML/ClosedXML

需要引用OpenXMLSDK（DocumentFormat.OpenXml.dll），以簡易物件導向的方式操作檔案（類似Visual Basic for Applications (VBA)），文件和例子都比較完善

//建立workbook
using (var wb = new XLWorkbook(XLEventTracking.Disabled))
{
	//設定預設Style
	var style = wb.Style;
	style.Font.FontName = "Microsoft YaHei";
	style.Font.FontSize = 11;

	//新增Sheets
	var ws = wb.Worksheets.Add("Sheet001");
	wb.Worksheets.Add("Sheet002");

	//手動cell賦值
	ws.Cell(1, 1).Value = "Project";
	ws.Cell(1, 2).Value = "Project001";
	ws.Cell("A2").Value = "User";
	ws.Cell("B2").Value = "User001";
	ws.Cell(3, 1).SetValue("Create Date");
	ws.Cell(3, 2).SetValue(DateTime.Now);

	//加重第一列文字
	var rngHeader = ws.Range(1, 1, 3, 1);
	rngHeader.Style
		.Font.SetBold()
		.Font.SetFontColor(XLColor.White)
		.Fill.SetBackgroundColor(XLColor.SkyBlue)
		.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

	//合併cell
	ws.Cell(5, 1).Value = "Data List";
	var rngTitle = ws.Range(5, 1, 5, 5);
	rngTitle.Merge();//ws1.Row(5).Merge();
	rngTitle.Style
		.Font.SetBold()
		.Font.SetFontSize(15)
		.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
   
	//插入表格或資料,設定Timespan format
	var fakeData = Enumerable.Range(1, 5)
		.Select(x => new FakeData
		{
			Time = TimeSpan.FromSeconds(x * 123.667),
			X = x,
			Y = -x,
			Address = "a" + x,
			Distance = x * 100
		}).ToArray();

	var table = ws.Cell(6, 1).InsertTable(fakeData);
	table.Style.Font.FontSize = 9;
	var data = ws.Cell(13, 1).InsertData(fakeData);
	data.Style.Font.FontSize = 9;
	ws.Range(7, 1, 18, 1).Style.DateFormat.Format = "HH:mm:ss.000";

	//插入圖片
	var image = ws.AddPicture("1.png");
	image.MoveTo(ws.Cell(19, 1).Address);
	image.Scale(0.3);
	//調整列距
	ws.Columns().AdjustToContents();//會花費寫入資料一倍的時間
	//儲存檔案
	wb.SaveAs("ClosedXML.xlsx");
}

二、EPPlus

主頁：https://github.com/JanKallman/EPPlus/

EPPlus不需要任何別的引用，文件和例子還算比較全

//建立workbook
using (var p = new ExcelPackage())
{
	//新增Sheets
	var ws=  p.Workbook.Worksheets.Add("Sheet001");
	p.Workbook.Worksheets.Add("Sheet002");

	//手動cell賦值
	ws.Cells[1,1].Value = "Project";
	ws.Cells[1, 2].Value = "Project001";
	ws.Cells["A2"].Value = "User";
	ws.Cells["B2"].Value = "User001";
	ws.Cells[3,1].Value = "Create Date";
	ws.Cells[3,2].Value = DateTime.Now;
	ws.Cells[3, 2].Style.Numberformat.Format = "YYYY/MM/DD";

	//加重第一列文字
	var rngHeader = ws.Cells[1, 1, 3, 1];
	rngHeader.Style.Font.Bold = true;
	rngHeader.Style.Font.Color.SetColor(System.Drawing.Color.White);
	rngHeader.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
	rngHeader.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DodgerBlue);
	rngHeader.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

	//合併cell
	ws.Cells[5, 1].Value = "Data List";
	var rngTitle = ws.Cells[5, 1, 5, 5];
	rngTitle.Merge = true;
	rngTitle.Style.Font.Size = 15;
	rngTitle.Style.Font.Bold = true;
	rngTitle.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;


	//插入表格或資料,設定Timespan format
	var fakeData = Enumerable.Range(1, 5)
		.Select(x => new FakeData
		{
			Time = TimeSpan.FromSeconds(x * 123.667),
			X = x,
			Y = -x,
			Address = "a" + x,
			Distance = x * 100
		}).ToArray();

	ws.Cells[6, 1].LoadFromCollection(fakeData, true, OfficeOpenXml.Table.TableStyles.Medium27);
	ws.Cells[13, 1].LoadFromArrays(
		fakeData.Select(x => new object[] {x.Time, x.X, x.Y, x.Address, x.Distance}));
	ws.Cells[6, 1, 18, 1].Style.Numberformat.Format = "HH:mm:ss.000";

	//插入圖片
	var image = ws.Drawings.AddPicture("picture", new FileInfo("1.png"));
	image.From.Row = 19;
	image.From.Column = 0;
	image.SetSize(30);
	//設定預設Style
	ws.Cells[ws.Dimension.Address].Style.Font.Name = "Microsoft YaHei";
	//調整列距
	ws.Cells.AutoFitColumns(0);//會花費寫入資料一倍的時間
	//儲存檔案
	p.SaveAs(new FileInfo("EPPlus.xlsx"));
}

三、NPOI

官網：https://github.com/tonyqus/npoi  .netcore version:https://github.com/dotnetcore/NPOI

需要引用SharpZipLib，可以讀寫Word和Excel，例子比較全，系統點的文件沒有找到，不過是國人的開源專案，百度應該能找到很多

參考：

http://blog.csdn.net/pan_junbiao/article/details/39717443   

http://www.cnblogs.com/yinrq/p/5590970.html 

http://www.cnblogs.com/hanzhaoxin/p/4232572.html 基於NPIO的Report控制元件

using (var fs = new FileStream("NPOI.xlsx", FileMode.Create, FileAccess.Write))
{
	//建立workbook
	IWorkbook wb = new XSSFWorkbook();

	//新增Sheets

	var ws = wb.CreateSheet("Sheet001");
	wb.CreateSheet("Sheet002");

	//手動cell賦值
	ws.CreateRow(0).CreateCell(0).SetCellValue("Project");
	ws.CreateRow(0).CreateCell(1).SetCellValue("Project001");
	ws.CreateRow(1).CreateCell(0).SetCellValue("User");
	ws.CreateRow(1).CreateCell(1).SetCellValue("User001");
	ws.CreateRow(2).CreateCell(0).SetCellValue("Create Date");
	ws.CreateRow(2).CreateCell(1).SetCellValue(DateTime.Now);

	wb.Write(fs);
}

四、Benchmarks

以上三個控制元件的簡單測試，10000條資料寫入

using (var wb = new XLWorkbook(XLEventTracking.Disabled))
{
	var ws = wb.AddWorksheet("1");
	ws.Column(1).Style.DateFormat.Format = "HH:mm:ss.000";
	int rowCount = 1;
	foreach (var fakeData in data)
	{
		rowCount++;
		ws.Cell(rowCount, 1).Value = fakeData.Time;
		ws.Cell(rowCount, 2).Value = fakeData.X;
		ws.Cell(rowCount, 3).Value = fakeData.Distance;
		ws.Cell(rowCount, 4).Value = fakeData.Address;
	}
	wb.SaveAs("ClosedXML.xlsx");
}

using (var wb = new ExcelPackage())
{
	var ws = wb.Workbook.Worksheets.Add("1");
	ws.Column(1).Style.Numberformat.Format = "HH:mm:ss.000";
	ws.Cells[1, 1].LoadFromCollection(data,true,
		OfficeOpenXml.Table.TableStyles.Medium2,
		System.Reflection.BindingFlags.Public|System.Reflection.BindingFlags.Instance,
		new System.Reflection.MemberInfo[]
		{
			typeof(FakeData).GetProperty("Time"),
			typeof(FakeData).GetProperty("X"),
			typeof(FakeData).GetProperty("Distance"),
			typeof(FakeData).GetProperty("Address")
		});
	wb.SaveAs(new FileInfo("EPPlus.xlsx"));
}

using (var fs = new FileStream("NPOI.xlsx", FileMode.Create, FileAccess.Write))
{
	var wb = new XSSFWorkbook();
	var ws = wb.CreateSheet("1");
	int rowCount = 0;
	IRow row;
	foreach (var fakeData in data)
	{
		row = ws.CreateRow(rowCount++);
		row.CreateCell(0).SetCellValue(fakeData.Time.ToString(@"hh\:mm\:ss\.fff"));
		row.CreateCell(1).SetCellValue(fakeData.X);
		row.CreateCell(2).SetCellValue(fakeData.Distance);
		row.CreateCell(3).SetCellValue(fakeData.Address);
	}
	wb.Write(fs);
}
BenchmarkDotNet=v0.10.9, OS=Windows 10 Redstone 2 (10.0.15063)
Processor=Intel Core i7-6700K CPU 4.00GHz (Skylake), ProcessorCount=8
Frequency=3914068 Hz, Resolution=255.4887 ns, Timer=TSC
  [Host]     : .NET Framework 4.7 (CLR 4.0.30319.42000), 32bit LegacyJIT-v4.7.2110.0
  Job-EJASFH : .NET Framework 4.7 (CLR 4.0.30319.42000), 32bit LegacyJIT-v4.7.2110.0
Method Mean Error StdDev Gen 0 Gen 1 Gen 2 Allocated
ClosedXML 337.6 ms NA 2.5647 ms 9625.0000 7062.5000 2812.5000 47.26 MB
EPPlus 145.8 ms NA 0.2533 ms 5000.0000 3250.0000 2000.0000 24.68 MB
NPOI 263.4 ms NA 5.8716 ms 10500.0000 7343.7500 2375.0000 55.65 MB

總體上EPPlus在速度和記憶體上都最佳，感覺ClosedXML在API呼叫上方便一些，文件寫全面一些。

五、其他

SpreadSheetLight 之前專案使用的，讀寫都可以，需要OpenXMLSDK 2.5

ExcelDataReader Excel 03-07 檔案讀取，只需要快速讀取excel檔案的可以用這個

相關文章