场景:
需求:动态表头,web导出
实体类不适应,可以用EasyExcel提供的不创建对象写出
表单字段表:
结果表:
form_content内容实例:(需要取出的是value的值)
[
{
"id": 45,
"activityId": 165353669805091,
"formField": "姓名",
"inputType": 0,
"value": "1",
"ztAppValue": "姓名:1\n"
},
{
"id": 46,
"activityId": 165353669805091,
"formField": "联系方式",
"inputType": 0,
"value": "1",
"ztAppValue": "联系方式:1\n"
},
{
"id": 47,
"activityId": 165353669805091,
"formField": "爱好",
"inputType": 2,
"optionContent": [
"吃饭",
"睡觉",
"打豆豆"
],
"value": [
"睡觉"
],
"ztAppValue": "爱好:睡觉"
},
{
"id": 48,
"activityId": 165353669805091,
"formField": "性别",
"inputType": 1,
"optionContent": [
"男",
"女"
],
"value": "女",
"ztAppValue": "性别:女\n"
},
{
"id": 49,
"activityId": 165353669805091,
"formField": "学科",
"inputType": 2,
"optionContent": [
"语",
"数",
"英",
"物",
"化"
],
"value": [
"数"
],
"ztAppValue": "学科:数"
},
{
"id": 50,
"activityId": 165353669805091,
"formField": "不良嗜好",
"inputType": 1,
"optionContent": [
"睡觉",
"打豆豆",
"磨牙"
],
"value": "打豆豆",
"ztAppValue": "不良嗜好:打豆豆\n"
},
{
"id": 51,
"activityId": 165353669805091,
"formField": "学生身份证",
"inputType": 0,
"optionContent": "352314200305028375",
"value": "1",
"ztAppValue": "学生身份证:1\n"
},
{
"id": 52,
"activityId": 165353669805091,
"formField": "父亲姓名",
"inputType": 0,
"optionContent": "张三",
"value": "1",
"ztAppValue": "父亲姓名:1\n"
},
{
"id": 53,
"activityId": 165353669805091,
"formField": "父亲电话",
"inputType": 0,
"optionContent": "12387324635",
"value": "1",
"ztAppValue": "父亲电话:1\n"
}
]
注意点:
- 表头、数据封装成以下形式
//封装表头
List<List<String>> headList = new ArrayList<>();
//封装数据
List<List<String>> data = new ArrayList<>();
- 表单list长度必须和一条数据List长度对应
代码:
@RequestMapping(value = "/exportData", method = RequestMethod.GET)
public Map<String, Object> exportData(Long activityId,HttpServletResponse response) throws Exception {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("活动报名表", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 取表头
// 查出数据库的表头字段
List<ActivityForm> activityFormList = activityFormService.getByActivityId(activityId);
List<String> fieldList = new ArrayList<>();
for (ActivityForm activityForm : activityFormList) {
fieldList.add(activityForm.getFormField());
}
// 封装表头
List<List<String>> headList = new ArrayList<>();
for (String str : fieldList) {
List<String> head = new ArrayList<>();
head.add(str);
headList.add(head);
}
List<String> head0 = new ArrayList<>();
head0.add("报名状态");
List<String> head1 = new ArrayList<>();
head1.add("签到");
List<String> head2 = new ArrayList<>();
head2.add("签退");
headList.add(head0);
headList.add(head1);
headList.add(head2);
// 动态无对象封装数据
List<List<String>> data = new ArrayList<>();
// 将所有的formContent数据封装成一个list
List<ActivityFormResult> resultList = activityFormResultService.getResultListExcel(activityId);
List<String> resultString = new ArrayList<>();
for (ActivityFormResult activityFormResult : resultList) {
String formContent = activityFormResult.getFormContent();
resultString.add(formContent);
}
for (int i = 0; i < resultList.size(); i++) {
// 处理formContent数据
JSONArray jsonArray = JSONArray.fromObject(resultString.get(i));
List<String> valueList = new ArrayList<>();
for (int j = 0; j < jsonArray.size(); j++) {
JSONObject jsonObject = jsonArray.getJSONObject(j);
Object value = jsonObject.get("value");
valueList.add(value.toString());
}
// 处理固定字段:报名状态、签到、签退
valueList.add(resultList.get(i).getAuthState().toString());
if (resultList.get(i).getSignIn() != null) {
valueList.add(resultList.get(i).getSignIn().toString());
} else {
valueList.add("");
}
if (resultList.get(i).getSignOff() != null) {
valueList.add(resultList.get(i).getSignOff().toString());
} else {
valueList.add("");
}
data.add(valueList);
}
// 写出Excel
EasyExcel.write(response.getOutputStream())
.autoCloseStream(Boolean.FALSE)
.head(headList)
.sheet("data")
.doWrite(data);
resultMap.put("status", 200);
resultMap.put("message", "请求成功!");
} catch (Exception e) {
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
resultMap.put("status", 500);
resultMap.put("message", "请求失败!");
LoggerUtils.fmtError(getClass(), e, "请求失败。");
}
return resultMap;
}