package org.apache.jmeter.functions;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.nio.file.NoSuchFileException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* File data container for CSV (and similar delimited) files Data is accessible
* via row and column number
*
*/
public class MyExcelReadBeanInfo {
private static final Logger log = LoggerFactory.getLogger(MyExcelReadBeanInfo.class);
private final List<List<String>> fileData; // Lines in the file, split into columns
private final String fileName; // name of the file
private final String sheetName; // name of the sheetName
//实例化workbook
private static Workbook excelWorkBook;
//实例化sheet
private static Sheet excelSheet;
//实例化FILE
private static FileInputStream excelFile;
//定义一个参数设置起始行
private static int beginCellNum;
//顶一个一个参数设置起始列
private static int beginRowNum;
private int nextRow;
public MyExcelReadBeanInfo(String file, String sheet, int rowNum, int cellNum) throws IOException, FileNotFoundException {
log.debug("FRCC({},{})", file, sheet, cellNum, rowNum);
fileName = file;
sheetName = sheet;
beginCellNum = cellNum;
beginRowNum = rowNum;
nextRow = 0;
fileData = new ArrayList<>();
load();
}
/*
* 此方法主要用于设定Excel的路径和sheet名称
*/
private void load() throws IOException, FileNotFoundException {
try {
// 实例化Excel文件的FileInputStream对象
excelFile = new FileInputStream(fileName);
// 实例化Excel文件的Workbook对象
String FileExtensionName = fileName.substring(fileName.indexOf("."));
// 判断文件类型如果是.xlsx，则使用XSSFWorkBook对象进行实例化
// 判断文件类型如果是.xls，则使用SSFWorkBook对象进行实力化
if (FileExtensionName.equals(".xlsx")) {
excelWorkBook = new XSSFWorkbook(excelFile);
} else if (FileExtensionName.equals(".xls")) {
excelWorkBook = new HSSFWorkbook(excelFile);
}
// 实例化Sheet对象，指定Excel文件中的Sheet名称，后续用于Sheet中，行列的单元格操作
excelSheet = excelWorkBook.getSheet(sheetName);
//获取Excel数据文件Sheet中的数据行号
//getLastRowNum方法获取数据的的最后行号
//getFirstRowNum方法获取数据的第一行行号
//相减后算出数据的行号
//注意：Excel文件的行号和列号都是从0开始的
int rowCount = excelSheet.getLastRowNum() - excelSheet.getFirstRowNum() + 1;
for(int i = beginRowNum - 1; i<rowCount; i++) {
System.out.println(excelRead(i, beginCellNum).size());
System.out.println(excelRead(i, beginCellNum));
fileData.add(excelRead(i, beginCellNum));
}
} catch (NoSuchFileException e) {
fileData.clear();
log.warn(e.toString());
throw e;
}
}
/*
* 此方法主要用于读取Excel中的数据
*/
private static List<String> excelRead(int RowNum, int cellNum) {
//实例化一个List
List<String> result = new ArrayList<>();
// 注意：Excel文件的行号和列号都是从0开始的
// 获取Excel数据文件中的列号
int cellCount = excelSheet.getRow(RowNum).getLastCellNum();
// 循环遍历获取数据
for (int i = cellNum-1; i < cellCount; i++) {
String excelDate = (String) (excelSheet.getRow(RowNum).getCell(i).getCellTypeEnum() == CellType.STRING
? excelSheet.getRow(RowNum).getCell(i).getStringCellValue()
: "" + excelSheet.getRow(RowNum).getCell(i).getNumericCellValue());
result.add(excelDate);
}
return result;
}
public String getColumn(int rowNum) throws IndexOutOfBoundsException {
String colData;
colData = fileData.get(rowNum).get(0);
log.debug("{}({},{}):{}", fileName, rowNum, colData);
return colData;
}
public int nextRow() {
int row = nextRow;
nextRow++;
if (nextRow >= fileData.size()) {// 0-based
nextRow = 0;
}
log.debug("Row: {}", row);
return row;
}
/**
* @return the file name for this class
*/
public String getFileName() {
return fileName;
}
// Added to support external testing
public int getSize(){
return fileData.size();
}
}