POI Excel 多檔案工作表合併

Util.java

import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 *
 * http://jxls.cvs.sourceforge.net/jxls/jxls/src/java/org/jxls/util/Util.java?revision=1.8&
 * view=markup by Leonid Vysochyn and modified (adding styles copying)
 */
public class Util {
  public static void copySheets(Sheet newSheet, Sheet sheet) {
    copySheets(newSheet, sheet, true);
  }

  public static void copySheets(Sheet newSheet, Sheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>() : null;

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
      Row srcRow = sheet.getRow(i);
      Row destRow = newSheet.createRow(i);
      if (srcRow != null) {
        Util.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
        if (srcRow.getLastCellNum() > maxColumnNum) {
          maxColumnNum = srcRow.getLastCellNum();
        }
      }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
      newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
  }

  private static void copyRow(Sheet srcSheet, Sheet destSheet, Row srcRow, Row destRow,
      Map<Integer, CellStyle> styleMap) {
    destRow.setHeight(srcRow.getHeight());
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
      Cell oldCell = srcRow.getCell(j);
      Cell newCell = destRow.getCell(j);
      if (oldCell != null) {
        if (newCell == null) {
          newCell = destRow.createCell(j);
        }
        copyCell(oldCell, newCell, styleMap);
        CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), j);
        if (mergedRegion != null) {
          CellRangeAddress newMergedRegion =
              new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
                  mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
          if (!destSheet.getMergedRegions().contains(newMergedRegion)) {
            destSheet.addMergedRegion(newMergedRegion);
          }
        }
      }
    }

  }

  private static void copyCell(Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap) {
    if (styleMap != null) {
      if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
        newCell.setCellStyle(oldCell.getCellStyle());
      } else {
        int stHashCode = oldCell.getCellStyle().hashCode();
        CellStyle 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 Cell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
      case Cell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
      case Cell.CELL_TYPE_BLANK:
        newCell.setCellType(Cell.CELL_TYPE_BLANK);
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
      case Cell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
      case Cell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
      default:
        break;
    }

  }

  private static CellRangeAddress getMergedRegion(Sheet sheet, int rowNum, int cellNum) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
      CellRangeAddress merged = sheet.getMergedRegion(i);
      if (merged.getFirstRow() <= rowNum && merged.getLastRow() >= rowNum
          && merged.getFirstColumn() <= cellNum && merged.getLastColumn() >= cellNum) {
        return merged;
      }
    }
    return null;
  }
}