Angular 6 - Formatting Export Excel Sheet

Question

i would like to apply styling in excel sheet to represent the data. Here is my code

 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 {

    var aoa = this.create2DArray(json, excelFileName);
    const worksheet: XLSX.WorkSheet = XLSX.utils.aoa_to_sheet(aoa);
    console.log('worksheet',worksheet);
    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    this.saveAsExcelFile(excelBuffer, excelFileName);

  }

I am able to download the file along with data but not in formatted style.

Formatted Excel-

Can anyone advice some piece of information on same.

Thanks in advance.

npm install xlsx-style --save The second answer: https://stackoverflow.com/questions/39177183/how-to-export-json-to-csv-or-excel-angular-2 — Len Tran, Nov 07 '18 at 02:54
Have you been able to fix it, I also have a requirement of formatting a column as text instead of String. I added a single quote still its not formatted as text. — Olakunle Awotunbo, Dec 17 '18 at 12:10

anurag shahi · Answer 1 · 2019-01-04T12:57:47.833

Hi you can style xlsx sheet using 'exceljs' library. You can style or customize your sheet as you want.

Step 1: npm install exceljs, npm install file-saver

Step 2: import { Workbook } from 'exceljs'; import * as fs from 'file-saver';

Add these two variables in ts file where you want to use these

const EXCEL_TYPE = 'application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet;charset=UTF-8';

const EXCEL_EXTENSION = '.xlsx';

Step 3: Add below code in tsconfig.json

"paths": {
      "exceljs": [
        "node_modules/exceljs/dist/exceljs.min"
      ]
    }

public exportAsExcelFile(json: any[], excelFileName: string, headersArray: any[]): void {
    //Excel Title, Header, Data
    const header = headersArray;
    const data = json;
    //Create workbook and worksheet
    let workbook = new Workbook();
    let worksheet = workbook.addWorksheet(excelFileName);
    //Add Header Row
    let headerRow = worksheet.addRow(header);
    // Cell Style : Fill and Border
    headerRow.eachCell((cell, number) => {
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFFFFF00' },
        bgColor: { argb: 'FF0000FF' }
      }
      cell.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } }
    })
    // Add Data and Conditional Formatting
    data.forEach((element) => {
      let eachRow = [];
      headersArray.forEach((headers) => {
        eachRow.push(element[headers])
      })
      if (element.isDeleted === "Y") {
        let deletedRow = worksheet.addRow(eachRow);
        deletedRow.eachCell((cell, number) => {
          cell.font = { name: 'Calibri', family: 4, size: 11, bold: false, strike: true };
        })
      } else {
        worksheet.addRow(eachRow);
      }
    })
    worksheet.getColumn(3).width = 15;
    worksheet.getColumn(4).width = 20;
    worksheet.getColumn(5).width = 30;
    worksheet.getColumn(6).width = 30;
    worksheet.getColumn(7).width = 10;
    worksheet.addRow([]);
    //Generate Excel File with given name
    workbook.xlsx.writeBuffer().then((data) => {
      let blob = new Blob([data], { type: EXCEL_TYPE });
      fs.saveAs(blob, excelFileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
    })
  }

@KatharineOsborne fs refers to FileSaver, `import * as FileSaver from 'file-saver';` He just named it 'fs' instead of 'FileSaver' — masu9, Jun 19 '19 at 15:16

Priya Shalini · Answer 2 · 2020-07-29T10:25:58.443

You can style the sheets using the exceljs library in angular. I had to make the cell headers capital in one of my implementations. I added the below snippet to format the headers to uppercase by iterating through the loop after reading the worksheet.

public exportAsExcelFile(json, excelFileName: string): void {
  const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
  var range = XLSX.utils.decode_range(worksheet['!ref']);
  for (var C = range.s.r; C <= range.e.c; ++C) {
    var address = XLSX.utils.encode_col(C) + '1'; // <-- first row, column number C
    if (!worksheet[address]) continue;
    worksheet[address].v = worksheet[address].v.toUpperCase();
  }
  const workbook: XLSX.WorkBook = {
    Sheets: { data: worksheet },
    SheetNames: ['data'],
  };
  const excelBuffer: any = XLSX.write(workbook, {
    bookType: 'xlsx',
    type: 'array',
  });
  this.saveAsExcelFile(excelBuffer, excelFileName);
}

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. — Dima Kozhevin, Jul 28 '20 at 10:01

Angular 6 - Formatting Export Excel Sheet

2 Answers2