使用easypoi导出Execl动态生成列

使用easypoi导出Execl动态生成列

最近接到个需求是导出考勤记录，然后时间段是不固定的，需要动态生成列。

类似上图的数据，日期选择多久就展示多久，然后我就去研究了下easypoi的导出。

第一步，导入jar包

这是导入导出用到的jar，在pom文件里加上这些就可以了

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.15</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.15</version>
		</dependency>

		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-web</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-annotation</artifactId>
			<version>3.2.0</version>
		</dependency>

第二步，生成列

List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
ExcelExportEntity colEntity = new ExcelExportEntity("姓名", "userName");
colEntity.setNeedMerge(true);
colList.add(colEntity);
。。。

ExcelExportEntity dateColGroup = new ExcelExportEntity("考勤日期", "clockingDate");
List<ExcelExportEntity> dateColList = new ArrayList<ExcelExportEntity>();
for (AttendanceDayWorkHourResp workHourResp : workHourList) {
	//动态生成子列
   dateColList.add(new ExcelExportEntity(workHourResp.getClockingDate(), workHourResp.getClockingDate()));
}

dateColGroup.setList(dateColList);
colList.add(dateColGroup);

colEntity = new ExcelExportEntity("全勤天数（天）", "fullAttendanceDays");
colEntity.setNeedMerge(true);
colList.add(colEntity);

。。。
以下省略几个列的添加代码  
  

第三步，set数据

List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
//存储每一行中的日期数据
List<Map<String, Object>> dataListChild = null;
//存储表格中的每一行数据
Map<String, Object> valMap = null;
for (AttendanceDayStatisticsResp attendanceDayStatistic : attendanceDayStatistics) {

    valMap = new HashMap(12);
    valMap.put("userName", attendanceDayStatistic.getUserName());
    。。。

    GetAttendanceDayWorkHourReq getReq = new GetAttendanceDayWorkHourReq();
    。。。
    //获取指定时间段内的考勤数据
    List<AttendanceDayWorkHourResp> attendanceDayWorkHourResps =
            attendanceListService.getAttendanceDayWorkHour(getReq);

    dataListChild = new ArrayList<>();
    Map<String, Object> dateMap = new HashMap<String, Object>();
    for (AttendanceDayWorkHourResp workHourResp : attendanceDayWorkHourResps) {
    	//设置指定时间段内的考勤数据
        dateMap.put(workHourResp.getClockingDate(),workHourResp.getWorkHour());
    }
    dataListChild.add(dateMap);
    valMap.put("clockingDate", dataListChild);

    valMap.put("fullAttendanceDays", attendanceDayStatistic.getFullAttendanceDays());
    。。。

    list.add(valMap);
}

第四步，导出数据

// 告诉浏览器打开文件的方式
response.setHeader("content-Type", "application/vnd.ms-excel");
 // 下载文件的默认名称
 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("每日统计", "UTF-8") + ".xlsx");

 Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("每日统计", "数据"), colList,
         list);
 workbook.write(response.getOutputStream());
 workbook.close();

导出结果

完整代码

@ApiOperation(value = "导出每日统计")
    @RequestMapping(value = "/exportAttendanceDayStatistics", method = {RequestMethod.POST})
    public void exportAttendanceDayStatistics(@RequestBody ExportAttendanceDayStatisticsReq req,
                                           HttpServletResponse response) {
        try {

            List<AttendanceDayStatisticsResp> attendanceDayStatistics = attendanceListService.exportAttendanceDayStatistics(req);
            if(!CollectionUtils.isEmpty(attendanceDayStatistics)){
                int days = DateUtil.getDaysByDate(req.getStartDate(),req.getEndDate());
                List<AttendanceDayWorkHourResp> workHourList = new ArrayList(days);
                for (int i = 0; i < days; i++) {
                    AttendanceDayWorkHourResp workHourResp = new AttendanceDayWorkHourResp();
                    workHourResp.setClockingDate(DateUtil.dateToStr("MM/dd",DateUtil.dateAdd(req.getStartDate(),"d",i)));
                    workHourList.add(workHourResp);
                }
                dynaCol(response,attendanceDayStatistics,workHourList,req);

            }
        }catch (Exception e) {
            LOGGER.error("-------------导出每日统计接口 失败: {},请求参数{}" , e,gson.toJson(req));
        }
    }

    //动态生成列
    private void dynaCol(HttpServletResponse response,List<AttendanceDayStatisticsResp> attendanceDayStatistics,
                         List<AttendanceDayWorkHourResp> workHourList,ExportAttendanceDayStatisticsReq req){

        try {

            List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
            ExcelExportEntity colEntity = new ExcelExportEntity("姓名", "userName");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("身份证号", "userIdentityCard");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("项目", "projectName");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("劳务公司", "organizationName");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("班组", "teamName");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            ExcelExportEntity dateColGroup = new ExcelExportEntity("考勤日期", "clockingDate");
            List<ExcelExportEntity> dateColList = new ArrayList<ExcelExportEntity>();
            for (AttendanceDayWorkHourResp workHourResp : workHourList) {

                dateColList.add(new ExcelExportEntity(workHourResp.getClockingDate(), workHourResp.getClockingDate()));
            }

            dateColGroup.setList(dateColList);
            colList.add(dateColGroup);

            colEntity = new ExcelExportEntity("全勤天数（天）", "fullAttendanceDays");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("出勤天数（天）", "attendanceDays");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("外勤天数（天", "outAttendanceDays");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("旷工天数（天）", "absenteeismDays");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("异常天数（天）", "exceptionDays");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            colEntity = new ExcelExportEntity("每日累计工作时长（H）", "workHour");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);

            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            //存储每一行中的日期数据
            List<Map<String, Object>> dataListChild = null;
            //存储表格中的每一行数据
            Map<String, Object> valMap = null;
            for (AttendanceDayStatisticsResp attendanceDayStatistic : attendanceDayStatistics) {

                valMap = new HashMap(12);
                valMap.put("userName", attendanceDayStatistic.getUserName());
                valMap.put("userIdentityCard", attendanceDayStatistic.getUserIdentityCard());
                valMap.put("projectName", attendanceDayStatistic.getProjectName());
                valMap.put("organizationName", attendanceDayStatistic.getOrganizationName());
                valMap.put("teamName", attendanceDayStatistic.getTeamName());

                GetAttendanceDayWorkHourReq getReq = new GetAttendanceDayWorkHourReq();

                List<AttendanceDayWorkHourResp> attendanceDayWorkHourResps =
                        attendanceListService.getAttendanceDayWorkHour(getReq);
 
                            

                dataListChild = new ArrayList<>();
                Map<String, Object> dateMap = new HashMap<String, Object>();
                for (AttendanceDayWorkHourResp workHourResp : attendanceDayWorkHourResps) {
                    dateMap.put(workHourResp.getClockingDate(),workHourResp.getWorkHour());
                }
                dataListChild.add(dateMap);
                valMap.put("clockingDate", dataListChild);

                valMap.put("fullAttendanceDays", attendanceDayStatistic.getFullAttendanceDays());
                valMap.put("attendanceDays", attendanceDayStatistic.getAttendanceDays());
                valMap.put("outAttendanceDays", attendanceDayStatistic.getOutAttendanceDays());
                valMap.put("absenteeismDays", attendanceDayStatistic.getAbsenteeismDays());
                valMap.put("exceptionDays", attendanceDayStatistic.getExceptionDays());
                valMap.put("workHour", attendanceDayStatistic.getWorkHour());

                list.add(valMap);
            }

            // 告诉浏览器打开文件的方式
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("每日统计", "UTF-8") + ".xlsx");

            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("每日统计", "数据"), colList,
                    list);
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

参考链接 https://blog.csdn.net/charmer21/article/details/80549211
这篇文章导出的Excel是一条数据有多行多列。