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