none
Processing large Excel files using multiple threads RRS feed

  • Question

  • 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 I shared the range object in threads and try to process the cells in different batches I getting 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)
     
    Monday, December 26, 2016 9:00 AM

All replies

  • Hi Satish E Jadhav,

    Ms Office application uses single thread apartment. so it is possible that because of that reason you got the error.

    you had mentioned that you want to process large file but you did not mentioned how much large it is.

    you are trying to use threads so it is possible that workbook contains thousands of records.

    if you try to use threads and try to check each cell then also it will take much time. processing time will be differ on different machines.

    so as you said that you want to complete this operation in seconds then it will not work like that.

    it needs the expected time to complete the operation. also it depends what operation you done on single cell.

    if you are performing more then one operations on single cell then overall execution time will be increase.

    so try to complete your operation with minimum task on single cell. so that you can get a better performance.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 27, 2016 2:53 AM
    Moderator
  • Hello Satish,

    You may try to use the following properties:

    - ScreenUpdating - Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.

    - Calculation - a XlCalculation value that represents the calculation mode.

    Also you may consider using Open XML SDK for dealing with open XML documents, see Welcome to the Open XML SDK 2.5 for Office for more information. 


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Tuesday, January 31, 2017 10:17 PM