XLSX表格导出导入最简单封装

表格导出，是后台管理项目绕不过去的一个功能，最近刚好有时间，重新写个最简单的导入导出功能；

依赖

"xlsx": "^0.16.9",
"file-saver": "^2.0.5",

封装方法

import XLSX from 'xlsx'
import {saveAs} from 'file-saver';
/*
    ExportToExcel(
        '测试',
        [
            ["Name", "Age", "City"],
            ["John", 30, "New York"],
            ["Peter", 25, "Paris"],
            ["Mary", 40, "London"]
        ]
    );
*/
export function ExportToExcel(fileName: string, data: any) {
    /* 创建工作簿 */
    const workbook = XLSX.utils.book_new();
    /* 创建工作表 */
    const worksheet = XLSX.utils.aoa_to_sheet(data);
    /* 将工作表添加到工作簿 */
    XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
    /* 将工作簿导出为 Excel 文件 */
    const wbout = XLSX.write(workbook, { bookType: "xlsx", type: "binary" });
    saveAs(
      new Blob([s2ab(wbout)], { type: "application/octet-stream" }),
      `${fileName}.xlsx`
    );
}
export function HandleJsonObjToExportExcelData(tHeade: string[], tBody: any, isSerialNumber = true) {
    const arr: any = [];
    if (isSerialNumber) {
        arr[0] = ['序号', ...tHeade];
    } else {
        arr[0] = tHeade;
    }
    tBody.forEach((item: any, idx: number) => {
        let serialNumber: any = [];
        if (isSerialNumber) {serialNumber = [idx+1]}
        arr[idx+1] = [
            ...serialNumber,
            ...Object.values(item),
        ];
    });
    return arr;
}
/* 将字符串转换为 ArrayBuffer */
function s2ab(s: any) {
    const buf = new ArrayBuffer(s.length);
    const view = new Uint8Array(buf);
    for (let i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xff;
    return buf;
}
/**
 * xlsx文件转换成json数据
 * @param {File} file xlsx文件
 * @returns {Promise<error:string, Array>}
*/
export function ImportFileToData(file: any) {
    return new Promise((resolve, reject) => {
        const reader = new FileReader()
        reader.onload = function (e: any) {
            try{
                /* 表格内容 */
                const data = e.target.result
                const wb = XLSX.read(data, { type: 'binary' })
                const jsonData = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])
                /* 获取表头 */
                const firstSheetName = wb.SheetNames[0]
                const worksheet = wb.Sheets[firstSheetName]
                const header = getheaderrow(worksheet);
                resolve({header, results: jsonData});
            } catch(err) {
                reject('表格解析失败!')
            }
        }
        reader.onerror = () => reject('错误的文件类型!')
        reader.readAsBinaryString(file)
      })
}
function getheaderrow(sheet: any) {
    const headers = []
    const range = XLSX.utils.decode_range(sheet['!ref'])
    let C
    const R = range.s.r
    for (C = range.s.c; C <= range.e.c; ++C) {
        const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })]
        let hdr = 'UNKNOWN ' + C
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
        headers.push(hdr)
    }
    return headers
}

使用

import {ImportFileToData, ExportToExcel, HandleJsonObjToExportExcelData} from '@/views/Components/Xls';

// 读取导入xls
<input id="excel-upload-input" type="file" accept=".xlsx, .xls" onChange={handleImportXlsChange} />
const handleImportXlsChange = (e: any) => {
    ImportFileToData(e.target.files[0]).then((res) => {
    }).catch((err) => {
        console.log('err', err);
    })
}

// 直接导出
const excelList = [
    ['ID', '姓名', '年龄'],
    [1, '张三', 18],
    [2, '李四', 16],
    ['3', '王天霸', 20]
];
ExportToExcel('测试', excelList);
// 处理后导出
const jsonData =[
    {'id':1,'name':'张三','age':18},
    {'id':2,'name':'李四','age':16},
    {'id':'3','name':'王天霸','age':20}
]
const tableHead = [
    'ID', '姓名', '年龄'
]
const excelList = HandleJsonObjToExportExcelData(tableHead, jsonData);
ExportToExcel('测试', excelList);