How to access Row Object and set properties #81

New Issue

2014-07-08T10:02:36Z

neversaid commented

2014-07-08 10:02:36 +00:00

(Migrated from github.com)

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

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 commented

2014-07-08 16:02:50 +00:00

(Migrated from github.com)

@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 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 commented

2014-07-08 16:06:44 +00:00

(Migrated from github.com)

@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 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 commented

2014-07-08 16:27:58 +00:00

(Migrated from github.com)

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.

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.

Sign in to join this conversation.