1、合并单元格工具类
/*** 导出excel工具类*/public class ExportExcelUtil {public ExportExcelUtil() {super();}/*** @Param [columnNames 表头, column 字段名称, rows 数据, excelName 文件名称, mergeIndex 合并列, response]**/public static void CombineExcel(String[] columnNames, String[] column, List<实体类型> rows, String excelName, int[] mergeIndex, HttpServletResponse response) {SXSSFWorkbook workbook = new SXSSFWorkbook(); // 创建工作薄，相当于一个文件SXSSFSheet sheet = workbook.createSheet("sheet名字"); // 创建一个表// 标题样式CellStyle titleStyle = workbook.createCellStyle();// 标题字体Font titleFont = workbook.createFont();titleFont.setFontHeightInPoints((short) 12); // 字体大小titleFont.setFontName("宋体");titleStyle.setFont(titleFont);// 表头样式CellStyle headerStyle = workbook.createCellStyle();headerStyle.setWrapText(true); // 设置多行显示DataFormat format = workbook.createDataFormat();headerStyle.setDataFormat(format.getFormat("@"));// 表头字体Font headerFont = workbook.createFont();headerFont.setFontHeightInPoints((short) 12);headerFont.setFontName("宋体");headerFont.setBold(true);headerStyle.setFont(headerFont);// 数据样式CellStyle dataStyle = workbook.createCellStyle();// 数据字体Font dataFont = workbook.createFont();dataFont.setFontHeightInPoints((short) 9);dataFont.setFontName("宋体");dataStyle.setFont(dataFont);Row row = null;Cell cell = null;row = sheet.createRow(0);//设置表头for (int i = 0; i < columnNames.length; i++) {row.setHeight((short) (2 * 256));cell = row.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(headerStyle);}List<PoiModel> poiModels = Lists.newArrayList();//设置数据行for (int i = 0; i < rows.size(); i++) {Row dataRow = sheet.createRow(i + 1);sheet.trackAllColumnsForAutoSizing();//设置对应的实体实体类型 project = rows.get(i); // 当前行数据int index = i + 1; // 当前行// 循环字段名称数组for (int j = 0; j < column.length; j++) {// 循环需要合并的列listfor (int mer : mergeIndex) {// 如果是第一行 为PoiModel赋值记录数据后结束循环if (index == 1) {String content=null;String oldContent=null;//设置那些列需要合并if(mer == 0){// 对应列 的 字段值//注意：project.具体的实体类型1（），这是获取实体的get方法。oldContent = project.具体的实体类型1（） == null ? "" : project.具体的实体类型1（）();content = project.具体的实体类型1（） == null ? "" : project.具体的实体类型1（）();}else if(mer == 1){// 对应列 的 字段值oldContent = project.具体的实体类型2() == null ? "" : project.具体的实体类型2();content = project.具体的实体类型2() == null ? "" : project.具体的实体类型2();}// 为poiModel 赋值PoiModel poiModel = PoiModel.builder().oldContent(oldContent).content(content).rowIndex(1).cellIndex(j).build();poiModels.add(poiModel);break;}// 如果记录list中有值if (poiModels != null && poiModels.size() > 0) {// 对应列 的 PoiModelPoiModel poiModel = poiModels.get(j);String content=null;//设置那些列需要合并if(mer == 0){// 对应列 的 字段值content = project.具体的实体类型1() == null ? "" : project.具体的实体类型1();}else if(mer == 1){content = project.具体的实体类型2() == null ? "" : project.具体的实体类型2();}if (j > 0 && mer == j) {if (!poiModel.getContent().equals(content)) {get(poiModel, content, index, j, sheet);}}if (mer == j && j == 0 && !poiModel.getContent().equals(content)) {get(poiModel, content, index, j, sheet);}if (mer == j && index == rows.size() && poiModels.get(j).getRowIndex() != index) {CellRangeAddress cra = new CellRangeAddress(poiModels.get(j).getRowIndex(), index, poiModels.get(j).getCellIndex(), poiModels.get(j).getCellIndex());sheet.addMergedRegion(cra);}}}//将值传入对应单元格Cell dataCell = dataRow.createCell(j);sheet.autoSizeColumn(j, true);if(column[j] == "实体1"){dataCell.setCellValue(project.具体的实体类型1());}else if(column[j] == "实体2"){dataCell.setCellValue(project.具体的实体类型2());}else if(column[j] == "实体3"){dataCell.setCellValue(project.具体的实体类型3());}else if(column[j] == "实体4"){dataCell.setCellValue(project.具体的实体类型4());}else if(column[j] == "实体5"){dataCell.setCellValue(project.具体的实体类型5());}dataCell.setCellStyle(dataStyle);}}OutputStream os = null;try {// 创建一个普通输出流os = response.getOutputStream();// 请求浏览器打开下载窗口response.reset();response.setCharacterEncoding("UTF-8");excelName = new String(excelName.getBytes(), "ISO8859-1");response.setHeader("Content-Disposition", "attachment; filename=" + excelName);// 要保存的文件名response.setContentType("application/octet-stream");workbook.write(os);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {workbook.close();os.close();} catch (IOException e) {e.printStackTrace();}}}private static void get(PoiModel poiModel, String content, int index, int i, Sheet sheet) {// 如果 PoiModel记录的行数 不是 当前行 则合并if (poiModel.getRowIndex() != index - 1) {// 相同值第一次出现的行数 到当前行数-1 （下标） 合并开始列 合并结束列CellRangeAddress cra = new CellRangeAddress(poiModel.getRowIndex(), index - 1, poiModel.getCellIndex(), poiModel.getCellIndex());//在sheet里增加合并单元格sheet.addMergedRegion(cra);}/*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/poiModel.setContent(content);poiModel.setRowIndex(index);poiModel.setCellIndex(i);}}
2、工具类引用的import:
import press.utils.Lists;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.servlet.http.HttpServletResponse;import java.io.FileNotFoundException;import java.io.IOException;import java.io.OutputStream;import java.util.List;
3、合并单元格具体的方法调用：
@GetMapping("/接口名")public void exportManageExcel1(HttpServletResponse response,String 参数1) throws Exception {try {List<实体> list = XXXService.exportExcel(参数1);String[] headers = {"名称1", "名称2", "名称3","名称4", "名称5"};String[] column = {"实体1", "实体2", "实体3", "实体4", "实体5"};String excelName = "表名.xlsx";ExportExcelUtil exportExcelUtil = new ExportExcelUtil();int[] mergeIndex = {0,1};bineExcel(headers, column, list, excelName, mergeIndex, response);} catch (Exception e) {e.printStackTrace();}}
如果觉得《Excel导出且合并相同值的单元格》对你有帮助，请点赞、收藏，并留下你的观点哦！
