ws.autoFilter not working #623

New Issue

2017-04-06T20:06:30Z

jcarvin commented

2017-04-06 20:06:30 +00:00

(Migrated from github.com)

The docs mention the autoFilter option on a worksheet object. I attempted to enable it, but my worksheets still don't open with autoFilter enabled.

ws.autoFilter = false as well as ws.autoFilter = true do nothing.

Does anyone know of a solution?

The docs mention the autoFilter option on a worksheet object. I attempted to enable it, but my worksheets still don't open with autoFilter enabled. `ws.autoFilter = false` as well as `ws.autoFilter = true` do nothing. Does anyone know of a solution?

SheetJSDev commented

2017-04-06 20:15:06 +00:00

(Migrated from github.com)

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

The autoFilter in the docs refer to the sheet protection: "functionality disabled if value is true". The protection isn't really relevant if the worksheet doesn't have a filter in the first place, but we tried to address all of the relevant protection information in one fell swoop.

We aren't reading or writing the auto filter information, but that's something we're looking into. Stay tuned.

https://github.com/sheetjs/js-xlsx#worksheet-object The `autoFilter` in the docs refer to the sheet protection: "functionality disabled if value is true". The protection isn't really relevant if the worksheet doesn't have a filter in the first place, but we tried to address all of the relevant protection information in one fell swoop. We aren't reading or writing the auto filter information, but that's something we're looking into. Stay tuned.

jcarvin commented

2017-04-06 20:16:53 +00:00

(Migrated from github.com)

ahh, okay. Thank you for the quick feedback!

SheetJSDev commented

2017-04-06 20:36:12 +00:00

(Migrated from github.com)

@jcarvin if you are feeling adventurous and want to contribute, it's not hard to add it in!

In the write_ws_xml function, the actual XML corresponding to auto filters should appear after the sheet protection and before the merge cells, so it would be in this location

So if you wanted to hard code something, the simplest change would be something like:

if(ws['!autoFilterXML']) o[o.length] = ws['!autoFilterXML'];

and you could set the worksheet !autoFilterXML property to the raw XML. We have a test file if you want to see how the XML works (unzip and look at the various xl/worksheets/sheet#.xml files)

That's obviously not desirable long-term, we'd want some sort of JS representation instead of passing raw XML, but we'd need to play around a bit before settling on something.

@jcarvin if you are feeling adventurous and want to contribute, it's not hard to add it in! In the `write_ws_xml` function, the actual XML corresponding to auto filters should appear after the sheet protection and before the merge cells, so it would be [in this location](https://github.com/SheetJS/js-xlsx/blob/master/bits/67_wsxml.js#L370) So if you wanted to hard code something, the simplest change would be something like: ```js if(ws['!autoFilterXML']) o[o.length] = ws['!autoFilterXML']; ``` and you could set the worksheet `!autoFilterXML` property to the raw XML. We have [a test file](https://github.com/SheetJS/test_files/blob/master/AutoFilter.xlsx) if you want to see how the XML works (unzip and look at the various `xl/worksheets/sheet#.xml` files) That's obviously not desirable long-term, we'd want some sort of JS representation instead of passing raw XML, but we'd need to play around a bit before settling on something.

👍 1

jcarvin commented

2017-04-06 20:43:03 +00:00

(Migrated from github.com)

Oh, wow okay! That's incredibly helpful. I'll do some tinkering with it tomorrow. Thanks again!

jcarvin commented

2017-04-07 18:14:10 +00:00

(Migrated from github.com)

I ended up hard coding the autoFilters in as you suggested. Worked like a charm. I am, however, wondering if there is a way to make a similar change that will freeze the top row. Looking into that now. If you have any suggestions, I would be grateful.

SheetJSDev commented

2017-04-07 18:26:10 +00:00

(Migrated from github.com)

The freeze options are set in the pane tag within the sheet views. In the XML it will appear right after the dimension tag, so https://github.com/SheetJS/js-xlsx/blob/master/bits/67_wsxml.js#L356 is probably the right place. I think something like this should take a parameter !freezerow in the worksheet: (warning: untested)

var freezerow = ws["!freezerow"] || 0;
if(freezerow > 0) { 
  var freezeref = "A" + encode_row(freezerow + 1);
  o[o.length] = '<sheetViews><sheetView tabSelected="1" workbookViewId="0"><pane ySplit="1" topLeftCell="' + freezeref + '" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>'
}

It might be necessary to add a selection within the sheetview <selection pane="bottomLeft" activeCell="A1" sqref="A1"/> but I haven't experimented yet.

The freeze options are set in the pane tag within the sheet views. In the XML it will appear right after the dimension tag, so https://github.com/SheetJS/js-xlsx/blob/master/bits/67_wsxml.js#L356 is probably the right place. I think something like this should take a parameter `!freezerow` in the worksheet: (warning: untested) ```js var freezerow = ws["!freezerow"] || 0; if(freezerow > 0) { var freezeref = "A" + encode_row(freezerow + 1); o[o.length] = '<sheetViews><sheetView tabSelected="1" workbookViewId="0"><pane ySplit="1" topLeftCell="' + freezeref + '" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>' } ``` It might be necessary to add a selection within the sheetview `<selection pane="bottomLeft" activeCell="A1" sqref="A1"/>` but I haven't experimented yet.

👍 1

jcarvin commented

2017-04-07 18:32:52 +00:00

(Migrated from github.com)

Thank you so much.... I'll take some shots at that, asap.

SheetJSDev commented

2017-04-07 18:51:27 +00:00

(Migrated from github.com)

In general, to figure out what's going on, you should:

generate a file with the feature you want, save it, and inspect the XML to see what needs to be added. What I like to do is create a sample file without the feature, save it to one file, then add the feature and save to a second file. That way, comparing the XML lets me see what changed.
search for the relevant tag in the spec -- you'll see a bunch of references in the read portion of the code which should help guide your search

In this case:

18.3.1.99 is the worksheet tag
18.3.1.88 is the sheetViews tag
18.3.1.87 is the sheetView tag
18.3.1.66 is the actual pane tag

figure out what options are supported. The table in 18.3.1.66 (page 1656 of the part 1 pdf) shows you the options. At the end of that part, you'll see a reference to the type CT_Pane in the schema, which does a decent job of telling you what must be included and what fields are optional.
generate the xml. For simple features you can literally write the code, but for something more complex you might want to use some of the helper functions. writextag takes a tag name, a body, and an attributes object and generates the corresponding xml entry.
figure out where to put the tag. Even though it is XML, putting the data in the wrong place may result in unreadable files. For small stuff, you can just look at your sample file and guess where to put the data, but to be complete you should dig into the schema for the parent tag (in this case CT_Worksheet, page 3900).

If you do add something, feel free to send a PR with changes. The hardest part of the process is settling on a JS representation that makes sense -- if it's too close to the underlying XLSX format, it won't be easy to use in your app

In general, to figure out what's going on, you should: 1) generate a file with the feature you want, save it, and inspect the XML to see what needs to be added. What I like to do is create a sample file without the feature, save it to one file, then add the feature and save to a second file. That way, comparing the XML lets me see what changed. 2) search for the relevant tag in [the spec](http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-376,%20Fourth%20Edition,%20Part%201%20-%20Fundamentals%20And%20Markup%20Language%20Reference.zip) -- you'll see a bunch of references in the read portion of the code which should help guide your search In this case: - 18.3.1.99 is the worksheet tag - 18.3.1.88 is the sheetViews tag - 18.3.1.87 is the sheetView tag - 18.3.1.66 is the actual pane tag 3) figure out what options are supported. The table in 18.3.1.66 (page 1656 of the part 1 pdf) shows you the options. At the end of that part, you'll see a reference to the type CT_Pane in the schema, which does a decent job of telling you what must be included and what fields are optional. 4) generate the xml. For simple features you can literally write the code, but for something more complex you might want to use some of the helper functions. `writextag` takes a tag name, a body, and an attributes object and generates the corresponding xml entry. 5) figure out where to put the tag. Even though it is XML, putting the data in the wrong place may result in unreadable files. For small stuff, you can just look at your sample file and guess where to put the data, but to be complete you should dig into the schema for the parent tag (in this case CT_Worksheet, page 3900). If you do add something, feel free to send a PR with changes. The hardest part of the process is settling on a JS representation that makes sense -- if it's too close to the underlying XLSX format, it won't be easy to use in your app

namoscato commented

2022-01-18 17:47:47 +00:00

(Migrated from github.com)

We aren't reading or writing the auto filter information

@SheetJSDev, I wanted to followup on this and clarify what you mean here.

We're looking to parse an Excel file that was saved with filters but do not see any representation of the filters in the resulting sheet object.

Is this expected? Are there any updated plans to support this? Does this open issue account for parsing or just writing? How does this relate to bb536a0d79?

> We aren't reading or writing the auto filter information @SheetJSDev, I wanted to followup on this and clarify what you mean here. We're looking to _parse_ an Excel file that was saved with [filters](https://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e) but do not see any representation of the filters in the resulting sheet object. Is this expected? Are there any updated plans to support this? Does this open issue account for _parsing_ or just writing? How does this relate to https://github.com/Folcon/js-xlsx/commit/bb536a0d79644718f79f205eca3c025be3d6fa0e?

SheetJSDev commented

2022-06-11 19:03:42 +00:00

(Migrated from github.com)

0.18.9 completely settles auto filter issues.

https://docs.sheetjs.com/docs/csf/sheet#worksheet-object ws["!autofilter"] will be assigned when parsing and will be serialized. It will also rewrite the relevant defined names for Excel formats.

0.18.9 completely settles auto filter issues. https://docs.sheetjs.com/docs/csf/sheet#worksheet-object `ws["!autofilter"]` will be assigned when parsing and will be serialized. It will also rewrite the relevant defined names for Excel formats.

👍 1

Sign in to join this conversation.