Exporting Data to Excel file in Angular using ExcelJS
Tags: angular, javascript, exceljs
Published: 2020-03-29 07:54:38

Generating Reports in Excel Sheet is a common use case for any application. There are different ways to generate excel file in Web application. You can generate it on server and download it on client side. Also another approach is you can generate Excel file on client-side (directly in browser).

Client-Side Excel file generation can be very helpful for reducing API calls and real-time excel sheet generation (without calling any API). 

In this article, we will see **How to export an excel file in Angular application ?**

![Excel Generated using ExcelJS in Angular](https://www.ngdevelop.tech/wp-content/uploads/2018/08/Export-an-excel-using-Angular-6.jpg)

> **This article is originally published on [NgDevelop Blog](https://www.ngdevelop.tech/blog/)**
> Check out the original article 📰 [Export to Excel in Angular using ExcelJS (Client Side Excel Generation in Angular)](https://www.ngdevelop.tech/export-to-excel-in-angular-6/)

## Client-Side Excel Generation Libraries

Two important open source libraries available to read/write an excel in the client-side web applications are :

- [ExcelJS](https://www.npmjs.com/package/exceljs)
- [XLSX](https://www.npmjs.com/package/xlsx) and [XLSX-Style](https://www.npmjs.com/package/xlsx-style)

Both libraries are used to read, manipulate and write spreadsheet data and styles to XLSX and JSON.

Initially, I had used **XLSX & XLSX-style** libraries to create and format an excel from JSON but the adverse point of this open-source library is it doesn’t provide any feature to add an image in excel (add image feature is available in pro version), which was the big requirement for my excel.

Later on, I found **ExcelJS**, which is really easy to use, provide almost all the features of Excel, and the main thing is it also provide add image feature.

So lets start with step by step implementation :

## Environment Setup
---------------------------------------------------------------------------
### Create a Angular Project
Create a new angular project, if it is already created then go ahead with next step.

### Install ExcelJS Library
`npm install --save exceljs`
*Note: if you are using Angular 8 or below, then install `exceljs@1.12.0`.*

### Update tsconfig.js
**ExcelJS** is generally used for server side web applications in node. Here we want to use it in client side Angular application. for that we need to set `compilerOptions` in `tsconfig.json` as shown below :

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

*for Angular 4 & 5 configuration checkout out my [original blog](https://www.ngdevelop.tech/export-to-excel-in-angular-6/)*

### Install file-saver
[FileSaver.js](https://www.npmjs.com/package/file-saver) is the solution to saving files on the client-side and is perfect for web apps that need to generate files, or for saving sensitive information that shouldn’t be sent to an external server.

It implements the `saveAs()` FileSaver interface in browsers that do not natively support it.

Install file-saver library using following command
`npm install --save file-saver`

## Generate Excel with Formatting
-------------------------------------------------------------------------------
Environment setup is done. Now lets start to build an excel.

We will create a separate service in our project called `excel.service.ts`,  you can create it using below command

`ng generate service excel`

### Import ExcelJS and FileSaver
In `excel.service.ts`, add the following import statements.

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

### Create a separate method and data variables.
In `excel.service.ts`, We will create a separate method called `generateExcel()`.
In this method, I have created some data variables as below, We will export these data in excel sheet.

*Note: You can pass data from the component as a parameter in `generateExcel()` and generate a dynamic excel sheet.*

```javascript
const title = 'Car Sell Report';
const header = ["Year", "Month", "Make", "Model", "Quantity", "Pct"]
const data = [
  [2007, 1, "Volkswagen ", "Volkswagen Passat", 1267, 10],
  [2007, 1, "Toyota ", "Toyota Rav4", 819, 6.5],
  [2007, 1, "Toyota ", "Toyota Avensis", 787, 6.2],
  [2007, 1, "Volkswagen ", "Volkswagen Golf", 720, 5.7],
  [2007, 1, "Toyota ", "Toyota Corolla", 691, 5.4],
  ...
];
```

### Create Workbook and Add Worksheet
Create a new workbook and add a new worksheet using `addWorksheet()` method of `Workbook`.

```javascript
let workbook = new Workbook();
let worksheet = workbook.addWorksheet('Car Data');
```

### Add Row and format the fonts.

We will use `addRow()` method of `worksheet` object. to add a row in a worksheet. as below,

```javascript
// Add new row
let titleRow = worksheet.addRow([title]);

// Set font, size and style in title row.
titleRow.font = { name: 'Comic Sans MS', family: 4, size: 16, underline: 'double', bold: true };

// Blank Row
worksheet.addRow([]);

//Add row with current date
let subTitleRow = worksheet.addRow(['Date : ' + this.datePipe.transform(new Date(), 'medium')]);
```

### Add Image in worksheet

To add an image in excel we need to add `base64` of an image. I have saved the `base64` of logo image in a separate file called `carlogo.js` as below.

`export const logoBase64 = "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfQAAAFKCAMAAADcyF29AAABxVBMVEVHcEwJCAggFxEBAQE2KyQAAAA0LScAAAAAAAA1LysXEQ0EBAQFBAMDAwMLCQgGBQUFBAOEQhUHBwZjQSuScFoVFRZvNAx5NghcOyaudU8yMDBrNhOiViMZFhXEdD3Ef0+4ZzFISUdSJwliMA6BPA6lVR8CAgEDAgQPDhANDgsKCQoVFhPObi4SERS2VxcE.......";`

To use it in `generateExcel()` method, We need to import `carlogo.js` in `excel.service.ts` as below,

`import * as logoFile from './carlogo.js';`

Now, add an image in a worksheet as below,
```javascript
let logo = workbook.addImage({
      base64: logoFile.logoBase64,
      extension: 'png',
});
worksheet.addImage(logo, 'E1:F3');
```
`workbook.addImage(image)` creates an image object and returns the image id, that image id we will use to place image in the worksheet using `worksheet.addImage(imageId, cellRange)`. The coordinates calculated from the range will cover from the top-left of the first cell to the bottom right of the second.

### Merge Cells
We can merge cells using `worklist.mergeCells(cellRange)` method, as below,
`worksheet.mergeCells('A1:D2');`
The coordinates calculated from the range will cover from the top-left of the first cell to the bottom right of the second.

### Add Data with Header & Conditional Formatting
We will add a header row for car data records with a background color, as below,

```javascript
//Add Header Row
let headerRow = worksheet.addRow(header);

// Cell Style : Fill and Border
headerRow.eachCell((cell, number) => {
  cell.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFFFFF00' },
    bgColor: { argb: 'FF0000FF' }
  }
  cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } }
});
```

ExcelJS directly doesn’t support conditional formatting, but we can achieve this functionality by assigning style based on required condition in angular, as below,

```javascript
// Add Data and Conditional Formatting
data.forEach(d => {
  let row = worksheet.addRow(d);
  let qty = row.getCell(5);
  let color = 'FF99FF99';
  if (+qty.value < 500) {
    color = 'FF9999'
  }
  qty.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: color }
  }
}
);
```

*Same way, you can add other rows like footer and additional information.*

### Export Excel file using FileSaver
Now our workbook is ready to export. We can export it using `saveFile()` method of file-saver, as shown below
```javascript
workbook.xlsx.writeBuffer().then((data) => {
      let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      fs.saveAs(blob, 'CarData.xlsx');
});
```

**Great!!!👍👍👍 You have exported the excel file in Angular**

## Checkout Original Article and GitHub Repository
----------------------------------------------------------------------------
Read my original article 📰 [Export to Excel in Angular using ExcelJS](https://www.ngdevelop.tech/export-to-excel-in-angular-6/) for a detailed and step-by-step explanation to generate excel file in each angular version.

GitHub Repository 📝 [angular-export-an-excel-file](https://github.com/ngdevelop-tech/angular-6-export-an-excel-file)

> I hope you like this article. Please provide your valuable comments, feedback, and suggestions.

> If you like the project please mark the ⭐ in the Github repository.

Keep Learning, Keep Sharing 🙂