1、正常情況是需要有一個(gè)方法提供data provider篙程，然后測(cè)試方法調(diào)用名字，而excel的路徑和sheet表名寫在調(diào)用的地方拥诡，

2氮发、通過注解實(shí)現(xiàn)excel文件配置讀取，用戶無需關(guān)心讀取過程爽冕，只需設(shè)置配置路徑

3、下面是測(cè)試用例

public class Demo extends ExcelDataHeleper {

     //excel文件讀取

    @Test(dataProvider="excel")

    @DataFile(path ="excel/addStudentTemplate.xls",sheet ="Sheet0")

    public void testmethod1(Map param){

              System.out.println(param.get("id")+"    "+param.get("phone"));

    }

   //csv文件讀取

    @Test(dataProvider="csv")

    @DataFile(path ="csv/a.csv")

    public void testmethodCsv(Map param){
              System.out.println(param.get("id")+"    "+param.get("phone"));

    }

    //yaml文件讀取
    @Test(dataProvider="yaml")

    @DataFile(path ="yaml/a.yaml",key="user")

    public void testmethodYaml(Map param){
              System.out.println(param.get("id")+"    "+param.get("phone"));

    }

}

dataProvider:標(biāo)注讀取的是excel文件

DataFile中的path:excel的相對(duì)路徑，在res下眯娱，sheet：表格的sheet名

param：為hashMap，通過表格中的鍵讀取值

DataFile類如下

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataFile {
    public String path();  //注解元素 為  id
    public String sheet() default "no description"; //設(shè)置默認(rèn)值试伙，
}

下面介紹繼承的基類：ExcelDataHeleper

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.testng.annotations.DataProvider;
import java.io.*;
import java.lang.reflect.Method;
import java.util.*;
public class ExcelDataHeleper {

    @DataProvider(name = "excel")
    public Iterator<Object[]> dataMethod(Method m) {
        System.out.println(m.getName());
        DataFile d = m.getAnnotation(DataFile.class);
        System.out.println(d.path() + "   " + d.sheet());
        List<Object> item = new ArrayList<Object>();
        List<List<String>> list = read(d.path(), d.sheet());
        if (list != null || list.size() > 0) {
            int size = list.get(0).size();
            for (int i = 1; i < list.size(); i++) {
                Map<String, Object> map = new HashMap<>();
                for (int j = 0; j < size; j++) {
                    map.put(list.get(0).get(j), list.get(i).get(j));
                }
                item.add(map);
            }
        }
        List<Object[]> users = new ArrayList<Object[]>();
        for (Object u : item) {
            //做一個(gè)形式轉(zhuǎn)換
            users.add(new Object[]{u});
        }
        return users.iterator();
    }
   //yaml文件
    @DataProvider(name = "yaml")
    public Iterator<Object[]> dataMethodYaml(Method m) {
        System.out.println(m.getName());
        DataFile d = m.getAnnotation(DataFile.class);
        logger.info(d.path() + "   " + d.key());
        List<Object> item = new ArrayList<Object>();
        InputStream in = ClassLoader.getSystemResourceAsStream(d.path());
        Yaml yaml = new Yaml();
        Map<String, Object> map = yaml.loadAs(in, Map.class);
        List<Map<String,String> > appid = (List<Map<String,String>>) map.get(d.key());
//        System.out.println(appid.get(1).get("username"));
        List<Object[]> users = new ArrayList<Object[]>();
        for (Object u : appid) {
            //做一個(gè)形式轉(zhuǎn)換
            users.add(new Object[]{u});
        }
        return users.iterator();
    }

    //csv文件讀取吱抚，還沒寫完
    @DataProvider(name = "csv")
    public Iterator<Object[]> dataMethod1(Method m) {
        DataFile d = m.getAnnotation(DataFile.class);
        File inFile = new File(System.getProperty("user.dir") + File.separator + "src/main/resources/" + d.path());
        try {
            BufferedReader reader = new BufferedReader(new FileReader(inFile));
            boolean sign = false;       //用來跳過第一行的名稱
            List<Object> item = new ArrayList<Object>();
            List<String> list = new ArrayList<>();
            String[] title = new String[0];
            while (reader.ready()) {
                String line = reader.readLine();
                String[] string = line.split(",");
                if (!sign) {
                    sign = true;
                    title = string;
                } else {
                    Map<String, Object> map = new HashMap<>();
                    if (string != null && string.length > 0 && title != null && title.length > 0) {
                        for (int j = 0; j < title.length; j++) {
                            map.put(specialUnicode(title[j]), string[j]);
                            //map.put(title[j], string[j]);
                        }
                        item.add(map);
                    }
                }
            }
            reader.close();
            List<Object[]> users = new ArrayList<Object[]>();
            for (Object u : item) {
                //做一個(gè)形式轉(zhuǎn)換
                users.add(new Object[]{u});
            }
            return users.iterator();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        }
        return null;
    }

    public List<List<String>> read(String fileName, String sheetName) {
        List<List<String>> maps = new ArrayList<>();
        if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$"))
            return maps;
        try {
            InputStream inputStream = new FileInputStream(System.getProperty("user.dir") + File.separator + "src/main/resources/" + fileName);
            System.out.println(inputStream);
            Workbook wb = WorkbookFactory.create(inputStream);
            maps = read(wb, sheetName);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return maps;
    }

    private int totalRows = 0;// 總行數(shù)
    private int totalCells = 0;// 總列數(shù)

    private List<List<String>> read(Workbook wb, String sheetName) {
        List<List<String>> maps = new ArrayList<>();
        List<List<String>> list = new ArrayList<List<String>>();
        int delnumber = 0;// 第一頁去除行數(shù)
        Sheet sheet = wb.getSheet(sheetName);
        this.totalRows = sheet.getPhysicalNumberOfRows() - delnumber; // 獲取工作表中行數(shù)
        if (this.totalRows >= 1 && sheet.getRow(delnumber) != null) {
            this.totalCells = sheet.getRow(0)
                    .getPhysicalNumberOfCells(); // 得到當(dāng)前行的所有單元格
            for (int j = 0; j < totalRows; j++) {
                List<String> rowLst = new ArrayList<String>();
                for (int f = 0; f < totalCells; f++) {
                    if (totalCells > 0) {
                        String value = getCell(sheet.getRow(j).getCell(f));
                        rowLst.add(value);
                    }
                }
                list.add(rowLst);
            }
        }
        return list;
    }
    /*
     * private String getRightStr(String sNum) { DecimalFormat decimalFormat =
     * new DecimalFormat("##.00"); String resultStr = decimalFormat.format(new
     * Double(sNum)); if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) { resultStr =
     * resultStr.substring(0, sNum.indexOf(".")); } return resultStr; }
     */

    public String getCell(Cell cell) {
        String cellValue = null;
        HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
        cellValue = hSSFDataFormatter.formatCellValue(cell); // 使用EXCEL原來格式的方式取得值
        return cellValue;
    }

 /**
     * 去除 字符串收尾的 特殊的Unicode [ "\uFEFF" ]
     * csv 文件可能會(huì)帶有該編碼
     * @param str
     * @return
     */
    private static String specialUnicode(String str){
        if (str.startsWith("\uFEFF")){
            str = str.replace("\uFEFF", "");
        }else if (str.endsWith("\uFEFF")){
            str = str.replace("\uFEFF","");
        }
        return str;
    }
{

注：因?yàn)閏sv文件有時(shí)候會(huì)隱藏一些標(biāo)識(shí)符號(hào)既绕，如果不去除涮坐，會(huì)導(dǎo)致通過map的key獲取不到對(duì)應(yīng)的值，所以加入了specialUnicode方法

pom.xml添加依賴

 <dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.17</version>
</dependency>

image.png

image.png

有問題聯(lián)系我疲扎，微信:fwrsmile