I found some time to continue my series that started with Excel XML Spreadsheet: Date.Type is mandatory :)
This time, it is about Styles and using the styles to format. I’ll limit myself to formatting Columns, but you can equally apply this to individual Cells, Rows, and Tables.
Note that in the below XML listings, I have replaced the angle brackets with { and }, because the WordPress editor will otherwise delete the XML from the sourcecode portions.
First, let’s look at some ss:Styles:
{Styles}
{Style ss:ID="Default" ss:Name="Normal"}
{Alignment ss:Vertical="Bottom"/}
{/Style}
{Style ss:ID="s21"}
{NumberFormat ss:Format="yyyy/mm/dd"/}
{/Style}
{Style ss:ID="s22"}
{NumberFormat ss:Format="yyyy/mm/dd\ h:mm:ss"/}
{/Style}
{Style ss:ID="s31"}
{NumberFormat ss:Format="[ENG][$-409]ddd"/}
{/Style}
{Style ss:ID="s32"}
{NumberFormat ss:Format="[$-F800]dddd\,\ mmmm\ dd\,\ yyyy"/}
{/Style}
{/Styles}
Then the usage of the styles in Columns:
{Column ss:StyleID="s21" ss:Width="53.25"/}
{Column ss:Index="4" ss:StyleID="s31" ss:Width="89.25"/}
{Column ss:StyleID="s22" ss:Width="95.25"/}
{Column ss:StyleID="s32" ss:Width="95.25"/}
First a few remarks about the ss:Styles:
- Styles have IDs, which don’t need to be in the form s##, you can use any unique ID for them. Excel uses s## because that’s how the formatting pick-list works.
- You specify the formatting as a ss:NumberFormat using the components from the Creating international number formats documentation.
- You can add an Excel specific LCID (locale identifier) to a format. Without it, it will use the user’s locale settings.
- You can ommit the language hint (like [ENG]) from the formatting.
- The Excel LCID is very similar to the LCID Structure using hexadecimal values from the (old now defunct Locale ID Chart and replaced by the new) Microsoft Locale ID Values, Language Identifier Constants and Strings table or list of Locale IDs Assigned by Microsoft, but with a few twists.
- There is a lot of confusion about [$-F800] and [$-F400] which actually behaves as LANG_SYSTEM_DEFAULT (0x0800 in the latter table), where [$-F800] displays the long date and [$-F400] displays the time (as correctly identified in this Openoffice Bugzilla bug report – or the Google cache of it).
- Three digit language [$-409] should be extended to 4-digit LCID 0x0409. It will format the cell using that specific language (in this case: English 3-letter weekday abbreviation).
A few remarks about the ss:Columns:
- These columns define formatting for column A, D, E and F.
- You don’t need to have a definition for every column in your Worksheet.Table, just for the ones that need formatting.
- The Column definition is smart: it can be sparse! After each gap, define a column having an ss:Index attribute, then continue defining subsequent columns until you need another gap.
- You can ommit the ss:Width attribute: when empty, the column will auto-size
–jeroen
PS: Rob van Gelder posted a nice formula to show nice translations using Excel formatting.
The Wiert Corner - irregular stream of stuff 