借助基于nodejs环境的第三方库 xlsx
实现解析与导出多种数据格式,支持在excel中创建多个Sheet,支持精准插入单元格,并支持.xlsb .xls .csv .txt等多种数据文件导出格式。
npm地址:SheetJS js-xlsx
小试牛刀
我想将个站某个数据库的某张数据表以.xlsx格式导出并下载到本地,该格式能使用Microsoft Office 2007+软件打开。
这是一张废弃的表,拿来做实验。这张表包含5个字段,共7条记录。相应的表头应该包含以下信息:
- id
- name
- role
- path
- state
预期最终生成的Excel表格布局如下:
id | name | role | path | state |
---|---|---|---|---|
1 | 首页 | user | / | 1 |
2 | 全部文章 | user | /article | 1 |
… | … | … | … | … |
AOA to sheet
创建二维数组, 如 [[1,2,3],[4,5,6],[7,8,9]]
。
其中数组的每一项([1,2,3]
[4,5,6]
[7,8,9]
)表示Excel中的一行。
每一项中的每一项(1
2
3
)表示一行上的每一个单元格。
因此,按照预期计划,Array-Of-Arrays 二维数组应该是这样:
const ws_data = [
['id','name','role','path','state'],
[1,'首页','user','/',1],
[2,'全部文章','user','/article',1],
...
]
使用上方构建的二维数组创建 sheet
const ws = XLSX.utils.aoa_to_sheet(ws_data)
创建空的表格簿,将刚创建的 sheet
追加到表格簿中,并为这个新创建的 sheet
命名为 SheetJS
let wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, ws, 'SheetJS')
执行写入方法将内存中的表格簿写入到文件
XLSX.writeFile(wb, 'out.xlsx')
根据扩展名生成相应编码规范的Excel表格文件
解析工作薄
读文件
解析的第一步是读取文件,包括获取数据和与xlsx库的绑定。
readFile
只在服务端环境下才可用,比如Nodejs环境下,浏览器没有API来支持读取任意路径的文件,所以在浏览器环境下需要借助其他技术。
var workbook = XLSX.readFile('test.xlsx')
读文件流
XLSX官方文档表示并不提供读取文件流的API。开发者可以使用Nodejs原生文件流模块。
var fs = require('fs');
var XLSX = require('xlsx');
function process_RS(stream/*:ReadStream*/, cb/*:(wb:Workbook)=>void*/)/*:void*/{
var buffers = [];
stream.on('data', function(data) { buffers.push(data); });
stream.on('end', function() {
var buffer = Buffer.concat(buffers);
var workbook = XLSX.read(buffer, {type:"buffer"});
/* DO SOMETHING WITH workbook IN THE CALLBACK */
cb(workbook);
});
}
读取已写入文件
下方官方代码示例使用了 tempfile
这一第三方模块。
var fs = require('fs'), tempfile = require('tempfile');
var XLSX = require('xlsx');
function process_RS(stream/*:ReadStream*/, cb/*:(wb:Workbook)=>void*/)/*:void*/{
var fname = tempfile('.sheetjs');
console.log(fname);
var ostream = fs.createWriteStream(fname);
stream.pipe(ostream);
ostream.on('finish', function() {
var workbook = XLSX.readFile(fname);
fs.unlinkSync(fname);
/* DO SOMETHING WITH workbook IN THE CALLBACK */
cb(workbook);
});
}
操作工作薄
读取指定单元格
var first_sheet_name = workbook.SheetNames[0];
var address_of_cell = 'A1';
/* 获取工作表 */
var worksheet = workbook.Sheets[first_sheet_name];
/* 找到期望中的单元格 */
var desired_cell = worksheet[address_of_cell];
/* 获取单元格的值 */
var desired_value = (desired_cell ? desired_cell.v : undefined);
添加工作表到工作薄
使用 XLSX.utils.aoa_to_sheet
创建表,使用 XLSX.utils.book_append_sheet
将工作表追加到工作薄。
var new_ws_name = "SheetJS";
/* 创建工作表 */
var ws_data = [
["S","h","e","e","t","J","S"],
[1,2,3,4,5]
];
var ws = XLSX.utils.aoa_to_sheet(ws_data);
/* 添加工作表到工作薄 */
XLSX.utils.book_append_sheet(wb, ws, ws_name);
创建空白工作薄
工作薄对象中包含包含存储着所有 SheetNames
名字的数组,Sheets
对象将所有表的名字映射到表对象。
XLSX.utils.book_new
工具函数用于创建空白工作薄。
var wb = XLSX.utils.book_new();
XLSX.utils
提供不同的帮助函数能将工作表转换成不同的数据格式:
XLSX.utils.sheet_to_csv
创建CSVXLSX.utils.sheet_to_txt
创建UTF16格式的文本XLSX.utils.sheet_to_html
创建HTMLXLSX.utils.sheet_to_json
创建对象数组XLSX.utils.sheet_to_formulae
创建公式列表
写入工作薄
第一步是创建输出数据。XLSX提供的帮助函数 write
和 writeFile
会将读取到的数据加工成各种各样便于开发者操作的格式。第二步就是将数据输出。
开发者就可以将 workbook
看作是工作薄。
写文件
XLSX.writeFile
基于使用Nodejs中的 fs.writeFileSync
实现:
XLSX.writeFile(workbook, 'out.xlsb');
写入流
XLSX.Stream
对象实现多个了写入流函数。向这些函数传入和其他用于写入文件的函数相同的参数即可。这些函数返回可读的写入流,这些XLSX提供的接口仅在Nodejs环境下可用。
XLSX.stream.to_csv
是文件流版的XLSX.utils.sheet_to_csv
XLSX.stream.to_html
是文件流版的XLSX.utils.sheet_to_html
XLSX.stream.to_json
是文件流版的XLSX.utils.sheet_to_json
将CSV文件流写入文件
var output_file_name = 'out.csv';
var stream = XLSX.stream.to_csv(worksheet);
stream.pipe(fs.createWriteStream(output_file_name));
XLSX参考
全局对象
- Nodejs环境下引入
XLSX
模块即可使用 - 浏览器环境下引入script脚本即可使用
版本
XLSX.version
SSF
XLSX
将第三方格式化库直接嵌入 XLSX.SSF
变量中。
解析函数
XLSX.read(data, read_opts)
函数用于解析数据XLSX.readFile(filename, read_opts)
函数用于读取文件并解析
其中参数 read_opts
是一个对象,可以设置以下属性:
Option Name | Default | Description |
---|---|---|
type | 要读取的文件的编码方式 | |
raw | false | If true, plain text parsing will not parse values ** |
codepage | If specified, use code page when appropriate ** | |
cellFormula | true | Save formulae to the .f field |
cellHTML | true | Parse rich text and save HTML to the .h field |
cellNF | false | Save number format string to the .z field |
cellStyles | false | Save style/theme info to the .s field |
cellText | true | Generated formatted text to the .w field |
cellDates | false | Store dates as type d (default is n) |
dateNF | If specified, use the string for date code 14 ** | |
sheetStubs | false | Create cell objects of type z for stub cells |
sheetRows | 0 | If >0, read the first sheetRows rows ** |
bookDeps | false | If true, parse calculation chains |
bookFiles | false | If true, add raw files to book object ** |
bookProps | false | If true, only parse enough to get book metadata ** |
bookSheets | false | If true, only parse enough to get the sheet names |
bookVBA | false | If true, copy VBA blob to vbaraw field ** |
password | “” | If defined and file is encrypted, use password ** |
WTF | false | If true, throw errors on unexpected file features ** |
写入函数
XLSX.write(wb, write_opts)
XLSX.writeFile(wb, filename, write_opts)
用于将wb
写入filename
文件,浏览器环境下会将文件下载到本地XLSX.writeFileAsync(filename, wb, o, cb)
XLSX.stream
包含一系列文件流写入函数
工具函数
XLSX.utils
对象包含多个工具函数
用于导入
aoa_to_sheet
js二维数组转换为工作表json_to_sheet
js对象数据转换为工作表table_t_sheet
DOM表格元素转换为工作表sheet_add_aoa
向已存在的工作表添加js二维数组sheet_add_json
向已存在的工作表添加对象数组
用于导出
sheet_to_json
工作表对象转换为JSON对象sheet_to_csv
生成csv格式的输出sheet_to_txt
生成UTF16格式文本sheet_to_html
生成HTML输出sheet_to_formulae
生成公式列表
操作单元格
format_cell
encode_row/decode_row
encode_col/decode_col
encode_range/decode_range
本篇以一个案例引入,并翻译了XLSX官方文档核心内容——解析、写入工作薄函数及工具函数。更详尽的文档请参考XLSX在github与npm发布的英文文档,本文中如有不严谨之处,请联系帅华君。
本文完,感谢阅读!