Generate Excel Files in Angular 9
Letโs create a new Angular project using the Angular CLI tool by running/executing the following angular CLI command
- ng new export-to-excel
- Would you like to add Angular routing? Yes
- Which stylesheet format would you like to use? SCSS
Whatโs ExcelJS?
ExcelJS is the Javascript library used to read, write and manipulate the excel spreadsheet data and styles to JSON and XLSX. We can create XLSX files easily with formatted headers, footers, and cells, and input any custom data including text, images, etc. Itโs an active and community-driven library with many features.
Features of ExcelJS
Here are some of the awesome features of Excel JS:-
- View, Add, Remove, and Access Worksheets, and also set the Worksheet States as hidden, visible, and very hidden.
- Page setup of a worksheet includes orientation, margins, height, width, and print area.
- Headers and Footers configuration of the worksheet with custom style, font, color, etc.
- Worksheet Views to Freeze & Split sections.
- Merging the group of cells or Handling individual cells.
- Cell-level data validation support.
- Tables and Comments on worksheets.
- Customized styling includes fonts, fill, border, alignment, size, gradient fills, etc.
- Images in the worksheet can be added using the addImage() function.
- File Input Output for Reading and writing of CSV & XLSX.
- ES5 Support can be used in simple HTML pages for some reactive applications like Angular, React, .Net, Java, and NodeJS.
Install ExcelJs
To install the ExcelJsย package execute the following command, which is the main player to deal with Excel format-related functionalities we want to achieve
- npm install โsave [email protected]
For more info or reference, you can checkย https://www.npmjs.com/package/exceljsย this link.
Install FileSaver
We require FileSave.js to deal with operations like saving files on the disk. It is mainly used in web applications to store large files in the system.
Run the following command to install the file-saver
- npm install โsave file-saver
Configure tsconfig.json
Open theย tsconfig.jsonย file at the project root, then add the โpathsโ property under the โcompiler optionsโ with the โExcelJsโ library location
tsconfig.json โcompiler optionsโ: { โฆ โpathsโ: { โexceljsโ: [ โnode_modules/exceljs/dist/exceljs.minโ ]}}
Create a Service For ExcelJs
Now weโll create a new service to keep the Excel-related method in one place so that we donโt have to write the whole code again and again for one functionality. To generate an Excel service in the service folder execute the following command.
- ng generate service services/excel
This will create an ExcelService under the services folder
Update the ExcelService
Now open the excel.service.ts file inside the services folder and make the following changes:
- import { Workbook } from โExcelJsโ;
- import * as fs from โtime-saver;
We will create a separate method in excel.service.ts called generate excel().
Now we will give the Title to the excel File Like โUser Dataโ
So, we will just declare a variable named Title and give value to it.
const title = โUser Dataโ
now, we will set the header and values of the column randomly
letโs suppose we have a JSON like
const user-list = [ { name: โRakeshโ, designation: โSoftware Developerโ, address: โDelhiโ, gender: โMaleโ }, { name: โKapilโ, designation: โQAโ, address: โNoidaโ, gender: โMaleโ }, { name: โSunitaโ, designation: โHRโ, address: โGurgaonโ, gender: โFemaleโ } ]
For setting the header randomly from the keys of JSON, we will use
const header = Object.keys(userList[0])
This will take all the keys from the 0 index from the JSON and then the Object. keys() method will return the keys of the 0 index from the JSON into the header variable in the form of an array.
Create Workbook and Add a Worksheet
Create a new workbook and add a new worksheet using add worksheet() method of Workbook.
- const workbook = new Workbook()
- const worksheet = workbook. add worksheet(โUser Reportโ)
Good to Read:- Angular Project Setup by Angular CLI Tool
Add Rows and Format the Fonts
Now, we want to use the โCustom Fontโ Family in the Excel file so first, we will download the custom font from the web. For example, I want to use Saysettha OTย font in our Excel file so I will download this font from https://laoscript.net/download/ this link.
Note: You can download any font from any website, you just need to install it in the system, we will tell you how to install the font in the system below
After downloading the font, letโs install the font in the system
- Search Font Settings in the search bar
2. Now drag and drop the download file below the Add Fonts section
After dropping the font file, the system will automatically install the font in the system and you can check this font in the Excel sheet in the font dropdown.
Adding Custom Font to the Project
- Go inside the assets folder inside the app folder
- Create a folder named Excel font
- Copy and paste the download font file inside the Excel font folder, it will look like this
- Now we have to add a font face to bind the download file with a name so that we can use it in the entire application
- Go to the index.html page and then add
<style> @font-face { font-family: โSaysettha OTโ; src: url(โ./assets/fonts/excelfont/saysettha_ot.ttfโ) format(โtruetypeโ);} </style>
It will look like this
Letโs Resume to the excel.service.ts now
1. Letโs add a title row with our custom font
const title row = worksheet.addRow([title]) title row.font = { name: โSaysettha OTโ, family: 4, size: 16, bold: true }
It will add a row with the title that we have provided above with our custom font family, here the name represents the name of the font family that we want to use in the Excel file, we have added our custom font with the font-face Saysettha OT.
Note: Make sure you must enter the exact value in the name that you have entered in the font-family attribute inside the @font-face
2. Adding Header in a new row with custom font
worksheet.addRow([]) const headerRow = worksheet.addRow(header) headerRow.eachCell(cell => { cell.font = { name: โSaysettha OTโ, bold: true } }
Note: you must give a custom font to every row
3. Add Data in Excel with a custom font
userList.forEach(d => { let row = worksheet.addRow(Object.values(d)) row.font = { name: โSaysettha OTโ } })
Export File Using FileSaver
workbook.xlsx.writeBuffer().then(excelData => { const blob = new Blob([excelData], { type: โapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetโ }) fs.saveAs(blob, โUserReport.xlsxโ) })
Good to Read:- How To Use Google Maps In Your Angular Application
Your final code will look like this
tsconfig.js { โcompileOnSaveโ: false, โcompilerOptionsโ: { โbaseUrlโ: โ./โ, โoutDirโ: โ./dist/out-tscโ, โsourceMapโ: true, โdeclarationโ: false, โdownlevelIterationโ: true, โexperimentalDecoratorsโ: true, โmoduleโ: โesnextโ, โmoduleResolutionโ: โnodeโ, โimportHelpersโ: true, โtargetโ: โes2015โ, โlibโ: [ โes2018โ, โdomโ ], โpathsโ: { โexceljsโ: [ โnode_modules/exceljs/dist/exceljs.minโ ] } }, โangularCompilerOptionsโ: { โfullTemplateTypeCheckโ: true, โstrictInjectionParametersโ: true } }
app.component.ts
import { Component } from โ@angular/coreโ; import { ExcelService } from โ./excel.serviceโ; @Component({ selector: โapp-rootโ, templateUrl: โ./app.component.htmlโ, styleUrls: [โ./app.component.cssโ] }) export class AppComponent { constructor(private excelService: ExcelService) {} generateExcel() { this.excelService.generateExcel(); } }
app.component.html
<button (click)=โgenerateExcel()โ> Generate Excel</button>
excel.service.ts
import { Injectable } from โ@angular/coreโ import { Workbook } from โexceljsโ import * as fs from โfile-saverโ @Injectable({ providedIn: โrootโ }) export class ExcelService { constructor() { } generateExcel() { const userList = [ { name: โRakeshโ, designation: โSoftware Developerโ, address: โDelhiโ, gender: โMaleโ }, { name: โKapilโ, designation: โQAโ, address: โNoidaโ, gender: โMaleโ }, { name: โSunitaโ, designation: โHRโ, address: โGurgaonโ, gender: โFemaleโ } ] const title = โUser Dataโ const header = Object.keys(userList[0]) const workbook = new Workbook() const worksheet = workbook.addWorksheet(โUser Reportโ) // Add new row const titleRow = worksheet.addRow([title]) // Set font family, font size, and style in title row. titleRow.font = { name: โSaysettha OTโ, family: 4, size: 16, bold: true } // Blank Row worksheet.addRow([]) // Add Header Row const headerRow = worksheet.addRow(header) // Cell Style : Fill and Border headerRow.eachCell(cell => { cell.font = { name: โSaysettha OTโ, bold: true } }) // Add Data and Conditional Formatting userList.forEach(d => { let row = worksheet.addRow(Object.values(d)) row.font = { name: โSaysettha OTโ } }) workbook.xlsx.writeBuffer().then(excelData => { const blob = new Blob([excelData], { type: โapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetโ }) fs.saveAs(blob, โUserReport.xlsxโ) }) } }
FAQs
1) How to Use Excel JS In Angular?
Here’s how to import and export Excel spreadsheets in Angular.
- Install the SpreadJS component in your application
- Instantiate a SpreadJS component
- Create an input element that accepts an XLSX file
- Add the import code
- Add the export code
2) How to Read Data from an Excel File In Angular 12?
// Create workbook object let wb:Workbook = new Workbook(); The Workbook object must use readFile and file types. where xlsx is the file type. Create a prompt to get a value from the workbook, then block and fix it.
3) How do Download an Excel File From the Assets Folder in Angular?
To download an item, follow these steps:
- Click on the logo in the upper left corner
- On the browse page, click Assets > Files
- Navigate to the folder containing the resource you want to download
- Select a folder or select one or more assets in a folder
- Click Download on the toolbar
If you are interested in even more development-related articles and information from us here at Devstringx, then we have a lot to choose from.