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
