Skip to content

Export to Excel

Jump to bottom Edit New page
Ghislain B edited this page Oct 17, 2019 · 35 revisions

index

Description

You can Export to Excel, it will create an Excel file with the .xlsx default extension (you can also change it to be .xls). If you wish to export to CSV or other delimiter like Tab Delimited, you can refer to the other Wiki - Export to File.

Demo

Demo Page / Demo Component

Grid Menu (hamburger menu)

The Grid Menu already has the "Export to Excel" enabled by default, so you will see it automatically in your Grid Menu. You still have the options to show/hide from the Grid Menu if you wish

  • hideExportExcelCommand false by default, so it's optional

Grid Options

You can set certain options for the entire grid, for example if you set exportWithFormatter it will evaluate the Formatter (when exist) output to export each cell. The Grid Menu also has the "Export to Excel" enabled by default.

this.gridOptions = {
  enableExcelExport: true,
  // set at the grid option level, meaning all column will evaluate the Formatter (when it has a Formatter defined)
  excelExportOptions: {
    exportWithFormatter: true
  },
  gridMenu: {
    hideExportExcelCommand: false,        // false by default, so it's optional
  }
};

Column Definition and Options

Options

Inside the column definition there are couple of flags you can set in excelExportOptions in your Grid Options. You can also see the excelExportOption.interface in case the following list is not up to date.

  • addGroupIndentation flag, enabled by default will add indentation and collapsed/expanded symbols when using grouping feature
    • groupCollapsedSymbol will let you choose a different group collapsed symbol, it must be a unicode string (for example "\u25B9" or "\u25B7")
    • groupExpandedSymbol will let you choose a different group collapsed symbol, it must be a unicode string (for example "\u25BF" or "\u25BD")
  • excludeFromExport flag, which as it's name suggest will skip that column from the export
  • exportWithFormatter flag (same as Grid Options but this flag defined in the Column Definition has higher priority).
    • So basically, if exportWithFormatter is set to True in the excelExportOptions of the Grid Options, but is set to False in the Column Definition, then the result will be False and will not evaluate it's Formatter.
  • exportCustomFormatter will let you choose a different Formatter when exporting
    • For example, you might have formatter: Formatters.checkmark but you want to see a boolean translated value, in this case you would define an extra property of customFormatter: Formatters.translateBoolean.
  • set sanitizeDataExport to remove any HTML/Script code from being export. For example if your value is <span class="fa fa-check">True</span> will export True without any HTML (data is sanitized).
    • this flag can be used in the Grid Options (all columns) or in a Column Definition (per column).
  • sheetName allows you to change the Excel Sheet Name (defaults to "Sheet1")
  • customExcelHeader is a callback method that can be used to provide a custom Header Title to your Excel File

Behaviors

  • If you have a headerKey defined (for Translate (i18n)), it will use the translated value as the Header Title
this.columnDefinitions = [
  { id: 'id', name: 'ID', field: 'id', 
    excludeFromExport: true // skip the "id" column from the export
  },
  { id: 'title', name: 'Title', field: 'id', headerKey: 'TITLE',
    formatter: myCustomTitleFormatter,
    exportWithFormatter: false // this Formatter will not be evaluated 
  },
  { id: 'start', name: 'Start', field: 'start', 
    headerKey: 'START', 
    formatter: Formatters.dateIso // this formatter will be used for the export
  },
  { id: 'finish', name: 'Finish', field: 'start', 
    headerKey: 'FINISH', 
    formatter: Formatters.dateIso // this formatter will be used for the export
  },
  { id: 'completed', name: 'Completed', field: 'completed', headerKey: 'COMPLETED', 
    formatter: Formatters.checkmark,              // will display a checkmark icon in the UI
    customFormatter: Formatters.translateBoolean, // will export a translated value, e.g. in French, True would show as 'Vrai'
  }
];

this.gridOptions = {
  // set at the grid option level, meaning all column will evaluate the Formatter (when it has a Formatter defined)
  excelExportOptions: {
    exportWithFormatter: true
  }
};

What we can see from the example, is that it will use all Formatters (when exist) on this grid, except for the last column "Completed" since that column has explicitly defined exportWithFormatter: false

Provide a Custom Header Title

You can optionally add a custom header title (that will be shown on first row of the Excel file) through the customExcelHeader callback method. We use the library Excel-Builder to create the export, however note that this library is no longer supported (but still the best) and the documentation site no longer exist but you can find all info on Web Archive - Excel Builder

The example below shows a title which uses a merged cell from "B1" to "D1" with a red bold color (pay attention to the color code, you need to add an extra "FF" in front of an html color code).

Component

export class MyComponent implements OnInit {
  prepareGrid() {
    this.columnDefinitions = [];

    this.gridOptions = {
      excelExportOptions: {
        // optionally pass a custom header to the Excel Sheet
        // a lot of the info can be found on Web Archive of Excel-Builder
        // http://web.archive.org/web/20160907052007/http://excelbuilderjs.com/cookbook/fontsAndColors.html
        customExcelHeader: (workbook, sheet) => {
          const customTitle = this.translate.currentLang === 'fr' ? 'Titre qui est suffisament long pour être coupé' : 'My header that is long enough to wrap';
          const stylesheet = workbook.getStyleSheet();
          const aFormatDefn = {
            'font': { 'size': 12, 'fontName': 'Calibri', 'bold': true, color: 'FF0000FF' }, // every color starts with FF, then regular HTML color
            'alignment': { 'wrapText': true }
          };
          const formatterId = stylesheet.createFormat(aFormatDefn);
          sheet.setRowInstructions(0, { height: 30 }); // change height of row 0

          // excel cells start with A1 which is upper left corner
          sheet.mergeCells('B1', 'D1');
          const cols = [];
          // push empty data on A1
          cols.push({ value: '' });
          // push data in B1 cell with metadata formatter
          cols.push({ value: customTitle, metadata: { style: formatterId.id } });
          sheet.data.push(cols);
        }
      },
    }
  }

Export from a Button Click Event

You can use the export from the Grid Menu and/or you can simply create your own buttons to export.

View

<button class="btn btn-default btn-sm" (click)="exportToExcel()">
   Download to Excel
</button>
Component

The code below is just an example and it can be configured in many ways, see the excelExportOptions.

exportToFile() {
  this.excelExportService.exportToExcel({
    filename: 'myExport',
    format: FileType.xlsx
  });
}

UI Sample

The Export to File handles all characters well, from Latin, to Unicode, to even Unicorn emoji, it all works with all browsers (Chrome, Firefox, even IE11, I don't have access to other versions apart from that). Here's a demo image

Add a custom footer

Contents

Clone this wiki locally