Innovative JavaScript Solutions

Archive for the ‘Uncategorized’ Category

We’ve Moved!

Due to the significant interest in XLSX.js, and the projects we have yet to release, we have decided to get our own site. This will allow us room to grow, and flexibility in hosting our content. Please update your bookmarks to:

http://blog.innovatejs.com

We hope to have a new demo/tutorial out today or tomorrow, so be sure to visit our new site! We apologize for any inconvenience!

Thanks,
Stephen and Kevin

Load an Excel file with XLSX.js

We’ve moved! You can see this post on the new site by clicking here.

It’s only the second day since XLSX.js was published, and we are thrilled and humbled by the response thus far! Thank you! In this post, we’re going to look at a very basic demonstration (located here: http://jsfiddle.net/innovatejs/a2RP3/) of how XLSX.js can be used to read data from an XLSX file.

The Setup

This demo is in the form of a fiddle (jsFiddle), and references JavaScript files for XLSX.js and jsZip hosted at yourjavascript.com. I’ve never used (or heard of) yourjavascript.com before, but it seems to work well. There are limitations in using GitHub’s “raw” versions of a JavaScript file, innovateJS does not yet have a full site, and JavaScript files can’t be hosted from WordPress. So, if those files are failing to load for you, please let us know and we will find another option.

To make this demo as basic as possible, I’ve embedded the file we’ll be reading into the fiddle as a base64 string. To do this, I created the spreadsheet, converted it into a base64 string using an online utility, and then pasted that string into the fiddle. However, as I mentioned in XLSX.js’s introduction post, there are multiple ways to obtain a base64 string of a particular file. There is nothing special about this file. Anyone could create it using Excel or any other product that supports XLSX. If you’d like, you can download the file here.

The Code

As you can see, there really isn’t much code to this demo – except for the embedded file. In fact, it could easily be condensed into one line, “$(‘#output’).text(JSON.stringify(xlsx(file)));”, located after the embedded file. However, for the sake of readability and tinker-ability, I put the code at the top and broke out the xlsx object into it’s own variable.

Reading an XLSX file with XLSX.js is very simple; call one function with one argument. XLSX.js takes care of the rest! This demo stringifies the object returned by the xlsx function, and then displays the string in the output div. The more complicated part comes in understanding the object representation of the XLSX file, and we’ll discuss that in the next section.

The Object

The data read or written by the current version of XLSX.js can be grouped into two main categories: metadata and worksheet data. The metadata is stored as a property of the object itself. For example, the “creator” field (Windows references it as “Authors” in the properties dialog) can be found at obj.creator. Similarly, we see fields for “created”, “lastModifiedBy”, and “modified”. In addition to the metadata, we also see “activeWorksheet”, “processTime”, and “zipTime”. These fields are not truly metadata, but they are similar in that they are a property of the loaded object as a whole. One XLSX file can only have one active worksheet, and it can only have taken one amount of time to process or unzip for a particular instance, just like it can only have one “created” time stamp. Therefore, in that context, these three fields are treated as metadata and stored in a similar fashion.

So, that just leaves the worksheet data. The object’s worksheet property is an array of the spreadsheet’s worksheets. Each worksheet is an array of rows in that worksheet, but the array also has a “name” property to specify the name of the worksheet. Finally, each row is an array of cell values in that row, from left to right.

The Rest

In the near future, we hope to release further demonstrations that illuminate how this basic load functionality can be expanded into a powerful user experience. However, this demo did allow us to look at one of the two fundamental functionalities of the library in a simple manner and to gain familiarity with the object representation of an XLSX file. The later will be essential for our next demo, which is basic file creation.

Stephen

Introducing XLSX.js

We’ve moved! You can see this post on the new site by clicking here.

It’s amazing, isn’t it? For years JavaScript was just a small scripting language of relatively little consequence. Now, in symbiosis with HTML and CSS, it is evolving into a first class development platform. It’s still short of that, to be sure, but I have little doubt as to the destination. Now we see JavaScript playing a role in server-side scripting, 3D gaming, and content rendering, but the future will hold much more for the little language that could. Rendering libraries such as PDF.js, JSMad, and Broadway are likely to be precursors to the emergence of JavaScript content generation. We already see a few indications of this, but today we’re looking to strengthen this trend.

XLSX.js

XLSX.js is a library that converts base64 strings into object representations of XLSX spreadsheets, and vice versa. Now, some of you may be thinking, “I get the object representation of an XLSX spreadsheet part, I like that, but what good is a base64 string?” Well, actually, there are a lot of things you can do with base64 strings. Though sometimes a conversion from binary is required, you can obtain them from XHRs, FileReader, or Silverlight/Flash uploaders. And, similarly, you can send them to web services, FileWriter, Data URIs, or Sliverlight/Flash downloaders. In other words, this library will allow you to read and write Excel-compatible XLSX files when paired with an appropriate file access mechanism.

Why?

Current solutions for reading or writing XLSX data take one of two approaches; use ActiveX to communicate with Excel or send your data to a server for conversion. A number of the applications we had written required the ability to read and write Excel files, so we explored both options and found a number of concerns with each.

ActiveX

This may work in some situations, but there are a number of concerns with this method:

  • One, ActiveX is only supported on Windows, using Internet Explorer. Still, even if that perfectly describes your audience, it looks as though ActiveX may be playing a diminished role in the future. Only the desktop version of Internet Explorer 10 supports ActiveX, it is not supported in the Modern UI, Windows Phone, or Xbox versions. Now, you may not need support for the Modern UI version or Windows Phone. And, I am sure there is no demand to generate XLSX files on the Xbox. However, I do think it is wise to pursue methods that are standards compliant and cross-browser compatible. Nobody knows what the future holds.
  • Two, using the Excel ActiveX control requires that 100% of your audience has Excel installed. In some scenarios, that is not the case.
  • Three, the user must change Internet Explorer’s security settings in order to allow JavaScript access to the Excel ActiveX control. It is restricted by default, and with good reason. Once the control has been loaded, I do not know of any checks on what the script can do (within the context of Excel). That may be acceptable in certain intranet zone or trusted sites zone scenarios, but that would never be an acceptable change to the internet zone. Still, even if your circumstance allows the user to change their settings, it still impacts user experience and often requires training and/or setup time.
  • Four, interacting with the ActiveX control is rather awkward. Most of the documentation and examples are for other programming languages, and it is not very efficient.

Server-side

The biggest potential concerns with this solution, that I can think of, are whether you have the ability to run server-side code and whether it makes sense to run server-side code. The answers to both questions may seem to be foregone conclusions… but maybe they aren’t. What about web applications hosted in SharePoint Online? SPO doesn’t allow you to run custom server-side code. What about Modern UI applications written in HTML5, CSS3, and JavaScript? Do you want to run a server just to generate content, when the rest of the application is local? What about HTML5 mobile applications? I would theorize that scenarios without the ability to run server-side code may become more common, at least for a while, rather than less. If you have the ability, there might still be some cases where you would want client-side generation, but I doubt many would prefer that idea at this point. However, if you don’t have the ability, there are no alternatives beside the ActiveX solution discussed above.

Our situations prevented the use of server-side code, so we were left with ActiveX. ActiveX, with the limitations described above, left us wanting more. Hence, we created XLSX.js!

How?

OK, so, how does it work? Well, let’s start with a little information about XLSX files. As opposed to Excel’s previous format, XLSX is XML based. However, an XLSX file isn’t just one big XML document. It is a ZIP file composed of numerous XML files in a particular (folder) structure. Therefore, in order to interact with these XML files (either through string manipulation or DOMParser) we need to unzip the archive. XLSX.js leverages JSZip to unzip the file, and then it reads and correlates the data in the XML documents to generate an object representation of the XLSX file. For writing the file, it does all of that in reverse.

What About…

  • JSZip: Yes, you will need JSZip in order for XLSX.js to work properly. XLSX.js does not come with, and will not be distributed with, JSZip. You will need to download the appropriate files, attach them to your project, and ensure you are in compliance with JSZip’s license by yourself.
  • Features: Right now XLSX.js supports reading data from multiple worksheets, the names of the worksheets, the active worksheet, and file metadata. It does not support the reading of formatting information, macros, charts, or anything else. It also supports writing simple data to multiple worksheets, setting the names of the worksheets, setting the active worksheet, and setting file metadata. It also supports the ability to turn the data on a worksheet into a sortable table. It does not support the writing of formatting information, macros, charts, or anything else. However, this need not (and hopefully will not) be the case forever. Our situation did not require the development of any features beyond what you see listed above. As time goes on we may add features as we need them for other projects, or as we have free time. But, that doesn’t prevent the community from expanding and refining the feature set! Every feature should be possible, should you wish to add it.
  • Support: No, we will not be providing official support at this time. We will try to answer your questions as we have time, and help as much as possible, but we are not currently in the position to guarantee any support, fixes, or updates.
  • Demos & Documentation: Our first demo, located here, covers basic load functionality. Our second demo, located here, builds on these concepts to look at basic save functionality. For further demos and tutorials, please check back often!
  • Word, PowerPoint, etc: Stay tuned…
  • Licensing:  XLSX.js has been released by Microsoft under the Microsoft Office Extensible File License. The text of this license can be found on GitHub, along with XLSX.js itself. The Microsoft Office Extensible File License is an open source license, but it is not OSI approved. This license has similarities to the MS-PL, but there are significant differences. Please read the Microsoft Office Extensible File License carefully before using XLSX.js

So, with that I give you XLSX.js! The source can be found on GitHub, located here:

https://github.com/stephen-hardy/xlsx.js

Enjoy!
Stephen

Introduction

Hello! I’d like to take a moment to welcome you to innovateJS’s new blog, and share a little bit about who we are. But first, thank you for your visit, and for your patience while we continue to refine and expand our site!

innovateJS is a partnership between Stephen Hardy and Kevin Braiden – two talented and dynamic developers working in Microsoft’s OEM Operations. We specialize in creating cutting edge web applications that promote an excellent user experience. Sometimes these applications require thinking outside of the box to solve problems yet unconquered, or rethinking solutions that have room for improvement. We’ve worked with .NET languages and Silverlight, but lately we’ve been using HTML5, CSS3, and JavaScript a lot more.

With this blog, we look forward to sharing some exciting solutions, as well as smaller tidbits. We hope you’ll check back from time to time!

Thanks!
Stephen and Kevin

Tag Cloud