Sheetjs: Empty cells don't have a style

[Schmitda]

Hello everyone,

I hope this is the right place to ask this. I am not quite sure if this is a bug or this is not yet implemente or I am doing something wrong.

Well to the problem:
I am trying to read the style of a cell which is empty.
The workbook gets loaded via this command:

 this.workbook = XLSX.readFile(path, {
            cellStyles: true
        });

And it also works great except that empty cells (value wise) won't have any styles.
I get it that worksheet['P15'].v == undefined, but why this is the case worksheet['P15'] == undefined.

Can you tell me if I am doing something wrong. If yes, what am I doing wrong?
Or is there an alternative way to read the cell style?
In my example the cell P15 has a blue background.

if(cell_ref == "P15"){
                console.log(cell_ref);
                console.log(this.currentWorksheet[cell_ref]);
            }

The output is:
P15
undefined.

Thanks in advance for any assistance.

[reviewher]

@Schmitda thanks for the report! If you pass sheetStubs:true to readFile it should generate objects for those blank cells.

@sheetjsdev sheetStubs does not appear to be consistent across formats. I made a simple sheet in Excel 2016 following the CSV:

1,2,3
4,,6
7,8,9

and bolded all 9 cells and saved in various formats. When sheetStubs is not set to true, no objects are generated. Here's what happens when sheetStubs is set:

| format | Sheets.Sheet1.B2 with sheetStubs:true |
| :----- | :------------------------------------ |
| xlsb | { t: 's', v: undefined } |
| xlsx | { t: 'stub' } |
| xls | undefined (no object) |
| xml | undefined (no object) |
| xls5.0 | undefined (no object) |

recommendation: keep a separate type for stub cells, maybe call it blank

[Schmitda]

@reviewher Thanks a lot for your explanation. Well it is true now I have an object. But saddly the style is still empty. (Cuz it is a xlsx I get {t:'stub'}).

But I am interested in the style of a cell without value.
Is there another way to get this?

I am really greatful for your time.

[Schmitda]

If anyone else has this particular problem.
I fixed it with using the xslx-style Library. Thanks to the person who gave me the hint.

https://www.npmjs.com/package/xlsx-style

[SheetJSDev]

@reviewher We are about to push a change unifying the stub processing under cell type z

[pawsong]

I wonder if there is any update or plan for this issue :)

[IvanPachecoEspejel]

I had the same problem, could not read the style of a blank cell. Fix my problem using the xlsx.js and jszip.js files, instead of xlsx.full.min.js and xlsx.core.min.js, Just load jszip.js before xlxs.js on my page, turn on the sheetStubs option when reading, and I modified the "safe_format" method of xlsx.js which in the version I have (0.11.6), is on line 11158, and only I changed a condition.
this is the original code :
if(p.t === 'z') return; if(p.t === 'd' && typeof p.v === 'string') p.v = parseDate(p.v); try { if(opts.cellNF) p.z = SSF._table[fmtid]; } catch(e) { if(opts.WTF) throw e; } if(!opts || opts.cellText !== false) try { if(p.t === 'e') p.w = p.w || BErr[p.v]; else if(fmtid === 0) { if(p.t === 'n') { if((p.v|0) === p.v) p.w = SSF._general_int(p.v); else p.w = SSF._general_num(p.v); } else if(p.t === 'd') { var dd = datenum(p.v); if((dd|0) === dd) p.w = SSF._general_int(dd); else p.w = SSF._general_num(dd); } else if(p.v === undefined) return ""; else p.w = SSF._general(p.v,_ssfopts); } else if(p.t === 'd') p.w = SSF.format(fmtid,datenum(p.v),_ssfopts); else p.w = SSF.format(fmtid,p.v,_ssfopts); } catch(e) { if(opts.WTF) throw e; } if(!opts.cellStyles) return; if(fillid != null) try { p.s = styles.Fills[fillid]; if (p.s.fgColor && p.s.fgColor.theme && !p.s.fgColor.rgb) { p.s.fgColor.rgb = rgb_tint(themes.themeElements.clrScheme[p.s.fgColor.theme].rgb, p.s.fgColor.tint || 0); if(opts.WTF) p.s.fgColor.raw_rgb = themes.themeElements.clrScheme[p.s.fgColor.theme].rgb; } if (p.s.bgColor && p.s.bgColor.theme) { p.s.bgColor.rgb = rgb_tint(themes.themeElements.clrScheme[p.s.bgColor.theme].rgb, p.s.bgColor.tint || 0); if(opts.WTF) p.s.bgColor.raw_rgb = themes.themeElements.clrScheme[p.s.bgColor.theme].rgb; } } catch(e) { if(opts.WTF && styles.Fills) throw e; }
This is the modification. I tried it with xlsx files only, I hope it helps:
if(p.t !== 'z'){ if(p.t === 'd' && typeof p.v === 'string') p.v = parseDate(p.v); try { if(opts.cellNF) p.z = SSF._table[fmtid]; } catch(e) { if(opts.WTF) throw e; } if(!opts || opts.cellText !== false) try { if(p.t === 'e') p.w = p.w || BErr[p.v]; else if(fmtid === 0) { if(p.t === 'n') { if((p.v|0) === p.v) p.w = SSF._general_int(p.v); else p.w = SSF._general_num(p.v); } else if(p.t === 'd') { var dd = datenum(p.v); if((dd|0) === dd) p.w = SSF._general_int(dd); else p.w = SSF._general_num(dd); } else if(p.v === undefined) return ""; else p.w = SSF._general(p.v,_ssfopts); } else if(p.t === 'd') p.w = SSF.format(fmtid,datenum(p.v),_ssfopts); else p.w = SSF.format(fmtid,p.v,_ssfopts); } catch(e) { if(opts.WTF) throw e; } } if(!opts.cellStyles) return; if(fillid != null) try { p.s = styles.Fills[fillid]; if (p.s.fgColor && p.s.fgColor.theme && !p.s.fgColor.rgb) { p.s.fgColor.rgb = rgb_tint(themes.themeElements.clrScheme[p.s.fgColor.theme].rgb, p.s.fgColor.tint || 0); if(opts.WTF) p.s.fgColor.raw_rgb = themes.themeElements.clrScheme[p.s.fgColor.theme].rgb; } if (p.s.bgColor && p.s.bgColor.theme) { p.s.bgColor.rgb = rgb_tint(themes.themeElements.clrScheme[p.s.bgColor.theme].rgb, p.s.bgColor.tint || 0); if(opts.WTF) p.s.bgColor.raw_rgb = themes.themeElements.clrScheme[p.s.bgColor.theme].rgb; } } catch(e) { if(opts.WTF && styles.Fills) throw e; }

[SheetJSDev]

We offer this in the Pro compendium. Since companies have paid for the features already, it would be unfair to them if we turned around and made it available as open source. We have a longer comment in a gist.