Angular XLSX-在同一XL文件中创建多个纸

Question

我需要下载带有3张纸（说明，填充数据，dataOptions）

指令表的Excel文件的标题，其中包含大胆文本：“指令” 例如： - 1.请仔细填写选项 2。从“ Sheet-Data选项”等复制选定的下拉值。

数据填充表将用于填写值

数据选项表可能会保留下拉选项

Ex：Indian， 英国 ...

在TS中，我添加了这样的添加，并且从以下代码中，它生成了2张带有说明，数据填充标头的表格。但是，将诸如大胆，单元格宽度之类的样式提供给其他方法？

    arr: Array<any>;
    arr2: Array<any>;
    textData: any = 'this is text message.....';
  constructor() {
    this.arr = [
      { name: 'Moran', role: 'back' },
      { name: 'Alain', role: 'front' },
      { name: 'Tony', role: 'back' },
      { name: 'Mike', role: 'back' },
      { name: 'Abo', role: 'back' },
      { name: 'Toni', role: 'back' },
    ];
    this.arr2 = [
      { instructions: `1. Use the "Template" sheet to fill.` },
      { instructions: `2. Use the "Template" sheet to fill 2.` }
    ];
  }



 exportToExcel($event) {
    const fileName = 'test.xlsx';
    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    var ws1 = XLSX.utils.json_to_sheet(this.arr2);
    XLSX.utils.book_append_sheet(wb, ws1, 'test1');

   const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(this.arr);
   XLSX.utils.book_append_sheet(wb, ws, 'test');
   XLSX.writeFile(wb, fileName);

}

Answer 1

我认为这将帮助您实现目标。 多个工作表

//package install
npm i --save exceljs
npm i --save file-saver
npm i --save-dev @types/file-saver


// import package
import { Workbook } from 'exceljs';
import * as fs from 'file-saver';


jsonToExcel(array): void {
const files = this.groupedBy(array, ['fileID']);
for (const file of files) {
  const fileName = file?.fileName;
  const sheets = this.groupedBy(file?.items, ['sheet']);
  const workbook = new Workbook();
  sheets.forEach(d => {
    const sheet = d?.sheet; // sheet names
    const excelData = d?.items.map(({ fileID, sheet, fileName, ...rest }) => ({ ...rest }));// data of each sheets
    const worksheet = workbook.addWorksheet(sheet);
    worksheet.addRow(Object.keys(excelData[0]));
    excelData.forEach((d: any) => {
      worksheet.addRow(Object.values(d));
    });
  });
  workbook.xlsx.writeBuffer().then((excel) => {
    const blob = new Blob([excel], {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    });
    fs.saveAs(blob, fileName + '.xlsx');
  });
}
}

groupedBy(arr: any[] = [], keys: any[] = []): any[] {
if (arr?.length === 0) {
  return [];
}
const result = [...arr?.reduce((r, o) => {
  const key = keys?.map(x => o[x]).join('-');
  const data = r.get(key) || Object.assign({}, o, {
    items: []
  });
  data?.items?.push(o);
  return r.set(key, data);
}, new Map()).values()];
return result;}

let data: any[] = [
  { fileID: "1", fileName: "aeam", sheet: "sheet11", name: 'aaaa', role: 'bbbbb' },
  { fileID: "1", fileName: "aeam", sheet: "sheet11", name: 'aaaa', role: 'bbbbb' },
  { fileID: "1", fileName: "aeam", sheet: "sheet12", name: 'aaaa', role: 'bbbbb' },
  { fileID: "1", fileName: "aeam", sheet: "sheet12", name: 'aaaa', role: 'bbbbb' },
  { fileID: "2", fileName: "aeam2", sheet: "sheet21", name: 'cccc', role: 'ffff' },
  { fileID: "2", fileName: "aeam2", sheet: "sheet21", name: 'cccc', role: 'ffff' },
  { fileID: "2", fileName: "aeam2", sheet: "sheet22", name: 'dddd', role: 'gggg' },
  { fileID: "2", fileName: "aeam2", sheet: "sheet22", name: 'dddd', role: 'gggg' },
];
jsonToExcel(data);

Answer 2

这就是我能够在同一工作簿中添加多张纸的方式。

const outboundWorksheet: xlsx.WorkSheet = xlsx.utils.json_to_sheet(outboundPerformanceData);
const inboundWorksheet: xlsx.WorkSheet = xlsx.utils.json_to_sheet(inboundPerformanceData);

const workbook = xlsx.utils.book_new();
      xlsx.utils.book_append_sheet(workbook, inboundWorksheet, 'Inbound Performance');
xlsx.utils.book_append_sheet(workbook, outboundWorksheet, 'Outbound Performance');

const excelBuffer: Xlsx = xlsx.writeXLSX(workbook, { bookType: 'xlsx', type: 'base64' });