workbook.Names Ref returns **MISSING** sheet reference when named range refers to multiple cells #680

New Issue

2017-06-11T13:56:23Z

bstiel commented

2017-06-11 13:56:23 +00:00

(Migrated from github.com)

workbook.Names returns Ref: "**MISSING**!" when the named range refers to a multi-cell range. Single cell and dynamic named ranges work as expected (NamedRanges.xlsb is attached in the zip).

var wb = XLSX.readFile('NamedRanges.xlsb');
console.log(wb.Workbook.Names);

returns

{"0":{"Name":"DynamicNamedRange","Comment":"","Ref":"OFFSET(Sheet1!$A$5,0,0,2,1)"},"1":{"Name":"MultiCellNamedRange","Comment":"","Ref":"**MISSING**!A2:A4"},"2":{"Name":"SingleCellNamedRange","Comment":"","Ref":"Sheet1!$A$1"}}

NamedRanges.zip

workbook.Names returns Ref: "\*\*MISSING\*\*!<CellRef>" when the named range refers to a multi-cell range. Single cell and dynamic named ranges work as expected (NamedRanges.xlsb is attached in the zip). ``` var wb = XLSX.readFile('NamedRanges.xlsb'); console.log(wb.Workbook.Names); ``` returns `{"0":{"Name":"DynamicNamedRange","Comment":"","Ref":"OFFSET(Sheet1!$A$5,0,0,2,1)"},"1":{"Name":"MultiCellNamedRange","Comment":"","Ref":"**MISSING**!A2:A4"},"2":{"Name":"SingleCellNamedRange","Comment":"","Ref":"Sheet1!$A$1"}}` [NamedRanges.zip](https://github.com/SheetJS/js-xlsx/files/1066467/NamedRanges.zip)

bstiel commented

2017-06-12 14:53:44 +00:00

(Migrated from github.com)

This works for xlsx and xlsm but not for xlsb and xls

SheetJSDev commented

2017-06-12 18:01:39 +00:00

(Migrated from github.com)

Thanks for the report!

XLSX/M store formulae and links as strings (life's easy :)

XLS/XLSB store formulae and links in this binary monstrosity which is effectively a memory dump of an AST for a grammar that Microsoft has conveniently omitted, so we had to backsolve many of the details. Any sort of MISSING string represents an area of the grammar that hasn't been addressed. If you're interested in exploring further, the bulk of the logic is in the fxls bit -- the comments point to the relevant parts of [MS-XLS] and [MS-XLSB].

XLSB and XLS store links in one of three ways:

internal references stored as indices to the sheet names array (pre-2016 versions of Excel used this form when there were no external links)
external link blob (content type application/vnd.ms-excel.externalLink)
supporting link structure (XLS has records like SupBook and ExternSheet, XLSB has records like BrtSupBookSrc and BrtSupSelf)

It's strange that the attached file has no external references yet still fell back to the supporting link structure. Our defined name test only really tests the first case, so we'll need to go back and look at the other two cases.

Thanks for the report! XLSX/M store formulae and links as strings (life's easy :) XLS/XLSB store formulae and links in this binary monstrosity which is effectively a memory dump of an AST for a grammar that Microsoft has conveniently omitted, so we had to backsolve many of the details. Any sort of MISSING string represents an area of the grammar that hasn't been addressed. If you're interested in exploring further, the bulk of the logic is in the [fxls bit](https://github.com/SheetJS/js-xlsx/blob/master/bits/62_fxls.js) -- the comments point to the relevant parts of [MS-XLS] and [MS-XLSB]. XLSB and XLS store links in one of three ways: - internal references stored as indices to the sheet names array (pre-2016 versions of Excel used this form when there were no external links) - external link blob (content type `application/vnd.ms-excel.externalLink`) - supporting link structure (XLS has records like `SupBook` and `ExternSheet`, XLSB has records like `BrtSupBookSrc` and `BrtSupSelf`) It's strange that the attached file has no external references yet still fell back to the supporting link structure. Our defined name test only really tests the first case, so we'll need to go back and look at the other two cases.

Sign in to join this conversation.