Formula cell values are empty when using sheet_to_json. #3218

New Issue

yaseer-frontrol · 2024-09-13T16:05:42Z

yaseer-frontrol commented

2024-09-13 16:05:42 +00:00

I am uploading an Excel file and attempting to retrieve all cell values in JSON format.

const data = event.target?.result;
let json = [];
const dataArray = new Uint8Array(data as ArrayBuffer);
const workbook = XLSX.read(dataArray, {
  type: "array",
  cellText: true,
  cellFormula: true,
});
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];
json = XLSX.utils.sheet_to_json(worksheet, {
  raw: true,
  header: 1,
  blankrows: false,
});

I have a cell with a formula like =G1+H1, but when I try to parse the sheet using sheet_to_json, the formula cell returns an empty value in the resulting array.

Excel:

I am uploading an Excel file and attempting to retrieve all cell values in JSON format. ``` const data = event.target?.result; let json = []; const dataArray = new Uint8Array(data as ArrayBuffer); const workbook = XLSX.read(dataArray, { type: "array", cellText: true, cellFormula: true, }); const sheetName = workbook.SheetNames[0]; const worksheet = workbook.Sheets[sheetName]; json = XLSX.utils.sheet_to_json(worksheet, { raw: true, header: 1, blankrows: false, }); ``` I have a cell with a formula like =G1+H1, but when I try to parse the sheet using sheet_to_json, the formula cell returns an empty value in the resulting array. Excel: <img width="356" alt="image" src="/attachments/b995f1c0-d3f0-4c54-bb6a-e0f5df92da3d">

image.png

2.4 KiB

sheetjs commented

2024-09-13 16:52:47 +00:00

XLSX.read pulls the cached values in the file.

Third-party writers can omit the cached values when writing to XLSX. Typically they set a flag that instructs Excel to recalculate the workbook when it is opened.

As part of the SheetJS Pro compendium we offer a custom implementation of a spreadsheet calculation engine. This is currently out of scope of the file processing libraries.

`XLSX.read` pulls the cached values in the file. Third-party writers can omit the cached values when writing to XLSX. Typically they set a flag that instructs Excel to recalculate the workbook when it is opened. As part of the [SheetJS Pro compendium](https://sheetjs.com/pro) we offer a custom implementation of a spreadsheet calculation engine. This is currently out of scope of the file processing libraries.

sheetjs closed this issue

2024-09-13 16:52:48 +00:00

Sign in to join this conversation.