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.

SharePoint on Windows Phone

AgilisPoint a free App for your Windows Phone