CODEWITHSUNDEEP
exceltypescriptangular6xlsx
Unicorn
Unicorn

Reputation: 295

Angular 6 - Formatting Export Excel Sheet

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-

Excel formatting example

Can anyone advice some piece of information on same.

Thanks in advance.

Upvotes: 2

Views: 43605

Answers (2)

Priya Shalini
Priya Shalini

Reputation: 11

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);
}

Upvotes: 0

anurag shahi
anurag shahi

Reputation: 59

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);
    })
  }

Upvotes: 2

Related Questions