js生成execl表格

木心操作

已于 2022-06-01 19:36:00 修改

阅读量4k

点赞数 5

分类专栏： javascript 文章标签： javascript webview vue.js

于 2022-06-01 19:26:32 首次发布

本文链接：https://blog.csdn.net/qq_41579327/article/details/125088468

版权

javascript 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

js方法

let str = '<table><tr><td>姓名</td><td>职别</td></tr><tr><td>张三</td><td>技术员</td></tr></table>'

// 方法一
function exportExecl1(){
    const uri = 'data:application/vnd.ms-excel;base64,';
    window.location.href = uri + window.btoa(unescape(encodeURIComponent(str)));
}

// 方法二
function exportExecl2(){
    const uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
    const link = document.createElement("a");
    link.href = uri;
    // 下载的文件命名 也可以（5月数据表.xlsx）
    link.download =  "5月数据表.csv";
    link.click();
}

完整代码（使用了vue渲染）

需要使用服务启动才能加载到 data.json 数据

可参考 HTML热加载 - 使用live-server

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <title>test</title>
        <script src="https://cdn.staticfile.org/vue/2.2.2/vue.min.js"></script>
        <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
        <style>
            #app{
                padding: 50px 0;
                display: flex;
                flex-direction: column;
            }
            table {
                /* border-collapse: collapse; */
            }
            th {
                padding: 0 20px;
            }
            button{
                align-self: center;
                margin-top: 50px;
                background-color: #43aebd;
                border: none;
                padding: 10px 50px;
                border-radius: 5px;
                box-shadow: 1px 1px 5px #bbb;
                cursor: pointer;
                color: #fff;
                font-weight: bold;
            }
        </style>
    </head>
    <body>
        <div id="app">
            <div style="align-self: center;" ref="tableRef">
                <table border="1" cellspacing="0" cellpadding="0" width="1000">
                    <thead>
                        <th :colspan="colNum" height="50">5月实际产出分析表</th>
                    </thead>
                    <tbody>
                        <tr align="center">
                            <td>工位描述</td>
                            <td>时段</td>
                            <td>时间段</td>
                            <td v-for="item in theda">{{item}}</td>
                            <td>总计</td>
                            <td>最大值</td>
                            <td>最小值</td>
                            <td>平均值</td>
                            <td>结论</td>
                        </tr>
                        <tr v-for="(item, index) in tbody" align="center">
                            <td rowspan="12" v-if="index == 0">{{item.gongWei}}</td>
                            <td rowspan="4" v-if="index == 0 || index == 4 || index == 8">{{item.shid}}</td>
                            <td>{{item.shiJianD}}</td>
                            <td v-for="item1 in item.dataNum">{{item1}}</td>
                            <td v-for="item2 in item.count">{{item2}}</td>
                        </tr>
                    </tbody>
                    <tfoot>
                        <tr v-for="item in tfoot" align="center">
                            <td colspan="3" :bgcolor="item.title == '理论需求' ? '#ff4f4f' : '#d1ffcf'">{{item.title}}</td>
                            <td v-for="(item1,index) in item.num" :bgcolor="item.title == '理论需求' ? (index < (item.num.length - 4) ? '#ff4f4f' : '') : (index < (item.num.length - 4) ? '#d1ffcf' : (item.title == '平均产出' ? (index > (item.num.length - 4) ? '#59b2ff' : '') : ''))">{{item1}}</td>
                        </tr>
                    </tfoot>
                </table>
            </div>

            <button @click="exportExcel">导出</button>
        </div>
    </body>
    <script>
        new Vue({
            el: '#app',
            data: {
                theda: {},
                colNum: 8,
                tbody: {},
                tfoot: {},
            },
            mounted() {
                axios.get('data.json').then((res) => {
                    // console.log(res.data.data)
                    this.theda = res.data.theda
                    this.colNum += this.theda.length
                    this.tbody = res.data.tbody
                    this.tfoot = res.data.tfoot
                })
            },
            methods: {
                exportExcel() {
                    let str = this.$refs.tableRef.innerHTML
                    // console.log(str)
                    const uri = 'data:application/vnd.ms-excel;base64,' + window.btoa(unescape(encodeURIComponent(str)))
                    // 下载
                    const link = document.createElement('a')
                    link.href = uri
                    link.download = '5月报表.xlsx'
                    link.click()
                },
            },
        })
    </script>
</html>

data.json数据

{"theda":["5/2","5/3","5/4","5/5","5/6","5/7","5/8","5/9","5/10","5/11","5/12","5/13","5/14","5/15"],"tbody":[{"gongWei":"工位05","shid":"上午","shiJianD":8,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"上午","shiJianD":9,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"上午","shiJianD":10,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"上午","shiJianD":11,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"中午","shiJianD":14,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"中午","shiJianD":15,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"中午","shiJianD":16,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"中午","shiJianD":17,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"晚上","shiJianD":19,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"晚上","shiJianD":20,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"晚上","shiJianD":21,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]},{"gongWei":"工位05","shid":"晚上","shiJianD":22,"dataNum":[0,29,30,40,34,28,37,19,48,20,6,49,55,49],"count":[133,40,29,33.25]}],"tfoot":[{"title":"当日上班时间","num":[12,12,12,12,8,12,12,12,12,11,8,11,8,8,"/","/","/","/"]},{"title":"总产出","num":[409,333,424,436,222,373,442,609,509,357,349,519,346,308,"/","/","/","/"]},{"title":"平均产出","num":[34,28,37,19,48,20,6,49,55,49,28,35,36,28,"/",50.75,27.75,37.7]},{"title":"理论需求","num":[64,64,64,64,64,64,64,64,64,64,64,64,64,64,"/","/","/","/"]}]}