How to export dropdown list(with options) from JSON to excel in Angular 8?

This is just an addition to @ZackReam solution with a focus on your current scenario

To begin, we first define a data structure

  workbookData = [
    {
      workSheet: "data 1",
      rows: [
        { eid: "1", ename: "John", esal: ["val 1", "val2", "val 3"] },
        { eid: "4", ename: "Parker", esal: ["val 1", "val2", "val 3"] },
        { eid: "5", ename: "Iron", esal: ["val 1", "val2", "val 3"] }
      ]
    },
    {
      workSheet: "data 2",
      rows: [
        { eid: "9", ename: "Doe", esal: ["val 1", "val2", "val 3"] },
        { eid: "10", ename: "Peter", esal: ["val 1", "val2", "val 3"] },
        { eid: "11", ename: "Man", esal: ["val 1", "val2", "val 3"] }
      ]
    }

Next we define a service to generate a workbook dynamically from the above data

import { Injectable } from "@angular/core";
import * as FileSaver from "file-saver";
import * as ExcelJS from "exceljs/dist/exceljs.min.js";

const EXCEL_TYPE =
  "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
const EXCEL_EXTENSION = ".xlsx";

@Injectable()
export class ExcelService {
  constructor() {}

  public async exportAsExcelFile(workbookData: any[], excelFileName: string) {
    const workbook = new ExcelJS.Workbook();

    workbookData.forEach(({ workSheet, rows }) => {
      const sheet = workbook.addWorksheet(workSheet);
      const uniqueHeaders = [
        ...new Set(
          rows.reduce((prev, next) => [...prev, ...Object.keys(next)], [])
        )
      ];
      sheet.columns = uniqueHeaders.map(x => ({ header: x, key: x }));

      rows.forEach((jsonRow, i) => {
        let cellValues = { ...jsonRow };

        uniqueHeaders.forEach((header, j) => {
          if (Array.isArray(jsonRow[header])) {
            cellValues[header] = "";
          }
        });
        sheet.addRow(cellValues);
        uniqueHeaders.forEach((header, j) => {
          if (Array.isArray(jsonRow[header])) {
            const jsonDropdown = jsonRow.esal;
            sheet.getCell(
              this.getSpreadSheetCellNumber(i + 1, j)
            ).dataValidation = {
              type: "list",
              formulae: [`"${jsonDropdown.join(",")}"`]
            };
          }
        });
      });
    });

    const buffer = await workbook.xlsx.writeBuffer();
    this.saveAsExcelFile(buffer, excelFileName);
  }

  private getSpreadSheetCellNumber(row, column) {
    let result = "";

    // Get spreadsheet column letter
    let n = column;
    while (n >= 0) {
      result = String.fromCharCode((n % 26) + 65) + result;
      n = Math.floor(n / 26) - 1;
    }

    // Get spreadsheet row number
    result += `${row + 1}`;

    return result;
  }

  private saveAsExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], {
      type: EXCEL_TYPE
    });
    FileSaver.saveAs(
      data,
      fileName + "_export_" + new Date().getTime() + EXCEL_EXTENSION
    );
  }
}

The service will dynamically determine the headers and the columns to set as validation

Transforming the data

We can transform your data to this structure using below

  transform (data) {
    const noOfRowaToGenerate = 10;
    return data.map(({name, values}) => {
      const headers = values.reduce((prev, next) => 
        ({...prev, [next.header]: Array.isArray
        (next.value) ? next.value.map(({name}) => name): next.value}), {})
      return {
        workSheet: name,
        rows: Array(noOfRowaToGenerate).fill(headers)
      }
    })
  }
   workbookData = this.transform(this.data1)

Below is a sample demo

Sample Demo

According to this issue in SheetJS (package: xlsx), data validation is a pro-only feature.

We offer this in the Pro compendium. Since companies have paid for the features already, it would be unfair to them if we turned around and made it available as open source.

Another library you could check out is ExcelJS (package: exceljs). It runs in the browser and has support for data validation. You would have to do a bit of manual mapping of your data to get your data in the format that the library expects, but it is possible.

Here is an example Stackblitz.

You can try do it this works using the same library exceljs and filesaver.js from you example

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

}

My reference is this How to add data validation list in excel using javascript