EsayExcel-合并表头-并且下拉框动态从数据库获取

一、什么是EasyExcel

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。该项目实现了列表数据的导入，分页列表导出，支持自定义格式，支持模版以及模板和列表的混合导出，小巧、简易、高性能。

 1.pom.xml 

<!--测试依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>2.3.4.RELEASE</version>
        </dependency>
<!--Lombok依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
<!--easyexcel依赖-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>

 

以下是实战的导出模板的样式：

 导出的模板Excel拦截器：

@Data
public class CustomTitleWriteHandler implements SheetWriteHandler {


    private final Map<Integer, ExcelSelectedResolve> selectedMap;
    /**
     * 标题
     */
    private final String fileName;

    /**
     * DTO数据类型
     */
    private final Class<?> elementType;

    public CustomTitleWriteHandler(Map<Integer, ExcelSelectedResolve> selectedMap, String fileName, Class<?> elementType) {
        this.selectedMap = selectedMap;
        this.fileName = fileName;
        this.elementType = elementType;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
         // 获取clazz所有的属性
        Field[] fields = this.elementType.getDeclaredFields();
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 2100);
        Cell cell = row1.createCell(0);
        //设置标题
        cell.setCellValue(fileName);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setAlignment(HorizontalAlignment.LEFT);//水平左对齐
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setWrapText(true);//自动换行
        Font font = workbook.createFont();
        //font.setBold(true); 字体加粗
        font.setFontHeight((short) 250);
        font.setFontName("楷体");
        font.setColor(IndexedColors.RED.getIndex());
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, fields.length-2));
        //自定义注解下拉框配置
        DataValidationHelper helper = sheet.getDataValidationHelper();
        if(selectedMap!=null&&!selectedMap.isEmpty()){
            selectedMap.forEach((k, v) -> {
                // 设置下拉列表的行： 首行，末行，首列，末列
                CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
                // 设置下拉列表的值
                DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
                // 设置约束
                DataValidation validation = helper.createValidation(constraint, rangeList);
                // 阻止输入非下拉选项的值
                validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
                validation.createErrorBox("提示", "请输入下拉选项中的内容");
                sheet.addValidationData(validation);
            });
        }
    }

}

Excel工具类：

package com.segi.uhomecp.cmsk.ibatch.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.segi.uhomecp.cmsk.ibatch.handler.CustomTitleWriteHandler;
import com.segi.uhomecp.cmsk.ibatch.handler.ExcelSelected;
import com.segi.uhomecp.cmsk.ibatch.handler.ExcelSelectedResolve;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * 高性能处理Excel工具类
 *
 */
public class EasyExcelUtil {
    /**
     * 使用 模型 来读取Excel
     *
     * @param inputStream Excel的输入流
     * @param clazz       模型的类
     * @return 返回 模型 的列表
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class<T> clazz) {
        ModelExcelListener<T> listener = new ModelExcelListener<T>();
        EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
        return listener.getDatas();
    }

    /**
     * 使用 模型 来导出到WEB
     *
     * @param response      web的响应
     * @param data         要写入的以 模型 为单位的数据
     * @param fileName     配置Excel的表名
     * @param sheetName    配置Excel的页签名
     * @param clazz        模型的类
     * @throws IOException
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> data, Class<T> clazz, String fileName, String sheetName) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 使用 模型 来写入Excel
     * <br/>注意，如果是web输出流，需要设置头
     *
     * @param outputStream Excel的输出流
     * @param data         要写入的以 模型 为单位的数据
     * @param sheetName    配置Excel的表名字
     * @param clazz        模型的类
     */
    public static <T> void writeExcel(OutputStream outputStream, List<T> data, Class<T> clazz, String sheetName) {
        EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(data);
    }

    /**
     * 模型 解析监听器
     */
    private static class ModelExcelListener<T> extends AnalysisEventListener<T> {
        private List<T> datas = new ArrayList<>();

        @Override
        public void invoke(T object, AnalysisContext context) {
            datas.add(object);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
        }

        public List<T> getDatas() {
            return datas;
        }
    }

    /**
     * 将 Map对象转化为JavaBean
     * @param map
     * @param T
     * @return
     * @throws Exception
     */
    public static <T> T convertMap2Bean(Map<String, Object> map, Class<T> T)
            throws Exception
    {
        if (map == null || map.size() == 0)
        {
            return null;
        }
        //获取map中所有的key值，全部更新成大写，添加到keys集合中,与mybatis中驼峰命名匹配
        Object mvalue = null;
        Map<String, Object> newMap = new HashMap<>();
        Iterator<Map.Entry<String, Object>> it = map.entrySet().iterator();
        while(it.hasNext()){
            String key = it.next().getKey();
            mvalue = map.get(key);
          /*  if (key.indexOf(CharacterConstant.UNDERLINE) != -1)
            {
                key = key.replaceAll(CharacterConstant.UNDERLINE, "");
            }*/
            newMap.put(key.toUpperCase(Locale.US), mvalue);
        }

        BeanInfo beanInfo = Introspector.getBeanInfo(T);
        T bean = T.newInstance();
        PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
        for (int i = 0, n = propertyDescriptors.length; i < n; i++)
        {
            PropertyDescriptor descriptor = propertyDescriptors[i];
            String propertyName = descriptor.getName();
            String upperPropertyName = propertyName.toUpperCase();

            if (newMap.keySet().contains(upperPropertyName))
            {
                Object value = newMap.get(upperPropertyName);
                //这个方法不会报参数类型不匹配的错误。
                BeanUtils.copyProperty(bean, propertyName, value);
            }
        }
        return bean;
    }

    /**
     * 设置样式
     */
    public static HorizontalCellStyleStrategy getCellStyle(){
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容垂直居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置内容水平居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
    }


    /**
     * Excel导出模板write
     * @param response
     * @param fileName sheet标题
     * @param exegesis 注释
     * @param name class类名
     * @param dataList 数据列表
     * @param id 统一客户是一级物业ID，项目客户是项目ID
     * @throws Exception
     */
    public static <T> void EasyExcelInit(HttpServletResponse response, String fileName, String exegesis, Class<T> name, List dataList,String id) throws Exception{
        //动态获取下拉属性，将动态数据下发到Excel
        Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(name,id);
        String fileName2 = URLEncoder.encode(fileName + "模板.xlsx", "utf-8");
        response.setHeader("Content-disposition", "attachment; filename=" + fileName2);
        response.setContentType("application/vnd.ms-excel");
        EasyExcel.write(response.getOutputStream(), name)
                .inMemory(true) // 富文本
                .autoCloseStream(true)//自动关闭流
                .relativeHeadRowIndex(1)//空格一行。用来添加注释
                .useDefaultStyle(false)
                .sheet(fileName)//设置sheet页的名称
                .registerWriteHandler(new CustomTitleWriteHandler(selectedMap,exegesis,name))//合并+添加样式
                .doWrite(dataList);
    }


    /**
     * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
     * @param head 导出的表头信息和配置
     * @param sheetNo sheet索引
     * @param sheetName sheet名称
     * @param <T> 泛型
     * @return sheet页
     */
    public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
        Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head,null);
        return EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
                .registerWriteHandler(new CustomTitleWriteHandler(selectedMap,null,null))
                .build();
    }

    /**
     * 解析表头类中的下拉注解
     * @param head 表头类
     * @param <T> 泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head,String id) {
        Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();

        // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        Field[] fields = head.getDeclaredFields();
        for (int i = 0; i < fields.length; i++){
            Field field = fields[i];
            // 解析注解信息
            ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (selected != null) {
                ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
                String[] source = excelSelectedResolve.resolveSelectedSource(selected,id);
                if (source != null && source.length > 0){
                    excelSelectedResolve.setSource(source);
                    excelSelectedResolve.setFirstRow(selected.firstRow());
                    excelSelectedResolve.setLastRow(selected.lastRow());
                    if (property != null && property.index() >= 0){
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }

        return selectedMap;
    }
}

    @RequestMapping(value = "/downLoadTemplate.json", method = {RequestMethod.POST})
    public void downLoadTemplate(@RequestBody  Map<String, Object> reqMap, HttpServletResponse response) throws IOException {
        if(reqMap.get("type")==null&&"".equals(reqMap.get("type"))){
            throw new RuntimeException("模板类型不能为空！");
        }
        String type=reqMap.get("type").toString();
        String topOrganId=null;
        String organId=null;
        if("1".contains(type)){
            if(reqMap.get("topOrganId")==null&&"".equals(reqMap.get("topOrganId"))){
                throw new RuntimeException("一级物业不能为空！");
            }
             topOrganId=reqMap.get("topOrganId").toString();
        }else if("2".contains(type)||"3".equals(type)){
            if(reqMap.get("organId")==null&&"".equals(reqMap.get("organId"))){
                throw new RuntimeException("项目机构不能为空！");
            }
            organId=reqMap.get("organId").toString();
        }
        try {
            if("1".equals(type)){
                String fileName = "模板一";
                EasyExcelInit(response,fileName, IbatchUtil.exegesis, CustMainAllTempleExcelDto.class,null,topOrganId);
            }else if("2".equals(type)){
                String fileName ="模板二";
                EasyExcelInit(response,fileName,IbatchUtil.exegesis2, CustomerTempleExcelDto.class,null,organId);
            }else if("3".equals(type)){
                String fileName ="模板三";
                List<CustomerInvoiceExcelDto>  customerInvoiceExcelDtoList=commonService.queryCustomerListByOrganId(organId);
                EasyExcelInit(response,fileName,IbatchUtil.exegesis3, CustomerInvoiceExcelDto.class,customerInvoiceExcelDtoList,organId);
            }else if("4".equals(type)){
                String fileName = "模板四";
                EasyExcelInit(response,fileName, IbatchUtil.exegesis4, CustMainInvoiceExcelDto.class,null,topOrganId);
            }
        } catch (Exception e) {
            Map<String, String> resMap = Maps.newHashMap();
            logger.error("下载模板异常:", e);
            resMap.put("code", "-1");
            resMap.put("message", "下载模板异常!"+e.getMessage());
            response.setHeader("errorMsg", URLEncoder.encode("下载模板异常!", "utf-8"));
            response.reset();
            response.setContentType("application/json;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.getWriter().println(JSON.toJSONString(resMap));
        }
    }

fillForegroundColor：前景色，类型是 short，使用的org.apache.poi.ss.usermodel.IndexedColors枚举的 idx 值（数字）。（两个类型不一致，一个是 short，一个是 int，没有办法直接引用。）

  实体类（需要写入excel的类）模板类：

@ContentRowHeight(15)
@HeadRowHeight(15)
@Data
//表头居中
@ContentStyle (horizontalAlignment= HorizontalAlignment.CENTER)
//实线表格
@HeadStyle(borderTop = BorderStyle.THIN,borderBottom =BorderStyle.THIN,borderLeft = BorderStyle.THIN,borderRight = BorderStyle.THIN)
public class CustMainAllTempleExcelDto implements Serializable {
    private static final long serialVersionUID = 1L;

    @ColumnWidth(13)
    @ExcelProperty(value = {"客户信息","客户性质"}, index = 0)
    @ExcelSelected(sourceClass = CustPubTypeSelected.class)
    //表头标黄
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor=13)
    @HeadFontStyle(color =10)
    private String custPubTypeName;

    @ColumnWidth(13)
    //合并表头，相同合并
    @ExcelProperty(value = {"客户信息","客户名称"}, index = 1)
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor=13)
    @HeadFontStyle(color =10)
    private String custName;

    ......//其他字段

    @ColumnWidth(20)
    @ExcelProperty(value = {"开票信息", "纳税人名称"}, index = 12)
    //表头标黄
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND,fillForegroundColor=13)
    //表头内容标红
    @HeadFontStyle(color =10)
    private String taxpayerName;

    @ColumnWidth(20)
    @ExcelProperty(value = {"开票信息", "纳税人税号"}, index = 13)
    private String taxpayerNum;
}

 下拉数据动态获取

/**
 * 下拉框-动态字段-自定义证件类型下拉数据
 */
public class CustPubTypeSelected implements ExcelDynamicSelect {

    @Override
    public String[] getSource(String id) {
        List<AttrDictDto> dictCustList = CommonDicts.getAttrByCode(Integer.valueOf(id), "CUST_PUB_TYPE");
        List<String> collect = dictCustList.stream().map(AttrDictDto::getDictName).collect(Collectors.toList());
        return collect.toArray(new String[dictCustList.size()]);
    }
}