SheetJS/js-xlsx cdn excle数据导出

最新推荐文章于 2024-07-14 22:15:03 发布

Yweir

最新推荐文章于 2024-07-14 22:15:03 发布

阅读量1.1k

点赞数

分类专栏： javascript 文章标签： excel vue

本文链接：https://blog.csdn.net/laow1314/article/details/114994393

版权

javascript 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

引入依赖js

下载 SheetJS
下载FileSaver.js

实现Vue 导出数据核心方法


            export_json_to_excel(th, jsonData, defaultTitle) {

                /* original data */

                var data = jsonData;
                data.unshift(th);
                var ws_name = "SheetJS";
                var wb = {
                    SheetNames: [],
                    Sheets: {},
                    Props: {}
                };
                var ws = this.sheet_from_array_of_arrays(data);


                /* add worksheet to workbook */
                wb.SheetNames.push(ws_name);
                wb.Sheets[ws_name] = ws;

                var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
                var title = defaultTitle || '列表'
                console.log(th);
                saveAs(new Blob([this.s2ab(wbout)], { type: "application/octet-stream" }), title + ".xlsx")
            },

完整示例

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <!-- import CSS -->
    <link rel="stylesheet" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css">
</head>

<body>
    <div id="app">
        <div class="app-container">
            <!-- 导出按钮 -->
            <div class="filter-container">
                <el-button :loading="downloadLoading" class="filter-item" type="primary" icon="el-icon-download"
                    @click="handleDownload">导出</el-button>
            </div>
        </div>
    </div>
</body>
<!-- import Vue before Element -->
<script src="https://unpkg.com/vue/dist/vue.js"></script>
<!-- import JavaScript -->
<script src="https://unpkg.com/element-ui/lib/index.js"></script>

<!-- 导入你下载的js  -->
<script src="./FileSaver.js"></script>
<script src="./xlsx.core.min.js"></script>
<script>
    new Vue({
        el: '#app',
        data: function () {
            return {
                downloadLoading: false,
                tableData: [
                    { 'index': '0', "nickName": "哈哈哈", "name": "小明" },
                    { 'index': '1', "nickName": "嘿嘿", "name": "小红" },
                    { 'index': '2', "nickName": "八百", "name": "小兰" }
                ]
            }
        },
        methods: {
            handleDownload() {
                this.downloadLoading = true

                const tHeader = ['序号', '昵称', '姓名'];
                // 上面设置Excel的表格第一行的标题
                const filterVal = ['index', 'nickName', 'name'];
                // 上面的index、nickName、name是tableData里对象的属性
                const list = this.tableData;  //把data里的tableData存到list
                const data = this.formatJson(filterVal, list);
                this.export_json_to_excel(tHeader, data, '12312321');
                setTimeout(() => {
                    this.downloadLoading = false
                }, 100);
            },


            formatJson(filterVal, jsonData) {
                return jsonData.map(v => filterVal.map(j => v[j]))
            },
            sheet_from_array_of_arrays(data, opts) {
                var ws = {};
                var range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
                for (var R = 0; R != data.length; ++R) {
                    for (var C = 0; C != data[R].length; ++C) {
                        if (range.s.r > R) range.s.r = R;
                        if (range.s.c > C) range.s.c = C;
                        if (range.e.r < R) range.e.r = R;
                        if (range.e.c < C) range.e.c = C;
                        var cell = { v: data[R][C] };
                        if (cell.v == null) continue;
                        var cell_ref = XLSX.utils.encode_cell({ c: C, r: R });

                        if (typeof cell.v === 'number') cell.t = 'n';
                        else if (typeof cell.v === 'boolean') cell.t = 'b';
                        else if (cell.v instanceof Date) {
                            cell.t = 'n';
                            cell.z = XLSX.SSF._table[14];
                            cell.v = datenum(cell.v);
                        }
                        else cell.t = 's';

                        ws[cell_ref] = cell;
                    }
                }
                if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
                return ws;
            },
            export_json_to_excel(th, jsonData, defaultTitle) {

                /* original data */

                var data = jsonData;
                data.unshift(th);
                var ws_name = "SheetJS";
                var wb = {
                    SheetNames: [],
                    Sheets: {},
                    Props: {}
                };
                var ws = this.sheet_from_array_of_arrays(data);
				let autoWidth = false;
				if (autoWidth) {
			        /*设置worksheet每列的最大宽度*/
			        const colWidth = data.map((row) =>
			          row.map((val) => {
			            /*先判断是否为null/undefined*/
			            if (val == null) {
			              return {
			                wch: 10,
			              };
			            } else if (val.toString().charCodeAt(0) > 255) {
			            /*再判断是否为中文*/
			              return {
			                wch: val.toString().length * 2,
			              };
			            } else {
			              return {
			                wch: val.toString().length,
			              };
			            }
			          })
			        );
			        /*以第一行为初始值*/
			        let result = colWidth[0];
			        for (let i = 1; i < colWidth.length; i++) {
			          for (let j = 0; j < colWidth[i].length; j++) {
			            if (result[j]["wch"] < colWidth[i][j]["wch"]) {
			              result[j]["wch"] = colWidth[i][j]["wch"];
			            }
			          }
			        }
			        ws["!cols"] = result;
			      }
                /* add worksheet to workbook */
                wb.SheetNames.push(ws_name);
                wb.Sheets[ws_name] = ws;

                var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
                var title = defaultTitle || '列表'
                console.log(th);
                saveAs(new Blob([this.s2ab(wbout)], { type: "application/octet-stream" }), title + ".xlsx")
            },
            s2ab(s) {
                var buf = new ArrayBuffer(s.length);
                var view = new Uint8Array(buf);
                for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
                return buf;
            },
            

        }
    })
</script>

</html>