Excel Automation works very slowly!

Question

Hello,
On my computer report is generated in about 3-4 seconds, and it was like this on all computers before. But recently on a couple of computers in the office report generating started to take 30-40 seconds. On other computers it is still fast. I tried to reinstall MS Office with no luck.

For example, this code takes !!! 3 !!! seconds to execute:

xlWS.Name = "INVOICE";
xlWS.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
xlWS.PageSetup.TopMargin = xl.CentimetersToPoints(1.5);
xlWS.PageSetup.LeftMargin = xl.CentimetersToPoints(1);
xlWS.PageSetup.BottomMargin = xl.CentimetersToPoints(1.5);
xlWS.PageSetup.RightMargin = xl.CentimetersToPoints(1);
xlWS.PageSetup.Zoom = 60;

What can it be?
Thank you in advance.

Answer 1

Change the default printer to a local printer instead of a network printer. Pop the network cable, to be sure. Then ask your LAN admin to post to serverfault.com

Answer 2

The Worksheet.PageSetup properties are extremely slow when you set them, but are very fast to simply get a value.

Therefore, the best approach is to check each value first, to make sure that you need to set it, and then only set the properties that you need. In addition, it can help to set 'Application.ScreenUpdating = False' and 'Application.Calculation = xlCalculationManual'.

Using your code as a starting point, the updated code could look something like this:

Excel.Application excelApp = xlWS.Application;

bool origScreenUpdating = excelApp.ScreenUpdating;

Excel.XlCalculation origCalcMode = excelApp.Calculation;

try
{
    myExcelApp.ScreenUpdating = False;

    myExcelApp.Calculation = xlCalculationManual;

    xlWS.Name = "INVOICE";

    if (xlWS.PageSetup.PaperSize != XlPaperSize.xlPaperA4)
    {
        xlWS.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
    }

    if (xlWS.PageSetup.TopMargin != xl.CentimetersToPoints(1.5))
    {
        xlWS.PageSetup.TopMargin = xl.CentimetersToPoints(1.5);
    }

    if (xlWS.PageSetup.LeftMargin != xl.CentimetersToPoints(1))
    {
        xlWS.PageSetup.LeftMargin = xl.CentimetersToPoints(1);
    }

    if (xlWS.PageSetup.BottomMargin != xl.CentimetersToPoints(1.5))
    {
        xlWS.PageSetup.BottomMargin = xl.CentimetersToPoints(1.5);
    }

    if (xlWS.PageSetup.RightMargin != xl.CentimetersToPoints(1))
    {
        xlWS.PageSetup.RightMargin = xl.CentimetersToPoints(1);
    }

    if (xlWS.PageSetup.Zoom != 60)
    {
        xlWS.PageSetup.Zoom = 60;
    }
}
finally
{
    myExcelApp.ScreenUpdating = origScreenUpdating;
    myExcelApp.Calculation = origCalcMode;
}

This is the best that you can do on the Excel side of things -- and it usually helps a lot. I'm less certain how much this will help in your situation, however, as it appears that you might be making a lot of setting changes that differ from the default. Therefore, first checking that the value needs to be made might not help you if all these changes do, in fact, need to occur.

But I have my fingers crossed for you...

-- Mike

(By the way, for cross reference, here's a VBA answer I gave on the same topic where the OP saw a nice performance improvement: How to print faster in Excel VBA?)

Answer 3

SpreadsheetGear for .NET will likely speed up your application significantly. You can read quotes from a few of our customers here - many of these quotes point out performance improvements provided by SpreadsheetGear for .NET.

You can download the free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC