Recent

Author Topic: FPSpreadsheet + Existing Excel file NOT Saving correctly  (Read 2163 times)

Mascarian

  • New member
  • *
  • Posts: 6
FPSpreadsheet + Existing Excel file NOT Saving correctly
« on: November 30, 2018, 03:45:31 pm »
Greetings,

I apologize in advance if this has been answered already. I looked but couldn't find an answer to the problem I am facing.


OBJECTIVE:
Open an existing excel file (template), this file has two tabs, the (Data) tab which is where the data get written to, and the (Report) tab, which contain a chart control, title, etc.

METHOD USED:
1 - Workbook (Create)
2 - ReadFromFile (open the existing file/template)
3 - GetWorkSheetByName (the "data" sheet)
4 - OnWriteCellData (populate the data in the "data" sheet)
5 - WriteToFile (Save + Overwrite) -- this would be a new file, with different name + path, not the template file

ISSUE:
No errors encountered, however, when I open the new (saved) file, I see the data sheet contains all the data (expected behavior), however, the "Report" sheet is blank (the chart is gone, and the text is gone)!

DESIRED OUTCOME:
Retain the text and the chart control with all the formatting (including cell merging), and formulas.


Any ideas/Suggesting/Help would be much appreciated.

Thank you


SeregaKR

  • New member
  • *
  • Posts: 35
Re: FPSpreadsheet + Existing Excel file NOT Saving correctly
« Reply #1 on: November 30, 2018, 04:00:59 pm »
I suggest to copy the template in order to create the file with the name you need. Then open the copy and write the data in the existing file on the data tab

SeregaKR

  • New member
  • *
  • Posts: 35
Re: FPSpreadsheet + Existing Excel file NOT Saving correctly
« Reply #2 on: November 30, 2018, 04:02:42 pm »
I needed to do the similar thing - it's easier to write something to existing file than copying the entire content in the new one.
You can use CopyFile in Lazarus to make file copy

Mascarian

  • New member
  • *
  • Posts: 6
Re: FPSpreadsheet + Existing Excel file NOT Saving correctly
« Reply #3 on: November 30, 2018, 04:36:13 pm »
I suggest to copy the template in order to create the file with the name you need. Then open the copy and write the data in the existing file on the data tab

You know what, come to think about it, that is an excellent idea! I will try it and let you know. Many thanks.

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: FPSpreadsheet + Existing Excel file NOT Saving correctly
« Reply #4 on: November 30, 2018, 04:37:57 pm »
the chart is gone, and the text is gone
fpspreadsheet does not support charts, it does neither read nor write them. So, when you read a file with a chart and save it, even without changing it, the chart will be gone.

What is "the text"?
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Mascarian

  • New member
  • *
  • Posts: 6
Re: FPSpreadsheet + Existing Excel file NOT Saving correctly
« Reply #5 on: November 30, 2018, 05:01:22 pm »
the chart is gone, and the text is gone
fpspreadsheet does not support charts, it does neither read nor write them. So, when you read a file with a chart and save it, even without changing it, the chart will be gone.

What is "the text"?

wp,

Thanks for letting me know that charts are not supported. This leaves me with the OLE route essentially, which will likely perform poorly with 100K+ rows, and that is an absolute bummer $%$@%*%$@#

Any ideas of possible future support of Excel Charts?

oh, and "Text" is simply (plain) text I had in different cells throughout the sheet, however, that won't matter much, as chart support is a mandatory business requirement by the client.

Thanks a lot :)

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: FPSpreadsheet + Existing Excel file NOT Saving correctly
« Reply #6 on: November 30, 2018, 10:00:24 pm »
Any ideas of possible future support of Excel Charts?
Probably not soon. There are SO MANY options...
« Last Edit: December 01, 2018, 12:59:39 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Mascarian

  • New member
  • *
  • Posts: 6
Re: FPSpreadsheet + Existing Excel file NOT Saving correctly
« Reply #7 on: December 01, 2018, 08:23:31 pm »
Any ideas of possible future support of Excel Charts?
There are SO MANY options...

Could you please suggest/recommend some? Thanks!

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: FPSpreadsheet + Existing Excel file NOT Saving correctly
« Reply #8 on: December 02, 2018, 12:03:47 am »
No, I meant: So many options are available in a chart, it is quite complex to make everything work. And the file structure of charts in Excel is a desaster - I just looked at it again. ODS seems to be easier, and this motivated me to setup a basic architecture for charts in fpspreadsheet. I can't promise anything, it may be wrong from the beginning.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10