Java POI操作Excel记录

1.在excel中新增行和单元格

首先在excel中指定行之后新增单元格，如果后面还有行数据的话，则需要先执行sheet.shiftRows()操作，将后面的数据往下移动需要新增行的行数。然后再执行sheet.createRow()和dstRow.createCell()方法。

2.复制单元格的值，以及复制之后的单元格上公式往下递增的操作

/**
* 拷贝单元格
* @param srcCell
* @param distCell
*/
private void copyCell(Sheet sheet, Cell srcCell, Cell distCell){
    if(null == srcCell){
        return;
    }
    if (srcCell.getCellStyle() != null) {
        distCell.setCellStyle(srcCell.getCellStyle());
    }
    if(srcCell.getCellComment() != null){
        distCell.setCellComment(srcCell.getCellComment());
    }
    int srcCellType=srcCell.getCellType();
    distCell.setCellType(srcCellType);
    switch (srcCellType){
        case Cell.CELL_TYPE_NUMERIC:
            if(DateUtil.isCellDateFormatted(srcCell)){
                distCell.setCellValue(srcCell.getDateCellValue());
            } else{
                distCell.setCellValue(srcCell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_STRING:
            distCell.setCellValue(srcCell.getRichStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            distCell.setCellValue(srcCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            // 拷贝单元格并且递增公式
            copyFormula(sheet, srcCell, distCell);
            break;
        case Cell.CELL_TYPE_ERROR:
            distCell.setCellErrorValue(srcCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        default:
            // do nothing
    }
}

/**
 * 拷贝公式并且公式在表格上依次递增下去
 * @param sheet
 * @param org
 * @param dest
 */
private void copyFormula(Sheet sheet, Cell org, Cell dest) {
    if (org == null || dest == null || sheet == null
            || org.getCellType() != Cell.CELL_TYPE_FORMULA)
        return;
    if (org.isPartOfArrayFormulaGroup())
        return;
    String formula = org.getCellFormula();
    int shiftRows = dest.getRowIndex() - org.getRowIndex();
    int shiftCols = dest.getColumnIndex() - org.getColumnIndex();

    Workbook workbook = sheet.getWorkbook();
    FormulaRenderingWorkbook workbookWrapper = null;
    Ptg[] ptgs = null;
    if(workbook instanceof XSSFWorkbook){
        XSSFEvaluationWorkbook xssfEvaluationWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());
        ptgs = FormulaParser.parse(formula, xssfEvaluationWorkbook, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet));
        workbookWrapper = xssfEvaluationWorkbook;
    }else if(workbook instanceof HSSFWorkbook){
        HSSFEvaluationWorkbook hssfEvaluationWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) sheet.getWorkbook());
        ptgs = FormulaParser.parse(formula, hssfEvaluationWorkbook, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet));
        workbookWrapper = hssfEvaluationWorkbook;
    }

    assert ptgs != null;
    for (Ptg ptg : ptgs) {
        if (ptg instanceof RefPtgBase) // base class for cell references
        {
            RefPtgBase ref = (RefPtgBase) ptg;
            if (ref.isColRelative())
                ref.setColumn(ref.getColumn() + shiftCols);
            if (ref.isRowRelative())
                ref.setRow(ref.getRow() + shiftRows);
        } else if (ptg instanceof AreaPtg) // base class for range references
        {
            AreaPtg ref = (AreaPtg) ptg;
            if (ref.isFirstColRelative())
                ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
            if (ref.isLastColRelative())
                ref.setLastColumn(ref.getLastColumn() + shiftCols);
            if (ref.isFirstRowRelative())
                ref.setFirstRow(ref.getFirstRow() + shiftRows);
            if (ref.isLastRowRelative())
                ref.setLastRow(ref.getLastRow() + shiftRows);
        }
    }
    assert workbookWrapper != null;
    formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
    dest.setCellFormula(formula);
}

3.新增完行之后，或者拷贝了旧行上的公式之后，打开excel公式未计算

可以对整个 Workbook.setForceFormulaRecalculation(true) 设置强制重计算 或者 对单个sheet页开启强制重新计算 Sheet.setForceFormulaRecalculation(true)

4.针对excel底表上执行公式，但是公式不计算的问题

需要注意的是：针对公式计算的单元格的类型都不能是字符串这种不能进行计算的类型，所以在写入需要针对要计算的单元格的设置对应的单元格格式。

private void dealCellType(Object value, Cell cell){
        if(null == value || StringUtils.isBlank(value.toString())){
            cell.setCellValue(StringUtils.EMPTY);
            cell.setCellType(Cell.CELL_TYPE_BLANK);
        } else if(isNumeric2(value.toString())){
            cell.setCellValue(Double.parseDouble(value.toString()));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        }else{
            cell.setCellValue(value.toString());
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
}

/**
* 判断是否是数字
*/
private static final Pattern NUMBER_PATTERN = Pattern.compile("-?\\d+(\\.\\d+)?");
public static boolean isNumeric2(String str) {
        return str != null && !StringUtils.isBlank(str) && 
    NUMBER_PATTERN.matcher(str).matches();
}