Processing large Excel files using multiple threads

2
votes

I am using c# VSTO and Interop lib for developing excel add-in. for validating each cell's value before pasting these values into another workbook with formatting. My actual query is to process, (in fastest possible way) cells with some criteria.

I have used WorkSheet.Cells.SpecialCells() for getting Excel.Range objects of my interest and use threads for processing Excel.Range (which is returned by SpecialCells())  simultaneously. Following are the some of the observations/issues:

  1. It seems Excel.Range can not be split based on offset and length (i.e I cant get the new Range object from existing Range based on some offset and count)
  2. If we shared the range object in threads and try to process the cells in different batches we get following exception:  "The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))" This also leaves that batch of cells unprocessed.

Any inputs or pointers to resolve the aforementioned issues would be helpful. Also any suggestions on processing large excel files quickly, say in seconds (this is the biggest bottle neck for now)

1
c#excelmultithreadinginterop
I expect that the COM stuff that is going on in these calls to Range objects don't like multiple threads. If you're only requirement is reading and writing to a new worksheet, maybe look into OpenXML SDK or a C# fully managed Excel library like EPPLUS – rene
Thanks for the info . . .. I will look into both the options – Satish Jadhav

1 Answers

3
votes

Excel is essentially a single-threaded application (technically, the COM objects live in a Single-Threaded Apartment). That means any COM access gets automatically marshalled to the main thread, so there is no benefit in using extra thread to make COM calls.

For your use case, it would make sense to get the whole data array in a single call to Range.Value and then process this array further without using extra COM calls.

You might also have a look at this question for ideas on how to read and write the range data quickly, including an example that uses the Excel C API.

A different approach is to read the Excel data file directly, not interacting with the Excel application. For this you can use a high-level wrapper over the xml-based file format like ClosedXML.