hutool导入导出多sheet页的Excel

背景

有个功能需要导入导出多sheet页的Excel，以前用poi搞，想试下用hutool处理一下。接口已弄完，简单总结一下。

导入

controller

正常使用就行，header的参数用来确认租户

    @ApiOperation(value = "导入字段分组excel", notes = "")
    @PostMapping(value = "/import/field")
    public R importFieldGroupModel(MultipartFile file, String ruleId, String mapId, @RequestHeader(BasePlatformConstant.TENANT_ID) String tenantId) {
        return pmFieldGroupService.importExcel(file, ruleId, mapId,tenantId);
    }
1
2
3
4
5
6

service

我是通过制定sheet页的顺序，来读取sheet内容
ExcelReader sheetReader = ExcelUtil.getReader(file.getInputStream(), i);
官方给出的文档：
https://doc.hutool.cn/pages/ExcelReader/
https://doc.hutool.cn/pages/ExcelUtil

    /**
     * 导入字段分组Excel模板
     * @param file     字段分组Excel
     * @param ruleId   规则id
     * @param mapId    地图id
     * @param tenantId 租户id
     */
    @Override
    public R importExcel(MultipartFile file, String ruleId, String mapId, String tenantId) {
        String aliasNameException = "";
        String sheetNameException = "";
        try {

            InputStream inputStream = file.getInputStream();
            ExcelReader readerAll = ExcelUtil.getReader(inputStream);
            int sheetCount = readerAll.getSheetCount();
            List<String> sheetNameList = readerAll.getSheetNames();

            for (int i = 0; i < sheetCount; i++) {
                // 获取图层名称
                String sheetName = sheetNameList.get(i);
                sheetNameException = sheetName;        
                ExcelReader sheetReader = ExcelUtil.getReader(file.getInputStream(), i);  
                List<Map<String, Object>> sheetFieldGroup = sheetReader.readAll();    
                List<PmFieldGroupVO> targetPpmFieldGroupVOList = new ArrayList<>();
                for (Map fieldInfo : sheetFieldGroup) {
                  // 遍历sheet页中记录，构造需要导入的对象
                    PmFieldGroupVO pmFieldGroupVO = new PmFieldGroupVO();
                    pmFieldGroupVO.setRuleId(ruleId);
                    pmFieldGroupVO.setLayerId(layerId);
                    pmFieldGroupVO.setFieldName(fieldname);
                    pmFieldGroupVO.setGroupId(baseGroup.getId());
                    pmFieldGroupVO.setOrders(order == null ? 10 : order);

                    targetPpmFieldGroupVOList.add(pmFieldGroupVO);
                }
                save(targetPpmFieldGroupVOList);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
            log.error(exception.getMessage());
            log.error("异常图层名称" + sheetNameException);
            log.error("异常字段名称：" + aliasNameException);
            return R.error(exception.getMessage());
        }
        return R.ok();
    }
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

导出

controller

   
   @ApiOperation(value = "导出字段分组excel", notes = "")
    @PostMapping(value = "/export/field")
    public void exportFieldGroupModel(@RequestBody PmLayerVO vo, HttpServletResponse response) {
          pmFieldGroupService.exportExcel(vo,response);
    }
1
2
3
4
5
6

service

ExcelWriter对象默认使用的是第一个sheet页。因此需要根据实际情况决定是否需要重命名
writer.renameSheet(layersEntityList.get(0).getName());//重命名sheet页
切换sheet页也是创建sheet页。可以直接指定sheet页名称
writer.setSheet(layersEntityList.get(i).getName());

注

需要注意的是，切换sheet页后需要设置样式。

 public void exportExcel(PmLayerVO vo, HttpServletResponse response) {
        try {
            ExcelWriter writer = ExcelUtil.getWriter(true);
            //单元格宽度高度
            writer.setColumnWidth(-1, 18);
            writer.setRowHeight(-1, 20);
            writer.setOnlyAlias(true);
            // 定义单元格背景色
            StyleSet style = writer.getStyleSet();
            // 第二个参数表示是否也设置头部单元格背景
            style.setBackgroundColor(IndexedColors.WHITE, true);

            Map<String, String> apiModelProperty = ClassPropertyCommentsUtils.getApiModelProperty(new FieldGroupVO());
           
            // 第一个图层/sheet页需要特殊处理
            List<FieldGroupVO> firstLayerFieldGroupList = getFieldGroupByLayerIdRuleId(vo.getRuleId(),
                    layersEntityList.get(0).getId());
            writer.renameSheet(layersEntityList.get(0).getName());
            if (firstLayerFieldGroupList.size() > 0) {

                apiModelProperty.forEach((k, v) -> writer.addHeaderAlias(v, k));
                writer.write(firstLayerFieldGroupList, true);
            }
            // 按图层构建分组信息
            for (int i = 1; i < layersEntityList.size(); i++) {
                // 切换sheet页
                writer.setSheet(layersEntityList.get(i).getName());
                List<FieldGroupVO> layerFieldGroupList = getFieldGroupByLayerIdRuleId(vo.getRuleId(),
                        layersEntityList.get(i).getId());

                apiModelProperty.forEach((k, v) -> writer.addHeaderAlias(v, k));
                //单元格宽度高度
                writer.setColumnWidth(-1, 18);
                writer.setRowHeight(-1, 20);
                writer.setOnlyAlias(true);
                writer.write(layerFieldGroupList, true);
            }
            ExportFileUtil.setResponseHeader(response, "信息表" + DateTime.now().getTime() + ".xlsx");
            OutputStream os = response.getOutputStream();
            writer.flush(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();

        }
    }
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

工具类

获取对象的ApiModelProperty注释

/**
 * 获取类注释
 *
 */
public class ClassPropertyCommentsUtils {
    public static Map<String, String> getApiModelProperty(Object obj) {
        try {
            // 1.根据类路径获取类
            Class<?> c = obj.getClass();
            // 2.获取类的属性
            Field[] declaredFields = c.getDeclaredFields();
            Map<String, String> map = new LinkedHashMap<>(declaredFields.length);

            // 3.遍历属性，获取属性上ApiModelProperty的值，属性的名，存入Properties
            if (declaredFields.length != 0) {
                for (Field field : declaredFields) {
                    if (field.getAnnotation(ApiModelProperty.class) != null) {
                        map.put(field.getAnnotation(ApiModelProperty.class).value(), field.getName());
                    }
                }
                return map;
            }
        } catch (Exception e) {

        }
        return null;
    }

    public static void nullifyStrings(Object o) {

        for (Field f : o.getClass().getDeclaredFields()) {
            f.setAccessible(true);
            try {
                if (f.getType().equals(String.class)) {
                    String value = (String) f.get(o);
                    if (value != null && value.trim().isEmpty()) {
                        f.set(o, null);
                    }
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }
}
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

导出Excel工具类中设置响应头的函数

    /*
       设置浏览器下载响应头
    */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19