Using SPServices and Excel to Create Printable Reports

So.. I was happily designing a Sharepoint solution in Visual Studio. The same solution I have been working on for a while now. It really never occurred to me that users would actually want to print from Sharepoint. Why print what you can just go see right? Well, being in a major industrial type environment and being able to support meetings where computer access might be limited, there are times when a good paper report will just have to do. So my challenge was to incorporate printing into my solution. There are a few 3rd party tools that can be used to do some type of reporting. There is SQL Reporting Services and a few others that come to mind. However, some of the requirements for what we need were just easier to do in Excel. So I am about to discuss the wonderful world of Excel, XML, and XSL.

Let me begin by stating that it is very important to understand that my solution is running only in IE7 and is inside an company intranet. What this means is that I have to have the users trust the site and setup IE to allow Unsigned Active X controls because we are going to be automating Excel. However, in this environment it is okay and all the users will share the same experience because they all have Excel 2007. [I would like to sign my controls but we have no way to do that just yet.]

Excel 2007 supports XML using the Excel XML format. The simplest way to see this in action is to open Excel, put some stuff in it, and format it the way you want. You can even change the page layout, margins, header/footer, and setup conditional formatting. Save the file as an XML file. Open this file in your favorite (or not so favorite) XML editor. I have VS 2008 and it works fine for this. You will notice that this XML is actually very easy (scary I know) to read (or am I just a geek!). You can also see all of the formatting you added and all of that is well formed XML. Now comes the trick! You can convert this XML to XSL very easily and this XSL can then be used to format data to Excel readable XML that will open in Excel formatted the way you want. Cool huh?

ON WITH THE GOOD STUFF!!

So the first report that I was asked to create was similar to a list view. However, we have need for a dynamic header and footer option along with some custom color coding. We also needed to span 3 rows for each item. Without getting too detailed, here are some of the steps that were taken:



Step 1: Open Excel and design the look and feel. Repeat the formatting for the 3 rows below row 5. Remember to format the cells according to the data that will be in them.

 Step 2: Save as XML! (File-Save As-Other Formats-->XML Spreadsheet)

Step 3: Convert this XML to XSL using your editor of choice (Or whatever you have!). I named my file PrintList2.xsl and loaded it to a document library called PrintTemplates.

First You have to Remove the XML declaration and change it to XSL stylesheet.

1. <xsl:stylesheet
2.     xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
3.     xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
4.     xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
5.     xmlns:rs="urn:schemas-microsoft-com:rowset"
6.     xmlns:z="#RowsetSchema" version="1.0">
7.   <xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />
8.   <xsl:template match="/">

Then you can add/remove the needed XSL inside the xsl:template tag!
[I have removed all of the styles for brevity]
The DocumentProperties and ExcelWorkbook sections are fairly straightforward and are added automatically based on your selections.

1. <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
2. xmlns:o="urn:schemas-microsoft-com:office:office"
3. xmlns:x="urn:schemas-microsoft-com:office:excel"
4. xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
5. xmlns:html="http://www.w3.org/TR/REC-html40">
6.  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
7.   <Author>Daniel Walker</Author>
8.   <LastAuthor>Dan</LastAuthor>
9.   <LastPrinted>2010-06-18T10:19:06Z</LastPrinted>
10.   <Created>2010-06-18T09:26:14Z</Created>
11.   <LastSaved>2010-12-28T16:32:41Z</LastSaved>
12.   <Company>My Company</Company>
13.   <Version>12.00</Version>
14.  </DocumentProperties>
15.  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
16.   <WindowHeight>11640</WindowHeight>
17.   <WindowWidth>17400</WindowWidth>
18.   <WindowTopX>120</WindowTopX>
19.   <WindowTopY>45</WindowTopY>
20.   <ProtectStructure>False</ProtectStructure>
21.   <ProtectWindows>False</ProtectWindows>
22.  </ExcelWorkbook>

The next section is the WorkSheet section and it contains most of the meat of the xml and xsl code

1. <Worksheet ss:Name="Sheet1">
2.   <Names>
3.    <NamedRange ss:Name="Print_Titles" ss:RefersTo="=Sheet1!R1:R5"/>
4.   </Names>
5.   <Table ss:ExpandedRowCount="60000" x:FullColumns="1" x:FullRows="1">
6.    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="61.5" ss:Span="3"></Column>
7.    <Column ss:Index="5" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="56.25"></Column>
8.    <Column ss:StyleID="s62" ss:Width="49.5" ss:Span="1"></Column>
9.    <Column ss:Index="8" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="66.75"></Column>
10.    <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="57"></Column>
11.    <Column ss:StyleID="s62" ss:Width="40.5"></Column>
12.    <Column ss:AutoFitWidth="0" ss:Width="9"></Column>
13.    <Column ss:AutoFitWidth="0" ss:Width="187.5" ss:Span="242"></Column>
14.    <Row ss:AutoFitHeight="0" ss:Height="8.25">
15.     <Cell ss:MergeAcross="9" ss:MergeDown="1" ss:StyleID="m65201024">
16.       <Data ss:Type="String">Commitments - All Tasks</Data>
17.       <NamedCell ss:Name="Print_Titles"/>
18.     </Cell>
19.    </Row>
20.    <Row ss:AutoFitHeight="0" ss:Height="8.25"></Row>
21.    <Row ss:AutoFitHeight="0" ss:Height="13.5">
22.     <Cell ss:MergeAcross="1" ss:StyleID="s266">
23.       <Data ss:Type="String">Priority</Data>
24.       <NamedCell ss:Name="Print_Titles"></NamedCell>
25.     </Cell>
26.     <Cell ss:StyleID="s268">
27.       <Data ss:Type="String">Division</Data>
28.       <NamedCell ss:Name="Print_Titles"></NamedCell>
29.     </Cell>
30.     <Cell ss:StyleID="s268">
31.       <Data ss:Type="String">Branch</Data>
32.       <NamedCell ss:Name="Print_Titles"></NamedCell>
33.     </Cell>
34.     <Cell ss:MergeAcross="2" ss:StyleID="s268">
35.       <Data ss:Type="String">Status</Data>
36.       <NamedCell ss:Name="Print_Titles"></NamedCell>
37.     </Cell>
38.     <Cell ss:StyleID="s268">
39.       <Data ss:Type="String">Serial #</Data>
40.       <NamedCell ss:Name="Print_Titles"></NamedCell>
41.     </Cell>
42.     <Cell ss:StyleID="s268">
43.       <Data ss:Type="String">ECD</Data>
44.       <NamedCell ss:Name="Print_Titles"></NamedCell>
45.     </Cell>
46.     <Cell ss:MergeDown="2" ss:StyleID="m54658428">
47.       <Data ss:Type="String">Wkg Days</Data>
48.       <NamedCell ss:Name="Print_Titles"></NamedCell>
49.     </Cell>
50.    </Row>
51.    <Row>
52.     <Cell ss:MergeAcross="6" ss:StyleID="m57148000">
53.       <Data ss:Type="String">Title</Data>
54.       <NamedCell ss:Name="Print_Titles"></NamedCell>
55.     </Cell>
56.     <Cell ss:StyleID="s195">
57.       <Data ss:Type="String">Fnd #</Data>
58.       <NamedCell ss:Name="Print_Titles"></NamedCell>
59.     </Cell>
60.     <Cell ss:StyleID="s195">
61.       <Data ss:Type="String">Rev ECD</Data>
62.       <NamedCell ss:Name="Print_Titles"></NamedCell>
63.     </Cell>
64.    </Row>
65.    <Row ss:Height="13.5">
66.     <Cell ss:MergeAcross="6" ss:StyleID="m57148020">
67.       <Data ss:Type="String">Description</Data>
68.       <NamedCell ss:Name="Print_Titles"></NamedCell>
69.     </Cell>
70.     <Cell ss:StyleID="s234">
71.       <Data ss:Type="String">Cmt #</Data>
72.       <NamedCell ss:Name="Print_Titles"></NamedCell>
73.     </Cell>
74.     <Cell ss:StyleID="s235">
75.       <Data ss:Type="String">Created</Data>
76.       <NamedCell ss:Name="Print_Titles"></NamedCell>
77.     </Cell>
78.    </Row>

This is the main header formatted the way we need. Most of the formatting options are declared later. This will produce just the first part of the spreadsheet as shown in the following image:

Now we need to setup the data portion of the template. When the page is called, it is going to be used to format XML data. We will need to loop through this data with the xsl:for-each command. As this report carries one list item over 3 rows we have to set that up correctly. Also, note that due to the way the data is returned from a webservice query, it might need to be tweaked to look correct. An example would be the need for the substring commands for the ows_Body. This data has opening and closing div tags because it is a note field.

1. <xsl:for-each select="listitems/rs:data/z:row">
2.    <Row>
3.     <Cell ss:MergeAcross="1" ss:StyleID="s271">
4.       <Data ss:Type="String">
5.         <xsl:value-of select="substring-after(@ows_Priority, '#')"/>
6.       </Data>
7.     </Cell>
8.     <Cell ss:StyleID="s272">
9.       <Data ss:Type="String">
10.         <xsl:value-of select="@ows_AssignedDivision"/>
11.       </Data>
12.     </Cell>
13.     <Cell ss:StyleID="s272">
14.       <Data ss:Type="String">
15.         <xsl:value-of select="@ows_AssignedBranch"/>
16.       </Data>
17.     </Cell>
18.     <Cell ss:MergeAcross="2" ss:StyleID="s272">
19.       <Data ss:Type="String">
20.         <xsl:value-of select="@ows_Status"/>
21.       </Data>
22.     </Cell>
23.     <Cell ss:StyleID="s272">
24.       <Data ss:Type="String">
25.         <xsl:value-of select="@ows_Serial"/>
26.       </Data>
27.     </Cell>
28.     <Cell ss:StyleID="s139">
29.       <Data ss:Type="DateTime">
30.         <xsl:value-of select="substring-before(@ows_DueDate, ' ')"/>
31.       </Data>
32.     </Cell>
33.     <Cell ss:MergeDown="2" ss:StyleID="m65201084">
34.       <Data ss:Type="Number">
35.         <xsl:value-of select="@ows_WorkingDays"/>
36.       </Data>
37.     </Cell>
38.    </Row>
39.    <Row>
40.     <Cell ss:MergeAcross="6" ss:StyleID="s276">
41.       <Data ss:Type="String">
42.         <xsl:value-of select="@ows_Title"/>
43.       </Data>
44.     </Cell>
45.     <Cell ss:StyleID="s274">
46.       <Data ss:Type="String">
47.         <xsl:value-of select="@ows_FindingNum"/>
48.       </Data>
49.     </Cell>
50.     <Cell ss:StyleID="s140">
51.       <xsl:choose>
52.         <xsl:when test="@ows_RECD !=''">
53.           <Data ss:Type="DateTime">
54.             <xsl:value-of select="substring-before(@ows_RECD, ' ')"/>
55.           </Data>
56.         </xsl:when>
57.       </xsl:choose>
58.     </Cell>
59.    </Row>
60.    <Row ss:Height="13.5">
61.     <Cell ss:MergeAcross="6" ss:StyleID="s277">
62.       <Data ss:Type="String">
63.         <xsl:value-of select="substring-before(substring-after(@ows_Body, '&lt;div&gt;'), '&lt;/div&gt;')"/>
64.       </Data>
65.     </Cell>
66.     <Cell ss:StyleID="s275">
67.       <Data ss:Type="String">
68.         <xsl:value-of select="@ows_CommitmentNum"/>
69.       </Data>
70.     </Cell>
71.     <Cell ss:StyleID="s141">
72.       <Data ss:Type="DateTime">
73.         <xsl:value-of select="substring-before(@ows_Created, ' ')"/>
74.       </Data>
75.     </Cell>
76.    </Row>
77.   </xsl:for-each>
78.   </Table>

Now we have looped through the data and output the results. The rest of the code handles the formatting including conditional header and the conditional formatting. The conditional formatting has code to calculate the range of cells it should affect as well!

Note again that this XSL file was uploaded to a document library called PrintTemplates

1. <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
2.    <PageSetup>
3.     <Layout x:Orientation="Landscape" x:CenterHorizontal="1"/>
4.      <xsl:variable name="MarkRequired" select="count(listitems/rs:data/z:row[@ows_HandleAs = 'SPECIAL 1'])" />
5.       <xsl:choose>
6.        <xsl:when test="$MarkRequired >= 1">
7.         <Header x:Margin="0.3" x:Data="&amp;C&amp;&quot;Arial,Bold&quot;&amp;8SPECIAL: This document is special!"/>
8.        </xsl:when>
9.      </xsl:choose>
10.     <Footer x:Margin="0.3" x:Data="&amp;L&amp;&quot;Arial,Bold&quot;CTS Commitments View&amp;C&amp;&quot;Arial,Bold&quot;&amp;D&amp;R&amp;&quot;Arial,Bold&quot;&amp;P of &amp;N"/>
11.     <PageMargins x:Bottom="0.75" x:Left="0.25" x:Right="0.25" x:Top="0.75"/>
12.    </PageSetup>
13.    <Print>
14.     <ValidPrinterInfo/>
15.     <Scale>88</Scale>
16.     <HorizontalResolution>600</HorizontalResolution>
17.     <VerticalResolution>600</VerticalResolution>
18.    </Print>
19.    <PageBreakZoom>60</PageBreakZoom>
20.    <Selected/>
21.    <FreezePanes/>
22.    <FrozenNoSplit/>
23.    <SplitHorizontal>5</SplitHorizontal>
24.    <TopRowBottomPane>5</TopRowBottomPane>
25.    <ActivePane>2</ActivePane>
26.    <Panes>
27.     <Pane>
28.      <Number>3</Number>
29.     </Pane>
30.     <Pane>
31.      <Number>2</Number>
32.      <ActiveRow>11</ActiveRow>
33.      <ActiveCol>1</ActiveCol>
34.     </Pane>
35.    </Panes>
36.    <ProtectObjects>False</ProtectObjects>
37.    <ProtectScenarios>False</ProtectScenarios>
38.   </WorksheetOptions>
39.   <ConditionalFormatting xmlns="urn:schemas-microsoft-com:office:excel">
40.    <xsl:variable name="ItemCount" select="count(listitems/rs:data/z:row)" />
41.    <xsl:variable name="Range1" select="$ItemCount * 3"/>
42.    <xsl:variable name="Range2" select="$Range1 + 5" />
43.    <Range>R6C10:R<xsl:value-of select="$Range2"/>C10</Range>
44.    <Condition>
45.     <Qualifier>GreaterOrEqual</Qualifier>
46.     <Value1>6</Value1>
47.     <Format Style='color:black;font-weight:700;background:red'/>
48.    </Condition>
49.    <Condition>
50.     <Qualifier>Between</Qualifier>
51.     <Value1>0</Value1>
52.     <Value2>5</Value2>
53.     <Format Style='color:black;font-weight:700;background:#FC6E04'/>
54.    </Condition>
55.    <Condition>
56.     <Qualifier>LessOrEqual</Qualifier>
57.     <Value1>0</Value1>
58.     <Format Style='color:black;font-weight:700;background:#00CC00'/>
59.    </Condition>
60.   </ConditionalFormatting>
61.  </Worksheet>
62. </Workbook>
63.   </xsl:template>
64. </xsl:stylesheet>

BRING ON THE DATA!!

So the next thing we need to do is to get the data and automate Excel. This is all done in javascript and jQuery and I had placed this code in the master page. I also chose to make it an action from a list view, but you can choose whatever method you want.

Excel Automation Using Javascript

1. <script language="javascript" type="text/javascript">
2.     var viewpage = null;
3.     var test = null;
4.                                                        
5.     $(document).ready(function() {
6.         viewname = $("td[id*='onetViewSelector']").text();
7.         test = new String(window.location);
8.         createPrintMenuItems()
9.     });
11.     function createPrintMenuItems() {
12.         // Attach to the Actions menu
13.         if (test.indexOf("Lists/Commitments") > 0) {
14.             var oNodes = document.getElementsByTagName("a");
15.             for (var c = 0; c < oNodes.length; c++) {
16.                 if (oNodes[c].id.indexOf("_ListActionsMenu") > -1) {
17.                     var oTokens = MMU_ParseNVAttr(oNodes[c], "menuTokenValues");
18.                     var sMenuID = MHash_Item(oTokens, "TEMPLATECLIENTID");
19.                     var oMenu = document.getElementById(sMenuID);
20.                     oMenu.innerHTML = "<ie:menuitem description='Prints the current view' id='printListMenuItem' type='option' onMenuClick='javascript:printList()' iconSrc='/_layouts/images/WPICON.GIF' text='Print View' menuGroupId='200'></ie:menuitem>" + oMenu.innerHTML;
21.                     return;
22.                 }
23.             }
24.             setTimeout("createPrintMenuItems()", 500);
25.         }
26.     }

So now let's call the printlist function that does most of the real work here. I tip my hat to Marc Anderson's SPServices library for making this a lot easier. It can be found here

1. function printList() {
2.     var camlQuery;    
3.     var xmldata1 = new ActiveXObject("MSXML.DomDocument");
4.     xmldata1.async = false;
5.     var xmldata2 = new ActiveXObject("MSXML.DomDocument");
6.     xmldata2.async = false;
7.     var xsldata1 = new ActiveXObject("MSXML.DomDocument");
8.     xsldata1.async = false;
9.     xsldata1.load(L_Menu_BaseUrl + "/PrintTemplates/PrintList2.xsl")
10.     var zpath = "c:\\tmpdata1.xml";
11.     if (viewname != "All") {
12.         camlQuery = "<Query><Where><Eq><FieldRef Name='AssignedDepartment'/><Value Type='Text'>" + viewname + "</Value></Eq></Where><OrderBy><FieldRef Name='ID' /></OrderBy></Query>";
13.     }
14.     else {
15.         camlQuery = "<Query><OrderBy><FieldRef Name='ID' /></OrderBy></Query>";
16.     }
17.     var camlViewFields = "<ViewFields><FieldRef Name='Priority' />";
18.     camlViewFields += "<FieldRef Name='Priority' />";
19.     camlViewFields += "<FieldRef Name='AssignedDivision' />";
20.     camlViewFields += "<FieldRef Name='AssignedBranch' />";
21.     camlViewFields += "<FieldRef Name='Title' />";
22.     camlViewFields += "<FieldRef Name='Serial' />";
23.     camlViewFields += "<FieldRef Name='FindingNum' />";
24.     camlViewFields += "<FieldRef Name='CommitmentNum' />";
25.     camlViewFields += "<FieldRef Name='DueDate' />";
26.     camlViewFields += "<FieldRef Name='RECD' />";
27.     camlViewFields += "<FieldRef Name='Status' />";
28.     camlViewFields += "<FieldRef Name='WorkingDays' />";
29.     camlViewFields += "</ViewFields>";
30.     $().SPServices({
31.         operation: "GetListItems",
32.         async: false,
33.         webURL: L_Menu_BaseUrl,
34.         listName: "Commitments",
35.         CAMLQuery: camlQuery,
36.         CAMLViewFields: camlViewFields,
37.         completefunc: function(xData, Status) {
38.             alert(xData.responseXML.xml);
39.             var rtz = elementContents(xData.responseXML.xml, "GetListItemsResult");
40.             try {
41.                 xmldata1.loadXML(rtz);
42.             }
43.             catch (e) { }
44.             xmldata1.transformNodeToObject(xsldata1, xmldata2);
45.             var fs = new ActiveXObject("Scripting.FileSystemobject");
46.             var f = fs.OpenTextFile(zpath, 2, true, 0);
47.             var output = "<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>" + xmldata2.xml;
48.             f.write(output);
49.             f.close();
51.             var oxl = new ActiveXObject("Excel.Application");
52.             oxl.Visible = true; // set to false
53.             var owb = oxl.Workbooks.Open(zpath);
54.             oxl.ActiveSheet.Cells.Select;
55.             oxl.Selection.EntireColumn.Autofit;
56.             //oxl.ActiveSheet.PrintOut;
57.             //owb.Close(false);
58.             //oxl.Quit;
59.                                                                    
60.         } // end completefunc
61.     });     // end spservices
62.                                                                
63. }
65. function elementContents(text, tagName) {
66.     var sTag = "<" + tagName + ">";
67.     var eTag = "</" + tagName + ">";
68.     var sTagIndex = text.indexOf(sTag);
69.     var eTagIndex = text.indexOf(eTag, sTagIndex + sTag.length);
70.     if ((sTagIndex >= 0) && (eTagIndex > sTagIndex)) return text.substring(sTagIndex + sTag.length, eTagIndex);
71.     return null;
72. }                                              
73. </script>

So, this code does the rest! In this case, I allowed for different CAML query based on the viewname variable which is set based on the current view for this list. In this case, it is based on the department the commitments are assigned to. I commented out the last 3 lines of the function that force the print out just to be sure it looks like what it should. This technique allowed me to create a header based on certain content and I can repeat the header fields on subsequent pages automatically too. This same approach could be used for single item printouts in a special layout as well. To see the results, on the list view I would go to Actions-> Print View (Or whatever you call it in the javascript code)

Resulting in the following Excel output.

There it is folks! Just print the spreadsheet and it will retain all the formatting you did on the original. As I said, it is very versatile for what we are doing and we can do a lot of stuff with it. Feel free to ask whatever questions you may have!!