Fomulas not showing as string/text in a cell #2948

New Issue

lh104 · 2023-05-19T08:24:41Z

lh104 commented

2023-05-19 08:24:41 +00:00

I am using SheetJs + X-spreadsheet,.

/* convert to x-spreadsheet form */
var data = stox(wb);

/* update x-spreadsheet */
xspr.loadData(data);

The data sometimes does not have the unrecognized fomulas as text in a cell and sometimes does. May I ask why is that?

I would like to have the fomulas in the "data" so that when I export it to excel file, I will write them back to the json data.

I am currently doing is that record the row and column index of a fomula and replce the cell with empty string so that the file can be loaded in X-spreadsheet. Then when I export it, I write them back to the json data for excel file.

I am using SheetJs + X-spreadsheet,. /* convert to x-spreadsheet form */ var data = stox(wb); /* update x-spreadsheet */ xspr.loadData(data); The data sometimes does not have the unrecognized fomulas as text in a cell and sometimes does. May I ask why is that? I would like to have the fomulas in the "data" so that when I export it to excel file, I will write them back to the json data. I am currently doing is that record the row and column index of a fomula and replce the cell with empty string so that the file can be loaded in X-spreadsheet. Then when I export it, I write them back to the json data for excel file.

sheetjs commented

2023-05-19 08:48:49 +00:00

The unminified version is available at https://cdn.sheetjs.com/xspreadsheet/xlsxspread.js (we'll eventually make a repo for this)

When importing data into the grid, x-spreadsheet checks the cell text and assumes text starting with = represents a formula. . The connector library checks each cell and passes the formula if the cell has a formula (and passing the formatted value if the cell does not have a formula).

When exporting data out of the grid, the connector library performs a value parsing step similar to the logic for reading CSV files. If the cell text is true or false, then the connector emits boolean cells (type b). If the cell text starts with =, the connector library assumes the text represents the formula. Numeric values are guessed by converting strings to numbers and testing if the cell value is NaN

The overarching issue is that x-spreadsheet provides no way to sideload formulae.

If you think the converter should behave differently, please let us know and we can consider adding an option or changing the default behavior.

The unminified version is available at https://cdn.sheetjs.com/xspreadsheet/xlsxspread.js (we'll eventually make a repo for this) When importing data into the grid, x-spreadsheet checks the cell text and assumes text starting with `=` represents a formula. . The connector library checks each cell and passes the formula if the cell has a formula (and passing the formatted value if the cell does not have a formula). When exporting data out of the grid, the connector library performs a value parsing step similar to the logic for reading CSV files. If the cell text is `true` or `false`, then the connector emits boolean cells (type `b`). If the cell text starts with `=`, the connector library assumes the text represents the formula. Numeric values are guessed by converting strings to numbers and testing if the cell value is `NaN` The overarching issue is that x-spreadsheet provides no way to sideload formulae. If you think the converter should behave differently, please let us know and we can consider adding an option or changing the default behavior.

lh104 commented

2023-05-20 18:54:18 +00:00

Hi @sheetjs

Thanks for the promt reply. I have found the "issue".

I copied the demo code and have it running in my local.

My excel file is generated by an API and saved to my local. If I use the demo above to load the file directly without any editing, I won't be able to see fomulas as text from the output of stox. But if I open it and do some editing, even just remove a single character and save it. I will be able to see the fomulas outputed by stox.

Please see my attached screenshots.

Hi @sheetjs Thanks for the promt reply. I have found the "issue". I copied the [demo](https://docs.sheetjs.com/xspreadsheet/) code and have it running in my local. My excel file is generated by an API and saved to my local. If I use the demo above to load the file directly without any editing, I won't be able to see fomulas as text from the output of stox. But if I open it and do some editing, even just remove a single character and save it. I will be able to see the fomulas outputed by stox. Please see my attached screenshots.

has-formula.png

262 KiB

no-formula.png

249 KiB

sheetjs commented

2023-05-20 20:19:44 +00:00

Can you try changing the following in the demo:

Line 74 (currently process_wb(XLSX.read(data));):

      process_wb(XLSX.read(data, {cellFormula: true, sheetStubs: true}));

Line 112 (currently .then(function(ab) { process_wb(XLSX.read(ab)); });):

      .then(function(ab) { process_wb(XLSX.read(ab, {cellFormula: true, sheetStubs: true})); });

Line 120 (currently XLSX.writeFile(new_wb, 'sheetjs.xlsx', {});):

  XLSX.writeFile(new_wb, 'sheetjs.xlsx', {sheetStubs: true});

If this works, we'll update the demo. If not, please share a sample file and we can take a closer look.

Can you try changing the following in the demo: Line 74 (currently `process_wb(XLSX.read(data));`): ```js process_wb(XLSX.read(data, {cellFormula: true, sheetStubs: true})); ``` Line 112 (currently `.then(function(ab) { process_wb(XLSX.read(ab)); });`): ```js .then(function(ab) { process_wb(XLSX.read(ab, {cellFormula: true, sheetStubs: true})); }); ``` Line 120 (currently `XLSX.writeFile(new_wb, 'sheetjs.xlsx', {});`): ```js XLSX.writeFile(new_wb, 'sheetjs.xlsx', {sheetStubs: true}); ``` If this works, we'll update the demo. If not, please share a sample file and we can take a closer look.

lh104 commented

2023-05-20 20:44:49 +00:00

I just tried it and it worked, I can see the formulas are in the workbook object.

But the data returned from stox still does not have cells with formulas.

See my attached screenshots.

I just tried it and it worked, I can see the formulas are in the workbook object. But the data returned from `stox` still does not have cells with formulas. See my attached screenshots.

has-formula-now.png

254 KiB

no-formula.png

70 KiB