easyPoi多级表头、自动合并单元格
目标：导出excel，包含多级表头、自动合并单元格
- easypoi依赖
4.1.2 cn.afterturn easypoi-base ${easypoi.version} cn.afterturn easypoi-web ${easypoi.version} cn.afterturn easypoi-annotation ${easypoi.version} - 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> list, String fileName, HttpServletResponse response) throws IOException {defaultExport(list, fileName, response);}/*** 默认的 excel 导出** @param list 数据列表* @param fileName 导出时的excel名称* @param response*/private static void defaultExport(List> 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 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 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 * @return*/public static List importExcel(MultipartFile file, Class pojoClass) throws IOException {return importExcel(file, 1, 1, pojoClass);}/*** excel 导入** @param filePath excel文件路径* @param titleRows 表格内数据标题行* @param headerRows 表头行* @param pojoClass pojo类型* @param * @return*/public static List importExcel(String filePath, Integer titleRows, Integer headerRows, Class 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 * @return*/public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class 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 * @return*/public static List importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class 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 void dataExportParam(List list, HeadStatisticsDTO dto, HttpServletResponse response, Class clazz) throws IOException {// 是否有省级高院dto.setTitle(StrUtil.format("破产平台应用效能统计表（{}）",dto.getTitleType()));List 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 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集合即可
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！