JAVA easypoi合并单元格

公司用到了easypoi导出，发现网上关于easypoi合并的例子真的很少，没找到，于是自己研究了下，项目是Spring Boot

pom.xml

<!--easypoi -->
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>3.0.1</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-web</artifactId>
			<version>3.0.1</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-annotation</artifactId>
			<version>3.0.1</version>
		</dependency>

合并单元格主要有两个注解

1、@Excel这个是必不可少的，主要是要有一个参数设置needMerge = true

源码这样解释的

2、@ExcelCollection此注解用于集合这种的多行

 

案例:

CustomerCluesExportVo

 

DealInfoExportVo

 

使用方法

一方的@Excel设置needMerge = true，框架默认是false,多方List(其他集合)使用@ExcelCollection,@ExcelCollection默认ArrayList

成品

 

 

如有需要，以下两个工具类可以直接使用

EasyPoiUtils

package com.shinedata.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**  
 * @Description:导出导入工具类
 * @date 2019年8月21日 下午6:13:54
 */
public class EasyPoiUtils {
	
	public static void sellerExportExcel(	List<?> list, String title, String sheetName,
											Class<?> pojoClass, String fileName,
											HttpServletResponse response) {
		defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
	}
	
	/**
	 * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
	 *
	 * @param list 导出的实体类
	 * @param title 表头名称
	 * @param sheetName sheet表名
	 * @param pojoClass 映射的实体类
	 * @param isCreateHeader 是否创建表头
	 * @param fileName
	 * @param response
	 * @return
	 */
	public static void exportExcel(	List<?> list, String title, String sheetName, Class<?> pojoClass,
									String fileName, boolean isCreateHeader,
									HttpServletResponse response) {
		ExportParams exportParams = new ExportParams(title, sheetName);
		exportParams.setCreateHeadRows(isCreateHeader);
		exportParams.setStyle(ExcelStyleUtils.class);
		defaultExport(list, pojoClass, fileName, response, exportParams);
	}
	
	/**
	 * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头
	 *
	 * @param list 导出的实体类
	 * @param title 表头名称
	 * @param sheetName sheet表名
	 * @param pojoClass 映射的实体类
	 * @param fileName
	 * @param response
	 * @return
	 */
	public static void exportExcel(	List<?> list, String title, String sheetName, Class<?> pojoClass,
									String fileName, HttpServletResponse response,
									Integer userMasterId) {
		defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
	}
	
	/**
	 * 功能描述：Map 集合导出
	 *
	 * @param list 实体集合
	 * @param fileName 导出的文件名称
	 * @param response
	 * @return
	 */
	public static void exportExcel(	List<Map<String, Object>> list, String fileName,
									HttpServletResponse response) {
		defaultExport(list, fileName, response);
	}
	
	/**
	 * 功能描述：默认导出方法
	 *
	 * @param list 导出的实体集合
	 * @param fileName 导出的文件名
	 * @param pojoClass pojo实体
	 * @param exportParams ExportParams封装实体
	 * @param response
	 * @return
	 */
	private static void defaultExport(	List<?> list, Class<?> pojoClass, String fileName,
										HttpServletResponse response, ExportParams exportParams) {
		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
		if (workbook != null) {
			downLoadExcel(fileName, response, workbook);
		}
	}
	
	/**
	 * 功能描述：Excel导出
	 *
	 * @param fileName 文件名称
	 * @param response
	 * @param workbook Excel对象
	 * @return
	 */
	private static void downLoadExcel(	String fileName, HttpServletResponse response,
										Workbook workbook) {
		try {
			response.setCharacterEncoding("UTF-8");
			response.setHeader("content-Type", "multipart/form-data");
			response.setHeader("Content-Disposition",
				"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
			workbook.write(response.getOutputStream());
			
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 功能描述：默认导出方法
	 *
	 * @param list 导出的实体集合
	 * @param fileName 导出的文件名
	 * @param response
	 * @return
	 */
	private static void defaultExport(	List<Map<String, Object>> list, String fileName,
										HttpServletResponse response) {
		Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
		if (workbook != null)
			;
		downLoadExcel(fileName, response, workbook);
	}
	
	/**
	 * 功能描述：根据文件路径来导入Excel
	 *
	 * @param filePath 文件路径
	 * @param titleRows 表标题的行数
	 * @param headerRows 表头行数
	 * @param pojoClass Excel实体类
	 * @return
	 */
	public static <T> List<T> importExcel(	String filePath, Integer titleRows, Integer headerRows,
											Class<T> pojoClass) {
		//判断文件是否存在
		if (StringUtils.isBlank(filePath)) {
			return null;
		}
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRows);
		params.setHeadRows(headerRows);
		List<T> list = null;
		File file = new File(filePath);
		try {
			list = ExcelImportUtil.importExcel(file, pojoClass, params);
		} catch (NoSuchElementException e) {
			throw new RuntimeException("模板不能为空");
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		return list;
	}
	
	/**
	 * 功能描述：根据文件流来导入Excel
	 *
	 * @param filePath 文件路径
	 * @param titleRows 表标题的行数
	 * @param headerRows 表头行数
	 * @param pojoClass Excel实体类
	 * @return
	 */
	public static <T> List<T> importExcel(	InputStream is, Integer titleRows, Integer headerRows,
											Class<T> pojoClass) {
		//判断文件是否存在
		if (is == null) {
			return null;
		}
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRows);
		params.setHeadRows(headerRows);
		List<T> list = null;
		try {
			list = ExcelImportUtil.importExcel(is, pojoClass, params);
		} catch (NoSuchElementException e) {
			throw new RuntimeException("模板不能为空");
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		return list;
	}
	
	/**
	 * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
	 *
	 * @param file 上传的文件
	 * @param titleRows 表标题的行数
	 * @param headerRows 表头行数
	 * @param pojoClass Excel实体类
	 * @return
	 */
	public static <T> List<T> importExcel(	MultipartFile file, Integer titleRows, Integer headerRows,
											Class<T> pojoClass) {
		if (file == null) {
			return null;
		}
		ImportParams params = new ImportParams();
		params.setTitleRows(titleRows);
		params.setHeadRows(headerRows);
		List<T> list = null;
		try {
			list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
		} catch (NoSuchElementException e) {
			throw new RuntimeException("excel文件不能为空");
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
			
		}
		return list;
	}
}

ExcelStyleUtils

package com.shinedata.util;

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

/**
 * @ClassName ExcelStyleUtils
 * @Date 2020/7/14 15:23
 */
public class ExcelStyleUtils implements IExcelExportStyler {

    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 10;
    private static final short FONT_SIZE_ELEVEN = 11;
    private static final short FONT_SIZE_TWELVE = 12;
    /**
     * 大标题样式
     */
    private CellStyle headerStyle;
    /**
     * 每列标题样式
     */
    private CellStyle titleStyle;
    /**
     * 数据行样式
     */
    private CellStyle styles;

    public ExcelStyleUtils(Workbook workbook) {
        this.init(workbook);
    }

    /**
     * 初始化样式
     *
     * @param workbook
     */
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook);
        this.styles = initStyles(workbook);
    }


    /**
     * 大标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        return headerStyle;
    }

    /**
     * 每列标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getTitleStyle(short color) {
        return titleStyle;
    }

    /**
     * 数据行样式
     *
     * @param parity 可以用来表示奇偶行
     * @param entity 数据内容
     * @return 样式
     */
    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
        return styles;
    }

    /**
     * 获取样式方法
     *
     * @param dataRow 数据行
     * @param obj     对象
     * @param data    数据
     */
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return getStyles(true, entity);
    }

    /**
     * 模板使用的样式设置
     */
    @Override
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return null;
    }

    /**
     * 初始化--大标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        return style;
    }

    /**
     * 初始化--每列标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initTitleStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
        //背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    /**
     * 初始化--数据行样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initStyles(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 基础样式
     *
     * @return
     */
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("宋体");
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }
}

调用

EasyPoiUtils.exportExcel(exportVos, title, fileName, CustomerCluesExportVo.class, fileName, true, response);