搬砖 - java读取Excel

package com.xf.common.myutils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class ReadExcelUtil {
    private Workbook workbook = null;
    private Sheet sheet;
    private Row row;
    private int sheetIndex = 0;
    private int rowIndex = 0;
    private int cellIndex = 0;
    private String file;

    public static ReadExcelUtil read(String file) throws IOException, InvalidFormatException {
        return new ReadExcelUtil(file);
    }

    public static ReadExcelUtil read(File file) throws IOException, InvalidFormatException {
        return new ReadExcelUtil(file.getAbsolutePath());
    }

    public ReadExcelUtil(String pathname) throws IOException, InvalidFormatException {
        InputStream in = new FileInputStream(pathname);
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        try {
            this.workbook = new HSSFWorkbook(in);
        } catch (Exception e) {
            this.workbook = new XSSFWorkbook(OPCPackage.open(in));
        }
        if (this.workbook == null) {
            if (!judgeIsCSV(pathname)) {
                throw new IllegalArgumentException("你的excel版本目前poi解析不了");
            }
            setFile(pathname);
        } else {
            setSheetIndex(0);
        }
    }

    public static void main(String[] args) throws IOException {
        try {
            String pathname = "C:\\Users\\huanglisong\\Desktop\\cb2b\\GeoIPCountryWhois.csv";

            List<ArrayList<String>> list = read(pathname).setSheetIndex(0).readAllByTitle(0);
            for (ArrayList<String> rowArray : list) {
                for (String string : rowArray) {
                    System.out.print(string + "\t");
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean judgeIsCSV(String fileName) {
        return fileName.endsWith(".csv");
    }

    public List<ArrayList<String>> readCoustom() throws FileNotFoundException, IOException {
        List<ArrayList<String>> excelData = new ArrayList<>();
        for (int i = this.rowIndex; i < this.sheet.getPhysicalNumberOfRows(); i++) {
            ArrayList<String> rowArr = new ArrayList<>();
            this.row = this.sheet.getRow(i);
            for (int j = this.cellIndex; j < this.row.getPhysicalNumberOfCells(); j++) {
                rowArr.add(getStringCellValue(this.row.getCell(j)));
            }
            excelData.add(rowArr);
        }
        return excelData;
    }

    public List<ArrayList<String>> readCoustomByTitle(int titleIndex) throws FileNotFoundException, IOException {
        Row titleRow = this.sheet.getRow(titleIndex);
        List<ArrayList<String>> excelData = new ArrayList<>();
        for (int i = this.rowIndex; i < this.sheet.getPhysicalNumberOfRows(); i++) {
            ArrayList<String> rowArr = new ArrayList<>();
            this.row = this.sheet.getRow(i);
            for (int j = this.cellIndex; j < titleRow.getPhysicalNumberOfCells(); j++) {
                rowArr.add(getStringCellValue(this.row.getCell(j)));
            }
            excelData.add(rowArr);
        }
        return excelData;
    }

    public List<ArrayList<String>> readCoustomExcludeNull() throws FileNotFoundException, IOException {
        List<ArrayList<String>> excelData = new ArrayList<>();
        for (int i = this.rowIndex; i < this.sheet.getPhysicalNumberOfRows(); i++) {
            ArrayList<String> rowArr = new ArrayList<>();
            this.row = this.sheet.getRow(i);
            for (int j = this.cellIndex; j < this.row.getPhysicalNumberOfCells(); j++) {
                String cellValue = getStringCellValue(this.row.getCell(j));
                if (!cellValue.equals("")) {
                    rowArr.add(cellValue);
                }
            }
            excelData.add(rowArr);
        }
        return excelData;
    }

    public List<ArrayList<String>> readAll() throws FileNotFoundException, IOException {
        List<ArrayList<String>> excelData = new ArrayList<>();
        for (int i = 0; i < this.sheet.getPhysicalNumberOfRows(); i++) {
            ArrayList<String> rowArr = new ArrayList<>();
            this.row = this.sheet.getRow(i);
            for (int j = 0; j < this.row.getPhysicalNumberOfCells(); j++) {
                rowArr.add(getStringCellValue(this.row.getCell(j)));
            }
            excelData.add(rowArr);
        }
        return excelData;
    }

    public List<ArrayList<String>> readAllByTitle(int titleIndex) throws FileNotFoundException, IOException {
        Row titleRow = this.sheet.getRow(titleIndex);
        System.out.println("titleRow.getPhysicalNumberOfCells() : " + titleRow.getPhysicalNumberOfCells());
        List<ArrayList<String>> excelData = new ArrayList<>();
        for (int i = 0; i < this.sheet.getPhysicalNumberOfRows(); i++) {
            ArrayList<String> rowArr = new ArrayList<>();
            this.row = this.sheet.getRow(i);
            for (int j = 0; j < titleRow.getPhysicalNumberOfCells(); j++) {
                rowArr.add(getStringCellValue(this.row.getCell(j)));
            }
            excelData.add(rowArr);
        }
        return excelData;
    }

    public List<String> readTitle(int titleIndexStart, int titleIndexEnd) {
        List<String> rowCell = new ArrayList<>();
        for (int i = 0; i < titleIndexEnd - titleIndexStart; i++) {
            Row titleRow = this.sheet.getRow(titleIndexStart);
            for (int j = 0; j < titleRow.getPhysicalNumberOfCells(); j++) {
                //Cell c = titleRow.getCell(j);
                //CellRangeAddress cra = c.getArrayFormulaRange();
                rowCell.add(getStringCellValue(titleRow.getCell(j)));
            }
        }
        return rowCell;
    }

    public static boolean isMergedRegion(HSSFSheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);

            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if ((row >= firstRow) && (row <= lastRow) && (column >= firstColumn) && (column <= lastColumn)) {
                return true;
            }
        }
        return false;
    }

    public JSONArray readNextJsonByTitle(int titleIndex) throws FileNotFoundException, IOException {
        Row titleRow = this.sheet.getRow(titleIndex);
        List<String> rowCell = new ArrayList<>();
        for (int i = 0; i < titleRow.getPhysicalNumberOfCells(); i++) {
            rowCell.add(getStringCellValue(titleRow.getCell(i)));
        }
        JSONArray array = new JSONArray();
        JSONObject object = null;
        for (int i = titleIndex + 1; i < this.sheet.getPhysicalNumberOfRows(); i++) {
            object = new JSONObject();
            this.row = this.sheet.getRow(i);
            for (int j = 0; j < titleRow.getPhysicalNumberOfCells(); j++) {
                if(this.row != null && this.row.getCell(j) != null) {
                    if(StringUtils.isNotEmpty(getStringCellValue(this.row.getCell(j)))) {
                        object.put(rowCell.get(j), getStringCellValue(this.row.getCell(j)));
                    }
                }
            }
            if(object.size() != 0) {
                array.add(object);
            }
        }
        return array;
    }

    public List<ArrayList<String>> readNextByTitle(int titleIndex) throws FileNotFoundException, IOException {
        Row titleRow = this.sheet.getRow(titleIndex);
        List<ArrayList<String>> excelData = new ArrayList<>();
        for (int i = titleIndex + 1; i < this.sheet.getPhysicalNumberOfRows(); i++) {
            ArrayList<String> rowArr = new ArrayList<>();
            this.row = this.sheet.getRow(i);
            for (int j = 0; j < titleRow.getPhysicalNumberOfCells(); j++) {
                rowArr.add(getStringCellValue(this.row.getCell(j)));
            }
            excelData.add(rowArr);
        }
        return excelData;
    }

    public List<ArrayList<String>> readAllExcludeNull() throws FileNotFoundException, IOException {
        List<ArrayList<String>> excelData = new ArrayList<>();
        for (int i = 0; i < this.sheet.getPhysicalNumberOfRows(); i++) {
            ArrayList<String> rowArr = new ArrayList<>();
            this.row = this.sheet.getRow(i);
            for (int j = 0; j < this.row.getPhysicalNumberOfCells(); j++) {
                String cellValue = getStringCellValue(this.row.getCell(j));
                if (!cellValue.equals("")) {
                    rowArr.add(cellValue);
                }
            }
            excelData.add(rowArr);
        }
        return excelData;
    }

    public ArrayList<String> readRowData(int rowIndex) throws FileNotFoundException, IOException {
        ArrayList<String> rowDataArr = new ArrayList<>();
        this.row = this.sheet.getRow(rowIndex);
        for (int i = 0; i < this.row.getPhysicalNumberOfCells(); i++) {
            String cellStr = getStringCellValue(this.row.getCell(i));
            rowDataArr.add(cellStr);
        }
        return rowDataArr;
    }

    public ArrayList<String> readRowExcludeNullData(int rowIndex) throws FileNotFoundException, IOException {
        ArrayList<String> rowDataArr = new ArrayList<>();
        this.row = this.sheet.getRow(rowIndex);
        for (int i = 0; i < this.row.getPhysicalNumberOfCells(); i++) {
            String cellValue = getStringCellValue(this.row.getCell(i));
            if (!cellValue.equals("")) {
                rowDataArr.add(cellValue);
            }
        }
        return rowDataArr;
    }

    public String readCellData(int rowIndex, int cellIndex) throws FileNotFoundException, IOException {
        this.row = this.sheet.getRow(rowIndex);
        return getStringCellValue(this.row.getCell(cellIndex));
    }

    public List<? extends PictureData> readPictures() {
        return this.workbook.getAllPictures();
    }

    public static String getStringCellValue(Cell cell) {
        String strCell = "";
        if (cell == null) {
            return strCell;
        }
        switch (cell.getCellType()) {
            case STRING:
                strCell = cell.getRichStringCellValue().getString().trim();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // strCell = DateUtils.parseString("yyyy-MM-dd hh:mm:ss",
                    // cell.getDateCellValue());
                } else {
                    strCell = String.valueOf(new DecimalFormat().format(cell.getNumericCellValue()));
                }
                break;
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                evaluator.evaluateFormulaCell(cell);
                CellValue cellValue = evaluator.evaluate(cell);
                strCell = String.valueOf(cellValue.getNumberValue());
                break;
            case BLANK:
            default:
                strCell = "";
        }
        return strCell;
    }

    public ReadExcelUtil setSheetIndex(int sheetIndex) {
        this.sheetIndex = sheetIndex;
        if (this.workbook != null) {
            this.sheet = this.workbook.getSheetAt(sheetIndex);
        }
        return this;
    }

    public ReadExcelUtil setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
        return this;
    }

    public ReadExcelUtil setCellIndex(int cellIndex) {
        this.cellIndex = cellIndex;
        return this;
    }

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

    public String getSheetNameByIndex(int index) {
        return this.workbook.getSheetAt(index).getSheetName();
    }

    public String getCurrentSheetName() {
        return this.workbook.getSheetAt(this.sheetIndex).getSheetName();
    }

    public Workbook getWorkbook() {
        return this.workbook;
    }

    public Sheet getSheet() {
        return this.sheet;
    }

    public int getSheetIndex() {
        return this.sheetIndex;
    }

    public String getFile() {
        return this.file;
    }

    public void setFile(String file) {
        this.file = file;
    }
}