2

I am using filesaver.js to export my div (with multiple tables) to excel. I am able to export it as XLS using the code below.

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

However, I want to export my div to XLSX. Can anyone help? I have tried changing the MIME type to XLSX, but didn't help.

MartinTeeVarga
  • 10,478
  • 12
  • 61
  • 98
DeiJ Wrapp
  • 116
  • 1
  • 11

1 Answers1

1

Update W3C does not implemented .xlsx so the browsers too. But you can use alsql a js library which export data as a valid .xlsx here is the jsfiddle or run below code

function myCtrl($scope) {
    $scope.exportData = function () {
        alasql('SELECT * INTO XLSX("Report.xlsx",{headers:true}) FROM ?',[$scope.items]);
    };
    
    $scope.items = [{
        name: "John Smith",
        email: "j.smith@example.com",
        dob: "1985-10-10"
    }, {
        name: "Jane Smith",
        email: "jane.smith@example.com",
        dob: "1988-12-22"
    }, {
        name: "Jan Smith",
        email: "jan.smith@example.com",
        dob: "2010-01-02"
    }, {
        name: "Jake Smith",
        email: "jake.smith@exmaple.com",
        dob: "2009-03-21"
    }, {
        name: "Josh Smith",
        email: "josh@example.com",
        dob: "2011-12-12"
    }, {
        name: "Jessie Smith",
        email: "jess@example.com",
        dob: "2004-10-12"
    }];
};
<script src="https://cdn.jsdelivr.net/alasql/0.3.6/alasql.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.2/xlsx.core.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.23/angular.min.js"></script>
<div ng-app>
<div ng-controller="myCtrl">
    <button ng-click="exportData()">Export</button>
    <br />
    <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>
</div>
</div>
nivas
  • 3,138
  • 3
  • 16
  • 15
  • Didn't work. File Downloaded with xlsx, When opening shows error "Excel cannot open the file 'Test.xlsx' because the file format or file extension is not valid. Verify the file has not been corrupted and that the file extension matches the format of the file. and then nothing happens. – DeiJ Wrapp Feb 19 '17 at 06:12
  • You mean template and xls? – DeiJ Wrapp Feb 19 '17 at 06:20
  • wait i am still looking for possible solution in github issues, can you share your div html as well – nivas Feb 19 '17 at 06:21
  • Hey try using `type:"application/octet-stream"` – nivas Feb 19 '17 at 06:28
  • You can use the this jsfiddle example code http://jsfiddle.net/TheSharpieOne/XNVj3/1/ – DeiJ Wrapp Feb 19 '17 at 06:29
  • you saved me, i am suppose to create a jsfiddle, ok i will try it, meanwhile apply above type and try – nivas Feb 19 '17 at 06:33
  • Are you sure? Its same behavior for me when I download xlsx from JSFiddle link you provided. but when i open it excel shows same error and nothing happens! Just FYI I am using Excel 2016..if that makes any diff! will check on other version as well – DeiJ Wrapp Feb 19 '17 at 06:43
  • I used openoffice and i did not get any error, i will try other meanwhile just use other softwares and try – nivas Feb 19 '17 at 06:52
  • I will try other version of excel as well, since I can't force my clients to use non excel software I will not have choice for open office – DeiJ Wrapp Feb 19 '17 at 06:57
  • I found the problem lately, i will update once i solve the problem – nivas Feb 19 '17 at 07:00
  • Thanks a lot @nivas ..while I appericiate your efforts I am only looking to work with filesaver.js ..reason being as I said my div has multiple tables (with different columns in each table) ..alaSql does not exports div nicely ..you can test it as well..it will work amazing for single table or if multiple tables has same number of columns ..but it will fail if first table in div has 4 columns , second table has 10 and third one has 8 .. – DeiJ Wrapp Feb 19 '17 at 15:23
  • Alsql internally uses [see here](https://github.com/agershun/alasql#filesaver) and if you don't want to use any library then you have to use `.xls` not `.xlsx` and i don't see much difference b/w them – nivas Feb 19 '17 at 16:51
  • Yes I actually don't have issues migrating to alasql, but as I said alasql doesn't exports excel correctly if div has multiple tables with different number of columns (Table 1-> 2 columns, Table 2-> 10 columns, Table 3-> 4) – DeiJ Wrapp Feb 19 '17 at 22:20
  • Yes i understand, rather passing the `innerHTML` to filesaver you can pass the `ng-repeat data` to filesaver (google for it) so that you won't get any problems and data will be accurate no matter how many columns you have – nivas Feb 20 '17 at 04:22
  • Thanks ..did you mean to alaSql? – DeiJ Wrapp Feb 20 '17 at 05:09
  • No, i mean without using `alsql` just with filesaver itself, ref this question http://stackoverflow.com/questions/33495979/save-excel-file-using-filesaver-js?rq=1 – nivas Feb 20 '17 at 05:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/136118/discussion-between-deij-wrapp-and-nivas). – DeiJ Wrapp Feb 20 '17 at 05:46
  • Update: So I opened an issue with alaSQL about it and got a confirmation that multiple tables with different columns is not supported yet and I should try to create a new table and export that one. https://github.com/agershun/alasql/issues/831 – DeiJ Wrapp Feb 20 '17 at 05:57
  • Can you try the above method, it is just passing the `$scope data` to filesaver and it does not have anything to do with table's, all you need different `$scope` data to each table. i will create a jsfiddle and show you how to do it later if you can't get around with it – nivas Feb 20 '17 at 06:03
  • So we are back to basic question, I can try with scope data but what should be type to generate xlsx! – DeiJ Wrapp Feb 20 '17 at 16:35
  • No matter of type of data, if you want to save the result in `.xlsx`, you have to use external js library like `alsql` there is no choice at least for now – nivas Feb 21 '17 at 04:29