复杂excel导出（动态列，条件背景色）

依赖

            <!-- excel工具 -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.2</version>
            </dependency>

工具类

@Data
@Slf4j
public class ExcelExportUtil {
    private static final Logger log = LoggerFactory.getLogger(ExcelExportUtil.class);

   	//给每一列设置一个key值，key值和表头根据数组位置对应；
	//数据和样式每行各一个map，根据key值进行填充；
	//样式内容放在一个map内，方便取用。

    /**各个列的表头*/
    private List<String> headList;
    /**各个列的key值*/
    private List<String> headKey;
    /**各个列的宽度*/
    private List<Integer> headWidth;
    /**需要填充的数据信息*/
    private List<HashMap> data;

    /**需要填充的表格样式,key是列的key，value是具体样式的key*/
    private List<HashMap<String,String>> cellStyleList;
    /** 样式列表 */
    private Map<String, CellStyle> styles;

    /**字体大小*/
    private int fontSize = 14;
    /**行高*/
    private int rowHeight = 30;
    /**列宽*/
    private int columWidth = 200;
    /**工作表*/
    private String sheetName = "sheet";
    /**文件名*/
    private String fileName;



    HSSFWorkbook wb;

    HSSFSheet sheet;

    HSSFCellStyle headCellStyle;

    HttpServletResponse response;

    public ExcelExportUtil() {
        this.wb = new HSSFWorkbook();
    }

    public ExcelExportUtil exportExport(HttpServletResponse response) throws IOException {
        this.response = response;
        int size = data.size();
        int count = (size - 1) / 50000 + 1;
        for (int i = 0; i < count; i++) {
            sheet = wb.createSheet(sheetName + (i + 1));
            exportExport(sheet,
                    data.subList(i * 50000, ((i + 1) * 50000 > size ? size : 50000 * (i + 1))));
        }
        return this;
    }

    private void exportExport(HSSFSheet sheet, List<HashMap> data) throws IOException{

        //检查参数配置信息
        checkConfig();
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFDataFormat format = wb.createDataFormat();
        //这样才能真正的控制单元格格式，@就是指文本型
        cellStyle.setDataFormat(format.getFormat("@"));
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);

        HSSFRow headRow = sheet.createRow(0);
        //设置列头元素
        for (int i = 0; i < headList.size(); i++) {
            Integer width = 15;
            if (headWidth != null && headWidth.size() >= headList.size()){
                width = headWidth.get(i);
            }
            sheet.setColumnWidth(i, 256 * width + 184);
            HSSFCell cellHead = headRow.createCell(i);
            cellHead.setCellValue(headList.get(i));
            cellHead.setCellStyle(headCellStyle);
        }

        //开始写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            HSSFRow row = sheet.createRow(a);
            HashMap map = data.get(i);
            HashMap styleMap = cellStyleList.get(i);
            HSSFCell cell;
            for (int j = 0; j < headKey.size(); j++) {
                cell = row.createCell(j);
                Object valueObject = map.get(headKey.get(j));
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    cell.setCellValue(((Integer) (valueObject)).floatValue());
                    cell.setCellType(CellType.NUMERIC);
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    cell.setCellValue(((BigDecimal) (valueObject)).floatValue());
                    cell.setCellType(CellType.NUMERIC);
                } else if (valueObject instanceof Double) {
                    //取出的数据是BigDecimal
                    cell.setCellValue((Double) valueObject);
                    cell.setCellType(CellType.NUMERIC);
                }else {
                    //取出的数据是字符串直接赋值
                    cell.setCellValue(StringUtils.isEmpty(String.valueOf(valueObject)) ? "" : String.valueOf(valueObject));
                }
                if(styleMap.get(headKey.get(j))!=null){
                    cell.setCellStyle(styles.get(styleMap.get(headKey.get(j))));
                }else{
                    cell.setCellStyle(cellStyle);

                }
            }
            a++;
        }
    }

    public void flushExplorer() throws Exception{
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));
        try {
            wb.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            log.error("导出Excel异常{}", e.getMessage());
            throw new UtilException("导出Excel失败，请联系网站管理员！");
        }
        finally
        {
            IOUtils.closeQuietly(wb);
        }



    }

    public HSSFCellStyle getHSSFCellStyle(){
        return wb.createCellStyle();
    }
    /**
     * 检查数据配置问题
     *
     * @throws IOException 抛出数据异常类
     */
    protected void checkConfig() throws IOException {
        if (headKey == null) {
            throw new IOException("表头不能为空");
        }

        if (headWidth != null && headWidth.size() < headKey.size()){
            throw new IOException("设置宽度的列数必须超过表头列数");
        }

        if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {
            throw new IOException("字体、宽度或者高度不能为负值");
        }

        if (StringUtils.isEmpty(sheetName)) {
            throw new IOException("工作表表名不能为NULL");
        }
        createDefaultHeadStyle();
    }

    public void createDefaultHeadStyle() {
        //创建表头样式
        headCellStyle= wb.createCellStyle();
        //居中
        headCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //背景色
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

        //字体


        HSSFFont font = wb.createFont();
        font.setFontName("Arial");
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontHeightInPoints((short)10);
        //字体增粗
        font.setBold(true);         
        headCellStyle.setFont(font);

        headCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        headCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        headCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        headCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
    }
}

测试用例

 @GetMapping("/exportSum")
    public void exportSum(HttpServletResponse response, AttendanceCountDetail detail) throws Exception {






        /*表头*/
        List<String> headList = new ArrayList<>();
        /*表头对应的key值*/
        List<String> headKey = new ArrayList<>();
        /*每一列的宽度*/
        List<Integer> widList = new ArrayList<>();


        headList.add("开始");
        headKey.add("begin");
        widList.add(15);
        headList.add("结束");
        headKey.add("end");
        widList.add(15);
        headList.add("总出勤天数");
        headKey.add("sumDays");
        widList.add(13);
        headList.add("夜班天数");
        headKey.add("yeDays");
        widList.add(13);


        Date tmp = DateUtils.dateTime("yyyyMMdd","20220925");
        Date  endDate =  DateUtils.dateTime("yyyyMMdd","20221024");

        HashMap holidays = HolidayUtil.getHoliday(2022);

        // 动态列，日期，同时节假日标注工资倍数
        while (tmp.getTime() <= endDate.getTime()) {

            Integer yyyyMMdd = Integer.valueOf(DateUtils.fmtDateToStr(tmp, "yyyyMMdd"));

            if(holidays.get(yyyyMMdd)!=null){
                headList.add(DateUtils.fmtDateToStr(tmp,"MM/dd")+"("+holidays.get(yyyyMMdd)+"倍"+")");
                headKey.add(DateUtils.fmtDateToStr(tmp,"yyyyMMdd"));
            }else{
                headList.add(DateUtils.fmtDateToStr(tmp,"MM/dd"));
                headKey.add(DateUtils.fmtDateToStr(tmp,"yyyyMMdd"));
            }

            widList.add(8);

            // 天数加上1
            tmp = DateUtils.addDays(tmp,1);

        }


        /*数据集合,每行一个hashmap*/
        ArrayList<HashMap> dataList = new ArrayList<>();
        /*样式集合,每行一个hashmap，再根据value去具体样式的hashmap找cellStyle*/
        ArrayList<HashMap<String, String>> styleList = new ArrayList<>();

        Set set = new HashSet();
        HashMap<String, Object> dataMap = new HashMap<>();
        HashMap<String, String> styleMap = new HashMap<>();
        Integer sum = 1;
        Integer yeDays = 0;

        /**/
        ArrayList<AttendanceCountDetail> aList = attendanceCountService.getCountSum(detail);

        for (AttendanceCountDetail a : aList) {
            if (set.contains(a.getStaffName())) {
                /*加班时间判断*/
                if(a.getOverTimeStr()==null){
                    dataMap.put(a.getInDate().toString(), "异常");
                }else if(a.getOverTimeStr()<0){
                    dataMap.put(a.getInDate().toString(), "假");//请假
                }else{
                    if(holidays.get(a.getInDate())!=null){
                        dataMap.put(a.getInDate().toString(), a.getOverTimeStr()+8);
                    }else {
                        dataMap.put(a.getInDate().toString(), a.getOverTimeStr());
                    }
                }
                /*夜班判断*/
                if(a.getBanciType() == AttendanceCountServiceImpl.Banci.YEBAN.value()){
                    yeDays+=1;
                    styleMap.put(a.getInDate().toString(), "bColor");
                }
                dataMap.put("end",a.getInDate());
                sum+=1;

            }else{
                if(dataMap.size()!=0) {
                    dataMap.put("sumDays",sum);
                    dataMap.put("yeDays",yeDays);

                    dataList.add(dataMap);
                    styleList.add(styleMap);

                    dataMap = new HashMap<>();
                    styleMap  = new HashMap<String, String>();
                    sum = new Integer(0);
                    yeDays = new Integer(0);
                }
                if(a.getStaffName()==null){
                    break;
                }


                dataMap.put("begin",a.getInDate());
                /*加班时间判断*/

                if(a.getOverTimeStr()==null){
                    dataMap.put(a.getInDate().toString(), "异常");
                }else if(a.getOverTimeStr()<0){
                    dataMap.put(a.getInDate().toString(), "假");
                }else{
                    if(holidays.get(a.getInDate())!=null){
                        dataMap.put(a.getInDate().toString(), a.getOverTimeStr()+8);
                    }else{
                        dataMap.put(a.getInDate().toString(), a.getOverTimeStr());
                    }
                }

                /*夜班判断*/
                if(a.getBanciType() == AttendanceCountServiceImpl.Banci.YEBAN.value()){
                    yeDays+=1;
                    styleMap.put(a.getInDate().toString(), "bColor");
                }
                dataMap.put("end",a.getInDate());
                sum+=1;


            }
        }


        ExcelExportUtil excelExportUtil = new ExcelExportUtil();


        /*存放具体样式*/
        HSSFCellStyle cellStyle = excelExportUtil.getHSSFCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HashMap<String, CellStyle> styles = new HashMap<>(1);
        styles.put("bColor",cellStyle);
        excelExportUtil.setStyles(styles);



		/*存放表头信息*/	
        excelExportUtil.setHeadList(headList);
        excelExportUtil.setHeadKey(headKey);
        excelExportUtil.setHeadWidth(widList);
        /*存放表格内容，样式id*/
        excelExportUtil.setCellStyleList(styleList);
        excelExportUtil.setData(dataList);
        
        excelExportUtil.setSheetName("考勤统计");
        excelExportUtil.setFileName("考勤");
        excelExportUtil.exportExport(response).flushExplorer();
    }

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class AttendanceCountDetail {

    

    /**姓名**/
    private String staffName;

    /**序列**/
    private String positionSeq;

    /**部门**/
    private String department;


    /**班次 1=白,2=夜,3=缺失,4=异常**/
    private Integer banciType;
    
    /**上班日期**/
    private Integer inDate;

    /**上班时间 **/
    private Integer inTime;

    /**下班日期**/
    private Integer outDate;

    /**下班时间**/
    private Integer outTime;
    
    /**加班时间（单位h,向下取最近的.5h）**/
    private Double overTimeStr;

    /**是否迟到  0:False   1:True **/
    private Integer isLate;

}