ExcelJS Data under specific date column

Question

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

@Spankied too many dependencies involved. YOu may check out this image of the related file to get an idea. https://drive.google.com/file/d/1McfIO-yaGIQct_bnOxilneOrWzRlGFYa/view?usp=sharing — Ehsan Nissar, Dec 20 '21 at 12:17
I'm sure this looks like quite an easy problem to solve technically, but I am very confused by the question itself. Perhaps you could rephrase it and gain more useful feedback. Because honestly, I don't understand the question. — html_programmer, Dec 22 '21 at 23:26

score 0 · Answer 1 · answered Dec 26 '21 at 12:00

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

ExcelJS Data under specific date column

1 Answers1