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.
- 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.
- 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:
- It doesn't support IE 8 and 9:
- It opens a pop up window to open the
objectUrl
which people might have blocked - 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
Check If Two Div HTML Elements Intersect
React Display Line Breaks from Saved Textarea
How to Handle Multiple Submit Buttons in a Form Using Angular Js
Bootstrap Datepicker Set Default Date Not Working
How to Use a Function from Another File in React
How to Stop Page Getting Refresh on Validating Form
Finding Sequential Values in Js Array
Javascript to Hide Multiple Div
How to Select Id and Get a Value of It in Input Data in This Row
How to Expire a Cookie in 30 Minutes Using Jquery
How to Validate a Credit Card Number
Slick Slider - How to Keep the Active Pagination (Dot) Always Centered
Is There a Way That I Can Check If a Data Attribute Exists
Ask for Geolocation Permission Again If It Was Denied
Prevent CSS Tooltip from Going Out of Page/Window
Slick.Js: Get Current and Total Slides (Ie. 3/5)
How to Prevent Drag on a Child, But Allow Drag on the Parent