React 前端导出 excel 表格

本次使用的 库为 xlsx-js-style

1.下载安装

npm install xlsx-js-style

项目要求 需要页面导出整个页面的数据,没有使用请求做后端导出,而是直接在前端页面配合 xlsx-js-style 库 直接导出 excel 表格

import React, { Component } from 'react';
import XLSX from 'xlsx-js-style';

class ExportExcelComponent extends Component {
    constructor(props) {
        super(props);
        this.state = {
            exportData: {
                "line": [
                    {
                        "id": "2011",
                        "lineName": "陕甘断面",
                        "send": "西北",
                        "receive": "",
                        "dcloudId": "04019901050010",
                        "sendPro": "陕西",
                        "receivePro": "甘肃",
                        "lineType": "省间断面",
                        "channelDataBO": {
                            "dcloudId": "04019901050010",
                            "col": "v1815",
                            "createDate": "Aug 10, 2023 12:00:00 AM",
                            "dict": "-",
                            "maxLimit": 720,
                            "num": 482.135
                        },
                        "isReversal": 0
                    },
                    // Other line data entries...
                ],
                "balanceMarginOfRingVOs": [
                    {
                        "id": "0101370000",
                        "area": "山东",
                        "dataMonth": "2023-08",
                        "timeField": "8",
                        "balanceMargin": -800,
                        "color": "red",
                        "supportVolume": 0,
                        "showArrow": 1,
                        "remains": 0
                    },
                    // Other balanceMarginOfRingVOs entries...
                ],
                "chat": [
                    {
                        "name": "张三",
                        "time": "2024-07-08 14:06:22",
                        "text": "人士称此次"
                    },
                    // Other chat entries...
                ]
            },
        };
    }

    // Function to export data to Excel


     exportToExcel = (linesData, mapData, chatData, fileName) => {
        try {
            const workbook = XLSX.utils.book_new();

            // Process line data
            const processedLinesData = linesData
                .filter(line => !line.dcloudId.startsWith('040'))
                .map(line => ({
                    '调控云ID': line.dcloudId,
                    '线路名称': line.lineName,
                    '预计划': line.channelDataBO && line.channelDataBO['num'],
                    '限额': line.channelDataBO && line.channelDataBO['maxLimit'],
                }));

            // Process map data
            const processedMapData = mapData.map(map => ({
                '地区名称': map.area,
                '裕度': map.balanceMargin
            }));

            // Process chat data
            const processedChatData = chatData && chatData.map(chat => ({
                '姓名': chat.name,
                '时间': chat.time,
                '内容': chat.text
            }));

            // Helper function to add data to sheet with specified column widths and borders
            const dataToSheet = (data, sheetName, colWidths) => {
                if (!data || data.length === 0) {
                    console.error('数据为空或未定义');
                    return;
                }

                const worksheet = XLSX.utils.json_to_sheet(data);

                // Add borders to all cells and background color to header cells
                const range = XLSX.utils.decode_range(worksheet['!ref']);
                for (let R = range.s.r; R <= range.e.r; ++R) {
                    for (let C = range.s.c; C <= range.e.c; ++C) {
                        const cell_address = XLSX.utils.encode_cell({ r: R, c: C });
                        if (!worksheet[cell_address]) continue;
                        if (!worksheet[cell_address].s) worksheet[cell_address].s = {};
                        worksheet[cell_address].s.border = {
                            top: { style: 'thin', color: { rgb: "000000" } },
                            bottom: { style: 'thin', color: { rgb: "000000" } },
                            left: { style: 'thin', color: { rgb: "000000" } },
                            right: { style: 'thin', color: { rgb: "000000" } }
                        };
                        // Set background color for header cells
                        if (R === 0) {
                            worksheet[cell_address].s.fill = {
                                fgColor: { rgb: 'CCFFCC' } // Light green background
                            };
                            worksheet[cell_address].s.alignment = {
                                horizontal: 'center',
                                vertical: 'center'
                            };
                        }
                    }
                }
                // Set header row height
                const headerRowHeight = 30; // Height in points (adjust as needed)
                worksheet['!rows'] = [{ hpt: headerRowHeight }];
                worksheet['!cols'] = colWidths.map(width => ({ wch: width }));
                XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
            };

            // Create sheets for lines, map, and chat data
            dataToSheet(processedLinesData, '线路', [30, 20, 20, 20]);
            dataToSheet(processedMapData, '地图', [30, 30]);
            processedChatData && dataToSheet(processedChatData, '聊天记录', [20, 20, 40]);

            // Write workbook to Excel file
            XLSX.writeFile(workbook, fileName);
            console.log('Excel 文件已成功生成');
        } catch (error) {
            console.error('导出 Excel 文件时出错:', error);
        }
    };



    // Handle export button click
    exportExcelData = () => {
        const { line, balanceMarginOfRingVOs, chat } = this.state.exportData;
        this.exportToExcel(line, balanceMarginOfRingVOs, chat, '协同办公平台数据.xlsx');
    };

    render() {
        return (
            <div>
                <button onClick={this.exportExcelData}>导出 Excel</button>
            </div>
        );
    }
}

export default ExportExcelComponent;