CODEWITHSUNDEEP
angularexport-to-excel
0917237
0917237

Reputation: 961

How Angular Export to Excel with formatting cell?

My Excel Service with method exportAsExcelFile:

    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: 'xls', type: 'array' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
  }

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

my Component with method exportDataToExcel() :

dataToExport: any = [];
exportFileName: string = "TRANSACTION_HISTORY_REPORT";


exportDatatoExcel(){

    this.myAPIService.getData().then(
      (data) => {

        data.forEach((data, index) => {

            this.dataToExport.push({
              no: index + 1,
              total_item_sold: data.detailItem.length,
              total_price: data.totalPrice,

            });
          });

        if(this.dataToExport.length > 0){
          if(this.exportFileName == "") this.exportFileName = "default";
          this.excelService.exportAsExcelFile(this.dataToExport, 
          this.exportFileName);
        }
      }
    );

  }

How to Export that json total_price with formatting Accounting cell in Excel?

Upvotes: 2

Views: 8850

Answers (1)

talhature
talhature

Reputation: 2165

The utility you use does not support cell formatting out of the box. You need to handle it yourself diving a little deeper in sheetjs.

You can iterate on the cells and change the cell.z property accordingly as it stands for cell formatting.

/* new format */
var fmt = "0.00";
/* change cell format of range B2:D4 */
var range = { s: {r:1, c:1}, e: {r:2, c:3} };
for(var R = range.s.r; R <= range.e.r; ++R) {
  for(var C = range.s.c; C <= range.e.c; ++C) {
    var cell = ws[XLSX.utils.encode_cell({r:R,c:C})];
    if(!cell || cell.t != 'n') continue; // only format numeric cells
    cell.z = fmt;
  }
}

here is a fiddle example: https://jsfiddle.net/1ny97xrb/1/

(https://github.com/SheetJS/js-xlsx/issues/966)

Upvotes: 0

Related Questions