Is it possible to hide rows or why shows LibreOffice rows with height zero?

[Soner]

Is it possible to hide rows or why shows LibreOffice rows with height zero?

I could not found the function for hiding rows so I set the height to zero:
Sheet1.WriteRowHeight(r,0, suPoints);
This works with Excel and Planmaker but LibreOffice ignoring it.
I am doing this because I dont want print some rows.

When I set in LibreOffice row height to 0 save and open it, then it has row height 0.
But LibreOffice does not accept 0 from fpspreadsheet.

I tried all possibilities:

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

1. Sheet1.WriteRowHeight(r,0, suPoints);      //libreoffice ignores it
2. Sheet1.WriteRowHeight(r,0, suMillimeters); //libreoffice ignores it
3. Sheet1.WriteRowHeight(r,0, suCentimeters); //libreoffice ignores it
4. Sheet1.WriteRowHeight(r,0, suInches);      //libreoffice ignores it
5. Sheet1.WriteRowHeight(r,0, suChars);       //libreoffice ignores it
6. Sheet1.WriteRowHeight(r,0, suLines);       //libreoffice ignores it
7.  
8. Sheet1.WriteRowHeight(r,0); //libreoffice makes it 0,11 cm
9.  

(Fileformat is xlsx saved from fpspreadsheet)

[wp]

It seems that LibreOffice does not write zero height rows, but writes the attribute 'table:visibility="collapse"' to the row nodes in this case.

I extended the TRow and TCol records by a "Hidden" fields and provided worksheet methods to hide/show rows/columns and to check their hidden state:

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

1.   procedure TsWorksheet.HideCol(ACol: Cardinal);
2.   procedure TsWorksheet.HideRow(ARow: Cardinal);
3.   procedure TsWorksheet.ShowCol(ACol: Cardinal);
4.   procedure TsWorksheet.ShowRow(ARow: Cardinal);
5.   function TsWorksheet.ColHidden(ACol: Cardinal): Boolean;
6.   function TsWorksheet.RowHidden(ARow: Cardinal): Boolean;

Writing is implemented for ODS, XLSX, XLS (BIFF8 and BIFF5, not BIFF2 which does not seem to support hidden rows/cols).

Reading is not yet implemented. And the WorksheetGrid still ignores the hidden state.

[wp]

Reading support for hidden rows/columns in ODS, XLSX and XLS (BIFF8 and BIFF5) formats added. Please test (I did not do too much testing myself...).

Note that the worksheet grid ignores the hidden state so far, I still have to make up my mind how to accomplish this because truely hiding rows/columns will mess up all row/column indexes. Setting col widths/row heights to zero?

[rvk]

Setting col widths/row heights to zero?

Yes. StringGrid1.RowHeights[ARow] := 0; works just fine with a TStringGrid.
Resizing the row in runtime to 0 also works with TStringGrid just like it does in LibreOffice.

[wp]

Implemented hidden rows/columns in the worksheet grid by setting their height/width to zero. Seems to work, but thorough testing needed. Looks too simple to be true...

[Soner]

Thanks.
Now, I use hiderow in my application and row is hidden when I save it as xls, xlsx and ods format and open with LibreOffice, Excel and Planmaker, that works fine.
But when I open the xls file with spready and save it again as xls file then all rows are hidden in saved file. Just as info for you, I dont really need this because I will switch to xlsx format.

[wp]

Sorry I cannot reproduce. The following code creates some test files with some dummy content and hidden row 2 and hidden column 2 (an re-reads them to check the hidden state). If I load the xls file (test.xls) into spready, re-save it as "test-resaved.xls" and load it again, then it appears to be complete.

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

1. program demo_hide_rows_cols;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   SysUtils, fpspreadsheet, fpstypes, fpsallformats;
7.  
8. const
9.   HIDDEN_COL = 2;
10.   HIDDEN_ROW = 2;
11.  
12. procedure WriteTest;
13. var
14.   b: TsWorkbook;
15.   sh: TsWorksheet;
16. begin
17.   b := TsWorkbook.Create;
18.   try
19.     Writeln('Writing test files...');
20.     sh := b.AddWorksheet('Test');
21.     sh.WriteNumber(0, 0, 1024);
22.     sh.WriteNumber(HIDDEN_ROW-1, HIDDEN_COL-1, 123);
23.     sh.WriteText(HIDDEN_ROW,   HIDDEN_COL, 'This is hidden');
24.     sh.WriteNumber(HIDDEN_ROW+1, HIDDEN_COL+1, 1.0);
25.     sh.HideCol(HIDDEN_COL);
26.     sh.HideRow(HIDDEN_ROW);
27.     b.WriteToFile('test.ods', sfOpenDocument, true);
28.     b.WriteToFile('test.xlsx', sfOOXML, true);
29.     b.WriteToFile('test.xls', sfExcel8, true);
30.     b.WriteToFile('test-5.xls', sfExcel5, true);
31.     WriteLn;
32.   finally
33.     b.Free;
34.   end;
35. end;
36.  
37. procedure ReadTest(AFileName: String; AFormat: TsSpreadsheetFormat);
38. var
39.   b: TsWorkbook;
40.   sh: TsWorksheet;
41. begin
42.   WriteLn('Reading "', AFileName, '" (', AFormat, ')...');
43.   b := TsWorkbook.Create;
44.   try
45.     b.ReadFromFile(AFileName, AFormat);
46.     sh := b.GetFirstWorksheet;
47.     Write('Column ', HIDDEN_COL, ' is ');
48.     if sh.ColHidden(HIDDEN_COL) then WriteLn('hidden') else WriteLn('visible');
49.     Write('Row ', HIDDEN_ROW, ' is ');
50.     if sh.RowHidden(HIDDEN_ROW) then WriteLn('hidden') else WriteLn('visible');
51.     WriteLn;
52.   finally
53.     b.Free;
54.   end;
55. end;
56.  
57. begin
58.   WriteTest;
59.  
60.   ReadTest('test.ods', sfOpenDocument);
61.   ReadTest('test.xlsx', sfOOXML);
62.   ReadTest('test.xls', sfExcel8);
63.   ReadTest('test-5.xls', sfExcel5);
64.  
65.   WriteLn('Press ENTER to close...');
66.   ReadLn;
67. end.

[Soner]

@wp
Your test is wrong. You must create file, open same file, save same file. (sfExcel8-format)

I changed your example to:

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

1. program fpsprdxlsrowhide;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   SysUtils, fpspreadsheet, fpstypes, fpsallformats;
7.  
8. const
9.   HIDDEN_COL = 2;
10.   HIDDEN_ROW = 2;
11.  
12. procedure WriteTest;
13. var
14.   b: TsWorkbook;
15.   sh: TsWorksheet;
16. begin
17.   b := TsWorkbook.Create;
18.   try
19.     Writeln('Writing test files...');
20.     sh := b.AddWorksheet('Test');
21.     sh.WriteNumber(0, 0, 1024);
22.     sh.WriteNumber(HIDDEN_ROW-1, HIDDEN_COL-1, 123);
23.     sh.WriteText(HIDDEN_ROW,   HIDDEN_COL, 'This is hidden');
24.     sh.WriteNumber(HIDDEN_ROW+1, HIDDEN_COL+1, 1.0);
25.     sh.HideCol(HIDDEN_COL);
26.     sh.HideRow(HIDDEN_ROW);
27.     b.WriteToFile('test.xls', sfExcel8, true);
28.     WriteLn;
29.   finally
30.     b.Free;
31.   end;
32. end;
33.  
34. procedure WriteTest2(openfile, savefile: string);
35. var
36.   b: TsWorkbook;
37.   sh: TsWorksheet;
38. begin
39.   b := TsWorkbook.Create;
40.   try
41.     b.ReadFromFile(openfile, sfExcel8);
42.     Writeln('Writing test files...');
43.     sh := b.GetFirstWorksheet;
44.     sh.WriteNumber(0, 0, 2094);
45.     b.WriteToFile(savefile, sfExcel8, true);
46.  
47.     WriteLn;
48.   finally
49.     b.Free;
50.   end;
51. end;
52.  
53.  
54. begin
55.   //create file as xls
56.   WriteTest;
57.   //open file as xls and save it as xls. And result is ok!
58.   WriteTest2('test.xls','test.xls');
59.  
60.   // open save verdana test. And result is not ok, why?
61.   // openspreadsheet with libre office or excel
62.   // All rows with content are hidden.
63.   WriteTest2('stdplan-verdana-row40hidden.xls','stdplan-verdana-row40hidden-savefromdemo.xls');
64.  
65.  
66.   WriteLn('Press ENTER to close...');
67.   ReadLn;
68. end.
69.  

The error only visible with my file, example code from above is okay.
I added my file and demo as attachment.

[Soner]

I found another issue. When you save stdplan-verdana-row40hidden.xls from above in spready you have different size than from demo.
stdplan-verdana-row40hidden.xls saved from spready is 21 KB and saved from demo above is 14KB

[Soner]

@wp
I think I found the error with hidden rows.
The reading of hidden rows in xls-format must wrong. Because when you save this file as ods or xlsx-format then all rows is again hidden.

I changed the example a little bit:

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

1. program fpsprdxlsrowhide;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   SysUtils, fpspreadsheet, fpstypes, fpsallformats;
7.  
8. const
9.   HIDDEN_COL = 2;
10.   HIDDEN_ROW = 2;
11.  
12. procedure WriteTest;
13. var
14.   b: TsWorkbook;
15.   sh: TsWorksheet;
16. begin
17.   b := TsWorkbook.Create;
18.   try
19.     Writeln('Writing test files...');
20.     sh := b.AddWorksheet('Test');
21.     sh.WriteNumber(0, 0, 1024);
22.     sh.WriteNumber(HIDDEN_ROW-1, HIDDEN_COL-1, 123);
23.     sh.WriteText(HIDDEN_ROW,   HIDDEN_COL, 'This is hidden');
24.     sh.WriteNumber(HIDDEN_ROW+1, HIDDEN_COL+1, 1.0);
25.     sh.HideCol(HIDDEN_COL);
26.     sh.HideRow(HIDDEN_ROW);
27.     b.WriteToFile('test.xls', sfExcel8, true);
28.     WriteLn;
29.   finally
30.     b.Free;
31.   end;
32. end;
33.  
34. procedure WriteWithFormat(openfile, savefile: string; AFormat: TsSpreadsheetFormat);
35. var
36.   b: TsWorkbook;
37.   sh: TsWorksheet;
38. begin
39.   b := TsWorkbook.Create;
40.   try
41.     b.ReadFromFile(openfile, sfExcel8);
42.     Writeln('Writing test files...');
43.     sh := b.GetFirstWorksheet;
44.     sh.WriteNumber(0, 0, 2094);
45.     b.WriteToFile(savefile, AFormat, true);
46.  
47.     WriteLn;
48.   finally
49.     b.Free;
50.   end;
51. end;
52.  
53.  
54. begin
55.   //create file as xls
56.   WriteTest;
57.   //open file as xls and save it as xls
58.   WriteTest2('test.xls','test.xls');
59.  
60.   // open save verdana test
61.   // openspreadsheet with libre office or excel
62.   // are rows with content are hidden.
63.   WriteWithFormat('stdplan-verdana-row40hidden.xls','stdplan-verdana-row40hidden-savefromdemo.xls', sfExcel8);
64.  
65.   WriteWithFormat('stdplan-verdana-row40hidden.xls','stdplan-verdana-row40hidden-savefromdemo.xlsx',sfOOXML);
66.   WriteWithFormat('stdplan-verdana-row40hidden.xls','stdplan-verdana-row40hidden-savefromdemo.ods',sfOpenDocument);
67.  
68.  
69.   WriteLn('Press ENTER to close...');
70.   ReadLn;
71. end.
72.  

And it happens when hidden rows contains formulas.

Here is simple files created with excel. I removed formulas in one file from hidden rows and this file has no problems when i save it from spready.

You must open and save the files from spready.

[wp]

I still cannot reproduce. But I found some suspicious code in xlscommon TsSpreadBiffReader.ReadRowInfo:

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

1. if DWordLEToN(rowrec.Flags) and $00000020 <> then
2.   lRow.Hidden := true;

This code does not reset lRow.Hidden to false if that hidden bit is not set in the rowrec.Flags!

Please try the new revision.

[Soner]

I still cannot reproduce.

Have you opened the file "stdplan-verdana-row40hidden.xls" or  "meintest.xls" from Spready and saved it again as xls format
then have you opened from spready save file in LibreOffice/Excel and the spreadsheet looked normal?

Did you forget to recompile spready after recent changes in fpspreadsheet?

Strange, maybe it happens only on my computer.

Here is some other test: (you need meintest.xls from above)

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

1. program fpsprdxlsrowhide;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   SysUtils, fpspreadsheet, fpstypes, fpsallformats;
7.  
8.  
9. procedure WriteTest;
10. var
11.   b: TsWorkbook;
12.   sh: TsWorksheet;
13. begin
14.   b := TsWorkbook.Create;
15.   try
16.     Writeln('Creating test files...');
17.     sh := b.AddWorksheet('Test');
18.  
19.     sh.WriteText(0, 0, 'Next row is hidden');
20.  
21.     sh.WriteText(1, 0, 'this row');
22.     sh.WriteNumber(1,1,1);
23.     sh.WriteFormula(1,2,'=B2*2');
24.  
25.     sh.WriteText(2, 0, 'next row too');
26.     sh.WriteNumber(2,1,2);
27.     sh.WriteFormula(2,2,'=B3*2');
28.  
29.     sh.WriteNumber(3,1,3);
30.     sh.WriteFormula(3,2,'=B4*2');
31.  
32.     sh.WriteFormula(4,2,'=SUM(C2:C4)');
33.  
34.  
35.     sh.HideRow(1);
36.     sh.HideRow(3);
37.  
38.     b.WriteToFile('test2.xls', sfExcel8, true);
39.     WriteLn;
40.   finally
41.     b.Free;
42.   end;
43. end;
44.  
45. procedure WriteAsFormat(openfile, savefile: string; AFormat: TsSpreadsheetFormat);
46. var
47.   b: TsWorkbook;
48.   sh: TsWorksheet;
49. begin
50.  Writeln('Saving test files...');
51.   b := TsWorkbook.Create;
52.   try
53.     b.Options:=b.Options+[boReadFormulas];
54.     b.ReadFromFile(openfile, sfExcel8);
55.     sh := b.GetFirstWorksheet;
56.     //sh.WriteNumber(0, 2, 2094);
57.     b.WriteToFile(savefile, AFormat, true);
58.   finally
59.     b.Free;
60.   end;
61. end;
62.  
63.  
64. begin
65.   //create test2.xls
66.   WriteTest;
67.  
68.   // open test2.xls and save as test3.xls
69.   //Test3.xls sollte auch fehlerhaft sein, aber es ist nicht Fehlerhaft.
70.   // Fehler ist  alle Zeilen mit Ihnahlt werden versteckt
71.   WriteAsFormat('test2.xls','test3.xls', sfExcel8);
72.  
73.   // Ich habe meintest.xls mit Excel2003 erstellt.
74.   // Bei meintest-rowhide-error.xls werden alle Zeilen versteckt
75.   WriteAsFormat('meintest.xls','meintest-rowhide-error.xls', sfExcel8);
76.  
77.  //WriteLn('Press ENTER to close...');  ReadLn;
78. end.
79.  

Edit:
I have still old version from fpspreadsheet. I will try new version now.

[Soner]

I updated fpsreadsheet yet.
Now is meintest.xls okay and at the file "stdplan-verdana-row40hidden.xls" the hidden row 40 is visible.
I mean when you open and save this file from examples above or Spready.

[wp]

Please test ccr-r6639.

[Soner]

Please test ccr-r6639.

You did it!   

Thanks.