in Development

Easily Import Spreadsheets into UI-Grid

Your desktop spreadsheet tools need to handle all sorts of different proprietary file formats. Why should your web tools be different?

Unfortunately in the past it has been pretty much impossible to get JavaScript in the browser to be able to read XLS, ODS, and all the others. Thankfully, these days there’s libraries that will do the heavy lifting for us. In this post we’ll look at importing data from spreadsheets right into your UI-Grid with SheetJS.

Bonus: Want to export grid data as a spreadsheet file, right in the browser, no servers involved? [leadbox_link interest=”ui-grid” title=”Get the Guide” text=”Enter your email to get the bonus content link.” button=”Send it to Me!”]Enter your email and get a link to the bonus area[/leadbox_link]. It has a how-to guide that will walk you through it.

SheetJS: In-Browser Spreadsheets

SheetJS is a github organization that provides several node.js and browser-based libraries for reading and writing spreadsheets, in a lot of different formats.  Just to name a few, it can handle:

  • XLS
  • XLSX
  • CSV / TSV
  • ODS

For me this covers all my use cases. Our office is split into people using current MS products, people with Open Office, and tools that export old XLS files.

The specific libraryies we’ll be using is js-xlsx, which will handle Excel files, and the add-on ods.js that will handle Open Office files.

Example App

We’re going to show off a little example that will fill out a UI-Grid instance from a spreadsheet file the user selects.

First we need to make a way to select the file. We’ll do this by transcluding content into the grid.

Want to read more about UI-Grid and transclusion. Check out this post: UI-Grid: The Easiest Customization You’ll Ever Write

UI-Grid will bring all the content inside <div ui-grid></div> into itself, at its own scope. We can use this to display a “Select File” message when there’s no rows.  Inside this message we have an <input type="file"> that accepts spreadsheet files. It also has a fileread attribute. That’s our directive that will do the heavy lifting.

The directive needs to do three things:

  • Listen for changes to the file input and handle selected files
  • Read the file contents in and process them with js-xls
  • Turn the output from js-xls into column definitions and data rows for UI-Grid

We can use the change event handler to be notified when the user selects a file. When that happens we will create a new FileReader  that will read the file in as a binary string, and we’ll process the data in the onload handler. The directive initially looks like this:

Now we have data in our onload handler which has the contents of the spreadsheet file. We next need to process data with js-xlsx.

Here we’ve added a scope property to the directive to allow us to pass in the grid’s options object. We’ll use this to get our column defs and rows into the grid.

XLSX.read(data) returns a workbook object to us. This contains two properties, one is an array called SheetNames that has the list of sheets in the workbook. The other property is Sheets which is a dictionary of sheet names whose value is their Sheet object.

The Sheet object, then,  is just a dictionary of of cell positions whose value is the cell information (value, formatting, etc). It kinda looks like:

This would be a pain to manually process. Luckily there’s a utility method in XLSX.utils called sheet_to_json. We can pass it the sheet and get the data back as a JSON object. It will even use the first row as the column names for the keys. Yay!

That’s our row data. We could stop here if we didn’t care about the column defs, but if we do we can use XLSX.utils.sheet_to_json again with the argument { header: 1 }. This will return a simple array of arrays where each element of the outer array is a row and each element of the inner arrays is the set of columns for that row. We can use the first row as our column names and iterate over each of them, creating a column def for each one.

At last, here’s our working demo!

Hopefully you have a spreadsheet sitting around. If not you can use this one. Save that link to your desktop/device and insert it into the demo above.

Summary

That’s about the long and short of it! You may have problems with specific file types; check out the SheetJS demo pages to see if your files work there, and do some debugging in your developer console to see what’s going on; js-xlsx is pretty good about giving useful exceptions.

Other things you could do with this:

  • Add a drag-and-drop target inside the grid.
  • Allow uploading multiple files and combine them all into one grid
  • Download a spreadsheet from UI-Grid directly in the browser (Let me know if you are interested in this one in the comments below and I’ll do another post).

Good luck! If you have any questions, comments, or suggestions please feel free to leave a comment. I try to respond to all of them as soon as I can.




Want to Export Files?

Enter your email to get a bonus how-to guide that will walk you through exporting XLSX files from UI-Grid.