Hello Everyone,

This is just a quick post to describe the changes present in v2.2.0 of XLSX.js. There are four of any significance:

• Read blank cells and rows. In previous versions of XLSX.js, blank cells or rows would not be represented in the JavaScript object. It would correctly represent a space, or other form of white space, but that creates some obvious issues in general use. Now, the JavaScript object observes blank cells or rows with null values in the arrays (null rows, or null cells in a row).
• Write formulas. XLSX.js v2 made a change to allow cells to be represented as objects, instead of just values. The first benefit was to allow the assigning of data format, and today this becomes another benefit of that change. XLSX files allow a cell to have a value and a formula, so the formula SHOULD NOT be given as the value. The value is shown before the formula is calculated for the first time, and does not need to match what the value should actually be (if a person would ever want to make them different – not sure if there is a use case for that). So, setting the formula of worksheet 1′s A1 cell may look like: xlsxObj.worksheets[0].data[0][0] = { value: 6, formula: ‘SUM(A2:A4)’ };
• maxCol and maxRow. The worksheets in XLSX files specify their dimensions in the XML document, but up until now that information has not been used. It was up to the developer making use of XLSX.js to calculate max dimensions, if necessary, from the output. However, that is very inconvenient and unperformant with regards to the number of columns. To calculate that from the output, one would need to loop through every row and find the maximum length. Since that is quite unnecessary, I added the maxCol and maxRow properties to a worksheet when read. When writing a worksheet, those properties would serve no purpose and would be ignored. These properties are like length, in that they are not 0-based. So, a reference to worksheet 1′s maxRow would look like: xlsxObj.worksheets[0].maxRow
• Unescaping &, <, >, ‘, and “. In previous versions, reading a worksheet with these values would show the escaped values for these characters. Now they should return to their ‘original’ values.

Thanks,
Stephen

Today, we are excited to launch the first major update to XLSX.js! With it comes two major changes that we’ll talk about in this post. The first is a change to the API (JavaScript object representation of the spreadsheet), and the second is the added support for number formats (numbers, currencies, text, dates, etc). Though the former may cause a little inconvenience, we think it is a needed change that will allow us more flexibility going forward.

API Change

Two things have changed with the JavaScript spreadsheet object, and both will break existing code. However, they are relatively simple changes that are easy to accommodate.

Worksheets

The first change pertains to the representation of individual worksheets. In the first version, worksheets were arrays that had additional “name” and “table” properties. Originally, this seemed to make some sense because the worksheet’s name is a property of that worksheet similar to how the data in the first row is a property, or index, of that worksheet. However, as time went by the initial prick to my conscience grew, and I realized a number of reasons why it wasn’t the best idea.

• Some developer tools can’t display properties of an array.
• Worksheets won’t stringify properly. Which could theoretically be needed for postMessage, for example.
• Worksheets, and therefore entire files, can’t be defined as object literals.
• It may be more confusing for those who are just starting into JavaScript

I’m sure there are more examples, and perhaps those aren’t even the most important, but the conclusion was pretty solid: worksheet properties needed to be separate from the array of rows. As a result, worksheets are now objects with three properties. Two are “name” and “table”, just like before, and the third is “data”. Data stores the array of rows previously stored on the worksheet object itself. So, for example, in the first version of XLSX.js one would access cell A1 of Sheet1 with file.worksheets[0][0][0]. Now, one would access the same cell with file.worksheets[0].data[0][0]. In both cases one would access the name with file.worksheets[0].name.

Cells

The second change pertains to cells and their values. In the first version, cells were strings or numbers in a row’s array. And, that works just fine as long as the only thing you need to know about a cell is it’s value. This was appropriate for the first version because it did not need to know about a cell’s number or style format. However, as we look to expand XLSX.js’ capabilities, this will need to change. Version two brings number format support, and future versions will likely bring style format support, which require more information be stored about each cell. We believe the best way to accommodate this is to turn cells into objects that store value and formatCode information. In the future, this can easily be expanded to storing style formatting information without affecting existing functionality.

So, when reading a file with XLSX.js, all cells will now be objects with a value property and a formatCode property. If you don’t need the formatCode information, feel free to ignore it and all you’ll need to do is add “.value” to the end of your existing cell references. For writing, this change is optional. If you need to specifically set a cell’s number format, make the cell an object with value and formatCode properties. Otherwise, feel free to just put the value as before. If no formatCode is specified, XLSX.js will automatically determine the format when necessary.

Number Formats

In the first version, XLSX.js supported numbers and strings without any formatting. Now, XLSX.js supports all of the number formats including currencies, dates, text, and boolean (though technically not a number format). When reading such formats, XLSX.js will convert date and boolean values into their native JavaScript counterparts. And, when writing such formats without specified format codes, XLSX.js will automatically convert to an appropriate number format. Booleans are pretty straightforward, but dates assume mm/dd/yy if no format code is given.

The format codes used by XLSX.js are the same ones found in Excel, specifically revealed in the “custom” pane in the format dialog. I’ve tested a number of format codes, but as far as I know any valid format code should work. Additionally, I had no problems writing a table with special formats included. Everything sorted and filtered properly, and looked great!

Additional Items

Date Conversion

Some of you may notice that the convertDate function adds a day when writing and subtracts a day when reading. This is necessary because of a bug, though not Microsoft’s, in the XLSX date format. In short, the 29th of February in 1900 exists when it shouldn’t. Therefore, JavaScript’s conversion from a date object to XLSX’s number will be one day short, and the opposite will be one day too many. I noticed that a fork of XLSX introduced the ability to read dates as formatted strings, but did not account for this bug. I’ve not tested the fork’s code, but it will likely be one day off. I only post to explain the discrepancy.

Comments

I’ve not heard anyone question this, but I realized the other day that my comment lines beginning with “//{” and “//}” might seem a little odd. I use Notepad++ as my code editor, and as long as there is no space between the “//” and the “{” or “}” it detects that as a block and allows the user to fold it. Being able to fold sections of code that I’m not working on, even though they may not otherwise be in a block, helps me a lot. My thought in leaving such comments in there was that it may work in some other editors, but if not people will still understand the grouping and may perhaps gain some benefit from it. If not, you are free to take it out!

Well, that’s it! Hopefully you’ll find the number formatting helpful, and the API changes easy to accommodate. I’d imagine that find and replace should take care of most it, but I believe the benefits outlined above outweigh any additional effort. Enjoy!

Stephen

Our first update to XLSX.js is now available on GitHub! It brings performance improvements (particularly in file creation), minor bug fixes, and the ability to work with more columns.

And, don’t forget to look through our demos and tutorials on loading an XLSX, and saving an XLSX. They should get you going in no time!

** 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!