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!

Warm start-up is: 3257 milliseconds to run in Excel 2013 and 670 milliseconds to run in Excel 2007. — gpanagopoulos, Jun 25 '15 at 12:17
If you leave out application startup time, how long does it take then? Do you have addins running? — Patrick Hofman, Jun 25 '15 at 12:46
So 2007 gets slower now and you gain a lot on startup of 2013. So isn't the key problem startup time then? — Patrick Hofman, Jun 25 '15 at 15:00
Good question. It is taking 1246 milliseconds for Excel 2013 and 456 for Excel 2007. I have disabled all add-ins as I read online that in some cases they have caused performance issues. I also tried some other optimisations found online but no improvement. - (First gave wrong results as I forgot to change the stopwatch in the code) — gpanagopoulos, Jun 25 '15 at 15:03
It also worth saying that I am running the Excel 2013 example in a very good machine whereas the numbers for the Excel 2007 version are coming from a generally slow VM. — gpanagopoulos, Jun 25 '15 at 15:11
Okay. Are you sure your measurements are solid? What if you do this 100 times? — Patrick Hofman, Jun 25 '15 at 15:21
Yes. I have run it multiple times and although I am almost always getting a slightly different result there is always a big difference between the two versions. In cases of cold startup runs, there is a buffer in both versions but again v2007 beats v2013 by far. — gpanagopoulos, Jun 25 '15 at 15:45

score 0 · Answer 1 · edited May 23 '17 at 12:06

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

Thanks for your suggestion Magnus. I have used OpenXML before and could possibly give a solution. However, the issue here is that the layer for managing excel using Interop is very mature, well tested and performing really well in our server with Excel 2007. Once we upgraded Excel to v2013, it started misbehaving. In order to change it to try OpenXML (still no guarantee that it will work), we will have to consume much time and budget to research, develop and test! — gpanagopoulos, Jun 25 '15 at 11:58

C# Microsoft.Office.Interop.Excel v15 slow

1 Answers1