.NET OpenXML性能问题 [英] .NET OpenXML performance issues
问题描述
我正在尝试使用OpenXML从ASP.NET Web服务器写出Excel文件.我大约有2100条记录,大约需要20到30秒才能完成.有什么办法可以使它更快?从数据库中检索2100行只需不到一秒钟的时间.不知道为什么要在内存中处理它们会花费更多时间.
I am attempting to write out an Excel file from an ASP.NET web server using OpenXML. I have about 2100 records and its taking around 20-30 seconds to do this. Any way I can make it faster? Retrieving the 2100 rows from the db takes a fraction of a second. Not sure why manipulating them in memory would take any longer.
注意:ExcelWriter是我们的自定义类,但是其所有方法都直接来自此链接中的代码 http://msdn.microsoft.com/en-us/library/cc861607.aspx
Note: ExcelWriter is our custom class, but all its methods are directly from code in this link, http://msdn.microsoft.com/en-us/library/cc861607.aspx
public static MemoryStream CreateThingReport(List<Thing> things, MemoryStream template)
{
SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(template, true);
WorksheetPart workSheetPart = spreadsheet.WorkbookPart.WorksheetParts.First();
SharedStringTablePart sharedStringPart = spreadsheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
Cell cell = null;
int index = 0;
//create cell formatting for header text
Alignment wrappedAlignment = new Alignment { WrapText = true };
uint rowOffset = 2;
foreach (Thing t in things)
{
//Received Date
cell = ExcelWriter.InsertCellIntoWorksheet("A", rowOffset, workSheetPart);
index = ExcelWriter.InsertSharedStringItem(t.CreateDate.ToShortDateString(), sharedStringPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.SharedString);
//Car Part Name
cell = ExcelWriter.InsertCellIntoWorksheet("B", rowOffset, workSheetPart);
index = ExcelWriter.InsertSharedStringItem(t.CarPart.Name, sharedStringPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.SharedString);
rowOffset++;
}
workSheetPart.Worksheet.Save();
spreadsheet.WorkbookPart.Workbook.Save();
spreadsheet.Close();
return template;
推荐答案
因此,看起来MSDN社区文档中的某人遇到了类似的性能影响.下面的代码效率很低.有人建议使用哈希表.
So it looks like someone in the MSDN community docs ran into similar performance implications. The code below is very inefficient. Someone recommended using a hash table.
对于我们的解决方案,我们完全删除了共享字符串的插入,下载时间从1:03秒变为0:03秒.
For our solution we just removed the insertion of shared strings altogether and went from 1:03 seconds to 0:03 seconds in download time.
//Old: (1:03)
cell = ExcelWriter.InsertCellIntoWorksheet("A", rowOffset, workSheetPart);
index = ExcelWriter.InsertSharedStringItem(thing.CreateDate.ToShortDateString(), sharedStringPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.SharedString);
//New: (0:03)
cell = ExcelWriter.InsertCellIntoWorksheet("A", rowOffset, workSheetPart);
cell.CellValue = new CellValue(thing.CreateDate.ToShortDateString());
cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.String);
MSDN文档(解决方案很慢,他们应该改用哈希表)
MSDN Docs (slow solution, they should use a Hash Table instead)
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
这篇关于.NET OpenXML性能问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!