DEV Community

Idris Rampurawala
Idris Rampurawala

Posted on

Exporting Data To Excel and CSV in Angular

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:

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

GitHub logo 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


Enter fullscreen mode Exit fullscreen mode

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>


Enter fullscreen mode Exit fullscreen mode

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

  }
  ...
}


Enter fullscreen mode Exit fullscreen mode

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

  ...

}


Enter fullscreen mode Exit fullscreen mode

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

...


Enter fullscreen mode Exit fullscreen mode

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

...


Enter fullscreen mode Exit fullscreen mode
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);
  }

...


Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
chriz_ profile image
Chriz

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

Collapse
 
sokkimthanh profile image
Sok Kim Thanh

:) Thanks

Collapse
 
habibcseju profile image
Habib • Edited

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

Collapse
 
hshahul profile image
hshahul

How to change the sheetname in download CSV file?

Collapse
 
idrisrampurawala profile image
Idris Rampurawala

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

   // check out this line
    XLSX.utils.book_append_sheet(workbook, ws, 'Sheet1');
Enter fullscreen mode Exit fullscreen mode
Collapse
 
hshahul profile image
hshahul

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.

Thread Thread
 
idrisrampurawala profile image
Idris Rampurawala

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

Collapse
 
technov profile image
TechNov

Thanks, you save my day

Collapse
 
wilburrito profile image
wilburrito

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.

Collapse
 
idrisrampurawala profile image
Idris Rampurawala

Hey, you can check out my GitHub repo of this implementation to accomplish your use case â˜ș

Collapse
 
kotakishore81 profile image
kishore kota

how to protected the sheet in angular excel export

  1. specific columns disable to edit
  2. don't allow to add new row -
  3. don't delete existing records
Collapse
 
100hpfvr profile image
Mateus com overclock de café

i'm using angular 13, this type error occurs. Any idea to how resolve? :/

Collapse
 
localghost profile image
localghost

what if when exporting large sets of data, it doesnt export everything just the header + 100 rows