Is it possible to hide rows or why shows LibreOffice rows with height
zero?
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
Soner · 2018-09-05 09:09

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Sheet1.WriteRowHeight(r,0, suPoints);      //libreoffice ignores
itSheet1.WriteRowHeight(r,0, suMillimeters); //libreoffice ignores
itSheet1.WriteRowHeight(r,0, suCentimeters); //libreoffice ignores
itSheet1.WriteRowHeight(r,0, suInches);      //libreoffice ignores
itSheet1.WriteRowHeight(r,0, suChars);       //libreoffice ignores
itSheet1.WriteRowHeight(r,0, suLines);       //libreoffice ignores
it Sheet1.WriteRowHeight(r,0); //libreoffice makes it 0,11 cm 
(Fileformat is xlsx saved from fpspreadsheet)

----------------------------------------------------------------------
wp · 2018-09-05 23:10

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   procedure
TsWorksheet.HideCol(ACol: Cardinal);  procedure
TsWorksheet.HideRow(ARow: Cardinal);  procedure
TsWorksheet.ShowCol(ACol: Cardinal);  procedure
TsWorksheet.ShowRow(ARow: Cardinal);  function
TsWorksheet.ColHidden(ACol: Cardinal): Boolean;  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 · 2018-09-06 10:12

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 · 2018-09-06 10:50

Quote from: wp on September 06, 2018, 12:12:48 pmSetting 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 · 2018-09-06 11:50

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 · 2018-09-07 18:09

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 · 2018-09-08 08:14

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
demo_hide_rows_cols; {$mode objfpc}{$H+} uses  SysUtils,
fpspreadsheet, fpstypes, fpsallformats; const  HIDDEN_COL = 2; 
HIDDEN_ROW = 2; procedure WriteTest;var  b: TsWorkbook;  sh:
TsWorksheet;begin  b := TsWorkbook.Create;  try    Writeln('Writing
test files...');    sh := b.AddWorksheet('Test');    sh.WriteNumber(0,
0, 1024);    sh.WriteNumber(HIDDEN_ROW-1, HIDDEN_COL-1, 123);   
sh.WriteText(HIDDEN_ROW,   HIDDEN_COL, 'This is hidden');   
sh.WriteNumber(HIDDEN_ROW+1, HIDDEN_COL+1, 1.0);   
sh.HideCol(HIDDEN_COL);    sh.HideRow(HIDDEN_ROW);   
b.WriteToFile('test.ods', sfOpenDocument, true);   
b.WriteToFile('test.xlsx', sfOOXML, true);   
b.WriteToFile('test.xls', sfExcel8, true);   
b.WriteToFile('test-5.xls', sfExcel5, true);    WriteLn;  finally   
b.Free;  end;end; procedure ReadTest(AFileName: String; AFormat:
TsSpreadsheetFormat);var  b: TsWorkbook;  sh: TsWorksheet;begin 
WriteLn('Reading "', AFileName, '" (', AFormat, ')...');  b :=
TsWorkbook.Create;  try    b.ReadFromFile(AFileName, AFormat);    sh
:= b.GetFirstWorksheet;    Write('Column ', HIDDEN_COL, ' is ');    if
sh.ColHidden(HIDDEN_COL) then WriteLn('hidden') else
WriteLn('visible');    Write('Row ', HIDDEN_ROW, ' is ');    if
sh.RowHidden(HIDDEN_ROW) then WriteLn('hidden') else
WriteLn('visible');    WriteLn;  finally    b.Free;  end;end; begin 
WriteTest;   ReadTest('test.ods', sfOpenDocument); 
ReadTest('test.xlsx', sfOOXML);  ReadTest('test.xls', sfExcel8); 
ReadTest('test-5.xls', sfExcel5);   WriteLn('Press ENTER to
close...');  ReadLn;end.

----------------------------------------------------------------------
Soner · 2018-09-09 10:12

@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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
fpsprdxlsrowhide; {$mode objfpc}{$H+} uses  SysUtils, fpspreadsheet,
fpstypes, fpsallformats; const  HIDDEN_COL = 2;  HIDDEN_ROW =
2; procedure WriteTest;var  b: TsWorkbook;  sh: TsWorksheet;begin  b
:= TsWorkbook.Create;  try    Writeln('Writing test files...');    sh
:= b.AddWorksheet('Test');    sh.WriteNumber(0, 0, 1024);   
sh.WriteNumber(HIDDEN_ROW-1, HIDDEN_COL-1, 123);   
sh.WriteText(HIDDEN_ROW,   HIDDEN_COL, 'This is hidden');   
sh.WriteNumber(HIDDEN_ROW+1, HIDDEN_COL+1, 1.0);   
sh.HideCol(HIDDEN_COL);    sh.HideRow(HIDDEN_ROW);   
b.WriteToFile('test.xls', sfExcel8, true);    WriteLn;  finally   
b.Free;  end;end; procedure WriteTest2(openfile, savefile:
string);var  b: TsWorkbook;  sh: TsWorksheet;begin  b :=
TsWorkbook.Create;  try    b.ReadFromFile(openfile, sfExcel8);   
Writeln('Writing test files...');    sh := b.GetFirstWorksheet;   
sh.WriteNumber(0, 0, 2094);    b.WriteToFile(savefile, sfExcel8,
true);     WriteLn;  finally    b.Free;  end;end;  begin  //create
file as xls  WriteTest;  //open file as xls and save it as xls. And
result is ok!  WriteTest2('test.xls','test.xls');   // open save
verdana test. And result is not ok, why?  // openspreadsheet with
libre office or excel  // All rows with content are hidden. 
WriteTest2('stdplan-verdana-row40hidden.xls','stdplan-verdana-
row40hidden-savefromdemo.xls');    WriteLn('Press ENTER to
close...');  ReadLn;end. 
The error only visible with my file, example code from above is okay.
I added my file and demo as attachment.

----------------------------------------------------------------------
Soner · 2018-09-09 10:18

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 · 2018-09-09 10:38

@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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
fpsprdxlsrowhide; {$mode objfpc}{$H+} uses  SysUtils, fpspreadsheet,
fpstypes, fpsallformats; const  HIDDEN_COL = 2;  HIDDEN_ROW =
2; procedure WriteTest;var  b: TsWorkbook;  sh: TsWorksheet;begin  b
:= TsWorkbook.Create;  try    Writeln('Writing test files...');    sh
:= b.AddWorksheet('Test');    sh.WriteNumber(0, 0, 1024);   
sh.WriteNumber(HIDDEN_ROW-1, HIDDEN_COL-1, 123);   
sh.WriteText(HIDDEN_ROW,   HIDDEN_COL, 'This is hidden');   
sh.WriteNumber(HIDDEN_ROW+1, HIDDEN_COL+1, 1.0);   
sh.HideCol(HIDDEN_COL);    sh.HideRow(HIDDEN_ROW);   
b.WriteToFile('test.xls', sfExcel8, true);    WriteLn;  finally   
b.Free;  end;end; procedure WriteWithFormat(openfile, savefile:
string; AFormat: TsSpreadsheetFormat);var  b: TsWorkbook;  sh:
TsWorksheet;begin  b := TsWorkbook.Create;  try   
b.ReadFromFile(openfile, sfExcel8);    Writeln('Writing test
files...');    sh := b.GetFirstWorksheet;    sh.WriteNumber(0, 0,
2094);    b.WriteToFile(savefile, AFormat, true);     WriteLn; 
finally    b.Free;  end;end;  begin  //create file as xls  WriteTest; 
//open file as xls and save it as xls 
WriteTest2('test.xls','test.xls');   // open save verdana test  //
openspreadsheet with libre office or excel  // are rows with content
are hidden.  WriteWithFormat('stdplan-verdana-
row40hidden.xls','stdplan-verdana-row40hidden-savefromdemo.xls',
sfExcel8);   WriteWithFormat('stdplan-verdana-
row40hidden.xls','stdplan-verdana-row40hidden-
savefromdemo.xlsx',sfOOXML);  WriteWithFormat('stdplan-verdana-
row40hidden.xls','stdplan-verdana-row40hidden-
savefromdemo.ods',sfOpenDocument);    WriteLn('Press ENTER to
close...');  ReadLn;end. 
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 · 2018-09-09 16:50

I still cannot reproduce. But I found some suspicious code in
xlscommon TsSpreadBiffReader.ReadRowInfo:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
DWordLEToN(rowrec.Flags) and $00000020 <> then  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 · 2018-09-09 19:52

Quote from: wp on September 09, 2018, 06:50:16 pmI 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
fpsprdxlsrowhide; {$mode objfpc}{$H+} uses  SysUtils, fpspreadsheet,
fpstypes, fpsallformats;  procedure WriteTest;var  b: TsWorkbook;  sh:
TsWorksheet;begin  b := TsWorkbook.Create;  try    Writeln('Creating
test files...');    sh := b.AddWorksheet('Test');     sh.WriteText(0,
0, 'Next row is hidden');     sh.WriteText(1, 0, 'this row');   
sh.WriteNumber(1,1,1);    sh.WriteFormula(1,2,'=B2*2');    
sh.WriteText(2, 0, 'next row too');    sh.WriteNumber(2,1,2);   
sh.WriteFormula(2,2,'=B3*2');     sh.WriteNumber(3,1,3);   
sh.WriteFormula(3,2,'=B4*2');    
sh.WriteFormula(4,2,'=SUM(C2:C4)');      sh.HideRow(1);   
sh.HideRow(3);     b.WriteToFile('test2.xls', sfExcel8, true);   
WriteLn;  finally    b.Free;  end;end; procedure
WriteAsFormat(openfile, savefile: string; AFormat:
TsSpreadsheetFormat);var  b: TsWorkbook;  sh:
TsWorksheet;begin Writeln('Saving test files...');  b :=
TsWorkbook.Create;  try    b.Options:=b.Options+[boReadFormulas];   
b.ReadFromFile(openfile, sfExcel8);    sh := b.GetFirstWorksheet;   
//sh.WriteNumber(0, 2, 2094);    b.WriteToFile(savefile, AFormat,
true);  finally    b.Free;  end;end;  begin  //create test2.xls 
WriteTest;   // open test2.xls and save as test3.xls  //Test3.xls
sollte auch fehlerhaft sein, aber es ist nicht Fehlerhaft.  // Fehler
ist  alle Zeilen mit Ihnahlt werden versteckt 
WriteAsFormat('test2.xls','test3.xls', sfExcel8);   // Ich habe
meintest.xls mit Excel2003 erstellt.  // Bei meintest-rowhide-
error.xls werden alle Zeilen versteckt 
WriteAsFormat('meintest.xls','meintest-rowhide-error.xls',
sfExcel8);  //WriteLn('Press ENTER to close...');  ReadLn;end. 

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

----------------------------------------------------------------------
Soner · 2018-09-09 20:05

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 · 2018-09-10 09:01

Please test ccr-r6639.

----------------------------------------------------------------------
Soner · 2018-09-10 10:46

Quote from: wp on September 10, 2018, 11:01:28 amPlease test
ccr-r6639.
You did it!  

Thanks.