How to export or convert JSON to Excel in AngularJS?

Question

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.

Solution 1

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

Solution 2

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

Solution 3

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>