.Net Excel Interop Multiple Columns Delete with range very slow

Question

I am using below code to delete multiple columns with range by using Excel Interop library. It is taking much time to delete the entire columns. Is there any other alternate ways to delete the multiple columns very quickly.

Excel.Application _excel = Globals.ThisAddIn.Application;
Excel.Workbook sourceWorkbook = _excel.ActiveWorkbook;
_excel.Application.ScreenUpdating = false;
_wb = _excel.Workbooks.Add(Type.Missing);
_wb.Activate();
_wb.Windows[1].Visible = true;
_wb.Application.ScreenUpdating = false;
_wb.Application.DisplayAlerts = false;
for (int i = 1; i <= sourceWorkbook.Sheets.Count; i++)
{
   sourceWorkbook.Sheets[i].Activate();
   sourceWorkbook.Sheets[i].Copy(Type.Missing, _wb.Sheets[_wb.Sheets.Count]);
   _wb.Activate();
   _wb.Sheets[_wb.Sheets.Count].Activate();
   if (_wb.Sheets[_wb.Sheets.Count].ProtectContents)
   {
      _wb.Sheets[_wb.Sheets.Count].Unprotect(AppConstants.Password);
   }
   _wb.Sheets[_wb.Sheets.Count].Columns["IP:XFD"].EntireColumn.Delete(); <-- Here the delete process is taking time
   if (!_wb.Sheets[_wb.Sheets.Count].ProtectContents)
   {
     _wb.Sheets[_wb.Sheets.Count].Protect(AppConstants.Password);
   }
}

Answer 1

Okay, since you specifically want to know how to do this fast, I think you have three options:

1. optimise your existing code. Some possibilities:

• Simplify the delete call:

_wb.Sheets[_wb.Sheets.Count].Columns["IP:XFD"].EntireColumn.Delete();

Since your Columns[] argument is already full-height columns ... I'm thinking the additional .EntireColumn dereference is superfluous. For the size of your data, that's definitely going to incur some overhead. I think this should function identically:

_wb.Sheets[_wb.Sheets.Count].Columns["IP:XFD"].Delete();

• @user246821 is totally right - free your memory, especially true if this is a function that you're going to run on multiple files and/or multiple times without restarting the addin.

• consider that every call to _wb.Sheets and _wb.Sheets.Count is inside the loop ... and these are not necessarily 'free'. Store the values in temporary variables if possible, then call those temps instead of dereferencing _wb and its components. Someone who has used Interop more than me will have to make further comment on how to store these and/or whether it is worthwhile.

• try not copying the workbook in sheete-sized bits inside your loop. Copying via the filesystem will clearly be faster. Can you start with a manually created copy of the complete file? Or could you do a single, complete workbook or file copy operation outside the loop? These would remove flipping back and forth amongst active objects, and may be you can avoid carrying two workbooks around in memory. As a bonus, if you copy the complete file ... maybe you can come up with a way to do the protect/unprotect in bulk?

2. Explore preparing or managing your data using PowerQuery. That may be faster and repeatable. That may not integrate with whatever else you are doing, however, and it's not horribly portable ... but it is a valid option.

3. Consider another library, language and/or converting formats. I think a best-case scenario for speed would be if your data is all plain text without formatting already, making your process:

• split out your XLS file to separate CSV files
• Truncate rows. I think you have some 260 columns to keep (hundreds of rows? thousands?) and 53 files ... , and you know C#, so I'm going to guess you could do this faster in C/C++.
• if you actually need it, recombine your CSV files into a single XLS file

Those are the main pathways that come to mind. Unless you are desperate and considering hardware upgrades!