Microsoft.Office.Interop.Excel really slow

user_476024 0
Viewed 53045

I am exporting a 1200 X 800 matrix (indexMatrix) to a excel file using the standard Microsoft.Office.Interop.Excel. The app works, just that it is really really really slow( even for the 100 x 100 matrix) . I also export in a text file through a TextWriter an it works almost instantly . Is there any way to export to the excel file faster?

Here is my code :

        Excel.Application xlApp=new Excel.Application();
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        //xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        for (int i = 0; i < 800; i++)   //h
            for (int j = 0; j < 1200; j++)
                xlWorkSheet.Cells[i+1,j+1] =indexMatrix[i][j];


        xlWorkBook.SaveAs("C:\\a.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        MessageBox.Show("Excel file created , you can find the file c:\\csharp-Excel.xls");
interop excel c#
8 Answers

Use Value2 to make it fast; Show excel before filling data

user_2127460

I am answering a little bit late sorry. I was working on my project and we had to use interop excel. And our data was too big, which was taking more than 1 minute with interop excel. We tried something else which is copy the all content of datagridview to clipboard, open an excel worksheet using interop excel, and paste the content to excel. It takes less than 1 second and exports our data perfectly.

DataGridView to string:

    var newline = System.Environment.NewLine;
    var tab = "\t";
    var clipboard_string = "";

    foreach (DataGridViewRow row in dgProjeler.Rows)
    {
        for (int i = 0; i < row.Cells.Count; i++)
        {
            if (i == (row.Cells.Count - 1))
                clipboard_string += row.Cells[i].Value + newline;
            else
                clipboard_string += row.Cells[i].Value + tab;
        }
    }

and copy the string to clipboard

        Clipboard.SetText(clipboard_string);

And open a worksheet, paste the content.

        Excel.Application app = new Excel.Application();
        app.Visible = true;
        Excel.Workbook wb = app.Workbooks.Add(1);
        Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
        // changing the name of active sheet
        ws.Name = "Exported from gridview";

        ws.Rows.HorizontalAlignment = HorizontalAlignment.Center;
        app.ActiveWindow.Activate();


        ws.Activate();
        ws.Paste();

        ws.Cells.EntireColumn.AutoFit();

It works perfectly on me, I hope it helps people who still couldn't find the solution.

user_72980560
Related