poi操作excel-查询封装

1.学习自动化的第一步，操作poi的类，获取excle数据驱动并成功遍历

代码1，地址封装

package test.pom.api;


public class SourceAddress {

//测试案例地址
public static String  filePath="C:/Users/yvon/Desktop/1.xlsx";
//测试案例sheet页名称
public static String  sheetName="TestCase";

}

代码2，poi操作

package test.pom.api;


import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;


public class TestCaseUtils {
public static void main(String[] args) {
excelShow(TestCaseUtils.excelRead());
}


/*
* 1.获取数据源
* 2.遍历数据
* 
* 
* */
public static void excelShow(Map<String, HashMap> map){
//Map<String, HashMap> map = TestCaseUtils.excelRead();
HashMap hashMap = map.get("1");
System.out.println("hashMap"+hashMap);

Set set = hashMap.entrySet();
Iterator it = set.iterator();
while(it.hasNext()){
Map.Entry<Integer, String> entry = (Entry<Integer, String>) it.next();
System.out.println("key= " + entry.getKey() + " and value= " + entry.getValue());
}


}





/*1.获取file文件
*2.遍历excel案例
*3.每条案例的testCaseCode为key，字段和对应值为value，作为Map<String, HashMap>
* 
* */
public static Map<String, HashMap> excelRead(){
File file = getFile(SourceAddress.filePath);
Workbook wb = null;
Sheet sh = null;
Map <String, HashMap> excelContents = new HashMap();
try {
wb = Workbook.getWorkbook(file);
sh = wb.getSheet(SourceAddress.sheetName);
//行数
int rowsCount = sh.getRows();
System.out.println("rowsCount:::::"+rowsCount);

//列数
int ColumnsCount =  sh.getColumns();
System.out.println("ColumnsCount:::::::"+ColumnsCount);
//System.out.println(sh.getCell(1, 1).getContents().toString());
 
if(rowsCount<2){
System.out.println("无测试案例！");
}else{
System.out.println("有测试案例！");

//检查是否存在空行
//定义内容
String rowContents ="";
for(int i = 0;i < rowsCount;i++){
for(int j = 0;j < ColumnsCount;j++){

//取出具体列cell的值
//System.out.println(sh.getCell(j, i).getContents().toString());
rowContents = rowContents + sh.getCell(j, i).getContents().toString();
}
}

}
//System.out.println("testCaseCode111:::::"+sh.getCell(2,0).getContents().toString());



//读取内容
for(int rowIndex = 1;rowIndex < rowsCount;rowIndex++){
//获取testNo字段列值
//System.out.println("testCaseCode:::::"+sh.getCell(0,rowIndex).getContents().toString());
String testCaseCode = sh.getCell(0,rowIndex).getContents().toString();

HashMap<String, String> rowMap = new HashMap();
for(int colIndex = 0;colIndex < ColumnsCount;colIndex++){

//取出具体列cell的值
//System.out.println(sh.getCell(j, i).getContents().toString());
//rowContents = rowContents + sh.getCell(j, i).getContents().toString();


//取出字段值
//System.out.println("testCaseCode111:::::"+sh.getCell(colIndex,0).getContents().toString());

//取出实体值
//System.out.println("testCaseCode222:::::"+sh.getCell(colIndex,rowIndex).getContents().toString());


//取出实体值和字段值对应Map<key value>
rowMap.put(sh.getCell(colIndex,0).getContents().toString(), sh.getCell(colIndex,rowIndex).getContents().toString());
}

excelContents.put(testCaseCode, rowMap);
}


//return excelContents;

wb.close();
return excelContents;

} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;

}



//获取文件
public static File getFile(String path){
File file = null;
try {
file = new File(path);
} catch (Exception e) {
// TODO: handle exception
}
return file;

}
}