Transcript SpreadsheetML Advanced

SpreadsheetML Advanced
Open XML Developer Workshop
Disclaimer
The information contained in this slide deck represents the current view of Microsoft Corporation on the issues discussed as of the date of
publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the
part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This slide deck is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE
INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this slide
deck may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic,
mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft
Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this
slide deck. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this slide deck does not give
you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events
depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo,
person, place or event is intended or should be inferred.
© 2006 Microsoft Corporation. All rights reserved.
Microsoft, 2007 Microsoft Office System, .NET Framework 3.0, Visual Studio, and Windows Vista are either registered trademarks or
trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Open XML Developer Workshop
Objectives
This module covers SpreadsheetML concepts that are used
in typical spreadsheet documents:
Formatting options
Anatomy of the styles part
Styles and themes
Named ranges
Comments
Pivot tables
Open XML Developer Workshop
SpreadsheetML Formatting Options
Direct Cell Formatting (XF)
Fonts
Fills
Borders
Numeric Formatting
Cell Styles
Table Styles
PivotTable Styles
Open XML Developer Workshop
STYLES
Open XML Developer Workshop
Style part (styles.xml)
Contents of the <styleSheet> element:
Description
Tag
Number Format Expressions
<numFmts>
Font Definitions
<fonts>
Fill Definitions
<fills>
Border Definitions
<borders>
Master Records – Cell Styles
<cellStyleXfs>
Master Records – Formatting
<cellXfs>
Cell Styles (named for UI)
<cellStyles>
Differential Formatting Records
<dxfs>
Custom Table Styles
<tableStyles>
Open XML Developer Workshop
Number Format Expressions <numFmts>
Cell number format definitions
The numFmtId attribute uniquely identifies each entry
Number formats don’t use 0-based index like other style definitions
Example (for currency formatting):
# of formats defined
Formatting expression
<numFmts count="1">
<numFmt numFmtId="164" formatCode=""$"#,##0.00" />
</numFmts>
ID for this formatting style (to apply it directly)
May not reference theme definitions
Open XML Developer Workshop
Font Definitions <fonts>
Referenced by index (fontId) from master records
Properties may reference theme definitions
fontId=0
fontId=1
fontId=2
<fonts count="3">
<font>
<sz val="11" />
<color theme="1" />
<name val="Calibri" />
<family val="2" />
<scheme val="minor" />
</font>
<font>
<sz val="8" />
<color indexed="81" />
<name val="Tahoma" />
<family val="2" />
</font>
<font>
<b />
<sz val="8" />
<color RGB=“FFABCDEF" />
<name val="Tahoma" />
<family val="2" />
</font>
</fonts>
Open XML Developer Workshop
Color 1 from theme definition
Indexed color reference
Bold version of above font
Fill Definitions <fills>
Referenced by index (fillId) from master records
ST_PatternType fill patterns documented in §3.18.57
<fills count="2">
<fill>
<patternFill patternType="none" />
</fill>
<fill>
<patternFill patternType="gray125" />
</fill>
<fill>
<patternFill patternType=“solid”>
<fgColor theme=“4”/>
<bgColor theme=“7”/>
</patternFill>
</fills>
Open XML Developer Workshop
Color 4 from theme definition
Master Record – Cell Styles <cellStyleXfs>
Groups together a specific combination of style settings
for use in a cellStyle definition
<cellStyleXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" />
</cellStyleXfs>
Border definition (0-based index)
Fill definition (0-based index)
Font definition (0-based index)
Number format expression (unique ID)
Master records are referenced by index (xfId)
Open XML Developer Workshop
Master Record – Formatting <cellXfs>
Groups together a specific combination of style settings
for use in directing formatting from a cell
<cellXfs count="2">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
<xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" />
</cellXfs>
NOTE: master records are referenced by index and not
xfId (§3.7.3.6). For example, to reference the 2nd master
record above (i.e., index=1):
<c r=“A1” s=“1” t=“s”>
<v>0</v>
</c>
Open XML Developer Workshop
Master Record Direct Formatting
DEMO
Open XML Developer Workshop
Styles & Themes
We have seen examples of direct formatting
Rich text settings in the <rPr> run properties element
Applied directly to cell contents or comment text
Formatting can also be specified by a style
Named collection of formatting elements
Style types: cell style, table style, pivotTable style
A style may refer to a theme definition
Themes define a set of colors, font information, and shape effects
Direct formatting may also refer to a theme
Picking a different theme changes the styles/formatting that refer to it
Open XML Developer Workshop
Applying Cell, Table, PivotTable Styles
Styles are named
<cellStyles count=“2">
<cellStyle name="Normal" xfId="0” />
<cellStyle name=“Accent1" xfId=“1” />
</cellStyles>
Explicit formatting records (xf) define the formatting
properties
Open XML Developer Workshop
Differential Formatting Records <dxfs>
Groups of formatting information that can be applied as
a differential/additive style
Referenced by index (0-based) from a tableStyle element
When applied, only the specified properties are changed
and all other formatting properties retain their values
<dxfs count=“1”>
<dxf>
<font>
<b/>
<color theme=“0”/>
</font>
<fill>
<patternFill patternType=“solid”>
<fgColor theme=“4”/>
<bgColor theme=“5”/>
</patternFill>
</fill>
</dxf>
</dxfs>
Open XML Developer Workshop
Change font to bold and
change color, leave other font
properties unchanged
Custom Table Styles <tableStyles>
Only custom-defined styles are saved – predefined
Table/PivotTable styles are not saved
<tableStyles count=“1" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16" />
<tableStyle name=“TableStyleMedium10 – Custom” pivot=“0” count=“1”>
<tableStyleElement type=“WholeTable” dxfId=“6”/>
Apply the 7th differential formatting
</tableStyle>
record to the “whole table” region
</tableStyles>
Predefined Table/PivtoTable styles – see §3.8.40
13 table regions can be formatted
15 additional PivotTables regions
All predefined styles reference themes
Custom styles may reference themes
Open XML Developer Workshop
Custom Table Style
Styles part:
Table definition:
Open XML Developer Workshop
Style & Formatting Example
Left set of numbers have direct & themed formatting
Table has a table style applied
PivotTable has a table style applied
Open XML Developer Workshop
Vary The Theme
Since all colors are specified as theme colors, they change
when a different theme is selected
Cells with non-theme colors specified wouldn’t change color
with theme changes
Open XML Developer Workshop
conditionalFormatting
Defined in worksheet part
Applied to a range of cells
<conditionalFormatting sqref="A1:A9">
<cfRule type="dataBar" priority="3">
<dataBar>
<cfvo type="min" val="0"/>
<cfvo type="max" val="0"/>
<color rgb="FF638EC6"/>
</dataBar>
</cfRule>
</conditionalFormatting>
DEMO
Open XML Developer Workshop
NAMED RANGES
Open XML Developer Workshop
Named Range (defined at workbook level)
Name – required
Description – user-viewable
Comment – internal documentation
Function/hidden/vbProcedure – booleans
Scope defined by localSheetId
Other attributes – see §3.2.5
<definedNames>
<definedName name=“MyFormula” comment=“My comment.">SUM(Sheet3!$B$2:$B$9)</definedName>
<definedName name=“MyRange1">Sheet3!$A$1:$C$12</definedName>
<definedName name=“MyRange2" localSheetId="2” hidden="1">Sheet5!$A$1:$T$47</definedName>
</definedNames>
DEMO
Open XML Developer Workshop
COMMENTS
Open XML Developer Workshop
Comment Example
Typical comment
Key concepts:
• Comments are not stored in the worksheet
• Each worksheet may have a comment part
• Contains all comments for that worksheet
• Each comment is attached to a cell (A1 notation)
• Drawing/rendering information is stored separately
• VML-based legacy format
• Implicit worksheet relationships point to comment part
and drawing part
Open XML Developer Workshop
Comment part
<comments xmlns="...spreadsheetml...">
<authors>
<author>John Smith</author>
</authors>
<commentList>
<comment ref="D4" authorId="0">
<text>
<r>
<rPr>
<b />
<sz val="8" />
<color indexed="81" />
Author 0 (from the list)
<rFont val="Tahoma" />
<family val="2" />
</rPr>
Comment for cell D4
<t>John:</t>
</r>
<r>
<rPr>
<sz val="8" />
<color indexed="81" />
Rich text formatting
<rFont val="Tahoma" />
<family val="2" />
</rPr>
<t xml:space="preserve">Why is shipping so high on this item?</t>
</r>
</text>
</comment>
</commentList>
</comments>
Open XML Developer Workshop
DEMO
PIVOT TABLES
Open XML Developer Workshop
Pivot Tables
Column axis
Row axis
Values
Pivot tables simplify large data sets
Aggregate information by rows/columns
Two components must be defined:
1.
The pivot cache definition
•
Defines the data source
•
Workbook has a relation to the
pivot cache definition
2. The pivot table definition
•
Defines the appearance
•
Worksheet has a relationship
to the pivot table definition
Workbook
1
1
n
n
Sheet
PivotCacheDefinition
1
1
n
PivotTable
Open XML Developer Workshop
n
Pivot Cache Definition
Defines data source (may be internal or external)
Defines pivot-table fields and pivot values
<pivotCacheDefinition refreshOnLoad="1" invalid="1">
<cacheSource type="worksheet">
<worksheetSource ref="A1:G1525" sheet="Sheet1" />
</cacheSource>
<cacheFields count="2">
<cacheField name="Territory" numFmtId="0">
<sharedItems count="10">
<s v="Canada" />
<s v="Northwest" />
<s v="Southeast" />
...
<s v="Australia" />
</sharedItems>
</cacheField>
<cacheField name="Sale Total" numFmtId="0">
<sharedItems containsSemiMixedTypes="0“
containsString="0“ containsNumber="1" />
</cacheField>
</cacheFields>
</pivotCacheDefinition>
Open XML Developer Workshop
Data source
Cache field
Pivot values
Pivot Table Definition
Defines the look & feel of the pivot table
<pivotTableDefinition name="PivotTable1"
cacheId="1“ dataCaption="Value">
Associated cache
definition (ID from
workbook part)
<location ref="A1:B12" firstHeaderRow="1"
firstDataRow="2" firstDataCol="1" />
<pivotFields count="2">
<pivotField axis="axisRow" showAll="0">
<items count="11">
<item x="0" />
<item x="1" />
...
<item t="default" />
</items>
</pivotField>
<pivotField dataField="1" showAll="0" />
</pivotFields>
<rowFields count="1">
<field x="0" />
</rowFields>
<dataFields count="1">
<dataField name="Sum of Sales" fld="1" />
</dataFields>
</pivotTableDefinition>
Open XML Developer Workshop
Location of the
pivot table within
the worksheet
DEMO
Open XML Developer Workshop