Exceljs : iterate each cell of each row and column

Question

I want to put thick border in all my cells. this is an angular project, I am using typescript.

I can do this for 1 cell,

worksheet.getCell('A1').border = {
  top: { style: 'thick' },
  left: { style: 'thick' },
  bottom: { style: 'thick' },
  right: { style: 'thick' }
};

But I want to do something like 2 nested for loops . For each row , make each cell thick

Here is I tried : app.component.ts

import { Component, OnInit } from '@angular/core';
import { DataService } from '../data.service';
import * as jspdf from 'jspdf';
import html2canvas from 'html2canvas';
// import * as XLSX from 'xlsx';

import * as ExcelJS from 'exceljs';
import * as FileSaver from 'file-saver';

import { ViewChild, ElementRef } from '@angular/core';



@Component({
  selector: 'app-items-report',
  templateUrl: './items-report.component.html',
  styleUrls: ['./items-report.component.css']
})
export class ItemsReportComponent implements OnInit {
  purchases: any;
  constructor(private dataService: DataService) {
    this.GetPurchases();
  }

  ngOnInit(): void {
  }

  async GetPurchases() {
    const response = await this.dataService.GetPurchases();
    const dataService = await response.json();
    this.purchases = dataService;
  }

  downloadPDF() {
    const data = document.getElementById('purchaseTable');  // Id of the table
    html2canvas(data).then(canvas => {
      // Few necessary setting options
      const imgWidth = 208;
      const pageHeight = 295;
      const imgHeight = canvas.height * imgWidth / canvas.width;
      const heightLeft = imgHeight;

      const contentDataURL = canvas.toDataURL('image/png');
      // Your 1st parameter (landscape [l] or portrait [p]) determines what becomes the width and the height.
      const pdf = new jspdf('p', 'mm', 'a4'); // A4 size page of PDF
      const position = 0;
      /* addImage explained below:
      param 1 -> image in code format
      param 2 -> type of the image. SVG not supported. needs to be either PNG or JPEG.
      all params are specified in integer
      param 3 -> X axis margin from left
      param 4 -> Y axis margin from top
      param 5 -> width of the image
      param 6 -> height of the image
      */
      // pdf.addImage(contentDataURL, 'PNG', 0, position, imgWidth, imgHeight);
      // pdf.addImage(contentDataURL, 'PNG', 18, 30, imgWidth - 17, imgHeight);
      pdf.addImage(contentDataURL, 'PNG', 18, 30, imgWidth - 21, imgHeight);

      pdf.save('MYPdf.pdf'); // Generated PDF
    });
  }
  downloadExcel() {


    const date = new Date().toISOString().slice(0, 10).split('-').reverse().join('/');
    console.log(date);



    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('My Sheet');

    worksheet.columns = [
      { header: 'Id', key: 'id', width: 10},
      { header: 'Name', key: 'name', width: 32 },
      { header: 'Quantity', key: 'quantity', width: 15 },
      { header: 'Rate', key: 'rate', width: 15 },
      { header: 'Date', key: 'date', width: 15 },
      { header: 'Total', key: 'total', width: 15 }
    ];




    for (const purchase of this.purchases) {
      worksheet.addRow({
        id: purchase.item_id ,
        date: purchase.item_purchase_date.toString().slice(0, 10).split('-').reverse().join('/'),
        name: purchase.item_name,
        quantity: purchase.item_quantity,
        rate: purchase.item_rate,
        total: purchase.item_rate * purchase.item_quantity
       })
      .alignment = { horizontal: 'left' };
    }

    worksheet.getRow(1).font = { bold: true };

// Iterate over all rows (including empty rows) in a worksheet
worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
  console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
  row.eachCell({ includeEmpty: true }, (cell, rowNumber) => {
    // ...please make my cell thick here
    // i cant no longer write a1 or b1
    // i need to access all cells - including empty cells

  });
});




   book.xlsx.readFile('export.xlsx');


}

I need to make each of my cells thick, inside for loop. So please help me how to access each cell in a loop without writing a1 or b1

Answer 1

To put border in all cells :- exceljs version 1.12.0

worksheet.columns.forEach((col) => {
        col.style.font = { name: 'Comic Sans MS' };
        col.style.border = { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } };
    })

Answer 2

Worksheet gives you a columns property on which you can iterate and use it like :-

worksheet.columns.forEach(column => {
      column.border = {
        top: { style: "thick" },
        left: { style: "thick" },
        bottom: { style: "thick" },
        right: { style: "thick" }
      };
    });