mesosteros mesosteros - 2 months ago 162
AngularJS Question

How to export or convert JSON to Excel in AngularJS?

I'm extracting an array with 4 objects and each object has an array inside, from my kendo charts datasource, on my Angular project.

The data inside each sub-object varies in size, but it always includes a timestamp, and 1-5 value fields.

I need to export this array to an Excel file (.xls or .xlsx NOT CSV).

So far I managed to download the JSON as a file on its own (both .json and unformatted .xls).

I'd like for each object to be a book and in that book to have a formatting that has the timestamp in the first column, value 1 in another, and so on. The header for the columns should be timestamp, value1 name, etc (I'm translating these on the ui according to user preferences).

How can I build this type of formatted .xls file using angular? I don't know a particular good library for this, that is clear on how to use it in Angular.

Answer

Following Nathan Beck's link sugestion, I used AlaSQL. I'm getting correctly formatted columns, just need to adapt my array to have multiple worksheets.

The way we integrate alaSQL into our Angular project is by including the alasql.min.js and xlsx.core.min.js.

Then we call the alasql method in our function

$scope.export = function(){
var arrayToExport = [{id:1, name:"gas"},...];
  alasql('SELECT * INTO XLSX("your_filename.xlsx",{headers:true}) FROM ?', arrayToExport);
}

EDIT: Solved the multiple worksheets issues as well. Keep in mind that when using the multiple worksheet method, you have to remove the asterisk and replace the headers: true object in the query with a question mark, passing the options in a separate array. So:

var arrayToExport1 = [{id:1, name:"gas"},...];
var arrayToExport2 = [{id:1, name:"solid"},...];
var arrayToExport3 = [{id:1, name:"liquid"},...];
var finalArray = arrayToExport1.concat(arrayToExport2, arrayToExport3);

var opts = [{sheetid: "gas", headers: true},{sheetid: "solid", headers: true},{sheetid: "liquid", headers: true}];
alasql('SELECT INTO XLSX("your_filename.xlsx",?) FROM ?', [opts, finalArray]);
Comments