Option to output formulae to JSON

[dandv]

I'm using .sheet_to_json for easier testing (instead of comparing the ws object with an expected one). The problem is that formula cells don't show up in the JSON output. Is there a way to make that happen (other than sheet_to_formulae)? If not, an option like formulas: true for sheet_to_json would be very useful for test automation.

[leostar-eva]

I also met the same problem. Has this problem been solved?

[SheetJSDev]

Sure, how would you handle cells that have a raw value (like a number or raw string)?

[dandv]

It's been a while since I filed this issue do I don't remember the entire context, but if I understand the question correctly, it's about deciding between outputting the raw value and the formula of a cell, in case both exists?

In that case, I would prefer the formula to be output, since the raw value represents a particular calculation for the formula at a point in time.

[SheetJSDev]

For cells that have formula expressions and values, you clearly would return the formula. The question is about cells that just have a value (no associated formula expression).

Currently the formula output utility function guesses based on the type of cell. Numeric cell values are stringified, and string cell values are prepended with a single quote (how it would appear in the formula bar if you entered the value manually)

[dandv]

I think that's a good solution.

(Naively, I would like the values coerced to Number or String according to the cell type, but then you'd have to distinguish between literal strings starting with the = sign, and formulas.)

[Himanshu032000]

@SheetJSDev Can I take this issue?

[MelinaMedinaCa]

Hola, Buenas estoy en el coienzo del curso y por ejemplo no me aparece la parte que decie en los primeros pasos, no me aparece la palabra fork. alguno tiene idea por que puede ser ?

[Antonio-Gonzalez-Gomez]

For anyone still looking for this, I'm using a function based on @Mithgol algorithm from #270 with some tweaks:

workbook.SheetNames.forEach(function(name) {
   const sheet = workbook.Sheets[name];
   const range = XLSX.utils.decode_range(sheet['!ref']);
   const json = [];
   var row;
   var rowNum;
   var colNum;
   for(rowNum = range.s.r; rowNum <= range.e.r; rowNum++){
       row = [];
       for(colNum = range.s.c; colNum <= range.e.c; colNum++){
           var nextCell = sheet[XLSX.utils.encode_cell({r: rowNum, c: colNum})];
           if( typeof nextCell === 'undefined' ){
               row.push(void 0);
           } else {
               const value = nextCell.f ? "=" + nextCell.f : nextCell.v; //change this line if needed
               row.push(value);
           }
       }
       json.push(row);
   }
   //do stuff with the json
});

[FatimaHassan1]

The SheetJS library did not have a built-in option like formulas: true for sheet_to_json to explicitly incorporate formula cells in the JSON output as of my most recent knowledge update in September 2021. Formulas are handled differently by sheet_to_json because it primarily focuses on converting cell data to JSON.

However, by iterating through the worksheet's range and removing both the cell values and formulae, you can manually include formulas in your JSON output. Here's an illustration of how you could accomplish this:

const XLSX = require('xlsx');

// Load the Excel workbook
const workbook = XLSX.readFile('example.xlsx');

// Assuming you're working with the first sheet
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

// Convert the worksheet to JSON
const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1, raw: true });

// Iterate through each cell in the worksheet
for (const cellAddress in worksheet) {
  if (worksheet[cellAddress].f) {
    const colRow = XLSX.utils.decode_cell(cellAddress);
    const col = colRow.col;
    const row = colRow.row;

    // Add the formula to the JSON data
    jsonData[row - 1][col] = {
      formula: worksheet[cellAddress].f,
      value: jsonData[row - 1][col]
    };
  }
}

// Print the JSON data
console.log(jsonData);