欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Java POI Excel 行高自适应 博客分类: 编程相关Java相关 exceljava行高自适应POI 

程序员文章站 2024-03-24 10:55:28
...
  在Excel处理的过程中,可能有需要用到行高自适应的时候。

  下面贴出用POI实现Excel行高自适应的代码。

  该代码可以处理一行Excel按内容自适应高度。可以处理合并单元格。

  上代码:
/**
	 * 根据行内容重新计算行高
	 * @param row
	 */
	public static void calcAndSetRowHeigt(HSSFRow sourceRow) {
		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内容字符串总宽度
			double cellContentWidth = cellContent.getBytes().length * 2 * 256;
			
	        //字符串需要的行数 不做四舍五入之类的操作
	        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 = ToolKits.nulltoempty(String.valueOf(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;
	}