@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上面了。www.npmjs.com/package/@zd…

# 使用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.

Style Prop	Sub Prop	Default	Description/Values
`alignment`	`vertical`	`bottom`	`"top"` or `"center"` or `"bottom"`
	`horizontal`	`left`	`"left"` or `"center"` or `"right"`
	`wrapText`	`false`	`true` or `false`
	`textRotation`	`0`	`0` to `180`, or `255` // `180` is rotated down 180 degrees, `255` is special, aligned vertically
`border`	`top`		`{ style: BORDER_STYLE, color: COLOR_STYLE }`
	`bottom`		`{ style: BORDER_STYLE, color: COLOR_STYLE }`
	`left`		`{ style: BORDER_STYLE, color: COLOR_STYLE }`
	`right`		`{ style: BORDER_STYLE, color: COLOR_STYLE }`
	`diagonal`		`{ style: BORDER_STYLE, color: COLOR_STYLE, diagonalUp: true/false, diagonalDown: true/false }`
`fill`	`patternType`	`"none"`	`"solid"` or `"none"`
	`fgColor`		foreground color: see `COLOR_STYLE`
	`bgColor`		background color: see `COLOR_STYLE`
`font`	`bold`	`false`	font bold `true` or `false`
	`color`		font color `COLOR_STYLE`
	`italic`	`false`	font italic `true` or `false`
	`name`	`"Calibri"`	font name
	`strike`	`false`	font strikethrough `true` or `false`
	`sz`	`"11"`	font size (points)
	`underline`	`false`	font underline `true` or `false`
	`vertAlign`		`"superscript"` or `"subscript"`
`numFmt`		`0`	Ex: `"0"` // integer index to built in formats, see StyleBuilder.SSF property
			Ex: `"0.00%"` // string matching a built-in format, see StyleBuilder.SSF
			Ex: `"0.0%"` // string specifying a custom format
			Ex: `"0.00%;\\(0.00%\\);\\-;@"` // string specifying a custom format, escaping special characters
			Ex: `"m/dd/yy"` // string a date format using Excel's format notation

`COLOR_STYLE` {object} Properties

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

Color Prop	Description	Example
`rgb`	hex RGB value	`{rgb: "FFCC00"}`
`theme`	theme color index	`{theme: 4}` // (0-n) // Theme color index 4 ("Blue, Accent 1")
`tint`	tint by percent	`{theme: 1, tint: 0.4}` // ("Blue, Accent 1, Lighter 40%")

`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;
}

javascript支持带样式的excel导出库