官方文档：https://mengshukeji.gitee.io/luckysheetdocs/
其他方式集合：https://baijiahao.baidu.com/s?id=1630963094390097886&wfr=spider&for=pc
1 基本介绍
Luckysheet ，一款纯前端类似excel的在线表格，功能强大、配置简单、完全开源。
2 引入依赖
引入依赖，有2种方式
2.1 CDN
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css' />
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>
2.2 本地引入
<link rel='stylesheet' href='./plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='./plugins/plugins.css' />
<link rel='stylesheet' href='./css/luckysheet.css' />
<link rel='stylesheet' href='./assets/iconfont/iconfont.css' />
<script src="./plugins/js/plugin.js"></script>
<script src="./luckysheet.umd.js"></script>
3 初始化表格
指定一个表格容器 “id=luckysheet”
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css' />
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>
</head>
<body>
<div id="luckysheet" style="margin:0px;padding:0px;position:absolute;width:100%;height:100%;left: 0px;top: 0px;"></div>
</body>
<script>
$(function () {
//配置项
var options = {
container: 'luckysheet' ,//luckysheet为容器id
title: 'Luckysheet Demo', // 设定表格名称
lang: 'zh' // 设定表格语言
}
// 初始化表格
luckysheet.create(options)
})
</script>
</html>
预览效果：
image.png
4 添加表格数据
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css' />
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>
</head>
<body>
<div id="luckysheet" style="margin:0px;padding:0px;position:absolute;width:100%;height:100%;left: 0px;top: 0px;"></div>
</body>
<script>
$(function () {
//配置项
var options = {
container: 'luckysheet' ,//luckysheet为容器id
title: 'Luckysheet Demo', // 设定表格名称
lang: 'zh' // 设定表格语言
}
//添加数据
options.data=[
{
"name": "表格1",
"config": {},
"index": "1",//Index索引
"status": "1", //激活状态
"order": "0", //Sheet摆放顺序
"zoomRatio": 1,
"showGridLines": "1",
"defaultColWidth": 80,
"defaultRowHeight": 20,
"celldata": [
{
"r": 0,//单元格的行号
"c": 0,//单元格的列号
//v单元格的值
"v": {
"v": "哈哈哈哈哈哈规划",
"ct": { "fa": "General", "t": "g" },
"bg": null,
"bl": 0,
"it": 0,
"ff": 1,
"fs": "11",
"ht": 1,
"vt": 1,
}
},
{
"r": 0,
"c": 1,
"v": {
"v": "反对犯得上发",
"ct": { "fa": "General", "t": "g" },
"bg": null,
"bl": 0,
"it": 0,
"ff": 1,
"fs": "11",
"ht": 1,
"vt": 1,
}
}
],
"calcChain": []
},
{
"name": "表格2",
"config": {},
"index": "1",
"status": "1",
"order": "0",
"zoomRatio": 1,
"showGridLines": "1",
"defaultColWidth": 80,
"defaultRowHeight": 20,
"celldata": [
{
"r": 0,
"c": 0,
"v": {
"v": "fffffff",
"ct": { "fa": "General", "t": "g" },
"bg": null,
"bl": 0,
"it": 0,
"ff": 1,
"fs": "11",
"ht": 1,
"vt": 1,
}
},
{
"r": 0,
"c": 1,
"v": {
"v": "ggggggg",
"ct": { "fa": "General", "t": "g" },
"bg": null,
"bl": 0,
"it": 0,
"ff": 1,
"fs": "11",
"ht": 1,
"vt": 1,
}
}
],
"calcChain": []
}
]
luckysheet.create(options)
})
</script>
</html>
效果：
image.png
4 从后端获取动态数据
上面的数据都是前端的死数据，如果想要从后端读取Excel数据，配置loadUrl的地址，Luckysheet会通过ajax请求表格数据，默认载入status为1的sheet数据中的所有data，其余的sheet载入除data字段外的所有字段
4.1 使用loadUrl加载服务端数据
<script type="module">
$(function () {
luckysheet.create({
container: 'luckysheet',
lang: 'zh',
allowEdit: true,
forceCalculation: false,
loadUrl: 'http://localhost:8000/load/test.json',
});
});
</script>
关于返回的数据格式，请看官方详细文档：
https://blog.csdn.net/DCDC2020/article/details/108486525
4.2 非loadUrl获取动态数据
4.2.1 前端ajax获取json数据，赋值 options.data
<body>
<div id="luckysheet" style="margin:0px;padding:0px;position:absolute;width:100%;height:100%;left: 0px;top: 0px;"></div>
</body>
<script>
$(document).ready(function () {
//获取url中的参数
var param= window.location.search.substring(1);
let id=param.substring(3)
$.ajax({
type: "post",
url: "/datashower/ut/previewExcel",
dataType: "json",
timeout: 10000,
data: {id:id},
success: function (res) {
var options = {
container: 'luckysheet' ,
title: '预览',
lang: 'zh'
}
//返回数据赋值
options.data=res.data.returnList
luckysheet.create(options)
},
error:function(request){
alert('失败，请确保网络正常')
},
})
});
</script>
4.2.2 后端读取Excel 封装返回json
public JsonResponse previewExcel(String url){
File file = new File(url);
if (null == file) {
LOGGER.info("读取excel文件失败，文件为空");
return null;
}
Workbook wb = createWorkbook(file);
if (null == wb) {
return null;
}
//返回封装json
List<ExcelJson> returnList=new ArrayList<>();
try {
int sheetNum = wb.getNumberOfSheets();
//循环遍历sheet
for (int currentSheet = 0; currentSheet < sheetNum; currentSheet++) {
ExcelJson excelJson=new ExcelJson();
List<CellData> cellDataList=new ArrayList<>();
Sheet sheet = wb.getSheetAt(currentSheet);
String sheetName = sheet.getSheetName();
excelJson.setName(sheetName);
excelJson.setIndex(currentSheet+1);
excelJson.setOrder(currentSheet);
//获得总列数
int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();
// 得到当前工作表的行数
int rowNum = sheet.getLastRowNum();
for (int j = 0; j <= rowNum; j++) {
Row row = sheet.getRow(j);
if (row != null) {
//循环列
for (int i = 0; i < coloumNum; i++) {
CellData cellData=new CellData();
cellData.setR(j);//行
cellData.setC(i);//列
cellData.setV(getCellValue(row.getCell(i)));//值
cellDataList.add(cellData);
}
}
}
excelJson.setCelldata(cellDataList);
returnList.add(excelJson);
}
} catch (RuntimeException e) {
LOGGER.error("预览指标数据出错！", e);
//抛出异常以触发数据回滚
throw e;
} finally {
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
JsonResponse response = new JsonResponse(ReturnCodeEnum.SUCCESS);
JSONObject jsonObject = new JSONObject();
jsonObject.put("returnList", returnList);
response.setData(jsonObject);
return response;
}
