DR 16-0007 - SML: omissions and inconsistencies in the specification of attributes

Wed Aug 16 20:08:35 CEST 2017

Dear all

I have finally started making progress on attempting to find a resolution to
this DR. My thanks to Rich McLain and Aarti Nankani for guiding me to the
right place in the Standards Support section of the MSDN Library.

The following document is relevant to resolution of this DR:

*	MS-OE376: Office Implementation Information for ECMA-376 Standards
Support

The following document is presumed not to be relevant, as it is only
concerned with extension elements that are specific to Office and therefore
not documented in the standard:

*	MS-XLSX: Excel (.xlsx) Extensions to the Office Open XML
SpreadsheetML File Format

Note that MS-OE376 refers to the ECMA-376 version of the standard, so the
numbering of clause references in it differs from that in 29500-1. In what
follows I have used 29500-1 clause reference numbering throughout.

MS-OE376 specifies requirements/constraints on attribute values in a number
of additional cases. For example, where Excel requires use of an attribute
that the standard specifies to be optional, I think that would be worth
reviewing. Some of these are, no doubt, specific to the Office
implementation, but ideally these should be checked to see which should
apply to all implementations. 

Here is a list of element-attribute combinations not included in the DR for
which MS-OE376 states that Excel requires the attribute but it is specified
to be optional in the standard:

*	§18.2.14 functionGroup (@name) (complex type:  CT_FunctionGroup)
*	§18.2.22 smartTagType (@namespaceUri) (complex type:
CT_SmartTagType) – @name is covered by the DR
*	§18.3.1.69 pivotSelection (@r:id) (complex type: CT_PivotSelection)
*	§18.3.2.1 colorFilter (@dxfId) (complex type: CT_ColorFilter)
*	§18.5.2.3 xmlCellPr (@uniqueName) (complex type: CT_XmlCellPr)
*	§18.10.1.6 cacheHierarchy (@caption) (complex type:
CT_CacheHierarchy)
*	§18.10.1.47 kpi (@caption) (complex type: CT_PCDKPI)
*	§18.11.1.3 nc (@r) (complex type: CT_Cell)

The note in MC-OE376 regarding @r on §18.11.1.3 nc (New Cell Data) has
caused me to look at what MC-OE376 has to say about @r on §18.3.1.3 c
(Cell):

c. The standard makes @r optional in the schema, but the prose does not
explain the meaning of the default behavior.

If this attribute is not specified, the cell shall be located in the column
with the index that is 1 greater than that of the previous cell in the
parent row collection. If this attribute is not specified and the cell is
the first c element in the parent row collection, the cell shall be located
in the first column.

Do we need to document this default behaviour in the standard? See
discussion below regarding omission of the attribute @r on §18.3.1.73 row.

Here is a list of element-attribute combinations not included in the DR for
which MS-OE376 states that Excel uses a default value not specified by the
standard:

*	§18.2.2 calcPr (@calcId) (complex type: CT_CalcPr) – default value 0
*	§18.2.28 workbookPr (@defaultThemeVersion) (complex type:
CT_WorkbookPr) – default value 0
*	§18.3.1.10 cfRule (@rank) (complex type: CT_CfRule) – default value
0
*	§18.3.1.33 dataValidations (@count, @xWindow, @yWindow) (complex
type: CT_DataValidations) – in each case default value 0
*	§18.3.1.52 inputCells (@numFmtId) (complex type: CT_InputCells) –
default value 0
*	§18.3.1.68 pivotArea (@field) (complex type: CT_PivotArea) – default
value –1
*	§18.3.1.78 selection (@activeCell) (complex type: CT_Selection) –
default value A1
*	§18.3.1.93 tabColor (@auto) (complex type: CT_TabColor) – default
value false
*	§18.3.2.4 dateGroupItem (@day, @month) (complex type:
CT_DateGroupItem) – default value 1 [Note: @year is required]
*	§18.3.2.4 dateGroupItem (@hour, @minute, @second) (complex type:
CT_DateGroupItem) – default value 0
*	§18.5.1.2 table (@connectionId) (complex type: CT_Table) – default
value 0
[Nit note: The description of this attribute contains poorly-worded
normative text: “This shall only be used for tables that are based off of
xmlupon XML maps.”]
*	§18.8.1 alignment (@horizontal, @vertical) (complex type:
CT_CellAlignment) – default values of general and bottom respectively
*	§18.8.1 alignment (@indent, @relativeIndent, @textRotation) (complex
type: CT_CellAlignment) – in each case default value 0
*	§18.8.1 alignment @justifyLastLine, @readingOrder, @shrinkToFit,
(@wrapText) (complex type: CT_CellAlignment) – in each case default value
false
*	§18.8.4 border (@diagonalDown, @diagonalUp) (complex type:
CT_Border) – in each case default value false
*	§18.8.33 protection (@hidden @locked) (complex type:
CT_CellProtection) – default values of false and true respectively
*	§18.10.1.33 filter (@iMeasureFld) (complex type: CT_PivotFilter) –
default value 0
*	§18.10.1.57 mp (@pPos, @pLen, @nameLen) (complex type:
CT_MemberProperty) – in each case default value 0
*	§18.10.1.69 pivotField (@dataSourceSort, @numFmtId) (complex type:
CT_PivotField) – default values of false and true respectively
*	§18.10.1.73 pivotTableDefinition (@applyAlignmentFormats,
@applyBorderFormats, @applyFontFormats, @applyNumberFormats,
@applyPatternFormats, @applyWidthHeightFormats, @dataPosition) (complex
type: CT_pivotTableDefinition) – in each case default value 0
[Nit note: inconsistency in name of complex type – should ideally be
CT_PivotTableDefinition – probably not worth correcting, unfortunately
]
*	§18.10.1.74 pivotTableStyleInfo (@showColHeaders, @showColStripes,
@showRowHeaders, @showRowStripes) (CT_PivotTableStyle) – in each case
default value 0
*	§18.10.1.88 set (@count) () (complex type: CT_Set) – default value
4294967295
*	§18.11.1.1 header (@maxRId, @minRId) (complex type:
CT_RevisionHeader) – Excel uses a default value 0 for @minRId, and a default
value equal to the current value of @minRId for @maxRId
*	§18.11.1.9 rcc (@numFmtId) (complex type: CT_RevisionCellChange) –
default value 0
*	§18.16.1 DataBinding (@FileBinding) (complex type: CT_DataBinding) –
default value false

More work for a further DR here, I think

Resolutions are indicated below for each of the first ten elements listed in
the DR. For convenience, I have list the elements that remain to be
considered at the end.

Francis

§18.2.2 calcPr (@forceFullCalc) (complex type: CT_CalcPr)

MS-OE376, subsection 2.1.598 (page 248) includes the following note:

e. The standard does not specify a default value for the forceFullCalc
attribute.

Excel uses a default value of false for this attribute.

There doesn’t appear to be any mechanism in the Excel 2016 user interface
for specifying a value for this attribute. By default, Excel 2016 doesn’t
save an explicit value for this attribute, and nor does LibreOffice Calc.

Proposed resolution: Add default value false to schema and prose, having
first checked that the other major implementation, LibreOffice Calc, either
uses the same default value or ignores this feature.

§18.2.7 ext (@uri) (complex type: CT_Extension)

There is no note on this attribute in MS-OE376. The attribute is clearly
optional, and its omission simply implies that there is no version or
application information available for this extension, or it is implied by
the content of the <ext> element.

Proposed resolution: For clarity, add use=”optional” to the schema, and
change the description of the attribute @uri in prose to read: “If present,
a token to identify version and application information for this particular
extension”. However, it may be simpler to take no action.

§18.2.22 smartTagType (@name) (complex type: CT_SmartTagType)

MS-OE376, subsection 2.1.614 (page 257) includes the following note:

d. The standard states that the name attribute is optional.

Excel requires this attribute.

So far as I can judge, this is a feature that is supported fully (i.e.
susceptible to user control) only by Excel 2007. However, given its intended
use in Excel 2007, it doesn’t make sense for a smart tag not to have a name,
so I believe that the schema is at fault and this attribute should be
required.

Proposed resolution: Change the schema to make this attribute required. No
change to prose.

§18.3.1.2 autoFilter (@ref) (complex type: CT_AutoFilter)

There is no note on this attribute in MS-OE376. The attribute is clearly
required, since data filtering is always applied to a specified range of
cells. The absence of @use on the attribute declaration in the schema is
possibly caused by a misunderstanding of the default value of @use on
attribute declarations in XSD (see W3C XML Schema Part 1, section 3.2.2).

Proposed resolution: Add use=”required” to the schema. No change to prose.

§18.3.1.10 cfRule (@type) (complex type: CT_CfRule)

MS-OE376, subsection 2.1.628 (page 267) includes the following note:

a. The standard implies that the type attribute is optional.

Excel requires this attribute.

I don’t believe that use of this element makes sense without @type being
specified, since it specifies the type of rule to be used to determine
whether a conditional formatting rule should be applied to a cell or not.
Without specifying the rule type, the condition cannot be determined.

Proposed resolution: Add use=”required” to the schema. No change to prose.

§18.3.1.73 row (@r) (complex type: CT_Row)

There is a note on this attribute in MS-OE376 (limiting the value range),
but no mention of the attribute being optional in the standard or how to
interpret its omission. Given what MS-OE376 says about how to interpret the
omission of @r on §18.3.1.3 c (Cell), I have tested Excel’s ability to open
a spreadsheet with @r omitted on rows. Excel will successfully open the
spreadsheet in certain limited circumstances, e.g. the following opens
without error and omission of @r on the second <row> element is taken to
imply a value ‘2’:

    <sheetData>

        <row r="1">

            <c r="A1">

                <v>1</v>

            </c>

        </row>

        <row>

            <c r="A2">

                <v>2</v>

            </c>

        </row>

        <row r="5">

            <c r="A5">

                <f>A1+A2</f>

                <v>3</v>

            </c>

        </row>

    </sheetData>

However, the values of @r on the <row> and <c> elements have to be
consistent, and if the @r value on the cell in the second row is changed
from ‘A2’ to ‘A3’, Excel reports an error, because the value of @r on the
second <row> element is implied to be ‘2’.

Proposed resolution: Add the following to the description of @r in prose: 

If this attribute is not specified, the row index shall be 1 greater than
that of the previous row in the parent element. If this attribute is not
specified and the row is the first in the parent element, the row index
shall be ‘1’.

We should also consider making a similar change to the description of @r on
§18.3.1.3 c (Cell) (see above).

§18.3.2.1 customFilter (@val) (complex type: CT_CustomFilter)

There is no note on this attribute in MS-OE376. The attribute @operator on
the same element clearly only makes sense if @val is specified, and
@operator has a default value equal. The absence of @use on the attribute
declaration in the schema is possibly caused by a misunderstanding of the
default value of @use on attribute declarations in XSD (see W3C XML Schema
Part 1, section 3.2.2).

Proposed resolution: Add use=”required” to the schema. No change to prose.

§18.3.2.6 filter (@val) (complex type: CT_Filter)

MS-OE376, subsection 2.1.694 (page 301) includes the following note:

a. The standard implies that the val attribute is optional.

Excel requires this attribute.

This element makes no sense if @val is omitted, since its purpose is to
specify a filter criteria value. The absence of @use on the attribute
declaration in the schema is possibly caused by a misunderstanding of the
default value of @use on attribute declarations in XSD (see W3C XML Schema
Part 1, section 3.2.2).

Proposed resolution: Add use=”required” to the schema. No change to prose.

§18.3.2.10 top10 (@filterVal) (complex type: CT_Top10)

MS-OE376, subsection 2.1.698 (page 302) includes the following note:

b. The standard does not specify a default value for the filterVal
attribute.

Excel uses a default value of 0 for this attribute.

Tests in Excel suggest that @filterVal simply preserves the lowest (if
filtering top values) or highest (if filtering bottom values) value to be
shown when opening the document. As such, its value is implied by the value
of @val, @percent (if present) and the range of values being filtered. If
@filterVal is omitted, Excel will simply re-calculate it when opening the
document and store it when saving the document. If an incorrect value is
inserted, Excel ignores but preserves the incorrect value until the filter
is re-specified. I therefore don’t understand what is meant by the note in
MS-OE376 saying that Excel uses a default value of 0 for this attribute.

I note in passing that the example given in the standard (pages 1716-1717)
is incorrect, because <top percent=”1” val=”5” filterVal=”6”> implies the
top 5 percent, not the top 10 percent as suggested in the prose.

Proposed resolution: No change to the schema or normative prose, but I
propose that the example be re-worded as follows:

[Example: This example filters the first column by the top 105 percent of
the values in that column. For all cells in the column whose value falls
outside the top 105 percent of the value in that column, the rows
corresponding to those cells are hidden from the view. In this example, if
there are 6 cells in the range, containing 1, 2, 3, 4, 5, 6 respectively,
all rows except the row containing 6 will be hidden.

§18.5.1.5 tableStyleInfo (@showColumnStripes, @showFirstColumn,
@showLastColumn, @showRowStripes) (complex type: CT_TableStyleInfo)

MS-OE376, subsection 2.1.710 (page 306) includes the following notes:

b. The standard does not specify a default value for the showColumnStripes
attribute.

Excel uses a default value of false for this attribute.

c. The standard does not specify a default value for the showFirstColumn
attribute.

Excel uses a default value of false for this attribute.

d. The standard does not specify a default value for the showLastColumn
attribute.

Excel uses a default value of false for this attribute.

e. The standard does not specify a default value for the showRowStripes
attribute.

Excel uses a default value of false for this attribute.

LibreOffice preserves table styling in a spreadsheet prepared in Excel
containing a table, but LibreOffice doesn’t use table styling and the user
cannot create tables and can only remove them by removing the cells
contained within it. In that sense, all the above defaults specified for
Excel would also apply to LibreOffice. No other defaults make any sense from
an interop perspective.

Proposed resolution: Add default=“false” to each of the four attribute
declarations in the schema.

ELEMENTS IN DR 16-0007 NOT YET CONSIDERED

The following 21 elements in the DR have yet to be considered:

§18.8.7 cellStyle (@name) (complex type: CT_CellStyle)

§18.8.34 rgbColor (@rgb) (complex type: CT_RgbColor)

§18.10.1.2 b (@cp, @f, @u) (complex type: CT_Boolean)

§18.10.1.8 calculatedItem (@formula) (complex type: CT_CalculatedItem)

§18.10.1.21 d (@cp, @f, @u) (complex type: CT_DateTime)

§18.10.1.27 e (@bc, @cp, @f, @fc, @in, @u) (complex type: CT_Error)

§18.10.1.45 item (@x) (complex type: CT_Item)

§18.10.1.50 m (@bc, @cp, @f, @fc, @in, @u) (complex type: CT_Missing)

§18.10.1.51 map (@dimension, @measureGroup) (complex type:
CT_MeasureDimensionMap)

§18.10.1.60 n (@bc, @cp, @f, @fc, @in, @u) (complex type: CT_Number)

§18.10.1.74 pivotTableStyleInfo (@showColHeaders, @showColStripes,
@showLastColumn, @showRowHeaders, @showRowStripes) (complex type:
CT_PivotTableStyle)

§18.10.1.85 s (@bc, @cp, @f, @fc, @in, @u) (complex type: CT_String)

§18.10.1.86 serverFormat (@culture, @format) (complex type: CT_ServerFormat)

§18.10.1.92 tpl (@fld, @hier) (complex type: CT_Tuple)

§18.13.1 connection (@name, @type) (complex type: CT_Connection)

§18.13.3 dbPr (@command) (complex type: CT_DbPr)

§18.13.5 olapPr (@rowDrillCount) (complex type: CT_OlapPr)

§18.13.12 textPr (@characterSet) (complex type: CT_TextPr) – the attribute
@codePage was moved to Part 4, but is still mentioned here and in §18.2.24
webPublishing.

§18.13.13 webPr (@url) (complex type: CT_WebPr)

§18.14.1 cell (@r) (complex type: CT_ExternalCell)

§18.14.15 definedName (@refersTo) (complex type: CT_ExternalDefinedName)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.vse.cz/pipermail/sc34wg4/attachments/20170816/a55cdaf7/attachment-0001.html>