CODEWITHSUNDEEP
excelangulartypescriptwebpackexport-to-excel
S2K
S2K

Reputation: 1285

How to implement Export data to excel feature in angular 4 application with styles and formatting?

We have used Swimlane-ngxtable for displaying data.
I want to export the data to excel.

I have tried following code :

private wrapAndCenterCell(range: XLSX.Range, worksheet: XLSX.WorkSheet) {
const wrapAndCenterCellStyle = {
    alignment: { wrapText: true, vertical: 'center', horizontal: 'center' },
    font: { bold: true, color: 'red' }
};

for (var R = range.s.r; R <= range.e.r; ++R) {
    for (var C = range.s.c; C <= range.e.c; ++C) {
        var cell_address = { c: C, r: R };
        var cell_ref = worksheet[XLSX.utils.encode_cell(cell_address)];
        this.setCellStyle(cell_ref, wrapAndCenterCellStyle);
        }
    }

}

private setCellStyle(cell: XLSX.CellObject, style: {}) {
    cell.s = style;
}

private saveAsExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], {
        type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"
    });
    FileSaver.saveAs(data, fileName + '_export_' + new Date().getTime() + ".xlsx");
}

I am able to export the data but no formatting is applied.

I want to know the optimized way to do it.

Upvotes: 1

Views: 3057

Answers (1)

Sagar M
Sagar M

Reputation: 1376

Follow this instruction if you want to export data to excel file in your project.

1.Add file-saver and xlsx dependencies

npm install file-saver --save
npm install xlsx --save

2.Implement ExcelService

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 ExcelService {

  constructor() { }

  public exportAsExcelFile(json: any[], excelFileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_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 + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
  }

}

Upvotes: 2

Related Questions