Option to output formulae to JSON #1370

Open
opened 2018-11-29 05:52:52 +00:00 by dandv · 8 comments
dandv commented 2018-11-29 05:52:52 +00:00 (Migrated from github.com)

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.

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 commented 2019-10-14 06:00:11 +00:00 (Migrated from github.com)

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

I also met the same problem. Has this problem been solved?
SheetJSDev commented 2020-01-18 18:11:12 +00:00 (Migrated from github.com)

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

Sure, how would you handle cells that have a raw value (like a number or raw string)?
dandv commented 2020-01-22 21:31:05 +00:00 (Migrated from github.com)

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.

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 commented 2020-01-22 21:46:36 +00:00 (Migrated from github.com)

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)

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](https://github.com/SheetJS/sheetjs/blob/master/bits/90_utils.js#L169-L174). 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 commented 2020-01-22 22:11:34 +00:00 (Migrated from github.com)

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.)

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 commented 2021-10-21 19:04:55 +00:00 (Migrated from github.com)

@SheetJSDev Can I take this issue?

@SheetJSDev Can I take this issue?
MelinaMedinaCa commented 2022-06-18 00:34:46 +00:00 (Migrated from github.com)

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 ?

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 commented 2022-07-04 16:32:58 +00:00 (Migrated from github.com)

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
});
For anyone still looking for this, I'm using a function based on @Mithgol algorithm from #270 with some tweaks: ```javascript 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 }); ```
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#1370
No description provided.