[xlsx]视同xlsx+file-saver实现excel文件

自己mark一下,勿喷

版本："xlsx": "^0.18.5，"file-saver": "2.0.5"

import FileSaver from "file-saver";
import XLSX from "xlsx";
// 如果执行不报错，也不生效，可以用如下方式
import XLSX from "xlsx/dist/xlsx.full.min";

两种方式

image.png

//方式2
const prez = [{ projectName: "adasd" }, { projectName: "adasd22" }];
const rows = prez.map((item, index) => ({
    index: index+1,
    projectName: item.projectName || "",
}));
/* generate worksheet and workbook */
const worksheet = XLSX.utils.json_to_sheet(rows);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");
/* fix headers */
XLSX.utils.sheet_add_aoa(worksheet, [["序号", "项目名"]], {origin: "A1"});
/* create an XLSX file and try to save to Presidents.xlsx */
XLSX.writeFile(workbook, "Presidents.xlsx");

//方式3
const tableData = [
  {projectName:"P1",recordTime:"2022-07-11"},
  {projectName:"P2",recordTime:"2022-07-12"},
];
const aoa = [["序号", "项目名", "日期"]];
tableData.forEach((item, index) => {
aoa.push([
  index + 1,
  item.projectName || "",
  item.recordTime || ""
]);
});
var worksheet = XLSX.utils.aoa_to_sheet(aoa);
var workBook = XLSX.utils.book_new();

XLSX.utils.book_append_sheet(workBook, worksheet, "项目记录");
// XLSX.writeFile(workBook, "项目记录.xlsx");
var wopts = { bookType: "xlsx", bookSST: false, type: "array" };
var wbout = XLSX.write(workBook, wopts);

合并单元格

image.png

多个sheet

/*
使用的包如下
     "file-saver": "2.0.5",
      "xlsx": "^0.18.5",
      "xlsx-style": "^0.8.13"
import FileSaver from "file-saver";
import XLSX from "xlsx/dist/xlsx.full.min";
*/

const tableData = response.rows || []; //原数据

const projectMap = new Map();
tableData.forEach((item, index) => {
let projectName = item.projectName || ""; // sheet名称，根据该字段进行分组
let itemArr = [
  item.projectName || "",
  item.recordTime || "",
  item.recordByName || "",
  item.content || "",
  item.losttime || ""
];
if (projectMap.get(projectName)) {
  projectMap.get(projectName).push(itemArr);
} else {
  projectMap.set(projectName, [
    ["项目名称", "日期", "人员", "日志内容", "工时"],
    itemArr,
  ]);
}
});
//生成
var workBook = XLSX.utils.book_new();
let index = 1;
projectMap.forEach((v, k) => {
// 参数说明 XLSX.utils.book_append_sheet(workBook,wookSheet,sheetName)
XLSX.utils.book_append_sheet(
  workBook,
  XLSX.utils.aoa_to_sheet(v),
  index + ". " + k
);
index++;
});

var wopts = { bookType: "xlsx", bookSST: false, type: "array" };
var wbout = XLSX.write(workBook, wopts);
FileSaver.saveAs(
new Blob([wbout], { type: "application/octet-stream" }),
"项目日志记录.xlsx"
);

 workbook2blob(workbook) {
      // 生成excel的配置项
      var wopts = {
        // 要生成的文件类型
        bookType: "xlsx",
        // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        bookSST: false,
        type: "binary",
      };
      var wbout = XLSX.write(workbook, wopts);
      // 将字符串转ArrayBuffer
      function s2ab(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
        return buf;
      }
      let buf = s2ab(wbout);
      var blob = new Blob([buf], {
        type: "application/octet-stream",
      });
      return blob;
    },