.NET OpenXML 性能问题

Question

我正在尝试使用OpenXML从ASP.NET Web服务器编写Excel文件。我有大约2100条记录，这需要大约20-30秒的时间。有没有办法让它更快？从数据库中检索2100行只需要一小部分时间。不确定为什么在内存中操作它们需要更长的时间。

注意：ExcelWriter是我们的自定义类，但是它的所有方法都直接来自此链接中的代码：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;

- The Internet

Answer 1

看起来MSDN社区文档中有人遇到了类似的性能问题。下面的代码非常低效。有人建议使用哈希表。

对于我们的解决方案，我们完全删除了共享字符串的插入操作，将下载时间从1分03秒缩短到了0分03秒。

//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文档（缓慢的解决方案，他们应该使用哈希表）

      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;
 }  

- The Internet

---

我遇到了同样的问题...我需要编写1000+行，有时甚至需要编写10000+行，但速度非常慢...你在这里说可以使用哈希表，你能给个例子吗？或者你用了其他什么方法来提高性能... - kunjee

我正在查看50万行数据。自从这篇文章发布以来，您是否进行了其他改进可以分享？我已经转向使用SAX方法来最小化内存使用。我大约每1.1秒可以处理1000行数据。如果您有更快的方法，请分享一下。 - CaptainBli

这个建议不使用SharedStringTable，所以虽然速度更快，但当字符串被重复使用时，文件大小会急剧增加。 - undefined

Answer 2

@互联网

请注意，String数据类型实际上是用于公式的，对于文本应该使用InlineString。请参见17.18.11 ST_CellType（单元格类型）：

• inlineStr（内联字符串）-包含（内联）富字符串的单元格， 即不在共享字符串表中的单元格。如果使用此单元格类型，则单元格值位于单元格（c元素）中的is元素而不是v元素中。
• str（字符串）-包含公式字符串的单元格。

- user3285954

Answer 3

重大的改进是将更多的Save()函数移出循环

 //Save data
        shareStringPart.SharedStringTable.Save();
        worksheetPart.Worksheet.Save();

对于500条记录，它使我的时间从10分钟变为1分钟。

- zquanghoangz

---

这是使用OpenXML工作的一个非常重要的部分 - 由于许多子部件需要保存，我们倾向于将保存放在单个操作方法或循环内部，而不是在外部范围中进行保存，这样可以提高性能。 - Andrew Hanlon

减少过多的“Save()”调用确实提高了性能，但我发现完全绕过共享字符串表，并将所有文本保存为“InLineString”对于几千行来说是最快的。三个小时缩短到不到一分钟。 - Mrphin

Answer 4

如果您想要更好的性能，创建所有必需的对象并将其作为参数传递到方法中，而不是在每次调用该方法时检查。这就是为什么SharedStringTable作为参数传递而不是部分。

字典可用于快速索引查找，比for循环具有更好的性能。它们比哈希表稍微快一些，因为它们是强类型的，所以不需要装箱。无论如何，强类型都是一个极大的优点。

private static int InsertSharedStringItem(string sharedString, SharedStringTable sharedStringTable, Dictionary<string, int> sharedStrings)
{
    int sharedStringIndex;

    if (!sharedStrings.TryGetValue(sharedString, out sharedStringIndex))
    {
        // The text does not exist in the part. Create the SharedStringItem now.
        sharedStringTable.AppendChild(new SharedStringItem(new Text(sharedString)));

        sharedStringIndex = sharedStrings.Count;

        sharedStrings.Add(sharedString, sharedStringIndex);
    }

    return sharedStringIndex;
}

- user3285954

Answer 5

根据互联网所述，他们应该使用Hashtable，正如zquanghoangz所建议的那样，他们应该将Save（）移出循环。

InlineString确实可以工作，但当使用不具有信息的错误消息打开生成的文件时，会使MS Excel头疼，这些错误消息可以修复，但仍会弹出令人恼火的弹窗。

static Cell AddCellWithSharedStringText(
    [NotNull]string text, 
    [NotNull]Hashtable texts, 
    [NotNull]SharedStringTablePart shareStringPart
)
{
    if (!texts.ContainsKey(text))
    {
        shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
        texts[text] = texts.Count;
    }
    var idx = (int)texts[text];
    Cell c1 = new Cell();
    c1.DataType = CellValues.SharedString;
    c1.CellValue = new CellValue(idx.ToString());
    return c1;
}

这个解决方案将导出时间从大约5分钟降至6秒，适用于[9880 x 66]网格。

- Karel Wesseling