用SpreadJS和GCExcel实现模板语法的实时预览

收藏

我们在用GCExcel的模板语法时，每次在Excel中写完模板，去执行完扩展模板以后，需要再次打开扩展后的表格，非常不方便，因此结合SpreadJS，用SpreadJS进行模板编辑，以及扩展后的结果查看会事半功倍
1.加载模板，将模板展示到SpreadJS中

var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), {
sheetCount: 1
});
// 加载一级行模板
$("#loadA").click(function () {
var excelIo = new GC.Spread.Excel.IO();
// Download Excel file
var excelFilePath = 'span/一级行汇总.xlsx';
var xhr = new XMLHttpRequest();
xhr.open('GET', excelFilePath, true);
xhr.responseType = 'blob';
xhr.onload = function (e) {
if (this.status == 200) {
// get binary data as a response
var blob = this.response;
// convert Excel to JSON
excelIo.open(blob, function (json) {
var workbookObj = json;
spread.fromJSON(workbookObj);
}, function (e) {
// process error
alert(e.errorMessage);
}, {});
}
};
xhr.send();
})

复制代码

页面如下：

2.保存模板（将修改后的模板传给后端保存到模板路径）前端代码：

$("#saveA").click(function () {
var json = spread.toJSON();
var excelIo = new GC.Spread.Excel.IO();
// here is excel IO API
excelIo.save(json, function (blob) {
var fd = new FormData();
fd.append("file", blob);
$.ajax({
url: "exportExcelA",
type: "POST",
contentType: false,
processData: false,
data: fd,
success: function (data) {
if (data.isSuccess == 1) {
alert("上传成功！");
} else {
alert(data.errorMessage);
}
},
error: function (ex) {
alert("上传失败：" + ex);
}
});
}, function (e) {
alert(e);
});
})

复制代码

后端代码：

// 保存一级行模板
@RequestMapping(value = "/exportExcelA", method = RequestMethod.POST)
@ResponseBody
public Map<String, Object> exportExcelA(HttpServletRequest request, @RequestParam("file") MultipartFile file)
throws IOException, ServletException {
String path = ClassUtils.getDefaultClassLoader().getResource("").getPath() + File.separator + "static"
+ File.separator + "span";
System.out.println(path);
File floaderpath = new File(path);
if (!floaderpath.exists()) {
floaderpath.mkdir();
}
// File newfile = new File(path + File.separator + new Date().getTime() +
// ".xlsx");
File newfile = new File(path + File.separator + "一级行汇总.xlsx");
int result = 0;
Map<String, Object> resultMap = new HashMap<String, Object>();
try {
file.transferTo(newfile);
resultMap.put("isSuccess", 1);
} catch (Exception e) {
resultMap.put("isSuccess", result);
resultMap.put("errorMessage", e.getMessage());
e.printStackTrace();
}
return resultMap;
}

复制代码

3.模板扩展：

// 将二级行数据汇总到一级行
@RequestMapping(value = "/spanExcelA", method = RequestMethod.GET)
@ResponseBody
public Map<String, Object> spanExcelA(HttpServletRequest request) throws IOException, ServletException {
int result = 0;
Map<String, Object> resultMap = new HashMap<String, Object>();
String spanPath = ClassUtils.getDefaultClassLoader().getResource("").getPath() + File.separator + "static"
+ File.separator + "result";
String savePath = ClassUtils.getDefaultClassLoader().getResource("").getPath() + File.separator + "static"
+ File.separator + "span";
File savefloaderpath = new File(savePath);
if (!savefloaderpath.exists()) {
savefloaderpath.mkdir();
}
try {
// 初始化合并workbook并载入合并模板
Workbook spanWorkbook = new Workbook();
spanWorkbook.open(FillInSpreadApplication.class.getClassLoader().getResourceAsStream("static/span/一级行汇总.xlsx"));
SalesData datasource = new SalesData();
datasource.salary = new ArrayList<SalesRecord>();
// #region Init Data
SalesRecord record1 = new SalesRecord();
record1.name = "二级行A";
record1.project = "1. 当年成本列支数（财务口径）";
record1.salariesPayable = 6000;
record1.otherSubjectsSalariesPayable = 3000;
record1.employeeWelfare = 2000;
record1.educationFunds = 4000;
record1.educationFundsOtherSubjects = 6000;
record1.tradeUnionFundsOtherSubjects = 7000;
datasource.salary.add(record1);
SalesRecord record2 = new SalesRecord();
record2.name = "二级行B";
record2.project = "1. 当年成本列支数（财务口径）";
record2.salariesPayable = 5000;
record2.otherSubjectsSalariesPayable = 3000;
record2.employeeWelfare = 2000;
record2.educationFunds = 4000;
record2.educationFundsOtherSubjects = 6000;
record2.tradeUnionFundsOtherSubjects = 7000;
datasource.salary.add(record2);
SalesRecord record3 = new SalesRecord();
record3.name = "二级行C";
record3.project = "1. 当年成本列支数（财务口径）";
record3.salariesPayable = 5000;
record3.otherSubjectsSalariesPayable = 3000;
record3.employeeWelfare = 2000;
record3.educationFunds = 4000;
record3.educationFundsOtherSubjects = 6000;
record3.tradeUnionFundsOtherSubjects = 7000;
datasource.salary.add(record3);
SalesRecord record4 = new SalesRecord();
record4.name = "二级行A";
record4.project = "2. 加：其他成本支出科目中核算金额";
record4.salariesPayable = 5000;
record4.otherSubjectsSalariesPayable = 3000;
record4.employeeWelfare = 2000;
record4.educationFunds = 4000;
record4.educationFundsOtherSubjects = 6000;
record4.tradeUnionFundsOtherSubjects = 7000;
datasource.salary.add(record4);
SalesRecord record5 = new SalesRecord();
record5.name = "二级行B";
record5.project = "2. 加：其他成本支出科目中核算金额";
record5.salariesPayable = 5000;
record5.otherSubjectsSalariesPayable = 3000;
record5.employeeWelfare = 2000;
record5.educationFunds = 4000;
record5.educationFundsOtherSubjects = 6000;
record5.tradeUnionFundsOtherSubjects = 7000;
datasource.salary.add(record5);
SalesRecord record6 = new SalesRecord();
record6.name = "二级行C";
record6.project = "2. 加：其他成本支出科目中核算金额";
record6.salariesPayable = 5000;
record6.otherSubjectsSalariesPayable = 3000;
record6.employeeWelfare = 2000;
record6.educationFunds = 4000;
record6.educationFundsOtherSubjects = 6000;
record6.tradeUnionFundsOtherSubjects = 7000;
datasource.salary.add(record6);
// #endregion
System.out.println(datasource.salary.size());
// Init template global settings
spanWorkbook.getNames().add("TemplateOptions.KeepLineSize", "true");
// Add data source
spanWorkbook.addDataSource("ds", datasource);
// Invoke to process the template
spanWorkbook.processTemplate();
// Save to an excel file
spanWorkbook.save(savePath + File.separator + "一级行汇总结果.xlsx");
resultMap.put("合并成功", 1);
} catch (Exception e) {
resultMap.put("isSuccess", result);
resultMap.put("errorMessage", e.getMessage());
e.printStackTrace();
}
return resultMap;
}

复制代码

4.加载模板扩展后的报表：

// 展示一级行汇总结果
$("#loadSpanA").click(function () {
var excelIo = new GC.Spread.Excel.IO();
// Download Excel file
var excelFilePath = 'span/一级行汇总结果.xlsx';
var xhr = new XMLHttpRequest();
xhr.open('GET', excelFilePath, true);
xhr.responseType = 'blob';
xhr.onload = function (e) {
if (this.status == 200) {
// get binary data as a response
var blob = this.response;
// convert Excel to JSON
excelIo.open(blob, function (json) {
var workbookObj = json;
spread.fromJSON(workbookObj);
spread.setActiveSheet("工资三费");
}, function (e) {
// process error
alert(e.errorMessage);
}, {});
}
};
xhr.send();
})

复制代码

SpreadJS的展示效果如下：
image.png665004882.png

非常的方便，完整代码见附件

		自动登录	找回密码
密码			立即注册

用SpreadJS和GCExcel实现模板语法的实时预览

0 个回复