Want to export XLSX files straight from your browser, no server involved? Step right in, here we go!
If you didn’t read the original post on importing spreadsheets head there first.
This guide will be broken up into three steps.
- Grab exportable data from the grid. By “exportable” we mean that filters and sorting are taken into account.
- Convert the data into a spreadsheet object that JS-XLS can use.
- Prompt the browser to download the file.
Get Exportable Data
We will use the grid’s API to get access to the grid instance, and we’ll use the the uiGridExporterService to get the visible columns and rows.
|
1 2 3 4 5 6 7 8 9 |
app.controller('MainCtrl', function (uiGridExporterService, uiGridExporterConstants) { var gridOptions = { onRegisterApi: function (api) { gridApi = api; } }; var rows = uiGridExporterService.getData(gridApi.grid, uiGridExporterConstants.VISIBLE, uiGridExporterConstants.VISIBLE); }); |
Note that we pass gridApi.grid to getData() as the grid to fetch data from, and the second and third arguments tell the method to return only visible data for both rows and columns. The format is an array of arrays where each inner array element represents the cell at that row+col combo, and has a value property containing the display value of the cell.
To get the columns names we can use gridApi.grid.columns and filter out the not-visible ones.
Convert Data into JS-XLS Sheet
Now that we have our rows, we can turn them and the column into a sheet.
|
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 |
var sheet = {}; gridApi.grid.columns.forEach(function (col, i) { if (col.visible) { var loc = XLSX.utils.encode_cell({r: 0, c: i}) sheet[loc] = { v: col.displayName, t: 's' }; } }); var endLoc; rows.forEach(function (row, ri) { ri +=1; gridApi.grid.columns.forEach(function (col, ci) { var loc = XLSX.utils.encode_cell({r: ri, c: ci}); sheet[loc] = { v: row[ci].value, t: 's' }; endLoc = loc; }); }); sheet['!ref'] = XLSX.utils.encode_range({ s: 'A1', e: endLoc }); |
First we iterate through the columns and fill the first sheet row with the column names as a header. We can use a combination of the row index and column index (both zero-based) and pass that to XLSX.utils.encode_cell() to get an alphanumeric cell address. So [0, 0] becomes “A1” and [5, 5] becomes “F6”, etc. The object for the cell has a v property representing the value, and a t property representing the type. We’re using ‘s’ for string but you can use column or data types to determine the spreadsheet cell type.
Then on the rows we iterate through each (making sure to increment the row index by one so that we don’t overwrite the header row), and then we iterate through the columns. Once again we use encode_cell for the address. We also need to record the final cell location so js-xls can properly write the workbook. This is done with the encode_range utility function; s is the start of the range (A1 in our case) and e i the ending address.
Serving Files From the Browser
Thankfully there’s a nice little library called FileSaver.js that will handle serving the file. It does have some size limits so keep that in mind, though they’re fairly large.
|
1 2 3 4 5 6 7 8 9 10 11 |
var wopts = { bookType: 'xlsx', bookSST: false, type: 'binary' }; var wbout = XLSX.write(workbook, wopts); saveAs(new Blob([s2ab(wbout)], {type: ""}), "test.xlsx"); function s2ab(s) { var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF; return buf; } |
The wopts object just has some properties for generating the spreadsheet. We’re going to use the XLSX format, no string table, and it will binary.
We’ll then turn out workbook string into a binary ArrayBuffer and FileSaver’s saveAs function will prompt the user to download it. The final argument is the file name, which you could make dynamic if you wanted.
Here’s a look at our final result:
That’s it! Hope you found this helpful. Note that I did check to see if cell formatting was possible so that I could bold the header row but they might still be working on it.
If you have any questions don’t hesitate to hit me up!
