exceljs 导入导出 excel 文件

使用 Nodejs 创建工程

安装 exceljs 插件： exceljs中文教程

cnpm install --save exceljs

创建 export.js 文件

const { Workbook } = require("exceljs");
async function export_excel() {
  const workbook = new Workbook();
  workbook.addWorksheet('export');
  await workbook.xlsx.writeFile(`./upload/export.xlsx`);
}
export_excel();

执行命令：node export.js

在 upload 文件夹下生成有一个 export sheet页的没有内容的 export.xlsx 文件

下面生成一些复杂的 excel 文件

const { Workbook } = require("exceljs");
async function export_excel() {
  const workbook = new Workbook();
  const export_sheet = workbook.addWorksheet("export");
  const header_name = ["No.", "商品ID", "商品名", "商品种类", "商品价格"];
  const header_comment = [
    "",
    "#数字",
    "#字符串(最长128位)",
    "#字符串(最长40位)",
    "#数字",
  ];
  const export_data = [
    {
      goodsID: 2001,
      goodsName: "苹果",
      goodsKind: "水果",
      price: 5,
    },
    {
      goodsID: 2002,
      goodsName: "白梨",
      goodsKind: "水果",
      price: 3,
    },
    {
      goodsID: 2003,
      goodsName: "韭菜",
      goodsKind: "蔬菜",
      price: 2,
    },
    {
      goodsID: 2004,
      goodsName: "茄子",
      goodsKind: "蔬菜",
      price: 4,
    },
  ];
  const export_width = [5, 20, 20, 20, 20];
  for (let i = 1; i < header_name.length + 1; i++) {
    export_sheet.getColumn(i).width = export_width[i - 1];
    export_sheet.getCell(1, i).value = header_name[i - 1];
    export_sheet.getCell(1, i).fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "f59e5d" },
    };
    export_sheet.getCell(2, i).value = header_comment[i - 1];
    export_sheet.getCell(2, i).fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "f59e5d" },
    };
  }
  export_sheet.getRow(2).height = 40;
  // データ挿入
  for (let i = 3; i < export_data.length + 3; i++) {
    export_sheet.getCell(i, 1).value = i - 2;
    export_sheet.getCell(i, 2).value = export_data[i - 3].goodsID;
    export_sheet.getCell(i, 3).value = export_data[i - 3].goodsName;
    export_sheet.getCell(i, 4).value = export_data[i - 3].goodsKind;
    export_sheet.getCell(i, 5).value = export_data[i - 3].price;
  }
  // 罫線挿入
  for (let i = 1; i < export_data.length + 3; i++) {
    for (let j = 1; j < header_name.length + 1; j++) {
      export_sheet.getCell(i, j).border = {
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
    }
  }
  await workbook.xlsx.writeFile(`./upload/export.xlsx`);
}
export_excel();

生成效果：

用 xlsx 读取文件，因为它的 sheet_to_json 可以将表头和数据合并为对象

安装 xlsx 插件

const { readFile, utils } = require('xlsx');
async function import_excel() {
  const workbook = readFile(`./upload/export.xlsx`);
  const import_date = await readXlsxSheet(workbook, 'export');
  console.log(import_date);
}
async function readXlsxSheet(workbook, sheetName) {
  try {
    const xlsxSheet = workbook.Sheets[sheetName];
    const range = utils.decode_range(xlsxSheet['!ref']);
    const columnHeader = [];
    for (let columnCnt = 1; columnCnt <= range.e.c; columnCnt++) {
      const ADR = utils.encode_cell({ c: columnCnt, r: 0 });
      columnHeader.push(xlsxSheet[ADR].v);
    }
    const jsonData = utils.sheet_to_json(xlsxSheet);
    const rowData = [];
    for (let rowCnt = 1; rowCnt < jsonData.length; rowCnt++) {
      rowData.push(jsonData[rowCnt]);
    }
    return {head: columnHeader, data: rowData};
  } catch {
    return {head: [], data: []};
  }
}
import_excel();