[SOLVED] Cell with empty string?

Forum > FPSpreadsheet

(1/1)

xixixi:
In a xlsx file, I need to create a cell with an empty string. When I do

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---ws.WriteText(0, 0, '');ws.WriteNumberFormat(0, 0, nfText);
the result is

--- Code: ---<c r="A1" s="1"><v></v></c>
--- End code ---

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?

xixixi:
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 :)

wp:

--- Quote from: xixixi on January 25, 2023, 05:02:19 am ---but I need an explicit empty string in sharedStrings.xml and the c element to refer to it.

--- End quote ---
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---program project1; uses fpSpreadSheet, fpsTypes, xlsxooxml;var workbook: TsWorkbook; worksheet: TsWorksheet; cell: PCell;begin workbook := TsWorkbook.Create; try worksheet := workbook.AddWorksheet('Test'); worksheet.WriteText(0, 0, 'abc'); cell := worksheet.WriteText(1, 0, 'def'); cell^.UTF8StringValue:= ''; worksheet.WriteText(2, 0, 'ghi'); workbook.WriteToFile('test.xlsx', true); finally workbook.Free; end;end.

--- Code: XML [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---// sharedStrings.xml<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="3" uniqueCount="3"> <si><t xml:space="preserve">abc</t></si> <si><t xml:space="preserve"></t></si> <si><t xml:space="preserve">ghi</t></si></sst> // sheet1.xml...<sheetData> <row r="1" spans="1:1"> <c r="A1" s="0" t="s"><v>0</v></c> </row> <row r="2" spans="1:1"> <c r="A2" s="0" t="s"><v>1</v></c> </row> <row r="3" spans="1:1"> <c r="A3" s="0" t="s"><v>2</v></c> </row></sheetData>[/code]

--- Quote from: xixixi on January 25, 2023, 05:34:45 am ---Now, this made me notice the issue that shared strings are not really shared

--- End quote ---
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.

xixixi:
Thanks a lot!

And also thanks for the library, it's excellent!

Navigation

[0] Message Index