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

Question

I am trying to export JSON data to excel which has a dropdown list.

Is it possible to create the dropdown(with options) in excel if we pass an array as a cell value?

I am trying to use the inbuilt library XLSX to achieve this but not able to see data if I pass the array in the cell value.

Update: Jack Provided one library ExcelJS that supports the data validation but needs to do some configurations for that.

Is there any Angular Library that supports this feature?

Below is an example of the project which I tried. I would like to append multiple sheets based on data passed to the service and names of sheets will be taken from the objects.

https://stackblitz.com/edit/angular6-export-xlsx-b4al4p

Answer 1

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