https://www.cnblogs.com/ryantocode/p/excelwriter.html
最近項目中需要一個導出Excel報告的功能，假期搜了一下，把其中比較主流的列一下，僅供參考。
功能需求：
建立并寫入.xlsx Excel2007+版本的電子表格檔案
不需要office元件支援，終端電腦無需安裝ms office
簡單的format，style,chart和formula支援(不用過于複雜)，并且能夠插入圖檔
速度，保證資料在萬行以上表格寫入速度
效果圖：
一、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
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
需要引用SharpZipLib，可以讀寫Word和Excel，例子比較全，系統點的文檔沒有找到，不過是國人的開源項目，百度應該能找到很多
參考：
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.GetRow(0).CreateCell(1).SetCellValue("Project001");
ws.CreateRow(1).CreateCell(0).SetCellValue("User");
ws.GetRow(1).CreateCell(1).SetCellValue("User001");
ws.CreateRow(2).CreateCell(0).SetCellValue("Create Date");
ws.GetRow(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檔案的可以用這個