Replace data in an existing spreadsheet

[eye]

I have created a spreadsheet in excel which has some data in one sheet and in the others sheets there are charts and tables based on these data.

Later I have created a pascal program to generate all the needed data and stores them to another spreadsheet. Is there any way to replace just the sheet with data in the spreadsheet I had without affecting the rest sheets and the charts and the tables to use the new data?

When I tried to read the file in order just to replace the data in the sheet I wanted plenty of stuff from other sheet were not recognized (charts, conditional format rules, some formulas). Is there any other way I can do it except copying the data manually?

[wp]

plenty of stuff from other sheet were not recognized (charts, conditional format rules, some formulas).

You should check out the svn version of FPSpreadsheet, it has (limited) chart support, conditional formats and some more formulas, maybe you have more success with it. But to be honest, I doubt it: FPSpreadsheet has never been planned to be a full-featured replacement for the big Office spreadsheet applications.

To get the svn version, use svn, or download the zipped snapshot from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/.

[eye]

plenty of stuff from other sheet were not recognized (charts, conditional format rules, some formulas).

You should check out the svn version of FPSpreadsheet, it has (limited) chart support, conditional formats and some more formulas, maybe you have more success with it. But to be honest, I doubt it: FPSpreadsheet has never been planned to be a full-featured replacement for the big Office spreadsheet applications.

To get the svn version, use svn, or download the zipped snapshot from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/.

I had seen it and it didn't help me. I could have implemented the conditional format rules with code in pascal but just to create a report that I don't need it very often it doesn't worth to spend so much time when I can do it much easier and much faster in Excel.

I thought that since fpspreadsheet reads .xslx files it could probably save exactly the same that it read with few changes to some cell values that doesn't contain formulas.

[wp]

Could you post a (simplified) project so that I can see what exactly you want to achieve.

In the attachment you find an example for a "template" xlsx file which plots the curve A * sin(f*x) where the values of A and f are stored in named cells on the data sheet. FPSpreadsheet reads the file, changes the "A" and "f" cells and stores it under a new name (containing the A and f values used). This works. But I found a few bugs along the way (and there are still some to-do items), and this means that if you want to test this project you need the latest svn version of FPSpreadsheet of today.

I thought that since fpspreadsheet reads .xslx files it could probably save exactly the same that it read with few changes to some cell values that doesn't contain formulas.

Well, this is not what FPSpreadsheet is doing. It reads an xlsx file and stores it into its own data structures (TsWorkbook, TsWorsheet, TCell, TsChart, etc) - and from that moment the input file is forgotten. When saving it writes a complely new xlsx file from these data structures. It is clear that the new file will not be a 1:1 copy of the original file, even if you do not make any changes.

Maybe you show go a different way: extract the xml files from the xlsx file (this is nothing but a zip), parse the xml, find the cell(s) that you want to change, do the change, pack the changed with the unchanged xml files back into a zip and save that as a new xlsx file.

I think I can work out a demo project for this, but again i need to know exactly what has to be done.

[eye]

Could you post a (simplified) project so that I can see what exactly you want to achieve.

In the attachment you find an example for a "template" xlsx file which plots the curve A * sin(f*x) where the values of A and f are stored in named cells on the data sheet. FPSpreadsheet reads the file, changes the "A" and "f" cells and stores it under a new name (containing the A and f values used). This works. But I found a few bugs along the way (and there are still some to-do items), and this means that if you want to test this project you need the latest svn version of FPSpreadsheet of today.

I thought that since fpspreadsheet reads .xslx files it could probably save exactly the same that it read with few changes to some cell values that doesn't contain formulas.

Well, this is not what FPSpreadsheet is doing. It reads an xlsx file and stores it into its own data structures (TsWorkbook, TsWorsheet, TCell, TsChart, etc) - and from that moment the input file is forgotten. When saving it writes a complely new xlsx file from these data structures. It is clear that the new file will not be a 1:1 copy of the original file, even if you do not make any changes.

Maybe you show go a different way: extract the xml files from the xlsx file (this is nothing but a zip), parse the xml, find the cell(s) that you want to change, do the change, pack the changed with the unchanged xml files back into a zip and save that as a new xlsx file.

I think I can work out a demo project for this, but again i need to know exactly what has to be done.

A simplified demo is a sheet1 containing student names in first row and grades of 5 courses for each student in the next 5 rows. Sheet2 puts the data of sheet1 in a table with borders and highlights the best grade at each course (conditional format). Sheets 3 does the same like sheet2 but highlights the best grade of each student. So what i want is at next semester only to change the data of sheet 1 and to have my reports ready.

That's a very simplified example. My program does all the needed calculations and exports just data like in sheet1 of the example. From these data I am creating around 50 different tables and 5 charts. What I am doing now is the data I get in a spreadsheet from my program I am copying them to a kind of template spreadsheet. I could also set the template file to import from the created spreadsheet but I was wondering if this could be done automatically from my program.

I will test your approach from your demo and will tell you if worked or not. In any case if you could find a way to replace just some data in excel files that we will use them as patterns it would be something great

[Roni Wolf]

Hello, eye!

For your needs, it may not even be necessary to use FPSpreadsheet, as wp said, manipulating the XML directly may be more practical. I suggest using the XMLReader sample program and opening a spreadsheet, it is not very complicated. As a test, rename a spreadsheet to ".ZIP", extract the contents to a folder and with the XMLReader sample program open the XML of the spreadsheet you want to modify. This XML will be in the path "xl\worksheets" inside the folder where you unzipped the XLSX file. I think this approach may indicate the way forward. Although the XMLSReader sample program only reads the file, I believe it can be modified to be able to write as well. Another idea would be to generate a spreadsheet with the FPSpreadsheet containing your first sheet and replace the sheet of your template file ("sheet1.xml" for example). I have never tested this procedure, but I think it would work.

I hope this helps you in some way.

Roni.

[wp]

A simplified demo is a sheet1 containing student names in first row and grades of 5 courses for each student in the next 5 rows. Sheet2 puts the data of sheet1 in a table with borders and highlights the best grade at each course (conditional format). Sheets 3 does the same like sheet2 but highlights the best grade of each student. So what i want is at next semester only to change the data of sheet 1 and to have my reports ready.

Please prepare an xlsx file with some dummy data showing this principal structure. I would like to know how you take care of a variing number of students in your evaluations.