Idris Rampurawala

Posted on Aug 31, 2020

Exporting Data To Excel and CSV in Angular

#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:

Export to Excel
Export to CSV

📝 NOTE
I have created a repository on GitHub on this implementation

idris-rampurawala / ng-data-export

Demonstration of an export service that exports data to excel, csv in Angular 10

Export to Excel

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) 😁

Installing dependencies

# 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.

1️⃣ 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 👇

<!-- 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>).

/* 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.

/* 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? 😆 What if we want to export more complex data? 🙄 Let's find out 👇

2️⃣ 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.

/* 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

/* 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? 😕 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 documentation and a sample implementation on github.

How do I style excel? 🧐

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 is a fork of xlsx that provides styling on top of it.

One more a very popular alternative to xlsx is ExcelJS. It has got styling included as well but provides lesser utilities in comparison to xlsx.

Export to CSV

Now let's move on to the second part of export i.e. CSV.

Don't worry 😟 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.

/* 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 🤓 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 package is required to save the file on the client's machine.

Well, that was pretty simple, isn't it? 🙌 You check out my GitHub repo 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 ❤️ or 🦄 if you like my post.

See ya! until my next post 😋

Top comments (13)

Chriz • Oct 20 '21

Thanks for the CSV code... You can also use the following code instead of depending on the FileSaver lib:

const blob = new Blob([csvContent], { type: 'text/csv'});
const a = document.createElement('a');
a.href = URL.createObjectURL(blob);
a.download = fileName;
a.click();

Sok Kim Thanh • Jun 17 '21

:) Thanks

Habib • Dec 13 '21 • Edited

can you share how to export html table to copy , pdf ,pagination feature?

hshahul • Jul 28 '21

How to change the sheetname in download CSV file?

Idris Rampurawala • Jul 29 '21

Hey, you can find the same in the export.service.ts

   // check out this line
    XLSX.utils.book_append_sheet(workbook, ws, 'Sheet1');

hshahul • Jul 29 '21

This one for export excel right? not for export CSV? Please check and let me sample example where I need to add this code in the following code if it is correct...

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 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); } }

I want to give different name for file and tab in CSV.

Idris Rampurawala • Jul 29 '21

You can pass the filename as mentioned in the function's exportToCsv dosctring. Also, CSV's won't have sheets in it ;)

TechNov • Sep 29 '22

Thanks, you save my day

wilburrito • Jun 19 '21

Hey, so how do I then make this excel sheet downloadable on the client's end? Basically, I want them to be able to click a button and have this excel sheet downloaded.