How to Export to Excel in Angular

In this tutorial let us learn how to Export to Excel in Angular with example. We will be using the free JavaScript Library ExcelJs for this tutorial. There are two ways you can generate Excel in a Web App. One is on the Server-side & another is on the Client Side. The simplest way is to it on the server-side and send it to the client for download. How you achieve it depends on the Server-side technology that you use. In this tutorial, we look at how to do it on the client-side using Angular & ExcelJs.

Using ExcelJs to Export Angular

There are two good libraries available to do this task. One is SheetJS js-xlsx (xlsx for short) & the other one is excelJs. The xlsx library is a good library, but changing fonts, backgrounds, etc are available only in the Pro edition. In this tutorial, we will use excelJs to show you how to export the data to excel from Angular.

Installing ExcelJs

First create a Angular Application excelJsExample.

ng new excelJsExample
cd excelJsExample

use the npm command to install the excelJs. We also need to install the file-saver so that we can save the file to disk.

npm i --save exceljs file-saver

We also need to set the path to exceljs.min so that typescript knows where to find it. Open the tsconfig.json and add the path as shown below.

"compilerOptions": {
   .....
     
   "paths": {
     "exceljs": [
       "node_modules/exceljs/dist/exceljs.min"
     ]
   },
}

Finally open the tsconfig.app.json and add node under the types.

{
  "extends": "./tsconfig.json",
  "compilerOptions": {
    "outDir": "./out-tsc/app",
    "types": ["node"]                      ==> Add This
  },
  "files": [
    "src/main.ts",
    "src/polyfills.ts"
  ],
  "include": [
    "src/**/*.d.ts"
  ]
}

That’s it. Now we are ready to create and export the excel file.

Data to Export

Now let us prepare the data export

Open the app.component.ts and add the following code

import { Component} from '@angular/core';

@Component({
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
})
export class AppComponent {

  title = 'excelJs Example in Angular';


  data: product[] = [
    { id: 1, name: "Nivia Graffiti Basketball", brand: "Nivia", color: "Mixed", price: 391.00 },
    { id: 2, name: "Strauss Official Basketball", brand: "Strauss", color: "Orange", price: 391.00 },
    { id: 3, name: "Spalding Rebound Rubber Basketball", brand: "Spalding", color: "Brick", price: 675.00 },
    { id: 4, name: "Cosco Funtime Basket Ball, Size 6 ", brand: "Cosco", color: "Orange", price: 300.00 },
    { id: 5, name: "Nike Dominate 8P Basketball", brand: "Nike", color: "brick", price: 1295 },
    { id: 6, name: "Nivia Europa Basketball", brand: "Nivia", color: "Orange", price: 280.00 }
  ]

}

export interface product {
  id: number
  name: string
  brand: string
  color: string
  price: number
}

Open the app.component.html. Create an HTML table to display the data. Add the Export button which is bound to exportExcel() method of the component class using event binding.

<h1>Angular Excel Export & Import Example</h1>

<table class="table table-hover">
  <thead>
    <tr>
      <th scope="col">id</th>
      <th scope="col">Name</th>
      <th scope="col">Brand</th>
      <th scope="col">Color</th>
      <th scope="col">Price</th>
    </tr>
  </thead>
  <tbody>
    <tr *ngFor="let item of data;">
      <td>{{item.id}}</td>
      <td>{{item.name}}</td>
      <td>{{item.brand}}</td>
      <td>{{item.color}}</td>
      <td>{{item.price}}</td>
    </tr>
  </tbody>
</table>

<button type="button" class="btn btn-primary" (click)="exportExcel()">Export</button>

Using excelJs

To use the excelJs , open the app.component.ts and import Workbook it from the exceljs. Also, import the file-saver

import { Workbook } from 'exceljs';
import * as fs from 'file-saver';

Create an Excel WorkBook

Creating a new excel file is as simple as calling new Workbook()

exportExcel() {

  let workbook = new Workbook();

}

Add a Worksheet

Next, let us add a worksheet using the addWorksheet method. We need to supply the name of the worksheet to it.

exportExcel() {

    let workbook = new Workbook();
    let worksheet = workbook.addWorksheet('ProductData');
}

Save Excel

We are yet to add any data to our Excel, but before that, we will save the blank file to check everything is ok. We use the xlsx.writeBuffer to write it to a buffer. The writeBuffer returns the data in a promise. We use that to create a blob. Finally use the blob data to download it using the file-saver

exportExcel() {

    let workbook = new Workbook();
    let worksheet = workbook.addWorksheet('ProductSheet');

    workbook.xlsx.writeBuffer().then((data) => {
      let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      fs.saveAs(blob, 'ProductData.xlsx');
    })
}

Run the app. Click on the export button. You will see the blank excel file if everything is ok.

You can also save it to a CSV File by using the csv.writeBuffer() mehtod.

    workbook.csv.writeBuffer().then((data) => {
      let blob = new Blob([data], { type: 'text/csv' });
      fs.saveAs(blob, 'ProductData.csv');
    })

There three write methods both in CSV & XLSX formats.

//Writing to a file
writeFile(fileName):

//Write to a stream
write(stream):

//Write to a buffer
writeBuffer():

Setting Column Headers

Add the column to columns array of the worksheet object. Each column has the property header, key, width, hidden, style & outlineLevel etc.

    worksheet.columns = [
      { header: 'Id', key: 'id', width: 10 },
      { header: 'Name', key: 'name', width: 32 },
      { header: 'Brand', key: 'brand', width: 10 },
      { header: 'Color', key: 'color', width: 10 },
      { header: 'Price', key: 'price', width: 10, style: { font: { name: 'Arial Black', size:10} } },
    ];

You can access the column using the key, column name, or number. All of the following returns the reference to the idCol

idCol = worksheet.getColumn('id');
idCol = worksheet.getColumn('A');
idCol = worksheet.getColumn(1);

Get a column and set its header.

let brandCol = worksheet.getColumn('brand');
brandCol.header = 'Brands';

Change its key or width

let brandCol = worksheet.getColumn('brand');
brandCol.key = 'Brands';
brandCol.width= 15;

Adding a Row

You can add the row using the addRow(data: any, styleOption: string): Row

data: The first argument is either an object or an Array.
styleOption which can be i for inherit from the row above, o for an original style, n for none.

Add a couple of rows by key-value, after the last current row, using the column keys. The property name must match the column key, which we defined while adding column headers.

var row = worksheet.addRow({id: 1, name: 'Nivia Graffiti Basketball', brand: 'Nivia', color:'red',price:'1000'},'n');

Add a row by contiguous Array (assign to columns A, B & C)

var row = worksheet.addRow([1, 'Nivia Graffiti Basketball', 'Nivia','red',1000],'n');

Add a blank row

var row = worksheet.addRow([], "n");

Mulitple Rows

var rows=[
  [1, 'Cosco Funtime Basket Ball, Size 6', 'Cosco', 'red', 50],
  [1, 'Nivia Europa Basketball', 'Nivia', 'red', 700],
]

worksheet.addRows(rows,"n")

Get a Row

const row = worksheet.getRow(5);

Change the Font

row.font = { name: 'Verdana', size: 16, underline: 'single', bold: true };

Change Cell Value

 row = worksheet.getRow(10);
 row.getCell("A").value = "Column A"
 row.getCell(2).value = "Column B"

Loop through the cells of a row

let Row= worksheet.getRow(1)
Row.eachCell(cell => {
  cell.font.size=8
})

Final Code

We have the data in our variable data. We can loop through it and add it to the worksheet as shown below.

this.data.forEach(e => {
  worksheet.addRow({id: e.id, name: e.name, brand:e.brand, color:e.color, price:e.price },"n");
});

Alternatively we can use the addRows method.

worksheet.addRows(this.data, "n");

The final code of our exportExcel is as shown below

exportExcel() {

  let workbook = new Workbook();
  let worksheet = workbook.addWorksheet('ProductSheet');

  worksheet.columns = [
    { header: 'Id', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 32 },
    { header: 'Brand', key: 'brand', width: 10 },
    { header: 'Color', key: 'color', width: 10 },
    { header: 'Price', key: 'price', width: 10, style: { font: { name: 'Arial Black', size:10} } },
  ];

  this.data.forEach(e => {
    worksheet.addRow({id: e.id, name: e.name, brand:e.brand, color:e.color, price:e.price },"n");
  });

  workbook.xlsx.writeBuffer().then((data) => {
    let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    fs.saveAs(blob, 'ProductData.xlsx');
  })

}

Run the App. Click on Export button and you should be able to download the excel file

Next Steps

You can refer to the ExcelJs Documentation and try out adding images. Set worksheet properties, merge cells & rows, set formulas etc.

References

1. ExcelJs Home Page
2. npm Package
3. GitHub
4. Office system file format MIME types on servers