Export Json data to an excel file using Angular 4

Question

I have an Array List of objects. I want to export those data to an Excel file (.xlsx or .xls).

The template of the Excel file is also given. What needs to be done is only mapping to an existing Excel file. The Excel file is located in the Angular project itself. How can I achieve this using Angular 4.

[
   {
      "name": "Roshan",
      "age": "35"
   },
   {
      "name": "Ritika",
      "age": "29"
   }
]

The above data should be map to an Excel of having column names are Employee_Name and Employee_age.

you can find a solution from the article: Exporting an Excel file in Angular — Chunbin Li
– Chunbin Li, Commented May 19, 2019 at 2:46

Indrakumara · Accepted Answer · 2019-05-19 02:39:44Z

2

try xlsx package

install xlsx to your project

 npm install xlsx --save

 import { Injectable } from '@angular/core';
 import * as FileSaver from 'file-saver';
 import * as XLSX from 'xlsx';

const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';

@Injectable()
export class ExcelExportService {
    constructor() { }

    public exportAsExcelFile(json: any[], excelFileName: string): void {
        const worksheet: XLSX.WorkSheet = XLSX.utils.aoa_to_sheet(json);
        const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
        const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });
        this.saveAsExcelFile(excelBuffer, excelFileName);
    }

    private saveAsExcelFile(buffer: any, fileName: string): void {
        const data: Blob = new Blob([buffer], {
            type: EXCEL_TYPE
        });
        FileSaver.saveAs(data, fileName + EXCEL_EXTENSION);
    }
}

export class AppComponent {
  name = 'Angular 6';
  data: any = [{
    eid: 'e101',
    ename: 'ravi',
    esal: 1000
  },
  {
    eid: 'e102',
    ename: 'ram',
    esal: 2000
  },
  {
    eid: 'e103',
    ename: 'rajesh',
    esal: 3000
  }];
  constructor(private excelService:ExcelExportService ){

  }
  exportAsXLSX():void {
    this.excelService.exportAsExcelFile(this.data, 'sample');
  }
}

answered May 19, 2019 at 2:39

Indrakumara

1,65518 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harshita Over a year ago

Thnx for sharing the answer,But i am unable to add any styling component to the cell. Can you tell by which module i can achieve it.

Indrakumara Over a year ago

@Harshita I have added a method to download excel with formatting, you can add formatting according to that

Md Samrat Akbor · Accepted Answer · 2023-01-10 12:19:31Z

You can export multiple sheets to an excel file using this code. First I created a service file and created a method exportAsExcelFile() This method code is included below. fileInfo is the object that I am including in the code section.

  @Injectable({
  providedIn: 'root'
  })
 export class ExcelExportServiceService {

 constructor() { }
public exportAsExcelFile(fileInfo: ExportFileInfo): void {
const workbook:  XLSX.WorkBook = XLSX.utils.book_new();
fileInfo.templates.forEach((x)=>{
    XLSX.utils.book_append_sheet(workbook, 
    XLSX.utils.json_to_sheet(x.data), x.sheetName);
});
XLSX.writeFile(
  wb,
  `${fileInfo.fileName}`
);
}

export interface ExportFileInfo{
 fileName:string;
 templates:ExportTemplateInfo[];
 }
  export interface ExportTemplateInfo{
  data:any;
  sheetName:string;
 }

 constructor(private exportService: ExcelExportServiceService) {}
excelFileExportFromJson(){
let data:any[]=[];
this.analysisDrugList.forEach(x=>{
  data.push({
    'NDC-9':x.ndc.substring(0,9),
    'Canister':x.drawerType==='Smart'?'SMART':x.canisterLocation,
    'Drug Name':x.name,
    'Strength':x.strength,
    'Manufacturer':x.manufacturer,
    'NDC-11':x.ndc,
    'CustomerNDC':x.customerDrugNdc,
    'CItem#':x.atpCanisterNumber,
  })
})
this.fileInfo={
  fileName: `test_file_name.xlsx`,
  templates:[{
  data:data,
  sheetName:`test_sheet`
  }]}
this.exportService.exportAsExcelFile(this.fileInfo);
  }

Collectives™ on Stack Overflow

Export Json data to an excel file using Angular 4

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related