目标:导出excel,包含多级表头、自动合并单元格
- easypoi依赖
<properties>
<easypoi.version>4.1.2</easypoi.version>
</properties>
<!--EasyPoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>${easypoi.version}</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>${easypoi.version}</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>${easypoi.version}</version>
</dependency>
- easypoi工具类
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 cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
/**
* Excel导入导出工具类
* com.tdh.emos.utils -> ExcelUtils
*
* @Author: guoxy
* @Description:
* @Date: 2021/7/21
*/
public class ExcelUtils {
/**
* excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, fileName, response);
}
/**
* 默认的 excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param response
* @param exportParams 导出参数(标题、sheet名称、是否创建表头,表格类型)
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
/**
* 导出excel表格
* @param list 数据集合
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param exportParams 导出参数(标题、sheet名称、是否创建表头,表格类型)
* @param addressList
* @param response
* @throws IOException
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams,
List<CellRangeAddress> addressList, HttpServletResponse response) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
Sheet sheet = workbook.getSheetAt(0);
// 处理自定义合并单元格和easypoi注解合并单元格冲突,以自定义为主
addressList.forEach(address -> {
sheet.getMergedRegions().forEach(item -> {
if (item.getFirstRow() >= address.getFirstRow()
&& item.getLastRow() <= address.getLastRow()
&& item.getFirstColumn() >= address.getFirstColumn()
&& item.getLastColumn() <= address.getLastColumn()) {
sheet.removeMergedRegion(sheet.getMergedRegions().indexOf(item));
}
});
sheet.addMergedRegion(address);
});
//设置标题,内容 行高
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (i == 3) {
row.setHeightInPoints(32);
}
}
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param exportParams 导出参数(标题、sheet名称、是否创建表头,表格类型)
* @param response
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
/**
* 自定义表头
*
* @param list
* @param title
* @param sheetName
* @param pojoClass
* @param fileName
* @param response
* @throws IOException
*/
public static void exportExcel(List<?> list, String title, String sheetName, List<ExcelExportEntity> pojoClass, String fileName, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param isCreateHeader 是否创建表头
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel下载
*
* @param fileName 下载时的文件名称
* @param response
* @param workbook excel数据
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
//本地导出测试
// FileOutputStream fos = null;
// try {
// fos = new FileOutputStream("E:\\" +UuidUtils.generateUuid()+fileName+".xlsx");
// workbook.write(fos);
// workbook.close();
// fos.close();
// } catch (IOException e) {
// e.printStackTrace();
// }
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param file excel文件
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
return importExcel(file, 1, 1, pojoClass);
}
/**
* excel 导入
*
* @param filePath excel文件路径
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedSave(true);
params.setSaveUrl("/excel/");
try {
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("模板不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param file 上传的文件
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
}
- 实体类、注解源码中都有中文注释
import cn.afterturn.easypoi.excel.annotation.Excel;
import com.tdh.emos.vo.performanceStatistics.AreaFymcVo;
import lombok.Data;
import java.math.BigDecimal;
/**
* com.tdh.emos.vo.statics -> CreditorStaticsVo
*
* @Author: guoxy
* @Description:
* @Date: 2022/6/28
*/
@Data
public class CreditorStaticsVo{
private static final String title = "债权申报";
/**
* 法院
*/
@Excel(name = "法院", orderNum = "0",mergeVertical = true,height = 16)
private String province;
/**
*
*/
@Excel(name = "市", orderNum = "1",mergeVertical = true)
private String city;
/**
* 法院
*/
@Excel(name = "法院", orderNum = "2",mergeVertical = true,width = 25)
private String fymc;
/**
* 案件总数
*/
@Excel(name = "案件总数", orderNum = "3")
private Integer caseTotal;
/**
* 发布债权申报公告的案件数
*/
@Excel(name = "发布债权申报公告的案件数", orderNum = "4",width = 15,groupName = title)
private Integer noticeTotal;
/**
* 发布债权申报公告率
*/
private BigDecimal noticeRate;
@Excel(name = "发布债权申报公告率", orderNum = "5",width = 15,groupName = title)
private String noticeRateStr;
/**
* 债权人总数
*/
@Excel(name = "债权人总数", orderNum = "6",width = 15,groupName = title)
private Integer creditorTotal;
/**
* 线上债权申报人数
*/
@Excel(name = "线上债权申报人数", orderNum = "7",width = 15,groupName = title)
private Integer applyPassTotal;
/**
* 线上申报率
*/
private BigDecimal applyRate;
@Excel(name = "线上申报率", orderNum = "8",width = 15,groupName = title)
private String applyRateStr;
/**
* 线上债权申报总额(元)
*/
@Excel(name = "线上债权申报总额(元)", orderNum = "9",width = 15,groupName = title)
private BigDecimal applyAmount;
/**
* 线上债权审核确定总额(元)
*/
@Excel(name = "线上债权审核确定总额(元)", orderNum = "10",width = 15,groupName = title)
private BigDecimal confirmAmount;
/**
* 确定债权总额(元)
*/
@Excel(name = "确定债权总额(元)", orderNum = "11",width = 15,groupName = title)
private BigDecimal creditorAmount;
}
- 自定义封装业务参数
/**
* 设置导出参数
*/
public static <T extends AreaFymcVo> void dataExportParam(List<T> list, HeadStatisticsDTO dto, HttpServletResponse response, Class<T> clazz) throws IOException {
// 是否有省级高院
dto.setTitle(StrUtil.format("破产平台应用效能统计表({})",dto.getTitleType()));
List<String> fydmProvince = list.stream().filter(e -> e.getFydm().endsWith(Constant.FYDM_PROVINCE) && Constant.ONE_STR.equals(e.getDm())).map(AreaFymcVo::getFydm).collect(Collectors.toList());
String fileName = StrUtil.format("{}破产平台应用效能统计{}({})", dto.getAreaName(),dto.getTitleType(), StrUtil.format("{}-{}",dto.getAssignStartTime(),dto.getAssignEndTime()));
// 标题
String sheetName = StrUtil.format("数据统计日期:{}-{}",
DateUtil.format(DateUtil.parse(dto.getAssignStartTime()), "yyyy年MM月"),
DateUtil.format(DateUtil.parse(dto.getAssignEndTime()), "yyyy年MM月"));
// 自定义合并行列,CellRangeAddress(第几行开始,第几行结束,第几列开始,第几列结束)
List<CellRangeAddress> addressList = new ArrayList<>();
int titleNum = 2;
// 合并第三到第四行,第一到第三列(即法院)
addressList.add(new CellRangeAddress(titleNum, titleNum + 1, 0, 2));
if (fydmProvince.size() > 0) {
addressList.add(new CellRangeAddress(list.size() + titleNum--, list.size() + titleNum--, 1, 2));
}
if (dto.getAreaId().endsWith(Constant.FYDM_PROVINCE) && list.size() > 1) {
long count = list.stream().filter(e -> e.getFydm().endsWith(Constant.FYDM_PROVINCE) && !Constant.ZERO_STR.equals(e.getDm())).count();
// 高院合并单元格
if (count > 0) {
addressList.add(new CellRangeAddress(list.size() + titleNum, list.size() + titleNum, 1, 2));
}
titleNum++;
addressList.add(new CellRangeAddress(list.size() + titleNum, list.size() + titleNum, 1, 2));
}
// 一级二级标题
ExportParams exportParams = new ExportParams(dto.getAreaName() + dto.getTitle(), dto.getAreaName() + dto.getTitle(), ExcelType.XSSF);
// exportParams.setStyle(ExcelExportStyler.class); // 设置样式
exportParams.setSecondTitle(sheetName);
response.setHeader("Content-file", "filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
response.setHeader("Access-Control-Expose-Headers", "Content-file" );
ExcelUtils.exportExcel(list, clazz, fileName, exportParams, addressList, response);
}
- 调用时,传入list集合即可