java excel导出动态列

公共的:

@Data
public class TenantTitleParamReq {
    @ApiModelProperty("列表标题")
    private String name;
    @ApiModelProperty("列表标题属性值")
    private String attributeValue;
    @ApiModelProperty("列表循序")
    private Integer titleOrder;

自己用的:

@Data
public class ProjectTrendsExportReq {

    private List<Long> ids;    

    private List<TenantTitleParamReq> titleParamReqs;
}

excel封装类:

@Getter
@Setter
public class ProjectExportExcel extends BaseExcel {

    @ExcelProperty(value = "序号")
    private long serNo;

    @ExcelProperty(value = "项目编号")
    private String bizNo;

    @ExcelProperty(value = "项目名称")
    private String name;

    /*@ExcelProperty(value = "客户编号",converter = MyLongConverter.class)
    private Long cooperationCompanyId;*/

    @ExcelProperty(value = "业主名称")
    private String cooperationCompanyName;

    @ExcelProperty(value = "项目类型")
    private String projectTypeName;

    @ExcelProperty(value = "所在地区")
    private String area;

    @ExcelProperty(value = "项目负责人")
    private String salesmanTenantUserName;

    @ExcelProperty(value ="项目登记人")
    private String projectRegistrantName;

    @ExcelProperty(value = "项目状态")
    private String statusName;

    @ExcelProperty(value ="项目跟进次数")
    private Integer followNumber;
    @ExcelProperty(value ="项目勘察次数")
    private Integer surveyNumber;
    @ExcelProperty(value ="报价方案编号")
    private String quotationProgrammeNumber;

    /*@ExcelProperty(value = "项目阶段")
    private String stageName;*/

    /*@ExcelProperty(value = "创建人")
    private String createName;

    @ExcelProperty(value = "创建时间",converter = MyLocalDateTimeConverter.class)
    private LocalDateTime createTime;

    @ExcelProperty(value = "项目简介")
    private String introduction;

    @ExcelProperty(value = "预计开工日期",converter = MyLocalDateConverter.class)
    private LocalDate predictWorkDate;

    @ExcelProperty(value = "预计完工日期",converter = MyLocalDateConverter.class)
    private LocalDate predictEndDate;

    @ExcelProperty(value = "成交概率")
    private String bargainRatioShowName;

    @ExcelProperty(value = "预计签约金额",converter = MyBigDecimalConverter.class)
    private BigDecimal signPrice;

    @ExcelProperty(value = "业主目标成本",converter = MyBigDecimalConverter.class)
    private BigDecimal costPrice;*/

    @ExcelProperty(value = "最近更新人")
    private String modifiedName;

    @ExcelProperty(value = "最近更新时间",converter = MyLocalDateTimeConverter.class)
    private LocalDateTime lastModifiedTime;

    @ExcelProperty(value = "审批状态")
    private String flowStatusName;

    @ExcelProperty(value = "删除时审批状态")
    private String recycledFlowStatusName;

    @ExcelProperty(value = "删除原因")
    private String recycleDesc;

}

具体实现:

public BaseUrlResp exportTrends(ProjectTrendsExportReq req) throws IOException {
		List<TenantTitleParamReq> titleParamReqs = req.getTitleParamReqs();
		if(CollectionUtils.isEmpty(titleParamReqs))throw new BizException("表头不能为空");
		ProjectPagePageReq pagePageReq = new ProjectPagePageReq();
		pagePageReq.setProjectIds(req.getIds());
		pagePageReq.setCurrent(1);
		pagePageReq.setSize(Integer.MAX_VALUE);
//		pagePageReq.setIsRecycled(req.getIsRecycled());
		BasePageListResp<ApiProjectPageResp> page = this.page(pagePageReq);
		List<ProjectExportExcel> collect = Lists.newArrayList();
		BaseUrlResp result = new BaseUrlResp();
		String url = "";
		if(CollectionUtils.isNotEmpty(page.getRecords())){
			for (int i = 0; i < page.getRecords().size(); i++) {
				ProjectExportExcel excel = new ProjectExportExcel();
				BeanUtils.copyProperties(page.getRecords().get(i), excel);
				excel.setSerNo(i + 1);
				collect.add(excel);
			}
			List<String>keys=new ArrayList<>();
			List<String>header=new ArrayList<>();
			titleParamReqs.forEach(x->{
				keys.add(x.getAttributeValue());
				header.add(x.getName());
			});
			List<JSONObject>data=new ArrayList<>();
			collect.forEach(x->{
				String s = JSONObject.toJSONString(x);
				data.add(JSONObject.parseObject(s));
			});
			String excelUrl = EasyExcelUtil.exportDetailLeave("导出项目.xlsx", data, header, keys);
			result.setUrl(excelUrl);
			return result;
		}
		result.setUrl(url);
		return result;
	}

配置类:

public class EasyExcelUtil {





    public static String createExcel(List<TenantTitleParamReq> titleParamReqs, List<JSONObject>data,String  name) {
        List<String>keys=new ArrayList<>();
        List<String>header=new ArrayList<>();
        titleParamReqs.forEach(x->{
            keys.add(x.getAttributeValue());
            header.add(x.getName());
        });

        return  exportDetailLeave(name, data, header, keys);
    }

    public static String createExcel(List<TenantTitleParamReq> titleParamReqs, List<JSONObject>data,String  name,Map<Integer,String>h) {
        List<String>keys=new ArrayList<>();
        List<String>header=new ArrayList<>();
        titleParamReqs.forEach(x->{
            keys.add(x.getAttributeValue());
            header.add(x.getName());
        });

        return  exportDetailLeave(name, data, header, keys,h);
    }


    public static String exportDetailLeave(String sheetName, List<JSONObject>data, List<String>header, List<String>keys){
        return exportDetailLeave( sheetName, data, header, keys,new HashMap<>());
    }

    public static String exportDetailLeave(String sheetName, List<JSONObject>data, List<String>header, List<String>keys,Map<Integer,String>h)  {


      //  String[] header = {"宿舍","姓名","学号","状态","归寝时间"};
        String fileName = StringUtils.isEmpty(sheetName)?"sheet1":sheetName;
        // 标题样式
        WriteCellStyle headWriteCellStyle = EasyExcelUtil.getHeadStyle();
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, new WriteCellStyle());
        ByteArrayOutputStream byteArrayOutputStream=new ByteArrayOutputStream();
        EasyExcel.write(byteArrayOutputStream)

                //设置默认样式及写入头信息开始的行数
                .useDefaultStyle(true).relativeHeadRowIndex(0)
                // 表头、内容样式设置
              // .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new MyMatchColumnWidthStyleStrategy())
                .sheet(fileName)

                // 这里放入动态头
                .head(head(header,fileName,h))
                // 当然这里数据也可以用 List<List<String>> 去传入
                .doWrite(detail(data,keys));
        byte[] bytes = byteArrayOutputStream.toByteArray();
        return AliyunOssUtil.uploadFileAliOssByName(bytes,"" ,  sheetName+".xlsx");


    }

    private static List<List<Object>> detail(List<JSONObject> mapList,List<String>keys) {
        List<List<Object>> list =  new ArrayList<List<Object>>();

        for (JSONObject jsonObject : mapList) {
            List<Object> objectList = new ArrayList<>();
            keys.forEach(x->{
                objectList.add(jsonObject.get(x));
            });
            list.add(objectList);
        }



        return list;
    }






    private static List<List<String>> head(List<String> header, String bigTitle,Map<Integer,String> startHead) {
        List<String> head0 = null;
        List<List<String>> list = new LinkedList<List<String>>();
        Integer i =0;
        for (String h : header) {
            head0 = new LinkedList<>();
            i++;
            String s = startHead.get(i);
            if (!StringUtils.isEmpty(s)){
                head0.add(s);
            }
            head0.add(h);
            list.add(head0);
        }
        return list;
    }


    private static WriteCellStyle getHeadStyle(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("黑体");//设置字体名字
        headWriteFont.setFontHeightInPoints((short)15);//设置字体大小
        headWriteFont.setBold(true);//字体加粗
        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;
        // 样式
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;
        headWriteCellStyle.setWrapped(true);  //设置自动换行;
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;
        headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适

        return headWriteCellStyle;
    }

}