XLSX.js v2.2.0

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

Tagged with: ,
Posted in xlsx.js

XLSX.js v2.1.0 & DOCX.js v1.0.1 – Line Endings and Bug Fixes

Hello All,

Today brings new, and fairly minor, versions of XLSX.js and DOCX.js.

First off, both are now using LF line endings in the database – as opposed to CRLF (DOS/Windows). Though I’ve known of this difference in line endings for years, it never connected in my brain that this could cause an issue in GitHub until I submitted a pull request for Isotope and had the first version rejected. Though I only changed 19 lines, GitHub showed every line as being changed. Technically, due to different line endings, GitHub was correct. Thankfully, Desandro was kind enough to point me in the right direction regarding my pull request, and I created a version that did not change the file to a non-standard format. However, at that time I was mostly focused on getting the pull request correct and moving on to other items. I did not explore the full scope of the problem until michl-me submitted pull request for XLSX.js that changed every line. To borrow from Yogi Berra, it was like deja-vu all over again. He graciously converted his fork to CRLF, before passing on some information he learned about the subject. Apparently GitHub can take care of this by itself, with the wave of a magic .gitattribute wand. So, I merged Michael’s new pull request, made a few minor changes, and converted XLSX.js and DOCX.js to LF.

In the end, I think there are two takeaways:

One, Git owners should learn about the .gitattribute file and configure it correctly to prevent issues for contributors. This is especially easy for JavaScript-based Gits, like XLSX.js and DOCX.js, because there should be no binary files or special considerations. And, even if you are including something like a picture as a part of some demos, it’s pretty easy to accommodate that.

Two, and of lesser importance, contributors should learn about line endings and GitHub. This allows contributors to collaborate more effectively, and help educate the Git owners about the problem and possible solutions. Just like what happened in this instance. :)

Aside from that change, there were some minor changes and bug fixes for XLSX.js and DOCX.js. XLSX.js received fixes for number detection, XML escaping, UTF-8 encoding, reading sharedStringsstyles for multiple worksheets, and miscellaneous optimizations. DOCX.js simply had the method of obtaining the milliseconds of the current time improved.

We hope to release new features for XLSX.js and DOCX.js in the future, but that is dependent on when the time and/or need arises. In the meantime, we’ve very much enjoyed seeing people use these libraries and contribute features and fixes!

Stephen

Tagged with: , ,
Posted in DOCX.js, Uncategorized, xlsx.js

XLSX v2.0.0

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

Tagged with: ,
Posted in xlsx.js

Introducing DOCX.js

We’ve been amazed and honored by the amount of interest in XLSX.js! The library certainly has a lot of room to grow (and we hope to have another update out soon!), but it’s been wonderful to see people using it in exciting new ways. We hope that today’s release will prove just as interesting and useful to the JavaScript developer community.

DOCX.js

DOCX.js is a JavaScript library that converts base64 strings of DOCX files into HTML, and vice versa. The conversion takes place purely in JavaScript, without the aid of plugins like ActiveX, Flash, or Silverlight. While general support for these plugins are waning, they are not supported at all on mobile devices or in node.js. Therefore, DOCX.js allows DOCX files to be read and written in ways not previously possible. For more background information regarding alternative solutions, use cases, and our methodology, please see the XLSX.js introduction post.

But… a “DOCX.js” Already Exists…

Yes, it certainly does. James Hall (MrRio) published DOCX.js to GitHub on February 21st of this year. James is, perhaps, best known as the creator of jsPDF – a PDF-authoring library that was a forerunner of JavaScript content generation. Unfortunately, DOCX.js did not get much publicity and we were unaware of it when we started developing XLSX.js and similar solutions. However, in the process of open sourcing our DOCX.js, we discovered James’ library (or, prototype, as he called it). The existing DOCX.js was great at generating a DOCX file with just plain text, but we needed something that could read and write formatted text – and we thought the community would find that useful too. Unfortunately, Microsoft could not contribute code to DOCX.js due to legal concerns. But, we wanted to make sure that James’ achievements were recognized while opening our code to the community and maintaining continuity with XLSX.js.

I wrote James an email explaining the situation, and he promptly wrote back with a very gracious offer to let us use the name “DOCX.js”. He certainly didn’t have to do that, but we definitely appreciate it. And, to that end, we want to be very clear regarding James’ preexisting work. We would encourage you to look at the links above for James’ website, his Twitter, and the amazing jsPDF library!

The Format

When we started writing XLSX.js, there was a fundamental problem to address: What does a JavaScript representation of a spreadsheet look like? Should worksheets be an array of columns or rows? How do we deal with active worksheets? How should the cell formatting be represented? Sometimes we followed XLSX’s internal structure, other times we went with what we thought would be most intuitive. And, we didn’t always get it right. The next version will be making some small tweaks to the JavaScript representation of XLSX files. However, with DOCX.js we think the answer is pretty clear: The best representation of a Word document is HTML5. There are a number of considerations that led us to this conclusion:

  1. WordprocessingML and HTML5 have a number of similarities that make conversion somewhat straightforward.
  2. HTML5 is already defined and well-known, as opposed to any JavaScript representation we could devise.
  3. HTML5 input can, theoretically, be taken from any number of WYSIWYG HTML editors. (eg. TinyMCE, Aloha Editor, etc.)
  4. HTML5 output can easily be displayed, styled, and/or edited without further processing.

The Features

DOCX.js supports the writing of bold, italic, underline, strikethrough, subscript, superscript, font sizes, font color, highlights, and horizontal alignment. For reading, it supports all of the above plus embedded PNGs. Support for tables and lists (bulleted and numbered) will likely come in the future, but is not currently present.

What About…

  • Licensing: DOCX.js is released under the Microsoft Office Extensible File License.
  • Dependencies: JSZip must be present for DOCX.js to work properly. DOCX.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.
  • DOMParser vs. String Processing: In my recent post, Answering a DOM Question, I talked about the benefits of processing XML as a string rather than using DOMParser. However, DOCX.js was created before that matter was closely examined. Future versions of DOCX.js will likely switch to string processing of the XML files.
  • Demos and Tutorials: Coming soon!

The Code

Ready to get at it? DOCX.js is hosted on GitHub, and can be found here:

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

Please let us know if you experience any issues, and we’ll try to get back to you as soon as our work allows. Also, we’d love to hear how you are using it to provide powerful new experiences! Enjoy!

Stephen

Tagged with: , ,
Posted in DOCX.js

Answering a DOM Question

What’s the saying? “There are no dumb questions, only dumb answers”? Well, apparently that can also apply to JavaScript methodologies. This post is written to answer what I thought was a “dumb” question. It was a question that I posed to myself after reading Ingo Rammer’s tweet: “Wow .. the xlsx.js code is *exactly* how one shouldn’t parse XML. Ever. I thought we passed this line about five years ago … ;)” Now, I didn’t take offense at what Ingo said. In fact, I agreed with him.

Back when I first started writing XLSX.js, I knew that parsing the XML out as a string was not in keeping with current practices. These days we have DOMParser in all the major browsers, and the XMLDOM ActiveX object for older IE browsers. We can deal with these XML nodes as hierarchical, searchable objects. But… I figured I’d just stick with the string parsing anyway. I assumed that I would update it at some point, when I had more time, but initially I was looking to create a solution quickly and theorized that string processing was probably somewhat faster. Fast forward to a week ago, and a need has arisen that will require me to expand XLSX.js’ capabilities. As I will need to modify the reading and writing code anyway, I started by switching over the read code to DOMParser. Upon completion, I remember Ingo’s tweet and wonder if I should post a little comment on the blog explaining why I used string processing in the first place. In fact, maybe I’ll just throw both pieces of code into jsPerf to substantiate my theory of string processing being a little faster. It’s probably a little faster, but can it really be fast enough to justify staying with that method?

But, that’s a “dumb” question, right? Parsing XML using the DOM is more maintainable, it’s more extensible, it’s likely more stable. It’s certainly more common. The community has decisively gone this direction, either natively or via libraries like jQuery, so it must be the better solution in this instance. However, despite those valid points, the jsPerf provided a compelling rebuttal. My initial tests showed that string processing was 10x faster on Safari 5, and 41x faster on IE. Someone out there found the jsPerf on an Android device, and showed string processing to be 47x faster there. So, the performance gains are highly significant.

However, while DOMParser will only be called once, getElementsByTagName will be called an increasing number of times as the document gets bigger. My initial tests were with a fairly small document, so I wondered if this performance difference was minimized as the document size increased. I created an Excel spreadsheet with two columns and one thousand rows, and placed the contents of the worksheet XML file in a preparation code variable. The DOM2 and String2 tests use the same code as the smaller tests, but they are processing a much larger file. In the large tests string processing showed to be 7.5x faster on Safari, and 32x faster on IE. Firefox was an outlier, in that it’s overall performance on the large document was worse than the Android device. Therefore, it’s roughly equal performance between XML parsing and string processing may not be applicable.

Now, not all performance increases are noteworthy. A 3,200% performance increase for a loop that executes a total of 10 simple additions, may not be of any real significance. In such a case, both will run so quickly that it is likely better to go with the more maintainable, extensible, stable, and conventional method. However, XLSX.js is not akin to such a simple loop. Spreadsheets can easily contain thousands of cells with data and formatting. Also, every instance of data or text formatting is associated with nodes describing said formatting. Now, this doesn’t necessarily mean a 1:1 relationship between the number of cells and the number of formatting-related nodes. However, every formatted cell will have association with at least one formatting-descriptive node regardless of whether the association is exclusive. Therefore, it is feasible (though somewhat unlikely) to say that a spreadsheet with 2,000 formatted cells could require the reading or writing of at least 6,000 nodes. In such a scenario, reduced processing time becomes a much larger consideration. Still, if processing a document in 3.1% of the time otherwise required does not seem significant, you may want to think of the capacity increase. For whatever amount of time you deem to be acceptable for a user to wait for their finished document/data, processing the XML as a string will allow you to work with a document 32x the size otherwise possible. Those 6,000 data or formatting nodes can become 192,000.

I realize that several factors influence the performance scaling of XLSX-file processing. However, my conclusion is that the benefits of string processing, in this instance, are quite clear. As a result, XLSX.js will stay with string processing until an alternative method with considerable performance is discovered. Additionally, the DOCX.js library that is about to release will convert over to string processing at some point. You are free to disagree with this decision, and fork the projects to pursue a DOM-based solution. Heck, the jsPerf even has some code to get a person started. But, I believe the case for string processing is a strong one with significant legitimacy.

With that, I’ll leave you with this musing…

In preparation for writing this post, I spent a good amount of time looking for arguments against string processing. Blog posts arguing for a particular JavaScript methodology are not rare, and I fully expected to see a good list of reasons why string processing should be abandoned in favor of DOM-based methods. Surprisingly, I could not find any. I found forums telling people not to use string processing, largely without reasoning, and posts describing the benefits of native methods (eg. getElementsByTagName) over jQuery. But, never could I find a good explanation for why string processing is wrong. And, I have a theory why: it’s not.

It is arguably less efficient, overall, in certain scenarios. For example, if one is processing a small document or XHR response, the maintainability, extensibility, and stability of the DOM-based methods would clearly win out. However, those scenarios are much less common these days. Ingo mentions the progression beyond string processing about five years ago, which was in the vicinity of the “JSON revolution”. More services started providing JSON, and then JSONP, causing people to migrate to the undeniable benefits. Performance for small bits of XML became less important as it became less common and browsers became faster. I would theorize that the average size of XML being worked with in JavaScript is increasing, as services continue to provide JSON data and HTML5/JavaScript continue to grow into a first class development platform. Developers will be more inclined to explore interactions with XML-based file formats, and these files may be quite large. Therefore, I would conclude that string processing of XML documents is likely to increase in the near to mid-future. While it won’t be applicable in every instance, large files with well-written specifications can be read and written via string processing with far greater speed than current DOM-based methods. It wasn’t the answer I expected, but I guess the moral of the story is to not assume that a methodology is best just because it is most prevalent.

If I missed something in my tests or conclusions, please feel free to comment!

Stephen

Tagged with: , ,
Posted in xlsx.js

xlsx.js for idiots (exporting edition)

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

This is my first post and I want to make a confession: Stephen Hardy is the brains of this operation.

Why am I even here you may ask (it’s a fair question)?  I bring two important attributes to this endeavor - ignorance and laziness.  At first those might not sound like the kind of skills one would want to bring to a technical blog and believe me no one is beating down my door to be a technical writer (although I am convinced that is only because I over use parentheses).  The thing is though, there is a good chance that I am just like you.  Yes, I just called you stupid and lazy.  NOTE – If this doesn’t sound like you, please feel free to  spend some time exploring the ads on the right side of this page (even this stupid and lazy guy needs to eat).

Whew, you know I am kinda glad those motivated brainiacs are gone. For the rest of us, I am going to show you the bare minimum you need to do (lazy) and try to frame it in the simplest terms (make way for ignorance!)   I may not show you the right way to do things but at the end of things it will be working.  I will leave it up to you to deal with “security” and “performance” and “doing things right” for your particular scenario.

With that caveat out of the way, here goes my first post on XLSX.js.

I am going to start with a screenshot of my folder structure at my web site.  Copy it:

we’ll start by looking at the HTML.  Open index.html:

 










Worksheet Name:

Nothing fancy here.

  • In the head section we have our script includes pointing to jquery, XLSX.js, the jszip.js files and our JavaScript file (mine.js).
  • In the body we have added an input field for the name of the worksheet (if you leave this blank or don’t include a worksheet name XLSX.js will add a generic name for you).
  • After that we have added a table with input fields for the content (but this isn’t necessary, you could have any static values in your table).
  • We cap off this stunning page with a button that will kick off all of the action.

Here is a screenshot:

All right, halfway there.

Lets take a look at our code. Open up the “mine.js” file and paste in the following code:

$('#save').on('click', function() {
var file = {
worksheets: [[]], // worksheets has one empty worksheet (array)
creator: 'John Smith', created: new Date('8/16/2012'),
lastModifiedBy: 'Larry Jones', modified: new Date(),
activeWorksheet: 0
}, w = file.worksheets[0]; // cache current worksheet
w.name = $('#WName').val();
$('#Worksheet1').find('tr').each(function() {
var r = w.push([]) - 1; // index of current row
$(this).find('input').each(function() { w[r].push(this.value); });
});

window.location = xlsx(file).href();
});

Save. Run it. Push it (the button).

XLSX.js should open up Excel (or your spreadsheet editor of choice which should be Excel IMHO) and show the following:

This didn’t work!

Are you using Internet Explorer? IE has a problem with using Data URIs as the download method for base64 files (which is what XLSX.js is trying to do here). To view this demo you will need to use Chrome, Firefox, Opera, etc.. To get this working with Internet Explorer you will need to have one of a few different Flash or Silverlight downloaded utilities. I will work on getting this working and explained in a separate post but didn’t want to confuse this walkthrough with that.

Stephen explains what is happening here much better than I can in this post (I am pretty sure there is witchcraft involved). But I will point out a few things that are probably obvious but might be missed if you are just cutting and pasting without examining the code.

  • The creator, created date, modified by, and last modified date are hard coded here in this function but obviously could be filled in via your HTML
  • ok, I guess there was just one thing I wanted to point out

Until next time

This was a pretty easy post and I truthfully don’t think I added a whole lot to what Stephen has previously explained. What this post did do is set the stage for my next post which will try to explain the importing of spreadsheet data. In my world the scenario above is the more common one and how it works is pretty straight forward and because of that was a natural to talk about first. Digging into the importing of data will require me to touch on more advanced concepts like cross-domain security and base-64 encoding and that will require me to learn what the hell those things are.

Tagged with: , ,
Posted in xlsx.js

New Microsoft Logo in CSS

As you’ve no doubt heard, or seen, last month Microsoft unveiled its first new logo in 25 years. Some love it, others not so much, but it did not take long for web developers to realize the benefits of its simplicity. Four days after the logo’s release, Mohamed Mansour posted a CSS version on Google Plus. News of this achievement was quick to spread, and developers were equally quick to offer their two cents on how it could be improved. When Kevin and I came across the post, we were no exception. By the time we arrived, Veljko Sekelj had already posted a very good revision, but we felt like it could be more accurate. And, therein lies the purpose of the post…

For reasons yet unknown, our contributions to Mohamed’s thread do not show for the general public. We posted an initial revision, with a greater focus on accuracy, but the thread tended toward an emphasis on reducing the code size. Which, to be clear, is a valid and useful endeavor. Veljko did some fine work, culminating in his final revision. However, it is my opinion that the usefulness of the exercise is more closely tied to the accuracy of the representation than the amount of code required to achieve it. Therefore, being that our posts are not publicly visible and our revision has a somewhat different focus, we thought we’d share it with you here.

For those that haven’t seen Microsoft’s new logo, or would like an image with which to compare the CSS implementations, here is a link to an image from Microsoft’s official release. Our CSS implementation can be found here, containing the following improvements on Veljko’s initial revision:

  •  Letter Spacing – the “f” and the “t” should share the same cross bar.
  • Vertical Alignment – “Microsoft” is too low relative to the squares
  • Font Weight – “Microsoft” is too bold
  • Left Padding – “Microsoft” is too close to the squares in the initial revision, and to far away in the later
  • Square Spacing – Slightly off in the first revision, and significantly off in the later
  • Square Colors
  • Text Color

We’re sure there is room for improvement in both accuracy and code optimization, so please feel free to share your thoughts!

Tagged with:
Posted in css

XLSX.js v1.1.0

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!

Tagged with: ,
Posted in xlsx.js

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

get it!

Start by downloading the script from github.

xlsx.js

Tagged with:
Posted in xlsx.js

Categories

disclaimer

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