XlsUtil.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package ro.nextreports.engine.exporter.util;

import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-another
 */
public final class XlsUtil {
	
	private XlsUtil() {
	}

	/**
	 * Copy a sheet to another sheet at a specific (row, column) position
	 * 
	 * @param parentSheet the sheet to copy into
	 * @param parentSheetRow the row inside parentSheet where we start to copy
	 * @param parentSheetColumn the column inside parentSheet where we start to copy
	 * @param sheet the sheet that is copied
	 * @return column number
	 */
	public static int copyToSheet(HSSFSheet parentSheet, int parentSheetRow, int parentSheetColumn, HSSFSheet sheet) {
		return copyToSheet(parentSheet, parentSheetRow, parentSheetColumn, sheet, true);
	}

	/**
	 * Copy a sheet to another sheet at a specific (row, column) position
	 * 
	 * @param parentSheet the sheet to copy into
	 * @param parentSheetRow the row inside parentSheet where we start to copy
	 * @param parentSheetColumn the column inside parentSheet where we start to copy
	 * @param sheet the sheet that is copied
	 * @param copyStyle true to copy the style
	 * @return column number
	 */
	public static int copyToSheet(HSSFSheet parentSheet, int parentSheetRow, int parentSheetColumn, HSSFSheet sheet, boolean copyStyle) {
		int maxColumnNum = 0;
		Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
		for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
			HSSFRow srcRow = sheet.getRow(i);
			HSSFRow destRow;
			// subreport is not the first cell in a row
			if ((parentSheetColumn > 0) && (i == sheet.getFirstRowNum())) {
				destRow = parentSheet.getRow(parentSheetRow);
			} else {
				destRow = parentSheet.getRow(parentSheetRow+i);
				if (destRow == null) {
					destRow = parentSheet.createRow(parentSheetRow + i);
				}
			}
			if (srcRow != null) {
				XlsUtil.copyRow(sheet, parentSheet, parentSheetRow, parentSheetColumn, srcRow, destRow, styleMap);
				if (srcRow.getLastCellNum() > maxColumnNum) {
					maxColumnNum = srcRow.getLastCellNum();
				}
			}
		}
		for (int i = 0; i <= maxColumnNum; i++) {
			parentSheet.setColumnWidth(i, sheet.getColumnWidth(i));
		}
		return maxColumnNum;
	}

	/**
	 * Copy a row from a sheet to another sheet
	 * 
	 * 
	 * @param srcSheet the sheet to copy
	 * @param destSheet the sheet to copy into
	 * @param parentSheetRow the row inside destSheet where we start to copy
	 * @param parentSheetColumn the column inside destSheet where we start to copy
	 * @param srcRow the row to copy
	 * @param destRow the row to create
	 * @param styleMap style map
	 *       
	 */
	public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, int parentSheetRow, int parentSheetColumn, HSSFRow srcRow, HSSFRow destRow,
			Map<Integer, HSSFCellStyle> styleMap) {
		// manage a list of merged zone in order to not insert two times a
		// merged zone
		Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
		destRow.setHeight(srcRow.getHeight());
		// pour chaque row
		for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
			HSSFCell oldCell = srcRow.getCell(j); // ancienne cell			
			if (oldCell != null) {				
				HSSFCell newCell = destRow.createCell(parentSheetColumn + j);				
				copyCell(oldCell, newCell, styleMap);
				
				CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

				if (mergedRegion != null) {
					
					CellRangeAddress newMergedRegion = new CellRangeAddress(parentSheetRow + mergedRegion.getFirstRow(),
							parentSheetRow + mergedRegion.getLastRow(), 
							parentSheetColumn + mergedRegion.getFirstColumn(), 
							parentSheetColumn + mergedRegion.getLastColumn());
					
					CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
					if (isNewMergedRegion(wrapper, mergedRegions)) {
						mergedRegions.add(wrapper);
						destSheet.addMergedRegion(wrapper.range);
					}
				}
			}
		}

	}

	/**
	 * Copy a cell to another cell
	 * 
	 * @param oldCell cell to be copied
	 * @param newCell cell to be created
	 * @param styleMap style map
	 */
	public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
		if (styleMap != null) {			
			if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
				newCell.setCellStyle(oldCell.getCellStyle());
			} else {				
				int stHashCode = oldCell.getCellStyle().hashCode();
				HSSFCellStyle newCellStyle = styleMap.get(stHashCode);				
				if (newCellStyle == null) {					
					newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
					newCellStyle.cloneStyleFrom(oldCell.getCellStyle());					
					styleMap.put(stHashCode, newCellStyle);
				}
				newCell.setCellStyle(newCellStyle);
			}			
		}
		switch (oldCell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			newCell.setCellValue(oldCell.getStringCellValue());
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			newCell.setCellValue(oldCell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			newCell.setCellValue(oldCell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			newCell.setCellErrorValue(oldCell.getErrorCellValue());
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			newCell.setCellFormula(oldCell.getCellFormula());
			break;
		default:
			break;
		}

	}
	
	public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) {
		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			CellRangeAddress merged = sheet.getMergedRegion(i);
			if (merged.isInRange(rowNum, cellNum)) {
				return merged;
			}
		}
		return null;
	}

	/**
	 * Check that the merged region has been created in the destination sheet.
	 * 
	 * @param newMergedRegion the merged region to copy or not in the destination sheet.
	 * @param mergedRegions the list containing all the merged region.
	 * @return true if the merged region is already in the list or not.
	 */
	private static boolean isNewMergedRegion(CellRangeAddressWrapper newMergedRegion, Set<CellRangeAddressWrapper> mergedRegions) {
		return !mergedRegions.contains(newMergedRegion);
	}

}