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下载地址
