C# Microsoft.Office.Interop.Excel v15 slow

Question

I am working on an application that needs to edit an excel file performing various actions like:

• Copy paste sections from worksheet to worksheet
• Add/Edit values
• Formulas and macros run in the background

This is an application initially developed with Excel 2007 but I now have to use Excel 2013. Unfortunately, I noticed that it is almost 10x times slower when running in a machine that has Excel 2013 installed rather than Excel 2007.

Therefore, I created a very simple benchmarking application that randomly generates numbers and writes them in 1000 (rows) * 15 (columns) = 15000 cells.

The results are disappointing as it takes around 670 milliseconds to run in Excel 2007 and around 3257 milliseconds to run in Excel 2013. (this is 5x times slower)

Please find the code that gives the above benchmark results:

static void Main(string[] args)
    {
        var stopwatch = new Stopwatch();

        stopwatch.Reset();
        stopwatch.Start();

        var excel = new Application();
        excel.Visible = false;
        excel.DisplayAlerts = false;

        var workbooks = excel.Workbooks;

        var workbook = workbooks.Add(Type.Missing);
        var worksheets = workbook.Sheets;
        var worksheet = (Worksheet)worksheets[1];

        WriteArray2(1000, 15, worksheet);

        workbook.SaveAs(@"c:\temp\Speedtest.xlsx");

        excel.Quit();

        stopwatch.Stop();
        Console.WriteLine("Time elapsed (milliseconds): "+(stopwatch.ElapsedMilliseconds).ToString());
        Console.ReadLine();
        return;
    }

    private static void WriteArray2(int rows, int columns, Worksheet worksheet)
    {
        var data = new object[rows, columns];
        Random r = new Random();

        for (var row = 1; row <= rows; row++)
        {
            for (var column = 1; column <= columns; column++)
            {
                data[row - 1, column - 1] = r.NextDouble()*2000000-1000000;
            }
        }
        var startCell = (Range)worksheet.Cells[1, 1];
        var endCell = (Range)worksheet.Cells[rows, columns];
        var writeRange = worksheet.Range[startCell, endCell];

        writeRange.Value2 = data;
    }
}

Have you had any similar issues with Excel 2013? Do you know if there is anything that I can try to optimise Excel 2013?

I know that some people have raised performance issues in Excel 2013 but Microsoft has not admitted that as being a fact.

Thanks for your help!

Answer 1

Its hard to say how you can optimise your application without seeing any code. One way that might help is instead of selecting individual cells, you can select a range. Then you can iterate through the array in your c# code, which is faster than repeatedly accessing the interop.

I am not sure why 2013 is slower than 2007 though, but interops are normally quite slow and require Excel to be installed to use. For those reasons I tend to avoid Interops.

As Excel spreadsheets are just made up of XML, an alternative solution might be to write the XML yourself using a library.

The OpenXML library is apparently a good choice, here are some useful links:

SDK - https://msdn.microsoft.com/en-us/library/office/bb448854.aspx

Tutorial - https://msdn.microsoft.com/en-us/library/office/hh180830%28v=office.14%29.aspx

However I think executing macros in OpenXML can be difficult. But someone has managed to do it: https://stackoverflow.com/a/21789643/3209889