Question

我正试图从协会那里书写一份Excel案。利用开放式XML网网络服务器。我有大约2100份记录,大约需要20-30秒。我能够更快地做到这一点。从干b中提取2100个行,占第二行的一小部分。不能确定为什么要把他们打上记忆。

注:ExcelWriter是我们的习惯类别,但其所有方法都直接来自这一链接中的代码:。

   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;

Answer 1

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秒,下载时间。

//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);

生活津贴

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

Answer 2

@un.org

Note that String data type is actually for formulas, for text should use InlineString. See 17.18.11 ST_CellType (Cell Type):

inlineStr (Inline String) - Cell containing an (inline) rich string, i.e., one not in the shared string table. If this cell type is used, then the cell value is in the is element rather than the v element in the cell (c element).
str (String) - Cell containing a formula string.

Answer 3

大型杂质是“救助”更能摆脱循环。

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

对500份记录,对我来说,它从10个min升到1个min。

Answer 4

@kunjee

如果你想要履约,就必须冒犯,以免每次援引这种方法时都会受到检查。因此,共同标准作为参数而不是部分通过。

假肢是快速、指数化的眼光,其性能优于 lo。比薄板更快,因为严格分类不需要箱子。强类是巨大的惠益。

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

Answer 5

As mentioned by The Internet, they should have used a Hashtable and as proposed by zquanghoangz they should have moved the Save() out of the loop.

联网的确发挥作用,但它在打开生成的文档时,给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;
}

这一解决办法使出口时间从[9880×66]电网上的大约5分钟降至6秒。

友情链接