Outputting Excel Spreadsheet XML

Posted on 02 Apr 2010 11:27am by Aleks Bochniak

Background: Information about Office Open XML File Format.

Example: Excel spreadsheet

Above is an image of a spreadsheet we would like to output from MySource Matrix. To achieve this result we need the following:

  • A Design parse for Excel XML
  • An Asset listing to output the XML

Dissecting the Excel XML spreadsheet

The source code of an Excel XML spreadsheet generally follows the following layout.

Workbook

  DocumentProperties

  ExcelWorkbook

  Styles

    Style

  Worksheet

    Tables

      Row

        Cell

          Data

    WorksheetOptions

      Print

        ValidPrinterInfo

        HorizontalResolution

        VerticalResolution

    Selected

    Panes

      Pane

        Number

        ActiveRow

    ProtectObjects

    ProtectScenarios

I am not going to get into a lot of detail in this example, as there is a ton of information about Excel XML format out there and it is fairly straight forward. I am sure if you are familiar with MySource Matrix you will get the gist of it quickly.

Let's get started

Let's begin with a design file. We need MySource Matrix to send the correct mime content type in the response headers and we can do this by modifying a global variable in our design. See below for an example. Download parse file as text.


<MySource_PRINT id_name="__global__" var="content_type" 
content_type="application/vnd.ms-excel" />
<MySource_area id_name="body" design_area="body" />

Next we need to create our Asset Listing. Please make sure you use the file extension .xls on the asset name so that web browsers recognise it as a file download.

There are two parts of the Asset Listing we need to put some code in.

  • Page Contents
  • Default Type Format

Page Contents

Below is an example, which has header row, font and presentation attributes defined. Obviously they are not mandatory. Download as a text file.


<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Aleks Bochniak</Author>
  <LastAuthor>Aleks Bochniak</LastAuthor>
  <Company>Web is Beautiful</Company>
  <Version>11.6568</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>7000</WindowHeight>
  <WindowWidth>10000</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>60</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s25">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font x:Family="Swiss" ss:Color="#333333" ss:Bold="1"/>
   <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s26">
   <Font x:Family="Swiss" ss:Size="16" ss:Bold="1"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="12" x:FullColumns="1"
   x:FullRows="1">
   <Row ss:Height="20.25">
    <Cell ss:StyleID="s26"><Data ss:Type="String">All User details</Data></Cell>
   </Row>
   <Row ss:Index="3">
    <Cell ss:StyleID="s25"><Data ss:Type="String">User type</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Industry</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Date joined</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Username</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">First name</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Last name</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Email</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Position</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Work phone</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Fax</Data></Cell>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Mobile</Data></Cell>
   </Row>

%asset_listing%

  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>1</Number>
     <ActiveRow>2</ActiveRow>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Type Formats - Default

For this example I have pre-created some Data Record assets with metadata. But, you can output whatever fields you like in your Asset Listing. Download as a text file


   <Row>
    <Cell><Data ss:Type="String">%asset_metadata_User_type%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Industry%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Date_joined%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Username%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_First_name%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Last_name%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Email%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Position%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Work_phone%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Fax%</Data></Cell>
    <Cell><Data ss:Type="String">%asset_metadata_Mobile%</Data></Cell>
   </Row>

The final result

Using the code supplied in this example I have created a working Asset Listing, which you can preview yourself.

If you need help getting something like this working, please ask below in the commenting area.

Comments

There are 4 comment(s) so far... say something?


09 Feb 2012 6:15am

Doh I just clicked the preview link above and firefox


09 Feb 2012 6:17am

Firefox crashed without warning...

Mebbe Firefox can't handle the Awesome Power!


25 Sep 2012 10:17pm

Was totally stuck until I read this, now back up and rnuning.


25 Oct 2015 6:39am

Stands back from the keyboard in amnemezat! Thanks!

Leave a comment

Name (required)

Email (required)

Website

Your comment

Security key
Enter the above security word

I am human