Nodejs实现Excel表格操作

帅华君
10,851 阅读5分钟

借助基于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 创建CSV
  • XLSX.utils.sheet_to_txt 创建UTF16格式的文本
  • XLSX.utils.sheet_to_html 创建HTML
  • XLSX.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 变量中。

了解更多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发布的英文文档，本文中如有不严谨之处，请联系帅华君。

本文完，感谢阅读！

avatar
文章
阅读
粉丝