ExcelJS

读取，操作并写入电子表格数据和样式到 XLSX 和 JSON 文件。

一个 Excel 电子表格文件逆向工程项目。

安装

npm install exceljs

新的功能!

• Merged fix: styles rendering in case when "numFmt" is present in conditional formatting rules (resolves #1814) #1815.

Many thanks to @andreykrupskii for this contribution! * Merged inlineStr cell type support #1575 #1576.

Many thanks to @drdmitry for this contribution! * Merged Fix parsing of boolean attributes #1849.

Many thanks to @bno1 for this contribution! * Merged add optional custom auto-filter to table #1670.

Many thanks to @thambley for this contribution! * Merged Deep copy inherited style #1850.

Many thanks to @ikzhr for this contribution! * Merged Upgrade actions/cache and actions/setup-node #1846.

Many thanks to @cclauss for this contribution! * Merged Check object keys in isEqual #1831.

Many thanks to @bno1 for this contribution! * Merged Add v17 to testing workflow #1856.

Many thanks to @Siemienik for this contribution! * Merged Upgrade jszip to its latest version to date. This version does not have any vulnerability found by Snyk so far #1895.

Many thanks to @ValerioSevilla for this contribution! * Merged Update README.md #1677.

Many thanks to @xjrcode for this contribution! * Merged (docs): set prototype of RegExp correctly. #1700.

Many thanks to @joeldenning for this contribution! * Merged Added timeouts to github actions #1733.

Many thanks to @alexbjorlig for this contribution! * Merged fix issue 1676 #1701.

Many thanks to @skypesky for this contribution! * Merged ExcelJS/ExcelJS#2237 : Update CI Tests, Drop support for Node v8 #2242.

Many thanks to @Siemienik for this contribution! * Merged Fix types for getWorksheet() #2223.

Many thanks to @hfhchan-plb for this contribution! * Merged add characters cannot be used for worksheet name #2126.

Many thanks to @tkm-kj for this contribution! * Merged Fix issue #1753 Reject promise when workbook reader is writing to temporary file stream and error occurs #1756.

Many thanks to @pauliusg for this contribution! * Merged README.md to have correct link for Streaming XLSX #2186.

Many thanks to @wulfsolter for this contribution! * Merged Added a polyfill of promise.finally to support lower versions of Firefox. #1982.

Many thanks to @DemoJj for this contribution! * Merged Fix read this.worksheet before assign it #1934.

Many thanks to @ZyqGitHub1 for this contribution! * Merged chore: upgrade jszip to ^3.10.1 #2211.

Many thanks to @jarrod-cocoon for this contribution! * Merged fixed spelling error in README.md file #2208.

Many thanks to @HugoP27 for this contribution! * Merged fix: Fix xlsx.writeFile() not catching error when error occurs #2244.

Many thanks to @zurmokeeper for this contribution! * Merged Improve worksheets' naming validation logic. #2257.

Many thanks to @Siemienik for this contribution! * Merged fix issue 2125 - spliceRows remove last row #2140.

Many thanks to @babu-ch for this contribution! * Merged fix: fix the loss of column attributes due to incorrect column order #2222.

Many thanks to @cpaiyueyue for this contribution! * Merged Fix: Sheet Properties Types #2327.

Many thanks to @albeniraouf for this contribution! * Merged Use node 18 LTS for tsc, and benchmark. Add node 20. to test matrix. … #2354.

Many thanks to @Siemienik for this contribution! * Merged Add missing tooltip attribute to CellHyperlinkValue index.d.ts #2350.

Many thanks to @NiklasPor for this contribution! * Merged Increase resilience to generating large workbooks #2320.

Many thanks to @hfhchan-plb for this contribution! * Merged repair all 'c2fo.io' links ('c2fo.github.io') #2324.

Many thanks to @justintunev7 for this contribution! * Merged fix: fix type definitions about last column, formula values and protection #2309.

Many thanks to @gltjk for this contribution! * Merged fix: add spinCount field for WorksheetProtection type #2284.

Many thanks to @damingerdai for this contribution! * Merged Add type definition for WorksheetModel.merges #2281.

Many thanks to @ytjmt for this contribution!

贡献

欢迎贡献！这可以帮助我了解大家需要一些什么功能，或者哪些 bugs 造成了极大的麻烦。

我只有一个请求；如果您提交对错误修复的请求（PR），请添加一个能够解决问题的单元测试或集成测试（在 spec 文件夹中）。 即使只是测试失败的请求（PR）也可以 - 我可以分析测试的过程并以此修复代码。

注意：请尽可能避免在请求（PR）中修改软件包版本。 版本一般在发布时会进行更新，任何版本更改很可能导致合并冲突。

明确地说，添加到该库的所有贡献都将包含在该库的 MIT 许可证中。

目录

• 导入
• 接口
  • 创建工作簿
  • 设置工作簿属性
  • 工作簿视图
  • 添加工作表
  • 删除工作表
  • 访问工作表
  • 工作表状态
  • 工作表属性
  • 页面设置
  • 页眉和页脚
  • 工作表视图
    • 冻结视图
    • 拆分视图
  • 自动筛选器
  • 列
  • 行
    • Add Rows
    • 处理单个单元格
    • 合并单元格
    • Insert Rows
    • Splice
    • 重复行
  • 定义名称
  • 数据验证
  • 单元格注释
  • 表格
  • 样式
    • 数字格式
    • 字体
    • 对齐
    • 边框
    • 填充
    • 富文本
  • 条件格式化
  • 大纲级别
  • 图片
  • 工作表保护
  • 文件 I/O
    • XLSX
      • 读 XLSX
      • 写 XLSX
    • CSV
      • 读 CSV
      • 写 CSV
    • 流式 I/O
      • 流式 XLSX
• 浏览器
• 值类型
  • Null 值
  • 合并单元格
  • 数字值
  • 字符串值
  • 日期值
  • 超链接值
  • 公式值
    • 共享公式
    • 公式类型
    • 数组公式
  • 富文本值
  • 布尔值
  • 错误值
• 配置
• 已知的问题
• 发布历史

导入⬆

const ExcelJS = require('exceljs');

ES5 导入⬆

要使用 ES5 编译代码，请使用 dist/es5 路径。

const ExcelJS = require('exceljs/dist/es5');

注意：ES5 版本对许多 polyfill 都具有隐式依赖，而 exceljs 不再明确添加。 您需要在依赖项中添加 core-js 和 regenerator-runtime，并在导入 exceljs 之前在代码中包含以下引用：

// exceljs 所需的 polyfills
require('core-js/modules/es.promise');
require('core-js/modules/es.string.includes');
require('core-js/modules/es.object.assign');
require('core-js/modules/es.object.keys');
require('core-js/modules/es.symbol');
require('core-js/modules/es.symbol.async-iterator');
require('regenerator-runtime/runtime');

const ExcelJS = require('exceljs/dist/es5');

对于 IE 11，您还需要一个 polyfill 以支持 unicode regex 模式。 例如，

const rewritePattern = require('regexpu-core');
const {generateRegexpuOptions} = require('@babel/helper-create-regexp-features-plugin/lib/util');

const {RegExp} = global;
try {
  new RegExp('a', 'u');
} catch (err) {
  global.RegExp = function(pattern, flags) {
    if (flags && flags.includes('u')) {
      return new RegExp(rewritePattern(pattern, flags, generateRegexpuOptions({flags, pattern})));
    }
    return new RegExp(pattern, flags);
  };
  global.RegExp.prototype = RegExp.prototype;
}

浏览器端⬆

ExcelJS 在 dist/ 文件夹内发布了两个支持浏览器的包：

一个是隐式依赖 core-js polyfills 的...

<script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/6.26.0/polyfill.js"></script>
<script src="https://github.com/exceljs/exceljs/raw/v4.4.1/exceljs.js"></script>

另一个则没有...

<script src="https://github.com/exceljs/exceljs/raw/v4.4.1/--your-project's-pollyfills-here--"></script>
<script src="https://github.com/exceljs/exceljs/raw/v4.4.1/exceljs.bare.js"></script>

接口⬆

创建工作簿⬆

const workbook = new ExcelJS.Workbook();

设置工作簿属性⬆

workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
workbook.created = new Date(1985, 8, 30);
workbook.modified = new Date();
workbook.lastPrinted = new Date(2016, 9, 27);

// 将工作簿日期设置为 1904 年日期系统
workbook.properties.date1904 = true;

设置计算属性⬆

// 在加载时强制工作簿计算属性
workbook.calcProperties.fullCalcOnLoad = true;

工作簿视图⬆

工作簿视图控制在查看工作簿时 Excel 将打开多少个单独的窗口。

workbook.views = [
  {
    x: 0, y: 0, width: 10000, height: 20000,
    firstSheet: 0, activeTab: 1, visibility: 'visible'
  }
]

添加工作表⬆

const sheet = workbook.addWorksheet('My Sheet');

使用 addWorksheet 函数的第二个参数来指定工作表的选项。

示例：

```javascript // 创建带有红色标签颜色的工作表 const sheet = workbook.addWorksheet('My Sheet', {properties:{tabColor:{argb:'FFC0000'}}});

// 创建一个隐藏了网格线的工作表 const sheet = workbook.addWorksheet('My Sheet', {views: [{showGridLines: false}]});

// 创建一个第一行和列冻结的工作表 const sheet = workbook.addWorksheet('My Sheet', {views:[{xSplit: 1, ySplit:1}]});

// 使用A4设置的页面设置设置创建新工作表 - 横向