Difference between revisions of "Excel Channel Report"

From AwasuWiki
Jump to: navigation, search
(The output format: - added Page Setup and AutoFilter text)
Line 1: Line 1:
Technically this Channel Report outputs [http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx 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" althought it could potentially be used in other spreadsheet applications as well.
+
Technically, this Channel Report outputs [http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx 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 [http://www.awasu.com/help/2.4/Advanced%20features/02-MetadataModules.html Metadata] that is attached to feeds. If a feed attaches numeric metadata like [http://earthquake.usgs.gov/earthquakes/shakemap/ 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.
 
While Excel may not seem like the best application for reading feeds (''it isn't''), remember that Awasu can extract, store and republish [http://www.awasu.com/help/2.4/Advanced%20features/02-MetadataModules.html Metadata] that is attached to feeds. If a feed attaches numeric metadata like [http://earthquake.usgs.gov/earthquakes/shakemap/ 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.
  
==Support Excel versions==
+
==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.
 
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.
  
Line 12: Line 12:
 
*The Workpad Item Notes (if applicable)
 
*The Workpad Item Notes (if applicable)
 
*AutoFilter is enabled for all 3 columns
 
*AutoFilter is enabled for all 3 columns
*The column headins are locked so that they won't scroll out of sight (''known as "Freeze Panes"'')
+
*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  
 
*"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  
  
Line 164: Line 164:
  
 
==Post processing command==
 
==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 the generic "open" 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:
  
 
<pre>
 
<pre>
 
cmd /c start {%REPORT-FILENAME%}
 
cmd /c start {%REPORT-FILENAME%}
 
</pre>
 
</pre>

Revision as of 01:23, 25 November 2010

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%}