Menu
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.
963
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]);
Angular directive for exporting and downloading JSON as a CSV. Perform bower install ng-csv-download. Run in plunkr
var app = angular.module('testApp', ['tld.csvDownload']);
app.controller('Ctrl1', function($scope, $rootScope) {
$scope.data = {};
$scope.data.exportFilename = 'example.csv';
$scope.data.displayLabel = 'Download Example CSV';
$scope.data.myHeaderData = {
id: 'User ID',
name: 'User Name (Last, First)',
alt: 'Nickname'
};
$scope.data.myInputArray = [{
id: '0001',
name: 'Jetson, George'
}, {
id: '0002',
name: 'Jetson, Jane',
alt: 'Jane, his wife.'
}, {
id: '0003',
name: 'Jetson, Judith',
alt: 'Daughter Judy'
}, {
id: '0004',
name: 'Jetson, Elroy',
alt: 'Boy Elroy'
}, {
id: 'THX1138',
name: 'Rosie The Maid',
alt: 'Rosie'
}];
});<!DOCTYPE html>
<html ng-app="testApp">
<head>
<meta charset="utf-8" />
<title>Exporting JSON as a CSV</title>
<script>document.write('<base href="' + document.location + '" />');</script>
<link rel="stylesheet" href="style.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.3.15/angular.min.js"></script>
<script src="csv-download.min.js"></script>
<script src="app.js"></script>
</head>
<body>
<div>Using an <a href="https://github.com/pcimino/csv-download" target="_blank">Angular directive for exporting JSON data as a CSV download.</a></div>
<div ng-controller="Ctrl1">
<h2>All Attributes Set</h2>
<csv-download
filename="{{data.exportFilename}}"
label="{{data.displayLabel}}"
column-header="data.myHeaderData"
input-array="data.myInputArray">
</csv-download>
<hr />
<h2>Only Required Attribute Set</h2>
<h3>Optional Attributes Default</h3>
<csv-download
input-array="data.myInputArray">
</csv-download>
</div>
</body>
</html>You can use the XLSX library to convert JSON into XLS file and Download. Just create a service for your AngularJS application then call it as service method having below code.
I found this tutorial having JS and jQuery code but we can refer this code to use in AngularJS
Working Demo
Source link
Method
Include library
<script type="text/javascript" src="//unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
JavaScript Code
var createXLSLFormatObj = [];
/* XLS Head Columns */
var xlsHeader = ["EmployeeID", "Full Name"];
/* XLS Rows Data */
var xlsRows = [{
"EmployeeID": "EMP001",
"FullName": "Jolly"
},
{
"EmployeeID": "EMP002",
"FullName": "Macias"
},
{
"EmployeeID": "EMP003",
"FullName": "Lucian"
},
{
"EmployeeID": "EMP004",
"FullName": "Blaze"
},
{
"EmployeeID": "EMP005",
"FullName": "Blossom"
},
{
"EmployeeID": "EMP006",
"FullName": "Kerry"
},
{
"EmployeeID": "EMP007",
"FullName": "Adele"
},
{
"EmployeeID": "EMP008",
"FullName": "Freaky"
},
{
"EmployeeID": "EMP009",
"FullName": "Brooke"
},
{
"EmployeeID": "EMP010",
"FullName": "FreakyJolly.Com"
}
];
createXLSLFormatObj.push(xlsHeader);
$.each(xlsRows, function(index, value) {
var innerRowData = [];
$("tbody").append('<tr><td>' + value.EmployeeID + '</td><td>' + value.FullName + '</td></tr>');
$.each(value, function(ind, val) {
innerRowData.push(val);
});
createXLSLFormatObj.push(innerRowData);
});
/* File Name */
var filename = "FreakyJSON_To_XLS.xlsx";
/* Sheet Name */
var ws_name = "FreakySheet";
if (typeof console !== 'undefined') console.log(new Date());
var wb = XLSX.utils.book_new(),
ws = XLSX.utils.aoa_to_sheet(createXLSLFormatObj);
/* Add worksheet to workbook */
XLSX.utils.book_append_sheet(wb, ws, ws_name);
/* Write workbook and Download */
if (typeof console !== 'undefined') console.log(new Date());
XLSX.writeFile(wb, filename);
if (typeof console !== 'undefined') console.log(new Date());
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
