Creating/Editing XLSX or DOCX documents directly in JavaScript

Must admit that when I first starting looking at this I wasn’t overly hopeful of achieving it, but it is possible…it’s not dead simple, but it is definitely doable. TL;DR: Download the project, open it in Visual Studio, read the code, deploy it and then try it. There are numerous things you need and it involves a number of different script libraries, all with varying documentation. Hopefully this post will put you in the right direction, mostly because it includes the source code to a working example of creating an Excel Spread Sheet in JavaScript and uploading it to a document library in SharePoint. First the bits you need In order to get this working you need to add a few scripts to your project. Fortunately Eric White over at OpenXmlDeveloper has done a lot of the hard work for us. I am basically adding a bit of SharePoint stuff to his work and it’s always nice to have a working sample. Firstly you will want to have a look at the Open XML SDK for JavaScript, which provides some wrapping for the Open Xml specification in JavaScript. The Open XML for JavaScript provides some useful examples, but if I am honest they do take a lot of reading and viewing of webcasts to get anything working; And once you add SharePoint into the mix it obviously gets a bit harder. You can go to the Open XML SDK for JavaScript site on CodePlex and download the source. This actually contains all the files you need to add support to your project. However, it’s worth mentioning the other projects that it uses. JSZip This allows the SDK to open the XLSX or DOCX as they are just ZIP files anyway. linq.js LINQ for JavaScript. This is used to access the object model of the XLSX or DOCX. JSZip extensions Extensions for inflating & deflating ZIP files in JavaScript. Open XML The Open XML file formats. The Open XML SDK for JavaScript doesn’t actually hide the complexity of the Open XML format, you still have to deal with all the different XML elements and attributes it requires. What it does do is give you easy access to the different parts of the document inside the DOCX/XLSX zip file. It also provides a simpler way to handle the different namespaces you need to generate the XML of a DOCX/XLSX file. What to include on your page This is always the first problem, what exactly are the scripts I need to include on my page for all this stuff to work. If you download the CodePlex project there are a number of scripts and sample pages. They all provide useful information and functionality, but you don’t need them all. These are the script I included on my SharePoint App page… <script src="../Scripts/openxml/linq.js"></script> <script src="../Scripts/openxml/jszip.js"></script> <script src="../Scripts/openxml/jszip-load.js"></script> <script src="../Scripts/openxml/jszip-deflate.js"></script> <script src="../Scripts/openxml/jszip-inflate.js"></script> <script src="../Scripts/openxml/ltxml.js"></script> <script src="../Scripts/openxml/ltxml-extensions.js"></script> <script src="../Scripts/openxml/openxml.js"></script> .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } This will give you all the functionality required and the order is important. I always have jQuery included and I wouldn’t be surprised to discover that it is a requirement too. How do I create a document Basically you don’t, you start from a blank document template and add to it. I won’t include it here, but they are in the sample project and it is a base64 encoded string, which is the XLSX or DOCX from where you want to start. In the samples and in my project this is included in a JavaScript file as a string literal. This actually works quite well as it means you can pre-configure things and save writing code to set formats or static content. Creating the base64 string is a little (but not too much) involved. There is a post describing it and I used the PowerShell scripts from that post without problems. Creating the document in JavaScript So, once you have your templates (feel free to re-use the ones in the project, they are just blank) you can then open the document in JavaScript and modify it’s contents. The JavaScript Open XML SDK has more examples for DOCX and so I will be showing an XLSX example. Here is the script to open an XLSX, add a row and to add a cell. The example below just uses the openXml object and the OpenXmlPackage  method to open the blank XLSX document template. Once the spreadsheet is opened it gets the workbook and the first sheet. You will notice it is using LinkJs to achieve this. It then gets the sheetdata, adds a new row and then adds a new column. This is a very basic example of setting one cell value. If you want to add different data types (strings, dates, etc) then you will need to modify the code which creates the ‘cell’. Generally you have to build the correct XML for the cell using the appropriate XAttributes and XElements. Building the correct structure of the OpenXML document is going to be a bit of a pain, but fortunately finding out what it is supposed to look like is pretty easy. Because an XLSX or DOCX is just a zip file you can rename them to .ZIP and look at the contents. In the case of a spreadsheet you will find a folder called ‘xl’ and in that folder another folder called ‘worksheets’, this is where all the sheets are stored. Opening one of the sheets you will see the OpenXML for that worksheet. So, by creating a sample spreadsheet, renaming it and looking at the XML you can find out what elements you need to create. The project The project is just a very basic SharePoint App which allows you to enter a number, generate the SpreadSheet using JavaScript and save it to a document library using REST. It’s hosted on GitHub and can be downloaded from here.

So you want to replace the default SharePoint asset picker

I have just had reason to replace the default OOTB SharePoint 2013 asset picker, you know the one. It gives you lots of places to find things, but absolutely no way to search or filter. And God help you if you want to link to an asset in another site collection.     Anyway, there is a pretty simple way to replace this dialog with your own implementation. You just need to override some global variables so that SharePoint uses your dialog instead of the one specified in assetpickers.js So, here is the code you need… <script> ExecuteOrDelayUntilScriptLoaded(function (){ window.CONST_ImageAssetPickerDialogName = "mypicker/picker.html"; window.CONST_LinkAssetPickerDialogName = "AssetPortalBrowser.aspx"; window.CONST_EditHyperLinkDialogDialogName = "AssetEditHyperLink.aspx"; window.CONST_EditImageDialogDialogName = "AssetImagePicker.aspx"; }, "assetpickers.js"); </script> .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }This modifies the dialog locations specified in assetpickers.js with your own custom one. Here I am replacing the Image Asset Picker with my own implementation. You can see your other options, but you only need to specify the ones you wish to change. The one thing you to ensure is that your dialog implementation returns the same object as the OOTB SharePoint one. Your OK handler should go something like this… function onOk(e){ var result = { AssetUrl: <selectedURL>, AssetText: <selected title>, ListItemFields: null}; window.frameElement.commonModalDialogClose(1, result) } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } This will return the correct format of the JavaScript object for the asset picker to work. You do get a lot of information passed in the query string, but I’ll leave it to you to decide if you need it or not. It’s also worth looking into assetpickers.js as there are a lot more global variables where you can override the default behaviour of your pickers. Shame I can’t share the search based picker I wrote, but I might write my own cut-down version that I can share with you all. Until then there is no reason why you can’t implement your own picker.

Introducing the JSOM Fiddle SharePoint App

I’ve been doing a fair amount of JSOM development recently, you kinda have to if your doing a ShareP [More]

TypeScript files not compiling on save in VS 2013

I’m using TypeScript for my SharePoint development…makes JavaScript soooo much easier. Anyway, I had a problem with .ts files not compiling on save and so not being able to update the .js file on the server while debugging. A big thanks to ‘Kevin S’ for sharing the workaround on Stack Overflow… http://stackoverflow.com/questions/19371735/typescript-0-9-1-1-not-always-compiling-on-save The solution is to close the .js and the .ts files. Then re-open the .ts file. It will now compile when you save it and you can re-open the .ts file.

SharePoint on Windows Phone

AgilisPoint a free App for your Windows Phone