使用LuckySheet实现线上Excel与导入导出
罗河
使用LuckySheet实现线上Excel与导入导出
一 Java使用LuckySheet实现线上Excel
首先准备好相关的静态资源内容
再定义一个div，当做线上Excel的容器
然后定义luckysheet的属性option，创建对象： luckysheet.create(options)
<!DOCTYPE html >
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
<link rel="stylesheet" href="/css/plugins.css">
<link rel="stylesheet" href="/css/pluginsCss.css">
<link rel="stylesheet" href="/css/iconfont.css">
<link rel="stylesheet" href="/css/luckysheet.css">
<link rel="stylesheet" href="/bootstrap/css/bootstrap.css">
<script src="/plugins/js/jquery.min.js"></script>
<script src="/bootstrap/js/bootstrap.js"></script>
<script type="text/javascript" src="/js/plugin.js" ></script>
<script type="text/javascript" src="/js/luckysheet.umd.js" ></script>
<script type="text/javascript" src="/js/xlsx.full.min.js"></script>
<script type="text/javascript" src="/js/luckyexcel.umd.js"></script>
</head>
<style>
#luckysheet {
margin:0px;
padding:0px;
position:absolute;
width:100%;
height:95%;
left: 0px;
top: 40px;
}
</style>
<body>
<div id="container">
<input id="importExcel_input" type="file" name="file" placeholder="導入" />
<div id="luckysheet" ></div>
</div>
</body>
<script src="/js/sheet/demoFeature.js"></script>
<script src="/js/sheet/sheetFormula.js"></script>
<script src="/js/sheet/sheetCell.js"></script>
<script src="/js/sheet/sheetConditionFormat.js"></script>
<script src="/js/sheet/sheetTable.js"></script>
<script src="/js/sheet/sheetComment.js"></script>
<script src="/js/sheet/sheetPivotTableData.js"></script>
<script src="/js/sheet/sheetPivotTable.js"></script>
<script src="/js/sheet/sheetSparkline.js"></script>
<script src="/js/sheet/sheetChart.js"></script>
<script src="/js/sheet/sheetPicture.js"></script>
<script src="/js/sheet/sheetDataVerification.js"></script>
<script th:inline="javascript">
//配置项
var options = {
container: 'luckysheet', //luckysheet为容器id
column: 20, //空表格默认的列数量
row: 20, //空表格默认的行数据量
// title:"title001",
lang: 'zh', // 设定表格语言
allowEdit: true,//作用：是否允许前台编辑
// allowUpdate: true,
allowCopy: true, //是否允许拷贝
showtoolbar: true, //是否第二列显示工具栏
showinfobar: true, //是否显示顶部名称栏
showsheetbar: true, //是否显示底部表格名称区域
showstatisticBar: true, //是否显示底部计数栏
pointEdit: false, //是否是编辑器插入表格模式
pointEditUpdate: null, //编辑器表格更新函数
// myFolderUrl: "/",//作用：左上角<返回按钮的链接
// forceCalculation: false,
// loadUrl: "downData",
// updateUrl: "ws://localhost:8080/UserService"
functionButton: '<button class="btn btn-primary" style=" padding:3px 6px; font-size: 16px;width: 100px;height: 27px; margin-right: 50px;" οnclick="downExcelData(this)">導出</button>',
}
$(function () {
luckysheet.create(options)
})
//监听文件上传
var input = document.querySelector('input');
input.addEventListener('change', importExcel);
/**
* 获取excel数据加載到頁面上
* @param event
*/
function importExcel(event) {
var file = event.target.files[0];
var fileName = file.name;
fileName = fileName + "";
//將文件加載到頁面上
LuckyExcel.transformExcelToLucky(file, function(exportJson, luckysheetfile){
if(exportJson.sheets==null || exportJson.sheets.length==0){
alert("Failed to read the content of the excel file, currently does not support xls files!");
return;
}
console.log(exportJson, luckysheetfile);
window.luckysheet.destroy();
window.luckysheet.create({
container: 'luckysheet', //luckysheet is the container id
showinfobar:false,
lang: 'zh', // 设定表格语言
allowEdit: true,//作用：是否允许前台编辑
// allowUpdate: true,
allowCopy: true, //是否允许拷贝
showtoolbar: true, //是否第二列显示工具栏
showinfobar: true, //是否显示顶部名称栏
showsheetbar: true, //是否显示底部表格名称区域
showstatisticBar: true, //是否显示底部计数栏
pointEdit: false, //是否是编辑器插入表格模式
pointEditUpdate: null, //编辑器表格更新函数
data:exportJson.sheets,
title:exportJson.info.name,
userInfo:exportJson.info.name.creator,
functionButton: '<button id="" class="btn btn-primary" style=" padding:3px 6px; font-size: 16px;width: 100px;height: 27px; margin-right: 85px;" οnclick="downExcelData()">導出</button>',
});
});
}
/**
* 將在線Excel下載到本地
*/
function downExcelData() {
var fileName = $("#luckysheet_info_detail_input").val()
fileName = (fileName + "").trim();
$.post("/excel/downfile", {
exceldatas: JSON.stringify(luckysheet.getAllSheets()),
fileName: fileName
}, function (data) {
//console.log("data = " + data)
});
}
</script>
</html>
二 LuckySheet和POI实现文件导入导出
将线上的Excel通过POI报保存为本地文件，首先就是要明确lucksheet的重要属性，然后将各属性的内容通过POI进行填充。
package com.lmc.excel.util;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.awt.*;
import java.io.*;
import java.util.HashMap;
import java.util.Map;
/**
* @ClassName: ExcelUtils
* @author: Leemon
* @Description: TODO
* @date: 2021/6/9 18:06
* @version: 1.0
*/
public class ExcelUtils {
/***
* 基于POI解析 从0开始导出xlsx文件，
* @param title 表格名
* @param newFileDir 保存的文件夹名
* @param newFileName 保存的文件名
* @param excelData luckysheet 表格数据
*/
public static void exportLuckySheetXlsxByPOI(String title, String newFileDir, String newFileName, String excelData) {
//去除luckysheet中 
 的换行
excelData = excelData.replace("
", "\\r\\n");
//获取luckysheet数据 sheet:[{name:sheet1, ...},{}, ...]
JSONArray jsonArray = (JSONArray) JSONObject.parse(excelData);
//创建操作Excel的XSSFWorkbook对象
XSSFWorkbook excel = new XSSFWorkbook();
for (int sheetIndex = 0; sheetIndex < jsonArray.size(); sheetIndex++) {
//获取sheet
JSONObject jsonObject = (JSONObject) jsonArray.get(sheetIndex);
JSONArray celldataObjectList = jsonObject.getJSONArray("celldata");//获取所有单元格（坐标，内容，字体类型，字体大小...）
JSONArray rowObjectList = jsonObject.getJSONArray("visibledatarow");
JSONArray colObjectList = jsonObject.getJSONArray("visibledatacolumn");
JSONArray dataObjectList = jsonObject.getJSONArray("data");//获取所有单元格,与celldata类似（坐标，内容，字体类型，字体大小...）
JSONObject mergeObject = jsonObject.getJSONObject("config").getJSONObject("merge");//合并单元格
JSONObject columnlenObject = jsonObject.getJSONObject("config").getJSONObject("columnlen");//表格列宽
JSONObject rowlenObject = jsonObject.getJSONObject("config").getJSONObject("rowlen");//表格行高
JSONArray borderInfoObjectList = jsonObject.getJSONObject("config").getJSONArray("borderInfo");//边框样式
//参考：https://blog.csdn.net/jdtugfcg/article/details/84100315
XSSFCellStyle cellStyle = excel.createCellStyle();
//创建XSSFSheet对象
XSSFSheet sheet = excel.createSheet(jsonObject.getString("name"));
//我们都知道excel是表格，即由一行一行组成的，那么这一行在java类中就是一个XSSFRow对象，我们通过XSSFSheet对象就可以创建XSSFRow对象
//如：创建表格中的第一行（我们常用来做标题的行) XSSFRow firstRow = sheet.createRow(0); 注意下标从0开始
//根据luckysheet创建行列
//创建行和列
if (rowObjectList != null && rowObjectList.size() > 0) {
for (int i = 0; i < rowObjectList.size(); i++) {
XSSFRow row = sheet.createRow(i);//创建行
try {
row.setHeightInPoints(Float.parseFloat(rowlenObject.get(i) + ""));//行高px值
} catch (Exception e) {
row.setHeightInPoints(20f);//默认行高
}
if (colObjectList != null && colObjectList.size() > 0) {
for (int j = 0; j < colObjectList.size(); j++) {
if (columnlenObject != null && columnlenObject.getInteger(j + "") != null) {
sheet.setColumnWidth(j, columnlenObject.getInteger(j + "") * 42);//列宽px值
}
row.createCell(j);//创建列
}
}
}
}
//设置值,样式
setCellValue(celldataObjectList, borderInfoObjectList, sheet, excel);
// 判断路径是否存在
File dir = new File(newFileDir);
if (!dir.exists()) {
dir.mkdirs();
}
OutputStream out = null;
try {
out = new FileOutputStream(newFileDir + newFileName);
excel.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 合并单元格与填充单元格颜色
* @param jsonObjectValue
* @param sheet
* @param style
*/
private static void setMergeAndColorByObject(JSONObject jsonObjectValue, XSSFSheet sheet, XSSFCellStyle style) {
JSONObject mergeObject = (JSONObject) jsonObjectValue.get("mc");
//合併單元格
if (mergeObject != null) {
int r = (int) (mergeObject.get("r"));
int c = (int) (mergeObject.get("c"));
if ((mergeObject.get("rs") != null && (mergeObject.get("cs") != null))) {
int rs = (int) (mergeObject.get("rs"));
int cs = (int) (mergeObject.get("cs"));
CellRangeAddress region = new CellRangeAddress(r, r + rs - 1, (short) (c), (short) (c + cs - 1));
sheet.addMergedRegion(region);
}
}
//填充顏色
if (jsonObjectValue.getString("bg") != null) {
int bg = Integer.parseInt(jsonObjectValue.getString("bg").replace("#", ""), 16);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置填充方案
style.setFillForegroundColor(new XSSFColor(new Color(bg))); //设置填充颜色
}
}
private static void setBorder(JSONArray borderInfoObjectList, XSSFWorkbook workbook, XSSFSheet sheet) {
//设置边框样式map
Map<Integer, BorderStyle> bordMap = new HashMap<>();
bordMap.put(1, BorderStyle.THIN);
bordMap.put(2, BorderStyle.HAIR);
bordMap.put(3, BorderStyle.DOTTED);
bordMap.put(4, BorderStyle.DASHED);
bordMap.put(5, BorderStyle.DASH_DOT);
bordMap.put(6, BorderStyle.DASH_DOT_DOT);
bordMap.put(7, BorderStyle.DOUBLE);
bordMap.put(8, BorderStyle.MEDIUM);
bordMap.put(9, BorderStyle.MEDIUM_DASHED);
bordMap.put(10, BorderStyle.MEDIUM_DASH_DOT);
bordMap.put(11, BorderStyle.MEDIUM_DASH_DOT_DOT);
bordMap.put(12, BorderStyle.SLANTED_DASH_DOT);
bordMap.put(13, BorderStyle.THICK);
//一定要通过 cell.getCellStyle() 不然的话之前设置的样式会丢失
//设置边框
if (borderInfoObjectList != null && borderInfoObjectList.size() > 0) {
for (int i = 0; i < borderInfoObjectList.size(); i++) {
JSONObject borderInfoObject = (JSONObject) borderInfoObjectList.get(i);
if (borderInfoObject.get("rangeType").equals("cell")) {//单个单元格
JSONObject borderValueObject = borderInfoObject.getJSONObject("value");
JSONObject l = borderValueObject.getJSONObject("l");
JSONObject r = borderValueObject.getJSONObject("r");
JSONObject t = borderValueObject.getJSONObject("t");
JSONObject b = borderValueObject.getJSONObject("b");
int row = borderValueObject.getInteger("row_index");
int col = borderValueObject.getInteger("col_index");
XSSFCell cell = sheet.getRow(row).getCell(col);
if (l != null) {
cell.getCellStyle().setBorderLeft(bordMap.get((int) l.get("style"))); //左边框
int bg = Integer.parseInt(l.getString("color").replace("#", ""), 16);
cell.getCellStyle().setLeftBorderColor(new XSSFColor(new Color(bg)));//左边框颜色
}
if (r != null) {
cell.getCellStyle().setBorderRight(bordMap.get((int) r.get("style"))); //右边框
int bg = Integer.parseInt(r.getString("color").replace("#", ""), 16);
cell.getCellStyle().setRightBorderColor(new XSSFColor(new Color(bg)));//右边框颜色
}
if (t != null) {
cell.getCellStyle().setBorderTop(bordMap.get((int) t.get("style"))); //顶部边框
int bg = Integer.parseInt(t.getString("color").replace("#", ""), 16);
cell.getCellStyle().setTopBorderColor(new XSSFColor(new Color(bg)));//顶部边框颜色
}
if (b != null) {
cell.getCellStyle().setBorderBottom(bordMap.get((int) b.get("style"))); //底部边框
int bg = Integer.parseInt(b.getString("color").replace("#", ""), 16);
cell.getCellStyle().setBottomBorderColor(new XSSFColor(new Color(bg)));//底部边框颜色
}
} else if (borderInfoObject.get("rangeType").equals("range")) {//选区
int bg_ = Integer.parseInt(borderInfoObject.getString("color").replace("#", ""), 16);
int style_ = borderInfoObject.getInteger("style");
JSONObject rangObject = (JSONObject) ((JSONArray) (borderInfoObject.get("range"))).get(0);
JSONArray rowList = rangObject.getJSONArray("row");
JSONArray columnList = rangObject.getJSONArray("column");
for (int row_ = rowList.getInteger(0); row_ < rowList.getInteger(rowList.size() - 1) + 1; row_++) {
for (int col_ = columnList.getInteger(0); col_ < columnList.getInteger(columnList.size() - 1) + 1; col_++) {
XSSFCell cell = sheet.getRow(row_).getCell(col_);
cell.getCellStyle().setBorderLeft(bordMap.get(style_)); //左边框
cell.getCellStyle().setLeftBorderColor(new XSSFColor(new Color(bg_)));//左边框颜色
cell.getCellStyle().setBorderRight(bordMap.get(style_)); //右边框
cell.getCellStyle().setRightBorderColor(new XSSFColor(new Color(bg_)));//右边框颜色
cell.getCellStyle().setBorderTop(bordMap.get(style_)); //顶部边框
cell.getCellStyle().setTopBorderColor(new XSSFColor(new Color(bg_)));//顶部边框颜色
cell.getCellStyle().setBorderBottom(bordMap.get(style_)); //底部边框
cell.getCellStyle().setBottomBorderColor(new XSSFColor(new Color(bg_)));//底部边框颜色 }
}
}
}
}
}
}
/**
* 設置值和樣式
* @param jsonObjectList
* @param borderInfoObjectList
* @param sheet
* @param workbook
*/
private static void setCellValue(JSONArray jsonObjectList, JSONArray borderInfoObjectList, XSSFSheet
sheet, XSSFWorkbook workbook) {
//设置字体大小和颜色
Map<Integer, String> fontMap = new HashMap<>();
fontMap.put(-1, "Arial");
fontMap.put(0, "Times New Roman");
fontMap.put(1, "Arial");
fontMap.put(2, "Tahoma");
fontMap.put(3, "Verdana");
fontMap.put(4, "微软雅黑");
fontMap.put(5, "宋体");
fontMap.put(6, "黑体");
fontMap.put(7, "楷体");
fontMap.put(8, "仿宋");
fontMap.put(9, "新宋体");
fontMap.put(10, "华文新魏");
fontMap.put(11, "华文行楷");
fontMap.put(12, "华文隶书");
//遍歷每一個單元格（先遍歷行，再遍歷列）
for (int index = 0; index < jsonObjectList.size(); index++) {
XSSFCellStyle style = workbook.createCellStyle();//样式
XSSFFont font = workbook.createFont();//字体样式
//獲取單元格
JSONObject object = jsonObjectList.getJSONObject(index);
//str_ = 行坐標+列坐標=內容
String str_ = (int) object.get("r") + "_" + object.get("c") + "=" + ((JSONObject) object.get("v")).get("v") + "\n";
JSONObject jsonObjectValue = ((JSONObject) object.get("v"));//獲取單元格樣式
//單元格內容
String value = "";
if (jsonObjectValue != null && jsonObjectValue.get("v") != null) {
value = jsonObjectValue.getString("v");
}
if (sheet.getRow((int) object.get("r")) != null && sheet.getRow((int) object.get("r")).getCell((int) object.get("c")) != null) {
XSSFCell cell = sheet.getRow((int) object.get("r")).getCell((int) object.get("c"));
//設置公式 注意：luckysheet与Java的公式可能存在不匹配问题，例如js的Int(data)
if (jsonObjectValue != null && jsonObjectValue.get("f") != null) {//如果有公式，设置公式
value = jsonObjectValue.getString("f");
cell.setCellFormula(value.substring(1,value.length()));//不需要=符号,例：INT(12.3)
}
//合并单元格与填充单元格颜色
setMergeAndColorByObject(jsonObjectValue, sheet, style);
//填充值
cell.setCellValue(value);
XSSFRow row = sheet.getRow((int) object.get("r"));
//设置垂直水平对齐方式
int vt = jsonObjectValue.getInteger("vt") == null ? 1 : jsonObjectValue.getInteger("vt");//垂直对齐 0 中间、1 上、2下
int ht = jsonObjectValue.getInteger("ht") == null ? 1 : jsonObjectValue.getInteger("ht");//0 居中、1 左、2右
switch (vt) {
case 0:
style.setVerticalAlignment(VerticalAlignment.CENTER);
break;
case 1:
style.setVerticalAlignment(VerticalAlignment.TOP);
break;
case 2:
style.setVerticalAlignment(VerticalAlignment.BOTTOM);
break;
}
switch (ht) {
case 0:
style.setAlignment(HorizontalAlignment.CENTER);
break;
case 1:
style.setAlignment(HorizontalAlignment.LEFT);
break;
case 2:
style.setAlignment(HorizontalAlignment.RIGHT);
break;
}
//设置合并单元格的样式有问题
String ff = jsonObjectValue.getString("ff");//0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体（Song）、6 黑体（ST Heiti）、7 楷体（ST Kaiti）、 8 仿宋（ST FangSong）、9 新宋体（ST Song）、10 华文新魏、11 华文行楷、12 华文隶书
int fs = jsonObjectValue.getInteger("fs") == null ? 14 : jsonObjectValue.getInteger("fs");//字体大小
int bl = jsonObjectValue.getInteger("bl") == null ? 0 : jsonObjectValue.getInteger("bl");//粗体 0 常规 、 1加粗
int it = jsonObjectValue.getInteger("it") == null ? 0 : jsonObjectValue.getInteger("it");//斜体 0 常规 、 1 斜体
String fc = jsonObjectValue.getString("fc") == null ? "" : jsonObjectValue.getString("fc");//字体颜色
// font.setFontName(fontMap.get(ff));//字体名字
//字體顏色
if (fc.length() > 0) {
font.setColor(new XSSFColor(new Color(Integer.parseInt(fc.replace("#", ""), 16))));
}
font.setFontName(ff);//字体名字
font.setFontHeightInPoints((short) fs);//字体大小
//是否粗體
if (bl == 1) {
font.setBold(true);//粗体显示
}
//是否斜體
font.setItalic(it == 1 ? true : false);//斜体
style.setFont(font);
style.setWrapText(true);//设置自动换行
cell.setCellStyle(style);
} else {
System.out.println("错误的=" + index + ">>>" + str_);
}
}
//设置边框
setBorder(borderInfoObjectList, workbook, sheet);
}
}
再通过以下的ExcelController，就可以实现线上Luckysheet与本地的导入导出（导入与下载）
/**
* 將在線Excel導出下載
* @param exceldatas excel数据
* @param fileName 文件名称
* @return
*/
@PostMapping("/downfile")
@ResponseBody
public String downData(String exceldatas, String fileName) {
String fileDirNew = "D:/data/";//保存文件夹名
String fileNameNew = "test" + "_" + PublicUtils.getNowDataByFormat("yyyyMMdd_HHmmss") + ".xlsx";//保存的文件名
ExcelUtils.exportLuckySheetXlsxByPOI("", fileDirNew,fileName,exceldatas);
return fileDirNew+fileName;
}
相关代码与资源，在我的gitee中模块 tools-excel 中可见 ： gitee仓库地址 https://gitee.com/lmchh/lmc-tools
类似资料：