很多时候大家处理 Excel 时，会采用格式固定，确定好某行，某列的位置，才能正常取数或写数据，这对于整个框架而言，限制会非常大，因此跟大家说下我这边的思路；大家也可以作为通用方法进行调用；
Excel 读方法
实现思路:1.确定好参数行,根据参数名称自动查找到该参数对应的数据，这里要求参数名称不能变动，参数所在位置可任意变换
/**
* 优势: 参数和参数值的位置不需要格式限制
* 按行读取所有数据，并把param与value以Map形式输出
* BasicExcelData以map形式存储数据
* @param excelPath excel路径
* @param sheetName sheet页名称
* @param paramRow 参数所在行
* @param iRow 参数值所在行
* @return BasicExcelData
*/
public static BasicExcelData ExcelRead(String excelPath,String sheetName,int paramRow,int iRow) throws Exception{
File directory = new File(".");
String path = null;
path = directory .getCanonicalPath();
path += excelPath;
File file = new File(path);
BasicExcelData basicExcelData = new BasicExcelData(); //new basicExcelData
if(file.exists()){
WorkbookSettings ws = new WorkbookSettings();
ws.setCellValidationDisabled(true);
Workbook wwb = Workbook.getWorkbook(file,ws);
Sheet sheet = wwb.getSheet(sheetName);
int rows = sheet.getRows();
int columns = sheet.getColumns();
// log.info(rows + "and" + columns);
Map<String, Object> paramWithValue= new HashMap<String, Object>();
Map<String, Object> paramWithCol= new HashMap<String, Object>();
Map<String, Object> paramValueWithCol= new HashMap<String, Object>();
//遍历行数据，并写入map中
for(int i = 0; i < columns; i ++){
Cell paramCell = sheet.getCell(i,paramRow);//获取excel中参数所在行数据
Cell valueCell = sheet.getCell(i,iRow);//获取excel中参数值所在行数据
//若参数值的末尾为#,则删除#
// log.info(valueCell.getContents());
if(valueCell.getContents().length() != 0 && "#".equals(valueCell.getContents().substring(valueCell.getContents().length()-1))){
//去除#后的字符串
paramWithValue.put(paramCell.getContents(), valueCell.getContents().substring(0,valueCell.getContents().length()-1));
paramValueWithCol.put(valueCell.getContents().substring(0,valueCell.getContents().length()-1), i);
}
else {
paramWithValue.put(paramCell.getContents(), valueCell.getContents());
paramValueWithCol.put(valueCell.getContents(), i);
}
paramWithCol.put(paramCell.getContents(), i);
}
basicExcelData.addParamWithParamValue(paramWithValue);
basicExcelData.addParamWithColIndex(paramWithCol);
basicExcelData.addParamValueWithColIndex(paramValueWithCol);
wwb.close();
return basicExcelData;
}else{
log.error("测试用例文件不存在，请确认");
return null;
}
}
Excel 写方法
private static Workbook workbook;
private static WritableWorkbook writableWorkbook;
private static WritableSheet writableSheet;
/**
* 初始化写excel
*/
public static void initExcel(String excelPath,String sheetName) throws Exception{
File directory = new File(".");
String path = null;
path = directory .getCanonicalPath();
path += excelPath;
File file = new File(path);
if(file.exists()){
workbook = Workbook.getWorkbook(file);
writableWorkbook = Workbook.createWorkbook(file, workbook);
writableSheet = writableWorkbook.getSheet(sheetName);
}else{
log.error("测试用例文件不存在，请确认");
}
}
public static void WriteToExcel(int paramsRow,int iRow,String paramName,Object resultValue) throws Exception{
Map<String, Object> map = new HashMap<String, Object>();
//2016-06-16增加
String sResultValue = resultValue.toString();
//超过32767个字符,则自动截断
if(sResultValue.length() > 32767){
sResultValue = sResultValue.substring(0,32766);
}
int rows = writableSheet.getRows();
int columns = writableSheet.getColumns();
// log.info(rows + "and" + columns);
//遍历行数据，并写入map中
for(int i = 0; i < columns; i ++){
Cell paramCell = writableSheet.getCell(i,paramsRow);//获取excel中参数所在行数据
Cell valueCell = writableSheet.getCell(i,iRow);//获取excel中参数所在行数据
map.put(paramCell.getContents(), i);
}
Label label;
Integer iCol = Integer.parseInt(map.get(paramName).toString());//获取指定参数所在列值
Cell cell = writableSheet.getCell(iCol, iRow);
if(resultValue.toString().contains("PASS")){
WritableFont titlefont= new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);
WritableCellFormat greenBackground = new WritableCellFormat(titlefont);
greenBackground.setBackground(Colour.GREEN);//设置背景颜色为绿色
label = new Label(iCol,iRow,sResultValue,greenBackground);
writableSheet.addCell(label);
}else if(resultValue.toString().contains("FAIL")){
WritableFont titlefont= new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);
WritableCellFormat redBackground = new WritableCellFormat(titlefont);
redBackground.setBackground(Colour.RED);//设置背景颜色为红色
label = new Label(iCol,iRow,sResultValue,redBackground);
writableSheet.addCell(label);
}else if(resultValue.toString().contains("N/A")){
WritableFont titlefont= new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);
WritableCellFormat grayBackground = new WritableCellFormat(titlefont);
grayBackground.setBackground(Colour.GRAY_25);//设置背景颜色为灰色
label = new Label(iCol,iRow,sResultValue,grayBackground);
writableSheet.addCell(label);
}else {
label = new Label(iCol, iRow, sResultValue);
writableSheet.addCell(label);
}
}
public static void closeExcel() throws Exception{
writableWorkbook.write();
writableWorkbook.close();
}
package com.finger.autoTest.basic;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class BasicExcelData {
private static Map<String, Object> paramWithParamValue = new HashMap<String, Object>();
private static Map<String, Object> paramWithColIndex = new HashMap<String, Object>();
private static Map<String, Object> paramValueWithColIndex = new HashMap<String, Object>();
private static Map<String, Object> basicHTTPInfo = new HashMap<String, Object>(); //获取http基础信息,针对新模版
private static Map<String, Object> paramsMapbject = new HashMap<String, Object>(); //获取http基础信息,针对新模版
/**
* 添加Key: 参数；Value:参数值
* @return Map
*/
public static Map<String, Object> addParamWithParamValue(Map<String, Object> map){
paramWithParamValue = map;
return paramWithParamValue;
}
/**
* 返回所有map值
* @return Map
*/
public static Map<String, Object> getAllParamWithParamValue(){
return paramWithParamValue;
}
/**
* @param param 根据key:param
* @return 参数值
*/
public static String getParamWithParamValue(String param){
return paramWithParamValue.get(param).toString();
}
/**
* 添加Key: 参数；Value:参数所在列值
* @return Map
*/
public static Map<String, Object> addParamWithColIndex(Map<String, Object> map){
paramWithColIndex = map;
return paramWithColIndex;
}
/**
* 返回所有map值
* @return Map
*/
public static Map<String, Object> getAllParamWithColIndex(){
return paramWithColIndex;
}
/**
* @param param 根据key:param
* @return 参数所在列值
*/
public static Integer getParamWithColIndex(String param){
return Integer.parseInt(paramWithColIndex.get(param).toString());
}
/**
* 添加Key: 参数值；Value:参数值所在列值
* @return Map
*/
public static Map<String, Object> addParamValueWithColIndex(Map<String, Object> map){
paramValueWithColIndex = map;
return paramValueWithColIndex;
}
/**
* 返回所有map值
* @return Map
*/
public static Map<String, Object> getAllParamValueWithColIndex(){
return paramValueWithColIndex;
}
/**
* @param param 根据key:参数值
* @return 参数所在列值
*/
public static Integer getParamValueWithColIndex(String param){
return Integer.parseInt(paramValueWithColIndex.get(param).toString());
}
/**
* 2016-05-29
* 添加key
* excel模版的基础信息数据
* Map形式存储,KEY-Value
*@return Map
*/
public static Map<String, Object> addBasicHTTPInfo(Map<String, Object> map){
basicHTTPInfo = map;
return basicHTTPInfo;
}
/**
* 2016-05-29
* 获取key
* excel模版的基础信息数据
* Map形式存储,KEY-Value
* @param key 根据key:param
* @return 参数值
*/
public static String getBasicHTTPInfo(String key){
return basicHTTPInfo.get(key).toString();
}
/**
* 2016-05-29
* 获取所有HTTP基础信息
* excel模版的基础信息数据
* Map形式存储,KEY-Value
* 返回所有map值
* @return Map
*/
public static Map<String, Object> getAllBasicHTTPInfo(){
return basicHTTPInfo;
}
/**
* 2016-05-29
* 添加key
* excel模版中参数相关信息
* MAP形式存储,与schema中存储一致
* {"paramters":{"UserId":{"type":"string","value":"123445",xxx}}}
*@return JSONObject
*/
public static Map<String, Object> addBasicParamsInfo(Map<String, Object> map){
paramsMapbject = map;
return paramsMapbject;
}
/**
* 2016-05-29
* 获取key
* excel模版的基础信息数据
* MAP形式存储,KEY-Value
* {"paramters":{"UserId":{"type":"string","value":"123445",xxx},"aaaa":{}}}
* @param paramkey 根据key:param
* @return 参数值
*/
public static String getBasicParamsInfo(String paramkey,String properKey){
//map转换成json
String jsonText = JSON.toJSONString(paramsMapbject);
return JSONObject.parseObject(jsonText).getJSONObject(paramkey).getString(properKey);
}
/**
* 2016-05-29
* 获取所有HTTP参数信息
* JSON形式存储,KEY-Value
* 返回所有map值
* @return Map
*/
public static Map<String, Object> getAllBasicParamsInfo(){
return paramsMapbject;
}
}