Faster creation of Excel (Interop)

Question

I identified the code slowing down the process as this one (where I'm filling the cells):

What I'm doing here is basically loading some data from a database using a DataSet.

Microsoft.Office.Interop.Excel.Range range1 = null;
Microsoft.Office.Interop.Excel.Range cell1 = null;
Microsoft.Office.Interop.Excel.Borders border1 = null;

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
    int s = i + 1;
    for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
    {
        data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
        xlWorkSheet.Cells[s + 1, j + 1] = data;

        range1 = xlWorkSheet.UsedRange;
        cell1 = range1.Cells[s + 1, j + 1];
        border1 = cell1.Borders;


        if (((IList)terms).Contains(xlWorkSheet.Cells[1, j + 1].Value.ToString()))
        {
            cell1.Interior.Color = System.Drawing.Color.Red;
        }

        range1.Columns.AutoFit();
        range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

        border1.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
        border1.Weight = 2d;

    }
}

It's sometimes taking like more than 1 minute to load the whole thing. Is there is away to optimize it?.

Have you tried to move the code: range1.Columns.AutoFit(); range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; to outside of both for-loops? — Lucian, Aug 09 '18 at 06:36
cell-by-cell is the slowest possible way to interact with Excel using interop - look up how to add data to a sheet from an array in one operation. E.g. https://stackoverflow.com/questions/536636/write-array-to-excel-range — Tim Williams, Aug 09 '18 at 06:41
Lucian has a good point, you format all cells equal to create one range and do the format over that range. You might even be able to do it for your borders. So do all common formatting before the loop and override then in the if the highlights you need. — Aldert, Aug 09 '18 at 06:42
@Lucian That made it like 10 seconds faster. Thank you! I'm looking into TimWilliams suggestion, I believe it's the key. — Zkai, Aug 09 '18 at 06:58
@TimWilliams Feel free to post that as an answer so I mark it. It worked. — Zkai, Aug 09 '18 at 08:21

score 2 · Accepted Answer · answered Aug 09 '18 at 14:49

2

Cell-by-cell is the slowest possible way to interact with Excel using Interop - look up how to add data to a sheet from an array in one operation.

E.g.

Write Array to Excel Range

shows this approach.

answered Aug 09 '18 at 14:49

Tim Williams

154,628
8
97
125

score 0 · Answer 2 · answered Aug 09 '18 at 06:58

Interop libraries are extremely slow and spends huge source of system.

Instead of using Interop Libraries to create Excel files, you can simply use it OpenXML library. I'm using it in production. And over 1 million rows it just takes about 10 seconds to export dataset to excel file.

Here is a sample code quoted from:

Export DataTable to Excel with Open Xml SDK in c#

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

Faster creation of Excel (Interop)

2 Answers2