CODEWITHSUNDEEP
javascriptnode.jsexceljs
Ehsan Nissar
Ehsan Nissar

Reputation: 683

ExcelJS Data under specific date column

I am creating an excel sheet using exceljs package in nodejs application. I have an array of headers that I've generated like this.

const monthsArray = arrDays.map((str) => (
   { header: str, key: str, width: 10 } // Example { header: "12-15-2021", key: "12-15-2021", width: 10}
));

let headers = [
   { header: "Name", key: "Name", width: 10 },
   { header: "Phone", key: "Phone", width: 13 },
   { header: "CNIC", key: "CNIC", width: 13 },
   { header: "City", key: "City", width: 10 },
   ...monthsArray
];

Now I am running an aggregate which will return me records of users and along with that I will be getting a nested object inside it which will have records of attendance on a particular day soemthing like this createdAt: "2021-12-15T19:56:37.984Z" and present: true. Now the headers I am able to print out correctly in the first row with dates on top of each column and before that the said data like name, phone, city etc. However I need to know how I can show correct attendance records under the correct column? Such that the true must be written under the column 12-15-2021 for a particular user.

Here is how I am generating excel file

generateAndUploadExcel: async function (worksheetName, headers, rows) {
    var workbook = new Excel.Workbook();
    var sheet = workbook.addWorksheet(worksheetName);
  
    sheet.columns = headers;
    sheet.addRows(rows);
    try {
      var bufferFile = await workbook.xlsx.writeBuffer();
      const fileExt = "xlsx";
      const aws = new FileUploadService(
        awsConfig.accessKeyId,
        awsConfig.secretAccessKey,
        awsConfig.bucketName
      );
      const upload = await aws.uploadBuffer(
        "download-center",
        bufferFile,
        fileExt,
        worksheetName
      );
      return upload;
    } catch (err) {
      throw new Error(err);
    }
  }

Upvotes: 5

Views: 1912

Answers (1)

sandeep.kgp
sandeep.kgp

Reputation: 886

Firstly, convert the db date to the exact same format which was used to create date header (key) in order to access specific column in exceljs sheet and while adding the row just do row['date-key']=true or false

For e.g. dateheader = { header: "12-15-2021", key: "12-15-2021", width: 10} here key is 12-15-2021 and date from DB in attendance is 2021-12-15T19:56:37.984Z, convert this to 12-15-2021 and then just do

let row={}
row['name']=user.name; 
row['city']=user.city; 
row[convertedDate]=true;
sheet.addRow(row);

Upvotes: 0

Related Questions