java操作excel:基于easypoi的动态列导出

基于easypoi的动态列导出

最近有一个需求，需要导出excel列表，但是列表的列名是不确定的只能先用value1、value2、value3…这样来表示，具体的列名存在另外一张竖表里面,每一种物品的字段名都不一样，这里通过反射+注解实现动态列的导出，代码如下

• 实体类

package com.ecity.hbp.service.entity.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

/**
 * <p>
 * 
 * </p>
 *
 * @author Shen_hs
 * @since 2021-3-18
 */
@Data
@EqualsAndHashCode(callSuper = false)
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("ALARM_OBJ")
public class AlarmObj implements Serializable {

    @TableId("GID")
    @Excel(name = "主键",orderNum = "0",width = 20,isColumnHidden=false)
    private Long gid;

    @TableField("ALARM_ID")
    @Excel(name = "外键",orderNum = "1",width = 20,isColumnHidden=false)
    private Long alarmId;

    @TableField("STATUS")
    @Excel(name = "状态",orderNum = "2",width = 20,isColumnHidden=false)
    private String status;

    @TableField("CREATETIME")
    @Excel(name = "创建时间",orderNum = "3",width = 20,isColumnHidden=false,format = "yyyy-MM-dd HH:mm:ss")
    private Date createtime;

    @TableField("VALUE1")
    @Excel(name = "VALUE1",orderNum = "4",width = 20,isColumnHidden=false)
    private String value1;

    @TableField("VALUE2")
    @Excel(name = "VALUE2",orderNum = "5",width = 20,isColumnHidden=false)
    private String value2;

    @TableField("VALUE3")
    @Excel(name = "VALUE3",orderNum = "6",width = 20,isColumnHidden=false)
    private String value3;

    @TableField("VALUE4")
    @Excel(name = "VALUE4",orderNum = "7",width = 20,isColumnHidden=false)
    private String value4;

    @TableField("VALUE5")
    @Excel(name = "VALUE5",orderNum = "8",width = 20,isColumnHidden=false)
    private String value5;

    @TableField("VALUE6")
    @Excel(name = "VALUE6",orderNum = "9",width = 20,isColumnHidden=false)
    private String value6;

    @TableField("VALUE7")
    @Excel(name = "VALUE7",orderNum = "10",width = 20,isColumnHidden=false)
    private String value7;

    @TableField("VALUE8")
    @Excel(name = "VALUE8",orderNum = "11",width = 20,isColumnHidden=false)
    private String value8;

    @TableField("VALUE9")
    @Excel(name = "VALUE9",orderNum = "12",width = 20,isColumnHidden=false)
    private String value9;

    @TableField("VALUE10")
    @Excel(name = "VALUE10",orderNum = "13",width = 20,isColumnHidden=false)
    private String value10;

    @TableField("VALUE11")
    @Excel(name = "VALUE11",orderNum = "14",width = 20,isColumnHidden=false)
    private String value11;

    @TableField("VALUE12")
    @Excel(name = "VALUE12",orderNum = "15",width = 20,isColumnHidden=false)
    private String value12;

    @TableField("VALUE13")
    @Excel(name = "VALUE13",orderNum = "16",width = 20,isColumnHidden=false)
    private String value13;

    @TableField("VALUE14")
    @Excel(name = "VALUE14",orderNum = "17",width = 20,isColumnHidden=false)
    private String value14;

    @TableField("VALUE15")
    @Excel(name = "VALUE15",orderNum = "18",width = 20,isColumnHidden=false)
    private String value15;

    @TableField("VALUE16")
    @Excel(name = "VALUE16",orderNum = "19",width = 20,isColumnHidden=false)
    private String value16;

    @TableField("VALUE17")
    @Excel(name = "VALUE17",orderNum = "20",width = 20,isColumnHidden=false)
    private String value17;

    @TableField("VALUE18")
    @Excel(name = "VALUE18",orderNum = "21",width = 20,isColumnHidden=false)
    private String value18;

    @TableField("VALUE19")
    @Excel(name = "VALUE19",orderNum = "22",width = 20,isColumnHidden=false)
    private String value19;

    @TableField("VALUE20")
    @Excel(name = "VALUE20",orderNum = "23",width = 20,isColumnHidden=false)
    private String value20;

    @TableField("VALUE21")
    @Excel(name = "VALUE21",orderNum = "24",width = 20,isColumnHidden=false)
    private String value21;

    @TableField("VALUE22")
    @Excel(name = "VALUE22",orderNum = "25",width = 20,isColumnHidden=false)
    private String value22;

    @TableField("VALUE23")
    @Excel(name = "VALUE23",orderNum = "26",width = 20,isColumnHidden=false)
    private String value23;

    @TableField("VALUE24")
    @Excel(name = "VALUE24",orderNum = "27",width = 20,isColumnHidden=false)
    private String value24;

    @TableField("VALUE25")
    @Excel(name = "VALUE25",orderNum = "28",width = 20,isColumnHidden=false)
    private String value25;

    @TableField("VALUE26")
    @Excel(name = "VALUE26",orderNum = "29",width = 20,isColumnHidden=false)
    private String value26;

    @TableField("VALUE27")
    @Excel(name = "VALUE27",orderNum = "30",width = 20,isColumnHidden=false)
    private String value27;

    @TableField("VALUE28")
    @Excel(name = "VALUE28",orderNum = "31",width = 20,isColumnHidden=false)
    private String value28;

    @TableField("VALUE29")
    @Excel(name = "VALUE29",orderNum = "32",width = 20,isColumnHidden=false)
    private String value29;

    @TableField("VALUE30")
    @Excel(name = "VALUE30",orderNum = "33",width = 20,isColumnHidden=false)
    private String value30;

    @TableField("VALUE31")
    @Excel(name = "VALUE31",orderNum = "34",width = 20,isColumnHidden=false)
    private String value31;

    @TableField("VALUE32")
    @Excel(name = "VALUE32",orderNum = "35",width = 20,isColumnHidden=false)
    private String value32;

    @TableField("VALUE33")
    @Excel(name = "VALUE33",orderNum = "36",width = 20,isColumnHidden=false)
    private String value33;

    @TableField("VALUE34")
    @Excel(name = "VALUE34",orderNum = "37",width = 20,isColumnHidden=false)
    private String value34;

    @TableField("VALUE35")
    @Excel(name = "VALUE35",orderNum = "38",width = 20,isColumnHidden=false)
    private String value35;

    @TableField("VALUE36")
    @Excel(name = "VALUE36",orderNum = "39",width = 20,isColumnHidden=false)
    private String value36;

    @TableField("VALUE37")
    @Excel(name = "VALUE37",orderNum = "40",width = 20,isColumnHidden=false)
    private String value37;

    @TableField("VALUE38")
    @Excel(name = "VALUE38",orderNum = "41",width = 20,isColumnHidden=false)
    private String value38;

    @TableField("VALUE39")
    @Excel(name = "VALUE39",orderNum = "42",width = 20,isColumnHidden=false)
    private String value39;

    @TableField("VALUE40")
    @Excel(name = "VALUE40",orderNum = "43",width = 20,isColumnHidden=false)
    private String value40;

    @TableField("VALUE41")
    @Excel(name = "VALUE41",orderNum = "44",width = 20,isColumnHidden=false)
    private String value41;

    @TableField("VALUE42")
    @Excel(name = "VALUE42",orderNum = "45",width = 20,isColumnHidden=false)
    private String value42;

    @TableField("VALUE43")
    @Excel(name = "VALUE43",orderNum = "46",width = 20,isColumnHidden=false)
    private String value43;

    @TableField("VALUE44")
    @Excel(name = "VALUE44",orderNum = "47",width = 20,isColumnHidden=false)
    private String value44;

    @TableField("VALUE45")
    @Excel(name = "VALUE45",orderNum = "48",width = 20,isColumnHidden=false)
    private String value45;

    @TableField("VALUE46")
    @Excel(name = "VALUE46",orderNum = "49",width = 20,isColumnHidden=false)
    private String value46;

    @TableField("VALUE47")
    @Excel(name = "VALUE47",orderNum = "50",width = 20,isColumnHidden=false)
    private String value47;

    @TableField("VALUE48")
    @Excel(name = "VALUE48",orderNum = "51",width = 20,isColumnHidden=false)
    private String value48;

    @TableField("VALUE49")
    @Excel(name = "VALUE49",orderNum = "52",width = 20,isColumnHidden=false)
    private String value49;

    @TableField("VALUE50")
    @Excel(name = "VALUE50",orderNum = "53",width = 20,isColumnHidden=false)
    private String value50;


}

说明:这里的@Excel注解是easypoi自带的,name不确定先用value代替，后面通过反射注入具体的列名;isColumnHidden是否隐藏字段，默认false全部导出，后面通过反射改变值，空值列不让导出

• 业务逻辑代码

package com.ecity.hbp.service.service.impl;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ecity.hbp.service.constant.MediaType;
import com.ecity.hbp.service.entity.pojo.AlarmObj;
import com.ecity.hbp.service.entity.pojo.MtModelFields;
import com.ecity.hbp.service.mapper.AlarmObjMapper;
import com.ecity.hbp.service.service.AlarmObjService;
import com.ecity.hbp.service.service.MtModelFieldsService;
import com.ecity.hbp.service.util.ExcelConvert;
import com.ecity.hbp.service.util.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.time.Instant;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author Shen_hs
 * @since 2021-03-18
 */
@Service
public class AlarmObjServiceImpl extends ServiceImpl<AlarmObjMapper, AlarmObj> implements AlarmObjService {


    public static final String TEMP_PATH = System.getProperty("user.dir") + File.separator + "temp" + File.separator + "export";

    @Autowired
    private MtModelFieldsService mtModelFieldsService;

    @Override
    public void export(String gid, String alarmId, String status, HttpServletResponse response, boolean isDelete) {
        File file = new File(TEMP_PATH);
        if (!file.exists()) file.mkdirs();
        QueryWrapper<AlarmObj> wrapper = new QueryWrapper<>();
        wrapper.eq(StringUtils.isNotEmpty(gid.trim()),"GID", StringUtils.isEmpty(gid.trim()) ? "" : Long.valueOf(gid))
                .eq(StringUtils.isNotEmpty(alarmId.trim()),"ALARM_ID",StringUtils.isEmpty(alarmId.trim()) ? "" : Long.valueOf(alarmId))
                .eq(StringUtils.isNotEmpty(status),"STATUS",status);
        List<AlarmObj> alarmObjs = baseMapper.selectList(wrapper);
        if (alarmObjs.isEmpty()) return;
        //字段表
        List<MtModelFields> modelFields = mtModelFieldsService.getBaseMapper().selectList(new QueryWrapper<MtModelFields>().eq("PARENT_MODEL", Long.valueOf(alarmId)));
        //将数据库表数据映射成value->name，这样的结构方便注入
        Map<String, String> fieldMap = modelFields.stream().collect(Collectors.toMap(MtModelFields::getAlarmColumn, MtModelFields::getFieldAlias, (n1, n2) -> n2));
        Workbook workbook = null;
        for (AlarmObj alarmObj : alarmObjs) {
            try{
                 ExcelConvert.convert(alarmObj,fieldMap);
            } catch (Exception e) {
                throw new ExcelExportException(ExcelExportEnum.TEMPLATE_ERROR,e); //模板导出有误
            }
        }
        String fileName = Instant.now().getEpochSecond() + ".xls";
        try(FileOutputStream fos = new FileOutputStream(TEMP_PATH + File.separator + fileName)
        ){
            workbook = ExcelExportUtil.exportExcel(new ExportParams("数据列表", "数据列表"), AlarmObj.class, alarmObjs);
            //将Excel导出到指定位置
            workbook.write(fos);
            response.setContentType(MediaType.APPLICATION_EXCEL_VALUE);
            MediaType.setAttachmentResponseHeader(response,fileName); //设置请求头
            workbook.write(response.getOutputStream());
            if (isDelete) {
                FileUtils.deleteFile(TEMP_PATH + File.separator + fileName);
            }
        } catch (Exception e) {
            throw new RuntimeException("####导出excel失败!!!",e);
        }finally {
            IOUtils.closeQuietly(workbook);
        }
    }
}

说明: Map<String, String> fieldMap = modelFields.stream().collect(Collectors.toMap(MtModelFields::getAlarmColumn, MtModelFields::getFieldAlias, (n1, n2) -> n2)); 将查出来的数据结构重组为value->name这样的结构，方便对应实体类@excel的name属性进行注入

• 反射操作，直接属性注入

package com.ecity.hbp.service.util;

import cn.afterturn.easypoi.excel.annotation.Excel;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.util.Map;

/**
 * <p>
 *   操作注解
 * </p>
 *
 * @author Shen_hs
 * @since 2021-03-04
 */
public class ExcelConvert {
    public static <T> void convert(T obj,final Map<String,String> fieldMap) throws NoSuchFieldException, IllegalAccessException {
        Class<T> clazz = (Class<T>) obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            Excel annotation = field.getAnnotation(Excel.class);

            if (annotation == null){
                continue;
            }
            String alias = fieldMap.get(annotation.name()); //别名
            if (alias == null){
                continue;
            }
            setAnnoName(annotation,alias);
        }
        setAnnoCoulmnHidden(obj); //null值列不导出
    }

    private static <T> void setAnnoCoulmnHidden(T obj) throws NoSuchFieldException, IllegalAccessException{
        Class<T> clazz = (Class<T>) obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            Object o = field.get(obj);
            if (null == o) {
                Excel annotation = field.getAnnotation(Excel.class);
                if (annotation == null) {
                    continue;
                }
                //获取 foo 这个代理实例所持有的 InvocationHandler
                InvocationHandler h = Proxy.getInvocationHandler(annotation);
                // 获取 AnnotationInvocationHandler 的 memberValues 字段
                Field hField = h.getClass().getDeclaredField("memberValues");
                // 因为这个字段事 private final 修饰，所以要打开权限
                hField.setAccessible(true);
                // 获取 memberValues
                Map memberValues = (Map) hField.get(h);
                // 修改 value 属性值
                memberValues.put("isColumnHidden", true);
            }
        }

    }

    public static void setAnnoName(Excel e,String s) throws NoSuchFieldException, IllegalAccessException {
        //获取 foo 这个代理实例所持有的 InvocationHandler
        InvocationHandler h = Proxy.getInvocationHandler(e);
        // 获取 AnnotationInvocationHandler 的 memberValues 字段
        Field hField = h.getClass().getDeclaredField("memberValues");
        // 因为这个字段事 private final 修饰，所以要打开权限
        hField.setAccessible(true);
        // 获取 memberValues
        Map memberValues = (Map) hField.get(h);
        // 修改 value 属性值
        memberValues.put("name", s);

    }

}

说明:setAnnoName这个方法操作name属性完成注入,setAnnoCoulmnHidden这个方法操作isColumnHidden属性，空值列不导出,这样实现了列的动态导出