Excel Channel Report

From AwasuWiki
Revision as of 01:23, 25 November 2010 by Kevotheclone (talk | contribs)
Jump to: navigation, search

Technically, this Channel Report outputs SpeadsheetML with an XML processing instruction that instructs a Windows PC to attempt to open the SpeadsheetML file in Excel. So for the most part this is an "Excel Channel Report" although it could potentially be used in other spreadsheet applications as well.

While Excel may not seem like the best application for reading feeds (it isn't), remember that Awasu can extract, store and republish Metadata that is attached to feeds. If a feed attaches numeric metadata like ShakeMaps then a simple variation of this Channel Report would be a great way to export ShakeMap metadata (ShakeMap Metadata module) to a spreadsheet program to perform calculations (distributions, deviations, frequency, mean, median, etc.) on that data.

Supported Excel versions

SpreadsheetML is supported by Excel XP, 2002, 2003, 2007, and 2010. I was only able to test this SpreadsheetML Channel Report in Excel 2003 and 2007 and it looked and functioned identically in both versions.

The output format

In this example I chose to output:

  • The Channel Name with a hyperlink to the web sites home page
  • The feed item's title with a hyperlink to the web page referenced by the feed item
  • The Workpad Item Notes (if applicable)
  • AutoFilter is enabled for all 3 columns
  • The column headings are locked so that they won't scroll out of sight (known as "Freeze Panes")
  • "Page Setup" is configured for landscape printing, centered horizontally on the page, set to fit horizontally on a page but vertically spread over many pages (if needed), column headings will repeat on every page
<?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:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 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">
    <Title>{%REPORT-NAME%}</Title>
    <Subject>{%REPORT-NAME%}</Subject>
    <Author>Awasu Channel Report Generator</Author>
    <Keywords>Awasu Channel Report</Keywords>
    <Description>{%REPORT-DESCRIPTION%}</Description>
    <LastAuthor>Awasu Channel Report Generator</LastAuthor>
    <Created>{%REPORT-TIME% "%y-%m-%dT%H:%M:%SZ%Z"}</Created>
    <LastSaved>{%REPORT-TIME% "%y-%m-%dT%H:%M:%SZ%Z"}</LastSaved>
    <Category>Awasu Channel Report</Category>
    <Version>11.9999</Version>
  </DocumentProperties>
  <CustomDocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    <Awasu_x0020_Channel_x0020_Report_x0020_Name dt:dt="string">{%REPORT-NAME%}</Awasu_x0020_Channel_x0020_Report_x0020_Name>
    <Awasu_x0020_Channel_x0020_Report_x0020_Description dt:dt="string">{%REPORT-DESCRIPTION%}</Awasu_x0020_Channel_x0020_Report_x0020_Description>
    <Awasu_x0020_Channel_x0020_Report_x0020_Date_x002f_Time dt:dt="string">{%REPORT-TIME%}</Awasu_x0020_Channel_x0020_Report_x0020_Date_x002f_Time>
  </CustomDocumentProperties>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>12270</WindowHeight>
    <WindowWidth>14955</WindowWidth>
    <WindowTopX>720</WindowTopX>
    <WindowTopY>375</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="s21" ss:Name="Hyperlink">
     <Font ss:Color="#0000FF" ss:Underline="Single"/>
    </Style>
    <Style ss:ID="s22">
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
      <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
      <Font x:Family="Swiss" ss:Bold="1"/>
      <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
      <NumberFormat ss:Format="@"/>
    </Style>
    <Style ss:ID="s24" ss:Parent="s21">
      <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
      <NumberFormat ss:Format="@"/>
    </Style>
    <Style ss:ID="s25">
      <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
      <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
      </Borders>
    </Style>
  </Styles>
  <Worksheet ss:Name="{%REPORT-NAME%}">
    <Names>
      <NamedRange ss:Name="_FilterDatabase" ss:RefersTo="='{%REPORT-NAME%}'!R1C1:R19C2" ss:Hidden="1"/>
    </Names>
    <Table x:FullColumns="1" x:FullRows="1">
      <Column ss:AutoFitWidth="0" ss:Width="213.75"/>
      <Column ss:AutoFitWidth="0" ss:Width="423.75"/>
      <Column ss:AutoFitWidth="0" ss:Width="213.75"/>
      <Row ss:Height="13.5">
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Channel Name</Data>
          <NamedCell ss:Name="_FilterDatabase"/>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Item Title</Data>
          <NamedCell ss:Name="_FilterDatabase"/>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Item Notes</Data>
        </Cell>
      </Row>
{%REPEAT% FeedItems}
      <Row>
        <Cell ss:StyleID="s24" ss:HRef="{%CHANNEL-METADATA% homeUrl encode=sgml chars=<&\"}">
          <Data ss:Type="String">{%CHANNEL-METADATA% name}</Data>
          <NamedCell ss:Name="_FilterDatabase"/>
        </Cell>
        <Cell ss:StyleID="s24" ss:HRef="{%ITEM-METADATA% url encode=sgml chars=<&\"}">
          <Data ss:Type="String">{%ITEM-METADATA% name!}</Data>
          <NamedCell ss:Name="_FilterDatabase"/>
        </Cell>
        <Cell ss:StyleID="s25">
          <Data ss:Type="String">{%ITEM-NOTES%}</Data>
        </Cell>
      </Row>
{%/REPEAT%}
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <PageSetup>
        <Layout x:Orientation="Landscape" x:CenterHorizontal="1"/>
        <Header x:Data="&L&D&C&A&R&T"/>
        <Footer x:Data="&L&Z&F&CPage &P of &N&R&A"/>
      </PageSetup>
      <FitToPage/>
      <Print>
        <FitHeight>0</FitHeight>
        <ValidPrinterInfo/>
        <Scale>76</Scale>
        <HorizontalResolution>600</HorizontalResolution>
        <VerticalResolution>600</VerticalResolution>
      </Print>
      <Selected/>
      <FreezePanes/>
      <FrozenNoSplit/>
      <SplitHorizontal>1</SplitHorizontal>
      <TopRowBottomPane>1</TopRowBottomPane>
      <ActivePane>2</ActivePane>
      <Panes>
        <Pane>
          <Number>3</Number>
        </Pane>
        <Pane>
          <Number>2</Number>
          <ActiveRow>20</ActiveRow>
          <ActiveCol>1</ActiveCol>
        </Pane>
      </Panes>
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    <AutoFilter x:Range="R1C1:R1C3" xmlns="urn:schemas-microsoft-com:office:excel">
  </AutoFilter>
  </Worksheet>
</Workbook>

Post processing command

Due to the embedded XML processing instruction (that's the second line of the Channel Report) this SpreadsheetML Channel Report should open in Excel simply by using the generic "open" post processing command:

cmd /c start {%REPORT-FILENAME%}