apache-poi copy example FormulaEvaluator

 FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workBook);

 

formulaEvaluator.evaluateFormulaCell(currentCell);

 

api:

http://poi.apache.org/apidocs/index.html

 

Workbook workbook = new HSSFWorkbook(InputStream);
   Sheet sheet = workbook.createSheet();
   workbook.getSheetAt(0);
   Row row = sheet.createRow(0);
   Cell cell = row.createCell(0);
   CellStyle style = workbook.createCellStyle()

   cell.setCellStyle(style);
   cell.setCellType(Cell.CELL_TYPE_STRING);
   cell.setCellValue("");
   
   row.removeCell(cell);
   sheet.removeRow(row);
   workbook.removeSheetAt(0);
   workbook.setSheetHidden(0, 2);
   
   OutputStream os = new FileOutputStream(getFileName(report));
   workbook.write(os);

=============================================================

public void copySheets(Workbook sourceWorkBook, Workbook destWorkBook, boolean copyStyle) throws Exception{
  String[] includeSheets = getIncludeSheets(sourceWorkBook, null);
  copySheets(sourceWorkBook, destWorkBook, includeSheets, copyStyle);
 }

 

private void copySheets(Workbook sourceWorkBook, Workbook destWorkBook, String[] includeSheets, boolean copyStyle) throws Exception{
  if(sourceWorkBook == null){
   logger.error("sourceWorkBook is null.");
   throw new Exception("sourceWorkBook is null");
  }
  
  if(destWorkBook == null){
   logger.error("destWorkBook is null.");
   throw new Exception("destWorkBook is null");
  }
  
  if(includeSheets == null || includeSheets.length == 0){
   logger.error("No sheet to copy.");
   throw new Exception("No sheet to copy.");
  }
  
  int numSheets = sourceWorkBook.getNumberOfSheets();

  for(int i=0;i<numSheets; i++){
   Sheet sourceSheet = sourceWorkBook.getSheetAt(i);
   String sheetName = sourceSheet.getSheetName();
   if(sheetName != null){
    for(int k=0; k<includeSheets.length; k++){
     if(sheetName.equals(includeSheets[k])){
      logger.info("Copy matched sheet:" + includeSheets[k]);
      Sheet destSheet =destWorkBook.createSheet(sheetName);
      copySheet(sourceSheet,destSheet, copyStyle);
      break;
     }
    }
   }else{
    logger.info("Sheet name at " + i + " is null.");
   } 
  }
  int numSheets2 = destWorkBook.getNumberOfSheets();
  logger.info("Total Number of Sheets after Copy: " + numSheets2);
 }

 

 

 

private void copySheet(Sheet sheet, Sheet newSheet, boolean copyStyle) throws Exception{
  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);
   if (srcRow != null) {
    Row destRow = newSheet.createRow(i);
    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 void copyRow(Sheet srcSheet, Sheet destSheet, Row srcRow,
   Row destRow, Map<Integer, CellStyle> styleMap) throws Exception {
  Set mergedRegions = new TreeSet();
  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);
    }
    CellRangeAddress mergedRegion = getMergedRegion(srcSheet,srcRow.getRowNum(), oldCell.getColumnIndex());
    if (mergedRegion != null) {
//     CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
//     mergedRegion.getFirstColumn(),
//     mergedRegion.getLastRow(),
//     mergedRegion.getLastColumn());
     if(destSheet instanceof HSSFSheet){
      Region newMergedRegion = new Region(mergedRegion.getFirstRow(),
        (short)mergedRegion.getFirstColumn(),
        mergedRegion.getLastRow(),
        (short)mergedRegion.getLastColumn());
      if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
       mergedRegions.add(newMergedRegion);
       ((HSSFSheet) destSheet).addMergedRegion(newMergedRegion);
      }
     }else if(destSheet instanceof XSSFSheet){
//      org.apache.poi.hssf.util.CellRangeAddress newMergedRegion = new org.apache.poi.hssf.util.CellRangeAddress(mergedRegion.getFirstRow(),
//                   mergedRegion.getFirstColumn(),
//                   mergedRegion.getLastRow(),
//                   mergedRegion.getLastColumn());
//      if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
//       mergedRegions.add(newMergedRegion);
//       ((XSSFSheet)destSheet).addMergedRegion(newMergedRegion);
//      }
      String errorMsg = "Do not support merge for xlsx format now!!!";
      logger.error(errorMsg);
      throw new Exception(errorMsg);
     }
    }
    
    copyCell(oldCell, newCell, styleMap);
   }
  }
 }

 

 

 

private void copyCell(Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap) {
  try{
   setCellStyle(oldCell, newCell, styleMap);
   
   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:
    if(true == copyValueOfFormular){
     copyCellFormularValue(oldCell, newCell);
    }else{
     newCell.setCellFormula(oldCell.getCellFormula());
    }
    break;
   default:
    break;
   }
  }catch(Exception e){
   logger.error(e);
  }
 }

 

private void setCellStyle(Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap){
  try{
   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);
    }
   }
  }catch(Exception e){
   logger.error(e);
  }
 }

 

private void copyCellFormularValue(Cell oldCell, Cell newCell){
  try {
   newCell.setCellValue(oldCell.getNumericCellValue());
  } catch (Exception e1) {
   try{
    newCell.setCellValue(oldCell.getStringCellValue());
   }catch (Exception e2){
    try {
     newCell.setCellValue(oldCell.getErrorCellValue());
    } catch (Exception e) {
     newCell.setCellValue(oldCell.getBooleanCellValue());
    }
   }
  }
 }

 

 

 

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

 

 private boolean isNewMergedRegion(Region region, Collection mergedRegions) {  
        return !mergedRegions.contains(region);  
    }