package com.jason.framework.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import net.sf.json.JSONObject;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
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;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.jason.framework.constants.constant;
import com.jason.framework.exception.NestedBusinessException;
public class ExcelReader {
private String filePath;
private String sheetName;
private Workbook workBook;
private Sheet sheet;
private List<String> columnHeaderList;
private List<List<String>> listData;
private List<Map<String,String>> mapData;
private boolean flag;
private static Map<String, String> map = new HashMap<String, String>();
private static SimpleLogger log = SimpleLogger.getLogger(ExcelReader.class);
public ExcelReader(String filePath, String sheetName) {
this.filePath = filePath;
this.sheetName = sheetName;
this.flag = false;
this.load();
}
private void load() {
FileInputStream inStream = null;
try {
inStream = new FileInputStream(new File(filePath));
workBook = WorkbookFactory.create(inStream);
sheet = workBook.getSheet(sheetName);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(inStream!=null){
inStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static List<Map<String, String>> getExcelToList(String xlsxPath, String sheetName) {
XSSFWorkbook xssfWorkbook=null;
try {
if (!xlsxPath.endsWith(".xlsx")) {
throw new NestedBusinessException("请检查文件名是否正确!");
}
InputStream is = new FileInputStream(xlsxPath);
xssfWorkbook = new XSSFWorkbook(is);
} catch (IOException e) {
throw new NestedBusinessException("系统找不到文件异常!", e);
}
// 循环工作表Sheet
XSSFSheet xssfSheet = xssfWorkbook.getSheet(sheetName);
if (null == xssfSheet) {
throw new NestedBusinessException("请检查sheet页是否存在!");
}
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
// 循环行Row
XSSFRow rowTitleRow =xssfSheet.getRow(0);
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
Map<String, String> map = new HashMap<String, String>();
// 循环列Cell
for (int cellNum = 0; cellNum <rowTitleRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
XSSFCell xssfCellTitleCell = rowTitleRow.getCell(cellNum);
map.put(getCellValue(xssfCellTitleCell), getCellValue(xssfCell));
}
list.add(map);
}
return list;
}
private static String getCellValue(Cell cell) {
String cellValue = "";
DataFormatter formatter = new DataFormatter();
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = formatter.formatCellValue(cell);
} else {
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
}
private List<List<String>> getSheetData() {
listData = new ArrayList<List<String>>();
mapData = new ArrayList<Map<String, String>>();
columnHeaderList = new ArrayList<String>();
int numOfRows = sheet.getLastRowNum() + 1;
for (int i = 0; i < numOfRows; i++) {
Row row = sheet.getRow(i);
Map<String, String> map = new HashMap<String, String>();
List<String> list = new ArrayList<String>();
if (row != null) {
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (i == 0){
columnHeaderList.add(getCellValue(cell));
}
else{
map.put(columnHeaderList.get(j), getCellValue(cell));
}
list.add(getCellValue(cell));
}
}
if (i > 0){
mapData.add(map);
}
listData.add(list);
}
flag = true;
return listData;
}
public String getCellData(int row, int col){
if(row<=0 || col<=0){
return null;
}
if(!flag){
this.getSheetData();
}
if(listData.size()>=row && listData.get(row-1).size()>=col){
return listData.get(row-1).get(col-1);
}else{
return null;
}
}
public String getCellData(int row, String headerName){
if(row<=0){
return null;
}
if(!flag){
this.getSheetData();
}
if(mapData.size()>=row && mapData.get(row-1).containsKey(headerName)){
return mapData.get(row-1).get(headerName);
}else{
return null;
}
}
public static Map<String, String> getDataByValue(String excelPath, String sheetName, String vaule){
List<Map<String, String>> list =ExcelReader.getExcelToList(excelPath,sheetName);
for (Map<String, String> data : list) {
if (data.containsValue(vaule)) {
map = data;
}
}
return map;
}
}
数据驱动---JAVA读取excle数据
最新推荐文章于 2023-04-26 15:15:55 发布