写在前面：一般掌握第一种就好，代码摘自官网。代码其实不多，耐心就能看懂，只不过为了直接拿走就能运行，所以这里放了完整的代码，只是看起来比较多。
worksheet对象：excel中的一个sheet
workbook对象：一个excel文件
workbook对象结构：见结尾
1.安装
npm i https://cdn.sheetjs.com/xlsx-0.18.10/xlsx-0.18.10.tgz -S
pnpm install https://cdn.sheetjs.com/xlsx-0.18.10/xlsx-0.18.10.tgz -S
yarn add https://cdn.sheetjs.com/xlsx-0.18.10/xlsx-0.18.10.tgz -S
注意：使用 npm i xlsx -S只能安装旧版，详情原因
import { read, utils, writeFileXLSX } from "xlsx";
或
import * as XLSX from "xlsx";
2.excel文件（workbook对象）和js对象之间的转化
import { read, utils, writeFileXLSX } from "xlsx";
import { useEffect } from "react";
import { useState } from "react";
import { useCallback } from "react";
interface President {
Name: string;
Index: number;
}
const Excel1 = () => {
const [pres, setPres] = useState<any>([]);
// 导入文件
const fetchFile = async () => {
// 导入的文件，转化为arrayBufer
const f = await (
await fetch("https://sheetjs.com/pres.xlsx")
).arrayBuffer();
// 读取工作簿，使用read
const wb = read(f); //-------wb格式见底部
// 使用typescript传入数据骨架，再使用wb.Sheets[sheet名]
// wb.SheetNames获取sheet名的数组
// 最终得到一个数据js对象
console.log(wb);
// sheet对象格式----转化为-----js对象格式
const data = utils.sheet_to_json<President>(wb.Sheets[wb.SheetNames[0]]);
setPres(data);
};
// 导出文件
const exportFile = useCallback(() => {
// js对象格式----转化为----sheet对象格式
const ws = utils.json_to_sheet(pres);
// 创建一个工作簿
const wb = utils.book_new();
// 选写：这一行改写单元格数据，从A1（A列1行）开始修改这一行前两个单元格
utils.sheet_add_aoa(ws, [["姓名", "索引"]], { origin: "A1" });
// 选写：更改列宽 wch: width characters
ws["!cols"] = [{ wch: 30 }, { wch: 50 }];
// 将sheet插入工作簿，sheet取名为Data
utils.book_append_sheet(wb, ws, "Data");
// 导出工作簿，文件名为SheetJSReactAoO.xlsx
writeFileXLSX(wb, "SheetJSReactAoO.xlsx");
}, [pres]);
useEffect(() => {
fetchFile();
}, []);
return (
<table border={1}>
<thead>
<th style={{ width: "200px" }}>Name</th>
<th style={{ width: "200px" }}>Index</th>
</thead>
<tbody>
{
/* 预览表格 */
pres.map((pre) => (
<tr>
<td>{pre.Name}</td>
<td>{pre.Index}</td>
</tr>
))
}
</tbody>
<tfoot style={{ textAlign: "center" }}>
<td colSpan={2}>
{/* 点击把data对象导出为excel */}
<button onClick={exportFile}>Export XLSX</button>
</td>
</tfoot>
</table>
);
};
export default Excel1;
3.excel文件（workbook对象）和html中table元素之间的转化
import { useCallback, useEffect, useRef, useState } from "react";
import { read, utils, writeFileXLSX } from 'xlsx';
const Excel2 = () => {
// string里是HTML结构,将会放入table元素
const [html, setHtml] = useState("");
// 绑定元素，用于导出
const tbl = useRef(null);
// 获取excel文件
const importData = async () => {
// excel文件-----转化为-----arrayBuffer
const f = await (await fetch("https://sheetjs.com/pres.xlsx")).arrayBuffer();
// 使用read读取arrayBuffer，为工作簿
const wb = read(f);
// 读取工作簿中的第一个sheet
const ws = wb.Sheets[wb.SheetNames[0]];
// 将sheet转化为table结构
const data = utils.sheet_to_html(ws);
setHtml(data);
}
useEffect(() => {
importData();
}, []);
// 导出文件
const exportFile = useCallback(() => {
// 获取div元素内的标签名叫table的元素数组第0个
const elt = tbl.current.getElementsByTagName("table")[0];
// 将此table元素结构，转化为wb工作簿对象
const wb = utils.table_to_book(elt);
// 导出工作簿excel，文件名叫SheetJSReactHTML.xlsx
writeFileXLSX(wb, "SheetJSReactHTML.xlsx");
}, [tbl]);
return (
<div>
<button onClick={exportFile}>Export XLSX</button>
<div ref={tbl} dangerouslySetInnerHTML={{ __html: html }} />
</div>
)
}
export default Excel2;
4.workbook对象结构
上述代码只涉及到了Sheets，和sheetNames
const workbook = {
Directory: {
workbooks: ["/xl/workbook.xml"],
sheets: ["/xl/worksheets/sheet1.xml"],
charts: [],
dialogs: [],
macros: [],
rels: [],
strs: ["/xl/sharedStrings.xml"],
comments: [],
threadedcomments: [],
links: [],
coreprops: ["/docProps/core.xml"],
extprops: ["/docProps/app.xml"],
custprops: [],
themes: ["/xl/theme/theme1.xml"],
styles: ["/xl/styles.xml"],
vba: [],
drawings: [],
metadata: [],
people: [],
TODO: [],
xmlns: "http://schemas.openxmlformats.org/package/2006/content-types",
calcchain: "",
sst: "/xl/sharedStrings.xml",
style: "/xl/styles.xml",
defaults: {
rels: "application/vnd.openxmlformats-package.relationships+xml",
xml: "application/xml",
},
},
Workbook: {
AppVersion: {
appName: "xl",
appname: "xl",
lastEdited: "7",
lastedited: "7",
lowestEdited: "7",
lowestedited: "7",
rupBuild: "10410",
rupbuild: "10410",
},
WBProps: {
CodeName: "ThisWorkbook",
allowRefreshQuery: false,
autoCompressPictures: true,
backupFile: false,
checkCompatibility: false,
date1904: false,
defaultThemeVersion: 0,
filterPrivacy: false,
hidePivotFieldList: false,
promptedSolutions: false,
publishItems: false,
refreshAllConnections: false,
saveExternalLinkValues: true,
showBorderUnselectedTables: true,
showInkAnnotation: true,
showObjects: "all",
showPivotChartFilter: false,
updateLinks: "userSet",
},
WBView: [
{
xWindow: "0",
xwindow: "0",
yWindow: "0",
ywindow: "0",
windowWidth: "38400",
windowwidth: "38400",
windowHeight: "24000",
windowheight: "24000",
uid: "{00000000-000D-0000-FFFF-FFFF00000000}",
activeTab: 0,
autoFilterDateGrouping: true,
firstSheet: 0,
minimized: false,
showHorizontalScroll: true,
showSheetTabs: true,
showVerticalScroll: true,
tabRatio: 600,
visibility: "visible",
},
],
Sheets: [
{
name: "Sheet1",
sheetId: "1",
sheetid: "1",
id: "rId1",
Hidden: 0,
},
],
CalcPr: {
calcId: "191029",
calcid: "191029",
calcCompleted: "true",
calcMode: "auto",
calcOnSave: "true",
concurrentCalc: "true",
fullCalcOnLoad: "false",
fullPrecision: "true",
iterate: "false",
iterateCount: "100",
iterateDelta: "0.001",
refMode: "A1",
},
Names: [],
xmlns: "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
Views: [{}],
},
Props: {
LastAuthor: "Sheet",
ModifiedDate: "2022-05-12T18:49:58.000Z",
Application: "Microsoft Macintosh Excel",
AppVersion: "16.0300",
DocSecurity: "0",
HyperlinksChanged: false,
SharedDoc: false,
LinksUpToDate: false,
ScaleCrop: false,
Worksheets: 1,
SheetNames: ["Sheet1"],
},
Custprops: {},
Deps: {},
Sheets: {
Sheet1: {
"!ref": "A1:B6",
A1: {
t: "s",
v: "Name",
r: "<t>Name</t>",
h: "Name",
w: "Name",
},
B1: {
t: "s",
v: "Index",
r: "<t>Index</t>",
h: "Index",
w: "Index",
},
A2: {
t: "s",
v: "Bill Clinton",
r: "<t>Bill Clinton</t>",
h: "Bill Clinton",
w: "Bill Clinton",
},
B2: {
t: "n",
v: 42,
w: "42",
},
A3: {
t: "s",
v: "GeorgeW Bush",
r: "<t>GeorgeW Bush</t>",
h: "GeorgeW Bush",
w: "GeorgeW Bush",
},
B3: {
t: "n",
v: 43,
w: "43",
},
A4: {
t: "s",
v: "Barack Obama",
r: "<t>Barack Obama</t>",
h: "Barack Obama",
w: "Barack Obama",
},
B4: {
t: "n",
v: 44,
w: "44",
},
A5: {
t: "s",
v: "Donald Trump",
r: "<t>Donald Trump</t>",
h: "Donald Trump",
w: "Donald Trump",
},
B5: {
t: "n",
v: 45,
w: "45",
},
A6: {
t: "s",
v: "Joseph Biden",
r: "<t>Joseph Biden</t>",
h: "Joseph Biden",
w: "Joseph Biden",
},
B6: {
t: "n",
v: 46,
w: "46",
},
"!margins": {
left: 0.7,
right: 0.7,
top: 0.75,
bottom: 0.75,
header: 0.3,
footer: 0.3,
},
},
},
SheetNames: ["Sheet1"],
Strings: [
{
t: "Name",
r: "<t>Name</t>",
h: "Name",
},
{
t: "Index",
r: "<t>Index</t>",
h: "Index",
},
{
t: "Bill Clinton",
r: "<t>Bill Clinton</t>",
h: "Bill Clinton",
},
{
t: "GeorgeW Bush",
r: "<t>GeorgeW Bush</t>",
h: "GeorgeW Bush",
},
{
t: "Barack Obama",
r: "<t>Barack Obama</t>",
h: "Barack Obama",
},
{
t: "Donald Trump",
r: "<t>Donald Trump</t>",
h: "Donald Trump",
},
{
t: "Joseph Biden",
r: "<t>Joseph Biden</t>",
h: "Joseph Biden",
},
{
t: "",
},
],
Styles: {
Fonts: [
{
sz: 12,
color: {
theme: 1,
},
name: "Calibri",
family: 2,
scheme: "minor",
},
],
Fills: [
{
patternType: "none",
},
{
patternType: "gray125",
},
],
Borders: [{}],
CellXf: [
{
numFmtId: 0,
numfmtid: "0",
fontId: 0,
fontid: "0",
fillId: 0,
fillid: "0",
borderId: 0,
borderid: "0",
xfId: 0,
xfid: "0",
applyNumberFormat: true,
applynumberformat: "1",
},
],
},
Themes: {},
SSF: {
"0": "General",
"1": "0",
"2": "0.00",
"3": "#,##0",
"4": "#,##0.00",
"9": "0%",
"10": "0.00%",
"11": "0.00E+00",
"12": "# ?/?",
"13": "# ??/??",
"14": "m/d/yy",
"15": "d-mmm-yy",
"16": "d-mmm",
"17": "mmm-yy",
"18": "h:mm AM/PM",
"19": "h:mm:ss AM/PM",
"20": "h:mm",
"21": "h:mm:ss",
"22": "m/d/yy h:mm",
"37": "#,##0 ;(#,##0)",
"38": "#,##0 ;[Red](#,##0)",
"39": "#,##0.00;(#,##0.00)",
"40": "#,##0.00;[Red](#,##0.00)",
"45": "mm:ss",
"46": "[h]:mm:ss",
"47": "mmss.0",
"48": "##0.0E+0",
"49": "@",
"56": '"上午/下午 "hh"時"mm"分"ss"秒 "',
},
bookType: "xlsx",
};
