Excel not properly generate in angularjs

Question

i am using angularjs and generate excel-sheet using blob with the help of filesaver.js i am getting my properly but excel will not open correctly in Micrsoft Excel it's working but i am not getting the cells it's shows black white page but content is there .help how to solve

here i attached my fiddle:https://jsfiddle.net/x30v0bym/3/

score 0 · Answer 1 · answered Jun 17 '17 at 05:46

You can use the following directive,

app
  .directive('excelExport',
    function() {
      return {
        restrict: 'A',
        scope: {
          fileName: "@",
          data: "&exportData"
        },
        replace: true,
        template: '<button class="btn btn-primary btn-ef btn-ef-3 btn-ef-3c mb-10" ng-click="download()">Export to Excel <i class="fa fa-download"></i></button>',
        link: function(scope, element) {

          scope.download = function() {

            function datenum(v, date1904) {
              if (date1904) v += 1462;
              var epoch = Date.parse(v);
              return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
            };

            function getSheet(data, opts) {
              var ws = {};
              var range = {
                s: {
                  c: 10000000,
                  r: 10000000
                },
                e: {
                  c: 0,
                  r: 0
                }
              };
              for (var R = 0; R != data.length; ++R) {
                for (var C = 0; C != data[R].length; ++C) {
                  if (range.s.r > R) range.s.r = R;
                  if (range.s.c > C) range.s.c = C;
                  if (range.e.r < R) range.e.r = R;
                  if (range.e.c < C) range.e.c = C;
                  var cell = {
                    v: data[R][C]
                  };
                  if (cell.v == null) continue;
                  var cell_ref = XLSX.utils.encode_cell({
                    c: C,
                    r: R
                  });

                  if (typeof cell.v === 'number') cell.t = 'n';
                  else if (typeof cell.v === 'boolean') cell.t = 'b';
                  else if (cell.v instanceof Date) {
                    cell.t = 'n';
                    cell.z = XLSX.SSF._table[14];
                    cell.v = datenum(cell.v);
                  } else cell.t = 's';

                  ws[cell_ref] = cell;
                }
              }
              if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
              return ws;
            };

            function Workbook() {
              if (!(this instanceof Workbook)) return new Workbook();
              this.SheetNames = [];
              this.Sheets = {};
            }

            var wb = new Workbook(),
              ws = getSheet(scope.data());
            /* add worksheet to workbook */
            wb.SheetNames.push(scope.fileName);
            wb.Sheets[scope.fileName] = ws;
            var wbout = XLSX.write(wb, {
              bookType: 'xlsx',
              bookSST: true,
              type: 'binary'
            });

            function s2ab(s) {
              var buf = new ArrayBuffer(s.length);
              var view = new Uint8Array(buf);
              for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
              return buf;
            }

            saveAs(new Blob([s2ab(wbout)], {
              type: "application/octet-stream"
            }), scope.fileName + '.xlsx');

          };

        }
      };
    }
  );

DEMO

why this much of code @Sajeetharan can you remodify my fiddle — jose, Jun 17 '17 at 05:49
@jose your one does not support .xlsx format, you need to modify a lot — Sajeetharan, Jun 17 '17 at 05:50
i need get html table as excel sheet kindly check once my fiddle — jose, Jun 17 '17 at 05:50

Piotr Lewandowski · Answer 2 · 2017-06-17T10:08:57.783

Your fiddle contains everything needed except one important thing. You're not generating content that excel can understood.

Your problem is here:

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

More specifically, here:

[document.getElementById('exportable').innerHTML]

This returns HTML, which is not Excel file format. There is no auto-magic that converts HTML into Excel.

This is usually done on server side, not AngularJS. But if you're forced, you'll need another library to handle conversion between your data and Excel. One of popular library is ExcelJS.

Simpler solution - generate CSV

I would propose to skip Excel and generate CSV, which is the simplest possible format to generate - understood by Excel. You only have to modify your export function:

$scope.exportData = function () {
    var blob = new Blob([convertToCsv($scope.items)], {
        type: "text/csv"
    });
    saveAs(blob, "Report.csv");

    function convertToCsv(items) {
        var headers = "Name; Email; DoB \n";

        return headers + items.map(function(item) {
          return item.name + ";" + item.email + ";" + item.dob;
        }).join("\n");
    }
};

Function convertToCsv organize your items into format:

Name; Email; DoB 
John Smith;j.smith@example.com;1985-10-10
Jane Smith;jane.smith@example.com;1988-12-22
Jan Smith;jan.smith@example.com;2010-01-02
Jake Smith;jake.smith@exmaple.com;2009-03-21
Josh Smith;josh@example.com;2011-12-12
Jessie Smith;jess@example.com;2004-10-12

Your fiddle updated: DEMO

Downloaded file Reports.csv can be opened and edited in Excel.

Notes

You won't be able to use function specific to excel as setting column width, colors etc.
My Solution with CSV is not quite what you're asked, but I believe it is still good enough
Related question: how to generate Excel through Javascript which points out other solutions

function myCtrl($scope) {
  $scope.exportData = function() {
    var blob = new Blob([convertToCsv($scope.items)], {
      type: "text/csv"
    });
    saveAs(blob, "Report.csv");

    function convertToCsv(items) {
      var headers = "Name; Email; DoB \n";

      return headers + items.map(function(item) {
        return item.name + ";" + item.email + ";" + item.dob;
      }).join("\n");
    }
  };

  $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://ajax.googleapis.com/ajax/libs/angularjs/1.2.22/angular.min.js"></script>
<script src="https://rawgithub.com/eligrey/FileSaver.js/master/FileSaver.js" type="text/javascript"></script>
<body 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>
</body>

Excel not properly generate in angularjs

2 Answers2

Simpler solution - generate CSV

Notes

Linked