none
Looking for fastest, most efficient way to handle large Excel files RRS feed

  • Question

  • I currently have my Excel AddIn up and running. Thanks so much to everybody. My current problem is one of fine tuning my apps for performance, which is currently abysmal

    Scenario:

    In order to have an Addin with small footprint, I am executing an external, WPF app to perform some verification (for instance: is the current Excel file a valid one for my purposes?). The technique described below has been helpful:

    https://dotnetthoughts.net/openxml-and-opening-a-file-in-read-only-mode/

    (the difference being that my external app is based on ClosedXML, but I could switch to OpenXML).

    Which library/approach would you recommend to perform this as fast as possible? I am actually opening a large Excel file twice, the 2nd time in read only mode. Is there a way to pass a stream of the current file (being processed by Interop) to the new, external process? Maybe I should start a new thread which inherits the stream?

    One of the links that I found says that it is best to read my file using SAX, as opposed to DOM. That is the kind of tip that I am looking.

    1K TIA


    Tuesday, July 10, 2018 7:42 PM

Answers

  • I am very glad to report that I ported my ClosedXML-based app (see screenshot above, implemented under WPF) to OpenXML and the results, while not qualifying as spectacular, are highly remarkable. The latter approach is about 4x faster.

    Tips:

        For writing spreadsheets ==> Use ClosedXML, it is much easier.

        For reading spreadsheets ==> Use OpenXML, it is much faster (and easy enough).

    Next task: how to fine-tune my OpenXML (use SAX!) so it runs even faster.

    Thanks again.


    Thursday, July 12, 2018 4:12 PM

All replies

  • Hello Travis Banger,

    Does the solution in your shared link for you? I think there is no most efficient but suitable solution. Solution works efficiently for others may be not work for you efficiently.

    And what would you do while performing verification? In my opinion, performing verification should not edit the verified object. Will you need edit the workbook while verifying it?

    Best Regards,

    Terry


    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.

    Wednesday, July 11, 2018 3:10 AM
  • Thanks so much for your interest, Terry: it is very appreciated.

    Allow me to provide further details. I have to deal with a massive spreadsheet format that will eventually be trimmed down to size, split in parts or even better: will go away being replaced by a database. To make things worse, our computers are a little old, underpowered so I need to conserve precious RAM within the already overburdened Excel process.

    I support several spreadsheet versions and therefore my app needs to know which version is currently under the hood. My distinguished colleagues who designed this format ages ago neglected to make it self-identified, with a version number inside a predetermined cell. Therefore, one of my buttons is in charge of going deep into the workbook, to determine its version and validity.

    When the user clicks on "Why Are Buttons Disabled" my external app (the one that I need to be superfast) is summoned, in read-only mode, and will provide the verdict below:

       

    The "Number of Items" must be consistent, or else I cannot possible help disseminate and multiply user error. I have no option but to disable most buttons. Hence the need for speed, as they said in the movie Top Gun.

    Thanks again.





    Thursday, July 12, 2018 3:57 PM
  • I am very glad to report that I ported my ClosedXML-based app (see screenshot above, implemented under WPF) to OpenXML and the results, while not qualifying as spectacular, are highly remarkable. The latter approach is about 4x faster.

    Tips:

        For writing spreadsheets ==> Use ClosedXML, it is much easier.

        For reading spreadsheets ==> Use OpenXML, it is much faster (and easy enough).

    Next task: how to fine-tune my OpenXML (use SAX!) so it runs even faster.

    Thanks again.


    Thursday, July 12, 2018 4:12 PM
  • Followup to this thread here:

    https://social.msdn.microsoft.com/Forums/en-US/e9972513-146a-41f2-a412-c803c057817e/found-for-fastest-most-efficient-way-to-handle-large-excel-files-need-to-make-it-even-faster?forum=vsto

    Thursday, July 12, 2018 4:34 PM
  • Hello Travis Banger,

    It seems your original issue has been resolved. I would suggest you mark the helpful reply or your solution to close  the thread.

    We will focus on your new issue in the new thread.

    Best Regards,

    Terry


    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.

    Friday, July 13, 2018 2:02 AM