In this article, We learn how to export to excel in Angular using ExportJS . I will show you how, just follow the steps clearly.

For more updates please do Subscribe via Email:

Table Of Contents
  1. I. Overview
  2. II. Requirements
  3. III. Export to Excel
  4. IV. Export Excel
  5. V. Put background color in header cell
  6. VI. Put border in cell
  7. VII. Text alignment center
  8. VIII. Merge Cell
  9. IX. Custom Sheet Name
  10. X. Custom File Name
  11. Related Topics

I. Overview

This article is tackle about exporting excel file from web app to you local devices. this is not just export base on what data table shown on the User Interface(UI). This is customizable exportation of excel file in angular using ExcelJS.

II. Requirements

Before we start, Please make sure to have installed the following

III. Export to Excel

Note: I will not start this article from the top like creating angular app. if you want and have idea how to create angular app just click this like here or click the image below.

Let’s Start!!

First install ExcelJS And File Saver

  • ExcelJS
npm i exceljs

Also you can install in specific version of ExcelJS.

npm i exceljs@version
  • File Saver
npm i file-saver

Also you can install in specific version of File Saver.

Next emport ExcelJS And File Saver

If you encounter issue for importing exceljs.

Try this approach and hoping may helpful to you.

  • Array of test Data.
    • Header = header of data in excel when export it.
    • Row = row of data array when export to excel file.

IV. Export Excel

  • Default settings
  • Code
 const workBook = new Workbook();
    const workSheet = workBook.addWorksheet('test');
    const row = workSheet.addRow(this.header);
    workSheet.addRow(this.Row);
    workBook.xlsx.writeBuffer().then( data => {
      let blob = new Blob([data], {
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      });
      saveAs(blob, 'test.xlsx');
    })

V. Put background color in header cell

Add background color in header cell.

  • Code
col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'} // color of the cell
        };

 const workBook = new Workbook();
    const workSheet = workBook.addWorksheet('test');
    const row = workSheet.addRow(this.header);
    for ( let i = 1; i <= this.header.length; i++) {
      const col = row.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'} // color of the cell
        };
    }
    workSheet.addRow(this.Row);
    workBook.xlsx.writeBuffer().then( data => {
      let blob = new Blob([data], {
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      });
      saveAs(blob, 'test.xlsx');
    })
  }

VI. Put border in cell

  • Code
col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }

const workBook = new Workbook();
    const workSheet = workBook.addWorksheet('test');
    const row = workSheet.addRow(this.header);
    for ( let i = 1; i <= this.header.length; i++) {
      const col = row.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'}
        };
        col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }
    }

    const row1 = workSheet.addRow(this.Row);
    for ( let i = 1; i <= this.Row.length; i++) {
      const col = row1.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'}
        };
        col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }
    }

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

VII. Text alignment center

col.alignment = {vertical:'middle', horizontal:'center'}

const workBook = new Workbook();
    const workSheet = workBook.addWorksheet('test');
    const row = workSheet.addRow(this.header);
    for ( let i = 1; i <= this.header.length; i++) {
      const col = row.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'}
        };
        col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }
        col.alignment = {vertical:'middle', horizontal:'center'}
    }

    const row1 = workSheet.addRow(this.Row);
    for ( let i = 1; i <= this.Row.length; i++) {
      const col = row1.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'}
        };
        col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }
        col.alignment = {vertical:'middle', horizontal:'center'}
    }

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

VIII. Merge Cell

  • Horizontal merging
 var A1 = workSheet.getCell('A1')
    workSheet.mergeCells('A1:A2');
    A1.value = 'Merge';
    (A1).fill= {
          type: 'pattern',
          pattern: 'solid',
          fgColor: {argb: 'FFC000'}
    };
    (A1).alignment = {vertical:'middle', horizontal:'center'};
    (A1).border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }

const workBook = new Workbook();
    const workSheet = workBook.addWorksheet('test');
    const row = workSheet.addRow(this.header);
    for ( let i = 1; i <= this.header.length; i++) {
      const col = row.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'}
        };
        col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }
        col.alignment = {vertical:'middle', horizontal:'center'}
    }

    const row1 = workSheet.addRow(this.Row);
    for ( let i = 1; i <= this.Row.length; i++) {
      const col = row1.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'}
        };
        col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }
        col.alignment = {vertical:'middle', horizontal:'center'}
    }

    var A1 = workSheet.getCell('A1')
    workSheet.mergeCells('A1:A2');
    A1.value = 'Merge';
    (A1).fill= {
          type: 'pattern',
          pattern: 'solid',
          fgColor: {argb: 'FFC000'}
    };
    (A1).alignment = {vertical:'middle', horizontal:'center'};
    (A1).border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }


    workBook.xlsx.writeBuffer().then( data => {
      let blob = new Blob([data], {
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      });
      saveAs(blob, 'test.xlsx');
    })
  • Vertical Merge
var A1 = workSheet.getCell('A1')
    workSheet.mergeCells('A1:B1');
    A1.value = 'Merge';
    (A1).fill= {
          type: 'pattern',
          pattern: 'solid',
          fgColor: {argb: 'FFC000'}
    };
    (A1).alignment = {vertical:'middle', horizontal:'center'};
    (A1).border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }


 const workBook = new Workbook();
    const workSheet = workBook.addWorksheet('test');
    const row = workSheet.addRow(this.header);
    for ( let i = 1; i <= this.header.length; i++) {
      const col = row.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'}
        };
        col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }
        col.alignment = {vertical:'middle', horizontal:'center'}
    }

    const row1 = workSheet.addRow(this.Row);
    for ( let i = 1; i <= this.Row.length; i++) {
      const col = row1.getCell(i);
        col.fill = {
          type: 'pattern',
          pattern: 'solid',
          border:"solid black",
          fgColor: {argb: 'FFC000'}
        };
        col.border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }
        col.alignment = {vertical:'middle', horizontal:'center'}
    }

    var A1 = workSheet.getCell('A1')
    workSheet.mergeCells('A1:B1');
    A1.value = 'Merge';
    (A1).fill= {
          type: 'pattern',
          pattern: 'solid',
          fgColor: {argb: 'FFC000'}
    };
    (A1).alignment = {vertical:'middle', horizontal:'center'};
    (A1).border = { top: {style:'thin'}, left:{style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'} }


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

IX. Custom Sheet Name

    const workSheet = workBook.addWorksheet('test');

.

X. Custom File Name

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

Happy Learning..

Web Link : https://portfolio.javelupango.com/angular/

GitHub Link: https://github.com/JaveTLupango/exportExcelFile/tree/ExportExcel

Thank you for visiting my blog site. Hoping you learn more here. please feel free to comment and suggest if there is need to enhance and update. thank you.

Related Topics

By: Jave LupangoPosted on Categories : Categories : Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *