Difference between revisions of "Excel Channel Report"

From AwasuWiki
Jump to: navigation, search
(First draft)
 
(Added link to ZIP file)
 
(2 intermediate revisions by the same user not shown)
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 11: Line 11:
 
*The feed item's title with a hyperlink to the web page referenced by the feed item
 
*The feed item's title with a hyperlink to the web page referenced by the feed item
 
*The Workpad Item Notes (if applicable)
 
*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
 +
 +
==The Excel Channel Report Template==
 +
 +
Download and unpack this [http://awasu.com/downloads/extensions/ExcelChannelReport/ExcelChannelReport-v1.zip ZIP file] to the Resources/Report Templates directory (under the Awasu installation directory).
  
 
<pre>
 
<pre>
Line 161: Line 168:
  
 
==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>

Latest revision as of 07:57, 11 March 2011

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

The Excel Channel Report Template

Download and unpack this ZIP file to the Resources/Report Templates directory (under the Awasu installation directory).

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