Exporting to excel with [email protected]

Question

I recently upgraded to [email protected] to use export to excel. The issue with this is it expects ExcelBuiler to be global, I am using webpack and typescript. Package https://github.com/stephenliberty/excel-builder.js gives build error so I used excel-builder-ts instead because of which I have to use latest [email protected]

Since it is not available globally, I just mimic code from UI-Grid to export to excel as follows and call it from gridMenuCustomItems

var jszip = require('jszip');
var ExcelBuilder = require('excel-builder-ts/ExcelBuilder.js');

public exportExcel(): void {
        var exportColumnHeaders = this.gridApi.grid.options.showHeader ? this.uiGridExporterService.getColumnHeaders(this.gridApi.grid, this.uiGridExporterConstants.ALL) : [];

        var workbook = ExcelBuilder.createWorkbook();
        var aName = this.gridApi.grid.options.exporterExcelSheetName ? this.gridApi.grid.options.exporterExcelSheetName : 'Sheet1';
        var sheet = workbook.createWorksheet({ name: aName });
        workbook.addWorksheet(sheet);
        var docDefinition = this.uiGridExporterService.prepareAsExcel(this.gridApi.grid, workbook, sheet);

        // The standard column width in Microsoft Excel 2000 is 8.43 characters based on fixed-width Courier font
        // Width of 10 in excel is 75 pixels
        var colWidths = [];
        var startDataIndex = this.gridApi.grid.treeBase ? this.gridApi.grid.treeBase.numberLevels : (this.gridApi.grid.enableRowSelection !== false ? 1 : 0);
        for (var i = startDataIndex; i < this.gridApi.grid.columns.length; i++) {
            colWidths.push({ width: (this.gridApi.grid.columns[i].drawnWidth / 75) * 10 });
        }
        sheet.setColumns(colWidths);

        var exportData = this.uiGridExporterService.getData(this.gridApi.grid, this.uiGridExporterConstants.ALL, this.uiGridExporterConstants.ALL, this.gridApi.grid.options.exporterFieldApplyFilters);

        var excelContent = this.uiGridExporterService.formatAsExcel(exportColumnHeaders, exportData, workbook, sheet, docDefinition);
        sheet.setData(sheet.data.concat(excelContent));

        let options = { type: 'blob', base64: true }

        ExcelBuilder.createFile(jszip, workbook, options).then((result) => {
            this.uiGridExporterService.downloadFile(
                this.gridApi.grid.options.exporterExcelFilename,
                result,
                this.gridApi.grid.options.exporterCsvColumnSeparator,
                this.gridApi.grid.options.exporterOlderExcelCompatibility);
        });
    }

And in columnDefs -

columnDefs: [
         gridMenuCustomItems: [
            {
                title: this.gettextCatalog.getString('Export all data as custom'),
                order: 210,
                action: (($event) => {
                    this.exportExcel();
                })
            }
        ]
    ]

But the issue with this is jszip is complaining "Error: No output type specified."

When I debug jszip code following is not able to extend options to opt to have type as blob.

 opts = utils.extend(options || {}, {
              streamFiles: false,
              compression: "STORE",
              compressionOptions : null,
              type: "",
              platform: "DOS",
              comment: null,
              mimeType: 'application/zip',
              encodeFileName: utf8.utf8encode
          });

when this executes opt.type is empty and jszip throws error Error: No output type specified.

I would really appreciate any suggestion.

Answer 1

A quick look at that excel-builder code

https://github.com/TeamworkGuy2/excel-builder-ts/blob/master/ExcelBuilder.js

I can see when it generates the zip it doesn't pass the type. So maybe this is why the old version of jszip is needed?

return zip.generateAsync({
  base64: (!options || options.base64 !== false)
});

The whole thing is a bit of a mess thought because the excel-builder project is abandoned. If the ui-grid project is maintained maybe you could raise an issue on their tracker but since it is angular1.x I assume probably it wont be that active.

I suggest trying the exact versions of jszip and excel-builder as they use. You can see their bower.json

"excel-builder-js": "excelbuilder#^2.0.2",
"jszip": "~2.6.1"

Failing that it is quite easy to implement your own excel export using some other excel library. On our team we did this using exceljs.