LibreOffice has implemented the database keywords [[#Data]], [[#Headers]], [[#Totals]] and [[#All]]. They work in principle besides bug 162087. But these keywords are not exported, if the file is saved in ODF format. Instead the referenced ranges are written out as absolute range addresses. Import/Export to xlsx could keep these keywords in principle, but I'm not sure whether all cases work correctly. The request is to save these keywords in ODF format too. That requires not only to change the import and export code, but in addition to help to get it included in ODF. Currently ODF has no means to hold this information. This feature becomes more important now, as the SORT function uses #Data as default in Excel and LibreOffice now has SORT function implemented.
+1
Eike, what do you mean by "table structured references"? Or is it only an additional term for to get better Bugzilla search results?
Excel calls them structured references https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e
The MS Open Specification "[MS-XLSX]: Excel (.xlsx) Extensions to the Office Open XML SpreadsheetML File Format" at https://learn.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/3d025add-118d-4413-9856-ab65712ec1b0 lists the ABNF grammar used by formulas. Search for "structure-reference", the following block (modulo comments) is exactly what needs to be added to ODFF as well: structure-reference = [table-identifier] intra-table-reference table-identifier = [book-prefix] table-name table-name = name ;table-name is the name of the table the structure reference refers to. If it is missing, the formula containing the structure reference MUST be entered into a cell which belongs to a table and that table's name is used as the table-name. table-name MUST be the value of the displayName attribute of some table element ([ISO/IEC29500-1:2016] section 18.5.1.2). It MUST NOT be any other user-defined name. intra-table-reference = spaced-lbracket inner-reference spaced-rbracket / keyword / ("[" [simple-column-name] "]") 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 = column [":" column] column = simple-column-name / ("[" *space simple-column-name *space "]") simple-column-name = [any-nospace-column-character *any-column-character] any-nospace-column-character escape-column-character = tick / "[" / "]" / "#" tick = %x27 unescaped-column-character = character ; MUST NOT match escape-column-character or space any-column-character = any-nospace-column-character / space any-nospace-column-character = unescaped-column-character / (tick escape-column-character) spaced-comma = [space] comma [space] spaced-lbracket = "[" [space] spaced-rbracket = [space] "]"
Created attachment 195468 [details] First thoughts about bringing it to ODF I have written down my first thought of how to bring it to ODF, for discussions.
Created attachment 195542 [details] First thoughts about bringing it to ODF Updated version
I have started an issue at Oasis.
(In reply to Eike Rathke from comment #4) > The MS Open Specification "[MS-XLSX]: Excel (.xlsx) Extensions to the Office > Open XML SpreadsheetML File Format" at > https://learn.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/ > 3d025add-118d-4413-9856-ab65712ec1b0 lists the ABNF grammar used by > formulas. Search for "structure-reference", the following block (modulo > comments) is exactly what needs to be added to ODFF as well: > > structure-reference = [table-identifier] intra-table-reference > table-identifier = [book-prefix] table-name > table-name = name > ;table-name is the name of the table the structure reference refers to. If > it is missing, the formula containing the structure reference MUST be > entered into a cell which belongs to a table and that table's name is used > as the table-name. table-name MUST be the value of the displayName attribute > of some table element ([ISO/IEC29500-1:2016] section 18.5.1.2). It MUST NOT > be any other user-defined name. > intra-table-reference = spaced-lbracket inner-reference spaced-rbracket / > keyword / ("[" [simple-column-name] "]") > 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 = column [":" column] > column = simple-column-name / ("[" *space simple-column-name *space "]") > simple-column-name = [any-nospace-column-character *any-column-character] > any-nospace-column-character > escape-column-character = tick / "[" / "]" / "#" > tick = %x27 > unescaped-column-character = character ; MUST NOT match > escape-column-character or space > any-column-character = any-nospace-column-character / space > any-nospace-column-character = unescaped-column-character / (tick > escape-column-character) > spaced-comma = [space] comma [space] > spaced-lbracket = "[" [space] > spaced-rbracket = [space] "]" IDEA What if keywords were "[#All]" for "[#All]" "[#Data]" for [#Data]" "[#RowHeaders]" for "[#Headers]" "[#RowTotals]" for "[#Totals]" "[#This Row]" for "[#This row]" "@Row" for "@" "[#ColHeaders]" for <unavailable expression in MS Excel> "[#ColTotals]" for <unavailable expression in MS Excel> "[#This col]" for <unavailable expression in MS Excel> "@Col" for <unavailable expression in MS Excel> This would be an improving of expressions to use a fully functional DatabaseRange (bidimensional: horizontal and vertical).
(In reply to gamgster from comment #8) > What if keywords were Makes no sense to define something different than Excel that can't be translated to Excel.