1、excel文件
1）单行表头，常规格式
2）多行表头，第一行是合并行
2、导入文件
/**
* 从Excel文件导入数据，由前端解析文件，获取数据
* @param file 导入文件
* @param colKeyMap 列名键值映射，key --> value，如：excel中列名为【样品编号】，其键值设置对应为【sampleNo】
* @returns 列表数据
*/
export async function importExcelFileByClient(file: any, keyColMap: Record<string, string>) {
console.log("file: ", file);file: File {name: '导入试剂-1-默认日期格式-单行表头.xlsx', lastModified: 1750482505273, lastModifiedDate: Sat Jun 21 2025 13:08:25 GMT+0800 (中国标准时间), webkitRelativePath: '', size: 8779, …}
{}3、获取文件数据
// 获取文件数据
const fileData = fileReader.result;
console.log("file => fileData: ", fileData);file => fileData: ArrayBuffer(8779)
{}4、读取工作薄 workbook
// 读取工作薄 workbook
const workbook = xlsx.read(fileData, { type: "array" });
console.log("fileData => workbook: ", workbook);fileData => workbook: {Directory: {…}, Workbook: {…}, Props: {…}, Custprops: {…}, Deps: {…}, …}
{
"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": "6",
"lowestedited": "6",
"rupBuild": "28827",
"rupbuild": "28827"
},
"WBProps": {
"allowRefreshQuery": false,
"autoCompressPictures": true,
"backupFile": false,
"checkCompatibility": false,
"CodeName": "",
"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": "-90",
"xwindow": "-90",
"yWindow": "-90",
"ywindow": "-90",
"windowWidth": "19380",
"windowwidth": "19380",
"windowHeight": "11460",
"windowheight": "11460",
"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": "162913",
"calcid": "162913",
"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": "GB Yang",
"Author": "GB Yang",
"CreatedDate": "2015-06-05T18:19:34.000Z",
"ModifiedDate": "2025-06-21T05:08:25.000Z",
"Application": "Microsoft Excel",
"AppVersion": "16.0300",
"DocSecurity": "0",
"HyperlinksChanged": false,
"SharedDoc": false,
"LinksUpToDate": false,
"ScaleCrop": false,
"Worksheets": 1,
"SheetNames": [
"Sheet1"
]
},
"Custprops": {},
"Deps": {},
"Sheets": {
"Sheet1": {
"!ref": "A1:D3",
"A1": {
"t": "s",
"v": "试剂名称",
"r": "<t>试剂名称</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "试剂名称",
"w": "试剂名称"
},
"B1": {
"t": "s",
"v": "有效期至",
"r": "<t>有效期至</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "有效期至",
"w": "有效期至"
},
"C1": {
"t": "s",
"v": "入库数量",
"r": "<t>入库数量</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "入库数量",
"w": "入库数量"
},
"D1": {
"t": "s",
"v": "入库金额",
"r": "<t>入库金额</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "入库金额",
"w": "入库金额"
},
"A2": {
"t": "s",
"v": "名称001",
"r": "<t>名称001</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "名称001",
"w": "名称001"
},
"B2": {
"t": "n",
"v": 45830,
"w": "6/22/25"
},
"C2": {
"t": "n",
"v": 101,
"w": "101"
},
"D2": {
"t": "n",
"v": 1234.56,
"w": "1234.56"
},
"A3": {
"t": "s",
"v": "名称002",
"r": "<t>名称002</t>",
"h": "名称002",
"w": "名称002"
},
"B3": {
"t": "n",
"v": 45831,
"w": "6/23/25"
},
"C3": {
"t": "n",
"v": 101,
"w": "101"
},
"D3": {
"t": "n",
"v": 1234.56,
"w": "1234.56"
},
"!margins": {
"left": 0.7,
"right": 0.7,
"top": 0.75,
"bottom": 0.75,
"header": 0.3,
"footer": 0.3
}
}
},
"SheetNames": [
"Sheet1"
],
"Strings": [
{
"t": "试剂名称",
"r": "<t>试剂名称</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "试剂名称"
},
{
"t": "有效期至",
"r": "<t>有效期至</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "有效期至"
},
{
"t": "入库数量",
"r": "<t>入库数量</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "入库数量"
},
{
"t": "入库金额",
"r": "<t>入库金额</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "入库金额"
},
{
"t": "名称001",
"r": "<t>名称001</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "名称001"
},
{
"t": "名称002",
"r": "<t>名称002</t>",
"h": "名称002"
},
{
"t": ""
}
],
"Styles": {
"Fonts": [
{
"sz": 11,
"color": {
"theme": 1
},
"name": "等线",
"family": 2,
"scheme": "minor"
},
{
"sz": 9,
"name": "等线",
"family": 3,
"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"
},
{
"numFmtId": 0,
"numfmtid": "0",
"fontId": 0,
"fontid": "0",
"fillId": 0,
"fillid": "0",
"borderId": 0,
"borderid": "0",
"xfId": 0,
"xfid": "0",
"applyAlignment": true,
"applyalignment": "1",
"alignment": {
"horizontal": "left"
}
},
{
"numFmtId": 14,
"numfmtid": "14",
"fontId": 0,
"fontid": "0",
"fillId": 0,
"fillid": "0",
"borderId": 0,
"borderid": "0",
"xfId": 0,
"xfid": "0",
"applyNumberFormat": true,
"applynumberformat": "1",
"applyAlignment": true,
"applyalignment": "1",
"alignment": {
"horizontal": "left"
}
}
]
},
"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\"秒 \""
}
}5、读取第一个worksheet
// 表格是有序列表，因此可以取多个 Sheet，这里取第一个 Sheet
const sheetName = workbook.SheetNames[0];
console.log("sheetName: ", sheetName);
const workSheet = workbook.Sheets[sheetName];
console.log("workbook => workSheet: ", workSheet);sheetName: Sheet1
workbook => workSheet: {!ref: 'A1:D3', A1: {…}, B1: {…}, C1: {…}, D1: {…}, …}
{
"!ref": "A1:D3",
"A1": {
"t": "s",
"v": "试剂名称",
"r": "<t>试剂名称</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "试剂名称",
"w": "试剂名称"
},
"B1": {
"t": "s",
"v": "有效期至",
"r": "<t>有效期至</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "有效期至",
"w": "有效期至"
},
"C1": {
"t": "s",
"v": "入库数量",
"r": "<t>入库数量</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "入库数量",
"w": "入库数量"
},
"D1": {
"t": "s",
"v": "入库金额",
"r": "<t>入库金额</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "入库金额",
"w": "入库金额"
},
"A2": {
"t": "s",
"v": "名称001",
"r": "<t>名称001</t><phoneticPr fontId=\"1\" type=\"noConversion\"/>",
"h": "名称001",
"w": "名称001"
},
"B2": {
"t": "n",
"v": 45830,
"w": "6/22/25"
},
"C2": {
"t": "n",
"v": 101,
"w": "101"
},
"D2": {
"t": "n",
"v": 1234.56,
"w": "1234.56"
},
"A3": {
"t": "s",
"v": "名称002",
"r": "<t>名称002</t>",
"h": "名称002",
"w": "名称002"
},
"B3": {
"t": "n",
"v": 45831,
"w": "6/23/25"
},
"C3": {
"t": "n",
"v": 101,
"w": "101"
},
"D3": {
"t": "n",
"v": 1234.56,
"w": "1234.56"
},
"!margins": {
"left": 0.7,
"right": 0.7,
"top": 0.75,
"bottom": 0.75,
"header": 0.3,
"footer": 0.3
}
}6、worksheet 转 sheetjson
6.1-1、worksheet 转 sheetjson（[[]]）
header: 1，转出来的就是二维数组格式 [[]]
只有header设置为1，xlsx.utils.sheet_to_json 转换出来的才是二维数组 [[]]，
其他参数，转换出来的是数组对象 [{}]
// 将 Excel 表格数据转为 JSON 格式
// header: 1，控制表头处理方式
// raw: true，禁止自动解析单元格格式（如日期、数字），保持原始值。
// 数据样式：二维数组 [[]]
// 如：[
// ['试剂名称', '有效期至', '入库数量', '入库金额'],
// ['名称001', 45830, 101, 1234.56],
// ['名称002', 45831, 101, 1234.56]
// ]
const sheetJson = xlsx.utils.sheet_to_json(worksheet, { header: 1 });
console.log("worksheet => sheetJson（[[]]）: ", sheetJson);workSheet => sheetJson（[[]]）: (3) [Array(4), Array(4), Array(4)]
[
[
"试剂名称",
"有效期至",
"入库数量",
"入库金额"
],
[
"名称001",
45830,
101,
1234.56
],
[
"名称002",
45831,
101,
1234.56
]
]
6.1-2、sheetJson（[[]]） 转 sheetJson（[{}]）
dataList = convertSheetToJson(workSheet);
console.log("sheetJson（[[]]） => sheetJson（[{}]）: ", dataList);sheetJson（[[]]） => sheetJson（[{}]）: (2) [{…}, {…}]
[
{
"试剂名称": "名称001",
"有效期至": "2025-06-22T00:00:00.000Z",
"入库数量": 101,
"入库金额": 1234.56
},
{
"试剂名称": "名称002",
"有效期至": "2025-06-23T00:00:00.000Z",
"入库数量": 101,
"入库金额": 1234.56
}
]6.2、worksheet 转 sheetjson（[{}]）
不设置header，默认就是0，xlsx.utils.sheet_to_json 转换出来的是数组对象 [{}]，这种模式才有 __rowNum__ 属性
// 将 Excel 表格数据转为 JSON 格式
// raw: false，获取格式化值
// dateNF: "yyyy-mm-dd"，日期格式
// 数据样式：[{"A","B"}, {1,"Text"}]
// 如：[
// {试剂名称: '名称001', 有效期至: '6/22/25', 入库数量: '101', 入库金额: '1234.56', __rowNum__: 1},
// {试剂名称: '名称002', 有效期至: '6/23/25', 入库数量: '101', 入库金额: '1234.56', __rowNum__: 2}
// ]
const sheetJson = xlsx.utils.sheet_to_json(worksheet, { raw: false, dateNF: "yyyy-mm-dd" }) as any[];
console.log("worksheet => sheetJson（[{}]）: ", sheetJson);worksheet => sheetJson（[{}]）: (2) [{…}, {…}]
[
{
"试剂名称": "名称001",
"有效期至": "6/22/25",
"入库数量": "101",
"入库金额": "1234.56"
},
{
"试剂名称": "名称002",
"有效期至": "6/23/25",
"入库数量": "101",
"入库金额": "1234.56"
}
]7、参照映射表，格式化 json，输出目标数据
// 方法1
dataList = convertSheetToJson(worksheet, keyColMap);
console.log("sheetJson（[{}]） => targetData: ", dataList);sheetJson（[{}]） => targetData: (2) [{…}, {…}]
[
{
"reagentName": "名称001",
"validityDate": "2025-06-22T00:00:00.000Z",
"amount": 101,
"total": 1234.56
},
{
"reagentName": "名称002",
"validityDate": "2025-06-23T00:00:00.000Z",
"amount": 101,
"total": 1234.56
}
]8、前端定义
// 试剂入库信息
export interface IReagentInByCkDetail {
......
// 试剂名称
reagentName?: string;
// 有效期至
validityDate?: string;
// 数量
amount?: number;
// 金额
total?: number;
}9、前端展示
汇总
file: File {name: '导入试剂-1-默认日期格式-单行表头.xlsx', lastModified: 1750482505273, lastModifiedDate: Sat Jun 21 2025 13:08:25 GMT+0800 (中国标准时间), webkitRelativePath: '', size: 8779, …}lastModified: 1750482505273lastModifiedDate: Sat Jun 21 2025 13:08:25 GMT+0800 (中国标准时间) {}name: "导入试剂-1-默认日期格式-单行表头.xlsx"size: 8779type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"webkitRelativePath: ""[[Prototype]]: File
excelUtils.ts:168 file => fileData: ArrayBuffer(8779)byteLength: 8779detached: falsemaxByteLength: 8779resizable: false[[Prototype]]: ArrayBuffer[[Int8Array]]: Int8Array(8779)[[Uint8Array]]: Uint8Array(8779)[[ArrayBufferByteLength]]: 8779[[ArrayBufferData]]: 6570
excelUtils.ts:171 fileData => workbook: {Directory: {…}, Workbook: {…}, Props: {…}, Custprops: {…}, Deps: {…}, …}Custprops: {}Deps: {}Directory: {workbooks: Array(1), sheets: Array(1), charts: Array(0), dialogs: Array(0), macros: Array(0), …}Props: {LastAuthor: 'GB Yang', Author: 'GB Yang', CreatedDate: Sat Jun 06 2015 02:19:34 GMT+0800 (中国标准时间), ModifiedDate: Sat Jun 21 2025 13:08:25 GMT+0800 (中国标准时间), Application: 'Microsoft Excel', …}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"秒 "'}SheetNames: ['Sheet1']Sheets: {Sheet1: {…}}Strings: (7) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, Count: '6', Unique: '6']Styles: {Fonts: Array(2), Fills: Array(2), Borders: Array(1), CellXf: Array(3)}Themes: {}Workbook: {AppVersion: {…}, WBProps: {…}, WBView: Array(1), Sheets: Array(1), CalcPr: {…}, …}[[Prototype]]: Object
excelUtils.ts:174 sheetName: Sheet1
excelUtils.ts:189 workbook => workSheet: {!ref: 'A1:D3', A1: {…}, B1: {…}, C1: {…}, D1: {…}, …}
excelUtils.ts:239 workSheet => sheetJson（[[]]）: (3) [Array(4), Array(4), Array(4)]0: (4) ['试剂名称', '有效期至', '入库数量', '入库金额']1: (4) ['名称001', 45830, 101, 1234.56]2: (4) ['名称002', 45831, 101, 1234.56]length: 3[[Prototype]]: Array(0)
excelUtils.ts:191 sheetJson（[[]]） => sheetJson（[{}]）: (2) [{…}, {…}]0: {试剂名称: '名称001', 有效期至: Sun Jun 22 2025 08:00:00 GMT+0800 (中国标准时间), 入库数量: 101, 入库金额: 1234.56}1: {试剂名称: '名称002', 有效期至: Mon Jun 23 2025 08:00:00 GMT+0800 (中国标准时间), 入库数量: 101, 入库金额: 1234.56}length: 2[[Prototype]]: Array(0)
excelUtils.ts:194 sheetJson（[{}]） => targetData: (2) [{…}, {…}]
扩展
无表头表格
可以使用 header: ["A列", "B列", "C列", "D列"]，worksheet => sheetJson（[{}]
// 适用于指定导入范围 或 无表头的表格，header: ["A列", "B列", "C列", "D列"]，指定列名为 A列、B列、C列、D列
const sheetJson = xlsx.utils.sheet_to_json(worksheet, {
header: ["A列", "B列", "C列", "D列"],
raw: false,
dateNF: "yyyy-mm-dd"
}) as any[];
console.log("worksheet => sheetJson（[{}]）: ", sheetJson);worksheet => sheetJson（[{}]）: (2) [{…}, {…}]
[
{
"A列": "名称001",
"B列": "6/22/25",
"C列": "101",
"D列": "1234.56"
},
{
"A列": "名称002",
"B列": "6/23/25",
"C列": "101",
"D列": "1234.56"
}
]推荐使用 header: "A"，worksheet => sheetJson（[{}]
// 适用于无表头的表格，header: "A"，列名默认为 A、B、C、D、...
const sheetJson = xlsx.utils.sheet_to_json(worksheet, { header: "A", raw: false, dateNF: "yyyy-mm-dd" }) as any[];
console.log("worksheet => sheetJson（[{}]）: ", sheetJson);worksheet => sheetJson（[{}]）: (2) [{…}, {…}]
[
{
"A": "名称001",
"B": "6/22/25",
"C": "101",
"D": "1234.56"
},
{
"A": "名称002",
"B": "6/23/25",
"C": "101",
"D": "1234.56"
}
]