Exporting Data To Excel and CSV in Angular
Tags: angular, webdev, javascript, beginners
Published: 2020-08-31 13:41:42

---
title: Exporting Data To Excel and CSV in Angular
published: true
description: While working on a web application, there are various instances where we would allow our users to download the data into their specific formats. One such requirement is to allow them to export the data in a spreadsheet (excel) or a CSV file. Let's find out how we can create such functionality in Angular.
tags: angular, webdev, javascript, beginners
---

While working on a web application, there are various instances where we would allow our users to download the data into their specific formats. One such requirement is to allow them to export the data in a spreadsheet (excel) or a CSV file.

This is a very common use case and hence I thought to create a step by step guide to accomplish this easily. We would be discussing the export into 2 main areas:
- <a href="#export-to-excel">Export to Excel</a>
- <a href="#export-to-csv">Export to CSV</a>


**:memo: NOTE**
I have created a repository on GitHub on this implementation
{% github idris-rampurawala/ng-data-export no-readme %}

----

### Export to Excel <a name="export-to-excel"></a>
The ability to export the data to excel not only gives a powerful feature for the users but also the ability to create an array of other related features to help our users better understand the data. So how do we start? Well, as you'd expect, we have an npm package to deal with it - [xlsx (also known as sheetjs) ](https://github.com/SheetJS/sheetjs) :grin:

#### Installing dependencies
```sh
# installing xlsx package
$ npm install xlsx
# installing file-saver - a solution to saving files on the client-side
$ npm install file-saver
```

#### Creating an export service
One way of creating common functionalities in Angular is by creating a service for it. Hence, we create an export service which will have functions to export all types of information (excel and CSV for this post).

#### Using xlsx
`xlsx` provides a very rich set of utilities to create or parse spreadsheets. For the sake of simplicity, we will concentrate on a couple of the utilities here.

##### :one: Exporting an HTML table
If we want to export an `HTML table` to excel, then it is pretty easy as `xlsx` provides utility for it. Consider if we have a table :point_down:
```html
<!-- app.component.html -->
<table class="table table-sm" #userTable> <!-- we will make use of this angular var as element reference -->
  <thead class="thead-dark">
    <tr>
      <th scope="col">#</th>
      ...
    </tr>
  </thead>
  <tbody>
    <tr *ngFor="let user of users">
      <td scope="row">{{ user.id }}</td>
      ...
    </tr>
    <tr>
  </tbody>
</table>
```
Now, we can just create a `service` function to take this `HTML element reference` and generate the excel from it (using `<thead>` and `<tbody>`).
```ts
/* export.service.ts */
import { Injectable, ElementRef } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';

const EXCEL_EXTENSION = '.xlsx';

@Injectable()
export class ExportService {
  constructor() { }

  /**
   * Creates excel from the table element reference.
   *
   * @param element DOM table element reference.
   * @param fileName filename to save as.
   */
  public exportTableElmToExcel(element: ElementRef, fileName: string): void {
    const ws: XLSX.WorkSheet = XLSX.utils.table_to_sheet(element.nativeElement);
    // generate workbook and add the worksheet
    const workbook: XLSX.WorkBook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, ws, 'Sheet1');
    // save to file
    XLSX.writeFile(workbook, `${fileName}${EXCEL_EXTENSION}`);

  }
  ...
}
```
And in `component.ts` we just create a handler for the `export button` to make an attempt to save the file as excel on the client machine.
```ts
/* app.component.ts */
import { Component, OnInit, ViewChild, ElementRef } from '@angular/core';
import { ExcelJson } from './interfaces/excel-json.interface';
import { ExportService } from './services/export.service';
...

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.scss']
})
export class AppComponent implements OnInit {
  ...
  /* the table reference */
  @ViewChild('userTable') userTable: ElementRef;
  ...

  constructor(
    private exportService: ExportService
  ) { }

  ngOnInit(): void {
    ...
  }

  /**
   * Function prepares data to pass to export service to create excel from Table DOM reference
   *
   */
  exportElmToExcel(): void {
    this.exportService.exportTableElmToExcel(this.userTable, 'user_data');
  }

  ...

}
```
That was pretty easy, isn't it? :satisfied: What if we want to export more complex data? :roll_eyes: Let's find out :point_down:

##### :two: Exporting more complex data
`xlsx` provides various other utilities to customize the data in excel (using excel column name identifies `A, B, C..`). For example, I had created a function to export the whole dashboard data to excel in one of my projects. Let's create a function in `service` for the same.
```ts
/* export.service.ts */
...

  /**
   * Creates XLSX option from the Json data. Use this to customize the sheet by adding arbitrary rows and columns.
   *
   * @param json Json data to create xlsx.
   * @param fileName filename to save as.
   */
  public exportJsonToExcel(json: ExcelJson[], fileName: string): void {
    // inserting first blank row
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(
      json[0].data,
      this.getOptions(json[0])
    );

    for (let i = 1, length = json.length; i < length; i++) {
      // adding a dummy row for separation
      XLSX.utils.sheet_add_json(
        worksheet,
        [{}],
        this.getOptions(
          {
            data: [],
            skipHeader: true
          }, -1)
      );
      XLSX.utils.sheet_add_json(
        worksheet,
        json[i].data,
        this.getOptions(json[i], -1)
      );
    }
    const workbook: XLSX.WorkBook = { Sheets: { Sheet1: worksheet }, SheetNames: ['Sheet1'] };
    // save to file
    XLSX.writeFile(workbook, `${fileName}${EXCEL_EXTENSION}`);
  }

  /**
   * Creates the XLSX option from the data.
   *
   * @param json Json data to create xlsx.
   * @param origin XLSX option origin.
   * @returns options XLSX options.
   */
  private getOptions(json: ExcelJson, origin?: number): any {
    // adding actual data
    const options = {
      skipHeader: true,
      origin: -1,
      header: []
    };
    options.skipHeader = json.skipHeader ? json.skipHeader : false;
    if (!options.skipHeader && json.header && json.header.length) {
      options.header = json.header;
    }
    if (origin) {
      options.origin = origin ? origin : -1;
    }
    return options;
  }

...
```
And in `component.ts`, we create the data in `xlsx` required format to pass to this service function
```ts
/* app.component.ts */
...

/**
   * Function prepares data to pass to export service to create excel from Json
   *
   */
  exportToExcel(): void {

    const edata: Array<ExcelJson> = [];
    const udt: ExcelJson = {
      data: [
        { A: 'User Data' }, // title
        { A: '#', B: 'First Name', C: 'Last Name', D: 'Handle' }, // table header
      ],
      skipHeader: true
    };
    this.users.forEach(user => {
      udt.data.push({
        A: user.id,
        B: user.firstName,
        C: user.lastName,
        D: user.handle
      });
    });
    edata.push(udt);

    // adding more data just to show "how we can keep on adding more data"
    const bd = {
      data: [
        // chart title
        { A: 'Some more data', B: '' },
        { A: '#', B: 'First Name', C: 'Last Name', D: 'Handle' }, // table header
      ],
      skipHeader: true
    };
    this.users.forEach(user => {
      bd.data.push({
        A: String(user.id),
        B: user.firstName,
        C: user.lastName,
        D: user.handle
      });
    });
    edata.push(bd);
    this.exportService.exportJsonToExcel(edata, 'user_data_customized');
  }

...
```
##### Explanation
Confused? :confused: Let me explain what we just did there.
- `xlsx` (or spreadsheets) have a `workbook` (that's an actual file) and in that, we can have multiple `sheets` added.
- `xlsx` provides a utility function `sheet_add_json()` to convert an array of objects to excel data with additional xlsx options. Hence, we just created a wrapper around it our `service` by which we can pass multiple objects with different xlsx options. This way our export service handles the complexity and we are only required to create an array of objects to pass to it.
- `xlsx` expects the array of objects in the form of `{cell: value }` and hence `{A: 'value'}` means that we want to put this `value` in the cell (column) `A` of the excel.
- `skipHeader` is to skip the auto-generated header from the objects being passed to the function `sheet_add_json()`
- `origin: -1` is to append data to the bottom of worksheet starting on the first column
- Also, `ExcelJson` is a custom interface (that I have created) to define the type of data that service function expects. It represents a valid object data for `xlsx`.

For more information, please read the [xlsx](https://github.com/SheetJS/sheetjs) documentation and [a sample implementation on github](https://github.com/idris-rampurawala/ng-data-export).

#### How do I style excel? :monocle_face:
`xlsx` does not provide styling in its open-source version. You can opt for a `pro version` for styling and dedicated support.

Alternatively, [xlsx-style](https://github.com/protobi/js-xlsx) is a fork of `xlsx` that provides styling on top of it.

One more a very popular alternative to `xlsx` is [ExcelJS](https://github.com/exceljs/exceljs). It has got styling included as well but provides lesser utilities in comparison to `xlsx`.

---

### Export to CSV <a name="export-to-csv"></a>
Now let's move on to the second part of export i.e. CSV. 

Don't worry :worried: it's pretty easy. We just need to add a function to our `export service` which accepts an array of objects along with a column header to create a CSV for it.
```ts
/* export.service.ts */
...
 /**
   * Saves the file on the client's machine via FileSaver library.
   *
   * @param buffer The data that need to be saved.
   * @param fileName File name to save as.
   * @param fileType File type to save as.
   */
  private saveAsFile(buffer: any, fileName: string, fileType: string): void {
    const data: Blob = new Blob([buffer], { type: fileType });
    FileSaver.saveAs(data, fileName);
  }

  /**
   * Creates an array of data to CSV. It will automatically generate a title row based on object keys.
   *
   * @param rows array of data to be converted to CSV.
   * @param fileName filename to save as.
   * @param columns array of object properties to convert to CSV. If skipped, then all object properties will be used for CSV.
   */
  public exportToCsv(rows: object[], fileName: string, columns?: string[]): string {
    if (!rows || !rows.length) {
      return;
    }
    const separator = ',';
    const keys = Object.keys(rows[0]).filter(k => {
      if (columns?.length) {
        return columns.includes(k);
      } else {
        return true;
      }
    });
    const csvContent =
      keys.join(separator) +
      '\n' +
      rows.map(row => {
        return keys.map(k => {
          let cell = row[k] === null || row[k] === undefined ? '' : row[k];
          cell = cell instanceof Date
            ? cell.toLocaleString()
            : cell.toString().replace(/"/g, '""');
          if (cell.search(/("|,|\n)/g) >= 0) {
            cell = `"${cell}"`;
          }
          return cell;
        }).join(separator);
      }).join('\n');
    this.saveAsFile(csvContent, `${fileName}${CSV_EXTENSION}`, CSV_TYPE);
  }

...
```
The code is pretty much self-explanatory :nerd_face: where we check if any of the column's data present in the data passed, and generates a CSV from it. We can always change the delimiter from `,` to any other based on our requirement. [file-saver](https://www.npmjs.com/package/file-saver) package is required to save the file on the client's machine.

---

Well, that was pretty simple, isn't it? :raised_hands: You check out my [GitHub repo](https://github.com/idris-rampurawala/ng-data-export) for a complete implementation of this post.

If you find this helpful or have any suggestions, feel free to comment. Also, do not forget to hit :heart: or :unicorn: if you like my post.

See ya! until my next post :yum: