前端几行代码实现excel表格解析

crazyXB

已于 2023-12-28 17:25:48 修改

阅读量545

点赞数 6

文章标签： excel javascript 前端 vue.js html

于 2023-12-28 17:23:23 首次发布

本文链接：https://blog.csdn.net/crazyXB/article/details/135268394

版权

需求

利用前端技术和浏览器的打印功能，实现excel文件打印。

优势

可直接本地上传，不用产生数据交互，操作便捷。

利用技术

JavaScript +xlsx

 <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.core.min.js"></script>

实现过程

使用input标签，不能修改浏览文件为上传文件，可以按照我下面的方法去写

 // HTML
<button class="btn" @click="uploadFile">上传excel文件</button>

// JavaScript
const uploadFile = (mark) => {
    let inputEle = document.createElement('input')
    inputEle.type = 'file'
    inputEle.accept = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,             
    application/vnd.ms-excel'
    inputEle.click()
    inputEle.addEventListener('input', (event) => {
        // excel文件处理逻辑
        onExcel( event)
    })
};

处理excel

可以根据自己的需求，处理成自己想要的数据，我这里是处理成两种格式，完整的代码，我放到最后面了！！！

// JavaScript
const onExcel = (event) => {
    let reader = new FileReader();
    let file = event.target.files[0];
    let fileName = file.name
    if (file) {
        reader.onload = function (e) {
            let data = e.target.result;
            let workbook = XLSX.read(data, { type: 'binary' });
            let sheetName = workbook.SheetNames[0];
            let sheet = workbook.Sheets[sheetName];
            dataArray.value = XLSX.utils.sheet_to_json(sheet, { header: 1 });
            objectArray.value = XLSX.utils.sheet_to_json(sheet);
    };
    reader.readAsArrayBuffer(file);
    }
}

表格数据预览

数据预览

完整代码

<!DOCTYPE html>
<html lang="en">

<head>
    <title>上传excel文件</title>
    <style>
        * {
            margin: 0;
            padding: 0;
        }
        .container{
            padding:0 50px;
        }
        .operation {
            padding: 20px;
        }

        .btn {
            min-width: 50px;
            font-size: 20px;
            color: #fff;
            background: #118ee9;
            margin: 0 20px 0 0;
            padding: 8px;
            border: none;
            border-radius: 4px;
            box-sizing: border-box;
        }

        .item {
            width: 350px;
            box-sizing: border-box;
            padding: 4px 14px 4px 14px;
            color: #000;
            font-size: 12px;
            background: #fff;
        }
    </style>

    <script src="https://unpkg.com/vue@3/dist/vue.global.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.core.min.js"></script>

</head>

<body>
    <div class="container" id="app">
        <div class="operation">
            <button class="btn" @click="uploadFile">上传excel文件</button>
        </div>
        <div v-if="objectArray.length ">
            <div v-for="val in objectArray">{{val}}</div>
        </div>
        <br>
        <div v-if="dataArray.length ">
            <div v-for=" val in dataArray">{{val}}</div>
        </div>
    </div>
    <script>
        const { createApp, ref} = Vue
        createApp({
            setup() {
                let objectArray = ref([]);
                let dataArray = ref([]);
                const uploadFile = (mark) => {
                    let inputEle = document.createElement('input')
                    inputEle.type = 'file'
                    inputEle.accept = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel'
                    inputEle.click()
                    inputEle.addEventListener('input', (event) => {
                        onExcel( event)
                    })
                };

                const onExcel = (event) => {
                    let reader = new FileReader();
                    let file = event.target.files[0];
                    let fileName = file.name
                    if (file) {
                        reader.onload = function (e) {
                            let data = e.target.result;
                            let workbook = XLSX.read(data, { type: 'binary' });
                            let sheetName = workbook.SheetNames[0];
                            let sheet = workbook.Sheets[sheetName];
                            dataArray.value = XLSX.utils.sheet_to_json(sheet, { header: 1 });
                            objectArray.value = XLSX.utils.sheet_to_json(sheet);
                        };
                        reader.readAsArrayBuffer(file);
                    }
                }
                return {
                    dataArray,
                    objectArray,
                    uploadFile,
                    onExcel,
                }
            }
        }).mount('#app')
    </script>
</body>
</html>