[SOLVED] How to mark WorkSheet as Hidden?

[shobits1]

I'm using FPSpreadSheet to open a preMade xlsx file (made with EXCEL 2010)... do some changes then save the new file as xlsx again.

I have two apparent problem that FPSpreadSheet differs than EXCEL's behaviors:
1. The wordwarp of some cells is changed even thought I didn't change the cells... FPSpreadSheet sets wordwrap every cell that has long text to true.

2. The original xlsx file has a hidden worksheet but in the saved file this hidden worksheet is visible.

now:
for the 1st problem I did find work around by merging cells; but I can't seem to find any solution for hiding the worksheet.

Any idea?

[wp]

In general, if it is important that workbooks are not changed unintentionally you should use the automation features built into Excel (http://wiki.lazarus.freepascal.org/Office_Automation#Reading.2FWriting_an_Excel_file_using_OLE, or http://wiki.lazarus.freepascal.org/ExcelAutomation/de), don't use fpspreadsheet in this case. Excel has so many features, and fpspreadsheet did never have the intention to support all of them. Therefore, it cannot be avoided that features are lost if an existing worksheet is loaded into fpspreadsheet, modified and re-saved.

1. This is not true. Maybe your spreadsheet is preformatted with wordwraps, and the columns are wide enough to not see them. Column widths in Excel and fpspreadsheet may differ because I don't know how Excel calculates column widths exactly. Please post a file which shows this phenomenon that wordwraps are added by fpspreadsheet (of course, post the file before it is processed by fpspreadsheet).

2. Hidden worksheets, like hidden columns/rows, protected worsheets/cells, encrypted workbooks etc. are features which are not supported, and I don't have plans to add them soon. But if you submit a patch I'd be happy to look at it.

[shobits1]

I'm not sure about using OLE for now but I'll give it a try if I run out of options.

1. This is not true. Maybe your spreadsheet is preformatted with wordwraps, and the columns are wide enough to not see them.

I attached a sample file before and after processing with fpspreadsheet. (also a screenshots).
the code used is:

Code: Pascal  [Select][+][-]

1.   // OPEN FILE
2.   wsWorkBook.LoadFromSpreadsheetFile('D:\_Projects\SHelper\Exams_Original.xlsx');
3.  
4.  
5.   // SAVE FILE AS
6.   wsGrid.Workbook.WriteToFile('D:\_Projects\SHelper\Exams_Processed', True);
7.  

2. Hidden worksheets, like hidden columns/rows, protected worsheets/cells, encrypted workbooks etc. are features which are not supported, and I don't have plans to add them soon. But if you submit a patch I'd be happy to look at it.

I did quick comparison between the file with hidden worksheet and the one with visible one, and found that by adding the attribute `state="hidden"` to the desired worksheet in the `workbook.xml`will do the trick (hide the worksheet) ... maybe I'll try and patch the xlsx reader/writer to keep the hidden worksheets hidden.

Anyway.. if you have any link(s) that describes the xlsx format in simple and effective way that maybe helpful... I'll try and do what I can when I have time.

BTW, I'm using the latest version provided by Online Package Manager.

thank you.

[wp]

I did quick comparison between the file with hidden worksheet and the one with visible one, and found that by adding the attribute `state="hidden"` to the desired worksheet in the `workbook.xml`will do the trick (hide the worksheet) ... maybe I'll try and patch the xlsx reader/writer to keep the hidden worksheets hidden.

Since this looks easy I decided to do it. The worksheet Options (TsSheetOption) now have a setting for a sheet being hidden (soHidden). If the xlsx reader finds the attribute 'state="hidden"' in the sheet node of the workbook.xml file then this option is added to the worksheet's Options. The writer goes the other way: if the option is set then the attribute 'state="hidden"' is added to the sheet node.

All this seems to work. Please test. Please note that at the moment xlsx is the only format supporting this feature.

[EDIT]
Now working also for ods.
... And now also for BIFF5 and BIFF8 (no need for BIFF2, it does not support multiple sheets).

[shobits1]

It works fine for me 

Thank you.

[wp]

Please check also the wordwrap issue with the current trunk version. On Feb 18, there was a commit with the description "Fix xlsx reader incorrectly detecting word wrap."

[shobits1]

Yep it appears to work correctly (just did quick).

BTW,, could you update the FPSpreadSheet in the Online Package Manager.

[wp]

I am planning a release version soon which will go into the online package manager. But I don't want to have the trunk version there because sometimes code is not yet tested as much as it should be, and sometimes I have to make changes which break previous trunk commits, and this is easier with a version that not everybody is using.