前端Excel的导入导出

create-react-app框架配合antd和xlsx实现Excel的导入导出

1 快速开始
yarn add xlsx

{
  "name": "my-app",
  "version": "0.1.0",
  "private": true,
  "dependencies": {
    "@testing-library/jest-dom": "^5.11.4",
    "@testing-library/react": "^11.1.0",
    "@testing-library/user-event": "^12.1.10",
    "antd": "^4.14.1",
    "react": "^17.0.2",
    "react-dom": "^17.0.2",
    "react-scripts": "4.0.3",
    "web-vitals": "^1.0.1",
    "xlsx": "^0.16.9"
  },
  "scripts": {
    "start": "react-scripts start",
    "build": "react-scripts build",
    "test": "react-scripts test",
    "eject": "react-scripts eject"
  },
  "eslintConfig": {
    "extends": [
      "react-app",
      "react-app/jest"
    ]
  },
  "browserslist": {
    "production": [
      ">0.2%",
      "not dead",
      "not op_mini all"
    ],
    "development": [
      "last 1 chrome version",
      "last 1 firefox version",
      "last 1 safari version"
    ]
  }
}

2 Excel的导入
ImportExcelModel组件

import {Upload, Button, message} from 'antd';
import React, {useState} from "react";
import XLSX from "xlsx";

function ImportExcelModel(props) {
    const {titlesName, keysName, requestUrl} = props;
    const [fileList, setFileList] = useState([]);
    const [uploading, setUploading] = useState(false);
    const [list, setList] = useState([]);
    const readExcel = (file) => {
        const reader = new FileReader();
        //5.绑定FileReader对象读取文件对象时的触发方法
        reader.onload = function (e) {
            //7.获取文件二进制数据流
            const data = e.currentTarget.result;
            //8.利用XLSX解析二进制文件为xlsx对象
            const wb = XLSX.read(data, {type: 'binary'})
            //9.利用XLSX把wb第一个sheet转换成JSON对象
            //wb.SheetNames[0]是获取Sheets中第一个Sheet的名字
            //wb.Sheets[Sheet名]获取第一个Sheet的数据
            const json = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])
            //10.在终端输出查看结果
            console.log(json);
            const array = json.map(item => {
                const obj = {};
                titlesName.forEach((value, index) => {
                    obj[keysName[index]] = item[titlesName[index]];
                })
                return obj;
            });
            console.log(array);
            setList(array);
        }
        //6.使用reader对象以二进制读取文件对象f，
        reader.readAsBinaryString(file);
    }


    const handleUpload = () => {
        console.log(list);
        // setUploading(true);
        // list是传给后端的数据  requestUrl post请求
        // setUploading(true);
    };

    const uploadProps = {
        onRemove: file => {
            const index = fileList.indexOf(file);
            const newFileList = fileList.slice();
            newFileList.splice(index, 1);
            setFileList(newFileList);
        },
        onChange({file, fileList}) {
            if (file.name.endsWith(".xlsx")) {
                readExcel(file);
            }
        },
        beforeUpload: file => {
            if (!file.name.endsWith(".xlsx")) {
                message.error("请上传.xlsx文件");
            } else {
                setFileList([...fileList, file]);
            }

            return false;
        },
        fileList,
    };


    return (
        <>
            <Upload {...uploadProps}>
                <Button disabled={fileList.length >= 1}>选择文件</Button>
            </Upload>
            <Button
                type="primary"
                onClick={handleUpload}
                disabled={fileList.length === 0}
                loading={uploading}
                style={{marginTop: 16}}
            >
                {uploading ? '上传中' : '开始上传'}
            </Button>
        </>
    );
}

export default ImportExcelModel;

然后再使用组件

	//要导入的Excel第一行的字符串名称标题
    const titlesName = ["编号", "名称", "性别", "日期"];
    //要转换成的对象的键名,与titlesName数组一一对应
    const keysName = ["orderNo", "name", "sex", "date"];
return (
        <div className="App">
            <ImportExcelModel titlesName={titlesName} keysName={keysName} requestUrl={"http://www.yourapi.com"}/>
        </div>
    );

转换后的数据是一个数组,即保存在list状态中，

[
    {orderNo: 1, name: "《Java架构师》", sex: "无", date: "2018-10-11"},
    {orderNo: 2, name: "《Python入门到精通》", sex: "男", date: "2019-03-22"},
    {orderNo: 3, name: "《JavaScript高级教程》", sex: "女", date: "2017-04-13"}
]

3 导出excel的代码

import XLSX from "xlsx";

/**
 * @param filename   文件名
 * @param titlesName
 * @param keysName   数据列表键数组，与标题数组一一对应
 * @param data       数据
 * @param translateFunc  转换函数对象,key是keysName里对应的键名，value是转换函数，该函数由需求来具体实现,要返回一个值,要是没有要转换的可以忽略
 */
export const exportExcel = (filename, titlesName = [],  keysName = [],
                            data = [], translateFunc = {}
) => {
    const excelData = [];
    excelData.push(titlesName);

        data.forEach(item => {
            const temp = [];
            keysName.forEach(key => {
                if (Object.is(typeof translateFunc[key], "function")) {
                    temp.push(translateFunc[key](item[key]));
                } else {
                    temp.push(item[key]);
                }
            });
            excelData.push(temp);
        })

    // 创建工作簿和工作表
    const wb = XLSX.utils.book_new(), // 工作簿，即一个Excel文件
        ws = XLSX.utils.aoa_to_sheet(excelData); // 工作表，即Excel内部的工作表

    // "SheetJS" 为工作表名称，即Excel文件中工作表
    XLSX.utils.book_append_sheet(wb, ws, "SheetJS");

    // 写出Excel工作簿
    XLSX.writeFile(wb, filename);
}

使用导出Excel的函数

import './App.css';
import {Button} from 'antd';
import React from "react";
import {exportExcel} from "./utils/ExcelUtil"

function App() {
    // Excel文件名称
    const filename = "yourFileName.xlsx";
    //后台返回的数据格式
    const array = [
        {orderNo: 1, name: "《Java架构师》", sex: 0, date: "2018-10-11"},
        {orderNo: 2, name: "《Python入门到精通》", sex: 1, date: "2019-03-22"},
        {orderNo: 3, name: "《JavaScript高级教程》", sex: 2, date: "2017-04-13"}
    ];
    //要导出的Excel第一行的字符串名称标题
    const titlesName = ["编号", "名称", "性别", "日期"];
    //array的对象的键名,与titlesName数组一一对应
    const keysName = ["orderNo", "name", "sex", "date"];
    //键值的转换函数,比如将性别为数字的0,1,2转换为无,男，女
    //translateFunction对象的属性名为keysName数组里对应的key名称,参数是array对象里对应key的value,函数返回要转换的字符串,具体实现可以自己定义
    const translateFunction = {
        sex: (sex) => {
            if (sex === 0) {
                return "无";
            }
            if (sex === 1) {
                return "男";
            }
            if (sex === 2) {
                return "女";
            }
        }
    }

    return (
        <div className="App">
            <Button type="primary"
                    onClick={() => exportExcel(filename, titlesName, keysName, array, translateFunction)}>导出Excel</Button>
        </div>
    );
}

export default App;

以上就是前端Excel导入导出的实现