new feature: name a cell range and few minor fixes #150
Conversation
…ofilter in a generated XLSX file
…rs the defined named range for LibreOffice autofilter would not have been properly defined
…racter the repeating rows definition is not defined correctly
| .collect(Collectors.joining(",")); | ||
|
|
||
| w.append("<definedNames>"); |
There was a problem hiding this comment.
If defineName and namesRanges are empty then will never be closed.
There was a problem hiding this comment.
I think you overlooked (it's being closed on line 254 either way).
There was a problem hiding this comment.
So if both defineName and namedRanges are not empty, then it will be closed twice?
There was a problem hiding this comment.
It will be opened and then closed only once, but there are conditional <definedName> tags inside of this <definedNames> tag.
There was a problem hiding this comment.
If they are empty - we'll have <definedNames></definedNames>, otherwise if any of them are non-empty we will have
<definedNames>
<definedName>....sth...</definedName>
<definedName>....sth2...</definedName>
</definedNames>
There was a problem hiding this comment.
Oh. I see it now. I misstook line 221 as </definedNames>. Nevertheless I would be good to cover this part of code in a test.
There was a problem hiding this comment.
Okay, will try to do that. Where should I put this test?
There was a problem hiding this comment.
You can put it in a new class in src/test/java
There was a problem hiding this comment.
Can I put it into PoiCompatibilityTest.java ? I guess all of the existing similar tests are there ?
There was a problem hiding this comment.
Done!
Please include a test that verifies (for example using Apache POI) that a worksheet with a named range is valid.
Done!
The new functionality of naming a Range can be used like this:
IMPORTANT: name of a cell range can only contain letters, numbers and underscore.
(This is an external limitation - LibreOffice Calc also doesn't allow any other characters.)
Besides named ranges, there's a fix for LibreOffice Calc to make it recognize the autofilter if it's been set in the generated XLSX document.
LibreOffice requires a specifically named range "_xlnm._FilterDatabase" to recognize the autofilter in generated documents.
This bug has been reported multiple times to bugzilla, however, many years have passed and no one had changed the way it works.
This way, by defining a named range, fastexcel will be able to generate documents with autofilter that will be opened properly in LibreOffice Calc.
The oddly named range will, ofcourse, also exist when opened in Google Sheets and Excel, but it will just be an extra defined named range.
There were a few minor mistakes from my side earlier, where the localSheetId was hardcoded to 0 - now the worksheet index is properly assigned.
Also, when sheetname had a space or another non-alphabetical characted, the definitions were not correctly written to xlsx - sheetname needs to be surrounded with ' or - in other words - '