最近項目中需要一個導出Excel報告的功能,假期搜了一下,把其中比較主流的列一下,僅供參考。 功能需求: 效果圖: 一、ClosedXML 主頁:https://github.com/ClosedXML/ClosedXML 需要引用OpenXMLSDK(DocumentFormat.OpenXml. ...
最近項目中需要一個導出Excel報告的功能,假期搜了一下,把其中比較主流的列一下,僅供參考。
功能需求:
- 創建並寫入.xlsx Excel2007+版本的電子錶格文件
- 不需要office組件支持,終端電腦無需安裝ms office
- 簡單的format,style,chart和formula支持(不用過於複雜),並且能夠插入圖片
- 速度,保證數據在萬行以上表格寫入速度
效果圖:
一、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"); }View Code
二、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")); }View Code
三、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); }View Code
四、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); }View Code
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文件的可以用這個