Luckysheet如何一键导入本地Excel
问题
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功能正在完善中,可以逐步关注.