holmos.webtest.junitextentions.parameters.excel.HolmosWorkBook Maven / Gradle / Ivy
package holmos.webtest.junitextentions.parameters.excel;
import holmos.webtest.basetools.file.MyFile;
import holmos.webtest.log.MyLogger;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author 吴银龙([email protected])
* */
public class HolmosWorkBook {
MyLogger logger=MyLogger.getLogger(HolmosWorkBook.class);
private Workbook workbook;
public Workbook getBook(){
return workbook;
}
public HolmosWorkBook(String excelFilePath){
workbook=createWorkBook(excelFilePath);
}
public HolmosSheet getSheetByName(String sheetName){
return new HolmosSheet(workbook.getSheet(sheetName));
}
public HolmosSheet getSheetByIndex(int index){
return new HolmosSheet(workbook.getSheetAt(index));
}
/**
* 根据excel文件路径，新建工作簿，不对文件存在性进行检查，也不对文件类型进行检查，这些检查
* @param excelFilePath excel文件路径
* */
public Workbook createWorkBook(String excelFilePath){
Workbook workbook = null;
if(MyFile.exist(excelFilePath)){
try{
if(excelFilePath.endsWith(".xls"))
workbook=new HSSFWorkbook(new FileInputStream(excelFilePath));
else if(excelFilePath.endsWith(".xlsx"))
workbook=new XSSFWorkbook(new FileInputStream(excelFilePath));
else if(excelFilePath.endsWith("csv"))
workbook=new XSSFWorkbook(new FileInputStream(excelFilePath));
else
logger.error(excelFilePath+"文件不是excel文件类型!请检查!");
}catch (Exception e){}
}else{
logger.error(excelFilePath+"文件不存在!请检查!");
}
return workbook;
}
/**
* 将更改后的workbook写入excelFilePath位置
* */
public void saveFile(String excelFilePath){
try {
FileOutputStream outputStream=new FileOutputStream(new File(excelFilePath));
workbook.write(outputStream);
outputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取此workBook中由sheetIndex索引指定的sheet里面所有的HolmosCell对象
* */
public ArrayList>getValidCellsBySheetIndex(int sheetIndex){
ArrayList> values=new ArrayList>();
for(HolmosRow row:getSheetByIndex(sheetIndex).getValidRows()){
values.add(row.getValidCells());
}return values;
}
/**
* 获取此workBook中由sheetName索引指定的sheet里面所有的HolmosCell对象
* */
public ArrayList>getValidCellsBySheetName(String sheetName){
ArrayList> values=new ArrayList>();
for(HolmosRow row:getSheetByName(sheetName).getValidRows()){
values.add(row.getValidCells());
}return values;
}
/**
* 获取此workBook中由sheetIndex索引指定的sheet里面所有的值
* */
public ArrayList>getValidValuesBySheetIndex(int sheetIndex){
ArrayList> values=new ArrayList>();
for(HolmosRow row:getSheetByIndex(sheetIndex).getValidRows()){
values.add(row.getValidValues());
}return values;
}
/**
* 获取此workBook中由sheetName索引指定的sheet里面所有的值
* */
public ArrayList>getValidValuesBySheetName(String sheetName){
ArrayList> values=new ArrayList>();
for(HolmosRow row:getSheetByName(sheetName).getValidRows()){
values.add(row.getValidValues());
}return values;
}
}