Tuesday, March 8, 2011

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

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

12 comments:

  1. Thanks for the GetViewCollection example.

    ReplyDelete
  2. First off, thank you for providing this information! I'm no expert by any means, but I'm learning my way with SharePoint customization. How do I go about implementing this code? Where do I place this and how can I invoke this code?
    Thank you in advance!

    ReplyDelete
    Replies
    1. Sorry for the late reply! There are many means to which you can employ this code. However, due to one of the major functions of how it works, you may want to first decide if it will be able to work in your environment. At some point I think I may refactor and improve this to be reusable as maybe a jQuery plugin or something. For now, I mainly used it in a content editor webpart.

      Delete
  3. I'm assuming we include the Base64 variable in the script itself? The link you have doesn't work anymore - goes to a domain purchase page.

    ReplyDelete
    Replies
    1. Yes, unfortunately that link seems to be dead. So, yes, I do include that in my script code. I may at some point have added it as a reusable piece somewhere but it really doesn't add too much to the code.

      Delete
  4. I think I'm almost there now - it's opening the spreadsheet and attempting to import the data. On the line where you open the workbook (defining variable 'owb'), where is the 'reqXML' object being defined? Is that supposed to be xmlData3 instead? It appears to be looking for responseText from an operation, but which one?

    ReplyDelete
    Replies
    1. OOOPS! That should be opening the (dest) url. oxl.Workbooks.Open(dest)

      Hope that helps

      Delete
    2. Yep, that got me a little further! It looks like the stream into user.xml isn't working, though - that file never gets created. The operations returns all of the list items, but then it looks like the "CopyIntoItems" operation is failing with 0 items. Error is "Value does not fall within expected range". Probably something on my end.

      Delete
  5. The script seems to be working now but there's a problem with my Excel XSL file. Do you have a copy of yours you can attach or send me? I created one by copying & pasting the XSL bits from the previous article, but it's not working either - tells me I have bad XSLT.

    ReplyDelete
    Replies
    1. Not sure that I have one readily available, but if you follow the instructions in the first post (http://spevilgenius.blogspot.com/2010/12/using-spservices-and-excel-to-create.html) this should work. I know that in Excel 2013 the save function is presented as Excel XML 2003 format, but this is what you want.

      Delete
    2. Ok - last issue, hopefully. The script is successfully launching Excel now and uses my template. It doesn't seem to be importing the z:row data, though - I just get the blank Excel template. I'm pretty sure something is wrong in my XSL. If this was a DVWP in SPD, I'd say the data source isn't working.

      Delete
    3. Or rather, the XSL isn't reading the data source. I noticed the 'z' node refers to #RowSetSchema, but that's not defined in my XSL anywhere. I don't see it in your example XSL either though, so maybe I'm barking up the wrong tree.

      Delete