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