Using SPServices and Excel to Create Printable Reports [Update 1]

After doing much testing and even more testing in my work environment, I came to the conclusion that there were a few things about the javascript code that I did not like. First it relies on the FileSystemObject and in today's security minded world that was going to be a big no-no where I work! So, I turned again to my trusty sidekick SPServices to come up with a better way to accomplish this. Basically I asked myself the following question: "Can I save the spreadsheet.xml as a file in a document library using only client-side code?" This is the question that I had to find an answer to and I had several fingers crossed when I fired up google. Though it was harder to type with my fingers crossed (just kidding), I had hoped to find something that looked promising. At first, this was very difficult and there was not much in the way of promising results. However, I did stumble across a reference to the Copy webservice which I had forgotten about. So maybe there would be a way to use this webservice to allow me to "copy" an xml file into a document library. It just so happens that indeed it did!

Enter the beauty of base64 encoding in javascript which I found here

I will add this to my javascript code so you can see the changes. I want to first also point out that I still used an xsl transform. Here is the first part of the updated code:

1. $(document).ready(function() {
2.     addprintmenu();
3. });
5. function addprintmenu() {
6.     // Attach to the Actions menu
7.         var oNodes = document.getElementsByTagName("a");
8.         for (var c = 0; c < oNodes.length; c++) {
9.             if (oNodes[c].id.indexOf("_ListActionsMenu") > -1) {
10.                 var oTokens = MMU_ParseNVAttr(oNodes[c], "menuTokenValues");
11.                 var sMenuID = MHash_Item(oTokens, "TEMPLATECLIENTID");
12.                 var oMenu = document.getElementById(sMenuID);
13.                 oMenu.innerHTML = "<ie:menuitem description='Prints the current view' id='nnsyrintListMenuItem' type='option' onMenuClick='javascript:printlist()' iconSrc='/_layouts/images/WPICON.GIF' text='Print View' menuGroupId='200'></ie:menuitem>" + oMenu.innerHTML;
14.                 return;
15.             }
16.         }
17.         setTimeout("addprintmenu()", 500);
18. }
20. function stripcontents(text, tagName) {
21.     var sTag = "<" + tagName + ">";
22.     var eTag = "</" + tagName + ">";
23.     var sTagIndex = text.indexOf(sTag);
24.     var eTagIndex = text.indexOf(eTag, sTagIndex + sTag.length);
25.     if ((sTagIndex >= 0) && (eTagIndex > sTagIndex))
26.         return text.substring(sTagIndex + sTag.length, eTagIndex);
27.     return null;
28. }
30. function printlist() {
31.     var viewname = $("td[id*='onetViewSelector']").text();
32.     var usr = $().SPServices.SPGetCurrentUser();
33.     // NOTE: May need to condition usr result if it returns domain\ data to remove domain\
34.     // var loc = usr.lastIndexOf("\\") + 1;
35.     // usr = usr.substring(loc, 50);
36.     var dest = "http://wss_sa/tools/cts/CTSReports/" + usr + ".xml"; // build destination url for user     
37.     var viewguid, camlQuery;
38.     var xmldata1 = new ActiveXObject("MSXML.DomDocument");
39.     xmldata1.async = false;
40.     var xmldata2 = new ActiveXObject("MSXML.DomDocument");
41.     xmldata2.async = false;
42.     var xmldata3 = new ActiveXObject("MSXML.DomDocument");
43.     xmldata3.async = false;
44.     var xsldata1 = new ActiveXObject("MSXML.DomDocument");
45.     xsldata1.async = false;
46.     xsldata1.load(L_Menu_BaseUrl + "/PrintTemplates/CTSPrintList.xsl")
47.     // Get the view guid by polling the views by the display name. This will get the actual query text so personal views will still work
48.     $().SPServices({
49.         operation: "GetViewCollection",
50.         async: false,
51.         webURL: L_Menu_BaseUrl,
52.         listName: "Commitments",
53.         async: false,
54.         completefunc: function(xData, Status) {
55.             $(xData.responseXML).find("[nodeName=View]").each(function() {
56.                 if ($(this).attr("DisplayName") == viewname) {
57.                     viewguid = $(this).attr("Name");
58.                 }
59.             });
60.         }
61.     });
62.     // Have the viewguid so get the actual query
63.     $().SPServices({
64.         operation: "GetViewHtml",
65.         webURL: L_Menu_BaseUrl,
66.         listName: "Commitments",
67.         viewName: viewguid,
68.         async: false,
69.         debug: true,
70.         completefunc: function(xData, Status) {
71.             camlQuery = stripcontents(xData.responseXML.xml, "Where");                
72.         }
73.     });
74.     camlQuery = "<Query><Where>" + camlQuery + "</Where></Query>";
76.     var camlViewFields = "<ViewFields><FieldRef Name='Priority' />";
77.     camlViewFields += "<FieldRef Name='AssignedDepartment' />";
78.     camlViewFields += "<FieldRef Name='AssignedDivision' />";
79.     camlViewFields += "<FieldRef Name='AssignedBranch' />";
80.     camlViewFields += "<FieldRef Name='Title' />";
81.     camlViewFields += "<FieldRef Name='Serial' />";
82.     camlViewFields += "<FieldRef Name='FindingNum' />";
83.     camlViewFields += "<FieldRef Name='CommitmentNum' />";
84.     camlViewFields += "<FieldRef Name='DueDate' />";
85.     camlViewFields += "<FieldRef Name='RECD' />";
86.     camlViewFields += "<FieldRef Name='Status' />";
87.     camlViewFields += "<FieldRef Name='WorkingDays' />";
88.     camlViewFields += "</ViewFields>";
89.     $().SPServices({
90.         operation: "GetListItems",
91.         async: false,
92.         webURL: L_Menu_BaseUrl,
93.         listName: "Commitments",
94.         CAMLQuery: camlQuery,
95.         CAMLViewFields: camlViewFields,
96.         completefunc: function(xData, Status) {
97.             //alert(xData.responseXML.xml);
98.             var rtz = stripcontents(xData.responseXML.xml, "GetListItemsResult");
99.             try {
100.                 xmldata1.loadXML(rtz);
101.             }
102.             catch (e) { }
103.             xmldata1.transformNodeToObject(xsldata1, xmldata2);
104.             var output = "<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>" + xmldata2.xml;
105.             xmldata3.loadXML(output);      
106.             var flds = "";
107.         var results = "";
108.         var desturls = [dest];
109.         var encoded = Base64.encode(xmldata3.xml); // This is the key!
110.         $().SPServices({
111.             operation: "CopyIntoItems",
112.             sourceurl: "http://wss_sa/tools/cts/PrintTemplates/CTSPrintList.xsl",
113.             DestinationUrls: desturls,
114.             Fields: flds,
115.             Stream: encoded,
116.             completefunc: function(xData, Status) { alert(xData.responseText);}
117.         });    
118.         } // end completefunc
119.         });  // end spservices
120.     var oxl = new ActiveXObject("Excel.Application");
121.         oxl.Visible = true;
122.       var owb = oxl.Workbooks.Open(reqXML.responseText);
123.       oxl.ActiveSheet.Columns("A:A").ColumnWidth = 9;
124.       oxl.ActiveSheet.Columns("C:C").ColumnWidth = 50;
125.       oxl.ActiveSheet.Columns("D:D").ColumnWidth = 15;
126.       oxl.ActiveSheet.Columns.Select;
127.       oxl.Selection.Rows.Autofit;
128.       oxl.Selection.EntireColumn.Autofit;
129.       oxl.UserControl = true;      
130. }

So, what does this code do?

The first few functions sets up the Print View action for the list views.

The stripcontents function is used to parse tags and remove content around the requested tag.

The first line of the printlist function gets the text of the view selector for the list view I am on. This is a major piece so that I know what view I am using. The next few lines setup the ActiveX objects needed for this to work including loading the xsl transform file from a document library CTSPrintList.xsl. This is similar to the xsl stylesheet from the first article.

The next thing I do is use SPServices and the GetViewCollection call to get the actual view guid that matches the current view I am on. Then I call the GetViewHtml webservice function to return the actual view's HTML. I then use the small utility function stripcontents to strip the actual Where clause of the CAML query for the view. I then add back the Query and Where pieces of the CAML query and am ready to then use the GetListItems webservice call to get the data from the view.

Once I get the data, I use the stripcontents function again to strip out the GetListItemsResult data which I then translate using the xsl stylesheet.

I then add the proper xml tags to denote this is spreadsheet xml. I then use the base64 functions listed at the bottom of this post to encode the xml to base64. I then use the CopyIntoItems webservice call to copy this xml file to the CTSReports document library using the users name for the file.

The last step is to then launch Excel and then open this file and you will have the report the way you want it!

I think that this is a better approach than the one that I had before. All questions or thoughts are welcome!

The base 64 code from the site mentioned above:

1. var Base64 = {
2.     // private property
3.     _keyStr: "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",
4.     // public method for encoding
5.     encode: function(input) {
6.         var output = "";
7.         var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
8.         var i = 0;
9.         input = Base64._utf8_encode(input);
10.         while (i < input.length) {
11.             chr1 = input.charCodeAt(i++);
12.             chr2 = input.charCodeAt(i++);
13.             chr3 = input.charCodeAt(i++);
14.             enc1 = chr1 >> 2;
15.             enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
16.             enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
17.             enc4 = chr3 & 63;
18.             if (isNaN(chr2)) {
19.                 enc3 = enc4 = 64;
20.             } else if (isNaN(chr3)) {
21.                 enc4 = 64;
22.             }
23.             output = output +
24.             this._keyStr.charAt(enc1) + this._keyStr.charAt(enc2) +
25.             this._keyStr.charAt(enc3) + this._keyStr.charAt(enc4);
26.         }
27.         return output;
28.     },
29.     // public method for decoding
30.     decode: function(input) {
31.         var output = "";
32.         var chr1, chr2, chr3;
33.         var enc1, enc2, enc3, enc4;
34.         var i = 0;
35.         input = input.replace(/[^A-Za-z0-9\+\/\=]/g, "");
36.         while (i < input.length) {
37.             enc1 = this._keyStr.indexOf(input.charAt(i++));
38.             enc2 = this._keyStr.indexOf(input.charAt(i++));
39.             enc3 = this._keyStr.indexOf(input.charAt(i++));
40.             enc4 = this._keyStr.indexOf(input.charAt(i++));
41.             chr1 = (enc1 << 2) | (enc2 >> 4);
42.             chr2 = ((enc2 & 15) << 4) | (enc3 >> 2);
43.             chr3 = ((enc3 & 3) << 6) | enc4;
44.             output = output + String.fromCharCode(chr1);
45.             if (enc3 != 64) {
46.                 output = output + String.fromCharCode(chr2);
47.             }
48.             if (enc4 != 64) {
49.                 output = output + String.fromCharCode(chr3);
50.             }
51.         }
52.         output = Base64._utf8_decode(output);
53.         return output;
54.     },
55.     // private method for UTF-8 encoding
56.     _utf8_encode: function(string) {
57.         string = string.replace(/\r\n/g, "\n");
58.         var utftext = "";
59.         for (var n = 0; n < string.length; n++) {
60.             var c = string.charCodeAt(n);
61.             if (c < 128) {
62.                 utftext += String.fromCharCode(c);
63.             }
64.             else if ((c > 127) && (c < 2048)) {
65.                 utftext += String.fromCharCode((c >> 6) | 192);
66.                 utftext += String.fromCharCode((c & 63) | 128);
67.             }
68.             else {
69.                 utftext += String.fromCharCode((c >> 12) | 224);
70.                 utftext += String.fromCharCode(((c >> 6) & 63) | 128);
71.                 utftext += String.fromCharCode((c & 63) | 128);
72.             }
73.         }
74.         return utftext;
75.     },
76.     // private method for UTF-8 decoding
77.     _utf8_decode: function(utftext) {
78.         var string = "";
79.         var i = 0;
80.         var c = c1 = c2 = 0;
81.         while (i < utftext.length) {
82.             c = utftext.charCodeAt(i);
83.             if (c < 128) {
84.                 string += String.fromCharCode(c);
85.                 i++;
86.             }
87.             else if ((c > 191) && (c < 224)) {
88.                 c2 = utftext.charCodeAt(i + 1);
89.                 string += String.fromCharCode(((c & 31) << 6) | (c2 & 63));
90.                 i += 2;
91.             }
92.             else {
93.                 c2 = utftext.charCodeAt(i + 1);
94.                 c3 = utftext.charCodeAt(i + 2);
95.                 string += String.fromCharCode(((c & 15) << 12) | ((c2 & 63) << 6) | (c3 & 63));
96.                 i += 3;
97.             }
98.         }
99.         return string;
100.     }
101. }