javacommon.excel.ExcelReader.java Source code

Java tutorial

Introduction

Here is the source code for javacommon.excel.ExcelReader.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package javacommon.excel;

import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.usermodel.Workbook;

/**
 * Excel ? ?03?07Excel
 *
 * @author xc
 */
public class ExcelReader implements Reader {

    /**
     * Excel?
     */
    private Workbook workbook;
    /**
     * 
     */
    private int sheets;

    public ExcelReader(Workbook workbook) {
        this.workbook = workbook;
        init();
    }

    /**
     * ?
     *
     * @return
     */
    public int getSheets() {
        return sheets;
    }

    /**
     * ?ExcelSheet
     *
     * @return
     */
    public int getTotalRows() {
        int total = 0;
        for (int i = 0; i < sheets; i++) {
            total += getTotalRows(i);
        }
        return total;
    }

    /**
     * ??
     *
     * @param sheetIndex 0
     * @return
     */
    public int getTotalRows(int sheetIndex) {
        if (sheetIndex < sheets) {
            return workbook.getSheetAt(sheetIndex).getLastRowNum() + 1;
        }
        return 0;
    }

    /**
     * 
     * @param sheetIndex 
     * @param row  
     * @return 
     */
    public int getMaxColumn(int sheetIndex, int row) {
        int cols = 0;
        if (sheetIndex < getSheets() && row < getTotalRows(sheetIndex)) {
            return workbook.getSheetAt(sheetIndex).getRow(row).getLastCellNum() + 1;
        }
        return cols;
    }

    /**
     * ?
     *
     * @param sheetIndex
     * @return
     */
    public int getMaxColumn(int sheetIndex) {
        if (sheetIndex >= sheets) {
            return 0;
        }
        int maxCol = 0;
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int maxRows = sheet.getLastRowNum();
        int cols = 0;
        Row row = null;
        for (int i = 0; i < maxRows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                cols = row.getLastCellNum();
                if (cols > maxCol) {
                    maxCol = cols;
                }
            }
        }
        return maxCol + 1;
    }

    /**
     * ?
     *
     * @param sheetIndex
     * @param rowIndex
     * @return
     */
    public boolean isBlankRow(int sheetIndex, int rowIndex) {
        Row row = getRow(sheetIndex, rowIndex);
        if (row == null) {
            return true;
        }
        int cols = row.getLastCellNum(); //
        boolean isBlank = true;//?
        String str = null;
        for (int i = 0; i <= cols; i++) {
            str = getCellStringValue(row.getCell(i));
            if (str != null && str.trim().length() > 0) {
                isBlank = false;
                break;
            }
        }
        return isBlank;
    }

    /**
     * ?
     * @param sheetIndex
     * @return 
     */
    public List<Integer> getColumnWidths(int sheetIndex) {
        int rowCount = getTotalRows(sheetIndex);
        if (rowCount > 0) {
            Row row = workbook.getSheetAt(sheetIndex).getRow(0);
            int cols = row.getLastCellNum(); //
            List<Integer> result = new ArrayList<Integer>(cols);
            for (int i = 0; i < cols; i++) {
                result.add(getColumnWidth(sheetIndex, i));
            }
            return result;
        }
        return null;
    }

    /**
     * ?
     * @param sheetIndex
     * @param columnIndex
     * @return 
     */
    public int getColumnWidth(int sheetIndex, int columnIndex) {
        return workbook.getSheetAt(sheetIndex).getColumnWidth(columnIndex) * 8 / 256;
    }

    /**
     * ?Excel? Null
     *
     * @param sheetIndex 0
     * @param rowIndex 0
     * @return ?
     */
    public List<String> getRowStingData(int sheetIndex, int rowIndex) {
        List<String> result = new Vector<String>();
        Row row = getRow(sheetIndex, rowIndex);
        if (row == null) {
            return null;
        }
        int cols = row.getLastCellNum(); //
        boolean isBlank = true;//?
        String str = null;
        for (int i = 0; i < cols; i++) {
            str = getCellStringValue(row.getCell(i));
            if (str != null && str.trim().length() > 0) {
                isBlank = false;
            }
            result.add(str);
        }
        return isBlank ? null : result;
    }

    /**
     * ?Excel?
     *
     * @param sheetIndex 0
     * @param rowIndex 0
     * @param colBegin 0
     * @param colEnd 0
     * @return ?
     */
    public List<String> getRowStingData(int sheetIndex, int rowIndex, int colBegin, int colEnd) {
        List<String> result = new Vector<String>();
        Row row = getRow(sheetIndex, rowIndex);
        if (row == null) {
            return null;
        }
        int cols = row.getLastCellNum(); //
        if (colEnd > cols)
            colEnd = cols;
        String str = null;
        int i = colBegin;
        int emptyCount = 0;
        for (i = 0; i <= colEnd; i++) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                str = getCellStringValue(cell);
                if ("".equals(str)) {
                    emptyCount++;
                }
            } else {
                emptyCount++;
                str = "";
            }
            result.add(str);
        }
        if (emptyCount == colEnd + 1) {
            return null;
        }
        return result;
    }

    /**
     * ?Excel? Null
     *
     * @param sheetIndex 0
     * @param rowIndex 0
     * @return ?
     */
    public List<Object> getRowData(int sheetIndex, int rowIndex) {
        Row row = getRow(sheetIndex, rowIndex);

        if (row == null) {
            return null;
        }
        List<Object> result = new Vector<Object>();
        int cols = row.getLastCellNum(); //
        boolean isBlank = true; //?
        Object obj = null;
        for (int i = 0; i <= cols; i++) {
            obj = getCellValue(row.getCell(i));
            result.add(obj);
            if (obj != null) {
                isBlank = false;
            }
        }
        return isBlank ? null : result;
    }

    /**
     * ???
     *
     * @param sheetIndex 0
     * @param rowIndex 0
     * @param columnIndex 0
     * @return
     */
    public Object getCellValue(int sheetIndex, int rowIndex, int columnIndex) {
        Row row = getRow(sheetIndex, rowIndex);
        if (row != null && columnIndex <= row.getLastCellNum()) {
            return getCellValue(row.getCell(columnIndex));
        }
        return null;
    }

    /**
     * ???
     *
     * @param sheetIndex 0
     * @param rowIndex 0
     * @param columnIndex 0
     * @return
     */
    public String getCellStringValue(int sheetIndex, int rowIndex, int columnIndex) {
        Row row = getRow(sheetIndex, rowIndex);
        if (row != null && columnIndex <= row.getLastCellNum()) {
            return getCellStringValue(row.getCell(columnIndex));
        }
        return null;
    }

    /**
     * ?Excel
     *
     * @param in
     */
    private void init() {
        sheets = this.workbook.getNumberOfSheets();
    }

    /**
     * ???
     *
     * @param c ?
     * @return
     */
    private String getCellStringValue(Cell c) {
        if (c == null) {
            return "";
        }
        String value = null;
        NumberFormat nf = NumberFormat.getInstance();
        nf.setGroupingUsed(false);
        nf.setMaximumFractionDigits(12);
        switch (c.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            value = String.valueOf(c.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(c)) {
                return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue());
            } else if ("@".equals(c.getCellStyle().getDataFormatString())) {
                value = nf.format(c.getNumericCellValue());
            } else if ("General".equals(c.getCellStyle().getDataFormatString())) {
                value = nf.format(c.getNumericCellValue());
            } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) {
                value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()),
                        c.getCellStyle().getDataFormatString());
            } else {
                value = nf.format(c.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_STRING:
            value = c.getStringCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            value = c.getCellFormula();
            break;
        }
        return value == null ? "" : value.trim();
    }

    /**
     * ?Excel? Null
     *
     * @param sheetIndex 0
     * @param rowIndex 0
     * @return ?
     */
    public List<String> getRowStingFormatData(int sheetIndex, int rowIndex) {
        List<String> result = new Vector<String>();
        Row row = getRow(sheetIndex, rowIndex);
        if (row == null) {
            return null;
        }
        int cols = row.getLastCellNum(); //
        boolean isBlank = true;//?
        String str = null;
        for (int i = 0; i < cols; i++) {
            str = getCellStringFormatValue(row.getCell(i));
            if (str != null && str.trim().length() > 0) {
                isBlank = false;
            }
            result.add(str);
        }
        return isBlank ? null : result;
    }

    /**
     * ???
     *
     * @param c ?
     * @return
     */
    private String getCellStringFormatValue(Cell c) {
        if (c == null) {
            return "";
        }
        String value = null;
        NumberFormat nf = NumberFormat.getInstance();
        nf.setGroupingUsed(false);
        nf.setMaximumFractionDigits(12);
        switch (c.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            value = String.valueOf(c.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(c)) {
                return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue());
            } else if ("@".equals(c.getCellStyle().getDataFormatString())) {
                value = nf.format(c.getNumericCellValue());
            } else if ("General".equals(c.getCellStyle().getDataFormatString())) {
                value = nf.format(c.getNumericCellValue());
            } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) {
                value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()),
                        c.getCellStyle().getDataFormatString());
            } else {
                value = nf.format(c.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_STRING:
            value = c.getStringCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            return c.getCellFormula();
        }
        return value == null ? "" : value.trim();
    }

    /**
     * ???
     *
     * @param c ?
     * @return
     */
    private Object getCellValue(Cell c) {
        if (c == null) {
            return null;
        }
        switch (c.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            return null;
        case Cell.CELL_TYPE_BOOLEAN:
            return c.getBooleanCellValue();
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(c)) {
                return c.getDateCellValue();
            }
            return c.getNumericCellValue();
        case Cell.CELL_TYPE_STRING:
            return c.getStringCellValue();
        case Cell.CELL_TYPE_FORMULA:
            return c.getCellFormula();
        }
        return null;
    }

    /**
     * ?Excel
     *
     * @param sheetIndex 0
     * @param rowIndex 0
     * @return
     */
    private Row getRow(int sheetIndex, int rowIndex) {
        if (sheetIndex < sheets) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (rowIndex <= sheet.getLastRowNum()) {
                return sheet.getRow(rowIndex);
            }
        }
        return null;
    }

}