CODEWITHSUNDEEP
javascriptexcelangularjsxls
w3uiguru
w3uiguru

Reputation: 5895

Export to xls using angularjs

I am working on angular js app and I stuck in a situation in which I have to export data to Xls using angular js. I have searched a lot on the internet for export functionality or any library for angular js so I can do that or at least I can get the idea how to export. I don't have any code or work to show here.

I have a data which is an array of objects and I am iterating that on UI in a table. My backend is node.js and frontend are angular js.

My problem is if we have the data from the server and I am using on UI, how can I use the same data to export to Xls using angular js. I don't want to give a call again on the backend to extract the data and export that.

In the existing table, the user can select the checkbox (Any number of rows or all rows) to extract the data to Xls.

In node.js I have used node module whose name is: Excel and it is available on nodemodules site.

My data is like that:

"data": [
    {
        "Name": "ANC101",
        "Date": "10/02/2014",
        "Terms": ["samsung", "nokia": "apple"]
    },{
        "Name": "ABC102",
        "Date": "10/02/2014",
        "Terms": ["motrolla", "nokia": "iPhone"]
    }
]

I want the solution using angularjs or any angularjs library.

Upvotes: 58

Views: 205167

Answers (9)

Lovekush
Lovekush

Reputation: 1

We need a JSON file which we need to export in the controller of angularjs and we should be able to call from the HTML file. We will look at both. But before we start, we need to first add two files in our angular library. Those two files are json-export-excel.js and filesaver.js. Moreover, we need to include the dependency in the angular module. So the first two steps can be summarised as follows -

  1. Add json-export.js and filesaver.js in your angular library.

  2. Include the dependency of ngJsonExportExcel in your angular module.

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

Now that we have included the necessary files we can move on to the changes which need to be made in the HTML file and the controller. We assume that a json is being created on the controller either manually or by making a call to the backend.

HTML :

Current Page as Excel
All Pages as Excel

In the application I worked, I brought paginated results from the backend. Therefore, I had two options for exporting to excel. One for the current page and one for all data. Once the user selects an option, a call goes to the controller which prepares a json (list). Each object in the list forms a row in the excel.

Read more at - https://www.oodlestechnologies.com/blogs/Export-to-excel-using-AngularJS

Disclaimer: I work for oodles technologies

Upvotes: 0

snake_404
snake_404

Reputation: 121

I had this problem and I made a tool to export an HTML table to CSV file. The problem I had with FileSaver.js is that this tool grabs the table with html format, this is why some people can't open the file in excel or google. All you have to do is export the js file and then call the function. This is the github url https://github.com/snake404/tableToCSV if someone has the same problem.

Upvotes: 0

Mr.Ngo
Mr.Ngo

Reputation: 51

$scope.ExportExcel= function () { //function define in html tag                          

                        //export to excel file
                        var tab_text = '<table border="1px" style="font-size:20px" ">';
                        var textRange;
                        var j = 0;
                        var tab = document.getElementById('TableExcel'); // id of table
                        var lines = tab.rows.length;

                        // the first headline of the table
                        if (lines > 0) {
                            tab_text = tab_text + '<tr bgcolor="#DFDFDF">' + tab.rows[0].innerHTML + '</tr>';
                        }

                        // table data lines, loop starting from 1
                        for (j = 1 ; j < lines; j++) {
                            tab_text = tab_text + "<tr>" + tab.rows[j].innerHTML + "</tr>";                                
                        }

                        tab_text = tab_text + "</table>";
                        tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, "");          //remove if u want links in your table
                        tab_text = tab_text.replace(/<img[^>]*>/gi, "");             // remove if u want images in your table
                        tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

                        // console.log(tab_text); // aktivate so see the result (press F12 in browser)               
                        var fileName = 'report.xls'                            
                        var exceldata = new Blob([tab_text], { type: "application/vnd.ms-excel;charset=utf-8" }) 

                        if (window.navigator.msSaveBlob) { // IE 10+
                            window.navigator.msSaveOrOpenBlob(exceldata, fileName);
                            //$scope.DataNullEventDetails = true;
                        } else {
                            var link = document.createElement('a'); //create link download file
                            link.href = window.URL.createObjectURL(exceldata); // set url for link download
                            link.setAttribute('download', fileName); //set attribute for link created
                            document.body.appendChild(link);
                            link.click();
                            document.body.removeChild(link);
                        }

                    }

        //html of button

Upvotes: 0

Kursad Gulseven
Kursad Gulseven

Reputation: 2008

When I needed something alike, ng-csv and other solutions here didn't completely help. My data was in $scope and there were no tables showing it. So, I built a directive to export given data to Excel using Sheet.js (xslsx.js) and FileSaver.js.

Here is my solution packed.

For example, the data is:

$scope.jsonToExport = [
    {
      "col1data": "1",
      "col2data": "Fight Club",
      "col3data": "Brad Pitt"
    },
    {
      "col1data": "2",
      "col2data": "Matrix Series",
      "col3data": "Keanu Reeves"
    },
    {
      "col1data": "3",
      "col2data": "V for Vendetta",
      "col3data": "Hugo Weaving"
    }
];

I had to prepare data as array of arrays for my directive in my controller:

$scope.exportData = [];
// Headers:
$scope.exportData.push(["#", "Movie", "Actor"]);
// Data:
angular.forEach($scope.jsonToExport, function(value, key) {
  $scope.exportData.push([value.col1data, value.col2data, value.col3data]);
});

Finally, add directive to my template. It shows a button. (See the fiddle).

<div excel-export export-data="exportData" file-name="{{fileName}}"></div>

Upvotes: 16

TheSharpieOne
TheSharpieOne

Reputation: 25726

A cheap way to do this is to use Angular to generate a <table> and use FileSaver.js to output the table as an .xls file for the user to download. Excel will be able to open the HTML table as a spreadsheet.

<div id="exportable">
    <table width="100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Email</th>
                <th>DoB</th>
            </tr>
        </thead>
        <tbody>
            <tr ng-repeat="item in items">
                <td>{{item.name}}</td>
                <td>{{item.email}}</td>
                <td>{{item.dob | date:'MM/dd/yy'}}</td>
            </tr>
        </tbody>
    </table>
</div>

Export call:

var blob = new Blob([document.getElementById('exportable').innerHTML], {
        type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
    });
    saveAs(blob, "Report.xls");
};

Demo: http://jsfiddle.net/TheSharpieOne/XNVj3/1/

Updated demo with checkbox functionality and question's data. Demo: http://jsfiddle.net/TheSharpieOne/XNVj3/3/

Upvotes: 65

Ravikant Kushwaha
Ravikant Kushwaha

Reputation: 41

Try below with customised file name: 

$scope.exportData= function(){                  
    var uri = 'data:application/vnd.ms-excel;base64,'
          , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
          , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
          , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }

          var table = document.getElementById("searchResult");
          var filters = $('.ng-table-filters').remove();
          var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML};
          $('.ng-table-sort-header').after(filters) ;          
          var url = uri + base64(format(template, ctx));
          var a = document.createElement('a');
          a.href = url;
          a.download = 'Exported_Table.xls';
          a.click();        
};

Upvotes: 4

debovis
debovis

Reputation: 123

If you load your data into ng-grid, you can use the CSV export plugin. The plugin creates a button with the grid data as csv inside an href tag.

http://angular-ui.github.io/ng-grid/

https://github.com/angular-ui/ng-grid/blob/2.x/plugins/ng-grid-csv-export.js

Updating links as the library got renamed:

Github link: https://github.com/angular-ui/ui-grid

Library page: http://ui-grid.info/

Documentation on csv export : http://ui-grid.info/docs/#/tutorial/206_exporting_data

Upvotes: 12

Braulio
Braulio

Reputation: 1728

One starting point could be to use this directive (ng-csv) just download the file as csv and that's something excel can understand

http://ngmodules.org/modules/ng-csv

Maybe you can adapt this code (updated link):

http://jsfiddle.net/Sourabh_/5ups6z84/2/

Altough it seems XMLSS (it warns you before opening the file, if you choose to open the file it will open correctly)

var tableToExcel = (function() {

  var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
, base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }

  return function(table, name) {
    if (!table.nodeType) table = document.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()

Upvotes: 9

agershun
agershun

Reputation: 4107

You can try Alasql JavaScript library which can work together with XLSX.js library for easy export of Angular.js data. This is an example of controller with exportData() function:

function myCtrl($scope) {
  $scope.exportData = function () {
    alasql('SELECT * INTO XLSX("john.xlsx",{headers:true}) FROM ?',[$scope.items]);
  };

  $scope.items = [{
    name: "John Smith",
    email: "[email protected]",
    dob: "1985-10-10"
  }, {
    name: "Jane Smith",
    email: "[email protected]",
    dob: "1988-12-22"
  }];
}

See full HTML and JavaScript code for this example in jsFiddle.

UPDATED Another example with coloring cells.

Also you need to include two libraries:

Upvotes: 24

Related Questions