POI实现基于注解的Excel导出，可实现合并相同行

在后台管理的业务中导出Excel是很常用的业务，为了提高复用性，采用了注解的模式，记录下。

如果对自定义注解不了解可以先看这里（转）：https://www.jianshu.com/p/b560b30726d4

实现思路都在注释中

依赖：

<!-- poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

自定义注解类代码：

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Excel导出配置
 * @author majj
 * @version 1.0.0
 * @date 2018/5/7 10:25
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelConfig {
    /**
     * 导入时，对应数据库的字段 主要是用户区分每个字段，不能有annotation重名的
     * 导出时的列名 导出排序跟定义了annotation的字段的顺序有关
     * @return 列名
     */
    public String exportName();
    /**
     * 导出时在excel中每个列的宽 单位为字符，一个汉字=2个字符
     * 如以列名列内容中较合适的长度 例如姓名列6 【姓名一般三个字】 性别列4【男女占1，但是列标题两个汉字】
     * 限制1-255
     * @return 列宽
     */
    public int exportFieldWidth() default 20;
    /**
     * 导出时是否进行字段转换 例如 性别用int存储，导出时可能转换为男，女
     * 若是sign为true,则需要在pojo中加入一个方法 get字段名Convert()
     * 例如，字段sex ，需要加入 public String getSexConvert() 返回值为string
     * 若是sign为false,则不必管
     * @return 导出是否字段转换
     */
    public boolean exportConvertSign() default false;

    /**
     * 导入数据是否需要转化 及 对已有的excel，是否需要将字段转为对应的数据
     * 若是sign为true,则需要在pojo中加入 void set字段名Convert(String text)
     * @return 导入是否字段转换
     */
    public boolean importConvertSign() default false;
    /**
     * 是否求和
     * @return boolean
     */
    public boolean isSum() default false;
    /**
     * 小数保留位数，默认不保留
     * @return int
     */
    public int scale() default 0;

    /**
     * 是否合并相同行，在同一列中存在相同的值时是否合并行，默认不合并
     * @return
     */
    public boolean isMerge() default false;

    /**
     * 合并判断字段，如果合并相同行，判断值是否相等的字段，默认为当前字段。
     * 如：每个人的姓名不是唯一的，但身份证是唯一的，当出现姓名相同是，可通过身份证判断是否合并
     * @return
     */
    public String mergeFlag() default "";
}

导出实体类：

import com.tongyou.entity.interfaces.ExcelConfig;

/**
 * @author berberberetta
 * @version 1.0.0
 * @date 2018/5/7 11:16
 */
public class TestDTO {

    private Integer id;

    @ExcelConfig(exportName = "姓名", isMerge = true, mergeFlag = "id")
    private String name;
    @ExcelConfig(exportName = "性别", exportConvertSign = true)
    private Integer sex;
    @ExcelConfig(exportName = "学科")
    private String subject;
    @ExcelConfig(exportName = "分数")
    private Integer score;

    public TestDTO() {
    }

    public TestDTO(Integer id, String name, Integer sex, String subject, Integer score) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.subject = subject;
        this.score = score;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public String getSubject() {
        return subject;
    }

    public void setSubject(String subject) {
        this.subject = subject;
    }

    public Integer getScore() {
        return score;
    }

    public void setScore(Integer score) {
        this.score = score;
    }

    public String getSexConvert() {
        switch (sex) {
            case 0:
                return "未知";
            case 1:
                return "男";
            case 2:
                return "女";
            default:
                return "未知";
        }
    }

}

导出工具类：

import com.tongyou.entity.interfaces.ExcelConfig;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

/**
 * @author majj
 * @version 1.0.0
 * @date 2018/5/7 9:16
 */
public class DownloadUtil {

    private static final Logger LOG = LoggerFactory.getLogger(DownloadUtil.class);

    /**
     * 生成导出Excel
     *
     * @param title
     * @param pojoClass
     * @param dataSet
     * @param out
     */
    public static void exportExcel(String title, Class<?> pojoClass, Collection<?> dataSet, OutputStream out) {
        try {
            // 首先检查数据看是否是正确的
            if (dataSet == null || dataSet.size() == 0) {
                throw new Exception("导出数据为空！");
            }
            if (title == null || out == null || pojoClass == null) {
                throw new Exception("传入参数不能为空！");
            }
            // 声明一个工作薄
            Workbook workbook = new HSSFWorkbook();
            // 生成一个表格
            Sheet sheet = workbook.createSheet(title);

            // 标题
            List<String> exportFieldTitle = new ArrayList<>();
            List<Integer> exportFieldWidth = new ArrayList<>();
            // 拿到所有列名，以及导出的字段的get方法
            List<Method> methodObj = new ArrayList<>();
            Map<String, Method> convertMethod = new HashMap<>();
            // 得到所有字段
            Field[] fields = pojoClass.getDeclaredFields();

            //是否求和配置
            boolean isSum=false;
            List<BigDecimal> sumList = new ArrayList<>();
            List<Boolean> isSumList = new ArrayList<>();
            List<Integer> scaleList = new ArrayList<>();
            List<Boolean> isMergeList = new ArrayList<>();
            List<Method> mergeFlagList = new ArrayList<>();

            // 遍历整个filed
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                ExcelConfig excelConfig = field.getAnnotation(ExcelConfig.class);
                // 如果设置了annotation
                if (excelConfig != null) {
                    // 添加到标题
                    exportFieldTitle.add(excelConfig.exportName());
                    // 添加标题的列宽
                    exportFieldWidth.add(excelConfig.exportFieldWidth());
                    // 添加到需要导出的字段的方法
                    String fieldName = field.getName();
                    LOG.debug(i + excelConfig.exportName() + " " + "列宽" + excelConfig.exportFieldWidth());
                    StringBuffer getMethodName = new StringBuffer("get");
                    getMethodName.append(fieldName.substring(0, 1).toUpperCase()).append(fieldName.substring(1));
                    Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[]{});
                    methodObj.add(getMethod);

                    if (excelConfig.exportConvertSign()) {
                        StringBuilder getConvertMethodName = new StringBuilder("get");
                        getConvertMethodName.append(fieldName.substring(0, 1).toUpperCase())
                                .append(fieldName.substring(1))
                                .append("Convert");
                        LOG.debug("convert: " + getConvertMethodName.toString());
                        Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(), new Class[]{});
                        convertMethod.put(getMethodName.toString(), getConvertMethod);
                    }
                    //记录是否求和配置
                    if (i != 0) {
                        if (excelConfig.isSum()) {
                            isSum = true;
                            LOG.debug(field.getName() + "需要合计");
                            isSumList.add(true);
                            sumList.add(new BigDecimal(0));
                            scaleList.add(excelConfig.scale());
                        } else {
                            isSumList.add(false);
                            sumList.add(null);
                            scaleList.add(null);
                        }
                    } else {
                        isSumList.add(false);
                        sumList.add(null);
                        scaleList.add(null);
                    }

                    // 是否合并
                    isMergeList.add(excelConfig.isMerge());
                    if (excelConfig.isMerge()) {
                        StringBuilder getMergeFlagName = new StringBuilder("get");
                        String mergeFlag;
                        if (StringUtils.isBlank(excelConfig.mergeFlag())) {
                            mergeFlag = getMethodName.toString();
                        } else {
                            getMergeFlagName.append(excelConfig.mergeFlag().substring(0, 1).toUpperCase()).append(excelConfig.mergeFlag().substring(1));
                            mergeFlag = getMergeFlagName.toString();
                        }
                        Method getMergeFlag = pojoClass.getMethod(mergeFlag, new Class[]{});
                        mergeFlagList.add(getMergeFlag);
                    } else {
                        mergeFlagList.add(null);
                    }
                }
            }
            int index = 0;
            // 产生表格标题行
            Row row = sheet.createRow(index);
            for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
                Cell cell = row.createCell(i);
                RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
                cell.setCellValue(text);
            }

            // 设置每行的列宽
            for (int i = 0; i < exportFieldWidth.size(); i++) {
                // 256=65280/255
                sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
            }
            Iterator its = dataSet.iterator();
            HashMap<String, PoiModel> poiModelMap = new HashMap<>();
            // 设置单元格样色
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 循环插入剩下的集合
            while (its.hasNext()) {
                // 从第二行开始写，第一行是标题
                index++;
                row = sheet.createRow(index);
                Object t = its.next();
                for (int k = 0; k < methodObj.size(); k++) {
                    Cell cell = row.createCell(k);
                    Method getMethod = methodObj.get(k);
                    Object value;
                    if (convertMethod.containsKey(getMethod.getName())) {
                        Method cm = convertMethod.get(getMethod.getName());
                        value = cm.invoke(t, new Object[]{});
                    } else {
                        value = getMethod.invoke(t, new Object[]{});
                    }
                    cell.setCellValue(value == null ? "" : value.toString());

                    //合计计算操作
                    if(isSumList.get(k)){
                        BigDecimal tempNum = sumList.get(k);
                        if(value instanceof Number){
                            sumList.set(k,tempNum.add(new BigDecimal(value.toString())));
                        }else if(value instanceof String){
                            sumList.set(k,tempNum.add(new BigDecimal(1)));
                        }else{
                            LOG.warn("未知合计类型"+value.toString());
                            sumList.set(k,tempNum.add(new BigDecimal(1)));
                        }
                    }

                    // 合并列
                    if (isMergeList.get(k)) {
                        String mergeValue;
                        Method cm = mergeFlagList.get(k);
                        mergeValue = cm.invoke(t, new Object[]{}).toString();
                        PoiModel poiModel = poiModelMap.get(getMethod.getName());
                        if (poiModel == null) {
                            poiModel = new PoiModel();
                            poiModel.setRowIndex(index);
                            poiModel.setContent(mergeValue);
                            poiModelMap.put(getMethod.getName(), poiModel);
                        } else {
                            // 判断值是否相等，不相等则合并
                            if (!poiModel.getContent().equals(mergeValue)) {
                                // 合并单元格必须是2个或以上
                                if (poiModel.getRowIndex() != (index - 1)) {
                                    CellRangeAddress cra=new CellRangeAddress(poiModel.getRowIndex(), index - 1, k, k);
                                    sheet.addMergedRegion(cra);
                                    sheet.getRow(poiModel.getRowIndex()).getCell(k).setCellStyle(cellStyle);
                                }
                                poiModel.setContent(mergeValue);
                                poiModel.setRowIndex(index);
                                poiModelMap.put(getMethod.getName(), poiModel);
                            } else {
                                // 最后一行无法在进行比较，直接合并
                                if (index == dataSet.size()) {
                                    if (poiModel.getRowIndex() != index) {
                                        CellRangeAddress cra=new CellRangeAddress(poiModel.getRowIndex(), index, k, k);
                                        sheet.addMergedRegion(cra);
                                        sheet.getRow(poiModel.getRowIndex()).getCell(k).setCellStyle(cellStyle);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            //合计行显示操作
            if(isSum){
                row = sheet.createRow(++index);
                row.createCell(0).setCellValue("合计");
                for (int k = 0; k < isSumList.size(); k++) {
                    if(isSumList.get(k)){
                        Cell cell = row.createCell(k);
                        cell.setCellValue((sumList.get(k).setScale(scaleList.get(k), RoundingMode.HALF_UP)).toString());
                    }
                }
            }
            workbook.write(out);
        } catch (Exception e) {
            LOG.error("Excel导出失败：", e);
        }
    }
}

controller代码：

/**
     * Excel导出
     * @param request
     * @param response
     */
    @GetMapping("/excelReport")
    public void excelReport(HttpServletRequest request, HttpServletResponse response) {
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        String fileName = "测试.xls";
        try {
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
            String name = "D:/upload/excel/" + System.currentTimeMillis()+".xls";
            File file = new File(name);
            OutputStream outputStream = new FileOutputStream(file);
            List<TestDTO> list = new ArrayList<>();
            list.add(new TestDTO(1,"王尼玛", 1, "C语言", 22));
            list.add(new TestDTO(1,"王尼玛", 1, "C++", 33));
            list.add(new TestDTO(2,"葫芦娃", 1, "Python", 50));
            list.add(new TestDTO(2,"葫芦娃", 1, "java", 44));
            list.add(new TestDTO(2,"葫芦娃", 1, "PHP", 66));
            list.add(new TestDTO(3,"佩奇", 1, "Python", 77));
            list.add(new TestDTO(3,"佩奇", 1, "java", 54));
            list.add(new TestDTO(3,"佩奇", 1, "PHP", 82));
            list.add(new TestDTO(4,"乔治", 1, "Python", 63));
            list.add(new TestDTO(4,"乔治", 1, "java", 77));
            list.add(new TestDTO(4,"乔治", 1, "PHP", 72));
            list.add(new TestDTO(5,"熊大", 1, "Python", 88));
            list.add(new TestDTO(5,"熊大", 1, "java", 91));
            list.add(new TestDTO(5,"熊大", 1, "PHP", 12));
            DownloadUtil.exportExcel("测试", TestDTO.class, list, outputStream);
            InputStream inputStream = new BufferedInputStream(new FileInputStream(file.getPath()));
            OutputStream os = response.getOutputStream();
            byte[] b = new byte[2048];
            int length;
            while ((length = inputStream.read(b)) > 0) {
                os.write(b, 0, length);
            }
            // 这里主要关闭。
            os.close();
            inputStream.close();
            outputStream.close();
            if (file.exists()) {
                boolean f = file.delete();
                System.out.println(f);
            }
        } catch (Exception e) {
            LOG.error("", e);
        }
    }

下载结果：