syntax definition of structure-reference misses brackets

Regina Henschel 201

Assume you have an Excel Table named myData.

And this Excel Table has first row with labels Name, Gender, Class, Age and Time .

With the syntax rules for structure-reference starting on page 37 in [MS-XLSX] - v20240416, I get this construction:

Gender is a simple-column-name

Age is a simple-column-name

Gender is a column

Age is a column

Gender:Age is a column-range

Gender:Age is a inner-reference

[Gender:Age] is a intra-table-reference

myData[Gender:Age] is a structure-reference

But when you enter

=myData[Gender:Age]

in a cell, you get a syntax error.

In fact, needed is

=myData[[Gender]:[Age]]

Sreekanth Nadendla 411 Reputation points Microsoft Employee

2024-08-05T04:19:03.25+00:00

Hello Regina, thank you for reporting this issue in Microsoft Open Specifications area. One of Office Open Specifications team members will review the details and investigate this issue.

Regards,

Sreekanth Nadendla

Microsoft Windows Open Specifications
Mike Bowen 1,781 Reputation points Microsoft Employee

2024-08-05T21:00:07.7166667+00:00

Hi Regina Henschel,

Thanks for finding this, I will investigate this and let you know soon what I find. Best regards,

Michael Bowen

Microsoft Open Specifications
Emi Zhang-MSFT 24,521 Reputation points Microsoft Vendor

2024-09-11T08:58:38.3+00:00

The answer is very helpful.

Accepted answer

Mike Bowen 1,781 Reputation points Microsoft Employee

2024-08-07T16:21:49.1466667+00:00

Hi Regina Henschel,

I investigated and I can confirm your findings. =myData[Gender:Age] is not a valid structure-reference and we will update MS-XLSX 2.2.2 Formulas to have the correct ABNF grammar to reflect this in a future release.

Thanks again for your efforts to find issues with our documentation, it is important for our documentation to be as accurate as possible, and we appreciate your support.

Best regards,

Michael Bowen

Microsoft Open Specifications
Please sign in to rate this answer.

1 person found this answer helpful.
Regina Henschel 201 Reputation points

2024-08-07T20:27:30.5133333+00:00

And there are a lot of problems with spaces.

The BNF allows [ Gender ] with space, but it must be [Gender].

The BNF allows myData[ [#Headers] ] with space. That opens as myData [#Headers] with space after the table name and it writes as such to file. Reopening gives an error.

The BNF allows myData[ [Gender] ] with space. That opens as myData[ Gender ] and writes it as such to file. Reopening gives an error.

The BNF allows myData[ ] with spaces. Opening gives an error. It must be myData[] without spaces.

Regina Henschel 201 Reputation points

2024-08-07T20:53:30.2366667+00:00

I think the following should work (without guarantee).

intra-table-reference = spaced-lbracket inner-reference spaced-rbracket / keyword / column / "[]" inner-reference = keyword-list / column-range / (keyword-list spaced-comma column-range) / (keyword spaced-comma column-range) keyword = "[#All]" / "[#Data]" / "[#Headers]" / "[#Totals]" / "[#This Row]" keyword-list = ("[#Headers]" spaced-comma "[#Data]") / ("[#Data]" spaced-comma "[#Totals]") column-range = column [":" column] column = ("[" simple-column-name "]")

Mike Bowen 1,781 Reputation points Microsoft Employee

2024-08-08T16:14:55.3933333+00:00

Thanks for writing out the ABNF. I will investigate this and let you know what I find soon.

Michael Bowen

Microsoft Open Specifications

Mike Bowen 1,781 Reputation points Microsoft Employee

2024-08-08T18:52:08.74+00:00

Hi Regina,

I have confirmed the behavior and your interpretation of your first 3 scenarios and I'm working to determine if they're a document issue or an Excel issue, I will let you know the determination. For the 4th bullet, however, I confirmed Excel's behavior, but in this case the ABNF is correct and doesn't allow =myData[ ] with spaces.

=myData[ ] is a structure-reference defined:

structure-reference = [table-identifier] intra-table-reference

myData is the optional table-identifier and intra-table-reference is the brackets and their contents that follow, which is defined:

intra-table-reference = spaced-lbracket inner-reference spaced-rbracket / keyword / ("[" [simple-column-name] "]")

inner-reference cannot be empty so this doesn't allow for empty brackets with spaces

spaced-lbracket inner-reference spaced-rbracket

inner-reference = keyword-list / ([keyword-list spaced-comma] column-range)

keyword-list = keyword / ("[#Headers]" spaced-comma "[#Data]") / ("[#Data]" spaced-comma "[#Totals]")

keyword = "[#All]" / "[#Data]" / "[#Headers]" / "[#Totals]" / "[#This Row]"

("[#Headers]" spaced-comma "[#Data]")

("[#Data]" spaced-comma "[#Totals]")

keyword is limited to only 5 set strings that don't contain spaces

keyword

keyword = "[#All]" / "[#Data]" / "[#Headers]" / "[#Totals]" / "[#This Row]"

simple-column-name must be at least one any-nospace-columncharacter and begin and end with any-nospace-columncharacter and without the simple-column-name, it only permits brackets with no spaces

("[" [simple-column-name] "]")

simple-column-name = [any-nospace-column-character *any-column-character] any-nospace-columncharacter

If there's something I'm missing, please let me know. I meantime, I will let you know what I find with my investigation.

Best regards,

Michael Bowen

Microsoft Open Specifications

Regina Henschel 201 Reputation points

2024-08-08T19:37:50.7933333+00:00

You are right about my last point. I get this derivation: inner-reference > column-range > column > simple-column-name > any-nospace-column-character > unescaped-column-character > character > Char in section 2.2 W3C-XML > [#x1-#xD7FF]. The property "no space" is in the comment to unescaped-column-character.

BTW, allowing the total range [#x1-#xD7FF] (besides the characters mentioned in the comment) looks strange to me.

Mike Bowen 1,781 Reputation points Microsoft Employee

2024-09-04T22:33:45.2733333+00:00

Hi Regina Henschel,

I have investigated this and we will update the ABNF in a future release to be:

intra-table-reference = (spaced-lbracket inner-reference spaced-rbracket) / keyword / ("[" [column-range] "]") inner-reference = keyword-list / ([keyword-list spaced-comma] column-range) keyword = "[#All]" / "[#Data]" / "[#Headers]" / "[#Totals]" / "[#This Row]" keyword-list = keyword / ("[#Headers]" spaced-comma "[#Data]") / ("[#Data]" spaced-comma "[#Totals]") column-range = simple-column-name / (column ":" column) column = ("[" simple-column-name "]") simple-column-name = [any-nospace-column-character *any-column-character] any-nospace-columncharacter

Thanks for all your help with this!

Best regards,

Michael Bowen

Microsoft Open Specifications

Regina Henschel 201 Reputation points

2024-09-08T08:48:46.2233333+00:00

Hi Michael Bowen,

I see these problems with the new ABNF:

(1)

The new ABNF allows myData[ Gender ].

Derivation for that is:

[ Gender ] matches spaced-lbracket inner-reference spaced-rbracket with Gender as inner-reference.

Gender as inner-reference matches column-range.

Gender as column-range matches simple-column-name.

But Excel considers myData[ Gender ] as invalid. When you enter it, Excel corrects it to myDate[Gender]. When it is in file markup, Excel generates a #Ref! error.

When you allow it in the ABNF, then you need to change Excel.

(2)

The new ABNF allows myData[ [#Headers] ].

Derivation for that is:

myData[ [#Headers] ] matches spaced-lbracket inner-reference spaced-rbracket with [#Headers] as inner-reference.

[#Headers] as inner-reference matches keyword-list.

[#Headers] as keyword-list matches keyword.

But Excel changes myData[ [#Headers] ] to the invalid expression myDate [#Headers] , both when you enter the expression or when it is in file markup.

When you allow myData[ [#Headers] ] in the ABNF, then it is questionable whether Excel should change a valid expression. When you decide to keep the current behavior, Excel needs a fix to produce a valid expression. Excel needs to remove not only the brackets but the spaces as well.

So my question is, whether you really intend that myData[ Gender ] and myData[ [#Headers] ] are valid expressions?

Mike Bowen 1,781 Reputation points Microsoft Employee

2024-09-09T15:58:54.1233333+00:00

Hi Regina,

So my question is, whether you really intend that myData[ Gender ] and myData[ [#Headers] ] are valid expressions?

No, this should not be valid. Changing spaced-lbracket inner-reference spaced-rbracke to "[" inner-reference "]" will resolve it:

intra-table-reference = ("[" inner-reference "]") / keyword / ("[" [column-range] "]") inner-reference = keyword-list / ([keyword-list spaced-comma] column-range) keyword = "[#All]" / "[#Data]" / "[#Headers]" / "[#Totals]" / "[#This Row]" keyword-list = keyword / ("[#Headers]" spaced-comma "[#Data]") / ("[#Data]" spaced-comma "[#Totals]") column-range = simple-column-name / (column ":" column) column = ("[" simple-column-name "]") simple-column-name = [any-nospace-column-character *any-column-character] any-nospace-columncharacter

Regina Henschel 201 Reputation points

2024-09-09T17:44:45.9033333+00:00

Hi Mike Bowen, that will not work, because then you lose the valid expressions myData[ [Name]:[Class] ] and myData[ [#Headers],[#Data] ] where a space is allowed.

That was the reason that in my proposal (7. Aug) I had changed it so, that inner-reference covers only the combined forms and the simple forms are listed separately in intra-table-reference.

Mike Bowen 1,781 Reputation points Microsoft Employee

2024-09-09T20:41:07.93+00:00

Hi Regina Henschel,

I think this will resolve the issues:

intra-table-reference = (spaced-lbracket inner-reference spaced-rbracket) / keyword / column / "[]" inner-reference = keyword-list / ((keyword-list / keyword) spaced-comma column) / ([(keyword-list / keyword) spaced-comma] column-range) keyword = "[#All]" / "[#Data]" / "[#Headers]" / "[#Totals]" / "[#This Row]" keyword-list = ("[#Headers]" spaced-comma "[#Data]") / ("[#Data]" spaced-comma "[#Totals]") column-range = column ":" column column = ("[" simple-column-name "]") simple-column-name = [any-nospace-column-character *any-column-character] any-nospace-columncharacter

Regina Henschel 201 Reputation points

2024-09-10T20:17:47.6933333+00:00

Hi Mike Bowen, your last version looks good to me :)

So the only remaining issues are in Excel itself. I think it is better if you make a ticket for your colleagues than me writing a feedback. The issues are:

Excel doesn't mark the user input myData[ [Gender] ] as invalid, but replaces it with the invalid expression myData[ Gender ].

Excel doesn't mark the user input myData[ [#Headers] ] as invalid, but replaces it with the invalid expression myData [#Headers] .

That is, the brackets are removed, but not the spaces.

Mike Bowen 1,781 Reputation points Microsoft Employee

2024-09-10T20:26:44.31+00:00

Hi Regina Henschel,

The update to the ABNF will be in an upcoming release of MS-XLSX. I am working with the Excel team regarding the other issues, but I don't have an ETA for them.

Thanks for your help with this 😀 it's important that our documentation be accurate and we appreciate your contributions.

Best,

Michae Bowen
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

syntax definition of structure-reference misses brackets

0 additional answers

Your answer