日期和时间
File Format Support (click to show)
Dates are a core concept in nearly every spreadsheet application in existence. Some legacy spreadsheet apps only supported dates. Others supported times as a distinct concept from dates.
Some file formats store dates in a textual format, while others store dates with numbers representing a difference from an epoch.
Many spreadsheet apps use special number formats to signal that values are dates or times. Quattro Pro for DOS had a distinct set of Date number formats and Time number formats, but did not have a mixed Date + Time format. OpenOffice uses ISO 8601 duration strings for pure time data.
Lotus 1-2-3 used a "1900" date system, while Numbers exclusively supports 1904 under the hood. Excel file formats typically include options for specifying the date system. OpenOffice can support arbitrary starting dates.
| Formats | Date | Time | D+T | Date Storage | Date System |
|---|---|---|---|---|---|
| NUMBERS | ✔ | ✔ | ✔ | Number | 1904 Only |
| XLSX / XLSM | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| XLSX (Strict ISO) | ✔ | ✔ | ✔ | Relative Date | 1900 + 1904 |
| XLSB | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| XLML | ✔ | ✔ | ✔ | Relative Date | 1900 + 1904 |
| XLS (BIFF5/8) | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| XLS (BIFF2/3/4) | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| XLR (Works) | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| ET (WPS 电子表格) | ✔ | ✔ | ✔ | Number | 1900 + 1904 |
| ODS / FODS / UOS | ✔ | ✔ | ✔ | ISO Duration or Date | Arbitrary |
| HTML | ✔ | ✔ | ✔ | Plaintext | Calendar |
| CSV / TSV / Text | ✔ | ✔ | ✔ | Plaintext | Calendar |
| DBF | ✔ | * | * | Number or Plaintext | Calendar |
| DIF | ✔ | ✔ | ✔ | Plaintext | Calendar |
| WK1 | ✔ | ✔ | ✕ | Number | 1900 |
| WKS (Works) | ✔ | ✔ | ✕ | Number | 1900 |
| WQ1 | ✔ | ✕ | Number | 1900 | |
| QPW | ✔ | ✔ | * | Number | 1900 |
X (✕) marks features that are not supported by the file formats. For example, the WK1 file format had date-only formats and time-only formats but no mixed date-time formats.
Newer DBF levels support a special T field type that represents date + time.
The QPW file format supports mixed date + time formats in custom number formats.
Lotus 1-2-3、Excel 和其他电子表格软件没有真正的日期或时间概念。相反，日期和时间存储为距纪元的偏移量。日期解释背后的魔力隐藏在函数或数字格式中。
¥Lotus 1-2-3, Excel, and other spreadsheet software do not have a true concept of date or time. Instead, dates and times are stored as offsets from an epoch. The magic behind date interpretations is hidden in functions or number formats.
SheetJS 尝试创建友好的 JS 日期体验，同时还公开使用传统日期代码的选项。
¥SheetJS attempts to create a friendly JS date experience while also exposing options to use the traditional date codes.
以下示例将当前时间导出到 XLSX 电子表格。此页面显示的时间将是 Excel 中显示的时间。
¥The following example exports the current time to XLSX spreadsheet. The time shown on this page will be the time displayed in Excel.
- React (Live Demo)
- JavaScript (Explanation)
function SheetJSNow() { const [date, setDate] = React.useState(new Date()); const xport = React.useCallback(() => { /* generate array of arrays */ const aoa = [[date]]; /* to avoid confusion, set milliseconds to 0 */ aoa[0][0].setMilliseconds(0); /* generate workbook */ const ws = XLSX.utils.aoa_to_sheet(aoa, {dense: true}); /* set cell A1 number format */ ws["!data"][0][0].z = "yyyy-mm-dd hh:mm:ss" ws["!cols"] = [{wch: 20}]; /* generate workbook and export */ const wb = XLSX.utils.book_new(ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSNow.xlsx"); }, []); return ( <> <p> <b>Local Time:</b>{date.toString()} <button onClick={()=>setDate(new Date())}>Refresh</button> </p> <button onClick={xport}>Export XLSX</button> </> ); }
-
创建一个新的 Date 对象并将毫秒设置为 0（以避免日期舍入）：
¥Create a new Date object and set milliseconds to 0 (to avoid date rounding):
/* starting date */
const date = new Date();
/* to avoid confusion, set milliseconds to 0 */
date.setMilliseconds(0);
-
构造一个数组数组来存储日期。它将被放置在单元格 A1 中：
¥Construct an array of arrays to store the date. It will be placed in cell A1:
/* generate array of arrays */
const aoa = [[date]];
/* generate workbook */
const ws = XLSX.utils.aoa_to_sheet(aoa, {dense: true});
-
使用 自定义数字格式 调整日期格式：
¥Adjust the date format using a custom number format:
/* set cell A1 number format */
ws["!data"][0][0].z = "yyyy-mm-dd hh:mm:ss"
-
调整 列宽
¥Adjust the column width
/* adjust column width */
ws["!cols"] = [{wch: 20}];
/* create workbook object */
const wb = XLSX.utils.book_new(ws, "Sheet1");
/* generate XLSX workbook and attempt to download */
XLSX.writeFile(wb, "SheetJSNow.xlsx");
电子表格如何理解时间
¥How Spreadsheets Understand Time
Excel 将日期存储为数字。显示日期时，格式代码应包含特殊的日期和时间标记，例如代表长年的 yyyy。EDATE 和其他日期函数对日期数字进行操作并返回日期数字。
¥Excel stores dates as numbers. When displaying dates, the format code should
include special date and time tokens like yyyy for long year. EDATE and
other date functions operate on and return date numbers.
对于 yyyy-mm-dd 这样的日期格式，整数部分表示距离起始纪元的天数。例如，日期 19-Feb-17 存储为数字 42785，数字格式为 d-mmm-yy。
¥For date formats like yyyy-mm-dd, the integer part represents the number of
days from a starting epoch. For example, the date 19-Feb-17 is stored as the
number 42785 with a number format of d-mmm-yy.
日期代码的小数部分用作时间标记。Excel 假设每天正好有 86400 秒。例如，日期代码 0.25 具有对应于 6:00 AM 的时间部分。
¥The fractional part of the date code serves as the time marker. Excel assumes
each day has exactly 86400 seconds. For example, the date code 0.25 has a
time component corresponding to 6:00 AM.
对于像 [hh]:mm 这样的绝对时间格式，整数部分表示 24 小时（或 1440 分钟）间隔的整数。格式 [hh]:mm 中的值 1.5 被解释为 36 小时 0 分钟。
¥For absolute time formats like [hh]:mm, the integer part represents a whole
number of 24-hour (or 1440 minute) intervals. The value 1.5 in the format
[hh]:mm is interpreted as 36 hours 0 minutes.
日期和时间数字格式
¥Date and Time Number Formats
假设单元格具有格式化日期，重新格式化为 "一般的" 将显示基础值。或者，可以使用 TEXT 函数返回日期代码。
¥Assuming a cell has a formatted date, re-formatting as "General" will reveal
the underlying value. Alternatively, the TEXT function can be used to return
the date code.
下表涵盖了一些常见的格式：
¥The following table covers some common formats:
Common Date-Time Formats (click to show)
| Fragment | Interpretation |
|---|---|
yy | Short (2-digit) year |
yyyy | Long (4-digit) year |
m | Short (1-digit) month |
mm | Long (2-digit) month |
mmm | Short (3-letter) month name |
mmmm | Full month name |
mmmmm | First letter of month name |
d | Short (1-digit) day of month |
dd | Long (2-digit) day of month |
ddd | Short (3-letter) day of week |
dddd | Full day of week |
h | Short (1-digit) hours |
hh | Long (2-digit) hours |
m | Short (1-digit) minutes |
mm | Long (2-digit) minutes |
s | Short (1-digit) seconds |
ss | Long (2-digit) seconds |
A/P | Meridiem ("A" or "P") |
AM/PM | Meridiem ("AM" or "PM") |
m and mm are context-dependent. It is interpreted as "minutes" when the
previous or next date token represents a time (hours or seconds):
yyyy-mm-dd hh:mm:ss
^^ ^^
month minutes
mmm, mmmm, and mmmmm always represent months.
1904 和 1900 日期系统
¥1904 and 1900 Date Systems
日期代码的解释需要对日期代码 0（也称为 "epoch"）有共同的理解。Excel 支持两个纪元：
¥The interpretation of date codes requires a shared understanding of date code
0, otherwise known as the "epoch". Excel supports two epochs:
-
默认纪元是 "1900 年 1 月 0 日"。
0值是 1899 年 12 月 31 日的 00:00，但其格式为 1900 年 1 月 0 日。¥The default epoch is "January 0 1900". The
0value is 00:00 on December 31 of the year 1899, but it is formatted as January 0 1900. -
启用 "1904 日期系统" 将默认纪元设置为 "1904 年 1 月 1 日"。
0值为 1904 年 1 月 1 日的 00:00。¥Enabling "1904 Date System" sets the default epoch to "January 1 1904". The
0value is 00:00 on January 1 of the year 1904.
可以通过检查工作簿的 wb.Workbook.WBProps.date1904 属性来确定工作簿的纪元：
¥The workbook's epoch can be determined by examining the workbook's wb.Workbook.WBProps.date1904 property:
if(!(wb?.Workbook?.WBProps?.date1904)) {
/* uses 1904 date system */
} else {
/* uses 1900 date system */
}
Why does the 1904 date system exist? (click to show)
1900 was not a leap year. For the Gregorian calendar, the general rules are:
- every multiple of 400 is a leap year
- every multiple of 100 that is not a multiple of 400 is not a leap year
- every multiple of 4 that is not a multiple of 100 is a leap year
- all other years are not leap years.
Lotus 1-2-3 erroneously treated 1900 as a leap year. This can be verified with
the @date function:
@date(0,2,28) -> 59 // Lotus accepts 2/28/1900
@date(0,2,29) -> 60 // <--2/29/1900 was not a real date
@date(0,2,30) -> ERR // Lotus rejects 2/30/1900
Excel extends the tradition in the default date system. The 1904 date system starts the count in 1904, skipping the bad date.
相对历元
¥Relative Epochs
纪元基于系统时区。纽约的纪元是东部时间的午夜，而西雅图的纪元是太平洋时间的午夜。
¥The epoch is based on the system timezone. The epoch in New York is midnight in Eastern time, while the epoch in Seattle is midnight in Pacific time.
这种设计的优点是时间显示统一：无论监视者位于哪个时区，"中午 12 点" 都是中午 12 点。然而，这种设计排除了任何国际协调（无法创建代表绝对时间的值），并使 JavaScript 处理有些模糊（因为 JavaScript Date 对象是时区感知的）
¥This design has the advantage of uniform time display: "12 PM" is 12 PM irrespective of the timezone of the viewer. However, this design precludes any international coordination (there is no way to create a value that represents an absolute time) and makes JavaScript processing somewhat ambiguous (since JavaScript Date objects are timezone-aware)
这是电子表格软件的缺陷。Excel 本身没有通用时间概念。
¥This is a deficiency of the spreadsheet software. Excel has no native concept of universal time.
文件如何存储日期和时间
¥How Files Store Dates and Times
Technical Details (click to show)
XLS, XLSB, and most binary formats store the raw date codes. Special number formats are used to indicate that the values are intended to be dates/times.
CSV and other text formats typically store actual formatted date values. They are interpreted as dates and times in the user timezone.
XLSX actually supports both! Typically dates are stored as n numeric cells,
but the format supports a special type d where the data is an ISO 8601 date
string. This is not used in the default Excel XLSX export and third-party
support is poor.
ODS does support absolute time values but drops the actual timezone indicator when parsing. In that sense, LibreOffice follows the same behavior as Excel.
Numbers uses a calendar date system, but records pure time values as if they are absolute times in 1904 January 01. It is spiritually equivalent to the 1904 mode in Excel and other spreadsheet applications.
JavaScript 引擎如何理解时间
¥How JavaScript Engines Understand Time
JavaScript 提供了一个代表绝对时间的 Date 对象。在幕后，Date 使用 UTC 1970 年 1 月 1 日午夜的 "UNIX" 纪元。这意味着不同时区的实际零日期是不同的！
¥JavaScript provides a Date object which represents an absolute time. Under
the hood, Date uses the "UNIX" epoch of 1970 January 01 midnight in UTC. This
means the actual zero date is different in different timezones!
| 地点 | IANA 时区 | 当地时间 new Date(0) |
|---|---|---|
| 檀香山 | Pacific/Honolulu | 1969-12-31 02:00 PM |
| 洛杉矶 | America/Los_Angeles | 1969-12-31 04:00 PM |
| 纽约 | America/New_York | 1969-12-31 07:00 PM |
| 圣保罗 | America/Sao_Paulo | 1969-12-31 09:00 PM |
| 伦敦 | Europe/London | 1970-01-01 01:00 AM |
| 开罗 | Africa/Cairo | 1970-01-01 02:00 AM |
| 吉布提 | Africa/Djibouti | 1970-01-01 03:00 AM |
| 钦奈 | Asia/Kolkata | 1970-01-01 05:30 AM |
| 上海 | Asia/Shanghai | 1970-01-01 08:00 AM |
| 汉城 | Asia/Seoul | 1970-01-01 09:00 AM |
| 悉尼 | Australia/Sydney | 1970-01-01 10:00 AM |
在现代环境中，可以通过 Intl 和 Date 对象发现 IANA 时区和时区偏移：
¥In modern environments, the IANA Timezone and timezone offset can be discovered
through the Intl and Date objects:
function LocalInfo() { const date = new Date(); return ( <> <b>Local Time</b>: {date.toString()}<br/> <b>Time offset (relative to UTC)</b>: {-date.getTimezoneOffset()/60} hours <br/> <b>IANA Timezone</b>: {Intl.DateTimeFormat().resolvedOptions().timeZone} </>)}
时区信息由 JavaScript 引擎和本地设置提供。Google Chrome 和 V8 存在与农历下时区的舍入偏移相关的突出错误。最后一个切换到公历的时区是 Africa/Monrovia（1972 年）。
¥The timezone information is provided by the JavaScript engine and local settings.
There are outstanding Google Chrome and V8 bugs related to rounded offsets for
timezones under a lunar calendar. The last timezone to switch to the Gregorian
calendar was Africa/Monrovia (in 1972).
SheetJS 实用程序尝试解决浏览器错误。
¥SheetJS utilities attempt to work around the browser bugs.
UTC 和当地时间
¥UTC and Local Time
Date 对象有许多用于检查该对象的原型方法。有些方法与真实值交互，而另一些方法则转换为本地时区。下表列出了一些方法：
¥The Date object has a number of prototype methods for inspecting the object.
Some methods interact with the true value, while others convert to the local
timezone. Some methods are listed in the table below:
| 特性 | 当地时间法 | 世界标准时间方法 |
|---|---|---|
| 年 | getFullYear | getUTCFullYear |
| 月份（0-11） | getMonth | getUTCMonth |
| 该月的哪一天 | getDate | getUTCDate |
| 小时 | getHours | getUTCHours |
| 分钟 | getMinutes | getUTCMinutes |
| 秒数 | getSeconds | getUTCSeconds |
| 整个日期 | toString | toUTCString |
网站和其他应用通常以当地时间渲染数据。为了服务国际受众，后端服务器通常使用 UTC 时间。
¥It is typical for websites and other applications to present data in local time. To serve an international audience, backend servers typically use UTC time.
以下示例显示了页面加载的时间。相同的绝对时间在本地和 UTC 解释下似乎有所不同：
¥The following example shows the time when the page was loaded. The same absolute time will appear to be different under local and UTC interpretations:
function LocalUTC() { const d = new Date(); /* display number with 2 digits, prepending `0` if necessary */ const f = (n) => n.toString().padStart(2, "0"); /* HH:MM:SS using local interpretation */ const local = `${f(d.getHours())}:${f(d.getMinutes())}:${f(d.getSeconds())}`; /* HH:MM:SS using UTC interpretation */ const utc = `${f(d.getUTCHours())}:${f(d.getUTCMinutes())}:${f(d.getUTCSeconds())}`; return ( <> <b>Local Interpretation</b><br/> <code>toString</code>: {d.toString()}<br/> 24-hour time: {local}<br/> <br/> <b>UTC Interpretation</b><br/> <code>toUTCString</code>: {d.toUTCString()}<br/> 24-hour time: {utc}<br/> </>)}
SheetJS 如何处理日期和时间
¥How SheetJS handles Dates and Times
SheetJS 尝试协调电子表格和 JavaScript 日期概念。
¥SheetJS attempts to reconcile the spreadsheet and JavaScript date concepts.
所有解析器的默认行为是生成数字单元格。将 cellDates 设置为 true 将强制解析器存储日期。
¥The default behavior for all parsers is to generate number cells. Setting
cellDates to true will force the parsers to store dates.
function SheetJSCellDates() { var csv = "Date,10/6/2048"; // cell B1 will be { t: 'n', v: 54337 } var wb_sans_date = XLSX.read(csv, {type:"binary"}); var ws_sans_date = wb_sans_date.Sheets.Sheet1; // cell B1 will be { t: 'd', v: <Date: 2048-10-06 00:00:00 UTC> } var wb_with_date = XLSX.read(csv, {type:"binary", cellDates: true}); var ws_with_date = wb_with_date.Sheets.Sheet1; return (<> <b>CSV:</b><pre>{csv}</pre> <b>Cell B1:</b><br/><br/> <table><tr><th>cellDates</th><th>type</th><th>value</th></tr> <tr><td>(unspecified)</td> <td><code>{ws_sans_date["B1"].t}</code></td> <td><code>{ws_sans_date["B1"].v}</code></td> </tr> <tr><td>true</td> <td><code>{ws_with_date["B1"].t}</code></td> <td><code>{ws_with_date["B1"].v.toISOString()}</code> (Date object)</td> </tr> </table> </>); }
写入时，日期单元格会自动转换回具有适当数字格式的数字单元格。
¥When writing, date cells are automatically translated back to numeric cells with an appropriate number format.
值格式化逻辑理解日期格式并在相关时进行转换。它始终使用 Date 对象的 UTC 解释。
¥The value formatting logic understands date formats and converts when relevant. It always uses the UTC interpretation of Date objects.
日期对象
¥Date Objects
当使用 UTC 日期方法解释时，存储在单元格中的实际值应该是正确的。
¥The actual values stored in cells are intended to be correct when interpreted using UTC date methods.
例如，DateTime.xlsx 是一个测试文件，其中包含以下数据：
¥For example, DateTime.xlsx is a test file with the following data:
| 类型 | 值 |
|---|---|
| 日期 | 2048-10-06 |
| 时间 | 15:00 |
| 日期时间 | 2048-10-06 15:00:00 |
原始数据值显示在在线演示中。无论本地时区如何，UTC 日期字符串都将显示与 Excel 相同的值。
¥The raw data values are shown in the live demo. The UTC date string will show the same value as Excel irrespective of the local timezone.
function SheetJSDateTimeXlsxValues() { const [data, setData] = React.useState([[]]); React.useEffect(() => { (async() => { const ab = await (await fetch("/DateTime.xlsx")).arrayBuffer(); const wb = XLSX.read(ab, {cellDates: true, dense: true}); setData(wb.Sheets.Sheet1["!data"]); })(); }); return ( <table><thead> <th>Excel Date</th><th>UTC Date</th><th>Local Date</th> </thead><tbody> {data.slice(1).map((row,R) => ( <tr key={R}> <td>{row[1].w}</td> <td>{row[1].v.toUTCString()}</td> <td>{row[1].v.toString()}</td> </tr> ))} </tbody></table> ); }
实用函数
¥Utility Functions
处理 JS 数据的实用函数接受 cellDates 参数，该参数指示应如何处理日期。
¥Utility functions that deal with JS data accept a cellDates argument which
dictates how dates should be handled.
创建工作表的函数将调整日期单元格并使用 m/d/yy 等数字格式来标记日期：
¥Functions that create a worksheet will adjust date cells and use a number
format like m/d/yy to mark dates:
// Cell A1 will be a numeric cell whose value is the date code
var ws = XLSX.utils.aoa_to_sheet([[new Date()]]);
// Cell A1 will be a date cell
var ws = XLSX.utils.aoa_to_sheet([[new Date()]], { cellDates: true });
使用原始值创建 JS 对象数组的函数将保留原生表示：
¥Functions that create an array of JS objects with raw values will keep the native representation:
// Cell A1 is numeric -> output is a number
var ws = XLSX.utils.aoa_to_sheet([[new Date()]]);
var A1 = XLSX.utils.sheet_to_json(ws, { header: 1 })[0][0];
// Cell A1 is a date -> output is a date
var ws = XLSX.utils.aoa_to_sheet([[new Date()]], { cellDates: true });
var A1 = XLSX.utils.sheet_to_json(ws, { header: 1 })[0][0];
世界标准时间选项
¥UTC Option
某些 API 函数支持 UTC 选项来控制日期的处理方式。
¥Some API functions support the UTC option to control how dates are handled.
如果 UTC 为真，则以 UTC 解释时日期将是正确的。默认情况下，以当地时间解释时日期将是正确的。
¥If UTC is true, the dates will be correct when interpreted in UTC. By default,
the dates will be correct when interpreted in local time.
通常，UTC 用于来自 API 端点的数据，因为服务器通常会触发 UTC 日期并期望脚本进行本地化。当用户提交数据时，本地解释是明智的，因为他们将提供本地时区的时间。
¥Typically UTC is used for data from an API endpoint, as servers typically emit
UTC dates and expect scripts to localize. The local interpretation is sensible
when users submit data, as they will be providing times in their local timezone.
aoa_to_sheet/sheet_add_aoa/json_to_sheet/sheet_add_json
如果 UTC 为 true，则将使用 UTC 日期解释。
¥If UTC is true, the UTC interpretation of dates will be used.
通常，UTC 用于来自 API 端点的数据，因为服务器通常会触发 UTC 日期并期望脚本进行本地化。当浏览器中生成日期对象时，本地解释是明智的。
¥Typically UTC is used for data from an API endpoint, as servers typically emit
UTC dates and expect scripts to localize. The local interpretation is sensible
when date objects are generated in the browser.
table_to_book / table_to_sheet / sheet_add_dom
如果 UTC 为 true，则潜在日期将被解释为代表 UTC 时间。默认情况下，潜在日期以当地时间解释。
¥If UTC is true, potential dates are interpreted as if they represent UTC times.
By default, potential dates are interpreted in local time.
通常，UTC 用于从 Excel 或其他电子表格应用导出的数据。如果该表是在前端以编程方式生成的，则日期和时间将采用本地时区，并且本地解释更好。
¥Typically UTC is used for data exported from Excel or other spreadsheet apps.
If the table is programmatically generated in the frontend, the dates and times
will be in the local timezone and the local interpretation is preferable.
数字格式
¥Number Formats
默认情况下，不触发数字格式。对于基于 Excel 的文件格式，传递选项 cellNF: true 将添加 z 字段。
¥By default, the number formats are not emitted. For Excel-based file formats,
passing the option cellNF: true adds the z field.
辅助函数 XLSX.SSF.is_date 解析格式并在格式表示日期或时间时返回 true：
¥The helper function XLSX.SSF.is_date parses formats and returns true if the
format represents a date or time:
XLSX.SSF.is_date("yyyy-mm-dd"); // true
XLSX.SSF.is_date("0.00"); // false
Live Demo (click to show)
function SSFIsDate() { const [format, setFormat] = React.useState("yyyy-mm-dd"); const cb = React.useCallback((evt) => { setFormat(evt.target.value); }); const is_date = XLSX.SSF.is_date(format); return ( <> <div>Format <b>|{format}|</b> is {is_date ? "" : "not"} a date/time</div> <input type="text" onChange={cb}/> </> ); }
JSON 和 API 如何理解时间
¥How JSON and APIs Understand Time
JSON 没有 JavaScript Date 对象的原生表示形式。
¥JSON does not have a native representation for JavaScript Date objects.
从 Date 对象开始，JSON.stringify 方法会将对象编码为 ISO 8601 日期字符串。将 JSON.parse 应用于结果将返回字符串而不是正确的 Date 对象。
¥Starting from a Date object, the JSON.stringify method will encode the object
as a ISO 8601 date string. Applying JSON.parse to the result will return the
string rather than a proper Date object.
SheetJS 实用函数不会尝试将这些字符串解释为日期。相反，字符串将被转换为文本。
¥SheetJS utility functions will not try to interpret those strings as dates. Instead, the strings will be translated to text.
在以下示例中，"ISO 文本" 数据将转换为字符串单元格，而 "日期对象" 数据将转换为电子表格日期。
¥In the following example, the "ISO Text" data will be converted to a string cell while the "Date Obj" data will be converted to a spreadsheet date.
function SheetJSONDates() { return ( <button onClick={() => { const aoa = [ ["ISO Text", "2048-10-06T00:00:00.000Z"], // B1 will be text ["Date Obj", new Date("2048-10-06T00:00:00.000Z")] // B2 will be a date ]; const ws = XLSX.utils.aoa_to_sheet(aoa); const wb = XLSX.utils.book_new(ws, "Data"); XLSX.writeFile(wb, "SheetJSONDates.xlsx"); }}>Click to Export Sample Data</button> ); }
许多 API 封装器库将日期作为字符串而不是 Date 对象返回。
¥Many API wrapper libraries return dates as strings instead of Date objects.
如果 SheetJS 操作生成字符串单元格，请查看封装器库的文档，以确保其他库正确处理日期。
¥If the SheetJS operations generate string cells, review the documentation for the wrapper library to ensure the other library is properly handling dates.
修复对象数组
¥Fixing Arrays of Objects
许多 API 将以 JSON 对象的形式返回数据。当已知特定字段包含日期字符串时，可以手动修复它们。
¥Many APIs will return data as JSON objects. When particular fields are known to contain date strings, they can be manually fixed.
例如，导出教程 创建一个最终对象数组，其中生日存储为字符串：
¥For example, the Export Tutorial makes a final array of objects with birthdays stored as strings:
const rows = [
{ name: "George Washington", birthday: "1732-02-22" },
{ name: "John Adams", birthday: "1735-10-19" },
// ... one row per President
];
如果导出此数据集，birthday 列将包含原始文本值。单个 Array#map 操作可以创建固定数据集：
¥If this dataset is exported, the birthday column will contain raw text values.
A single Array#map operation can create a fixed dataset:
const new_rows = rows.map(({birthday, ...rest}) => ({birthday: new Date(birthday), ...rest}))
Date 构造函数以当地时间解释日期。
¥The Date constructor interprets the dates in local time.
Excel 和其他电子表格软件通常不支持 1900 年之前的日期。如果阈值之前有日期，强烈建议传递字符串而不是 Date 对象。
¥Excel and other spreadsheet software do not typically support dates before 1900.
If there are dates before the threshold, it is strongly recommended to pass
strings instead of Date objects.
JavaScript 字符串到 Date 的转换是 "implementation-dependent"，可能会误解某些日期格式。设计 API 时，强烈建议尽可能传递 ISO 8601 字符串。
¥JavaScript string to Date conversion is "implementation-dependent" and may
misinterpret some date formats. When designing APIs, it is strongly recommended
to pass ISO 8601 strings when possible.