How to Export or Convert Json to Excel in Angularjs

How to export or convert JSON to Excel in AngularJS?

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]);

Export JSON Data to CSV File in AngularJs based on ui-grid's every row id

First convert JSON to comma separated CSV string then crate an anchor tag(a) set this CSV string as href fire a click, remove anchor tag.

function convertToCSV(array) {

    var str = '';

    for (var i = 0; i < array.length; i++) {
        var line = '';
        for (var index in array[i]) {
            if (line != '') line += ','

            line += array[i][index];
        }

        str += line + '\r\n';
    }
    return str;
}

function exportCSVFile(headers, items, fileTitle) {

    items.unshift(headers);

    var csv = convertToCSV(items);
    var exportedFilenmae = fileTitle + '.csv' || 'export.csv';

    var blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
    if (navigator.msSaveBlob) { // IE 10+
        navigator.msSaveBlob(blob, exportedFilenmae);
    } else {
        var link = document.createElement("a");
        if (link.download !== undefined) { // feature detection
            // Browsers that support HTML5 download attribute
            var url = URL.createObjectURL(blob);
            link.setAttribute("href", url);
            link.setAttribute("download", exportedFilenmae);
            link.style.visibility = 'hidden';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }
    }
}

/*Function to export*/
var funcExport = function (id) {

    var exportHeader = ['Licence', 'Condition'];

    var exportarray = [{ "Licence": "229973", "Condition": "Usage" }, { "Licence": "24141", "Condition": "Level" }];

    exportCSVFile(exportHeader , exportarray, 'download' );

}

This code was taken from here

how to convert json array object into csv format and export it in excel file in an ionic app

There is a similar question here..
Angular.js generate csv file from json data

If you're using ionic, I would assume angular, and therefore recommend this directive that turns arrays and objects into downloadable CSV files.

https://github.com/asafdav/ng-csv

If you don't want to read the accepted answers. Here's the sklinny.

var myapp = angular.module('myapp', ['ngSanitize', 'ngCsv'])

Add ng-csv directive to the wanted element, example:

<button type="button" ng-csv="getArray" filename="test.csv">Export</button>

In your controller, getarray is just your array (the json you'd get back from your request).

 $scope.getArray = [{a: 1, b:2}, {a:3, b:4}];

How to Export JSON to CSV or Excel - Angular 2

The fact that you are using Angular isn't all that important, though it does open up for some more libs.

You basically have two options.

1. Write your own json2csv converter, which isn't all that hard. You already have the JSON, which you can turn to JS objects, and then just iterate over every object and get the correct field for the current column.
2. You can use a lib like https://github.com/zemirco/json2csv which does it for you.

Also, this SO question probably answers your question How to convert JSON to CSV format and store in a variable

CSV is the basic format for Excel-like programs. Don't go messing with xls(x) unless you really have to. It will make your brain hurt.

AngularJS $http-post - convert binary to excel file and download

Just noticed you can't use it because of IE8/9 but I'll push submit anyway... maybe someone finds it useful

This can actually be done through the browser, using blob. Notice the responseType and the code in the success promise.

$http({
    url: 'your/webservice',
    method: "POST",
    data: json, //this is your json data string
    headers: {
       'Content-type': 'application/json'
    },
    responseType: 'arraybuffer'
}).success(function (data, status, headers, config) {
    var blob = new Blob([data], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});
    var objectUrl = URL.createObjectURL(blob);
    window.open(objectUrl);
}).error(function (data, status, headers, config) {
    //upload failed
});

There are some problems with it though like:

1. It doesn't support IE 8 and 9:
2. It opens a pop up window to open the objectUrl which people might have blocked
3. Generates weird filenames

It did work!

The server side code in PHP I tested this with looks like this. I'm sure you can set similar headers in Java:

$file = "file.xlsx";
header('Content-disposition: attachment; filename='.$file);
header('Content-Length: ' . filesize($file));
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');
echo json_encode(readfile($file));

Edit 20.04.2016

Browsers are making it harder to save data this way. One good option is to use filesaver.js. It provides a cross browser implementation for saveAs, and it should replace some of the code in the success promise above.

---

Related Topics