EasyExcel操作excel文件

EasyExcel官方文档：https://alibaba-easyexcel.github.io/index.html
记一次使用easyExcel导出列表为excel文件：

    //因为要使用OutputStream所以需要HttpServletResponse
    @GetStatusMapping(path = "/export/multiCallList", statusCode = StatusCode.EXPORT_EXCEL_SUCCESS)
    @ApiOperation(value = "导出手机话单-多话单，话单分析", notes = "根据条件查询")
    @ApiImplicitParam(name = "queryRequest", value = "手机话单多话单分析入参对象", dataType = "MultiCallAnalysisQueryRequest")
    public void exportMultiCalls(MultiCallAnalysisQueryRequest queryRequest, HttpServletResponse response) {
        exportCallListService.exportMultiCalls(queryRequest, response);
    }

  
  /**
     * 导出多话单 话单分析列表
     * @param queryRequest
     */
    @Override
    public void  exportMultiCalls(MultiCallAnalysisQueryRequest queryRequest, HttpServletResponse response) {
        try {
            //构建要导入的数据列表
            List<MultiCallAnalysisVO>  list = multiCallAnalysisService.analysisList(queryRequest);
            List<ExportMultiCallDto> exportMultiCallDtos =  buildExportMultiCall(list);
            //构建文件名
            String senderIds = String.join("_", queryRequest.getSenderId());
            String fileName = URLEncoder.encode( senderIds + "-手机话单-多话单话单分析", "UTF-8").replaceAll("\\+", "%20");
            //设置响应头
            exportDeleteCallService.setResponseHeader(response, fileName);
            //easyExcel写excel
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new ExcelCellsMergeStrategy(0, new int[]{0}))
                    .build();
            //构建数据表，包含表头        
            WriteTable writeTable = getWriteTable();
            //构建sheetName
            String sheetName = getSheetName(queryRequest);
            // 这里注意 如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(exportMultiCallDtos,  writeSheet, writeTable);
            excelWriter.finish();
        } catch (Exception e) {
            log.error("导出手机数据-多话单话单分析异常{}", e.getMessage());
        }
    }


   /**
     * 设置响应头，其中有字符编码，以及文件名和格式
     */
   public void setResponseHeader(HttpServletResponse response, String fileName) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
    }
 

   
    /**
     * 设置动态合并单元格和列宽已经表头
     * @return
     */
    private WriteTable getWriteTable() {
        WriteTable writeTable = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
        List<WriteHandler> handlerList = new ArrayList<>();

        // 默认行宽18
        handlerList.add(new SimpleColumnWidthStyleStrategy(22));
        // 默认列高18
        handlerList.add(new SimpleRowHeightStyleStrategy((short) 18, (short) 18));
        writeTable.setCustomWriteHandlerList(handlerList);
        List<List<String>> header = new ArrayList<>();
        Field[] fields = ExportMultiCallDto.class.getDeclaredFields();
        for (Field field : fields) {
            ExcelProperty mapping = field.getAnnotation(ExcelProperty.class);
            List<String> list1 = new ArrayList<>();
            list1.add(mapping.value()[0]);
            header.add(list1);
        }
        writeTable.setHead(header);
        return writeTable;
    }

   @Data
   public class ExportMultiCallDto {

         @ExcelProperty({"共同联系号码"})
         private String commonPhone;

         @ExcelProperty({"共同联系人姓名"})
         private String commonName;

         @ExcelProperty({"本方号码"})
         private String senderPhone;

         @ExcelProperty({"采集编号"})
         private String collectNum;

         @ExcelProperty({"起止时间"})
         private String startAndStopTime;

         @ExcelProperty({"联系次数"})
         private int contactNum;
         
}