poi导出 合并单元格 自适应高度

package com.example.demoweb.demo;
 
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
 
public class ExcelExportUtil {
 
    public static void main(String[] args) {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("test");
        Row row1 = sheet.createRow(0);
 
        /** ************************************************************ **/
        /** **********必须先计算合并单元格的高度再计算普通单元格的高度********** **/
        /** ************************************************************ **/
 
        //合并单元格先合并再set值，最后设置自适应高度
        Cell mergeCell = row1.createCell(1);
        ExcelExportUtil.addWlergedRegion(wb, sheet, 0, 1,1, 1);
        String mergeMaxValue = "北京市卫健委刚刚发布消息，10月25日0时至24时，北京无新增报告本地确诊病例、疑似病例和无症状感染者；无新增报告境外输入确诊病例、疑似病例和无症状感染者。私家车等交通方式，若乘坐公共交通工具，全程佩戴口單，注意与其他乘客保持安全距离，乘坐时尽量开窗通风；就医时全程佩戴口罩；做好手卫生，尽量避免触摸门把手、挂号机、取款机等物体表面，接触后及时洗手或用速干手消毒剂揉搓双手。打喷嚏、咳嗽时用纸巾或肘臂遮挡；";
        String mergeMinValue = "啊不吃等";
        ExcelExportUtil.setCellValue(wb, mergeCell, mergeMaxValue);
        ExcelExportUtil.calcAndSetRowHeigt(mergeCell);
 
        Cell cell1_1 = row1.createCell(0);
        //普通单元格直接set值
        ExcelExportUtil.setCellValue(wb, cell1_1, "尽量选择楼梯步行，若乘坐厢式电梯，应分散乘梯，避免同梯人员过多过密");
        ExcelExportUtil.calcAndSetRowHeigt(cell1_1);
 
        if(null != sheet.getRow(1)){
            Row row2 = sheet.getRow(1);
            if(null != row2.getCell(0)){
                Cell cell2_1 = row2.getCell(0);
                System.out.println(cell2_1.getStringCellValue());
            } else {
                Cell cell2_1 = row2.createCell(0);
                //普通单元格直接set值
                ExcelExportUtil.setCellValue(wb, cell2_1, "市卫健委提醒，疫情防控常态化形势下，市民朋友前往医疗机构，要备好口罩、消毒湿纸巾或免洗洗手液。如有发热症状的患者就医时应佩戴口罩，");
                ExcelExportUtil.calcAndSetRowHeigt(cell2_1);
            }
        }
 
        try {
            OutputStream out = new FileOutputStream("C:\\Users\\hegg\\Desktop\\信用管理模板文件(未加密)/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xlsx");
            wb.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
    /**
     * 合并单元格并设置单元格边框样式
     *
     * @param wb
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public static void addWlergedRegion(Workbook wb, Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        //合并单元格
        CellRangeAddress regionB = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(regionB);
        //使用RegionUtil类为合并后的单元格添加边框
        //旧版setBorderBottom为4个参数，参数依次为border、region、sheet、wb， 其中border为CellStyle.BORDER_THIN
        //新版4.1.2setBorderBottom为三个参数，参数依次为border、region、sheet， 其中border为BorderStyle.THIN
        //RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, regionB, sheet, wb);
        RegionUtil.setBorderBottom(BorderStyle.THIN, regionB, sheet);//下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, regionB, sheet);//左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, regionB, sheet);//有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, regionB, sheet);//上边框
    }
 
    /**
     * 统一setCellValue，并设置样式
     *
     * @param wb
     * @param cell
     * @param value
     */
    public static void setCellValue(Workbook wb, Cell cell, String value) {
        cell.setCellValue(value);
        cell.setCellStyle(setCellStyle(wb));
    }
 
    /**
     * 设置单元格样式
     *
     * @param wb
     * @return
     */
    public static CellStyle setCellStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        //设置边框样式
        //setBorderBottom旧版为CellStyle.BORDER_THIN，新版为BorderStyle.THIN
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        //垂直居中setVerticalAlignment旧版为CellStyle.VERTICAL_CENTER，新版为VerticalAlignment.CENTER
        style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        //自动换行
        style.setWrapText(true);
        Font font = wb.createFont();//设置字体
        font.setFontName("宋体");//设置字体大小
        font.setFontHeightInPoints((short) 11);//在样式中引用这种字体
        style.setFont(font);
        return style;
    }
 
    /**
     * 获取单元格及合并单元格的起始行、结束行、起始列、结束列、高度、宽度、是否为合并单元格
     * @param cell
     * @return Map<String, Object>
     */
    private static Map<String, Object> getCellInfo(Cell cell) {
        Sheet sheet = cell.getSheet();
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();
        boolean isPartOfRegion = false;
        int firstColumn = 0;
        int lastColumn = 0;
        int firstRow = 0;
        int lastRow = 0;
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            firstColumn = ca.getFirstColumn();
            lastColumn = ca.getLastColumn();
            firstRow = ca.getFirstRow();
            lastRow = ca.getLastRow();
            if (rowIndex >= firstRow && rowIndex <= lastRow) {
                if (columnIndex >= firstColumn && columnIndex <= lastColumn) {
                    isPartOfRegion = true;
                    break;
                }
            }
        }
        Map<String, Object> map = new HashMap<>();
        Integer width = 0;
        Integer height = 0;
        boolean isPartOfRowsRegion = false;
        if (isPartOfRegion) {
            for (int i = firstColumn; i <= lastColumn; i++) {
                width += sheet.getColumnWidth(i);
            }
            for (int i = firstRow; i <= lastRow; i++) {
                height += sheet.getRow(i).getHeight();
            }
            if (lastRow > firstRow) {
                isPartOfRowsRegion = true;
            }
        } else {
            width = sheet.getColumnWidth(columnIndex);
            height += cell.getRow().getHeight();
        }
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        map.put("firstColumn", firstColumn);
        map.put("lastColumn", lastColumn);
        map.put("width", width);
        map.put("height", height);
        map.put("isPartOfRowsRegion", isPartOfRowsRegion);
        return map;
    }
 
    /**
     * 解析一个单元格得到数据
     *
     * @param cell
     * @return
     */
    private static String getCellContentAsString(Cell cell) {
        if (null == cell) {
            return "";
        }
        String result = "";
        switch (cell.getCellType()) {
            case NUMERIC:
                String s = String.valueOf(cell.getNumericCellValue());
                if (s != null) {
                    if (s.endsWith(".0")) {
                        s = s.substring(0, s.length() - 2);
                    }
                }
                result = s;
                break;
            case STRING:
                result = String.valueOf(cell.getStringCellValue());
                break;
            case BLANK:
                break;
            case BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                break;
            default:
                break;
        }
        return result;
    }
 
    /**
     * 设置自适应行高*@paramcell
     */
    public static void calcAndSetRowHeigt(Cell cell) {
        //单元格的内容
        String cellContent = getCellContentAsString(cell);
        if (null != cellContent && !"".equals(cellContent)) {
            Row row = cell.getRow();
            //获取行高
            double maxHeight = row.getHeight();
            //单元格的宽高及单元格信息
            Map<String, Object> cellInfoMap = getCellInfo(cell);
            Integer cellWidth = (Integer) cellInfoMap.get("width");
            Integer cellHeight = (Integer) cellInfoMap.get("height");
            if (cellHeight > maxHeight) {
                maxHeight = cellHeight;
            }
            XSSFCellStyle cellstyle = (XSSFCellStyle) cell.getCellStyle();
            XSSFFont font = cellstyle.getFont();
            //字体的高度
            short fontHeight = font.getFontHeight();//cell内容字符串总宽度
            double cellContentWidth = cellContent.getBytes().length * 2 * 256;
 
            //字符串需要的行数不做四舍五入之类的操作
            double stringNeedsRows = (double) cellContentWidth / cellWidth;//小于一行补足一行
            if (stringNeedsRows < 1.0) {
                stringNeedsRows = 1.0;
            }
            //需要的高度
            double stringNeedsHeight = (double) fontHeight * stringNeedsRows;
            //需要重设行高
            if (stringNeedsHeight > maxHeight) {
                maxHeight = stringNeedsHeight / 2.25;
                //最后取天花板防止高度不够
                maxHeight = Math.ceil(maxHeight);
                //重新设置行高同时处理多行合并单元格的情况
                Boolean isPartOfRowsRegion = (Boolean) cellInfoMap.get("isPartOfRowsRegion");
                if (isPartOfRowsRegion) {
                    Integer firstRow = (Integer) cellInfoMap.get("firstRow");
                    Integer lastRow = (Integer) cellInfoMap.get("lastRow");//平均每行需要增加的行高
                    double addHeight = (maxHeight - cellHeight) / (lastRow - firstRow + 1);
                    for (int i = firstRow; i <= lastRow; i++) {
                        double rowsRegionHeight = row.getSheet().getRow(i).getHeight() + addHeight;
                        row.getSheet().getRow(i).setHeight((short) (rowsRegionHeight));
                    }
                } else {
                    if(maxHeight > cell.getRow().getHeight()){
                        row.setHeight((short)(maxHeight));
                    }
                }
            } else {
                row.setHeight((short)(stringNeedsHeight));
            }
        }
    }
}

jar下载地址