官方文檔：https://mengshukeji.gitee.io/luckysheetdocs/
其他方式集合：https://baijiahao.baidu.com/s?id=1630963094390097886&wfr=spider&for=pc
1 基本介紹
Luckysheet ，一款純前端類似excel的在線表格，功能強(qiáng)大、配置簡單、完全開源。
2 引入依賴
引入依賴，有2種方式
2.1 CDN
<link rel='stylesheet' />
<link rel='stylesheet' />
<link rel='stylesheet' />
<link rel='stylesheet' />
<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 初始化表格
指定一個(gè)表格容器 “id=luckysheet”
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link rel='stylesheet' />
<link rel='stylesheet' />
<link rel='stylesheet' />
<link rel='stylesheet' />
<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 () {
//配置項(xiàng)
var options = {
container: 'luckysheet' ,//luckysheet為容器id
title: 'Luckysheet Demo', // 設(shè)定表格名稱
lang: 'zh' // 設(shè)定表格語言
}
// 初始化表格
luckysheet.create(options)
})
</script>
</html>
預(yù)覽效果：
image.png
4 添加表格數(shù)據(jù)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link rel='stylesheet' />
<link rel='stylesheet' />
<link rel='stylesheet' />
<link rel='stylesheet' />
<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 () {
//配置項(xiàng)
var options = {
container: 'luckysheet' ,//luckysheet為容器id
title: 'Luckysheet Demo', // 設(shè)定表格名稱
lang: 'zh' // 設(shè)定表格語言
}
//添加數(shù)據(jù)
options.data=[
{
"name": "表格1",
"config": {},
"index": "1",//Index索引
"status": "1", //激活狀態(tài)
"order": "0", //Sheet擺放順序
"zoomRatio": 1,
"showGridLines": "1",
"defaultColWidth": 80,
"defaultRowHeight": 20,
"celldata": [
{
"r": 0,//單元格的行號
"c": 0,//單元格的列號
//v單元格的值
"v": {
"v": "哈哈哈哈哈哈規(guī)劃",
"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": "反對犯得上發(fā)",
"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 從后端獲取動(dòng)態(tài)數(shù)據(jù)
上面的數(shù)據(jù)都是前端的死數(shù)據(jù)，如果想要從后端讀取Excel數(shù)據(jù)，配置loadUrl的地址，Luckysheet會(huì)通過ajax請求表格數(shù)據(jù)，默認(rèn)載入status為1的sheet數(shù)據(jù)中的所有data，其余的sheet載入除data字段外的所有字段
4.1 使用loadUrl加載服務(wù)端數(shù)據(jù)
<script type="module">
$(function () {
luckysheet.create({
container: 'luckysheet',
lang: 'zh',
allowEdit: true,
forceCalculation: false,
loadUrl: 'http://localhost:8000/load/test.json',
});
});
</script>
關(guān)于返回的數(shù)據(jù)格式，請看官方詳細(xì)文檔：
https://blog.csdn.net/DCDC2020/article/details/108486525
4.2 非loadUrl獲取動(dòng)態(tài)數(shù)據(jù)
4.2.1 前端ajax獲取json數(shù)據(jù)，賦值 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中的參數(shù)
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: '預(yù)覽',
lang: 'zh'
}
//返回?cái)?shù)據(jù)賦值
options.data=res.data.returnList
luckysheet.create(options)
},
error:function(request){
alert('失敗，請確保網(wǎng)絡(luò)正常')
},
})
});
</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();
//循環(huán)遍歷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);
//獲得總列數(shù)
int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();
// 得到當(dāng)前工作表的行數(shù)
int rowNum = sheet.getLastRowNum();
for (int j = 0; j <= rowNum; j++) {
Row row = sheet.getRow(j);
if (row != null) {
//循環(huán)列
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("預(yù)覽指標(biāo)數(shù)據(jù)出錯(cuò)！", e);
//拋出異常以觸發(fā)數(shù)據(jù)回滾
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;
}
