Erroneous parsing of defined names

[rjplevin]

I have a workbook that has traveled through many hands, so I don't know the origins of these "hidden" defined names, but they're breaking XLSX.jl. I replaced the error() at the end of parse_workbook! with println("Could not parse named range '$(ref_or_range_str)' in $defined_name_node."), yielding this:

Could not parse named range '2' in <definedName name="lssolver_est" localSheetId="1" hidden="1">2</definedName>.
Could not parse named range '1000' in <definedName name="lssolver_itr" localSheetId="1" hidden="1">1000</definedName>.
Could not parse named range '0' in <definedName name="lssolver_neg" localSheetId="1" hidden="1">0</definedName>.
Could not parse named range '0.000001' in <definedName name="lssolver_piv" localSheetId="1" hidden="1">0.000001</definedName>.

...

I don't know the meaning of "hidden" defined names, but the parser seems to be interpreting the value incorrectly as the name of the range.

[felipenoris]

@rjplevin Thank you for finding this bug! I'll take a look at it shortly.

[felipenoris]

@rjplevin If you use this formula in a Cell: =lssolver_est , does it show 2 as a result?

[rjplevin]

Oddly, no.

I can email you the workbook; I'd rather not attach it here for all of eternity. :~) Email me at plevin@berkeley.edu...

[felipenoris]

Sure! Just sent you an email.

[felipenoris]

Cool! After looking in the internet for examples like these, I see 3 Workbook features not covered by XLSX.jl. I'll work on them ASAP:

• Worksheet level names
• Names associated with constant values, instead of cell ranges
• hidden names.

The fact that it is a hidden name it just means that it doesn't show in the Insert -> Name -> Define Name menu. To change them you have to use VBA. But it works just like any other defined named, and you can reference them from formulas. I guess it was created by an Add-in that is an optimizer in your case.

Just give me some time and I'll fix this in the package.

Thanks!

[felipenoris]

As a reference: https://www.datawright.com.au/excel_resources/excel_sheet_level_names.htm

[felipenoris]

@rjplevin, you're all set. You can read this file using the latest master version. Just use Pkg.checkout("XLSX") to get it. I'll tag a new version by the weekend.

Thanks for finding this bug!