Conditional Formatting Results in Very Slow OpenExcel

Posted by: lars-fp on 8 September 2017, 2:45 pm EST

  • Posted 8 September 2017, 2:45 pm EST

    I encountered a number of Excel files that were taking an extraordinarily long time to open.   Via process of elimination I determined that it was due to conditional formatting being applied to an entire column.

    By default Excel shows 68 thousand rows.   If the user applied conditional formatting on an entire column, even though there may be only a couple of hundred rows containing actual data, the OpenExcel command appears to process all 68 thousand rows.   If I remove the conditional formatting and make sure it is only applied to the rows containing data, the import action occurs as the expected speed.

    Applying conditional formatting for an entire column is a fairly common action for Excel users.   Spread should really ignore these 68 thousand empty row and import at the usual speed.

    (Note that using the "DataOnly" flag also solves this issue, however this is an inadequate solution as one loses other important data such as formatted dates, etc.)

  • Replied 8 September 2017, 2:45 pm EST

    Can you send me an example excel sheet that gives you this issue?
  • Replied 8 September 2017, 2:45 pm EST

    Thanks, Paul.   Unfortunately the TruncateEmptyRowsAndColumns flag does not solve this issue as the Spread OpenExcel function does the truncation *after* it has finished the import.  Thus means it still processes the 65k lines first and then truncates, so the load action still takes an extraordinary amount of time.  (Can be 10-15 minutes).
  • Replied 8 September 2017, 2:45 pm EST

    In Spread for Winforms and Spread for Asp.net we have OpenExcelFlags.TruncateEmptyRowsAndColumns flag which would do this for you. Its an enhancement we are requesting for Spread for COM as well but it is not currently supported.
  • Replied 8 September 2017, 2:45 pm EST

    Paul -

    I'll send you a sample file, but I'm not sure how to attach a file on the forum.  Can you provide instructions?

     Thanks again for taking a look at this.

    - lars

  • Replied 8 September 2017, 2:45 pm EST

    Hello,

    Applying conditional formatting to whole column which contains 68000 rows will be slower. When you apply formatting to the whole column it does make blank cells into account too after checking the cell'data it comes to know that the cell is blank.In order to make the process faster you may only apply formatting to the data area. You can get last row that contains data using "DataRowCnt"  property. You are already aware of the second work around.

     

    Thanks,

     

     

  • Replied 8 September 2017, 2:45 pm EST

    Thank you for the quick reply Deepak.

    Unfortunately, this isn't an adequate solution for us. We are importing sheets provided by 3rd parties and have no control about how those sheets are formatted.  It is common practice in Excel to apply conditional formatting to an entire column, rather than to select just the cells with data.

    Would it not make more sense for Spread to detect the last row containing data in Excel *before* processing the 68k rows?    It seems like Spread should handle this case gracefully as we've found it to be quite common.

    Getting the last row with data in Excel is quite easy (VBA code):

    lastRow = ActivSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

    Thanks,

    - Lars

  • Replied 8 September 2017, 2:45 pm EST

    when you are posting there is an options tab above with a File/Attachment Add/Udate button.
  • Replied 8 September 2017, 2:45 pm EST

    I think I've nailed down the bug.

    At the link below you'll find 2 Excel files each containing only 8 cells with data.

      The first takes 4 seconds to open

      The second takes 45 seconds to open (despite also containing 8 cells with data)

    The difference between the two is that in the second (slow) file the first row does *not* contain data validation.   In the first (fast) file *all* rows contain the same data validation.

    Hopefully this will help you get closer to resolving this issue.

    https://docs.google.com/open?id=0B6VzeSi0k75hZTVmNjg3NjAtNmNmMS00NmJhLTgxODItNTRjNDcyZjIwNzNl

    thanks,

    - Lars

     

  • Replied 8 September 2017, 2:45 pm EST

    Hello,

    Using Spread 6 for .Net latest build it is loading the excel file in just 1 minute or less. May I know what version of Spread are you using?

     

     

    Thanks,

     

  • Replied 8 September 2017, 2:45 pm EST

    I've updated to the latest patch for Spread 5 (05.00.3522) and the problem persists.   Thoughts?
  • Replied 8 September 2017, 2:45 pm EST

    Hello,

    I tested this issue with Spread 6 latest build and I see that excel file with header takes 18 seconds where the other excel file with  no headers takes only 3 seconds.I would like to know how did you created both these excel files?

     

    Thanks,

     

  • Replied 8 September 2017, 2:45 pm EST

    Hello,

    I tested this with the latest build of Spread 5 and I see that it takes around 1 minutes only to load the excel file. Could you please tell me how much time it is taking at your end to load? I do not see a way to truncate the rows before loading the excel file.

     

    Thanks,

     

  • Replied 8 September 2017, 2:45 pm EST

    Paul -

    You can get to a sample document here:

    https://docs.google.com/leaf?id=0B6VzeSi0k75hMzMxZWMxZTgtMjY0My00MzEyLWE5M2ItNDlhODExZWM2YWFi&hl=en_US

     Thanks!

    - Lars

  • Replied 8 September 2017, 2:45 pm EST

    Hello,

    I tested this with Spread 6. Yes I used the excel files that you provided link for. The files which I downloaded are

    "Example - ComboBox - NO HEADER.xls"

    "Example - ComboBox - HEADER.xls"

    Excel file with Header takes longer than the excel file without header. I do not see how you have applied conditional formatting in both the files. Also let me know how did you create these excel files?

     

    Thanks,

     

  • Replied 8 September 2017, 2:45 pm EST

    I'm using the latest version of Spread 5.    Did you happen to try with the files that I referenced?
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels