SaveExcel is very slow

Posted by: naeron84 on 8 September 2017, 2:35 pm EST

  • Posted 8 September 2017, 2:35 pm EST

    Hi, I'm using FpSpread for winforms 5.0.3518.

    FpSpread.SaveExcel(string fileName, ExcelSaveFlags saveFlags); is very slow (sometimes 2 mins). Only using ExcelSaveFlags.UseOOXMLFormat.

    I've recently upgraded from 3511.

    The average file itself contains about 20 sheets and not so much data. So the amount of data is far below average. 

    Besides that I have no clue what is causing this issue.   

  • Replied 8 September 2017, 2:35 pm EST

    Hello,

    I exported a SpreadSheet to excel which has 20 sheets with a lots of data. The size of the exported excel file is 1.90 MB and it took 8 seconds to export which is reasonable.Could you please tell me the size of the exported file and also if possible please post the sample application to debug?

     

    Thanks,

     

     

     

  • Replied 8 September 2017, 2:35 pm EST

    Its size is only about a few hundred KB.

     We are performing read's and writes's before the export. We are using the following methods:

    -SheetView.GetArray()

    -SheetView.SetActiveCell()

    -SheetView.ActiveCell.Value  READ & WRITE 

    -SheetView.ActiveCell.CellType WRITE 

    -SheetView.ActiveCell.Locked WRITE

    -SheetView.ClearRange

     

    I cannot share the source code because it's too complicated and I can't identify any code sections as critical or relevant. Basically we are just using the above listed methods(this is fast, about 1 sec) and after this comes the SaveExcel which is very slow. I assume SaveExcel could not possibly depend on any previous method calls, it just converts and saves data. Is this assumption wrong and does it involve flushing some cache(s)?

    This problem is a blocking one, the user will not wait for 2 or 3 minutes just to open an excel file. 

     

  • Replied 8 September 2017, 2:35 pm EST

    Hi,

    Can you add some code to save the FpSpread to XML and send us this file to examine?  It will save the state of the workbook, and we can load it here to test export to XLSX and do detailed performance analysis.  You can post it here, or email it to powersupport@gcpowertools.com (please reference this thread in the email).

  • Replied 8 September 2017, 2:35 pm EST

    Sorry, I've been busy. I will send the xml soon.
  • Replied 8 September 2017, 2:35 pm EST

    Hello,

    Thanks for the XML file over email. We are following up your case over email now.

    Thanks.

  • Replied 8 September 2017, 2:35 pm EST

    I had a similar problem. A Spread with 850 rows and 80 columns and differnet colors, bordrs and celltypes needed 8 min for ExcelSave.


    In my case my fault was to assign CellType to each single cell like the following example:
    for each cell in sheet.cells
       dim cellTypeX as new New CellType.NumberCellType()
       cellTypeX.DecimalPlaces = 3
       cell.cellType = cellTypeX
       ...
    next
    fp.saveExcel(...)
    The export was slow and and in Excel an error ocurred when opening the file something like "too many formatting ...". The size of the excel file was over 3 MB, not all formatting was correct

    Then I created ONE Celltype for each purpose in my module/class, initialized the celltypes once and then assigned is to the cells. SaveExcel then needed 30 sec. the size was only 600 KB, all formatting was correct.


    Private cellTypeX as new New CellType.NumberCellType()
    private sub InitSellTypes()
       cellTypeX.DecimalPlaces = 3
    end sub
    for each cell in sheet.cells
       cell.cellType = cellTypeX
       ...
    next
    This may be trivial for the most of you, but because I was used to the COM version of fpSpread I was not so familiar with CellType objects.


    Wilfried


     

  • Replied 8 September 2017, 2:35 pm EST

    HI,

    Thanks for sharing your observations with us & letting us know that you issues has been resolved

    Feel free to revert incase you have additional questions

    Regards,

    ~Gatim

     

     

  • Replied 8 September 2017, 2:35 pm EST

    Hello,

    I see no impact on performance using these functions/properties  just before export. I am simply using the code as follows :

    FpSpread1.Sheets.Count=20

    im ias As FarPoint.Win.Spread.Model.IArraySupport

    ias = FpSpread1.Sheets(0).Models.Data

    Dim arr(,) As Object

    arr = ias.GetArray(0, 0, 1, 7)

    FpSpread1.Sheets(0).SetActiveCell(2, 2)

    FpSpread1.Sheets(0).ClearRange(0,0,5,5,True,FarPoint.Win.Spread.ClipboardCopyOptions.Values)

    FpSpread1.SaveExcel("c:\Spread 5\Excelfile.xls")

    Is it possible to recreate this issue with a small sample application? If yes please post that application to us for further investigation.

    Thanks,

     

Need extra support?

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

Learn More

Forum Channels