很多时候大家处理 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 写方法

做了简单分离 initExcel(),WriteToExcel(),CloseExcel(),主要目的提高写 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;
    }
}


↙↙↙阅读原文可查看相关链接,并与作者交流