随笔- 113 文章- 0 评论- 9 阅读- 39万

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

话不多说，上代码：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

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;
    }
 
}