Angular 6 Download Excel File From Api

Recently I was working on ane awarding where I need to export to excel in Angular 8, I need to prove a set of account records with header and footer. I researched it and found some docs but it doesn't give any proper example for angular. After all of the research, I accept implemented it in my application.

In this post, I will show you how to export an excel file in angular eight.

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

XLSX & XLSX-Manner
ExcelJS

Both libraries are used to read, dispense and write spreadsheet information and styles to XLSX and JSON.

Initially, I had used XLSX & XLSX-manner libraries to create and format an excel from JSON but the agin betoken of this open-source library is information technology doesn't provide any characteristic to add an image in excel (add image characteristic is available in pro version), which was the big requirement for my excel.

Afterwards on, I establish ExcelJS, which is really piece of cake to use, provide almost all the features of Excel, and the main thing is it also provide add together image feature.

Consign to Excel in Angular 8 using ExcelJS

Create a Angular 8 Project

Use below command to create a new Angular half dozen project with Athwart CLI.

ng new angular-exceljs-example

Install ExcelJS Library

Important

The latest version of exceljs (version 3.4.0 while writing the commodity) uses the @types/[email protected]^x.12.0 while angular 8 uses @types/[e-mail protected]~8.9.4. Because of this, yous might face the following error while compiling an application :

Mistake in node_modules/exceljs/index.d.ts:1661:34 - fault TS2503: Cannot find namespace 'NodeJS'.  dictionary: Buffer | NodeJS.TypedArray | DataView | ArrayBuffer; // debunk/inflate merely, empty dictionary past default

We tin can prepare this by upgrading the @types/nodes to 10.12.0 but considering athwart viii doesn't utilize this version I will not recommend doing so, So until we required any specific feature which is released in latest exceljs version, I will recommend you to utilise [electronic mail protected] for angular eight applications.

If y'all find any issue in angular 8 with exceljs, Refer to the comment section below which contains some useful solutions. However, if you don't find any solution please add your outcome in new comment.

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 nosotros need to set up compilerOptions in tsconfig.json as shown below :

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

If you are using Angular iv or five you need to use the following path in tsconfig.json

"paths": {     "exceljs": [     "../node_modules/exceljs/dist/es5/exceljs.browser"     ] }

In Linux, It is not able to find exceljs.browser or exceljs.min from tsconfig.js So remove the path fromtsconfig and import ExcelJS like this:

import * as Excel from "exceljs/dist/exceljs.min.js"; import * as ExcelProper from "exceljs"; let workbook: ExcelProper.Workbook = new Excel.Workbook();

Install file-saver

FileSaver.js is the solution to saving files on the client-side and is perfect for spider web apps that need to generate files, or for saving sensitive information that shouldn't be sent to an external server.

It implements thesaveAs() FileSaver interface in browsers that practice not natively support it.

Install file-saver library using post-obit control

npm install --save file-saver

Surroundings setup is done. Now lets get-go to build an excel.

We will create a separate service in our projection called excel.service.ts, you can create it using beneath command

ng generate service excel

Import ExcelJS and FileSaver

In excel.service.ts, add together the following import statements.

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

Create a separate method and data varibales.

In excel.service.ts, We will create a separate method called generateExcel().

In this method, I have created some data variables every bit below, Nosotros volition consign these information in excel sheet.

Notation: You lot tin can pass data from the component as a parameter in generateExcel() and generate a dynamic excel sheet.

const title = 'Car Sell Report'; const header = ["Year", "Month", "Make", "Model", "Quantity", "Percentage"] const data = [   [2007, 1, "Volkswagen ", "Volkswagen Passat", 1267, 10],   [2007, 1, "Toyota ", "Toyota Rav4", 819, six.v],   [2007, 1, "Toyota ", "Toyota Avensis", 787, 6.2],   [2007, i, "Volkswagen ", "Volkswagen Golf", 720, 5.vii],   [2007, one, "Toyota ", "Toyota Corolla", 691, 5.4],   ... ];

Create Workbook and Add Worksheet

Create a new workbook and add together a new worksheet using addWorksheet() method of Workbook.

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

Add Row and format the fonts.

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

// Add together new row let titleRow = worksheet.addRow([championship]);  // Fix font, size and way in title row. titleRow.font = { name: 'Comic Sans MS', family: iv, size: 16, underline: 'double', assuming: true };  // Blank Row worksheet.addRow([]);  //Add row with current date allow 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 paradigm in a divide file called carlogo.js every bit 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 equally beneath,

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

Now, add an image in a worksheet as below,

let logo = workbook.addImage({       base64: logoFile.logoBase64,       extension: 'png', });  worksheet.addImage(logo, 'E1:F3');

workbook.addImage(image) creates an prototype 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 volition cover from the peak-left of the get-go 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 volition cover from the acme-left of the beginning prison cell to the bottom right of the 2d.

Add Data with Header & Provisional Formatting

We will add a header row for motorcar information records with a background color, as below,

//Add together Header Row allow headerRow = worksheet.addRow(header);  // Prison cell Style : Fill and Edge headerRow.eachCell((cell, number) => {   cell.fill = {     blazon: 'pattern',     pattern: 'solid',     fgColor: { argb: 'FFFFFF00' },     bgColor: { argb: 'FF0000FF' }   }   cell.border = { top: { way: 'thin' }, left: { manner: 'sparse' }, bottom: { way: 'thin' }, right: { style: 'sparse' } } });

ExcelJS directly doesn't back up conditional formatting, but we can accomplish this functionality by assigning style based on required status in angular, as below,

// Add Data and Conditional Formatting data.forEach(d => {   let row = worksheet.addRow(d);   allow qty = row.getCell(v);   let color = 'FF99FF99';   if (+qty.value < 500) {     color = 'FF9999'   }    qty.make full = {     blazon: 'design',     design: 'solid',     fgColor: { argb: colour }   } }  );

Note: Using worklist.addRows(records: whatsoever[]) method we tin add multiple rows. as an example,

worksheet.addRows(information);

But in our case, we desire to perform cell styling based on a conditional check, And so we have saved the individual row using worklist.addRow(tape) method.

Aforementioned way, you lot can add other rows like footer and additional information.

Export file using FileSaver

Now our workbook is set up to consign. We can export it using saveFile() method of file-saver, as shown below

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

Final Code Review

Note : I have added Generate Excel button in AppComponent which will phone call genetateExcel() method of excel.service.ts.

{   "compileOnSave": false,   "compilerOptions": {     "baseUrl": "./",     "outDir": "./dist/out-tsc",     "sourceMap": true,     "declaration": false,     "moduleResolution": "node",     "emitDecoratorMetadata": true,     "experimentalDecorators": truthful,     "target": "es5",     "paths": {       "exceljs": [         "node_modules/exceljs/dist/exceljs.min"       ]     },     "typeRoots": [       "node_modules/@types"     ],     "lib": [       "es2017",       "dom"     ]   } }

import { Component } from '@angular/core'; import { ExcelService } from './excel.service';  @Component({   selector: 'app-root',   templateUrl: './app.component.html',   styleUrls: ['./app.component.css'] }) export form AppComponent {    constructor(private excelService: ExcelService) {    }    generateExcel() {     this.excelService.generateExcel();   }  }

<push (click)="generateExcel()"> Generate Excel</button>

import { Injectable } from '@angular/cadre'; import { Workbook } from 'exceljs'; import * as fs from 'file-saver'; import * as logoFile from './carlogo.js'; import { DatePipe } from '../../node_modules/@angular/common'; @Injectable({   providedIn: 'root' }) export class ExcelService {     constructor(private datePipe: DatePipe) {    }    generateExcel() {          //Excel Title, Header, Data     const championship = 'Automobile Sell Report';     const header = ["Year", "Month", "Make", "Model", "Quantity", "Percentage"]     const data = [       [2007, 1, "Volkswagen ", "Volkswagen Passat", 1267, 10],       [2007, ane, "Toyota ", "Toyota Rav4", 819, 6.5],       [2007, 1, "Toyota ", "Toyota Avensis", 787, 6.2],       [2007, one, "Volkswagen ", "Volkswagen Golf", 720, v.7],       [2007, one, "Toyota ", "Toyota Corolla", 691, 5.4],       [2007, ane, "Peugeot ", "Peugeot 307", 481, 3.eight],       [2008, one, "Toyota ", "Toyota Prius", 217, ii.ii],       [2008, 1, "Skoda ", "Skoda Octavia", 216, ii.2],       [2008, 1, "Peugeot ", "Peugeot 308", 135, 1.4],       [2008, two, "Ford ", "Ford Mondeo", 624, 5.nine],       [2008, 2, "Volkswagen ", "Volkswagen Passat", 551, 5.ii],       [2008, two, "Volkswagen ", "Volkswagen Golf game", 488, iv.vi],       [2008, 2, "Volvo ", "Volvo V70", 392, 3.7],       [2008, 2, "Toyota ", "Toyota Auris", 342, iii.2],       [2008, ii, "Volkswagen ", "Volkswagen Tiguan", 340, iii.2],       [2008, 2, "Toyota ", "Toyota Avensis", 315, 3],       [2008, 2, "Nissan ", "Nissan Qashqai", 272, ii.6],       [2008, 2, "Nissan ", "Nissan X-Trail", 271, 2.six],       [2008, ii, "Mitsubishi ", "Mitsubishi Outlander", 257, 2.iv],       [2008, two, "Toyota ", "Toyota Rav4", 250, 2.4],       [2008, 2, "Ford ", "Ford Focus", 235, 2.2],       [2008, 2, "Skoda ", "Skoda Octavia", 225, 2.1],       [2008, 2, "Toyota ", "Toyota Yaris", 222, 2.1],       [2008, 2, "Honda ", "Honda CR-Five", 219, 2.1],       [2008, ii, "Audi ", "Audi A4", 200, 1.9],       [2008, two, "BMW ", "BMW 3-serie", 184, ane.seven],       [2008, 2, "Toyota ", "Toyota Prius", 165, ane.6],       [2008, two, "Peugeot ", "Peugeot 207", 144, ane.4]     ];      //Create workbook and worksheet     let workbook = new Workbook();     let worksheet = workbook.addWorksheet('Car Data');       //Add Row and formatting     let titleRow = worksheet.addRow([title]);     titleRow.font = { name: 'Comic Sans MS', family: 4, size: 16, underline: 'double', bold: true }     worksheet.addRow([]);     let subTitleRow = worksheet.addRow(['Date : ' + this.datePipe.transform(new Date(), 'medium')])       //Add Prototype     let logo = workbook.addImage({       base64: logoFile.logoBase64,       extension: 'png',     });      worksheet.addImage(logo, 'E1:F3');     worksheet.mergeCells('A1:D2');       //Bare Row      worksheet.addRow([]);      //Add Header Row     permit headerRow = worksheet.addRow(header);          // Jail cell Fashion : Fill and Edge     headerRow.eachCell((prison cell, number) => {       cell.fill = {         type: 'pattern',         pattern: 'solid',         fgColor: { argb: 'FFFFFF00' },         bgColor: { argb: 'FF0000FF' }       }       cell.border = { elevation: { manner: 'thin' }, left: { mode: 'thin' }, bottom: { fashion: 'thin' }, correct: { mode: 'thin' } }     })     // worksheet.addRows(data);       // Add Data and Conditional Formatting     data.forEach(d => {       permit row = worksheet.addRow(d);       let qty = row.getCell(five);       allow color = 'FF99FF99';       if (+qty.value < 500) {         color = 'FF9999'       }        qty.fill = {         type: 'pattern',         pattern: 'solid',         fgColor: { argb: color }       }     }      );      worksheet.getColumn(3).width = thirty;     worksheet.getColumn(4).width = 30;     worksheet.addRow([]);       //Footer Row     let footerRow = worksheet.addRow(['This is system generated excel sail.']);     footerRow.getCell(1).make full = {       blazon: 'pattern',       pattern: 'solid',       fgColor: { argb: 'FFCCFFE5' }     };     footerRow.getCell(ane).border = { top: { style: 'thin' }, left: { style: 'sparse' }, bottom: { manner: 'thin' }, right: { style: 'thin' } }      //Merge Cells     worksheet.mergeCells(`A${footerRow.number}:F${footerRow.number}`);      //Generate Excel File with given name     workbook.xlsx.writeBuffer().then((data) => {       allow blob = new Blob([data], { type: 'awarding/vnd.openxmlformats-officedocument.spreadsheetml.canvas' });       fs.saveAs(blob, 'CarData.xlsx');     })    } }

Final Output

In this article, we have seen, export an excel file in athwart 8 using exceljs.

I hope you like this article, please provide your valuable feedback and suggestions in beneath comment section🙂.

For more updates, Follow us 👍 on NgDevelop Facebook page.

DOWNLOAD HERE

Posted by: hoskinslunarned.blogspot.com

Angular 6 Download Excel File From Api UPDATED