Recent

Author Topic: How to keep the existing format  (Read 4087 times)

eye

  • Newbie
  • Posts: 6
How to keep the existing format
« on: March 06, 2018, 07:13:23 am »
Hi,

I had studied programming and wrote lot of code mainly at pascal but stopped about 25 years ago. All these time never wrote any code. So i had an excel file and needed to write code for an operation which was impossible to do it using excel formulas. At beginning i thought of learning vba coding but then i found fpspreadsheet so i prefered it.
I wrote the code, had no problem reading and getting the correct results. My problem is when i save the results at this workbook in a new sheet the others sheets and loosing their format, formulas. If i save the results to new file and copy  them by hand it is ok. So am i doing something wrong or missing something? Or maybe is it impossible to create a new sheet in an existing workbook without destroying the format of other sheets using fpspreadsheet?

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: How to keep the existing format
« Reply #1 on: March 06, 2018, 09:13:20 am »
So am i doing something wrong or missing something?
Certainly you are doing something wrong, but it's hard to tell from this description only without seeing your code. fpspreadsheet is conservative regarding formulas because an incompletely read formula should not crash the entire document. Therefore, reading of formulas is off by default. You must turn it on by adding boReadFormulas to the workbook.Options (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Formulas).
Code: Pascal  [Select][+][-]
  1. var
  2.   workbook: TsWorkbook;
  3. begin
  4.   workbook := TsWorkbook.Create;
  5.   try
  6.     workbook.Options := workbook.Options +  [boReadFormulas, boCalcBeforeSaving, boAutoCalc];
  7.     workook.ReadFromFile(...);
  8.     ...
« Last Edit: March 06, 2018, 09:15:51 am by wp »

eye

  • Newbie
  • Posts: 6
Re: How to keep the existing format
« Reply #2 on: March 06, 2018, 09:45:59 am »
I am not at the office to copy exactly the code but it was same like the one you wrote except the

workbook.Options := workbook.Options +  [boReadFormulas, boCalcBeforeSaving, boAutoCalc];

Then i used the workbook.GetFirstWorksheet to access the working sheet and the rest code was to read values from specific cells in order to do the needed calculations.

As i understand the above line will keep the formulas as it was. Other problems except formulas was at the formatting of cells. Some has conditional formatting so maybe this is not supported and also some hidden columns appeared again and some protected cells were not protected anymore. Will all these be fixed if i add this line in my code?

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: How to keep the existing format
« Reply #3 on: March 06, 2018, 09:56:13 am »
This line should fix the formulas - at least those supported (fpspreadsheet does not support all formulas which Excel offers), but nothing else.

The basic cell formats should be there (color, font, text justification etc). Conditional formatting is not supported, hidden columns neither. Cell protection should work,maybe you should use the trunk version, I am not sure if this is already in the current release version (and this reminds me that it's getting time to release a new version soon...).

Hidden columns should be easily implemented in principle, but might probably cause a major rewrite of the grid. No plans for conditional formatting. So, don't expect anything to happen here too soon.

eye

  • Newbie
  • Posts: 6
Re: How to keep the existing format
« Reply #4 on: March 06, 2018, 10:05:51 am »
Thanks for your reply. As i understand you are the one who developed fpsreadsheet. You did a good job. Do you know if there are other similar libraries or any other way i could probably do this?

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: How to keep the existing format
« Reply #5 on: March 06, 2018, 10:14:45 am »
If you have a very complex Excel document and you want to add something but keep the existing format, your best bet would be to use Excel itself via OLE Automation. In that case you can add anything you want in the sheets with the capabilities of Excel. The only downside (and it's a big one and the reason FPSpreadsheet probably exists) is that you need Excel on the computer where you run the program.

eye

  • Newbie
  • Posts: 6
Re: How to keep the existing format
« Reply #6 on: March 06, 2018, 10:22:51 am »
If you have a very complex Excel document and you want to add something but keep the existing format, your best bet would be to use Excel itself via OLE Automation. In that case you can add anything you want in the sheets with the capabilities of Excel. The only downside (and it's a big one and the reason FPSpreadsheet probably exists) is that you need Excel on the computer where you run the program.

That's not big deal at all !! Hope not many changes will be needed in code

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: How to keep the existing format
« Reply #7 on: March 06, 2018, 10:31:31 am »
I never tried this one: http://avemey.com/zexmlss/index.php?lang=en, but they mention "conditional formatting".

http://www.kluug.net/xlsx-ods-delphi.php appears to be rather complete, but is not free.

As for OLE automation: Good luck, some of my gray hair is due to it... If you understand German or can machine-translate it this page may be helpful: http://wiki.lazarus.freepascal.org/ExcelAutomation/de

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: How to keep the existing format
« Reply #8 on: March 06, 2018, 10:40:40 am »
As for OLE automation: Good luck, some of my gray hair is due to it...
Yes, sometimes it can be problematic  :'(

But for small changes to a very complex Excel-file, or adding a sheet to it, it's probably the fastest way to go. Especially if having Excel on the computer already isn't a problem. (Although your other suggestions seem a good option in the long run too.)

I usually resort to recording the changes I want in a macro (in Excel itself) and looking at that macro to translate the commands to OLE-Automation. That's the easiest way.

wofs

  • New Member
  • *
  • Posts: 16
Re: How to keep the existing format
« Reply #9 on: March 26, 2018, 08:01:19 am »
I never tried this one: http://avemey.com/zexmlss/index.php?lang=en, but they mention "conditional formatting".
By the way, it uses a very efficient XML parser.
« Last Edit: March 26, 2018, 08:04:17 am by wofs »
-----
Best Regards, Alexander D.

 

TinyPortal © 2005-2018