CODEWITHSUNDEEP
c#excelloopsoffice-interopexcel-interop
Uğur Can
Uğur Can

Reputation: 164

Microsoft.Office.Interop.Excel is too slow

I'm using the Excel Interop and I have an object list. It takes too long when I set the properties of the objects in the list in Excel cells in a loop:

        workSheet = (Excel.Worksheet)wbook.ActiveSheet;
        var row = 1;
        foreach (var shiftStop in shiftStops)
        {
            row++;
            workSheet.Cells[row, "A"] = shiftStop.MachineName;
            workSheet.Cells[row, "B"] = shiftStop.MachineType;
            workSheet.Cells[row, "C"] = shiftStop.UnitName;

        }

The shiftStops list has about 4000 objects. It takes 4-5 minutes to complete the return. Is there a way to make this process faster?

Upvotes: 0

Views: 5019

Answers (2)

Cindy Meister
Cindy Meister

Reputation: 25663

Each time you make a call to an object in an Office application it takes time - especially via the interop. This is particularly noticeable in the kind of looping operation you show.

Instead, consider creating an ARRAY of the values then writing the array to an Excel Range. Excel should write each element of the array to a cell. This is standard practise when writing large amounts of data to Excel in order to speed the process.

Assuming shiftStops is an array, the code could look like:

    workSheet = (Excel.Worksheet)wbook.ActiveSheet;
    var row = 1;
    Excel.Range rng = worksheet.Cells[row, "A"];
    rng = rng.Resize(1, shiftStops.Length);
    rng.Value = shiftStops;

Upvotes: 3

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

The following functionality can usually be turned off while your code is executed:

  • Application.ScreenUpdating

    Turn off screen updating. If Application.ScreenUpdating is set to False, Excel does not redraw the screen. While your code runs, the screen updates quickly, and it is usually not necessary for the user to see each update. Updating the screen once, after the code executes, improves performance.

  • Application.DisplayStatusBar

    Turn off the status bar. If Application.DisplayStatusBar is set to False, Excel does not display the status bar. The status bar setting is separate from the screen updating setting so that you can still display the status of the current operation even while the screen is not updating. However, if you do not need to display the status of every operation, turning off the status bar while your code runs also improves performance.

  • Application.Calculation

    Switch to manual calculation. If Application.Calculation is set to xlCalculationManual, Excel only calculates the workbook when the user explicitly initiates the calculation. In automatic calculation mode, Excel determines when to calculate. For example, every time a cell value that is related to a formula changes, Excel recalculates the formula. If you switch the calculation mode to manual, you can wait until all the cells associated with the formula are updated before recalculating the workbook. By only recalculating the workbook when necessary while your code runs, you can improve performance.

  • Application.EnableEvents

    Turn off events. If Application.EnableEvents is set to False, Excel does not raise events. If there are add-ins listening for Excel events, those add-ins consume resources on the computer as they record the events. If it is not necessary for the add-in to record the events that occur while your code runs, turning off events improves performance.

  • ActiveSheet.DisplayPageBreaks

    Turn off page breaks. If ActiveSheet.DisplayPageBreaks is set to False, Excel does not display page breaks. It is not necessary to recalculate page breaks while your code runs, and calculating the page breaks after the code executes improves performance.

Read more about that in the Excel performance: Tips for optimizing performance obstructions article.

Also I'd recommend releasing underlying COM objects. All Excel add-ins should systematically release their references to Excel objects when they are no longer needed. Failing to systematically release reference to Excel objects can prevent Microsoft Office Excel from shutting down properly.

Use System.Runtime.InteropServices.Marshal.ReleaseComObject to release an Excel object when you have finished using it. Then set a variable to Nothing in Visual Basic (null in C#) to release the reference to the object.

Upvotes: 5

Related Questions