How to access Row Object and set properties

[neversaid]

Hi All,

thanks for creating such a great project.
I tried to figure out how its possible to set some Row Properties when creating a XLSX Spreadsheet - i need to set the property "hidden" on the Row to hide it.
How is the best practice to do this?

Thanks a lot,
Bernhard

[SheetJSDev]

@neversaid Row properties are not parsed or written at the moment. It is on the roadmap :)

If you are interested in adding it now, the process is fairly straightforward. The line that writes the row tag on output is:

https://github.com/SheetJS/js-xlsx/blob/master/bits/67_wsxml.js#L244

        if(r.length) o[o.length] = (writextag('row', r.join(""), {r:rr}));

To make rows hidden, add hidden:1 to the object. For example, to hide all rows:

        if(r.length) o[o.length] = (writextag('row', r.join(""), {r:rr, hidden:1}));

I would stick the metadata in an array !rows to the worksheet:

workbook.Sheets[worksheet_name]['!rows'] = []

To set row N to be hidden:

workbook.Sheets[worksheet_name]['!rows'][N-1] = {hidden:true}

Then the write_ws_xml_data function would check at the end of each row:

        if(r.length) {
            var params = {r:rr};
            if(ws['!rows'] && ws['!rows'][R] && ws['!rows'][R].hidden) params.hidden = "1"
            o[o.length] = (writextag('row', r.join(""), params));
        }

[neversaid]

@SheetJSDev
Thanks a lot - that helps - just added the function to save Formulas on my local git checkout.
If you want i can commit, would appreciate to work on your team.

TIA
Bernhard

[SheetJSDev]

Contributions are welcome :)

If you want to send a contribution, can you also add similar logic to the parser? The code is in the same file, and there are two additions to the code:

https://github.com/SheetJS/js-xlsx/blob/master/bits/67_wsxml.js#L152

Just before the for-loop, add an empty array to the worksheet:

s['!rows'] = [];

Later in the code, tag is an object corresponding to the row tag in the xml:

https://github.com/SheetJS/js-xlsx/blob/master/bits/67_wsxml.js#L158

        tag = parsexmltag(x.substr(0,ri), true);

Right after that, save the tag in the array:

        s['!rows'][tag.r] = tag;

We'd also need some tests. For now, keep it simple: generate a file, read it, and check if the hidden property is preserved. As an example roundtrip test, https://github.com/SheetJS/js-xlsx/blob/master/test.js#L400 writes the workbook in memory as a nodejs Buffer and then reads it.