How to export data to excel sheet with colors and style in angular.

 There are two most popular library for javascript to export data with excel sheet.

1) xlsx library
2) exceljs library

If you would like to export simple data without changing its style then xlsx is perfact, but if you would like to change style before export then exceljs is perfect for JavaScript frameworks.

Lets create an example with exceljs library to export data by changing style and colors for angular.

Step 1: Install "exceljs" library

            npm install exceljs

Step 2: Install "file-saver" library to save excel file.

            npm install file-saver

Step 3: Import "exceljs" and "file-saver" library to your component.

import * as ExcelJS from 'exceljs/dist/exceljs.min.js';
import { saveAs } from 'file-saver'; Step 4: Now create function in your component to export Data.
  exportExcel()
  {
 
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('My Sheet');
 
    let  dataArray = [{'Surname':'Lakhani','Name':'Ali'},
                      {'Surname':'Lakhani','Name':'Raza'},
                      {'Surname':'Patel','Name':'Nikunj'},
                      {'Surname':'Patel','Name':'Jigar'}];
  // Add headers
  const headers = Object.keys(dataArray[0]);
  let headerRow = worksheet.addRow(headers);
  headerRow.font = {bold:true};
 
  // Add data rows
  dataArray.forEach((item,index) => {
    let row = [];

    headers.forEach((header) =>
    {
      row.push(item[header]);
    });
 
    let row1 = worksheet.addRow(row);
   
    const isEvenRow = index % 2 === 0;
    const fillColor = isEvenRow ? 'DDDDDDDD' : 'FFFFFFFF';
 
    row1.fill  = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: fillColor }
    };
  });
 
  worksheet.getColumn(1).width = 20;
  worksheet.getColumn(2).width = 20;
 
  // Generate Excel file
  workbook.xlsx.writeBuffer().then((buffer: any) => {
    const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    saveAs(blob, 'Friends.xlsx');
  });
 
  } Step 4: Now call above function in ngOnInint to execute it. Suggesion: "exceljs" library is created for backend nodejs framework, So don't try to import directly exceljs. import * as ExcelJS from 'exceljs'; //Wrong way to import exceljs in angular, it will throw bellow error. "Cannot find module 'stream' or its corresponding type declarations.", import * as ExcelJS from 'exceljs/dist/exceljs.min.js'; //Correct way to import in angular. If you still want to use "exceljs" directly then Add "types": ["node"] option to the compilerOptions section in your tsconfig.app.json file, it will resolve the error.  

Comments

Post a Comment

Popular posts from this blog

how to save images with angular nodejs

 There are two ways to save images. 1) store image in database by converting it to binary format. 2) copy image in your project folder then save image path in table. ------- 1st way with front end angular------ pass uploaded file in "handleFileInput" function.  saveImage ( files :  FileList ) {    var   file : File  =  files . item ( 0 );    var   myReader : FileReader  =  new   FileReader ();    myReader . onloadend  = ( e )  =>  {      this . image  =  myReader . result ;   }    myReader . readAsDataURL ( file );    //convert to base64 ends } now you will get  binary format image in this.image variable. save this converted image directly to database through API. ------2nd way with api nodejs------ we can use "multer" to copy file in project folder. const   multer  =  require ( 'multer'...
Read more

How to restrict your logged In page in angular (authentication guard)

 You can set authentication guard to restrict users which are not loggedin. 1) Service of auth guard    import { Injectable } from '@angular/core' ; import { Router , CanActivate , ActivatedRouteSnapshot , RouterStateSnapshot , UrlTree } from '@angular/router' ; import { CookieService } from 'ngx-cookie-service' ; @ Injectable ({ providedIn: 'root' }) export class AuthGuardService implements CanActivate { constructor ( private _router : Router , private _cookieService : CookieService ) { } userid = "" ; canActivate ( route : ActivatedRouteSnapshot , state : RouterStateSnapshot ): boolean | UrlTree { this . userid = this . _cookieService . get ( 'userid' ) if ( this . userid == "" ) { alert ( 'You are not allowed to view this page. You are redirected to login Page' ); this . _router . navigate ([ "login" ],{ queryParams: { retUrl: route . url } }); return false ; /...
Read more

How to notify about data update to other component.

   we can update about data changes to any component using bellow simple method 1) Service to publish and receive data import {Injectable} from '@angular/core'; import {Subject} from 'rxjs'; @Injectable({ providedIn: 'root' }) export class GlobalFooService { private fooSubject = new Subject<any>(); publishSomeData(data: any) { this.fooSubject.next(data); } getObservable(): Subject<any> { return this.fooSubject; } } 2) Data sending component @Component({ selector: 'app-home', templateUrl: 'home.page.html', styleUrls: ['home.page.scss'] }) export class HomePage { constructor(private globalFooService: GlobalFooService) { } onSomeButtonClick() { this.globalFooService.publishSomeData({ foo: 'bar' }); } } 3) Data receiving component @Component({ selector: 'app-root', templateUrl: 'app.component.htm...
Read more