Lazarus

Programming => Packages and Libraries => FPSpreadsheet => Topic started by: xixixi on January 25, 2023, 05:02:19 am

Title: [SOLVED] Cell with empty string?
Post by: xixixi on January 25, 2023, 05:02:19 am
In a xlsx file, I need to create a cell with an empty string. When I do

Code: Pascal  [Select][+][-]
  1. ws.WriteText(0, 0, '');
  2. ws.WriteNumberFormat(0, 0, nfText);

the result is

Code: [Select]
<c r="A1" s="1"><v></v></c>
but I need an explicit empty string in sharedStrings.xml and the c element to refer to it.
The original xlsx file I'm rewriting with fpS has it that way, btw.

I need that because the file I generate is later read by a stupid program that interprets empty cells (as fpS writes them) as 0.

So is there a way to make fpS add an empty string in sharedStrings.xml and refer to it in cells I explicitly assigned an empty string to?
Title: Re: Cell with empty string?
Post by: xixixi on January 25, 2023, 05:34:45 am
Quick workaround: comment out lines 3949-3957 in fpspreadsheet.pas.

Now, this made me notice the issue that shared strings are not really shared... but let's leave that for later :)
Title: Re: Cell with empty string?
Post by: wp on January 25, 2023, 10:38:52 am
but I need an explicit empty string in sharedStrings.xml and the c element to refer to it.
First write a non-empty string and then access the UTF8StringValue element of the cell record and reset it to empty. This by-passes the conversion the blank cell type.
Code: Pascal  [Select][+][-]
  1. program project1;
  2.  
  3. uses
  4.   fpSpreadSheet, fpsTypes, xlsxooxml;
  5. var
  6.   workbook: TsWorkbook;
  7.   worksheet: TsWorksheet;
  8.   cell: PCell;
  9. begin
  10.   workbook := TsWorkbook.Create;
  11.   try
  12.     worksheet := workbook.AddWorksheet('Test');
  13.     worksheet.WriteText(0, 0, 'abc');
  14.     cell := worksheet.WriteText(1, 0, 'def');
  15.     cell^.UTF8StringValue:= '';
  16.     worksheet.WriteText(2, 0, 'ghi');
  17.     workbook.WriteToFile('test.xlsx', true);
  18.   finally
  19.     workbook.Free;
  20.   end;
  21. end.

Code: XML  [Select][+][-]
  1. // sharedStrings.xml
  2. <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="3" uniqueCount="3">
  3.   <si><t xml:space="preserve">abc</t></si>
  4.   <si><t xml:space="preserve"></t></si>
  5.   <si><t xml:space="preserve">ghi</t></si>
  6. </sst>
  7.  
  8. // sheet1.xml
  9. ...
  10. <sheetData>
  11.   <row r="1" spans="1:1">
  12.     <c r="A1" s="0" t="s"><v>0</v></c>
  13.   </row>
  14.   <row r="2" spans="1:1">
  15.     <c r="A2" s="0" t="s"><v>1</v></c>
  16.   </row>
  17.   <row r="3" spans="1:1">
  18.     <c r="A3" s="0" t="s"><v>2</v></c>
  19.   </row>
  20. </sheetData>
[/code]

Now, this made me notice the issue that shared strings are not really shared
Shared strings are a concept of Excel, not of fpspreadsheet. There was some time when I wanted to implement them but I noticed that they do not offer any benefit, just make things more complicated.
Title: Re: Cell with empty string?
Post by: xixixi on January 25, 2023, 03:53:52 pm
Thanks a lot!

And also thanks for the library, it's excellent!
TinyPortal © 2005-2018