基于Hutool技术Excel表格导出
今天分享一下基于Hutool技术Excel表格导出，我们先看看导出Excel表格的样子
第1步：引入maven依赖
<dependencies>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
</dependencies>第2步：controller接口
public void exportExcel2(HttpServletRequest req, HttpServletResponse resp) {
try {
String questionType = req.getParameter("questionType") == null ? "1" : req.getParameter("questionType");
List<Map> list = exportDao.exportExcel2(questionType);
List<Map> choiceList = exportDao.exportChoice();
ExcelWriter writer = ExcelUtil.getWriter(true);
writer.writeRow(Arrays.asList("序号","技术领域","考核类型","现有领域","试题类型","试题内容","试题答案","选项1","选项2","选项3","选项4","选项5","选项6","选项7","选项8"));
String answer = "ABCDEFGH";
if("1".equals(questionType) || "2".equals(questionType)) { // 多选题,单选题
list.stream().forEach(item->{
List<String> sList = new ArrayList<>();
StringBuilder stringBuilder = new StringBuilder();
choiceList.stream().forEach(choiceItem->{
if(item.get("question_id").equals(choiceItem.get("question_id"))){
sList.add((String) choiceItem.get("choice_content"));
stringBuilder.append(choiceItem.get("is_right"));
}
});
switch (sList.size()) {
case 1:
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),null,null,null,null,null,null,null));
break;
case 2:
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),sList.get(1),null,null,null,null,null,null));
break;
case 3:
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),sList.get(1),sList.get(2),null,null,null,null,null));
break;
case 4:
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),sList.get(1),sList.get(2),sList.get(3),null,null,null,null));
break;
case 5:
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),sList.get(1),sList.get(2),sList.get(3), sList.get(4),null,null,null));
break;
case 6:
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),sList.get(1),sList.get(2),sList.get(3), sList.get(4), sList.get(5),null,null));
break;
case 7:
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),sList.get(1),sList.get(2),sList.get(3), sList.get(4), sList.get(5), sList.get(6),null));
break;
case 8:
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),sList.get(1),sList.get(2),sList.get(3), sList.get(4), sList.get(5), sList.get(6),sList.get(7)));
break;
}
});
} else if ("3".equals(questionType)) { // 填空题
list.stream().forEach(item->{
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),item.get("question_answer"),null,null,null,null,null,null,null,null));
});
} else if("4".equals(questionType)) { // 判断题
list.stream().forEach(item->{
String ans = item.get("question_answer").toString();
ans = "1".equals(ans) ? "正确" : "错误";
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),ans,null,null,null,null,null,null,null,null));
});
} else if("5".equals(questionType) || "6".equals(questionType)) { // 简答题 或 程序设计题
list.stream().forEach(item->{
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),item.get("question_answer"),null,null,null,null,null,null,null,null));
});
}
// 设置响应头content-type
resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
// 设置下载文件名
String fileName= URLEncoder.encode("成绩信息表","UTF-8");
resp.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
ServletOutputStream outputStream= resp.getOutputStream();
// 输出excel文件
writer.flush(outputStream, true);
outputStream.close();
writer.close();
} catch (SQLException | IOException throwables) {
throwables.printStackTrace();
}
}关键技术点：
public void exportExcel2(HttpServletRequest req, HttpServletResponse resp) {
try {
// 省略数据获取的操作....
ExcelWriter writer = ExcelUtil.getWriter(true);
// 设置表格列
writer.writeRow(Arrays.asList("序号","技术领域","考核类型","现有领域","试题类型","试题内容","试题答案","选项1","选项2","选项3","选项4","选项5","选项6","选项7","选项8"));
String answer = "ABCDEFGH";
// 数据需要循环写入....
// 写入数据操作
writer.writeRow(Arrays.asList(item.get("question_id"),item.get("field_name"),null,null,item.get("question_type"),item.get("question_content"),this.mapCharacters(stringBuilder.toString(), answer),sList.get(0),null,null,null,null,null,null,null));
// 设置响应头content-type
resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
// 设置下载文件名
String fileName= URLEncoder.encode("成绩信息表","UTF-8");
resp.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
ServletOutputStream outputStream= resp.getOutputStream();
// 输出excel文件
writer.flush(outputStream, true);
outputStream.close();
writer.close();
} catch (SQLException | IOException throwables) {
throwables.printStackTrace();
}
}
