java easyExcel动态导出字段

需求根据实体+动态字段导出数据

思路

1 把实体转成JSON作为接收数据的值
2 把查询的数据转成jsonobject 通过上面的值对应放入List

代码实现

1. 引入包

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.2.6</version>
		</dependency>
		<dependency>
			<groupId>com.belerweb</groupId>
			<artifactId>pinyin4j</artifactId>
			<version>2.5.0</version>
		</dependency>
        <dependency>
            <groupId>com.vaadin.external.google</groupId>
            <artifactId>android-json</artifactId>
            <version>0.0.20131108.vaadin1</version>
            <scope>compile</scope>
        </dependency>

2. 实体

public class ExportEntity {
    private String officeId;
    private String taskId;
    //扩展字段
    private String extend;

    public String getExtend() {
        return extend;
    }

    public void setExtend(String extend) {
        this.extend = extend;
    }

    public String getOfficeId() {
        return officeId;
    }

    public void setOfficeId(String officeId) {
        this.officeId = officeId;
    }

    public String getTaskId() {
        return taskId;
    }

    public void setTaskId(String taskId) {
        this.taskId = taskId;
    }
}

3. 实现

public class Export{
public void exportExcel() throws JSONException {
        String json = "{\"序号\":\"1\",\"编号\":\"0001\"}";
        //可以通过前端进行参数的传入来确定表头列的数量
        String filedNames = "组织id,任务id";
        String filedCodes = "officeId,taskId";
        List<ExportEntity> callRecordEntities = getExportEntity();

        List<JSONObject> exportCallRecord = Lists.newArrayList();
        //存放头信息
        Map<String, String> titleMap = new TreeMap();
        Boolean flag = true;
        for(ExportEntity entity:callRecordEntities){
            JSONObject jsonObject = new JSONObject(JsonUtils.objectToJson(entity));
            //1 组装表达头数据  TODO
            if(flag){
                entity.setExtend("{\"序号\":\"1\",\"编号\":\"0001\"}");
                flag =false;
            }else {
                entity.setExtend("{\"编号\":\"0001\",\"上上迁\":\"666\"}");
                flag = true;
            }
            //组装展示数据
            JSONObject hand = new JSONObject(entity.getExtend());
            Iterator<String> sIterator = hand.keys();
            while(sIterator.hasNext()){
                String key = sIterator.next();
                jsonObject.put(ReflectUtils.getPinyin(key),hand.getString(key));
                titleMap.put(ReflectUtils.getPinyin(key),key);
            }
            exportCallRecord.add(jsonObject);
            log.info("OBJECT : " + jsonObject);
        }
        for (Map.Entry<String, String> entry : titleMap.entrySet()) {
            filedNames=filedNames+","+ entry.getValue();
            filedCodes=filedCodes+","+ entry.getKey();
        }
        /**Excel头，参数格式：姓名,生日*/
        String[] head = filedNames.split(",");
        List<String> headList = new ArrayList<>(Arrays.asList(head));

        /**Excel表格内容，参数格式：officeId,taskId*/
        File file1 = new File("f:/data");
        if (!file1.exists()) {
            file1.mkdir();
        }
        String[] file = filedCodes.split(",");
        List<String> fileList = new ArrayList<>(Arrays.asList(file));

        ExcelWriter excelWriter = null;
        excelWriter = EasyExcel.write(file1 + "/" + new Date().getTime()+".xls").build();
        //文件导出为.xls文件
        WriteSheet writeSheet0 = EasyExcelUtils.noModelWrite("测试例子", 0,  headList);
        WriteSheet writeSheet1 = EasyExcelUtils.noModelWrite("测试例子1", 1, headList);

        excelWriter.write(EasyExcelUtils.dataListJson(exportCallRecord, fileList), writeSheet0);
        excelWriter.write(EasyExcelUtils.dataListJson(exportCallRecord, fileList), writeSheet1);
        excelWriter.finish();
         }
    private List<ExportEntity> getExportEntity() {

        List<ExportEntity> list = Lists.newArrayList();
        for (int i = 0; i < 10; i++) {
            ExportEntity exportEntity = new ExportEntity();
            exportEntity.setOfficeId("officeId"+i);
            exportEntity.setTaskId("taskId"+i);
            list.add(exportEntity);
        }
        return list;
    }
}

4. utils

public class EasyExcelUtils {
    /**
     * @param sheetNo  页
     * @param sheetName sheet名
     * @param headList  传入的Excel头（例如：姓名，生日）
     */

    //在导出时注册registerWriteHandler(new CustomCellWriteHandler())
    public static WriteSheet noModelWrite( String sheetName,Integer sheetNo, List<String> headList) {
        //生成sheet
        return EasyExcel.writerSheet(sheetNo, sheetName).head(head(headList)).registerWriteHandler(new CustomCellWriteHandler()).build();

    }
/**
     * 设置表格信息
     *
     * @param listIn 查询出的数据
     * @param fileList 需要显示的字段
     * @return
     */
    public static List<List<Object>> dataListJson(List listIn, List<String> fileList) {
        List<JSONObject> dataList = listIn;
        List<List<Object>> list = new ArrayList<>();
        try {
            for (JSONObject jSONObject : dataList) {
                List<Object> data = new ArrayList<>();
                for (String fieldName : fileList) {
                    Object o = "";
                            try{
                                o =  jSONObject.get(fieldName) == null ? "" : jSONObject.get(fieldName);
                            }catch (Exception e){
                               log.info("dynimicDateIsNull");
                            };
                    data.add(o);
                }
                list.add(data);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

}