JAVA poi导出Excel 并且合并单元格 (直接拷贝就能使用)

合并前

合并后

活不多说,直接上代码

一、实体工具类

package com.ruoyi.scenic.domain;import lombok.Data;@Datapublic class PoiModel {private String content;private String oldContent;private int rowIndex;private int cellIndex;}

二、Excel导出工具类

package com.ruoyi.scenic.utils;import com.ruoyi.scenic.domain.PoiModel;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.*;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.FileNotFoundException;import java.io.IOException;import java.io.OutputStream;import java.text.DecimalFormat;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.regex.Pattern;public class ExcelUtils {/*** 判断是不是数字** @param str* @return*/private static boolean isNumeric(String str) {if (str == null || str.length() == 0) {return false;}Pattern pattern = pile("^[-\\+]?[\\d]*$");return pattern.matcher(str).matches();}/** @param objData 数据* @param fileName 文件名* @param sheetName sheet名* @param columns 表头* @param mergeIndex 需要合并的列号集合 ,在不确定的情况下 有多少列就填充多少条* @param request* @param response* @return*/public static int exportToExcelForXlsx(List<List> objData, String fileName, String sheetName, List<String> columns, List mergeIndex, HttpServletResponse response) {int flag = 0;// 创建工作薄XSSFWorkbook wb = new XSSFWorkbook();// sheet1XSSFSheet sheet1 = wb.createSheet(sheetName);//设置样式XSSFCellStyle style = wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);//水平对齐//表头sheet1.createFreezePane(0, 1);//冻结表头XSSFRow sheet1row1 = sheet1.createRow((short) 0);sheet1row1.setHeight((short) 480);//写入表头if (columns != null && columns.size() > 0) {for(int i=0;i<columns.size();i++) {String column = columns.get(i);//列XSSFCell cell = sheet1row1.createCell(i);cell.setCellValue(column);}}int dataSatrtIndex = 1;//数据开始行boolean isMerge = false;if(mergeIndex != null && mergeIndex.size() != 0) {isMerge = true;}//写入数据if (objData != null && objData.size() > 0) {Map<Integer, PoiModel> poiModels = new HashMap<Integer, PoiModel>();//循环写入表中数据int i = 0;for ( ; i < objData.size(); i++) {//数据行XSSFRow row = sheet1.createRow((short) (i+dataSatrtIndex));//行内循环，既单元格（列）List<Object> list = objData.get(i);DecimalFormat decimalFormat = new DecimalFormat("0.00");int j = 0;for (Object o : list) {//数据列String content = "";if(o != null) {if (o.toString().contains(".") && isNumeric(o.toString())) {content = decimalFormat.format(Float.valueOf(o.toString()));} else if (o.toString().contains("-") && o.toString().contains(":")) {content = String.valueOf(o).split("\\.")[0];}else {content = String.valueOf(o);}}if(isMerge && mergeIndex.contains(j)) {//如果该列需要合并PoiModel poiModel = poiModels.get(j);if(poiModel == null) {poiModel = new PoiModel();poiModel.setContent(content);poiModel.setRowIndex(i + dataSatrtIndex);poiModel.setCellIndex(j);poiModels.put(j, poiModel);}else {if(!poiModel.getContent().equals(content)) {//如果不同了，则将前面的数据合并写入XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex());XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列lastCell.setCellValue(poiModel.getContent());//合并单元格if(poiModel.getRowIndex() != i + dataSatrtIndex - 1) {sheet1.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));}//将新数据存入poiModel.setContent(content);poiModel.setRowIndex(i + dataSatrtIndex);poiModel.setCellIndex(j);poiModels.put(j, poiModel);}}row.createCell(j);//创建单元格}else {//该列不需要合并//数据列XSSFCell cell = row.createCell(j);cell.setCellValue(content);}j++;}}//将最后一份存入if(poiModels != null && poiModels.size() != 0) {for(Integer key : poiModels.keySet()) {PoiModel poiModel = poiModels.get(key);XSSFRow lastRow = sheet1.getRow(poiModel.getRowIndex());XSSFCell lastCell = lastRow.getCell(poiModel.getCellIndex());lastCell.setCellValue(poiModel.getContent());//合并单元格if(poiModel.getRowIndex() != i + dataSatrtIndex -1) {sheet1.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));}}}} else {flag = -1;}//设置固定列宽，大概规律网上有不少版本自行百度//这里大概是143像素for (int i = 0; i < columns.size(); i++) {sheet1.setColumnWidth(i, 4550);}OutputStream os = null;try {// 创建一个普通输出流os = response.getOutputStream();fileName = "file.xls";// 请求浏览器打开下载窗口response.reset();response.setCharacterEncoding("UTF-8");fileName = new String(fileName.getBytes(), "ISO8859-1");response.setHeader("Content-Disposition", "attachment; filename=" + fileName);// 要保存的文件名response.setContentType("application/octet-stream");wb.write(os);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {wb.close();os.close();} catch (IOException e) {e.printStackTrace();}}return flag;}}

三、Test测试类

public static void main(String[] args) {List columns = new ArrayList<>();//标头columns.add("目录");columns.add("目录");columns.add("目录");columns.add("内容");String fileName = "文件名字";//文件名字String sheetName = "sheet名字";//sheet名字// 内容数据List<List> exportData = new ArrayList<>();//行内的数据List rowData = new ArrayList();rowData.add("一级目录1");rowData.add("二级目录1");rowData.add("三级目录1");rowData.add("内容1");exportData.add(rowData);List rowData2 = new ArrayList();rowData2.add("一级目录1");rowData2.add("二级目录1");rowData2.add("三级目录1");rowData2.add("内容2");exportData.add(rowData2);List rowData3 = new ArrayList();rowData3.add("一级目录1");rowData3.add("二级目录1");rowData3.add("三级目录2");rowData3.add("内容3");exportData.add(rowData3);List rowData4 = new ArrayList();rowData4.add("一级目录1");rowData4.add("二级目录1");rowData4.add("三级目录2");rowData4.add("内容4");exportData.add(rowData4);List rowData5 = new ArrayList();rowData5.add("一级目录1");rowData5.add("二级目录2");rowData5.add("三级目录3");rowData5.add("内容5");exportData.add(rowData5);List rowData6 = new ArrayList();rowData6.add("一级目录1");rowData6.add("二级目录2");rowData6.add("三级目录3");rowData6.add("内容6");exportData.add(rowData6);//需要合并的列号 在不确定的情况下 最好有多少列就填充几条List mergeIndex = new ArrayList();mergeIndex.add(0);mergeIndex.add(1);mergeIndex.add(2);mergeIndex.add(3);int flag = exportToExcelForXlsx(exportData, fileName, sheetName, columns, mergeIndex);System.out.println(flag);}

原博：/CSDNxiaoxuan/article/details/124817923?ops_request_misc=&request_id=&biz_id=102&utm_term=java%E5%AF%BC%E5%87%BAexcel%E5%90%88%E5%B9%B6%E5%8D%95%E5%85%83%E6%A0%BC&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduweb~default-8-124817923.nonecase&spm=1018.2226.3001.4187