@zdhsoft/nodexlsx

• 之前一直使用node-xlsx这个来生成excel文件。大多数的情况下，这个完全胜任。前端时间，要求输出合并单元格，这个支持，然后要求居中，就嗝屁了~。没有办法，只好去找了。

• 最后找到一个xlsx-js-style，符合要求，与node-xlsx一样，都是基于xlsx的库。但是我的代码都是使用node-xlsx的，批量替换会改很多代码。于是，我fork了node-xlsx，然后用xlsx-js-style替换了xlsx，再把xlsx-js-style全部导出，设置单元的时候，这样就可以用代码提示，不用盳猜了。

• 把node-xlsx fork后，我发布到了npmjs上面了。https://www.npmjs.com/package/@zdhsoft/nodexlsx

# 使用npm命令就可以安装了，前端也可以使用。
npm i @zdhsoft/nodexlsx

• 请参阅 SheetJS 核心 API 参考文档
• 当前使用的 sheetjs 版本：0.18.5 (sheetjs的xlsx在0.18.5之后，就没有放到github上面开源了，同时也没有到npmjs更新版本了，主要是它推出了pro版本，也就是作者要开始赚钱了, 但是我们还是要非常感谢它，0.18.5的版本还是非常强大的版本，基本上够用了）

单元格的属性

• Cell styles are specified by a style object that roughly parallels the OpenXML structure.
• Style properties currently supported are: alignment, border, fill, font, numFmt.

COLOR_STYLE {object} Properties

Colors for border, fill, font are specified as an name/value object - use one of the following:

BORDER_STYLE {string} Properties

Border style property is one of the following values:

• dashDotDot
• dashDot
• dashed
• dotted
• hair
• mediumDashDotDot
• mediumDashDot
• mediumDashed
• medium
• slantDashDot
• thick
• thin

Border Notes

Borders for merged areas are specified for each cell within the merged area. For example, to apply a box border to a merged area of 3x3 cells, border styles would need to be specified for eight different cells:

• left borders (for the three cells on the left)
• right borders (for the cells on the right)
• top borders (for the cells on the top)
• bottom borders (for the cells on the left)

示例

解析excel

import xlsx from '@zdhsoft/nodexlsx';
// Or var xlsx = require('@zdhsoft/nodexlsx').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 '@zdhsoft/nodexlsx';
// 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

自定义列宽度

import xlsx from '@zdhsoft/nodexlsx';
// Or var xlsx = require('@zdhsoft/nodexlsx').default;const data = [[1, 2, 3],[true, false, null, 'sheetjs'],['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'],['baz', null, 'qux'],
];
const sheetOptions = {'!cols': [{wch: 6}, {wch: 7}, {wch: 10}, {wch: 20}]};var buffer = xlsx.build([{name: 'mySheetName', data: data}], {sheetOptions}); // Returns a buffer

合并单元格 A1:A4

import xlsx from '@zdhsoft/nodexlsx';
// Or var xlsx = require('@zdhsoft/nodexlsx').default;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 sheetOptions = {'!merges': [range]};var buffer = xlsx.build([{name: 'mySheetName', data: data}], {sheetOptions}); // Returns a buffer

在第二个表合并单元格 A1:A4

import xlsx from '@zdhsoft/nodexlsx';
// Or var xlsx = require('@zdhsoft/nodexlsx').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

增加使用样式的功能示例

// typescript的代码
import {TCellType, nodexlsx, CellStyle, WorkSheetOptions} from '@zdhsoft/nodexlsx';
import fs from 'fs';function simpleExcel() {console.info('---->开始执行');const sheetOptions: WorkSheetOptions = {'!cols': [{wch: 6}, {wch: 30}, {wch: 10}, {wch: 15}, {wch: 10}, {wch: 10}],'!merges': [{s: {c: 0, r: 0}, e: {c: 2, r: 0}},{s: {c: 3, r: 0}, e: {c: 5, r: 0}},],};const s: CellStyle = {alignment: {horizontal: 'center', // 水平居中vertical: 'center', // 垂直居中},};const contentCellStyle: CellStyle = {border: {top: {style: 'dashDot',color: {rgb: '00ff00'},},bottom: {style: 'medium',color: {rgb: '0000ff'},},left: {style: 'medium',color: {rgb: 'ff0000'},},right: {style: 'medium',color: {rgb: '770066'},},},};// 指定标题单元格样式：加粗居中const headerStyle: CellStyle = {font: {bold: true,},alignment: {horizontal: 'center',},};const title: TCellType[] = ['序号', '名称', '年级', {v: '任课老师', s}, '学生数量', '已报名数量'];const title1: TCellType[] = [{v: '天下无难事', s: headerStyle}, null, null, '右边', null];const value: TCellType[] = [1, 'test', 9, {v: '张老师', s: contentCellStyle}, 10, 99];const rows: TCellType[][] = [];rows.push(title1);rows.push(title);rows.push(value);const data = nodexlsx.build([{name: '社团列表', data: rows, options: sheetOptions}]);const outFileName = 'd:\\\\temp\\\\a.xlsx';fs.writeFileSync(outFileName, data);console.info('生成' + outFileName + ' ok!');return true;
}