前端处理

https://github.com/SheetJS/sheetjs

node后端处理

  1. const fs = require('fs');
  2. let xlsx = require('xlsx');
  3. try {
  4.     // const stats = fs.statSync('demo.xlsx'); 文件状态
  5.     // fs.readFile('./readme.txt', { encoding: 'utf8', flag: 'r' }, (err, data)=> {
  6.     //     if (err) throw err;
  7.     //     console.log(data,'--------------readme.txt')
  8.     // });
  9.     let workbook = xlsx.readFile('demo.xlsx'); //workbook就是xls文档对象
  11.     let sheetNames = workbook.SheetNames; //获取表名
  13.     let sheet = workbook.Sheets[sheetNames[0]]; //通过表明得到表对象
  15.     let data =xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json
  16.     console.log(`表格-${sheetNames[0]}`, data)
  17. } catch(error) {
  18.     // 处理错误
  19.     console.error(error)
  20. }
  22. //写入
  23. let json = [
  24.     { Name: 'name_01', Age: 21, Address: 'address_01' },
  25.     { Name: 'name_02', Age: 22, Address: 'address_02' },
  26.     { Name: 'name_03', Age: 23, Address: 'address_03' },
  27.     { Name: 'name_04', Age: 24, Address: 'address_04' },
  28.     { Name: 'name_05', Age: 25, Address: 'address_05' }, ];
  30. let ss = xlsx.utils.json_to_sheet(json); //通过工具将json转表对象
  31. let keys = Object.keys(ss).sort(); //排序 [需要注意，必须从A1开始]
  33. let ref = keys[1]+':'+keys[keys.length - 1]; //这个是定义一个字符串 也就是表的范围[A1:C5]
  35. let workbook = { //定义操作文档
  36.     SheetNames:['nodejs-sheetname'], //定义表明
  37.     Sheets:{
  38.         'nodejs-sheetname':Object.assign({},ss,{'!ref':ref}) //表对象[注意表明]
  39.     },
  40. }
  42. xlsx.writeFile(workbook,"./newWrite.xls", {type:"array"}); //将数据写入文件,不传array默认99条，现在可支持999条