SpreadJS 全家桶实践 - 在线Excel报表系统（二）
本帖最后由 dexteryao 于 2020-8-31 14:52 编辑上一期SpreadJS 全家桶实践 - 在线Excel报表系统（一），已经搭建好了整个项目框架并集成了SpreadJS、新编辑器和GCExcel。
本期将实现模板上传加载功能,并通过GCExcel的模板功能实现数据绑定。
1. 自定义Ribbon（工具栏），添加上传、加载、绑定和模板按钮
<blockquote><script src="lib/pako.min.js" type="text/javascript"></script>将编辑器默认配置default_config.json修改为designerConfig.js 并引入
let designerConfig = {
"ribbon":[ ... ]添加designer.js和ribbon.file.js分离功能，方便维护
designer.js中添加自定义config，在初始化编辑器时使用自定义config
var customerRibbon = {
"id": "operate",
"text": "操作",
"buttonGroups": [
]
}
function initStates(designer) {
spreadTemplateJSON = undefined;
designer.setData(AllowBindingData, true)
designer.setData(AllowBackTemplate, false)
}
function initDesigner() {
customerRibbon.buttonGroups.push(ribbonFileConfig) //将ribbon.file config注入
designerConfig.ribbon.push(customerRibbon) //添加自定义ribbon tab
var designer = new GC.Spread.Sheets.Designer.Designer(
document.getElementById('ssDesigner'), /**designer host */
designerConfig, // designerConfigJson /**If you want to change Designer's layout or rewrite it, you can pass in your own Config JSON */ ,
undefined /**If you want to use the spread you already created instead of generating a new spread, pass in */
);
initStates(designer);
return designer
}修改index.js，初始化调用initDesigner 方法，并引入pako，添加前端压缩解压方法
var designer
window.onload = function () {
GC.Spread.Common.CultureManager.culture("zh-cn");
designer = initDesigner();
};
在ribbon.file.js添加 ribbonFileConfig ，设置上传、加载、绑定和模板按钮，并指定icon和命令
var ribbonFileConfig =
{
"label": "文件操作",
"thumbnailClass": "ribbon-thumbnail-spreadsettings",
"commandGroup": {
"children": [
{
"direction": "vertical",
"commands": [
{
iconClass: "ribbon-button-download",
text: "加载",
commandName: "getTemplates",
execute: getTemplates,
},
{
iconClass: "ribbon-button-upload",
text: "上传",
commandName: "uploadFile",
execute: uploadFile,
}
]
},
{
"type": "separator"
},
{
"direction": "vertical",
"commands": [
{
iconClass: "ribbon-button-namemanager",
text: "绑定",
commandName: "bindingData",
execute: bindingData,
enableContext: AllowBindingData
},
{
iconClass: "ribbon-button-namemanager",
text: "模板",
commandName: "backTemplate",
execute: backTemplate,
enableContext: "AllowBackTemplate"
}
]
}
]
}
};添加命令对应方法
getTemplates 通过接口获取服务器上模板文件，并在表单中通过超链接形式展示
点击超链接后调用loadTemplate，通过模板名称加载模板json，导入工作簿
uploadFile 方法触发文件选择器，选择文件通过接口上传，服务端GCExcel 处理后返回json，并加载。可上传.xlsx Excel文件和SpreadJS ssjson
bindingData 将模板提交GCExcel与模拟数据绑定并返回结果json
backTemplate 通过缓存的json，还原回模板状态
async function getTemplates(designer) {
var spread = designer.getWorkbook();
$.post("spread/getTemplates", {}, function (data) {
if (data != "0") {
data = ungzipString(data);
console.log(data)
var files = data.split(";")
var sheet = spread.getActiveSheet();
sheet.reset();
sheet.defaults.colWidth = 180
sheet.setArray(0, 0, )
// var HyperLinkCellType = GC.Spread.Sheets
var h = new GC.Spread.Sheets.CellTypes.HyperLink();
h.onClickAction(function (e) {
var sheet = e.sheet, row = e.row, col = e.col;
var value = sheet.getValue(row, col);
loadTemplate(value)
})
sheet.getRange(0, 0, 1, files.length).cellType(h)
}
})
}
async function loadTemplate(fileName) {
var spread = designer.getWorkbook();
let formData = new FormData();
formData.append("fileName", fileName);
$.ajax({
url: "spread/loadTemplate",
type: "POST",
contentType: false,
processData: false,
data: formData,
success: function (data) {
if (data != "0") {
data = ungzipString(data);
var json = JSON.parse(data);
json.calcOnDemand = true;
spread.fromJSON(json, { doNotRecalculateAfterLoad: true });
initStates(designer)
}
}
})
}
async function uploadFile(designer) {
var spread = designer.getWorkbook();
var ribbon_file_selector = document.getElementById("ribbon_file_selector");
ribbon_file_selector.onchange = function (event) {
console.log(event)
var file = event.target.files;
let formData = new FormData();
formData.append("file", file);
formData.append("fileName", file.name);
$.ajax({
url: "spread/uploadFile",
type: "POST",
contentType: false,
processData: false,
data: formData,
success: function (data) {
if (data != "上传失败！") {
data = ungzipString(data);
var json = JSON.parse(data);
json.calcOnDemand = true;
spread.fromJSON(json, { doNotRecalculateAfterLoad: true });
initStates(designer)
}
else {
alert("上传失败")
}
}
})
}
ribbon_file_selector.value = "";
ribbon_file_selector.click();
}
var spreadTemplateJSON;
var AllowBindingData = "AllowBindingData", AllowBackTemplate = "AllowBackTemplate";
async function bindingData(designer) {
var spread = designer.getWorkbook();
if (!spreadTemplateJSON) {
spreadTemplateJSON = JSON.stringify(spread.toJSON())
}
var uploadData = gzipString(spreadTemplateJSON);
$.post("spread/bindingData", { data: uploadData }, function (data) {
if (data != "0") {
data = ungzipString(data);
var json = JSON.parse(data);
json.calcOnDemand = true;
spread.fromJSON(json, { doNotRecalculateAfterLoad: true });
}
})
designer.setData(AllowBackTemplate, true)
}
async function backTemplate(designer) {
if (spreadTemplateJSON) {
var spread = designer.getWorkbook();
spread.fromJSON(JSON.parse(spreadTemplateJSON))
spreadTemplateJSON = undefined;
}
designer.setData(AllowBindingData, true)
designer.setData(AllowBackTemplate, false)
}
2. 添加服务支持
引入GCExcel ，pom.xml加入
<!-- https://mvnrepository.com/artifact/com.grapecity.documents/gcexcel -->
<dependency>
<groupId>com.grapecity.documents</groupId>
<artifactId>gcexcel</artifactId>
<version>3.2.2</version>
</dependency>新建SpreadController，提供api接口
@RestController
@RequestMapping({"Spread","spread"})
public class SpreadController {
public SpreadController() {
// 指定字体文件路径
// Workbook.FontsFolderPath = "/Users/dexteryao/Documents/Projects/IdeaProjects/common/pdfFont";
}
getTemplates获取resources/reports中模板文件
/*获取reports路径下模板文件列表，返回分号分割文件名string*/
@RequestMapping(value="/getTemplates",method= RequestMethod.POST)
public String getTemplates(){
try {
var path = "src/main/resources/reports";
File reportDir = ResourceUtils.getFile(path);
File[] files = reportDir.listFiles();
String result = "";
for(int i=0;i<files.length;i++){
File file = files;
if(file.isFile() && !file.isHidden()) {
result += (file.getName() + ";");
System.out.println("^^^^^" + file.getName());
}
}
result = compress(result);
return result;
} catch (Exception e) {
e.printStackTrace();
}
return "0";
}
loadTemplate加载模板文件，Excel文件通过GCExcel转JSON
/*通过文件名，返回压缩后json，如果是Excel文件，使用GCExcel toJSON*/
@RequestMapping(value="/loadTemplate",method= RequestMethod.POST)
public String loadTemplate(@RequestParam("fileName") String fileName){
try {
var filePath = "src/main/resources/reports/" + fileName;
String workbookJSON;
Workbook workbook = new Workbook();
System.out.println("开始获取数据：" + new Date());
if(fileName.endsWith("xlsx")) {
URL url = ResourceUtils.getURL(filePath);
workbook.open(url.getFile());
workbookJSON = workbook.toJson();
}
else{
workbookJSON = getTextFileContent(filePath);
}
System.out.println("开始压缩：" + new Date());
String result = compress(workbookJSON);
System.out.println("压缩结束：" + new Date());
return result;
} catch (Exception e) {
e.printStackTrace();
}
return "0";
}
uploadFile上传模板，Excel通过GCExcel 预处理后返回json。此处可直接保持JSON
/*上传文件，报错到reports文件夹，excel和ssjson都通过GCExcel 处理一遍后再返回压缩后ssjson，用于测试GCExcel */
@RequestMapping(value="/uploadFile", headers = ("content-type=multipart/form-data"), method=RequestMethod.POST)
public String uploadFile(@RequestParam("file") MultipartFile file,
@RequestParam("fileName") String fileName) throws FileNotFoundException {
if (file.isEmpty()) {
System.out.println("文件空");
return "上传失败！";
}
String filePath = "src/main/resources/reports/" + fileName;
URL url = ResourceUtils.getURL(filePath);
System.out.println(filePath);
File dest = new File(url.getFile());
try {
file.transferTo(dest);
Workbook workbook = new Workbook();
if(fileName.endsWith("xlsx")) {
workbook.open(url.getFile());
}
else{
String workbookJSON = getTextFileContent(url.getFile());
workbook.fromJson(workbookJSON);
}
String workbookJSON = workbook.toJson();
String result = compress(workbookJSON);
return result;
} catch (IOException e) {
e.printStackTrace();
}
return "上传失败！";
}bindingData绑定数据，数据为模拟数据，设计模板时候需按照数据模型设计器
/*绑定模拟数据*/
@RequestMapping(value="/bindingData",method= RequestMethod.POST)
public String BindingData(@RequestParam(value = "data", required = true) String data){
try {
String json = uncompress(data);
if(json != null && !json.equals("")) {
Workbook workbook = new Workbook();
workbook.setEnableCalculation(false);
workbook.fromJson(json);
bindingDataToWrokbook(workbook);
System.out.println("开始toJSON：" + new Date());
String workbookJSON = workbook.toJson();
System.out.println("开始压缩：" + new Date());
String result = compress(workbookJSON);
System.out.println("结束：" + new Date());
return result;
}
return "0";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "0";
}数据模型
public class DataSourceModel {
public String SecondaryInstitution;
public String EndDate;
public String EndMonth;
public int InsuranceYear;
public String Area;
public int InsuranceCount;
public int BaseAmount;
}
模拟数据并绑定到workbook
public voidbindingDataToWrokbook(Workbook workbook){
System.out.println("开始创建模拟数据：" + new Date());
List<DataSourceModel> ds1 = new ArrayList<>() ;
for(int i = 0; i< 30; i++){
for(int j = 0; j < 77; j++) {
for(int k = 0; k < 2; k++) {
DataSourceModel dsm = new DataSourceModel();
dsm.Area = "地区" + j;
dsm.BaseAmount = i * j * 6 + k;
dsm.InsuranceYear = 2020;
dsm.SecondaryInstitution = "机构" + j + k;
dsm.InsuranceCount = (j % 5) * i + k;
dsm.EndMonth = "2020-" + (k % 2 + 8);
dsm.EndDate = dsm.EndMonth + "-" + (i + 1);
ds1.add(dsm);
}
}
}
System.out.println("模拟数据行数：" + ds1.size());
System.out.println("开始添加数据：" + new Date());
workbook.addDataSource("ds", ds1);
System.out.println("开始绑定数据：" + new Date());
workbook.processTemplate();
System.out.println("结束绑定数据：" + new Date());
}
GZip压缩解压方法，获取文本文件内容
public static String compress(String str) {
if (str.length() <= 0) {
return str;
}
try{
ByteArrayOutputStream bos = null;
GZIPOutputStream os=null; //使用默认缓冲区大小创建新的输出流
byte[] bs =null;
try{
bos = new ByteArrayOutputStream();
os = new GZIPOutputStream(bos);
os.write(str.getBytes()); //写入输出流
os.close();
bos.close();
bs = bos.toByteArray();
return new String(bs, "ISO-8859-1");
}finally{
bs = null;
bos = null;
os = null;
}
}catch(Exception ex){
return str;
}
}
public static String uncompress(String str) {
if (str.length() <= 0) {
return str;
}
try {
ByteArrayOutputStream out = new ByteArrayOutputStream();
ByteArrayInputStream in = new ByteArrayInputStream(str.getBytes("ISO-8859-1"));
GZIPInputStream ungzip = new GZIPInputStream(in);
byte[] buffer = new byte;
int n;
while ((n = ungzip.read(buffer)) >= 0) {
out.write(buffer, 0, n);
}
return new String(out.toByteArray(), "UTF-8");
} catch (Exception e) {
}
return str;
}
private String getTextFileContent(String path){
try {
File file = ResourceUtils.getFile(path);
String encoding = "UTF-8";
Long fileLength = file.length();
byte[] fileContent = new byte;
FileInputStream in = new FileInputStream(file);
in.read(fileContent);
in.close();
String content = new String(fileContent, encoding);
return content;
}
catch (Exception e){
}
return "";
}
至此，上传加载，数据绑定功能也已实现
页:
[1]