合併 excel 檔案

MergeWorkSheet.java

import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * https://gist.github.com/davidsommer/7805574
 */
public class MergeWorkSheet {
  static Logger logger = LoggerFactory.getLogger(MergeWorkSheet.class);

  public static void main(String[] args) {
    XSSFWorkbook result = new XSSFWorkbook();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
    String outputFile = "result_" + sdf.format(new Date()) + ".xlsx";

    File folder = new File("xlsx");
    for (File file : folder.listFiles(new FileFilter() {
      public boolean accept(File pathname) {
        return pathname.getName().endsWith(".xlsx");
      }
    })) {
      FileInputStream fis = null;
      Workbook myWorkBook = null;
      try {
        fis = new FileInputStream(file);
        myWorkBook = WorkbookFactory.create(fis);
        Sheet oldSheet = myWorkBook.getSheetAt(0);
        String name = oldSheet.getRow(2).getCell(7).getStringCellValue();
        logger.info("Processing: {}, {}", file.getName(), name);
        
        XSSFSheet newSheet = result.createSheet(name);
        Util.copySheets(newSheet, oldSheet);
      } catch (Exception e) {
        logger.error("Process file fail.", e);
      } finally {
        if (myWorkBook != null)
          try {
            myWorkBook.close();
            myWorkBook = null;
          } catch (IOException ignore) {
          }
        if (fis != null)
          try {
            fis.close();
            fis = null;
          } catch (IOException ignore) {
          }
      }

    }

    try {
      logger.info("Output to file: {}", outputFile);
      writeFile(result, new File(outputFile));
    } catch (Exception e) {
      logger.error("Output fail.", e);
    }
    logger.info("Finish.");
  }

  protected static void writeFile(Workbook book, File file) throws Exception {
    FileOutputStream out = new FileOutputStream(file);
    book.write(out);
    out.close();
  }
}

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;
  }
}