基于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-typeresp.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-typeresp.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();}}