如何在 Angular 8 中将下拉列表（带选项）从 JSON 导出到 Excel？

我正在尝试将 JSON 数据导出到具有下拉列表的 Excel。

如果我们将数组作为单元格值传递，是否可以在 Excel 中创建下拉菜单（带选项）？

我正在尝试使用内置库 XLSX 来实现此目的，但如果我在单元格值中传递数组，则无法查看数据。

更新：Jack 提供了一个支持数据验证的 ExcelJS 库，但需要为此进行一些配置。

有没有支持此功能的 Angular 库？

下面是我尝试过的项目的示例。我想根据传递给服务的数据附加多个工作表，并且工作表的名称将从对象中获取。

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

这只是 @ZackReam 解决方案的补充，重点关注您当前的场景

首先，我们首先定义一个数据结构

  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"] }
      ]
    }

接下来我们定义一个服务来根据上述数据动态生成工作簿

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

该服务将动态确定要设置为验证的标题和列

转换数据

我们可以使用下面的方法将您的数据转换为这种结构

  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)

下面是一个示例演示

示例演示