fpSpreadSheet CSV WriteToFile empty string missing
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
Jkey · 2016-06-03 12:58

Hello,
I loaded an Excel (xlsx) with the following content:
Cell A1 = "A"
Cell B1 = "" //nothing, cell is left empty
Cell C1 = "B"
It loads perfectly, and can be exported to Excel8 (xls) without
problems. If I export it to CSV, I would expect this result: A;;B.
Instead of this, file contains only A;B. How can I avoid skipping the
empty cell?
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 Workbook :=
TsWorkbook.Create;Workbook.ReadFromFile('Input.xlsx');Workbook.WriteTo
File('Output.csv', sfCSV, True);Workbook.Free;

----------------------------------------------------------------------
wp · 2016-06-03 14:01

Fixed in r4676.

----------------------------------------------------------------------
Jkey · 2016-06-06 07:10

Unfortunately output.csv is still the same. In fpscsv.pas, in
WriteSheet procedure (currently line 417) there is this condition:
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 cell =
nil then  FCSVBuilder.AppendCell('')else  WriteCellToStream(AStream,
cell);Code never detects value of cell as nil, so only the second
(else) part runs, even if the cell is empty. If I modified the source
like this, condition would detect the empty cell, and result would
become the expected:
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
FWorkSheet.ReadAsText(cell) = '' then  FCSVBuilder.AppendCell('')else 
WriteCellToStream(AStream, cell);
Or, another idea for the condition could be, that cell has usually
ContentType of cctUTF8String, but in case of empty cell, it's
cctEmpty. What do you think?

----------------------------------------------------------------------
wp · 2016-06-06 08:53

Quote from: Jkey on June 06, 2016, 09:10:46 amWriteSheet procedure
(currently line 417)
WriteSheet begins at line 377 in my version. So, do you use the trunk
version of fpspreadsheet? The released version was not changed. If you
don't use trunk replace the WriteSheet method by this code:
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
TsCSVWriter.WriteSheet(AStream: TStream; AWorksheet: TsWorksheet);var 
r, c: Cardinal;  firstRow, lastRow: Cardinal;  firstCol, lastCol:
Cardinal;  cell: PCell;  n: Integer;begin  FWorksheet := AWorksheet;  
FCSVBuilder := TCSVBuilder.Create;  try    FCSVBuilder.Delimiter :=
CSVParams.Delimiter;    FCSVBuilder.LineEnding :=
LineEndingAsString(CSVParams.LineEnding);    FCSVBuilder.QuoteChar :=
CSVParams.QuoteChar;    FCSVBuilder.SetOutput(AStream);     n :=
FWorksheet.GetCellCount;    if FClipboardMode and (n = 1) then   
begin      cell := FWorksheet.Cells.GetFirstCell;     
WriteCellToStream(AStream, cell);    end else    begin      if
FClipboardMode then      begin        firstRow :=
FWorksheet.GetFirstRowIndex;        firstCol :=
FWorksheet.GetFirstColIndex;      end else      begin        firstRow
:= 0;        firstCol := 0;      end;      lastRow :=
FWorksheet.GetLastOccupiedRowIndex;      lastCol :=
FWorksheet.GetLastOccupiedColIndex;      for r := firstRow to lastRow
do      begin        for c := firstCol to lastCol do        begin    
    cell := FWorksheet.FindCell(r, c);          if cell = nil then   
       FCSVBuilder.AppendCell('')          else           
WriteCellToStream(AStream, cell);        end;       
FCSVBuilder.AppendRow;      end;    end;  finally   
FreeAndNil(FCSVBuilder);  end;end;
This is my test code for creating a csv file with content in cells A1
and C1, but not in B1:
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 begin  //
Open the output file  MyDir := ExtractFilePath(ParamStr(0));   //
Create the spreadsheet  MyWorkbook := TsWorkbook.Create;  MyWorksheet
:= MyWorkbook.AddWorksheet('My Worksheet'); //    -- currently not
working  MyWorksheet.Options := MyWorksheet.Options +
[soHasFrozenPanes];  MyWorksheet.LeftPaneWidth := 1; 
MyWorksheet.TopPaneHeight := 3;   // Write some number cells 
MyWorksheet.WriteNumber(0, 0, 2.0);  MyWorksheet.WriteNumber(0, 2,
3.0);   CSVParams.Delimiter := ';'; 
CSVParams.FormatSettings.DecimalSeparator := '.'; 
CSVParams.NumberFormat := '%.9f';  CSVParams.QuoteChar := '''';   //
Save the spreadsheet to a file  MyWorkbook.WriteToFile(MyDir + 'test'
+ STR_COMMA_SEPARATED_EXTENSION, sfCSV, true);  MyWorkbook.Free;end.
And this result in this file
Quote2.000000000;;3.000000000which looks correct.

----------------------------------------------------------------------
Jkey · 2016-06-06 11:37

Yes, I used trunk. Line 417 refers only to the location of the quoted
condition.
Your example works as expected. However, put some boarder around the
cells, which generally should not influence the resulting CSV files:
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
MyWorkSheet.WriteBoarders(0, 0, [cbNorth, cbSouth, cbEast,
cbWest]);MyWorkSheet.WriteBoarders(0, 1, [cbNorth,
cbSouth]);MyWorkSheet.WriteBoarders(0, 2, [cbNorth, cbSouth, cbEast,
cbWest]);Resulting CSV file skips the empty cell again. I suppose,
that the empty cell becomes not nil due to the boarders, so the
current condition skips it, and cell will be missed in CSV file.

----------------------------------------------------------------------
wp · 2016-06-06 14:28

Ah, I see. Should be fixed now.

----------------------------------------------------------------------
Jkey · 2016-06-07 08:38

Yes, it works now (svn r4678), thank you for the corrections.