更快地创建 Excel (Interop)

Question

首先，此示例代码是在 .net core 中创建的，您应该通过以下代码在配置中设置 GraphScopes："GraphScopes": "User.Read User.ReadBasic.All Mail.Send MailBoxSettings.ReadWrite Contacts.ReadWrite"另请注意，如果有多个文件夹，ContactFolders 只会返回结果。永远不会返回默认的联系人文件夹。如果用户没有其他文件夹，这将返回空结果。如果要获取主文件夹和需要分别获取的附加文件夹，则合并结果。// Get the defaultContactsvar defaultContacts = await graphClient    .Me    .Contacts    .Request()    .GetAsync();// Get the contactFoldersvar contactFolders = await graphClient    .Me    .ContactFolders    .Request()    .GetAsync();// Use this to store the contact from all contact folder.List<Contact> contactFolderContacts = new List<Contact>();if (contactFolders.Count > 0) {    for (int i = 0; i < contactFolders.Count; i++) {        var folderContacts = await graphClient            .Me            .ContactFolders[contactFolders[i].Id]            .Contacts            .Request()            .GetAsync();        contactFolderContacts.AddRange(folderContacts.AsEnumerable());    }    // This will combine the contact from main folder and the additional folders.    contactFolderContacts.AddRange(defaultContacts.AsEnumerable());} else {    // This user only has the default contacts folder    contactFolderContacts.AddRange(defaultContacts.AsEnumerable());}// Use this to test the result.foreach (var item in contactFolderContacts) {    Debug.WriteLine("first:" + item.EmailAddresses);}

Answer 1

逐个单元格是使用 Interop 与 Excel 交互的最慢可能的方式 - 查看如何在一次操作中将数据从数组添加到工作表。

Answer 2

互操作库非常慢，并且花费了大量的系统资源。

您可以简单地使用 OpenXML 库，而不是使用互操作库来创建 Excel 文件。我在生产中使用它。超过 100 万行数据只需要 10 秒就可以将数据集导出到 excel 文件。

private void ExportDSToExcel(DataSet ds, string destination)

{

    using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))

    {

        var workbookPart = workbook.AddWorkbookPart();

        workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

        workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

        uint sheetId = 1;

        foreach (DataTable table in ds.Tables)

        {

            var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();

            var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();

            sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);                

            DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();

            string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

            if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)

            {

                sheetId =

                    sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;

            }

            DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };

            sheets.Append(sheet);

            DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

            List<String> columns = new List<string>();

            foreach (DataColumn column in table.Columns)

            {

                columns.Add(column.ColumnName);

                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();

                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;

                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);

                headerRow.AppendChild(cell);

            }

            sheetData.AppendChild(headerRow);

            foreach (DataRow dsrow in table.Rows)

            {

                DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                foreach (String col in columns)

                {

                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();

                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;

                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //

                    newRow.AppendChild(cell);

                }

                sheetData.AppendChild(newRow);

            }

        }

    }

}