How to add data validation list in excel using javascript

Question

> 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 specified value "rejected","sleected" and "on-hold"

 downloadTableData(data, headers) {
  this.dataToDownload = "";
  let dataSourceLength = data.length;
  let rowData = '';
  for (let i = 0; i < dataSourceLength; i++) {
    let line = '';
    for (let key in data[i]) {
      if (line != '') {
        line = line + ','
      }
      line = line + data[i][key];
    }
    rowData = rowData + line + "\r\n";
  }

  // as of now; but based on api data, row data and column dat ashould be done
  this.dataToDownload = this.dataToDownload + headers.join(',') + "\r\n" + rowData;

  if (this.dataToDownload.split('\n').length - 1 >= 1) {
    // const fileName = 'reports-' + new Date();
    const fileName ='Upload_template.xlsx';
    let anchor = document.createElement('a');
    anchor.href = URL.createObjectURL(new Blob([this.dataToDownload], { type: 'text/csv' }));
    anchor.download = fileName + '.csv';
    // anchor.download = fileName;
    // start download
    anchor.click();
  }

}

Cameroun

Answer 1

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) {
  let data: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 worksheet
  let workbook = new Workbook();
  let worksheet = workbook.addWorksheet('Candidate Report');

  //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' } }
  })
  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 = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    fs.saveAs(blob, 'candidate.xlsx');
  })

}

`

Cameroun

is this working on windows excel apps? – Ruthenium

Answer 2

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

Enthusiast

See also learn.microsoft.com/en-us/office/dev/add-ins/excel/… – Liselisetta

Hi Raymond Lu, Your given gist link is not working please update it. – Cameroun

I just had a try on my gist now, it works on my Mac.what error you see? – Enthusiast

i had issues with the git two...the git is a yaml file. Im using Script Lab with Excel for testing the excel.js script. in the menu you can import the yaml. so i just copied and paste to the Import section under "import snippet", then click import. works great! – Demise