Java 导出Excel根据单元格内容计算并设置列宽度、行高

话不多说，上代码：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

/**
 * 根据单元格内容计算并设置列宽度、行高
 */
public class ExcelUtil {

    // sheet.setColumnWidth()参数的单位是1/256个字符宽度，这里使用300计算用于保留部分空白位置
    public static final Integer CELL_CARDINAL = 300;

    /**
     * 根据单元格内容计算并设置列宽度
     *
     * @param sheet           需要设置的Sheet对象
     * @param cellValue       单元格内容
     * @param cellIndex       单元格下标
     * @param allCellWidthMap 所有需要设定的单元格列宽度
     * @return 所有需要设定的单元格列宽度
     */
    public static Map<Integer, Integer> setCellWidth(Sheet sheet, String cellValue, int cellIndex, Map<Integer, Integer> allCellWidthMap) {
        if (StringUtils.isBlank(cellValue)) {
            return allCellWidthMap;
        }
        // 根据单元格内容计算宽度
        Integer cellWidth = cellValue.getBytes().length * CELL_CARDINAL;
        // 限定最大宽度
        int maxCellWidth = 60 * CELL_CARDINAL;
        Integer oldCellWidth = allCellWidthMap.get(cellIndex);
        if (Objects.nonNull(oldCellWidth)) {
            // 当前宽度不超过已设定宽度时，保留原宽度
            if (oldCellWidth > cellWidth) {
                cellWidth = oldCellWidth;
            }
            // 宽度超过最大限度时，使用最大宽度限度
            // 如果需要全部展示单元格内容可使用setWrapText(true)设置单元格自动换行
            if (cellWidth > maxCellWidth) {
                cellWidth = maxCellWidth;
            }
        }
        sheet.setColumnWidth(cellIndex, cellWidth);
        allCellWidthMap.put(cellIndex, cellWidth);
        return allCellWidthMap;
    }

    /**
     * 设置并获取单元格样式
     *
     * @param workbook
     * @return 单元格样式
     */
    public static CellStyle getCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 左对齐
        //style.setAlignment(HorizontalAlignment.LEFT);
        // 居中
        style.setAlignment(HorizontalAlignment.CENTER);
        /*//设置解析格式
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));*/
        // 自动换行
        style.setWrapText(true);
        /*// 设置背景色
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);*/
        /*// 单元格是否锁定
        style.setLocked(false);*/
        return style;
    }

    /**
     * 根据单元格内容计算并设置行高
     *
     * @param sourceRow
     */
    public static void setRowHeight(Row sourceRow) {
        double maxHeight = sourceRow.getHeight();
        for (int cellIndex = sourceRow.getFirstCellNum(); cellIndex <= sourceRow.getPhysicalNumberOfCells(); cellIndex++) {
            if (cellIndex < 0) {
                continue;
            }
            Cell sourceCell = sourceRow.getCell(cellIndex);
            String cellContent = getCellContentAsString(sourceCell);
            if (StringUtils.isBlank(cellContent)) {
                continue;
            }
            //单元格的宽高及单元格信息
            Map<String, Object> cellInfoMap = getCellInfo(sourceCell);
            Integer cellWidth = (Integer) cellInfoMap.get("width");
            Integer cellHeight = (Integer) cellInfoMap.get("height");
            if (cellHeight > maxHeight) {
                maxHeight = cellHeight;
            }
            //XSSFCellStyle cellStyle = sourceCell.getCellStyle();
            //XSSFFont font = cellStyle.getFont();
            //short fontHeight = font.getFontHeight();
            //int cellContentWidth = cellContent.getBytes().length * 256;
            int cellContentWidth = cellContent.getBytes().length * CELL_CARDINAL;
            int stringNeedsRows = cellContentWidth / cellWidth + 1;
            double stringNeedsHeight = (double) cellHeight * stringNeedsRows;

            if (stringNeedsHeight > maxHeight) {
                maxHeight = stringNeedsHeight;
                /*if (maxHeight / cellHeight > 5) {
                    maxHeight = 4 * cellHeight;
                }*/
                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 = sourceRow.getSheet().getRow(i).getHeight() + addHeight;
                        sourceRow.getSheet().getRow(i).setHeight((short) rowsRegionHeight);
                    }
                } else {
                    sourceRow.setHeight((short) maxHeight);
                }*/
            }
        }
        sourceRow.setHeight((short) maxHeight);
    }

    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 && s.endsWith(".0")) {
                    s = s.substring(0, s.length() - 2);
                }
                result = s;
                break;
            case STRING:
                result = String.valueOf(cell.getRichStringCellValue());
                break;
            case BLANK:
                break;
            case BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                break;
            default:
                break;
        }
        return result;
    }

    private static Map<String, Object> getCellInfo(Cell cell) {
        Map<String, Object> map = new HashMap();
        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 c = sheet.getMergedRegion(i);
            firstColumn = c.getFirstColumn();
            lastColumn = c.getLastColumn();
            firstRow = c.getFirstRow();
            lastRow = c.getLastRow();
            if (rowIndex >= firstRow && rowIndex <= lastRow) {
                if (columnIndex >= firstColumn && columnIndex <= lastColumn) {
                    isPartOfRegion = true;
                    break;
                }
            }
        }

        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++) {
                if (sheet.getRow(i) == null) {
                    height += sheet.createRow(i).getHeight();
                } else {
                    height += sheet.getRow(i).getHeight();
                }
            }
            if (lastRow > firstRow) {
                isPartOfRowsRegion = true;
            }
        } else {
            width = sheet.getColumnWidth(columnIndex);
            height += cell.getRow().getHeight();
        }
        map.put("isPartOfRowsRegion", isPartOfRowsRegion);
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        map.put("width", width);
        map.put("height", height);
        return map;
    }

}