Luckysheet如何一键导入本地Excel

7月 28, 2020

分类:插件

问题

Luckysheet是一款功能很丰富的在线电子表格插件,但是初始版本暂时不支持excel导入导出.笔者结合身边朋友的经验,给出案例供参考.

解决方案

通过sheetJs将导入的excel文件转换成Luckysheet能够支持的数据格式即可

操作步骤

参照官网教程,先打包源代码,得到Luckysheet核心文件,并在在项目主页引入Luckysheet依赖的js，css,和sheetJs插件xlsx.full.min.js

<link rel="stylesheet" href="./luckysheet/plugins/css/pluginsCss.min.css">

<link rel="stylesheet" href="./luckysheet/plugins/plugins.min.css">

<link rel="stylesheet" href="./luckysheet/css/main.min.css">

<script src="./luckysheet/plugins/js/plugin.min.js"></script>

<script src="./luckysheet/main.min.js"></script>

<script src="https://cdn.bootcdn.net/ajax/libs/xlsx/0.16.2/xlsx.full.min.js"></script>

页面准备,渲染一个空白的Luckysheet,input file 监听Excel文件上传，通过sheetJs将导入的数据转换成Luckysheet能够识别的格式

// 渲染一个空白的Luckysheet
$(function () {
    luckysheet.create({
        container: 'luckysheet'
    })
})

// 监听文件上传
var input = document.querySelector('input');
input.addEventListener('change', importExcel);

通过sheetJs将导入的excel数据转换成html table格式数据,使用从luckysheet中抽出来的数据转换方法, 转换为Luckysheet可识别的格式

// 获取excel数据
function importExcel(event) {

    var file = event.target.files[0];
    /* 实例FileReader对象 */
    //other code...
}

// 解析excel数据
function readFile(txtdata) {
    $("#luckysheet-copy-content").html(txtdata);

   //other code...
}

代码参考

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>luckysheet import excel</title>

    <link rel="stylesheet" href="./luckysheet/plugins/css/pluginsCss.min.css">

    <link rel="stylesheet" href="./luckysheet/plugins/plugins.min.css">

    <link rel="stylesheet" href="./luckysheet/css/main.min.css">

    <script src="./luckysheet/plugins/js/plugin.min.js"></script>

    <script src="./luckysheet/main.min.js"></script>

    <script src="https://cdn.bootcdn.net/ajax/libs/xlsx/0.16.2/xlsx.full.min.js"></script>

    <style>
        #container {
            position: relative;
        }
    </style>
</head>

<body>
    <div id="container">
        <input type="file" />
        <div id="luckysheet"
            style="margin:0px;padding:0px;position:absolute;width:100%;height:100%;left: 0px;top: 30px;"></div>
    </div>

    <script>
        document.querySelector('#container').style.height = window.innerHeight + 'px';

        // 渲染一个空白的Luckysheet
        $(function () {

            luckysheet.create({
                container: 'luckysheet'
            })
        })

        // 监听文件上传
        var input = document.querySelector('input');
        input.addEventListener('change', importExcel);

        // 获取excel数据
        function importExcel(event) {

            var file = event.target.files[0];
            /* 实例FileReader对象 */
            var reader = new FileReader();
            reader.onload = function (e) {
                /* Parse data */
                var bstr = e.target.result;
                var wb = XLSX.read(bstr, { type: 'binary' });
                /* 获取第一个工作表*/
                var wsname = wb.SheetNames[0];
                var ws = wb.Sheets[wsname];
                /* 将工作表对象转换为JSON对象数组*/
                // var dataArr = XLSX.utils.sheet_to_json(ws, { header: 1 });
                /* 生成HTML输出 */
                var data = XLSX.utils.sheet_to_html(ws);
                /* 调用数据转换方法将数据格式转换成Luckysheet格式*/
                readFile(data);
            };
            /*将文件内容读取为原始二进制字符串*/
            reader.readAsBinaryString(file);
        }

        // 解析excel数据
        function readFile(txtdata) {
            $("#luckysheet-copy-content").html(txtdata);

            var data = new Array($("#luckysheet-copy-content").find("table tr").length);
            var colLen = 0;
            $("#luckysheet-copy-content").find("table tr").eq(0).find("td").each(function () {
                var colspan = parseInt($(this).attr("colspan"));
                if (isNaN(colspan)) {
                    colspan = 1;
                }
                colLen += colspan;
            });

            for (var i = 0; i < data.length; i++) {
                data[i] = new Array(colLen);
            }

            var r = 0;
            var borderInfo = {};
            var luckysheet_select_save = luckysheet.getluckysheet_select_save();

            $("#luckysheet-copy-content").find("table tr").each(function () {
                var $tr = $(this);
                var c = 0;
                $tr.find("td").each(function () {
                    var $td = $(this);
                    var cell = {};
                    var txt = $td.text();

                    if ($.trim(txt).length == 0) {
                        cell.v = null;
                        cell.m = "";
                    }
                    else {
                        var mask = luckysheet.mask.genarate($td.text());
                        cell.v = mask[2];
                        cell.ct = mask[1];
                        cell.m = mask[0];
                    }

                    var bg = $td.css("background-color");
                    if (bg == "rgba(0, 0, 0, 0)") {
                        bg = "rgba(255,255,255)";
                    }

                    cell.bg = bg;

                    var bl = $td.css("font-weight");
                    if (bl == 400 || bl == "normal") {
                        cell.bl = 0;
                    }
                    else {
                        cell.bl = 1;
                    }

                    var it = $td.css("font-style");
                    if (it == "normal") {
                        cell.it = 0;
                    }
                    else {
                        cell.it = 1;
                    }

                    var ff = $td.css("font-family");
                    var ffs = ff.split(",");
                    for (var i = 0; i < ffs.length; i++) {
                        var fa = $.trim(ffs[i].toLowerCase());
                        fa = luckysheet.menuButton.fontjson[fa];
                        if (fa == null) {
                            cell.ff = 0;
                        }
                        else {
                            cell.ff = fa;
                            break;
                        }
                    }

                    var fs = Math.floor(parseInt($td.css("font-size")) * 72 / luckysheet.dpi_y) + 1;
                    cell.fs = fs;

                    var fc = $td.css("color");

                    cell.fc = fc;

                    var ht = $td.css("text-align");
                    if (ht == "center") {
                        cell.ht = 0;
                    }
                    else if (ht == "right") {
                        cell.ht = 2;
                    }
                    else {
                        cell.ht = 1;
                    }

                    var vt = $td.css("vertical-align");
                    if (vt == "middle") {
                        cell.vt = 0;
                    }
                    else if (vt == "top" || vt == "text-top") {
                        cell.vt = 1;
                    }
                    else {
                        cell.vt = 2;
                    }

                    while (c < colLen && data[r] != null) {
                        c++;
                    }

                    if (c == colLen) {
                        return true;
                    }

                    if (data[r] == null) {
                        data[r] = cell;
                        var rowspan = parseInt($td.attr("rowspan"));
                        var colspan = parseInt($td.attr("colspan"));

                        if (isNaN(rowspan)) {
                            rowspan = 1;
                        }

                        if (isNaN(colspan)) {
                            colspan = 1;
                        }


                        var r_ab = luckysheet_select_save[0]["row"][0] + r;
                        var c_ab = luckysheet_select_save[0]["column"][0] + c;
                        for (var rp = 0; rp < rowspan; rp++) {
                            for (var cp = 0; cp < colspan; cp++) {
                                if (rp == 0) {
                                    var bt = $td.css("border-top");
                                    if (bt != null && bt.length > 0 && bt.substr(0, 3).toLowerCase() != "0px") {
                                        var width = $td.css("border-top-width");
                                        var type = $td.css("border-top-style");
                                        var color = $td.css("border-top-color");
                                        var borderconfig = luckysheet.menuButton.getQKBorder(width, type, color);

                                        if (borderInfo[(r + rp) + "_" + (c + cp)] == null) {
                                            borderInfo[(r + rp) + "_" + (c + cp)] = {};
                                        }

                                        borderInfo[(r + rp) + "_" + (c + cp)].t = { "style": borderconfig[0], "color": borderconfig[1] };
                                    }
                                }

                                if (rp == rowspan - 1) {
                                    var bb = $td.css("border-bottom");
                                    if (bb != null && bb.length > 0 && bb.substr(0, 3).toLowerCase() != "0px") {
                                        var width = $td.css("border-bottom-width");
                                        var type = $td.css("border-bottom-style");
                                        var color = $td.css("border-bottom-color");
                                        var borderconfig = luckysheet.menuButton.getQKBorder(width, type, color);

                                        if (borderInfo[(r + rp) + "_" + (c + cp)] == null) {
                                            borderInfo[(r + rp) + "_" + (c + cp)] = {};
                                        }

                                        borderInfo[(r + rp) + "_" + (c + cp)].b = { "style": borderconfig[0], "color": borderconfig[1] };
                                    }
                                }

                                if (cp == 0) {
                                    var bl = $td.css("border-left");
                                    if (bl != null && bl.length > 0 && bl.substr(0, 3).toLowerCase() != "0px") {
                                        var width = $td.css("border-left-width");
                                        var type = $td.css("border-left-style");
                                        var color = $td.css("border-left-color");
                                        var borderconfig = luckysheet.menuButton.getQKBorder(width, type, color);

                                        if (borderInfo[(r + rp) + "_" + (c + cp)] == null) {
                                            borderInfo[(r + rp) + "_" + (c + cp)] = {};
                                        }

                                        borderInfo[(r + rp) + "_" + (c + cp)].l = { "style": borderconfig[0], "color": borderconfig[1] };
                                    }
                                }

                                if (cp == colspan - 1) {
                                    var br = $td.css("border-right");
                                    if (br != null && br.length > 0 && br.substr(0, 3).toLowerCase() != "0px") {
                                        var width = $td.css("border-right-width");
                                        var type = $td.css("border-right-style");
                                        var color = $td.css("border-right-color");
                                        var borderconfig = luckysheet.menuButton.getQKBorder(width, type, color);

                                        if (borderInfo[(r + rp) + "_" + (c + cp)] == null) {
                                            borderInfo[(r + rp) + "_" + (c + cp)] = {};
                                        }

                                        borderInfo[(r + rp) + "_" + (c + cp)].r = { "style": borderconfig[0], "color": borderconfig[1] };
                                    }
                                }

                                if (rp == 0 && cp == 0) {
                                    continue;
                                }
                                data[r + rp] = { "mc": { "r": r_ab, "c": c_ab } };
                            }
                        }

                        if (rowspan > 1 || colspan > 1) {
                            var first = { "rs": rowspan, "cs": colspan, "r": r_ab, "c": c_ab };
                            data[r].mc = first;
                        }
                    }

                    c++;

                    if (c == colLen) {
                        return true;
                    }
                });

                r++;
            });

            var index = luckysheet.sheetmanage.getSheetIndex(luckysheet.currentSheetIndex);
            var file = luckysheet.getluckysheetfile()[index];
            var luckysheet_selection_range = file["luckysheet_selection_range"];

            luckysheet_selection_range = [];
            luckysheet.selection.pasteHandler(data, borderInfo);
            $("#luckysheet-copy-content").empty();
        }

    </script>
</body>

</html>

探讨

仅前端文件导入是不带单元格样式的,但是从excel复制单元格到Luckysheet是带单元格样式的.可以从在线demo 尝试下.

目前Luckysheet功能正在完善中,可以逐步关注.

Viewing all items for tag js表格插件

Luckysheet如何一键导入本地Excel

问题

解决方案

操作步骤

代码参考

探讨