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

上接 Java POI Excel sheet 合并
http://zhuyufufu.iteye.com/blog/2033386
Java POI Excel sheet 合并问题解决
http://zhuyufufu.iteye.com/blog/2035033

上面的博文生成的拷贝Excel程序在Cell非常多的时候还会出问题

   Caused by: java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook

   解决的办法就是，每一种cell样式存储起来，共用。
  
   从中也可以看出来office Excel在实现样式的时候做了很多限制。

上具体的代码

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);
			if(i == 1){
				copyRows(wbFirst, wb, fromsheet, toSheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum(), (toSheet.getLastRowNum()));
			}else{
				copyRows(wbFirst, wb, fromsheet, toSheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum(), (toSheet.getLastRowNum() + 1));
			}
		}
		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;
			}
		}
		List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();
		// 拷贝行并填充数据
		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);
				//复制样式
				//样式的设置
				HSSFCellStyle cStyle = compareStoreAndGetCellStyle(cellStyleList, sourceCell.getCellStyle(), destWorkBook, 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;
				}
			}
		}
	}
	

	/**
	 * 比较存储并获得cellstyle
	 * @param cellStyleList
	 * @param cellStyle
	 * @param sourceWorkBook
	 * @return
	 */
	private static HSSFCellStyle compareStoreAndGetCellStyle(
			List<HSSFCellStyle> cellStyleList, 
			HSSFCellStyle sourceCellStyle,
			HSSFWorkbook destWorkBook,
			HSSFWorkbook sourceWorkBook) {
		for (int index = 0; index < cellStyleList.size(); index++) {
			HSSFCellStyle cellStyle = cellStyleList.get(index);
			if(isEqual(cellStyle, sourceCellStyle, destWorkBook, sourceWorkBook)){
				return cellStyle;
			}
		}
		//拷贝新的样式到列表
		HSSFCellStyle cStyle = destWorkBook.createCellStyle();
		cStyle = copyCellStyle(cStyle, sourceCellStyle, sourceWorkBook);
		cellStyleList.add(cStyle);
		return cStyle;
	}

	/**
	 * 两个cellStyle是否相同
	 * @param cellStyle
	 * @param sourceCellStyle
	 * @param sourceWorkBook 
	 * @param destWorkBook 
	 * @return
	 */
	private static boolean isEqual(HSSFCellStyle cellStyle,
			HSSFCellStyle sourceCellStyle, HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook) {
		//判断换行样式是否一样
		if(cellStyle.getWrapText() != sourceCellStyle.getWrapText()){
			return false;
		}
		//对齐方式是否一样
		if(cellStyle.getAlignment() != sourceCellStyle.getAlignment()){
			return false;
		}
		if(cellStyle.getVerticalAlignment()!= sourceCellStyle.getVerticalAlignment()){
			return false;
		}
		//边框是否一样
		if(cellStyle.getBorderBottom()!= sourceCellStyle.getBorderBottom()){
			return false;
		}
		if(cellStyle.getBorderLeft()!= sourceCellStyle.getBorderLeft()){
			return false;
		}
		if(cellStyle.getBorderRight()!= sourceCellStyle.getBorderRight()){
			return false;
		}
		if(cellStyle.getBorderTop()!= sourceCellStyle.getBorderTop()){
			return false;
		}
		if(cellStyle.getBottomBorderColor()!= sourceCellStyle.getBottomBorderColor()){
			return false;
		}
		if(cellStyle.getLeftBorderColor()!= sourceCellStyle.getLeftBorderColor()){
			return false;
		}
		if(cellStyle.getRightBorderColor()!= sourceCellStyle.getRightBorderColor()){
			return false;
		}
		if(cellStyle.getTopBorderColor()!= sourceCellStyle.getTopBorderColor()){
			return false;
		}
		//字体是否一样
		HSSFFont sourceFont = sourceCellStyle.getFont(sourceWorkBook);
		HSSFFont destFont = cellStyle.getFont(destWorkBook);
		if(destFont.getBoldweight() != sourceFont.getBoldweight()){
			return false;
		}
		if(destFont.getCharSet() != sourceFont.getCharSet()){
			return false;
		}
		if(destFont.getColor() != sourceFont.getColor()){
			return false;
		}
		if(destFont.getColor() != sourceFont.getColor()){
			return false;
		}
		if(destFont.getFontHeight() != sourceFont.getFontHeight()){
			return false;
		}
		if(destFont.getFontHeightInPoints() != sourceFont.getFontHeightInPoints()){
			return false;
		}
		if(destFont.getIndex() != sourceFont.getIndex()){
			return false;
		}
		if(destFont.getItalic() != sourceFont.getItalic()){
			return false;
		}
		if(destFont.getUnderline() != sourceFont.getUnderline()){
			return false;
		}
		if(destFont.getStrikeout() != sourceFont.getStrikeout()){
			return false;
		}
		if(!destFont.getFontName().equals(sourceFont.getFontName())){
			return false;
		}
		//别的样式是否一样
		return true;
	}

	/**
	 * 样式拷贝
	 * @param cStyle 目标style
	 * @param sourceCellStyle 源style
	 * @param sourceWorkBook 源workBook
	 * @return
	 */
	private static HSSFCellStyle copyCellStyle(HSSFCellStyle cStyle, HSSFCellStyle sourceCellStyle, HSSFWorkbook sourceWorkBook) {
		if(sourceCellStyle == null || cStyle == 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();
	}