如何在Angular 8中将JSON下拉列表(带有选项)从excel导出到excel?

问题描述

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

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

如果我们将数组作为单元格值传递，是否可以在excel中创建下拉列表(带有选项)?

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

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

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.

更新:杰克(Jack)提供了一个库ExcelJS，该库支持数据验证，但需要为此进行一些配置.

是否有支持此功能的Angular库?

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

推荐答案

这只是@ZackReam解决方案的补充，重点是您当前的情况

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

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

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)

下面是一个示例演示

示例演示