Non removal of empty columns #592

New Issue

2017-03-13T11:38:03Z

rashthedude commented

2017-03-13 11:38:03 +00:00

(Migrated from github.com)

I'm currently using this library and empty columns/cells are removed when parsed, is there an option to keep them somehow?

👍 1

rashthedude commented

2017-03-13 12:17:01 +00:00

(Migrated from github.com)

This is what I'm doing more or less:

var sheet_name_list = workbook.SheetNames;
sheet_name_list.forEach(function(y) { /* iterate through sheets /
var worksheet = workbook.Sheets[y];
for (var z in worksheet) {
/ all keys that do not begin with "!" correspond to cell addresses */
if(z[0] === '!') continue;
console.log(y + "!" + z + "=" + JSON.stringify(worksheet[z].v));
}
});

It loops through every nonempty sheet. But my question is how can I maintain/keep the empty cells?

This is what I'm doing more or less: var sheet_name_list = workbook.SheetNames; sheet_name_list.forEach(function(y) { /* iterate through sheets */ var worksheet = workbook.Sheets[y]; for (var z in worksheet) { /* all keys that do not begin with "!" correspond to cell addresses */ if(z[0] === '!') continue; console.log(y + "!" + z + "=" + JSON.stringify(worksheet[z].v)); } }); It loops through every nonempty sheet. But my question is how can I maintain/keep the empty cells?

SheetJSDev commented

2017-03-13 13:25:38 +00:00

(Migrated from github.com)

@rashthedude I assume you mean empty cell.

README: https://github.com/SheetJS/js-xlsx#worksheet-object

Each worksheet stores its range in a key !ref. If worksheet is the your sheet, then worksheet['!ref'] is the range. Using the XLSX.utils.decode_range function, you can get the full extent of the worksheet. Then you can loop across the sheet by looping across all rows and columns in the range:

var range = XLSX.utils.decode_range(worksheet['!ref']);
for(var R = range.s.r; R <= range.e.r; ++R) {
  for(var C = range.s.c; C <= range.e.c; ++C) {
    var coord = XLSX.utils.encode_cell({r:R,c:C});
    if(!worksheet[coord]) {
      /* The cell is not specified in the worksheet */
    } else {
      /* worksheet[coord] is a valid cell */
    }
  }
}

The utilities functions skip the encode_cell calls by pre-computing the column names. For example, sheet_to_csv essentially does:

var range = XLSX.utils.decode_range(sheet["!ref"]);
var rowlabel = "";
var cols = [];
var R = 0, C = 0;

/* precompute column labels */
for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);

for(R = range.s.r; R <= range.e.r; ++R) {
  /* precompute row label */
  rowlabel = encode_row(R);
  for(C = range.s.c; C <= range.e.c; ++C) {
    /* a cell reference is merely a column label concatenated with a row label*/
    val = sheet[cols[C] + rowlabel];
    /* do something */
  }
}

@rashthedude I assume you mean empty cell. README: https://github.com/SheetJS/js-xlsx#worksheet-object Each worksheet stores its range in a key `!ref`. If `worksheet` is the your sheet, then `worksheet['!ref']` is the range. Using the `XLSX.utils.decode_range` function, you can get the full extent of the worksheet. Then you can loop across the sheet by looping across all rows and columns in the range: ```js var range = XLSX.utils.decode_range(worksheet['!ref']); for(var R = range.s.r; R <= range.e.r; ++R) { for(var C = range.s.c; C <= range.e.c; ++C) { var coord = XLSX.utils.encode_cell({r:R,c:C}); if(!worksheet[coord]) { /* The cell is not specified in the worksheet */ } else { /* worksheet[coord] is a valid cell */ } } } ``` The utilities functions skip the `encode_cell` calls by pre-computing the column names. For example, [`sheet_to_csv`](https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L152-L164) essentially does: ```js var range = XLSX.utils.decode_range(sheet["!ref"]); var rowlabel = ""; var cols = []; var R = 0, C = 0; /* precompute column labels */ for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C); for(R = range.s.r; R <= range.e.r; ++R) { /* precompute row label */ rowlabel = encode_row(R); for(C = range.s.c; C <= range.e.c; ++C) { /* a cell reference is merely a column label concatenated with a row label*/ val = sheet[cols[C] + rowlabel]; /* do something */ } } ```

rashthedude commented

2017-03-13 15:26:30 +00:00

(Migrated from github.com)

Hi there,

Thanks for the swift reply but I'm a little confused. To give you a clear idea of what I'm doing:

workbook - xlsx.readFile(file)
sheet_name_list = workbook.SheetNames

sheet_name_list.forEach( (y) ->
worksheet = workbook.Sheets[y]
for m, n of worksheet
// do something here

So where exactly do I make use of "decode_range"?

Hi there, Thanks for the swift reply but I'm a little confused. To give you a clear idea of what I'm doing: workbook - xlsx.readFile(file) sheet_name_list = workbook.SheetNames sheet_name_list.forEach( (y) -> worksheet = workbook.Sheets[y] for m, n of worksheet // do something here So where exactly do I make use of "decode_range"?

SheetJSDev commented

2017-03-13 15:32:38 +00:00

(Migrated from github.com)

@rashthedude The worksheets are objects whose keys are cell addresses (like B3) and whose values are cell objects. The code you presented will iterate across the cells. In retrospect that's not a great pattern and we will remove it from the README.

What exactly are you trying to do? For example, if you just want to get the content as an array of arrays, the sheet_to_json utility function will do that.

@rashthedude The worksheets are objects whose keys are cell addresses (like `B3`) and whose values are cell objects. The code you presented will iterate across the cells. In retrospect that's not a great pattern and we will remove it from the README. What exactly are you trying to do? For example, if you just want to get the content as an array of arrays, the `sheet_to_json` utility function will do that.

rashthedude commented

2017-03-13 15:49:26 +00:00

(Migrated from github.com)

@SheetJSDev Yes I'm trying to fetch the content as an array and iterate over it. Tried:

wb = xlsx.readFile(file)
stuff = xlsx.utils.sheet_to_json(wb)

But "stuff" seems to be empty at this point.

@SheetJSDev Yes I'm trying to fetch the content as an array and iterate over it. Tried: wb = xlsx.readFile(file) stuff = xlsx.utils.sheet_to_json(wb) But "stuff" seems to be empty at this point.

rashthedude commented

2017-03-13 16:38:18 +00:00

(Migrated from github.com)

Managed to get it to work at last. Thanks again @SheetJSDev.

SheetJSDev commented

2017-03-13 16:56:02 +00:00

(Migrated from github.com)

@rashthedude sheet_to_json, as the name indicates, takes a worksheet :D Feel free to raise another issue if you encounter more issues.

yash2503 commented

2019-09-09 08:19:50 +00:00

(Migrated from github.com)

@rashthedude could you please share how did you resolve the issue, I am facing the same issue right now.