• HTML save data to CSV or excel


    /*********************************************************************************
     *                     HTML save data to CSV or excel
     * 说明:
     *     将网页内容保存到csv文件或者execl中。
     *
     *                                              2017-10-28 深圳 南山平山村 曾剑锋
     ********************************************************************************/
    
    一、参考文档:
        1. store data from a form using just HTML
            https://stackoverflow.com/questions/14360769/store-data-from-a-form-using-just-html
        2. Write to CSV file locally with HTML5
            https://stackoverflow.com/questions/27013963/write-to-csv-file-locally-with-html5
        3. Web导出excel的几种方法
            http://taote.iteye.com/blog/842496
        4. JS直接导出excel 兼容ie、chrome、firefox
            http://blog.csdn.net/sinat_15114467/article/details/51098522
        5. How do I export html table data as .csv file?
            https://stackoverflow.com/questions/7161113/how-do-i-export-html-table-data-as-csv-file
    
    
    二、原理:
        data协议方式:对于支持data协议的浏览器,可以将html或是csv先用js base64处理,然后前缀data:application/vnd.ms-excel;base64,,即可使浏览器将其中的数据当做excel来处理,浏览器将提示下载或打开excel文件,可惜的是ie不支持。extjs的官网有一个grid的plugin,实现导出xhtml格式的伪excel文件,就是这么做的。
    
    三、Example
        <!DOCTYPE html>
        <html>
          <head>
          <!-- <script src="jquery-3.2.1.min.js"></script> -->
          <script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.2.1.min.js"></script> 
          <script type="text/javascript">
            function exportTableToCSV($table, filename) {
                var $rows = $table.find('tr:has(td),tr:has(th)'),
            
                    // Temporary delimiter characters unlikely to be typed by keyboard
                    // This is to avoid accidentally splitting the actual contents
                    tmpColDelim = String.fromCharCode(11), // vertical tab character
                    tmpRowDelim = String.fromCharCode(0), // null character
            
                    // actual delimiter characters for CSV format
                    colDelim = '","',
                    rowDelim = '"
    "',
            
                    // Grab text from table into CSV formatted string
                    csv = '"' + $rows.map(function (i, row) {
                        var $row = $(row), $cols = $row.find('td,th');
            
                        return $cols.map(function (j, col) {
                            var $col = $(col), text = $col.text();
            
                            return text.replace(/"/g, '""'); // escape double quotes
            
                        }).get().join(tmpColDelim);
            
                    }).get().join(tmpRowDelim)
                        .split(tmpRowDelim).join(rowDelim)
                        .split(tmpColDelim).join(colDelim) + '"',
            
                    // Data URI
                    csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);
            
                    console.log(csv);
            
                    if (window.navigator.msSaveBlob) { // IE 10+
                        //alert('IE' + csv);
                        window.navigator.msSaveOrOpenBlob(new Blob([csv], {type: "text/plain;charset=utf-8;"}), "csvname.csv")
                    } 
                    else {
                        $(this).attr({ 'download': filename, 'href': csvData, 'target': '_blank' }); 
                    }
            }
            
            $(function() {
                // This must be a hyperlink
                $("#xx").on('click', function (event) {
                
                    // exportTableToCSV.apply(this, [$('#projectSpreadsheet'), 'export.csv']);
                    exportTableToCSV.apply(this, [$('#QMSTable'), 'export.csv']);
                
                    // IF CSV, don't do event.preventDefault() or return false
                    // We actually need this to be a typical hyperlink
                });
            });
            
            function fillHidTable(){
                var htqf; //-- hidden field
                var rf; //-- retrieved field
                for ( var i = 1; i < 5; i++ ) {
                    rf = "htqf"+i;
                    document.getElementById(rf).innerHTML = document.getElementById("Q"+i+"CALC").value;
                }
                tableToExcel('hidTable', 'Analysis Results');
            }
            
            var tableToExcel = (function() {
                var uri = 'data:application/vnd.ms-excel;base64,'
                        , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
                        , base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
                        , format = function(s, c) { return s.replace(/{(w+)}/g, function(m, p) { return c[p]; }) }
                return function(table, name) {
                    if (!table.nodeType) table = document.getElementById(table)
                    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
                    window.location.href = uri + base64(format(template, ctx))
                }
            })()
          </script>
          
          <title>HTML Form Data to Excel</title>
          
          <style type="text/css" media="screen">
              .divCenMid{font-family:Arial,sans-serif;font-size:14pt;font-style:normal;font-weight:700;text-align:center;vertical-align:middle;margin:0;}
              .allbdrCenMid{border:.75pt solid windowtext;color:#000;font-family:Arial,sans-serif;font-size:10pt;font-style:normal;font-weight:400;text-align:center;vertical-align:middle;margin:0;}
              .allbdrCenTop{border:.75pt solid windowtext;color:#000;font-family:Arial,sans-serif;font-size:10pt;font-style:normal;font-weight:400;text-align:center;vertical-align:top;margin:0;}
              .allbdrLtMid{border:.75pt solid windowtext;color:#000;font-family:Arial,sans-serif;font-size:10pt;font-style:normal;font-weight:400;text-align:left;vertical-align:middle;margin:0;}
              .allbdrLtTop{border:.75pt solid windowtext;color:#000;font-family:Arial,sans-serif;font-size:10pt;font-style:normal;font-weight:400;text-align:left;vertical-align:top;margin:0;}
          
          </style>
          
          </head>
          
          <body>
          
          <table width= "565px" cellspacing="0" cellpadding="0" style="border-spacing:0;" id="QMSTable">
              <col width="25px"/>
              <col width="120px"/>
              <col width="360px"/>
              <col width="60px"/>
              <tr>
                  <td class="divCenMid" colspan = "4"> QMS Assessment</td>
              </tr>
              <tr>
                  <td class="allbdrCenMid"> No</td>
                  <td class="allbdrCenMid"> Criteria</td>
                  <td class="allbdrLtMid"> Question</td>
                  <td class="allbdrCenMid"> Score</td>
              </tr>
              <tr>
                  <td class="allbdrCenTop"> Q1</td>
                  <td class="allbdrLtTop"> Quality Unit Independency</td>
                  <td class="allbdrLtTop"> Do you have the Quality Unit?</td>
                  <td class="allbdrCenMid">
                      <input id="Q1CALC" type="text" value="" class="nobdrCenMid" style="overflow:hidden; 93% " name="Q1CALC"/>
                  </td>
              </tr>
              <tr>
                  <td class="allbdrCenTop"> Q2</td>
                  <td class="allbdrLtTop"> Apply PICS GMP</td>
                  <td class="allbdrLtTop"> Which GMP regulation do you use?</td>
                  <td class="allbdrCenMid">
                      <input id="Q2CALC" type="text" value="" class="nobdrCenMid" style="overflow:hidden; 93% " name="Q2CALC"/>
                  </td>
              </tr>
              <tr>
                  <td class="allbdrCenTop"> Q3</td>
                  <td class="allbdrLtTop"> Deviation or Non-conformance</td>
                  <td class="allbdrLtTop"> Do you have a deviation or non-conformance procedure?</td>
                  <td class="allbdrCenMid">
                      <input id="Q3CALC" type="text" value="" class="nobdrCenMid" style="overflow:hidden; 93% " name="Q3CALC"/>
                  </td>
              </tr>
              <tr>
                  <td class="allbdrCenTop"> Q4</td>
                  <td class="allbdrLtTop"> Complaint</td>
                  <td class="allbdrLtTop"> Do you have a customer complaint procedure?</td>
                  <td class="allbdrCenMid">
                      <input id="Q4CALC" type="text" value="" class="nobdrCenMid" style="overflow:hidden; 93% " name="Q4CALC"/>
                  </td>
              </tr>
          </table>
          
          <div id="hidTable" style="display: none">
              <table id="testTable">
                  <caption>Supplier Risk Analysis</caption>
                  <colgroup></colgroup>
                  <colgroup></colgroup>
                  <colgroup></colgroup>
                  <thead>
                  <tr>
                      <th>No.</th>
                      <th>Question</th>
                      <th>Score</th>
                  </tr>
                  </thead>
                  <tbody>
                  <tr>
                      <td>Q1</td>
                      <td>Do you have the Quality Unit?</td>
                      <td id="htqf1">-</td>
                  </tr>
                  <tr>
                      <td>Q2</td>
                      <td>Apply PICS GMP?</td>
                      <td id="htqf2">-</td>
                  </tr>
                  <tr>
                      <td>Q3</td>
                      <td>Do you have a deviation or non-conformance procedure?</td>
                      <td id="htqf3">-</td>
                  </tr>
                  <tr>
                      <td>Q4</td>
                      <td>Do you have a customer complaint procedure?</td>
                      <td id="htqf4">-</td>
                  </tr>
                  </tbody>
              </table>
          </div>
          <input type="button" onclick="fillHidTable()" value="Export Data to Excel">
        
          <br />
          <br />
          <div>
            <a href="#" id="xx" style="text-decoration:none;color:#000;background-color:#ddd;border:1px solid #ccc;padding:8px;">Export Table data into Excel</a>
          </div>
          </body>
        </html>
  • 相关阅读:
    这两天自己模仿写的一个Asp.Net的显示分页方法 附加实体转换和存储过程
    JavaScript 删除 ASP.NET 设置的多值 Cookie 的方法
    Http 请求处理流程
    ASP.NET实现二维码(QRCode)的创建和读取
    ASP.NET实现网站的自动升级
    ASP.NET乱码深度剖析
    ASP.net学习总结
    ASP.net MVC基础
    umeditor编辑器复制粘贴图片上传
    fckeditor编辑器复制粘贴图片上传
  • 原文地址:https://www.cnblogs.com/zengjfgit/p/7747439.html
Copyright © 2020-2023  润新知