安装 node-xlsx

npm install node-xlsx --save

读取 Excel

var xlsx = require('node-xlsx');
 
// 解析得到文档中的所有 sheet
var sheets = xlsx.parse('test.xls');
 
// 遍历 sheet
sheets.forEach(function(sheet){
  console.log(sheet['name']);
  // 读取每行内容
  for(var rowId in sheet['data']){
    console.log(rowId);
    var row=sheet['data'][rowId];
    console.log(row);
  }
});

写入 Excel

var data = [{
  name: 'sheet1',
  data: [
    ['ID', 'Name', 'Score'],
    ['1', 'Michael', '99'],
    ['2', 'Jordan', '98']
  ]
},
{
  name: 'sheet2',
  data: [
    ['AA', 'BB'],
    ['23', '24']
  ]
}
]
var buffer = xlsx.build(data);


// 写入文件
fs.writeFile('a.xlsx', buffer, function (err) {
  if (err) {
    console.log("Write failed: " + err);
    return;
  }
  console.log("Write completed.");
});

控制单元格的宽度

import xlsx from 'node-xlsx';
// Or var xlsx = require('node-xlsx').default;

const dataSheet1 = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];
const dataSheet2 = [[4, 5, 6], [7, 8, 9, 10], [11, 12, 13, 14], ['baz', null, 'qux']];
const range = {s: {c: 0, r:0 }, e: {c:0, r:3}}; // A1:A4
const sheetOptions = {'!merges': [ range ]};

var buffer = xlsx.build([{name: "myFirstSheet", data: dataSheet1}, {name: "mySecondSheet", data: dataSheet2, options: sheetOptions}]); // Returns a buffer

读取的 Excel 的内容可以批量导入到 MySQL。使用(sequelize)bulkCreate函数中的updateOnDuplicate设置，可以批量更新数据。

参考：

https://github.com/mgcrea/node-xlsx

https://www.jianshu.com/p/1c42659f5680

https://javascript.net.cn/article?id=768



真诚赞赏，手留余香

微信支付