How To Add Data Validation List In Excel Using Javascript

> I am creating and downloading excel file with data that I have gotten in JSON format. Now I want to add status column in last and provide list data validation there with three

Solution 1:

Here is the sample code how to apply data validation

const nameSourceRange = context.workbook.worksheets.getItem("Status").getRange("A1:A3");

let approvedListRule = {
  list: {
    inCellDropDown: true,
    source: nameSourceRange
  }
};
nameRange.dataValidation.rule = approvedListRule;

I created a gist for you to demo how to add the approved status. https://gist.github.com/lumine2008/827ab26a65b76a5826331d960323c43b

Solution 2:

I found the solution with the help of excel.js and file-saver.js.

import { Workbook } from'exceljs';
import * as fs from'file-saver';
generateExcel(list,header) {
  letdata:any = [];
  for(let i=0;i<list.length;i++){
    let arr = [list[i].requisitionId,list[i].applicationid, list[i].candidateid, list[i].unitcode];
    data.push(arr);
  }
  console.log(data);
  //Create workbook and worksheetlet workbook = newWorkbook();
  let worksheet = workbook.addWorksheet('Candidate Report');

  //Add Header Rowlet 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' } }
  })
  worksheet.getColumn(3).width = 30;
  data.forEach(d => {
    let row = worksheet.addRow(d);
  }
  );
  list.forEach((element,index) =>{
    worksheet.getCell('E'+(+index+2)).dataValidation = {
      type: 'list',
      allowBlank: true,
      formulae: ['"Selected,Rejected,On-hold"']
  };
  })
  //Generate Excel File with given name
  workbook.xlsx.writeBuffer().then((data) => {
    let blob = newBlob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    fs.saveAs(blob, 'candidate.xlsx');
  })

}

`