更快地创建 Excel (Interop)

Question

首先，此示例代码是在 .net core 中创建的，您应该通过以下代码在配置中设置 GraphScopes：

"GraphScopes": "User.Read User.ReadBasic.All Mail.Send MailBoxSettings.ReadWrite Contacts.ReadWrite"

另请注意，如果有多个文件夹，ContactFolders 只会返回结果。永远不会返回默认的联系人文件夹。如果用户没有其他文件夹，这将返回空结果。如果要获取主文件夹和需要分别获取的附加文件夹，则合并结果。

// Get the defaultContacts

var defaultContacts = await graphClient

    .Me

    .Contacts

    .Request()

    .GetAsync();

// Get the contactFolders

var 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){&nbsp; &nbsp; using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; var workbookPart = workbook.AddWorkbookPart();&nbsp; &nbsp; &nbsp; &nbsp; workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();&nbsp; &nbsp; &nbsp; &nbsp; workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();&nbsp; &nbsp; &nbsp; &nbsp; uint sheetId = 1;&nbsp; &nbsp; &nbsp; &nbsp; foreach (DataTable table in ds.Tables)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sheetId =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sheets.Append(sheet);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<String> columns = new List<string>();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (DataColumn column in table.Columns)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; columns.Add(column.ColumnName);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; headerRow.AppendChild(cell);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sheetData.AppendChild(headerRow);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (DataRow dsrow in table.Rows)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; foreach (String col in columns)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; newRow.AppendChild(cell);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sheetData.AppendChild(newRow);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}