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;
}
}
