Java POI Excel sheet 合并遇到的问题解决

上接 Java POI Excel sheet
http://zhuyufufu.iteye.com/blog/2033386

在使用程序合并Excel的过程中可能会遇到下面两个问题：

  此文件中的某些文本格式可能已经更改，因为它已经超出最多允许字体数。关闭其他文档再试一次可能有用。

  文件错误。数据可能丢失。

  这两个问题都与样式的拷贝有关系：

//样式的设置
			    HSSFCellStyle cStyle = destWorkBook.createCellStyle();
			    cStyle.cloneStyleFrom(sourceCell.getCellStyle());
			    targetCell.setCellStyle(cStyle);

上面这段代码拷贝的样式生成的Excel文件在wps下是没问题的，但是在office下当合并单元格超过一定数量时就会出现上面的两个问题。

  具体的原因没有去深究，无非是office实现样式时做了什么限制，或者有什么情况没有考虑到。
  下面给出一种解决办法，就是不使用cloneStyleFrom拷贝样式，希望能够帮助到有需要的同行。

  代码如下：

/**
	 * 样式拷贝
	 * @param cStyle 目标style
	 * @param sourceCellStyle 源style
	 * @param sourceWorkBook 源workBook
	 * @return
	 */
	private static HSSFCellStyle copyCellStyle(HSSFCellStyle cStyle, HSSFCellStyle sourceCellStyle, HSSFWorkbook sourceWorkBook) {
		if(sourceCellStyle == null){
			return cStyle;
		}
		//是否换行
		cStyle.setWrapText(sourceCellStyle.getWrapText());
		//字体拷贝
		cStyle.setFont(sourceCellStyle.getFont(sourceWorkBook));
//		 cStyle.cloneStyleFrom(sourceCellStyle);
		//拷贝对齐方式
		cStyle.setAlignment(sourceCellStyle.getAlignment());
		cStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
		//边框拷贝
		cStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
		cStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
		cStyle.setBorderRight(sourceCellStyle.getBorderRight());
		cStyle.setBorderTop(sourceCellStyle.getBorderTop());
		cStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
		cStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
		cStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
		cStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor());
		//别的样式的拷贝
		return cStyle;
	}

改变后的上篇文章的代码如下：

public static HSSFWorkbook mergeHSSFWorkbooks(HSSFWorkbook[] workbooks) {
		if(workbooks == null || workbooks.length == 0){
			return null;
		}else if(workbooks.length == 1){
			return workbooks[0];
		}
		HSSFWorkbook wbFirst = workbooks[0];
		HSSFSheet toSheet = wbFirst.getSheetAt(0);
		for (int i = 1; i < workbooks.length; i++) {
			HSSFWorkbook wb = workbooks[i];
			HSSFSheet fromsheet = wb.getSheetAt(0);
			copyRows(wbFirst, wb, fromsheet, toSheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum(), (toSheet.getLastRowNum()));
		}
		return wbFirst;
	}
	
	/**
	 * @param destWorkBook 目标workbook
	 * @param sourceWorkBook 源workbook
	 * @param sourceSheet 源sheet
	 * @param targetSheet 目sheet
	 * @param pStartRow 起始读取行
	 * @param pEndRow 结束读取行
	 * @param pPosition 目标保存
	 */
	public static void copyRows(HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook, HSSFSheet sourceSheet,HSSFSheet targetSheet, int pStartRow, int pEndRow, int pPosition) {
		HSSFRow sourceRow = null;
		HSSFRow targetRow = null;
		HSSFCell sourceCell = null;
		HSSFCell targetCell = null;
		int cType;
		int i;
		int j;
		int targetRowFrom;
		int targetRowTo;

		if ((pStartRow == -1) || (pEndRow == -1)) {
			return;
		}

		List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
		for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
			oldRanges.add(sourceSheet.getMergedRegion(i));
		}

		// 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
		for (int k = 0; k < oldRanges.size(); k++) {
			CellRangeAddress oldRange = oldRanges.get(k);
			CellRangeAddress newRange = new CellRangeAddress(oldRange
					.getFirstRow(), oldRange.getLastRow(), oldRange
					.getFirstColumn(), oldRange.getLastColumn());

			if (oldRange.getFirstRow() >= pStartRow
					&& oldRange.getLastRow() <= pEndRow) {
				targetRowFrom = oldRange.getFirstRow() - pStartRow + pPosition;
				targetRowTo = oldRange.getLastRow() - pStartRow + pPosition;
				oldRange.setFirstRow(targetRowFrom);
				oldRange.setLastRow(targetRowTo);
				targetSheet.addMergedRegion(oldRange);
				sourceSheet.addMergedRegion(newRange);
			}
		}
		// 设置列宽
		for (i = pStartRow; i <= pEndRow; i++) {
			sourceRow = sourceSheet.getRow(i);
			if (sourceRow != null) {
				for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) {
					targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
					targetSheet.setColumnHidden(j, false);
				}
				break;
			}
		}
		// 拷贝行并填充数据
		for (; i <= pEndRow; i++) {
			sourceRow = sourceSheet.getRow(i);
			if (sourceRow == null) {
				continue;
			}
			targetRow = targetSheet.createRow(i - pStartRow + pPosition);
			targetRow.setHeight(sourceRow.getHeight());
			for (j = sourceRow.getFirstCellNum(); j <= sourceRow.getPhysicalNumberOfCells(); j++) {
				sourceCell = sourceRow.getCell(j);
				if (sourceCell == null) {
					continue;
				}
				targetCell = targetRow.createCell(j);
				//复制样式
				//样式的设置
				if(sourceWorkBook != null){
					HSSFCellStyle cStyle = destWorkBook.createCellStyle();
					cStyle = copyCellStyle(cStyle, sourceCell.getCellStyle(), sourceWorkBook);
					targetCell.setCellStyle(cStyle);
				}

				cType = sourceCell.getCellType();
				targetCell.setCellType(cType);
				switch (cType) {
				case HSSFCell.CELL_TYPE_BOOLEAN:
					targetCell.setCellValue(sourceCell.getBooleanCellValue());
					// System.out.println("--------TYPE_BOOLEAN:" + targetCell.getBooleanCellValue());
					break;
				case HSSFCell.CELL_TYPE_ERROR:
					targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
					// System.out.println("--------TYPE_ERROR:" + targetCell.getErrorCellValue());
					break;
				case HSSFCell.CELL_TYPE_FORMULA:
					// parseFormula这个函数的用途在后面说明
					targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
					// System.out.println("--------TYPE_FORMULA:" + targetCell.getCellFormula());
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					targetCell.setCellValue(sourceCell.getNumericCellValue());
					// System.out.println("--------TYPE_NUMERIC:" + targetCell.getNumericCellValue());
					break;
				case HSSFCell.CELL_TYPE_STRING:
					targetCell.setCellValue(sourceCell.getRichStringCellValue());
					// System.out.println("--------TYPE_STRING:" + i + targetCell.getRichStringCellValue());
					break;
				default:
					targetCell.setCellValue(sourceCell.getRichStringCellValue());
					// System.out.println("--------TYPE_STRING:" + i + targetCell.getRichStringCellValue());
					break;
				}
			}
		}
	}
	

	/**
	 * 样式拷贝
	 * @param cStyle 目标style
	 * @param sourceCellStyle 源style
	 * @param sourceWorkBook 源workBook
	 * @return
	 */
	private static HSSFCellStyle copyCellStyle(HSSFCellStyle cStyle, HSSFCellStyle sourceCellStyle, HSSFWorkbook sourceWorkBook) {
		if(sourceCellStyle == null){
			return cStyle;
		}
		//是否换行
		cStyle.setWrapText(sourceCellStyle.getWrapText());
		//字体拷贝
		cStyle.setFont(sourceCellStyle.getFont(sourceWorkBook));
//		 cStyle.cloneStyleFrom(sourceCellStyle);
		//拷贝对齐方式
		cStyle.setAlignment(sourceCellStyle.getAlignment());
		cStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
		//边框拷贝
		cStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
		cStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
		cStyle.setBorderRight(sourceCellStyle.getBorderRight());
		cStyle.setBorderTop(sourceCellStyle.getBorderTop());
		cStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
		cStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
		cStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
		cStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor());
		//别的样式的拷贝
		return cStyle;
	}

	/**
	 * 处理公式
	 * @param pPOIFormula
	 * @return
	 */
	private static String parseFormula(String pPOIFormula) {
		final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
		StringBuffer result = null;
		int index;
		result = new StringBuffer();
		index = pPOIFormula.indexOf(cstReplaceString);
		if (index >= 0) {
			result.append(pPOIFormula.substring(0, index));
			result.append(pPOIFormula.substring(index + cstReplaceString.length()));
		} else {
			result.append(pPOIFormula);
		}
		return result.toString();
	}