使用node-xlsx读取生成excel文件

node-xlsx是基于js-xlsx的实现的excel解析、生成器。这里我们通过node-xlsx实现excel的读取和生成。

安装

$ npm install node-xlsx -S

解析 excel文件

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

// Parse a buffer
const workSheetsFromBuffer = xlsx.parse(fs.readFileSync(`${__dirname}/myFile.xlsx`));
// Parse a file
const workSheetsFromFile = xlsx.parse(`${__dirname}/myFile.xlsx`);

生成excel文件

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

const data = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];
var buffer = xlsx.build([{name: "mySheetName", data: data}]); // Returns a buffer

自定义excel格式

import xlsx from 'node-xlsx';
 // Or var xlsx = require('node-xlsx').default; 
// 自定义列宽
const data = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']] 
const options = {'!cols': [{ wch: 6 }, { wch: 7 }, { wch: 10 }, { wch: 20 } ]}; 
var buffer = xlsx.build([{name: "mySheetName", data: data}], options); // Returns a buffer
// 跨行设置
const data = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];
const range = {s: {c: 0, r:0 }, e: {c:0, r:3}}; // A1:A4
const options = {'!merges': [ range ]};
var buffer = xlsx.build([{name: "mySheetName", data: data}], options); // Returns a buffer
// 跨页合并单元格
const data = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']]; 
const range = {s: {c: 0, r:0 }, e: {c:0, r:3}}; // A1:A4 
const options = {'!merges': [ range ]}; 
var buffer = xlsx.build([{name: "mySheetName", data: data}], options); // Returns a buffer