JS实现EXCEL转SQL

最新推荐文章于 2024-01-05 16:56:37 发布

取名字真的很难

最新推荐文章于 2024-01-05 16:56:37 发布

阅读量376

点赞数

分类专栏： JS

本文链接：https://blog.csdn.net/qq_20662097/article/details/107534678

版权

JS 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

JS实现EXCEL转SQL

可以直接镶嵌在WordPress，不多BB直接上代码

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.0/dist/css/bootstrap.min.css"
        integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">

    <title>excel转sql</title>
</head>



<body>
    <script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.js"></script>
    <script src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.core.min.js"></script>
    <script src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.full.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.slim.min.js"
        integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous">
    </script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"
        integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous">
    </script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.0/dist/js/bootstrap.min.js"
        integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704+h835Lr+6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous">
    </script>

    <div class="form-group">
        <label>表名</label>
        <input id="tableName" class="form-control" type="text" placeholder="必须输入表名">
    </div>

    <div class="form-group">
        <input type="file" class="form-control-file" id="excel-file">

    </div>

    <div class="form-group">
        <button type="button" class="btn btn-primary" onclick="convert()">转换</button>
        <textarea class="form-control" id="area" rows="3"></textarea>
    </div>

    <script language="javascript" type="text/javascript">
        function convert() {
            var files = $('#excel-file')[0].files;
            console.log(files);
            var fileReader = new FileReader();
            fileReader.onload = function (ev) {
                try {
                    var data = ev.target.result
                    var workbook = XLSX.read(data, {
                        type: 'binary'
                    }) // 以二进制流方式读取得到整份excel表格对象
                    var persons = []; // 存储获取到的数据
                } catch (e) {
                    console.log('文件类型不正确');
                    return;
                }
                // 表格的表格范围，可用于判断表头是否数量是否正确
                var fromTo = '';
                // 遍历每张表读取
                for (var sheet in workbook.Sheets) {
                    if (workbook.Sheets.hasOwnProperty(sheet)) {
                        fromTo = workbook.Sheets[sheet]['!ref'];
                        console.log(fromTo);
                        persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
                        // break; // 如果只取第一张表，就取消注释这行
                    }
                }
                //在控制台打印出来表格中的数据
                console.log(persons);
                //$("#area").val(JSON.stringify(persons));

                //json转sql
                toSql(persons);
            };
            // 以二进制方式打开文件
            fileReader.readAsBinaryString(files[0]);
        }

        /**
         * json转sql
         */
        function toSql(persons) {
            var tableName = $("#tableName")[0].value;

            var sqlstr = '';
            persons.forEach(element => {
                var columns = '(';
                var values = '(';
                for (const key in element) {
                    var value = element[key];
                    columns = columns + key + ',';

                    values = values + value + ',';
                }

                columns = columns.substr(0, columns.length - 1);
                columns = columns + ')';
                values = values.substr(0, values.length - 1);
                values = values + ')';
                sqlstr = sqlstr + 'INSERT INTO ' + tableName + ' ' + columns + ' VALUES ' + values + ';\n\r';
            });


            $("#area").val(sqlstr);
        }
    </script>
</body>

</html>

取名字真的很难

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JS实现EXCEL转SQL

JS实现EXCEL转SQL可以直接镶嵌在WordPress，不多BB直接上代码<html xmlns="http://www.w3.org/1999/xhtml"><head>  <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrin
复制链接

扫一扫