阅读 186

node将excel的表格按照某一列分组拆分成多个excel表格

  1. 拿到所有班级的成绩按照班级生成表格发给每个老师
  2. 有了所有人工资后按照每个人生成工资表发放到个人
  3. 从数据中心拿到汇总数据后分发给各个单位

image.png

image.png

const XLSXWriter = require('xlsx-writestream');
const powXLSX = require('xlsx-extract').XLSX;
const fs = require('fs');

const fileName = './test.xlsx';
const groupName = "班级";

const generateTable = (json, name) => {
  const newFile = `./result/${name}.xlsx`
  console.log(`${name}.xlsx 有 ${json.length} 条数据`)

  const writer = new XLSXWriter(newFile, {} /* options */);

  writer.getReadStream().pipe(fs.createWriteStream(newFile));
  json.forEach(item => {
    writer.addRow(item)
  })
  writer.finalize();
}

let title = []
let groupIndex = 0;
const data = []
let groupArr = [];
new powXLSX().extract(fileName, { sheet_all: true }) // 读取文件所有sheet,默认只读取第一张sheet,参数配置如下
  .on('sheet', function (sheet) {
    console.log('sheet', sheet);  // sheet is array [sheetname, sheetid, sheetnr]
  })
  .on('row', function (row) {
    if (!title.length) {
      title = row
      groupIndex = row.findIndex(e => e === groupName)
    } else {
      console.log(data.length + 1)
      const value = {}
      title.forEach((item, index) => value[item] = row[index])
      data.push(value);
      groupArr = [...new Set([...groupArr, row[groupIndex]])]
    }
  })
  .on('cell', function (cell) {
    // console.log('cell', cell); //cell is a value or null
  })
  .on('error', function (err) {
    console.error('error', err);
  })
  .on('end', function (err) {
    console.log(`共 ${data.length} 条数据`)
    console.log(`应生成 ${groupArr.length} 个表`)
    groupArr.forEach(name => {
      generateTable(data.filter(item => item[groupName] === name), name)
    })
  });

复制代码

image.png

文章分类
前端
文章标签