功能介绍
首先说下我的功能,根据excel 模板生成后,内容太多根据内容自动换行,且高度自动增加,实现不遮挡。
关注公众号了解更多精彩
效果图如下:
模板生成后。生成后发现中间文字多的地方不能很好的展示完全。
最终调整完效果
代码调用
FileInputStream fileInputStream = new FileInputStream(excelUrl);HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);HSSFSheet sheet = workbook.getSheet("付款通知书");int lastRowNum = sheet.getLastRowNum();for(int x=0;x<lastRowNum;x++){HSSFRow row = sheet.getRow(x);if(row!=null){ExcelModelExportUtil.calcAndSetRowHeigt(sheet.getRow(x));}}OutputStream os = new BufferedOutputStream(new FileOutputStream(excelUrl));workbook.write(os);fileInputStream.close();os.flush();os.close();
public static void calcAndSetRowHeigt(HSSFRow sourceRow) throws UnsupportedEncodingException {for (int cellIndex = sourceRow.getFirstCellNum();cellIndex <= sourceRow.getPhysicalNumberOfCells(); cellIndex++) {//行高double maxHeight = sourceRow.getHeight();HSSFCell sourceCell = sourceRow.getCell(cellIndex);//单元格的内容String cellContent = getCellContentAsString(sourceCell);if (null == cellContent || "".equals(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;}System.out.println("单元格的宽度 : " + cellWidth + " 单元格的高度 : " + maxHeight + ", " +" 单元格的内容 : " + cellContent);HSSFCellStyle cellStyle = sourceCell.getCellStyle();HSSFFont font = cellStyle.getFont(sourceRow.getSheet().getWorkbook());//字体的高度short fontHeight = font.getFontHeight();// cell内容字符串总宽度256为单个字符所占的宽度 200 上下留的间隔自定义double cellContentWidth =( cellContent.getBytes().length ) * 256;double zifu =( cellContent.length() ) * 256;List<String>contentList = Arrays.asList(cellContent.split("\n"));//重新计算 宽度if (contentList.size() > 1) {cellContentWidth = 0.0;for (String content : contentList) {double tempCountWidth = 0.0;double tempWidth =( content.getBytes().length ) * 256;double num = tempWidth / cellWidth;if (num > 1.0) {tempCountWidth =( (int)num +1) * cellWidth;}else {tempCountWidth = cellWidth;}cellContentWidth += tempCountWidth;}}//85.333 倍System.out.println("字符:"+zifu +" 字节:"+cellContent.getBytes().length );System.out.println("cellContentWidth:"+cellContentWidth );//字符串需要的行数 不做四舍五入之类的操作double stringNeedsRows = (double) cellContentWidth / cellWidth;//小于一行补足一行if (stringNeedsRows < 1.0) {stringNeedsRows = 1.0;}//需要的高度(Math.floor(stringNeedsRows) - 1) * 40 为两行之间空白高度double stringNeedsHeight = (double) fontHeight * stringNeedsRows;//需要重设行高if (stringNeedsHeight > maxHeight) {maxHeight = stringNeedsHeight;//超过原行高三倍 则为5倍 实际应用中可做参数配置if (maxHeight / cellHeight > 5) {maxHeight = 5 * 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);}}System.out.println("字体高度 : " + fontHeight + ", 字符串宽度 : " + cellContentWidth + ", 字符串需要的行数 : " + stringNeedsRows + ", 需要的高度 : " + stringNeedsHeight + ", 现在的行高 : " + maxHeight);System.out.println();}}/*** 解析一个单元格得到数据 * @param cell * @return*/private static String getCellContentAsString(HSSFCell cell) {if (null == cell) {return "";}String result = "";switch (cell.getCellType()) {case Cell.CELL_TYPE_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 Cell.CELL_TYPE_STRING:result = cell.getStringCellValue() == null ? "" : cell.getStringCellValue().trim();break;case Cell.CELL_TYPE_BLANK:break;case Cell.CELL_TYPE_BOOLEAN:result = String.valueOf(cell.getBooleanCellValue());break;case Cell.CELL_TYPE_ERROR:break;default:break;}return result;}/*** 获取单元格及合并单元格的宽度* @param cell* @return*/private static Map<String, Object> getCellInfo(HSSFCell cell) {HSSFSheet 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++) {Region ca = sheet.getMergedRegionAt(i);firstColumn = ca.getColumnFrom();lastColumn = ca.getColumnTo();firstRow = ca.getRowFrom();lastRow = ca.getRowTo();if (rowIndex >= firstRow && rowIndex <= lastRow) {if (columnIndex >= firstColumn && columnIndex <= lastColumn) {isPartOfRegion = true;break;}}}Map<String, Object> map = new HashMap<String, Object>();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("isPartOfRowsRegion", isPartOfRowsRegion);map.put("firstRow", firstRow);map.put("lastRow", lastRow);map.put("width", width);map.put("height", height);return map;}
摸索过程
为什么有个256
Poi 设置宽度 是 / 256 这里 * 256。所以我们算行的时候 总内容的总宽度 除以 单元格的宽度(单元格宽度 * 256)了的。
问题产生:
发现是\n计算不准, \n占一个字节,其实后一行应该是占满了的。
所以需要重新计算。
加入这个代码后效果明显。
不白嫖一键三连!如有问题望指正!