SpreadJS 全家桶实践 - 在线Excel报表系统（二）

上一期SpreadJS 全家桶实践 - 在线Excel报表系统（一），已经搭建好了整个项目框架并集成了SpreadJS、新编辑器和GCExcel。
本期将实现模板上传加载功能,并通过GCExcel的模板功能实现数据绑定。

1. 自定义Ribbon（工具栏），添加上传、加载、绑定和模板按钮

1. <blockquote><script src="lib/pako.min.js" type="text/javascript"></script>

复制代码

将编辑器默认配置default_config.json修改为designerConfig.js 并引入

1. 
   
2. let designerConfig = {
   
3.     "ribbon":[ ... ]

复制代码

添加designer.js和ribbon.file.js分离功能，方便维护
designer.js中添加自定义config，在初始化编辑器时使用自定义config

1. var customerRibbon = {
   
2.     "id": "operate",
   
3.     "text": "操作",
   
4.     "buttonGroups": [
   
5.     ]
   
6. }
   
7. 
   
8. function initStates(designer) {
   
9.     spreadTemplateJSON = undefined;
   
10.     designer.setData(AllowBindingData, true)
    
11.     designer.setData(AllowBackTemplate, false)
    
12. }
    
13. 
    
14. function initDesigner() {
    
15. 
    
16.     customerRibbon.buttonGroups.push(ribbonFileConfig) //将ribbon.file config注入
    
17.     designerConfig.ribbon.push(customerRibbon) //添加自定义ribbon tab
    
18. 
    
19.     var designer = new GC.Spread.Sheets.Designer.Designer(
    
20.         document.getElementById('ssDesigner'), /**designer host */
    
21.         designerConfig, // designerConfigJson /**If you want to change Designer's layout or rewrite it, you can pass in your own Config JSON */ ,
    
22.         undefined /**If you want to use the spread you already created instead of generating a new spread, pass in */
    
23.     );
    
24.     initStates(designer);
    
25.     return designer
    
26. }

复制代码

修改index.js，初始化调用initDesigner 方法，并引入pako，添加前端压缩解压方法

1. var designer
   
2. window.onload = function () {
   
3.     GC.Spread.Common.CultureManager.culture("zh-cn");
   
4.     designer = initDesigner();
   
5. };

复制代码

在ribbon.file.js添加 ribbonFileConfig ，设置上传、加载、绑定和模板按钮，并指定icon和命令

1. var ribbonFileConfig =
   
2. {
   
3.     "label": "文件操作",
   
4.     "thumbnailClass": "ribbon-thumbnail-spreadsettings",
   
5.     "commandGroup": {
   
6.         "children": [
   
7.             {
   
8.                 "direction": "vertical",
   
9.                 "commands": [
   
10.                     {
    
11.                         iconClass: "ribbon-button-download",
    
12.                         text: "加载",
    
13.                         commandName: "getTemplates",
    
14.                         execute: getTemplates,
    
15.                     },
    
16.                     {
    
17.                         iconClass: "ribbon-button-upload",
    
18.                         text: "上传",
    
19.                         commandName: "uploadFile",
    
20.                         execute: uploadFile,
    
21.                     }
    
22.                 ]
    
23.             },
    
24. 
    
25.             {
    
26.                 "type": "separator"
    
27.             },
    
28.             {
    
29.                 "direction": "vertical",
    
30.                 "commands": [
    
31.                     {
    
32.                         iconClass: "ribbon-button-namemanager",
    
33.                         text: "绑定",
    
34.                         commandName: "bindingData",
    
35.                         execute: bindingData,
    
36.                         enableContext: AllowBindingData
    
37.                     },
    
38.                     {
    
39.                         iconClass: "ribbon-button-namemanager",
    
40.                         text: "模板",
    
41.                         commandName: "backTemplate",
    
42.                         execute: backTemplate,
    
43.                         enableContext: "AllowBackTemplate"
    
44.                     }
    
45.                 ]
    
46.             }
    
47.         ]
    
48.     }
    
49. };

复制代码

添加命令对应方法
getTemplates 通过接口获取服务器上模板文件，并在表单中通过超链接形式展示
点击超链接后调用loadTemplate，通过模板名称加载模板json，导入工作簿
uploadFile 方法触发文件选择器，选择文件通过接口上传，服务端GCExcel 处理后返回json，并加载。可上传.xlsx Excel文件和SpreadJS ssjson
bindingData 将模板提交GCExcel与模拟数据绑定并返回结果json
backTemplate 通过缓存的json，还原回模板状态

1. 
   
2. async function getTemplates(designer) {
   
3.     var spread = designer.getWorkbook();
   
4. 
   
5.     $.post("spread/getTemplates", {}, function (data) {
   
6.         if (data != "0") {
   
7.             data = ungzipString(data);
   
8.             console.log(data)
   
9.             var files = data.split(";")
   
10.             var sheet = spread.getActiveSheet();
    
11.             sheet.reset();
    
12.             sheet.defaults.colWidth = 180
    
13.             sheet.setArray(0, 0, [files])
    
14.             // var HyperLinkCellType = GC.Spread.Sheets      
    
15.             var h = new GC.Spread.Sheets.CellTypes.HyperLink();
    
16.             h.onClickAction(function (e) {
    
17.                 var sheet = e.sheet, row = e.row, col = e.col;
    
18.                 var value = sheet.getValue(row, col);
    
19.                 loadTemplate(value)
    
20.             })
    
21.             sheet.getRange(0, 0, 1, files.length).cellType(h)
    
22.         }
    
23.     })
    
24. }
    
25. async function loadTemplate(fileName) {
    
26.     var spread = designer.getWorkbook();
    
27. 
    
28.     let formData = new FormData();
    
29.     formData.append("fileName", fileName);
    
30. 
    
31.     $.ajax({
    
32.         url: "spread/loadTemplate",
    
33.         type: "POST",
    
34.         contentType: false,
    
35.         processData: false,
    
36.         data: formData,
    
37.         success: function (data) {
    
38.             if (data != "0") {
    
39.                 data = ungzipString(data);
    
40.                 var json = JSON.parse(data);
    
41.                 json.calcOnDemand = true;
    
42.                 spread.fromJSON(json, { doNotRecalculateAfterLoad: true });
    
43.                 initStates(designer)
    
44.             }
    
45.         }
    
46.     })
    
47. }
    
48. async function uploadFile(designer) {
    
49.     var spread = designer.getWorkbook();
    
50.     var ribbon_file_selector = document.getElementById("ribbon_file_selector");
    
51.     ribbon_file_selector.onchange = function (event) {
    
52.         console.log(event)
    
53.         var file = event.target.files[0];
    
54.         let formData = new FormData();
    
55.         formData.append("file", file);
    
56.         formData.append("fileName", file.name);
    
57. 
    
58.         $.ajax({
    
59.             url: "spread/uploadFile",
    
60.             type: "POST",
    
61.             contentType: false,
    
62.             processData: false,
    
63.             data: formData,
    
64.             success: function (data) {
    
65.                 if (data != "上传失败！") {
    
66.                     data = ungzipString(data);
    
67.                     var json = JSON.parse(data);
    
68.                     json.calcOnDemand = true;
    
69.                     spread.fromJSON(json, { doNotRecalculateAfterLoad: true });
    
70. 
    
71.                     initStates(designer)
    
72. 
    
73.                 }
    
74.                 else {
    
75.                     alert("上传失败")
    
76.                 }
    
77.             }
    
78.         })
    
79.     }
    
80.     ribbon_file_selector.value = "";
    
81.     ribbon_file_selector.click();
    
82. 
    
83. }
    
84. 
    
85. 
    
86. var spreadTemplateJSON;
    
87. var AllowBindingData = "AllowBindingData", AllowBackTemplate = "AllowBackTemplate";
    
88. async function bindingData(designer) {
    
89.     var spread = designer.getWorkbook();
    
90.     if (!spreadTemplateJSON) {
    
91.         spreadTemplateJSON = JSON.stringify(spread.toJSON())
    
92.     }
    
93.     var uploadData = gzipString(spreadTemplateJSON);
    
94.     $.post("spread/bindingData", { data: uploadData }, function (data) {
    
95.         if (data != "0") {
    
96.             data = ungzipString(data);
    
97.             var json = JSON.parse(data);
    
98.             json.calcOnDemand = true;
    
99.             spread.fromJSON(json, { doNotRecalculateAfterLoad: true });
    
100.         }
     
101.     })
     
102.     designer.setData(AllowBackTemplate, true)
     
103. }
     
104. async function backTemplate(designer) {
     
105.     if (spreadTemplateJSON) {
     
106.         var spread = designer.getWorkbook();
     
107.         spread.fromJSON(JSON.parse(spreadTemplateJSON))
     
108.         spreadTemplateJSON = undefined;
     
109.     }
     
110.     designer.setData(AllowBindingData, true)
     
111.     designer.setData(AllowBackTemplate, false)
     
112. }

复制代码

2. 添加服务支持
引入GCExcel ，pom.xml加入

1. <!-- https://mvnrepository.com/artifact/com.grapecity.documents/gcexcel -->
   
2.                 <dependency>
   
3.                         <groupId>com.grapecity.documents</groupId>
   
4.                         <artifactId>gcexcel</artifactId>
   
5.                         <version>3.2.2</version>
   
6.                 </dependency>

复制代码

新建SpreadController，提供api接口

1. @RestController
   
2. @RequestMapping({"Spread","spread"})
   
3. public class SpreadController {
   
4. 
   
5.     public SpreadController() {
   
6. //        指定字体文件路径
   
7. //        Workbook.FontsFolderPath = "/Users/dexteryao/Documents/Projects/IdeaProjects/common/pdfFont";
   
8.     }

复制代码

getTemplates获取resources/reports中模板文件

1. /*获取reports路径下模板文件列表，返回分号分割文件名string*/
   
2.     @RequestMapping(value="/getTemplates",method= RequestMethod.POST)
   
3.     public String getTemplates(){
   
4.         try {
   
5.             var path = "src/main/resources/reports";
   
6.             File reportDir = ResourceUtils.getFile(path);
   
7.             File[] files = reportDir.listFiles();
   
8.             String result = "";
   
9.             for(int i=0;i<files.length;i++){
   
10.                 File file = files[i];
    
11.                 if(file.isFile() && !file.isHidden()) {
    
12.                     result += (file.getName() + ";");
    
13.                     System.out.println("^^^^^" + file.getName());
    
14.                 }
    
15.             }
    
16.              result = compress(result);
    
17.             return result;
    
18.         } catch (Exception e) {
    
19.             e.printStackTrace();
    
20.         }
    
21.         return "0";
    
22.     }
    

复制代码

loadTemplate加载模板文件，Excel文件通过GCExcel转JSON

1.     /*通过文件名，返回压缩后json，如果是Excel文件，使用GCExcel toJSON*/
   
2.     @RequestMapping(value="/loadTemplate",method= RequestMethod.POST)
   
3.     public String loadTemplate(@RequestParam("fileName") String fileName){
   
4.         try {
   
5.             var filePath = "src/main/resources/reports/" + fileName;
   
6.             String workbookJSON;
   
7.             Workbook workbook = new Workbook();
   
8. 
   
9.             System.out.println("开始获取数据：" + new Date());
   
10.             if(fileName.endsWith("xlsx")) {
    
11.                 URL url = ResourceUtils.getURL(filePath);
    
12.                 workbook.open(url.getFile());
    
13.                 workbookJSON = workbook.toJson();
    
14.             }
    
15.             else{
    
16.                 workbookJSON = getTextFileContent(filePath);
    
17.             }
    
18. 
    
19.             System.out.println("开始压缩：" + new Date());
    
20.             String result = compress(workbookJSON);
    
21. 
    
22.             System.out.println("压缩结束：" + new Date());
    
23.             return result;
    
24.         } catch (Exception e) {
    
25.             e.printStackTrace();
    
26.         }
    
27.         return "0";
    
28.     }

复制代码

uploadFile上传模板，Excel通过GCExcel 预处理后返回json。此处可直接保持JSON

1. /*上传文件，报错到reports文件夹，excel和ssjson都通过GCExcel 处理一遍后再返回压缩后ssjson，用于测试GCExcel */
   
2.     @RequestMapping(value="/uploadFile", headers = ("content-type=multipart/form-data"), method=RequestMethod.POST)
   
3.     public String uploadFile(@RequestParam("file") MultipartFile file,
   
4.                             @RequestParam("fileName") String fileName) throws FileNotFoundException {
   
5.         if (file.isEmpty()) {
   
6.             System.out.println("文件空");
   
7.             return "上传失败！";
   
8.         }
   
9. 
   
10.         String filePath = "src/main/resources/reports/" + fileName;
    
11.         URL url = ResourceUtils.getURL(filePath);
    
12.         System.out.println(filePath);
    
13.         File dest = new File(url.getFile());
    
14.         try {
    
15.             file.transferTo(dest);
    
16. 
    
17.             Workbook workbook = new Workbook();
    
18. 
    
19.             if(fileName.endsWith("xlsx")) {
    
20.                 workbook.open(url.getFile());
    
21.             }
    
22.             else{
    
23.                 String workbookJSON = getTextFileContent(url.getFile());
    
24.                 workbook.fromJson(workbookJSON);
    
25.             }
    
26.             String workbookJSON = workbook.toJson();
    
27.             String result = compress(workbookJSON);
    
28. 
    
29.             return result;
    
30.         } catch (IOException e) {
    
31.             e.printStackTrace();
    
32.         }
    
33.         return "上传失败！";
    
34.     }

复制代码

bindingData绑定数据，数据为模拟数据，设计模板时候需按照数据模型设计器

1.     /*绑定模拟数据*/
   
2.     @RequestMapping(value="/bindingData",method= RequestMethod.POST)
   
3.     public String BindingData(@RequestParam(value = "data", required = true) String data){
   
4.         try {
   
5.             String json = uncompress(data);
   
6.             if(json != null && !json.equals("")) {
   
7. 
   
8.                 Workbook workbook = new Workbook();
   
9.                 workbook.setEnableCalculation(false);
   
10.                 workbook.fromJson(json);
    
11. 
    
12.                 bindingDataToWrokbook(workbook);
    
13. 
    
14.                 System.out.println("开始toJSON：" + new Date());
    
15.                 String workbookJSON = workbook.toJson();
    
16.                 System.out.println("开始压缩：" + new Date());
    
17.                 String result = compress(workbookJSON);
    
18.                 System.out.println("结束：" + new Date());
    
19.                 return result;
    
20.             }
    
21.             return "0";
    
22.         } catch (Exception e) {
    
23.             // TODO Auto-generated catch block
    
24.             e.printStackTrace();
    
25.         }
    
26.         return "0";
    
27.     }

复制代码

数据模型

1. public class DataSourceModel {
   
2.     public String SecondaryInstitution;
   
3.     public String EndDate;
   
4.     public String EndMonth;
   
5.     public int InsuranceYear;
   
6.     public String Area;
   
7.     public int InsuranceCount;
   
8.     public int BaseAmount;
   
9. }
   

复制代码

模拟数据并绑定到workbook

1. public void  bindingDataToWrokbook(Workbook workbook){
   
2. 
   
3.         System.out.println("开始创建模拟数据：" + new Date());
   
4.         List<DataSourceModel> ds1 = new ArrayList<>() ;
   
5.         for(int i = 0; i< 30; i++){
   
6.             for(int j = 0; j < 77; j++) {
   
7.                 for(int k = 0; k < 2; k++) {
   
8.                     DataSourceModel dsm = new DataSourceModel();
   
9.                     dsm.Area = "地区" + j;
   
10.                     dsm.BaseAmount = i * j * 6 + k;
    
11.                     dsm.InsuranceYear = 2020;
    
12.                     dsm.SecondaryInstitution = "机构" + j + k;
    
13.                     dsm.InsuranceCount = (j % 5) * i + k;
    
14.                     dsm.EndMonth = "2020-" + (k % 2 + 8);
    
15.                     dsm.EndDate = dsm.EndMonth + "-" + (i + 1);
    
16.                     ds1.add(dsm);
    
17.                 }
    
18.             }
    
19.         }
    
20.         System.out.println("模拟数据行数：" + ds1.size());
    
21.         System.out.println("开始添加数据：" + new Date());
    
22.         workbook.addDataSource("ds", ds1);
    
23.         System.out.println("开始绑定数据：" + new Date());
    
24.         workbook.processTemplate();
    
25.         System.out.println("结束绑定数据：" + new Date());
    
26.     }

复制代码

GZip压缩解压方法，获取文本文件内容

1. public static String compress(String str) {
   
2.         if (str.length() <= 0) {
   
3.             return str;
   
4.         }
   
5.         try{
   
6.             ByteArrayOutputStream bos = null;
   
7.             GZIPOutputStream os=null; //使用默认缓冲区大小创建新的输出流
   
8.             byte[] bs =null;
   
9.             try{
   
10.                 bos = new ByteArrayOutputStream();
    
11.                 os = new GZIPOutputStream(bos);
    
12.                 os.write(str.getBytes()); //写入输出流
    
13.                 os.close();
    
14.                 bos.close();
    
15.                 bs = bos.toByteArray();
    
16.                 return new String(bs, "ISO-8859-1");
    
17.             }finally{
    
18.                 bs = null;
    
19.                 bos = null;
    
20.                 os = null;
    
21.             }
    
22.         }catch(Exception ex){
    
23.             return str;
    
24.         }
    
25.     }
    
26. 
    
27.     public static String uncompress(String str) {
    
28.         if (str.length() <= 0) {
    
29.             return str;
    
30.         }
    
31.         try {
    
32.             ByteArrayOutputStream out = new ByteArrayOutputStream();
    
33.             ByteArrayInputStream in = new ByteArrayInputStream(str.getBytes("ISO-8859-1"));
    
34.             GZIPInputStream ungzip = new GZIPInputStream(in);
    
35.             byte[] buffer = new byte[256];
    
36.             int n;
    
37.             while ((n = ungzip.read(buffer)) >= 0) {
    
38.                 out.write(buffer, 0, n);
    
39.             }
    
40.             return new String(out.toByteArray(), "UTF-8");
    
41.         } catch (Exception e) {
    
42. 
    
43.         }
    
44.         return str;
    
45.     }
    
46. 
    
47.     private String getTextFileContent(String path){
    
48.         try {
    
49.             File file = ResourceUtils.getFile(path);
    
50.             String encoding = "UTF-8";
    
51.             Long fileLength = file.length();
    
52.             byte[] fileContent = new byte[fileLength.intValue()];
    
53.             FileInputStream in = new FileInputStream(file);
    
54.             in.read(fileContent);
    
55.             in.close();
    
56.             String content = new String(fileContent, encoding);
    
57.             return content;
    
58.         }
    
59.         catch (Exception e){
    
60. 
    
61.         }
    
62.         return "";
    
63.     }
    

复制代码

至此，上传加载，数据绑定功能也已实现