jq导入导出excel

Z_Xshan

已于 2023-10-25 08:43:54 修改

阅读量1.5k

点赞数

分类专栏： jQuery 文章标签： javascript 前端 html

于 2023-02-24 18:46:52 首次发布

本文链接：https://blog.csdn.net/Z_Gleng/article/details/129206638

版权

jQuery 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

使用js实现excel文件的上传及渲染到前端页面为table:

导入表格：

这里拿到数据处理成自己想要的格式就可以

导出代码

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta http-equiv="X-UA-Compatible" content="ie=edge" />
    <title>Document</title>
    <script type="text/javascript" src="./lib/jquery.js"></script>
    <script type="text/javascript" src="https://cdn.bootcss.com/xlsx/0.12.7/xlsx.core.min.js"></script>
    <style>
        table,
        th,
        td {
            border: 1px solid black;
            border-collapse: collapse;
        }

        th,
        td {
            padding: 5px;
        }
    </style>
</head>

<body>
    <!-- 1.文件选择框 -->
    <input type="file" id="file01">
    <!-- 2.上传文件按钮 -->
    <button id="btnUpload">上传文件</button>    
    <div class="progress" style="width: 500px;margin: 15px 10px;">
        <!--bootstrap进度条-->
        <!-- <div class="progress-bar progress-bar-striped active" style="width: 0%" id="percent">0%</div> -->                    
        <table id="demo01"></table>        
    </div>

    <img src="./img/loading.gif" style="display: none;" alt="" id="loading">
    <!-- 3.img标签，来选择上传文件后的图片 -->
    <img src="" alt="" id="img" width="800">

    <script>
        $('#btnUpload').on('click', function () {
            var files = $('#file01')[0].files[0];//文件对象
            // console.log(files);
            var file_name = files['name'];//文件名称                        
            var index = file_name.lastIndexOf(".");
            if (index != -1) {
                file_format = file_name.substr(index + 1).toUpperCase();//文件格式后缀
                if (file_format != 'XLS' && file_format != 'XLSX') {
                    alert("只能上传.xls和.xlsx类型的文件!");
                } else {
                    //读取文件内容
                    var reader = new FileReader();
                    reader.readAsBinaryString(files);
                    reader.onload = function (e) {
                        var data = e.target.result;
                        var wb = XLSX.read(data, {
                            type: 'binary' // 以二进制流方式读取获得整份excel表格对象
                        });
                        var sheet_names = wb.SheetNames;//获取excel中所有表名称
                        // console.log(sheet_name);
                        var sheet1_name = sheet_names[0];//获取excel中第一张表名称
                        var sheet01_obj = XLSX.utils.sheet_to_json(wb.Sheets[sheet1_name]);//获取excel中第一张表数据，数组类型，每一行数据都是一个对象
                        // console.log(sheet01_obj);
                        // console.log(sheet01_obj.length);
                        var table1 = "";
                        var table2 = "";                        

                        var keyArr = [];
                        table1 += "<tr>";
                        for (var key01 in sheet01_obj[0]) {
                            keyArr.push(key01);
                            table1 += '<th nowrap>' + key01 + '</th>';//表头                            
                            // console.log(table1);

                        };
                        table2 += "</tr>";
                        // console.log(table1);

                        for (var i = 0; i < sheet01_obj.length; i++) {
                            table2 += "<tr>";
                            for (var j in sheet01_obj[i]) {
                                // console.log('value:'+j);
                                table2 += '<td nowrap>' + sheet01_obj[i][j] + '</td>';//表数据

                            };
                            table2 += "</tr>";

                        }
                        console.log(table1 + table2);
                        document.getElementById("demo01").innerHTML = table1 + table2;

                    }

                }

            }


        });





    </script>
</body>

</html>

XLSX的源码地址SheetJS Table Export

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

<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>Document</title>
  <script src="https://cdn.bootcdn.net/ajax/libs/xlsx/0.18.5/xlsx.core.min.js"></script>
  <script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>

  <style>
    table,
    table tr th,
    table tr td {
      border: 1px solid #333;
    }
    table {
      width: 400px;
      margin-top: 10px;
      text-align: center;
      border-collapse: collapse;
      padding: 2px;
    }
  </style>

</head>

<body>
  <div>
    <button onclick="btn_export()">Excel导出</button>
    <!-- 表格 -->
    <table id="table1">
      <tr class="info" style=" text-align: center;">
        <th>列1</th>
        <th>列2</th>
        <th>列3</th>
        <th>列4</th>
        <th>列5</th>
        <th>列6</th>
      </tr>
      <tr>
        <td>111</td>
        <td>222</td>
        <td>333</td>
        <td>444</td>
        <td>555</td>
        <td>666</td>
      </tr>
    </table>
  </div>
  </div>

  <script>

    function btn_export() {
      var table1 = document.querySelector("#table1");
      var sheet = XLSX.utils.table_to_sheet(table1);//将一个table对象转换成一个sheet对象
      openDownloadDialog(sheet2blob(sheet), '导出.xlsx');
    }

    // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
    function sheet2blob(sheet, sheetName) {
      sheetName = sheetName || 'sheet1';
      var workbook = {
        SheetNames: [sheetName],
        Sheets: {}
      };
      workbook.Sheets[sheetName] = sheet; // 生成excel的配置项

      var wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary'
      };
      var wbout = XLSX.write(workbook, wopts);
      var blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
      }); // 字符串转ArrayBuffer
      function 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;
      }
      return blob;
    }

    function openDownloadDialog(url, saveName) {
      if (typeof url == 'object' && url instanceof Blob) {
        url = URL.createObjectURL(url); // 创建blob地址
      }
      var aLink = document.createElement('a');
      aLink.href = url;
      aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
      var event;
      if (window.MouseEvent) event = new MouseEvent('click');
      else {
        event = document.createEvent('MouseEvents');
        event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
      }
      aLink.dispatchEvent(event);
    }
  </script>
</body>

</html>