故障排除
以下是一些常见错误及其解决方法。这并不全面。问题跟踪器 拥有丰富的信息和用户提供的示例。
¥Here are some common errors and their resolutions. This is not comprehensive. The issue tracker has a wealth of information and user-contributed examples.
其中许多错误已在新版本中得到修复!确保使用最新版本的库。一些旧端点已经过时。查看安装说明。
¥Many of these errors have been fixed in newer releases! Ensure that the latest version of the library is being used. Some legacy endpoints are out of date. Review the Installation instructions.
如果文档或问题跟踪器中未涵盖问题,或者文档中未讨论解决方案,我们将不胜感激你提供错误报告。
¥If issues are not covered in the docs or the issue tracker, or if a solution is not discussed in the documentation, we would appreciate a bug report.
特别感谢早期采用者和用户发现并分享了许多解决方法和解决方案!
¥Special thanks to the early adopters and users for discovering and sharing many workarounds and solutions!
错误
¥Errors
未捕获的类型错误:无法读取未定义的属性
¥Uncaught TypeError: Cannot read property of undefined
错误包括
¥Errors include
Uncaught TypeError: Cannot read property 'read' of undefined
Uncaught TypeError: Cannot read property 'writeFile' of undefined
Uncaught TypeError: Cannot read property 'utils' of undefined
根本原因是未定义的 XLSX
变量。这通常意味着库未正确加载。
¥The root cause is an undefined XLSX
variable. This usually means the library
was not properly loaded.
¥Review the Installation instructions.
如果在使用最新版本时出现错误,项目可能需要其他配置或加载策略。
¥If the error shows up while using the latest version, projects may require other configuration or loading strategies.
Upgrade Note (click to show)
Older versions of the library only shipped with CommonJS and standalone script. Webpack and other bundlers supported CommonJS dependencies with default import:
// old way
import XLSX from "xlsx";
Newer versions of the library ship with an ESM build. When upgrading, imports should be updated:
// new way
import * as XLSX from "xlsx";
import * as cptable from "xlsx/dist/cpexcel.full.mjs";
XLSX.set_cptable(cptable);
Newer releases support tree shaking, and special methods like writeFileXLSX
help reduce bundle size.
The bundler note explains in further detail.
"噢,啪!" 或 "糟糕,出现错误!"
¥"Aw Snap!" or "Oops, an error has occurred!"
浏览器有严格的内存限制,大型电子表格可能会超出限制。
¥Browsers have strict memory limits and large spreadsheets can exceed the limits.
对于大型工作表,请使用 密集的工作表:
¥For large worksheets, use dense worksheets:
var wb = XLSX.read(data, {dense: true}); // creates a dense-mode sheet
XLSX.writeFile(data, "large.xlsx"); // writeFile can handle dense-mode sheets
当必须处理非常大的文件时,请考虑使用 NodeJS 或其他服务器端技术在服务器中运行进程。
¥When processing very large files is a must, consider running processes in the server with NodeJS or some other server-side technology.
如果文件较小,请 向我们的问题跟踪器报告
¥If the files are small, please report to our issue tracker
历史上,稀疏工作表在小工作表中的性能更高。由于 2014 年 V8 中的错误 和 2017 年 V8 回归(为 Node 和 Chrome 提供支持的 JavaScript 引擎),大型稀疏工作表将使 Web 浏览器崩溃。
¥Sparse worksheets historically were more performant in small sheets. Due to a 2014 bug in V8 and a 2017 regression in V8 (the JavaScript engine powering Node and Chrome), large sparse worksheets will crash the web browser.
"字符串长度无效" 或 ERR_STRING_TOO_LONG
¥"Invalid String Length" or ERR_STRING_TOO_LONG
V8(Node/Chrome)的最大字符串长度多年来一直在变化。Node 16 和 Chrome 106 强制执行 0x1fffffe8
(536870888) 个字符的限制。2017 年 V8 讨论 解释了 V8 决定背后的一些背景。
¥V8 (Node/Chrome) have a maximum string length that has changed over the years.
Node 16 and Chrome 106 enforce a limit of 0x1fffffe8
(536870888) characters.
A 2017 V8 discussion
explains some of the background behind the V8 decision.
XLSX 和 ODS 是基于 ZIP 的格式,它们以 XML 条目存储工作表。在单元格超过 100M 的工作表中,XML 字符串可能会超出 V8 限制!
¥XLSX and ODS are ZIP-based formats that store worksheets in XML entries. In worksheets with over 100M cells, the XML strings may exceed the V8 limit!
根据环境的不同,此问题可能会导致工作表丢失;错误消息,例如 Invalid string length
、ERR_STRING_TOO_LONG
或 Cannot create a string longer than 0x1fffffe8 characters
;或浏览器崩溃。
¥Depending on the environment, this issue may result in missing worksheets; error
messages such as Invalid string length
, ERR_STRING_TOO_LONG
, or
Cannot create a string longer than 0x1fffffe8 characters
; or browser crashes.
已经向 V8 和 Chromium 项目报告了有关此主题的许多错误,其中一些错误已经开放了近十年。
¥A number of bugs have been reported to the V8 and Chromium projects on this subject, some of which have been open for nearly a decade.
请 留言 包括工作表大小(行/列数和文件大小)和环境(浏览器或 NodeJS 或其他平台)。
¥Please leave a note including worksheet sizes (number of rows/columns and file size) and environment (browser or NodeJS or other platform).
"Excel 二进制工作簿" XLSB 格式使用二进制表示形式。XLSB 解析器不受此问题的影响。
¥The "Excel Binary Workbook" XLSB format uses a binary representation. The XLSB parser is not affected by this issue.
XLSB 文件通常小于同等的 XLSX 文件。XLSB 还有其他 Excel 性能优势,因此强烈建议尽可能使用 XLSB。
¥XLSB files are typically smaller than equivalent XLSX files. There are other Excel performance benefits to XLSB, so it is strongly recommended to use XLSB when possible.
无效的 HTML:找不到表格
¥Invalid HTML: could not find table
数据可以由库获取和解析:
¥Data can be fetched and parsed by the library:
const response = await fetch("test.xlsx");
const wb = XLSX.read(await response.arrayBuffer());
如果该文件不存在,服务器将发送 404 响应,其中可能包含友好的 HTML 页面。如果不检查响应代码,集成将尝试读取 404 页面并失败,因为 HTML 通常没有 TABLE 元素。
¥If the file does not exist, servers will send a 404 response that may include a friendly HTML page. Without checking the response code, the integration will try to read the 404 page and fail since the HTML typically has no TABLE elements.
集成代码应通过检查状态代码来防御网络问题。例如,使用 fetch
时:
¥Integration code should defend against network issues by checking status code.
For example, when using fetch
:
async function fetch_workbook_and_error_on_404(url) {
const response = await fetch(url);
if(res.status == 404) throw new Error("404 File Not Found");
const ab = await response.arrayBuffer();
return XLSX.read(ab);
}
使用 create-react-app
或其他模板构建项目时,电子表格必须放置在 public
文件夹中。该文件夹通常由开发服务器提供服务,并在构建过程中复制到生产站点。
¥When building a project with create-react-app
or other templates, spreadsheets
must be placed in the public
folder. That folder is typically served by the
dev server and copied to the production site in the build process.
Cloudflare Worker“错误:脚本启动超出了 CPU 时间限制。”
¥Cloudflare Worker "Error: Script startup exceeded CPU time limit."
这可能会出现在具有许多依赖的项目中。官方的解决方法是动态 import
。例如:
¥This may show up in projects with many dependencies. The official workaround is
dynamic import
. For example:
export default {
async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
const XLSX = await import("xlsx"); // dynamic import
const wb = XLSX.read("abc\n123", {type: "string"});
const buf = XLSX.write(wb, {type: "buffer", bookType: "xlsb"});
const response = new Response(buf);
response.headers.set("Content-Disposition", 'attachment; filename="cf.xlsb"');
return response;
},
};
"要求未定义"
¥"require is not defined"
此错误将显示在浏览器控制台中,并将指向 xlsx.mjs
¥This error will be displayed in the browser console and will point to xlsx.mjs
旧版本的 Webpack 不支持 ECMAScript 模块的 mjs
。这通常会影响较旧的 create-react-app
项目。
¥Older versions of Webpack do not support mjs
for ECMAScript Modules. This
typically affects older create-react-app
projects.
"独立式" 构建 应该使用 require
或 import
加载:
¥The "Standalone" build should
be loaded using require
or import
:
CommonJS
var XLSX = require("xlsx/dist/xlsx.full.min");
ECMAScript 模块
¥ECMAScript Modules
import * as XLSX from "xlsx/dist/xlsx.full.min.js";
SCRIPT5022:DataCloneError
IE10 无法正确支持 Transferable
。
¥IE10 does not properly support Transferable
.
对象不支持属性或方法 'slice'
¥Object doesn't support property or method 'slice'
IE 不实现 Uint8Array#slice
。shim 脚本中包含一个实现。检查 "独立式" 安装说明
¥IE does not implement Uint8Array#slice
. An implementation is included in the
shim script. Check the "Standalone" Installation note
TypeError:f.substr 不是函数
¥TypeError: f.substr is not a function
某些 Google 系统使用 base64url
编码。base64url
和 base64
是不同的编码。一个简单的正则表达式可以翻译数据:
¥Some Google systems use the base64url
encoding. base64url
and base64
are
different encodings. A simple regular expression can translate the data:
var wb = XLSX.read(b64.replace(/_/g, "/").replace(/-/g, "+"), {type:'base64'});
错误:无法读取未定义的属性 '0'
¥Error: Cannot read property '0' of undefined
FileReader#readAsText
将损坏二进制数据,包括 XLSX、XLSB、XLS 和其他二进制电子表格文件。
¥FileReader#readAsText
will corrupt binary data including XLSX, XLSB, XLS, and
other binary spreadsheet files.
应用应使用 FileReader#readAsArrayBuffer
或 Blob#arrayBuffer
。示例包括 在 "用户提交内容" 中
¥Applications should use FileReader#readAsArrayBuffer
or Blob#arrayBuffer
.
Examples are included in "User Submissions"
专门针对 IE10 等旧版浏览器的应用应使用 FileReader#readAsBinaryString
读取数据并使用 binary
类型调用 XLSX.read
。
¥Applications specifically targeting legacy browsers like IE10 should use
FileReader#readAsBinaryString
to read the data and call XLSX.read
using the
binary
type.
读取 ArrayBuffer 对象时的 Unsupported file undefined
¥Unsupported file undefined
when reading ArrayBuffer objects
旧版本的库不会自动检测 ArrayBuffer
对象。
¥Old versions of the library did not automatically detect ArrayBuffer
objects.
Workaround (click to show)
This solution is not recommended for production deployments. Native support
for ArrayBuffer
was added in library version 0.9.9
.
After reading data with FileReader#readAsArrayBuffer
, manually translate to
binary string and call XLSX.read
with type "binary"
document.getElementById('file-object').addEventListener("change", function(e) {
var files = e.target.files,file;
if (!files || files.length == 0) return;
file = files[0];
var fileReader = new FileReader();
fileReader.onload = function (e) {
var filename = file.name;
// pre-process data
var binary = "";
var bytes = new Uint8Array(e.target.result);
var length = bytes.byteLength;
for (var i = 0; i < length; i++) {
binary += String.fromCharCode(bytes[i]);
}
// call 'xlsx' to read the file
var oFile = XLSX.read(binary, {type: 'binary', cellDates:true, cellStyles:true});
};
fileReader.readAsArrayBuffer(file);
});
浏览器卡住了!
¥Browser is stuck!
默认情况下,操作在主渲染器上下文中运行并阻止浏览器更新。Web Worker 将繁重的工作卸载到单独的上下文中,从而释放渲染器进行更新。
¥By default, operations run in the main renderer context and block the browser from updating. Web Workers offload the hard work to separate contexts, freeing up the renderer to update.
Web 浏览器中导出的文件名奇怪
¥Strange exported file names in the web browser
JS 和 DOM API 没有创建文件的标准方法。有一个 saveAs
提案作为“文件 API:作家”,但在 2014 年被放弃。
¥JS and the DOM API do not have a standard approach for creating files. There was
a saveAs
proposal as part of "File API: Writer" but it was abandoned in 2014.
该库集成了许多针对不同环境的特定于平台的技术。在现代 Web 浏览器中,该库会创建具有 download
属性的 A
元素并单击链接。完整的分析包含在 "本地文件访问" 演示 中
¥The library integrates a number of platform-specific techniques for different
environments. In modern web browsers, the library creates an A
element with
the download
attribute and clicks the link. A full analysis is included in the
"Local File Access" demo
如果文件名看起来像 UUID(十六进制字符和连字符),则这是 Google 跟踪代码管理器 (GTM) 重写链接的已知问题。对于使用 GTM 的网站,有一个特殊的 workaround。
¥If the filename looks like a UUID (hexadecimal characters and hyphens), this is a known issue with Google Tag Manager (GTM) rewriting links. There is a special workaround for sites that use GTM.
FileSaver.js
等第三方库提供了 saveAs
的实现,其中包括更多特定于浏览器的解决方法。
¥Third party libraries like FileSaver.js
provide an implementation of saveAs
that include more browser-specific workarounds.
FileSaver.js integration (click to show)
Standalone Build
<script src="https://unpkg.com/file-saver@2.0.5/dist/FileSaver.js"></script>
<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
<!-- XLSX.writeFile will use the FileSaver `saveAs` implementation -->
Frameworks and Bundlers
At the time of writing, file-saver@2.0.5
leaks saveAs
to the global scope,
so merely importing the module works:
import FileSaver from 'file-saver'; // as a side effect, `saveAs` is visible
import { writeFile } from 'xlsx'; // writeFile will use the global `saveAs`
NodeJS 中的 "无法保存文件"
¥"Cannot save file" in NodeJS
fs
模块使用 require
自动加载到脚本中:
¥The fs
module is automatically loaded in scripts using require
:
var XLSX = require("xlsx"); // automatically loads `fs`
使用 ESM 导入,必须导入 fs
模块并将其传递到库:
¥Using the ESM import,
the fs
module must be imported and passed to the library:
import * as XLSX from 'xlsx';
/* load 'fs' for readFile and writeFile support */
import * as fs from 'fs';
XLSX.set_fs(fs);
数据问题
¥Data Issues
生成的 XLSX 文件非常大!
¥Generated XLSX files are very large!
默认情况下,压缩处于禁用状态。在 write
或 writeFile
选项对象中设置选项 compression: true
。例如:
¥By default, compression is disabled. Set the option compression: true
in the
write
or writeFile
options object. For example:
XLSX.writeFile(workbook, "export.xlsx", { compression: true });
包含中文或日语字符的 CSV 和 XLS 文件看起来乱码
¥CSV and XLS files with Chinese or Japanese characters look garbled
Webpack 和 Deno 等工具中使用的 ESM 构建默认不包含代码页表。"框架和打包器" 部分解释了如何加载支持。
¥The ESM build, used in tools like Webpack and in Deno, does not include the codepage tables by default. The "Frameworks and Bundlers" section explains how to load support.
含有中文或日语字符的 DBF 文件带有下划线
¥DBF files with Chinese or Japanese characters have underscores
正如前面的答案中提到的,必须加载代码页表。
¥As mentioned in the previous answer, codepage tables must be loaded.
读取不包含字符集元数据的旧文件时,codepage
选项控制代码页。共同值:
¥When reading legacy files that do not include character set metadata, the
codepage
option controls the codepage. Common values:
codepage | 描述 |
---|---|
874 | Windows 泰语 |
932 | 日语 Shift-JIS |
936 | 简体中文 GBK |
950 | 繁体中文大五 |
1200 | UTF-16 小尾数法 |
1252 | Windows 拉丁语 1 |
当以传统格式(如 DBF)写入文件时,将使用默认代码页 1252。代码页选项将覆盖该设置。字符集中缺少的任何字符都将替换为下划线。
¥When writing files in legacy formats like DBF, the default codepage 1252 will be used. The codepage option will override the setting. Any characters missing from the character set will be replaced with underscores.
工作表仅包含一行数据
¥Worksheet only includes one row of data
某些第三方编写器工具不会更新 XLSX 或 XLS 或 XLSB 导出中的尺寸记录。SheetJS 实用函数将跳过不在范围内的值。
¥Some third-party writer tools will not update the dimensions records in XLSX or XLS or XLSB exports. SheetJS utility functions will skip values not in range.
以下辅助函数将重新计算范围:
¥The following helper function will recalculate the range:
function update_sheet_range(ws) {
var range = {s:{r:Infinity, c:Infinity},e:{r:0,c:0}};
Object.keys(ws).filter(function(x) { return x.charAt(0) != "!"; }).map(XLSX.utils.decode_cell).forEach(function(x) {
range.s.c = Math.min(range.s.c, x.c); range.s.r = Math.min(range.s.r, x.r);
range.e.c = Math.max(range.e.c, x.c); range.e.r = Math.max(range.e.r, x.r);
});
ws['!ref'] = XLSX.utils.encode_range(range);
}
More Code Snippets (click to show)
set_sheet_range
changes a sheet's range given a general target spec that can include only the start or end cell:
/* given the old range and a new range spec, produce the new range */
function change_range(old, range) {
var oldrng = XLSX.utils.decode_range(old), newrng;
if(typeof range == "string") {
if(range.charAt(0) == ":") newrng = {e:XLSX.utils.decode_cell(range.substr(1))};
else if(range.charAt(range.length - 1) == ":") newrng = {s:XLSX.utils.decode_cell(range.substr(0, range.length - 1))};
else newrng = XLSX.utils.decode_range(range);
} else newrng = range;
if(newrng.s) {
if(newrng.s.c != null) oldrng.s.c = newrng.s.c;
if(newrng.s.r != null) oldrng.s.r = newrng.s.r;
}
if(newrng.e) {
if(newrng.e.c != null) oldrng.e.c = newrng.e.c;
if(newrng.e.r != null) oldrng.e.r = newrng.e.r;
}
return XLSX.utils.encode_range(oldrng);
}
/* call change_sheet and modify worksheet */
function set_sheet_range(sheet, range) {
sheet['!ref'] = change_range(sheet['!ref'], range);
}
Adding a cell to a range
function range_add_cell(range, cell) {
var rng = XLSX.utils.decode_range(range);
var c = typeof cell == 'string' ? XLSX.utils.decode_cell(cell) : cell;
if(rng.s.r > c.r) rng.s.r = c.r;
if(rng.s.c > c.c) rng.s.c = c.c;
if(rng.e.r < c.r) rng.e.r = c.r;
if(rng.e.c < c.c) rng.e.c = c.c;
return XLSX.utils.encode_range(rng);
}
range_add_cell("A1:C3","B2")
function add_to_sheet(sheet, cell) {
sheet['!ref'] = range_add_cell(sheet['!ref'], cell);
}
一些小数值被四舍五入
¥Some decimal values are rounded
在某些情况下,Excel 似乎会对值进行舍入。怀疑 XLSX 解析器处理 15 位十进制数字的精度。这会导致不准确,例如 7581185.559999999
舍入为 7581185.56
,7581185.5599999903
舍入为 7581185.55999999
。
¥Excel appears to round values in certain cases. It is suspected that the XLSX
parser handles 15 decimal digits of precision. This results in inaccuracies such
as 7581185.559999999
rounding to 7581185.56
and 7581185.5599999903
rounding to 7581185.55999999
.
有关详细信息,请参阅主 SheetJS CE 存储库中的 第 3003 期。
¥See Issue 3003 in the main SheetJS CE repo for details.
文件损坏
¥Corrupt files
第三方构建工具和框架可以对 SheetJS 脚本进行后处理。这些更改可能会导致文件损坏。
¥Third-party build tools and frameworks may post-process SheetJS scripts. The changes may result in corrupt files.
在 Web 浏览器中,来自 SheetJS CDN 的独立脚本将使用正确的编码,并且应该在应用中运行。这些脚本通常可以添加到 index.html
文件中,绕过任何第三方后处理
¥In the web browser, the standalone scripts from the SheetJS CDN will use proper
encodings and should work in applications. The scripts typically can be added
to an index.html
file, bypassing any third-party post-processing
SWC 压缩器(在 Next.js 13+ 中使用)存在已知错误。可以通过设置 next.config.js
中的 swcMinify: false
来启用原始压缩器。
¥There are known bugs with the SWC minifier (used in Next.js 13+). The original
minifier can be enabled by setting swcMinify: false
in next.config.js
.
此文件应通过 HTTPS 提供
¥This file should be served over HTTPS
writeFile
使用平台 API 下载文件。在浏览器中,writeFile
使用 download
属性。
¥writeFile
uses platform APIs to download files. In browsers, writeFile
uses
the download
attribute.
Google Chrome 和其他浏览器的较新版本将阻止从 "不安全上下文" 下载这些内容(当通过 HTTP 而不是 HTTPS 提供时)。用户可能会看到对文件进行 "keep" 或 "save" 的选项:
¥Newer versions of Google Chrome and other browsers will block these downloads from "insecure contexts" (when served over HTTP rather than HTTPS). Users may be presented with the option to "keep" or "save" the file:
这些限制由浏览器强制执行。强烈建议尽可能通过 HTTPS 为网站提供服务。
¥These limitations are enforced by the browser. It is strongly recommended to serve websites over HTTPS when possible.
有关更详细的讨论,请参阅 问题 #3145。
¥See issue #3145 for a longer discussion.