Sheetjs: Hidden columns and rows

[pascaldls]

Hi is there any way i could exclude hidden columns and rows from a workbook.
If options not available is there any way i could know if columns or row is hidden in the workbook.
Thanks for taking the time.

[SheetJSDev]

@pascaldls we just pushed a change, to show up in the next release, that will expose hidden property on row and column objects. So for a given worksheet:

• !!((worksheet['!rows']||[])[R]||{}).hidden is true if row R is hidden and false otherwise.
• !!((worksheet['!cols']||[])[C]||{}).hidden is true if col C is hidden and false otherwise.

If you are wondering why the expression looks convoluted, the rows and cols arrays are only generated if the file specifies any row or column metadata. If the given row/column uses the default size and is visible, there will be no object for the corresponding row.

The relevant docs:

• Column: https://sheetjs.gitbooks.io/docs/#column-properties
• Row: https://sheetjs.gitbooks.io/docs/#row-properties

[robinmackenzie]

Hi @SheetJSDev - please can you advise if the change is in v0.10.1 ?

[SheetJSDev]

@robinmackenzie it landed in 0.9.13, so yes :)

On the read side, set the option cellStyles:true in the options argument to XLSX.read or XLSX.readFile. The two (admittedly messy) expressions above should work, but we are going to wrap some of that logic in a utility function.

On the write side, add a '!cols' or '!rows' object to the worksheet to set the visibility:

// to hide row R
if(!worksheet['!rows']) worksheet['!rows'] = [];
if(!worksheet['!rows'][R]) worksheet['!rows'][R] = {};
worksheet['!rows'][R].hidden = true;

// to hide col C
if(!worksheet['!cols']) worksheet['!cols'] = [];
if(!worksheet['!cols'][C]) worksheet['!cols'][C] = {};
worksheet['!cols'][C].hidden = true;

[robinmackenzie]

@SheetJSDev - I was missing the cellStyles option when reading the book.

The expressions note above are now returning the expected values.

Thanks very much - much appreciated.

[futurist]

I've use below code:

var xlsParseOptions = {
  cellStyles: true, 
  cellFormula: true, 
  cellDates: true,
  cellNF: true,
}
var WB = XLSX.readFile(workbook, xlsParseOptions)

But the WB.Sheets['sheetName']['!cols'] array not contain any hidden info, I've tried several times, the column did hidden in excel, but the hidden info just lost. Anything wrong here?

[Malex]

Hi, not sure if opening another issue or updating this one.
I am currectly wrinting a !cols object before writing my file, the object is correctly istantiated and valued, and yet my excel file is not formatted corretly.
Is it because I'm using the old 2004 format (xls)?