ExcelUtilTest.java
5.47 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
package com.bootdo.common.utils;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
public class ExcelUtilTest {
public static void main(String[] args) throws Exception {
InputStream in = new FileInputStream("d://12348.xls");
Map<String, String> fieldd = new HashMap<String, String>();
fieldd.put("ids", "ids");
fieldd.put("titles", "titles");
fieldd.put("answers", "answers");
List<HzdyDO> resultList = new ArrayList<HzdyDO>();
resultList = ExeclUtil.ExecltoList(in, HzdyDO.class, fieldd);
List<Integer> idLength = new ArrayList<Integer>();
List<Integer> titleLength = new ArrayList<Integer>();
List<Integer> answerLength = new ArrayList<Integer>();
for(HzdyDO hzdyDO:resultList) {
idLength.add(hzdyDO.getIds().split("[@][|],").length);
titleLength.add(hzdyDO.getTitles().split("[@][|],").length);
answerLength.add(hzdyDO.getAnswers().split("[@][|],").length);
}
//id与title对比
for(int i=0;i<idLength.size();i++) {
if(idLength.get(i).intValue() != titleLength.get(i).intValue()) {
System.out.println("第"+i+1+"条数据的id与title长度不匹配");
break;
}
if(idLength.get(i).intValue() != answerLength.get(i).intValue()) {
System.out.println("第"+i+1+"条数据的id与answer长度不匹配");
break;
}
}
//对查询结果中的题目id，题目文本，用户答案进行拆分
List<Map<String, Object>> finalDataList = new ArrayList<Map<String,Object>>();
for(int i=0;i<10;i++) {
Map finalMap = new HashMap<String, Object>();
finalMap.put("enterpriseName", i+"a");//乙方企业
finalMap.put("projectName", i+"b");//项目名称
finalMap.put("taskDt", i+"c");//任务月度
finalMap.put("fullName", i+"d");//答题人姓名
finalMap.put("phone", i+"e");//答题人手机号
finalMap.put("createDt", i+"f");//答题时间
finalMap.put("audit_status", i+"g");//状态
String topicIds = String.valueOf(resultList.get(i).getIds());//用户答题的，题目id数组, 与问卷标准的id数组可能不匹配,但与用户答题的题目文本，题目答案数组是匹配的
topicIds = topicIds.substring(0, topicIds.length()-2);
String topicTitles = String.valueOf(resultList.get(i).getTitles());//用户答题的，题目文本数组
topicTitles = topicTitles.substring(0,topicTitles.length()-2);
String topicAnswers = String.valueOf(resultList.get(i).getAnswers());//用户答题的，题目答案数组
topicAnswers = topicAnswers.substring(0,topicAnswers.length()-2);
List<String> topicIdList = new ArrayList<String>();//当前问卷应该存在的题目id 问卷的标准id数组
String [] topicIdArr = topicIds.split("[@][|],");
String [] topicTitleArr = topicTitles.split("[@][|],");
String [] topicAnswerArr = topicAnswers.split("[@][|],");
for(int j=0;j<topicIdArr.length;j++) {
finalMap.put(topicIdArr[j], topicAnswerArr[j]);
}
finalDataList.add(finalMap);
}
//设置报表名字
String fileName="医学调研任务报表新";
//设置分页名称
String sheetName="医学调研任务记录新";
//设置报表1表头
String [] columns = {"乙方企业","项目名称","任务月度","项目参与人","手机号","填写时间","状态"};
//状态单独处理
String statusRemark = "audit_status|1:审核中|2:审核通过|3:审核不通过|其他";
//设置报表从map中获取的key，必须与表头长度对应
String [] columnKeys = {"enterpriseName","projectName","taskDt","fullName","phone","createDt",statusRemark};
//增加表头，增加key 表头为题目的title ，key为题目的id
List<String> columnArr = new ArrayList<String>(Arrays.asList(columns));
List<String> columnKeyArr = new ArrayList<String>(Arrays.asList(columnKeys));
String [] topicIdArr = resultList.get(0).getIds().split("[@][|],");
String [] topicTitleArr = resultList.get(0).getTitles().split("[@][|],");
for(int j=0;j<topicIdArr.length;j++) {
columnArr.add(String.valueOf(topicTitleArr[j]));
columnKeyArr.add(String.valueOf(topicIdArr[j]));
}
String[] columnsV2 = new String[columnArr.size()];
String[] columnKeysV2 = new String[columnKeyArr.size()];
columnArr.toArray(columnsV2);
columnKeyArr.toArray(columnKeysV2);
//第一个excel表格
JxlExcelUtils.exportOwoSheetExcleFinal(null, fileName, finalDataList,sheetName, columnsV2, columnKeysV2);
System.out.println(999);
}
}