Apache POI Utilities

PoiUtils.java

package jca02266;

import java.util.Date;

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

public class PoiUtils {
  public static void printSheetInfo(Sheet sheet) {
    System.out.println("sheetName=" + sheet.getSheetName());
    System.out.println(String.format("[firstRow=%d, lastRow=%d]", sheet.getFirstRowNum(), sheet.getLastRowNum()));
  }

  public static Object getCellValue(Cell srcCell) {
    switch (srcCell.getCellType()) {
      case _NONE:
        return null;
      case BLANK:
        return "";
      case BOOLEAN: {
        Boolean val = srcCell.getBooleanCellValue();
        return val;
      }
      case ERROR:
        return "#ERROR!";
      case FORMULA: {
        String val = "=" + srcCell.getCellFormula();
        return val;
      }
      case NUMERIC:
        if (DateUtil.isCellDateFormatted(srcCell)) {
          Date val = srcCell.getDateCellValue();
          return val;
        } else {
          double val = srcCell.getNumericCellValue();
          return val;
        }
      case STRING: {
        String val = srcCell.getStringCellValue();
        return val;
      }
      default:
        throw new IllegalArgumentException(
          String.format("The value of cell %s(type: %s) is invalid", srcCell.getAddress(), srcCell.getCellType()));
    }
  }

  public static void setCellValue(Cell destCell, Object value) {
    if (value == null) {
      // do nothing
      return;
    }
    if (value instanceof String) {
      String str = (String)value;
      if (str.startsWith("=")) {
        destCell.setCellFormula(str.substring(1));
        return;
      }
      destCell.setCellValue(str);
      return;
    }
    if (value instanceof Boolean) {
      destCell.setCellValue((boolean)value);
    }
    if (value instanceof Date) {
      destCell.setCellValue((Date)value);
      return;
    }
    if (value instanceof Double) {
      destCell.setCellValue((double)value);
      return;
    }
  }

  public static void copyRange(Sheet srcSheet, Sheet destSheet, CellRangeAddress srcRange) {
    copyRange(srcSheet, destSheet, srcRange, srcRange);
  }

  public static void copyRange(Sheet srcSheet, Sheet destSheet, CellRangeAddress srcRange, CellRangeAddress destRange) {
    int rowOffset = destRange.getFirstRow();
    int columnOffset = destRange.getFirstColumn();
    for (int rowIndex = srcRange.getFirstRow(); rowIndex <= srcRange.getLastRow(); rowIndex++) {
      Row srcRow = CellUtil.getRow(rowIndex, srcSheet);
      Row destRow = CellUtil.getRow(rowIndex + rowOffset, destSheet);
      for (int columnIndex = srcRange.getFirstColumn(); columnIndex <= srcRange.getLastColumn(); columnIndex++) {
        Cell srcCell = CellUtil.getCell(srcRow, columnIndex);
        Cell destCell = CellUtil.getCell(destRow, columnIndex + columnOffset);
        PoiUtils.setCellValue(destCell, PoiUtils.getCellValue(srcCell));
        destCell.setCellStyle(srcCell.getCellStyle());
      }
    }
  }
}