前言
當(dāng)要導(dǎo)出的 DataTable 數(shù)據(jù)量很大時(shí)（比如行數(shù)幾十萬的情況下）翩蘸，NPOI 內(nèi)存占用非常高防嗡，這里研究一下性能更好的excel導(dǎo)出方式
一变汪、使用 closedXML 庫
image.png
0、工具類-生成DataTable
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ExcelExportTest
{
public class GenerateDataTable
{
public DataTable GetNewTable(int rowCount=600000)
{
Stopwatch sw = new Stopwatch();
sw.Start();
var data = new DataTable("測試表格");
data.Columns.Add("Library", typeof(string));
data.Columns.Add("Name", typeof(string));
data.Columns.Add("Description", typeof(string));
data.Columns.Add("Type", typeof(string));
data.Columns.Add("Definer", typeof(string));
data.Columns.Add("Definer_Description", typeof(string));
data.Columns.Add("Creation_Date", typeof(DateTime));
data.Columns.Add("Days_Since_Creation", typeof(string));
data.Columns.Add("Size", typeof(string));
data.Columns.Add("Last_Used", typeof(DateTime));
data.Columns.Add("Attribute", typeof(string));
data.Columns.Add("Count_Of_Objects_Referenced", typeof(string));
data.Columns.Add("Possibly_Referenced", typeof(string));
for (var i = 0; i < rowCount; i++)
{
data.Rows.Add(
"xxxxxxxxx",
"xxxxxxxxx",
"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"*USRSPC",
"xxxxxxxxx",
"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
DateTime.Now,
"789",
"16384",
DateTime.Now.AddDays(120),
"GRC",
"0",
"0"
);
}
sw.Stop();
Console.WriteLine($"Generate datable used [{sw.ElapsedMilliseconds}] ms");
return data;
}
}
}
1蚁趁、一次導(dǎo)入全部數(shù)據(jù)并保存
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using ClosedXML.Excel;
namespace ExcelExportTest
{
public class ClosedXmlTest
{
public void DoTest_GenerateDirectly()
{
var gdt = new GenerateDataTable();
var table = gdt.GetNewTable();
Thread.Sleep(5000);
Stopwatch sw = new Stopwatch();
sw.Start();
using (var workbook = new XLWorkbook())
{
var filePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss")+ ".xlsx");
var worksheet = workbook.Worksheets.Add(table, table.TableName);
sw.Stop();
Console.WriteLine($"Add workbook used {sw.ElapsedMilliseconds} ms");
sw.Restart();
workbook.SaveAs(filePath);
}
sw.Stop();
Console.WriteLine($"Save used {sw.ElapsedMilliseconds} ms");
}
}
}
-
耗時(shí)及內(nèi)存占用
image.png
image.png
內(nèi)存占用 3000 MB左右裙盾，耗時(shí) 100 秒左右，生成的文件大小 23 MB
image.png
2他嫡、每次導(dǎo)入8000筆數(shù)據(jù)番官，保存，再打開文件追加
public void DoTest_GenerateThenAttach()
{
var gdt = new GenerateDataTable();
var table = gdt.GetNewTable(1);
Stopwatch sw = new Stopwatch();
sw.Start();
var filePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss") + ".xlsx");
int looRowCount = 8000;
using (var workbook = new XLWorkbook())
{
workbook.Worksheets.Add(gdt.GetTableWithNRows(table, looRowCount), table.TableName);
workbook.SaveAs(filePath);
}
int i = 2;
int loop = 300000 / looRowCount;
while (i <= loop)
{
using (var workbook = new XLWorkbook(filePath))
{
IXLWorksheet Worksheet = workbook.Worksheet(table.TableName);
int NumberOfLastRow = Worksheet.LastRowUsed().RowNumber();
IXLCell CellForNewData = Worksheet.Cell(NumberOfLastRow + 1, 1);
CellForNewData.InsertTable(gdt.GetTableWithNRows(table, looRowCount));
if (i == loop)
{
Worksheet.Columns().AdjustToContents();
}
workbook.Save();
Console.WriteLine($"Loop {i} work done...");
i++;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
sw.Stop();
Console.WriteLine($"Save all rows used {sw.ElapsedMilliseconds} ms");
}
public DataTable GetTableWithNRows(DataTable dataIn, int rowCount)
{
DataTable data = dataIn.Clone();
for (var i = 0; i < rowCount; i++)
{
data.Rows.Add(
"xxxxxxxxx",
"xxxxxxxxx",
"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"*USRSPC",
"xxxxxxxxx",
"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
DateTime.Now,
"789",
"16384",
DateTime.Now.AddDays(120),
"GRC",
"0",
"0"
);
}
return data;
}
-
但是這個(gè)運(yùn)行非常慢钢属，耗時(shí)嚴(yán)重徘熔，而且越往后讀取excel后內(nèi)存占用還是會(huì)很大
image.png
二、使用 OpenXML 庫
image.png
1淆党、使用高級(jí)API
public void DoTest()
{
var gdt = new GenerateDataTable();
var table = gdt.GetNewTable();
Stopwatch sw = new Stopwatch();
sw.Start();
var filename = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss") + ".xlsx");
WriteExcelFile(filename, table);
sw.Stop();
Console.WriteLine($"Save used {sw.ElapsedMilliseconds} ms");
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
private static void WriteExcelFile(string filename, DataTable table)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = table.TableName };
sheets.Append(sheet);
Row headerRow = new Row();
List<String> columns = new List<string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookPart.Workbook.Save();
}
}
-
內(nèi)存占用峰值情況【1300MB】
image.png -
耗時(shí)【22秒】
image.png -
生成文件大小【2.33MB】
image.png
2酷师、使用OpenXmlWriter
參考文章
- Writing Large Excel Files with the Open XML SDK
- Export big amount of data from XLSX - OutOfMemoryException
- How to properly use OpenXmlWriter to write large Excel files
- Working with the shared string table (Open XML SDK)
public void DoTest2()
{
var gdt = new GenerateDataTable();
var table = gdt.GetNewTable();
Stopwatch sw = new Stopwatch();
sw.Start();
var filename = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss") + ".xlsx");
LargeExport(filename, table);
sw.Stop();
Console.WriteLine($"Save used {sw.ElapsedMilliseconds} ms");
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
public static void LargeExport(string filename, DataTable table)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
//this list of attributes will be used when writing a start element
List<OpenXmlAttribute> attributes;
OpenXmlWriter writer;
document.AddWorkbookPart();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
// 表頭列信息
//create a new list of attributes
attributes = new List<OpenXmlAttribute>();
// add the row index attribute to the list
attributes.Add(new OpenXmlAttribute("r", null, "1"));
//write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
for (int columnNum = 0; columnNum < table.Columns.Count; ++columnNum)
{
//reset the list of attributes
attributes = new List<OpenXmlAttribute>();
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
attributes.Add(new OpenXmlAttribute("t", null, "str"));
//add the cell reference attribute
attributes.Add(new OpenXmlAttribute("r", "", $"{GetColumnName(columnNum+1)}1"));
//write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
//write the cell value
writer.WriteElement(new CellValue(table.Columns[columnNum].ColumnName));
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
for (int rowNum = 1; rowNum <= table.Rows.Count; ++rowNum)
{
int docRowNum = rowNum + 1;
//create a new list of attributes
attributes = new List<OpenXmlAttribute>();
// add the row index attribute to the list
attributes.Add(new OpenXmlAttribute("r", null, docRowNum.ToString()));
//write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
for (int columnNum = 1; columnNum <= table.Columns.Count; ++columnNum)
{
//reset the list of attributes
attributes = new List<OpenXmlAttribute>();
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
attributes.Add(new OpenXmlAttribute("t", null, "str"));
//add the cell reference attribute
attributes.Add(new OpenXmlAttribute("r", "", $"{GetColumnName(columnNum)}{docRowNum}"));
//write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
var cellValue = table.Rows[rowNum - 1][columnNum - 1];
string cellStr = cellValue == null ? "" : (cellValue is DateTime?((DateTime)cellValue).ToString("yyyy-MM-dd HH:mm:ss.fff") : cellValue.ToString());
//write the cell value
writer.WriteElement(new CellValue(cellStr));
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
}
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = table.TableName,
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
// End Sheets
writer.WriteEndElement();
// End Workbook
writer.WriteEndElement();
writer.Close();
document.Close();
}
}
//A simple helper to get the column name from the column index. This is not well tested!
private static string GetColumnName(int columnIndex)
{
int dividend = columnIndex;
string columnName = String.Empty;
int modifier;
while (dividend > 0)
{
modifier = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modifier).ToString() + columnName;
dividend = (int)((dividend - modifier) / 26);
}
return columnName;
}
-
內(nèi)存占用【不超過 190MB】
image.png -
耗時(shí)【17 秒】
image.png -
生成的文件大小【26 MB】
image.png
將數(shù)據(jù)量增大到100萬
內(nèi)存占用不超過【260 MB】，耗時(shí)【30秒】染乌，生成文件大小【48.1MB】
image.png
image.png
三山孔、使用 npoi
public void DoTest()
{
var gdt = new GenerateDataTable();
var table = gdt.GetNewTable();
Stopwatch sw = new Stopwatch();
sw.Start();
var filename = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss") + ".xlsx");
ExportDataTableToExcel(table, filename);
sw.Stop();
Console.WriteLine($"Save used {sw.ElapsedMilliseconds} ms");
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
public static Tuple<bool, string> ExportDataTableToExcel(DataTable dt, string saveTopath)
{
bool result = false;
string message = "";
IWorkbook workbook = null;
FileStream fs = null;
IRow row = null;
ISheet sheet = null;
ICell cell = null;
try
{
if (dt != null && dt.Rows.Count > 0)
{
if (saveTopath.IndexOf(".xlsx", StringComparison.Ordinal) > 0) // 2007版本
workbook = new XSSFWorkbook();
else //if (saveTopath.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet(dt.TableName);
int rowCount = dt.Rows.Count;//行數(shù)
int columnCount = dt.Columns.Count;//列數(shù)
//設(shè)置列頭
row = sheet.CreateRow(0);//excel第一行設(shè)為列頭
for (int c = 0; c < columnCount; c++)
{
cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
}
//設(shè)置每行每列的單元格,
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//excel第二行開始寫入數(shù)據(jù)
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
using (fs = File.OpenWrite(saveTopath))
{
workbook.Write(fs);//向打開的這個(gè)xls文件中寫入數(shù)據(jù)
result = true;
}
}
else
{
message = "沒有解析到數(shù)據(jù)！";
}
return new Tuple<bool, string>(result, message);
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
return new Tuple<bool, string>(false, ex.Message);
}
}
-
內(nèi)存占用【2600MB】
image.png 耗時(shí)【50 秒】
-
生成的文件大小【29 MB】
image.png
四慕匠、結(jié)論
顯然饱须，在只考慮生成文件的情況下，應(yīng)該選擇使用OpenXmlWriter的方式
