c# - 如何更快地遍历数据并写入excel文件

Question

我有一个非常简单的方法，它从 sql 查询中获取数据，然后将其放入列表中，最后遍历两个 for each 循环到一个 excel 文件中。它适用于少量数据，但不适用于大量数据。迭代并最快写入我的工作簿的最佳方法是什么？

这是代码

  private UInt32 AddSheets(SpreadsheetDocument document, WorkbookPart workbookPart, Sheets sheets, string sheetName, UInt32 sheetNumber, List<RentalFeeReportingRecord> currentRecordList)
    {
        Dictionary<int, string> listofCoreBankingIds = new Dictionary<int, string>();
        listofCoreBankingIds = currentRecordList.Select(x => new { id = x.CoreBankingId, regionName = x.RegionName }).Distinct().ToDictionary(y => y.id, y => y.regionName);
        foreach (var coreBankingId in listofCoreBankingIds.OrderBy(x => x.Value))
        {

            WorksheetPart worksheetPart1 = workbookPart.AddNewPart<WorksheetPart>();
            string currentCurrency = null;
            string regionName = null;

            Worksheet worksheet1 = new Worksheet();
            SheetData sheetData1 = new SheetData();

            Row rowInSheet1 = new Row();
            Row firstRow = new Row();
            Row lastRow = new Row();

            rowInSheet1.Append(
              excelController.ConstructCell("Region", CellValues.String, 3), excelController.ConstructCell("Merchant - Terminal", CellValues.String, 3),
              excelController.ConstructCell("Fee Charged", CellValues.String, 3),
              excelController.ConstructCell("Currency", CellValues.String, 3),
              excelController.ConstructCell("Processing Date", CellValues.String, 3),
              excelController.ConstructCell("Description", CellValues.String, 3)
            );

            sheetData1.Append(rowInSheet1);


            foreach (var currentRecord in currentRecordList.OrderBy(x => x.RegionName).ThenBy(z => z.Currency).ThenBy(y => y.DateProcessed))
            {
                if (coreBankingId.Key == currentRecord.CoreBankingId)
                {

                    regionName = currentRecord.RegionName;
                    currentCurrency = currentRecord.Currency;
                    double currentTotal = currentRecord.TotalFeeAmount;
                    string merchant = currentRecord.MerchantRecord.MerchantID + " - " + currentRecord.MerchantTerminalRecord.terminalID;

                    if (currentRecord.IsPinPad)
                    {
                        merchant = merchant + " - PIN Pad";
                    }

                    string description = currentRecord.TerminalRecord.description;
                    if (currentRecord.IsProRated)
                    {
                        int days = DateTime.DaysInMonth(currentRecord.DeployedDate.Year, currentRecord.DeployedDate.Month);

                        description = currentRecord.TerminalRecord.description + " (" + currentRecord.MonthlyFee + " Fee \\ " + days.ToString() + " Days) * " + currentRecord.DaysActive.ToString() + " Days Active";
                    }

                    rowInSheet1 = new Row();
                    firstRow = new Row();
                    lastRow = new Row();

                    rowInSheet1.Append(
                      excelController.ConstructCell(regionName, CellValues.String, 2),
                      excelController.ConstructCell(merchant, CellValues.String, 2),
                      excelController.ConstructCell(currentRecord.TotalFee.ToString("N2"), CellValues.String, 1),
                      excelController.ConstructCell(currentRecord.Currency, CellValues.String, 2),
                      excelController.ConstructCell(currentRecord.DateProcessed.ToString("MMM dd, yyyy"), CellValues.String, 2),
                      excelController.ConstructCell(description, CellValues.String, 2)
                    );

                    sheetData1.Append(rowInSheet1);

                }

            }


            Columns columns = excelController.AutoSize(sheetData1);

            worksheet1.AppendChild(columns);
            worksheet1.AppendChild(sheetData1);
            worksheetPart1.Worksheet = worksheet1;


            Sheet sheet1 = new Sheet()
            {

                Id = document.WorkbookPart.GetIdOfPart(worksheetPart1),
                SheetId = sheetNumber,
                Name = regionName
            };

            sheets.Append(sheet1);
            sheetNumber++;
        }
        return sheetNumber;

    }

任何帮助，将不胜感激。

标签： c#excelopenxml