Save an Excel File with XLSX.js

** Note: With version 2, the API of XLSX.js has been updated in ways that will break existing code. The details of these changes are outlined here. **

Hello, and welcome to our new site! We are still getting things setup, so please excuse the ongoing changes.

Today, we are going to look at creating an Excel-compatible XLSX file with XLSX.js. Since this demo/tutorial picks up where our last one (Load an Excel File with XLSX.js) left off, please be sure to read through that post – especially “The Object” section. So, with that, let’s load the fiddle (http://jsfiddle.net/innovatejs/ueETX/) and dig in.

The Setup

I started this fiddle by forking the one used in our last demo, so all the references and settings should be the same. And, like the last demo, this one is targeted toward simplicity and a basic demonstration of the functionality. To that end, Data URI’s are used as the download mechanism for the base64 file created. IE does not yet support this download method for this file type. Therefore, I recommend that you view the demo in something other than IE (I tested Chrome, Firefox, and Opera, all worked fine. I think Chrome’s experience is the best, in this case). If you must view the demo in IE, I suggest changing the second to last line to “alert(xlsx(file).base64);”. That will not create a downloadable file for you, but it will give evidence of the base64 string created. There are two reasons why I did not include a more advanced download mechanism for this demo: One, Flash or Silverlight downloaders must be hosted on the same domain as the viewed page. Given that I cannot upload Flash or Silverlight to jsFiddle, that poses a problem. Two, it would add unneeded complexity to an otherwise simple demo (this is not a production page).

The Code

This demo requires quite a bit more code than the previous one, but fortunately it’s not much more complicated. Let’s start with the HTML. At the top we have a text input for the worksheet’s name, followed by a table that will represent our worksheet data. This table has four rows and four columns, each with text inputs showing default values that match our previous demo. Finally, we have a save button that will trigger the processing and download.

For the JavaScript, we start by defining a click event for the save button. “file” is declared at the top of the event, with all of the metadata defined and an array of one empty array for the “worksheets” property. As we covered in the last demo, the “worksheets” property is an array of worksheets, worksheets are an array of rows, and rows are an array of values. Therefore, an array of one empty array means that this workbook will have just one empty (for now) worksheet. Next we cache said worksheet to the variable “w”, for worksheet. This will be helpful for readability, and speed in the coming loops. However, before we get to those loops, we are going to give this worksheet a name. The worksheet name is stored in the worksheet’s “name” property, so we set w.name equal to the value of the “WName” text input.

Next, we populate our solitary worksheet with data. I’ve named the one table in the fiddle “Worksheet1″, so that tinkerers can easily explore scenarios with multiple worksheets. But, for this scenario, we only have the one table/worksheet. We start by getting the jQuery object for that table, then find all rows (“tr”) inside it. Then, for each row, we create a new row in the file object. The “push” method of a JavaScript array works nicely to add an empty row (array) to the worksheet, and then cache the index for this new item. “Push” always returns the length of the array it is modifying, after it makes the modification. Therefore, since the pushed item is always the last one, it’s index is always the result of “push” minus one (since index is zero based, whereas length is not). Caching this index into “r”, for row, will come in handy in our next loop. The last step is populating each created row with data. To do this, we get the jQuery object for the row (this), and find each input inside it. In this scenario, we know that there will not be more than one input per td (column), so it is safe to skip over the td elements. For each input, we push the value into the recently created row – found by looking at the “r” (row) index of the “w” (worksheet) array.

Finally, we point the window’s location to the href string XLSX.js generates from the file object. Calling “xlsx(file)” will return an object with four properties. “processTime” and “zipTime” represent the same information as they do when reading an XLSX file – the amount of time it took to process the data and the amount of time it took to zip the data. “base64″ is the actual base64 string generated by XLSX.js. However, it’s the “href” property that might cause the most confusion. “href” is a function that simply concatenates the base64 string with the XLSX MIME type to generate a string to which the browser can be navigated (in non-IE browsers). Our goal in creating this was to save the developer from needing to find the MIME string and do the concatenation themselves with each use. We figured that the memory benefit of not storing the string in concatenated and non-concatenated forms would outweigh the performance detriment of needing to concatenate the string with each call. Since the user should cache whichever result they prefer, if they are using it multiple times, there should not be a significant performance detriment.

The Rest

So, there you have it. At the click of a button, a table of text inputs are downloaded as an Excel file – without the use of ActiveX or Excel itself. For those who wish to fiddle with the fiddle, I tried to lay out the demo in a very expandable way. For example, the loops allow for the easy addition of more rows or columns. And, these loops can be duplicated and used to create a second worksheet, with slight modification. One could even add columns, rows, or worksheets dynamically - the possibilities are endless!

Tagged with: ,
Posted in xlsx.js
4 comments on “Save an Excel File with XLSX.js
  1. Christian Masannek says:

    Hello Stephen Hardy.
    First of all. Thank you for this nice piece of software.

    However I have some problems.
    First of all:
    I’m using JSF to populate a with content from our database.
    Now I want to save this dynamically created table.
    table:

    ….

    When I try to access the table via
    $(“databaseToExcel”).find(‘tr’).each(function()
    The script doesn’t do anything.

    And when I try to use the dynamically created ID :
    #j_id1135038557_2_55aaadc1:databaseToExcel
    an error is thrown:
    “uncaught exception: Syntax error, unrecognized expression: databaseToExcel”

    How can I access dynamic id’s via your script.

    • Hello Christian,

      Nick is quite correct. $(“databaseToExcel”).find(‘tr’) will cause jQuery to look for all tr elements under a databaseToExcel element (as opposed to a table or div element). Prepended hashtags will look for elements of the specified id, prepended periods will look for elements of the specified class, and strings without a prepended symbol will look for elements of the specified node name.

  2. Nick says:

    Christian: You have to learn jQuery to understand that error. See, you didn’t tell the jQuery selector engine (called Sizzle) to find you anything sensible. In order to select an element by it’s ID attribute, you must specify it in this format: #idValue. You just specified the id value without the hashtag in front…

Leave a Reply

Categories

disclaimer

The opinions expressed herein are the personal opinions of the authors and do not represent our employer’s view in any way.