代码改变世界

THML导出Excel

2020-03-10 15:10  Evan.Pei  阅读(163)  评论(0)    收藏  举报

1.导出

<script src="scripts/jquery-1.7.2.min.js"></script>
<script src="scripts/rusty1s_table2excel.js"></script>//网上找

html

<body>
    <div>
        <button type="button" id="btnExport">导出Excel</button>
    </div>
    <div id="myDiv">
        <table id="tableExcel" width="70%" border="1" cellspacing="0" cellpadding="0">
            <tr>
                <td colspan="5" align="center">html 表格导出道Excel</td>
            </tr>
            <tr>
                <td>列标题1</td>
                <td>列标题2</td>
                <td>类标题3</td>
                <td>列标题4</td>
                <td>列标题5</td>
            </tr>
        </table>
    </div>
</body>

js

<script language="JavaScript" type="text/javascript">
    
    $("#btnExport").click(function () {
        exportExcel("tableExcel", "aaa");
    });
    function addhtml() {
        var html = "";
        for (var i = 0; i <10; i++) {
            html += "<tr><td>AAAAAAA</td><td>BBBBBBBBBB</td><td>CCCCCCCCCCCCC</td><td>DDDDDDDDDDD</td><td>EEEEEEEEEEE</td></tr>";
        }
        $("#tableExcel").append(html);
    }
    addhtml();

    function exportExcel(tableid, filename) {
        var table2excel = new Table2Excel({
            defaultFileName: filename
        });
        table2excel.export(document.getElementById(tableid));
    }

</script>

2.导入

 <script src="scripts/jquery-1.7.2.min.js"></script>
 <script src="scripts/SheetJS_js-xlsx/xlsx.core.min.js.js"></script>
 //https://github.com/SheetJS/sheetjs/tree/master/dist

html

<body>
    <input type="file" id="file" style="display:none;" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
    <a href="javascript:selectFile()">加载本地excel文件</a>
    <div id="result" contenteditable=""></div>
</body>

js

<script type="text/javascript">
    function selectFile() {
        document.getElementById('file').click();
    }
    $(function () {
        //改变事件
        document.getElementById('file').addEventListener('change', function (e) {
            var files = e.target.files;
            if (files.length == 0) return;
            var f = files[0];
            if (!/\.xlsx$/g.test(f.name)) {
                alert('仅支持读取xlsx格式！');
                return;
            }
            readWorkbookFromLocalFile(f, function (workbook) {
                readWorkbook(workbook);
            });
        });
        loadRemoteFile('./sample/test.xlsx');
    });

    function readWorkbook(workbook) {
        var sheetNames = workbook.SheetNames; // 工作表名称集合
        var worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
        var csv = XLSX.utils.sheet_to_csv(worksheet);
        document.getElementById('result').innerHTML = csv2table(csv);
    }

    // 将csv转换成表格
    function csv2table(csv) {
        var html = '<table>';
        var rows = csv.split('\n');
        rows.pop(); // 最后一行没用的
        //得到数据，这里可以转为json
        //处理数据
        rows.forEach(function (row, idx) {
            var columns = row.split(',');
            columns.unshift(idx + 1); // 添加行索引
            if (idx == 0) { // 添加列索引
                html += '<tr>';
                for (var i = 0; i < columns.length; i++) {
                    html += '<th>' + (i == 0 ? '' : String.fromCharCode(65 + i - 1)) + '</th>';
                }
                html += '</tr>';
            }
            html += '<tr>';
            columns.forEach(function (column) {
                html += '<td>' + column + '</td>';
            });
            html += '</tr>';
        });
        html += '</table>';
        return html;
    }

    //读取本地excel文件
    function readWorkbookFromLocalFile(file, callback) {
        var reader = new FileReader();
        reader.onload = function (e) {
            var data = e.target.result;
            var workbook = XLSX.read(data, { type: 'binary' });
            if (callback) callback(workbook);
        };
        reader.readAsBinaryString(file);
    }

    function loadRemoteFile(url) {
        readWorkbookFromRemoteFile(url, function (workbook) {
            readWorkbook(workbook);
        });
    }
</script>