Java POI 讀取整個Sheet以及寫入值至cell和取得公式計算後結果

用到的jar有以下幾個

poi-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
commons-collections4-4.1.jar
xmlbeans-2.6.0.jar

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;

/**
 * @see <a href="https://poi.apache.org/components/spreadsheet/index.html">POI-HSSF and POI-XSSF/SXSSF - Java API To Access Microsoft Excel Format Files</a>
 */
public class Excel {
    private boolean isExcel2003 = false;
    private Workbook workbook;
    private Sheet sheet;


    public Excel() throws IOException {
        this(Excel.class.getResource("test.xlsx").getPath());
    }

    public Excel(String path) throws IOException {
        this.isExcel2003 = (path.matches("^.+\\.(?i)(xls)$")) ? true : false;

        FileInputStream fileInputStream = new FileInputStream(path);
        if (this.isExcel2003) {
            this.workbook = new HSSFWorkbook(fileInputStream);
        } else {
            //2007
            this.workbook = new XSSFWorkbook(fileInputStream);
        }
        this.selectSheet(0);
    }

    public Sheet selectSheet(int index) {
        if (index < 0 || index > (this.getSheetsCount() - 1)) return null;
        this.sheet = this.workbook.getSheetAt(index);
        return this.sheet;
    }

    public int getSheetsCount() {
        return this.workbook.getNumberOfSheets();
    }

    public void readAllCell(int index) {
        this.selectSheet(index);

        Iterator<Row> rowIterator = this.sheet.rowIterator();
        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();
                switch (cell.getCellTypeEnum()) {
                    case FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
                    case STRING:
                        System.out.println(cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        System.out.println(cell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    default:
                        break;
                }
            }
        }
    }

    public Object getCalculateRule(CellReference reference) {
        FormulaEvaluator evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
        Row row = this.sheet.getRow(reference.getRow());
        Cell cell = row.getCell(reference.getCol());
        CellValue value = evaluator.evaluate(cell);

        switch (value.getCellTypeEnum()) {
            case STRING:
                return value.getStringValue();
            case NUMERIC:
                return value.getNumberValue();
            case BOOLEAN:
                return value.getBooleanValue();
            default:
                return null;
        }
    }

    public void setValue(CellReference reference, Object v) {
        Row row = this.sheet.getRow(reference.getRow());
        if (row == null)
            row = sheet.createRow(reference.getRow());
        Cell cell = row.getCell(reference.getCol());
        if (v instanceof String)
            cell.setCellValue((String) v);
        else if (v instanceof Double)
            cell.setCellValue((Double) v);
        else if (v instanceof Boolean)
            cell.setCellValue((Boolean) v);
        else if (v instanceof Date)
            cell.setCellValue((Date) v);
        else if (v instanceof Calendar)
            cell.setCellValue((Calendar) v);
        else if (v instanceof RichTextString)
            cell.setCellValue((RichTextString) v);
    }

}