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.
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
<div
ng-controller="MainCtrl as vm">
<div
id="grid1" ui-grid="vm.gridOptions" class="grid">
<div class="grid-msg-overlay" ng-show="!vm.gridOptions.data.length">
<div
class="msg">
<div class="center">
<span class="muted">Select Spreadsheet File</span>
<br />
<input type="file"
accept=".xls,.xlsx,.ods" fileread opts="vm.gridOptions" multiple="false"
/>
</div>
</div>
</div>
</div>
</div>
|
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
.directive("fileread", [function
() {
return {
link: function ($scope,
$elm, $attrs) {
$elm.on('change', function (changeEvent) {
var reader = new FileReader();
reader.onload = function (evt) {
var data = evt.target.result;
};
reader.readAsBinaryString(changeEvent.target.files[0]);
});
}
}
}]);
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
.directive("fileread", [function
() {
return {
scope: {
opts:
'='
},
link: function ($scope,
$elm, $attrs) {
$elm.on('change', function (changeEvent) {
var reader = new FileReader();
reader.onload =function
(evt) {
$scope.$apply(function ()
{
var data = evt.target.result;
var workbook = XLSX.read(data,
{type: 'binary'});
var headerNames = XLSX.utils.sheet_to_json(
workbook.Sheets[workbook.SheetNames[0]],
{ header:
1 }
)[0];
var data = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);
$scope.opts.columnDefs
= [];
headerNames.forEach(function (h) {
$scope.opts.columnDefs.push({
field: h
});
});
$scope.opts.data
= data;
$elm.val(null);
});
};
reader.readAsBinaryString(changeEvent.target.files[0]);
});
}
}
}]);
|
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
{
A1: {
t: 's',
// Cell type. "s" for "String"
v: 'my raw
value'
w: 'my
formatted value'
},
A2: {
t: 's',
v: 'Another
value',
w: 'Another
value'
}
}
|
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.
