All Downloads are FREE. Search and download functionalities are using the official Maven repository.

com.ludii.excel.parse.AbstractExcelImportCellValueReader Maven / Gradle / Ivy

Go to download

excel读写封装基础库

There is a newer version: 1.1.0
Show newest version
package com.ludii.excel.parse;

import com.ludii.excel.exceptions.ExcelException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author 陆迪
 * @date 2022/3/24
 */
public abstract class AbstractExcelImportCellValueReader implements ExcelImportCellValueReader {

    protected final Logger log = LoggerFactory.getLogger(this.getClass());

    protected final static String EMPTY_STRING = "";

    @Override
    public List getHeaderValueList() {
        int headerColumnIndex = getHeaderColumnIndex();
        if (headerColumnIndex < 0) {
            throw new ExcelException("请设置正确的标题所在行");
        }

        Row row = this.getRow(headerColumnIndex);
        if (row == null) {
            throw new ExcelException("不存在的标题行");
        }

        List headerValueList = new ArrayList<>();
        short lastCellNum = row.getLastCellNum();
        for (int j = 0; j < lastCellNum; j++) {
            Object cellValue = this.getCellValue(row, j);
            headerValueList.add(Objects.toString(cellValue));
        }
        log.debug("标题行：{}", headerValueList);

        return headerValueList;
    }

    @Override
    public List> getCellValueListList() {
        List> cellValueListList = new ArrayList<>();

        int dataRowStartIndex = this.getDataRowStartIndex();
        int dataRowEndIndex = this.getDataRowEndIndex();
        for (int i = dataRowStartIndex; i <= dataRowEndIndex; i++) {
            Row row = getRow(i);
            if (row == null) {
                continue;
            }
            List cellValueList = new ArrayList<>();
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Object cellValue = this.getCellValue(row, j);
                cellValueList.add(cellValue);
            }
            cellValueListList.add(cellValueList);
        }

        return cellValueListList;
    }


    /**
     * 获取单元格值
     *
     * @param row         获取的行
     * @param columnIndex 获取单元格列号
     * @return 单元格值
     */
    private Object getCellValue(Row row, int columnIndex) {

        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            return EMPTY_STRING;
        }

        return getValueByCell(cell);
    }

    /**
     * 获取单元格的值
     *
     * @param cell 单元格
     * @return 返回值
     */
    private Object getValueByCell(Cell cell) {
        Object cellValue;
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = DateUtil.getJavaDate(cell.getNumericCellValue());
                } else {
                    cellValue = cell.getNumericCellValue();
                }
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case FORMULA:
                //公式
                FormulaEvaluator formulaEvaluator = getFormulaEvaluator();
                CellValue evaluatorCellValue = formulaEvaluator.evaluate(cell);
                cellValue = getValueByCellValue(evaluatorCellValue);
                break;
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case ERROR:
                byte errorCode = cell.getErrorCellValue();
                FormulaError errorMsg = FormulaError.forInt(errorCode);
                throw new ExcelException(String.format("读取单元格:第%s行、第%s列出现错误。错误信息：%s", cell.getRowIndex(), cell.getColumnIndex(), errorMsg));
            case _NONE:
            case BLANK:
            default:
                cellValue = EMPTY_STRING;
                break;
        }

        return cellValue;
    }

    /**
     * 获取公式计算后的值
     *
     * @param cellValue 公式计算后的值
     * @return 返回值
     */
    private Object getValueByCellValue(CellValue cellValue) {
        Object cellValueObject;
        CellType cellType = cellValue.getCellType();
        switch (cellType) {
            case NUMERIC:
                cellValueObject = cellValue.getNumberValue();
                break;
            case STRING:
                cellValueObject = cellValue.getStringValue();
                break;
            case BOOLEAN:
                cellValueObject = cellValue.getBooleanValue();
                break;
            case ERROR:
                byte errorCode = cellValue.getErrorValue();
                FormulaError errorMsg = FormulaError.forInt(errorCode);
                throw new ExcelException(String.format("读取单元格出现错误。错误信息：%s", errorMsg));
            case _NONE:
            case FORMULA:
            case BLANK:
            default:
                cellValueObject = EMPTY_STRING;
                break;
        }

        return cellValueObject;
    }

    /**
     * 获取工作表
     *
     * @return 返回值
     */
    protected abstract Sheet getSheet();

    /**
     * @return 标题所在行，从0开始计数
     */
    protected int getHeaderColumnIndex() {
        return getDataRowStartIndex() - 1;
    }

    /**
     * 数据开始行
     *
     * @return 数据开始的第一行，从0开始计算
     */
    protected abstract int getDataRowStartIndex();

    /**
     * @return 数据最后一行，从0开始计算
     */
    protected int getDataRowEndIndex() {
        return this.getSheet().getLastRowNum();
    }

    /**
     * 获取行号对应的行数据
     *
     * @param rowIndex 行号
     * @return 行数据
     */
    protected Row getRow(int rowIndex) {
        return getSheet().getRow(rowIndex);
    }

    /**
     * 获取公式计算器
     *
     * @return 公式计算器
     */
    protected abstract FormulaEvaluator getFormulaEvaluator();
}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy