文件导入（读取）

// 组件
<Upload fileList={this.state.fileList} beforeUpload={this.fileBeforeUpload} accept=".xlsx">导入文件</Upload>

// 上传组件中添加 beforeUpload 使用xlsx读取上传文件数据，写入数据库
fileBeforeUpload = file => {
    // xlsx 读取文件内容
    const reader = new FileReader();
    reader.onload = e => {
        try {
            const data = new Uint8Array(e.target.result);
            const workbook = XLSX.read(data, { type: 'array' });
            const firstSheetName = workbook.SheetNames[0];
            const worksheet = workbook.Sheets[firstSheetName];
            const rows = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
            let newData = []
            for (let i = 1; i < rows.length; i++) {
                newData.push({
                    id: this.state.tabData.length + 1,
                    equipmentName: rows[i][0],
                    manufacturer: rows[i][1],
                    model: rows[i][2],
                    mn: rows[i][3],
                    status: rows[i][4],
                    productionDate: rows[i][5],
                    useYear: rows[i][6],
                })
            }
            this.setState({ tabData: [...this.state.tabData, ...newData], fileList: [] }, () => {
                scriptUtil.showMessage("数据写入成功", 'success');
            });
        } catch (error) {
            scriptUtil.showMessage("数据写入失败，请检查文件格式或内容", 'error');
        }
   };
   reader.onerror = () => {
       console.error("文件读取失败");
       scriptUtil.showMessage("文件读取失败，请检查文件是否正确", 'error');
   };
   reader.readAsArrayBuffer(file);
   return false;
}

文件导出

一、二维数组格式数据导出：aoa_to_sheet

[['姓名', '年龄', '城市'],['张三', 28, '北京'],['李四', 34, '上海'],['王五', 22, '广州']]

const data = [
  ['姓名', '年龄', '城市'],
  ['张三', 28, '北京'],
  ['李四', 32, '上海'],
  ['王五', 25, '广州']
];

const workbook = XLSX.utils.book_new();
const worksheet = XLSX.utils.aoa_to_sheet(data);
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
XLSX.writeFile(workbook, 'output.xlsx');

二、对象数组格式数据导出：json_to_sheet

[{ 姓名: '张三', 年龄: 28, 城市: '北京' },{ 姓名: '李四', 年龄: 34, 城市: '上海' },{ 姓名: '王五', 年龄: 22, 城市: '广州' }]

const data = [
  { 姓名: '张三', 年龄: 28, 城市: '北京' },
  { 姓名: '李四', 年龄: 32, 城市: '上海' },
  { 姓名: '王五', 年龄: 25, 城市: '广州' }
];

const workbook = XLSX.utils.book_new();
const worksheet = XLSX.utils.json_to_sheet(data);
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
XLSX.writeFile(workbook, 'output.xlsx');

三、table表格直接导出：table_to_sheet

页面展示什么样导出就是什么样，注：勾选列、操作列也会被导出

可以在写入sheet前调用deleteRow(deleteRowIndex);deleteCell(deleteColumnIndex)删除不需要的行和列

// html原生table直接通过id导出
<table id="myTable">
    <thead>
        <tr>
            <th>姓名</th>
            <th>年龄</th>
            <th>城市</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>张三</td>
            <td>28</td>
            <td>北京</td>
        </tr>
        <tr>
            <td>李四</td>
            <td>32</td>
            <td>上海</td>
        </tr>
        <tr>
            <td>王五</td>
            <td>25</td>
            <td>广州</td>
        </tr>
    </tbody>
</table>

// 导出方法
handleExport() {
    const table = document.getElementById('myTable');
    const worksheet = XLSX.utils.table_to_sheet(table);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
    XLSX.writeFile(workbook, 'output.xlsx');
});


// 封装后的表格（antd），通过class获取到表头与表格，创建一个新的table，将表头与表格合并后得到一个table再导出
/**
* 导出表格到excel
    * @param {string} tableClass 表格的class名称
    * @param {string} fileName 导出的文件名
    * @param {number[]} deleteRowIndices 要删除的行索引，从0开始，如果为[]则不删除任何行，删除单行：[0]，删除多行：[0, 1, 2]
    * @param {number[]} deleteColumnIndices 要删除的列索引，从0开始，如果为[]则不删除任何列，删除单列：[0]，删除多列：[0, 1, 2]
* */
exportTableToExcel = (tableClass, fileName, deleteRowIndices = [], deleteColumnIndices = []) => {
    const table = document.getElementsByClassName(tableClass);
    const headerHtml = table[0].outerHTML;
    const bodyHtml = table[1].outerHTML;
    const fullTable = document.createElement('table');
    const thead = document.createElement('thead');
    thead.innerHTML = headerHtml;
    fullTable.appendChild(thead);
    const tbody = document.createElement('tbody');
    tbody.innerHTML = bodyHtml;
    fullTable.appendChild(tbody);

    // 删除指定行
    deleteRowIndices.sort((a, b) => a - b);
    if (deleteRowIndices.length > 0) {
        // 从后往前删除，以避免因删除行导致索引变化
        for (let i = deleteRowIndices.length - 1; i >= 0; i--) {
            const deleteRowIndex = deleteRowIndices[i];
            if (deleteRowIndex >= 0 && deleteRowIndex < fullTable.rows.length) {
                fullTable.deleteRow(deleteRowIndex);
            }
        }
    }

    // 删除指定列
    deleteColumnIndices.sort((a, b) => a - b);
    if (deleteColumnIndices.length > 0) {
        for (let i = 0; i < fullTable.rows.length; i++) {
            const cells = fullTable.rows[i].cells;
            // 从后往前删除列
            for (let j = deleteColumnIndices.length - 1; j >= 0; j--) {
                const deleteColumnIndex = deleteColumnIndices[j];
                if (deleteColumnIndex >= 0 && deleteColumnIndex < cells.length) {
                    fullTable.rows[i].deleteCell(deleteColumnIndex);
                }
            }
        }
    }

    const workbook = XLSX.utils.table_to_book(fullTable, { sheet: "Sheet1" });
    XLSX.writeFile(workbook, `${fileName}.xlsx`);
};

// 调用实例
// 删除第2行和第3列
exportTableToExcel('myTableClass', 'myFileName', [1], [2]);

// 只删除第2行
exportTableToExcel('myTableClass', 'myFileName', [1], []);

// 只删除第3列
exportTableToExcel('myTableClass', 'myFileName', [], [2]);

// 删除多行多列
exportTableToExcel('ant-table-fixed', 'table_to_sheet', [2, 1], [8, 0]);

四、复杂表格结构导出

分组表头或表格内有单元格合并等（使用table_to_sheet方式导出更方便）

// 导出逻辑
exportToExcel = () => {
    let { rangePickerValue: dates, tabData, selsectValue, selectList } = this.state;
    const diff = dates[1].diff(dates[0], 'days');
    let selectPreject = "项目名称"; // 报表第一行
    let title = "报表数据起止时间：" + dates[0].format('YYYY-MM-DD') + "至" + dates[1].format('YYYY-MM-DD'); // 报表第二行
    let sheetTitle = ["设备名称", "总电量", "", "", "", ""]; // 报表第三行
    let subTitle = ["", "总", "尖", "峰", "平", "谷"]; // 报表第四行
    let keys = []; // 存放日期 ["09-13","09-12",...]
    let colIdx = 6 // 表格列索引值
    let merge = [ // 表格合并规则数组，一个对象代表一个合并规则，s:开始行和列（从0开始），e:结束行和列，r:行索引值，c:列索引值
        { s: { r: 0, c: 0 }, e: { r: 0, c: 15 } },
        { s: { r: 1, c: 0 }, e: { r: 1, c: 15 } },
        { s: { r: 2, c: 0 }, e: { r: 3, c: 0 } },
        { s: { r: 2, c: 1 }, e: { r: 2, c: 5 } }
    ]
    // "_" lodash库
    _.range(diff + 1).forEach((day) => {
        let date = dates[1].clone().subtract(day, 'days').format('MM-DD');
        sheetTitle.push(date, "", "", "", "");
        subTitle.push("总", "尖", "峰", "平", "谷");
        merge.push({ s: { r: 2, c: colIdx }, e: { r: 2, c: colIdx + 4 } }) // 动态添加单元格合并规则
        colIdx += 5
        keys.push(date);
    })
    let sheetData = tabData.reduce((acc, cur) => {
        let sumArr = ["sum", "spike", "peak", "flat", "valley"].map((type) =>
            getValue(cur[type])
         );
         let rowData = _.reduce(keys, (subPre, key) => {
             ["sum", "spike", "peak", "flat", "valley"].forEach((type) => {
                 const vItem = getValue(cur[type + key]);
                 subPre.push(vItem);
             });
             return subPre;
         },[cur.equipmentName, ...sumArr]);
         acc.push(rowData);
         return acc;
        }, [[selectPreject], [title], sheetTitle, subTitle]
    )
     
     let worksheet = XLSX.utils.aoa_to_sheet(sheetData);
     let workbook = XLSX.utils.book_new();
     worksheet["!merges"] = merge; // 添加单元格合并规则
     XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
     XLSX.writeFile(workbook, selectPreject + '_报表导出.xlsx');
}
最后修改：2024 年 09 月 14 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏