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.

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.

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.


Best Angular Books
The Top 8 Best Angular Books, which helps you to get started with Angular  

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

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

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.

Using excelJs

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

Create an Excel WorkBook

Creating a new excel file is as simple as calling 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.

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

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.

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.

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

Get a column and set its header.

Change its key or width

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.

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

Add a blank row

Mulitple Rows

Get a Row

Change the Font

Change Cell Value

Loop through the cells of a row

Final Code

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

Alternatively we can use the addRows method.

The final code of our exportExcel is as shown below

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

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top