Excel 读写

一、环境准备：pom.xml 导入依赖 poi-ooxml

 <dependencies>   
     <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>
 </dependencies>

二、Excel 读取 =》封装成对象

思路：将excel中的每一行row封装成对象cases，将对象cases保存到listCases集合方便后续调用！

1.创建对象cases

 1 package cn.xiaobing.pojo;
 2 
 3 /**1.创建实体类
 4  * 2.生成get和set方法
 5  * 3.提供有参和无参构造器
 6  * 4.重写toString方法
 7  * @author Administrator
 8  */
 9 public class Cases {
10     private String caseId;
11     private String interfaceName;
12     private String url;
13     private String submitType;
14     private String dataType;
15     private String desc;
16     private String mobilephone;
17     private String pwd;
18     public String getCaseId() {
19         return caseId;
20     }
21     public void setCaseId(String caseId) {
22         this.caseId = caseId;
23     }
24     public String getInterfaceName() {
25         return interfaceName;
26     }
27     public void setInterfaceName(String interfaceName) {
28         this.interfaceName = interfaceName;
29     }
30     public String getUrl() {
31         return url;
32     }
33     public void setUrl(String url) {
34         this.url = url;
35     }
36     public String getSubmitType() {
37         return submitType;
38     }
39     public void setSubmitType(String submitType) {
40         this.submitType = submitType;
41     }
42     public String getDataType() {
43         return dataType;
44     }
45     public void setDataType(String dataType) {
46         this.dataType = dataType;
47     }
48     public String getDesc() {
49         return desc;
50     }
51     public void setDesc(String desc) {
52         this.desc = desc;
53     }
54     public String getMobilephone() {
55         return mobilephone;
56     }
57     public void setMobilephone(String mobilephone) {
58         this.mobilephone = mobilephone;
59     }
60     public String getPwd() {
61         return pwd;
62     }
63     public void setPwd(String pwd) {
64         this.pwd = pwd;
65     }
66     public Cases() {
67         super();
68     }
69     public Cases(String caseId, String interfaceName, String url, String submitType, String dataType, String desc,
70             String mobilephone, String pwd) {
71         super();
72         this.caseId = caseId;
73         this.interfaceName = interfaceName;
74         this.url = url;
75         this.submitType = submitType;
76         this.dataType = dataType;
77         this.desc = desc;
78         this.mobilephone = mobilephone;
79         this.pwd = pwd;
80     }
81     @Override
82     public String toString() {
83         return "Cases [caseId=" + caseId + ", interfaceName=" + interfaceName + ", url=" + url + ", submitType="
84                 + submitType + ", dataType=" + dataType + ", desc=" + desc + ", mobilephone=" + mobilephone + ", pwd="
85                 + pwd + "]";
86     }
87 }

2.读取readExcel方法实现

  1 package cn.xiaobing.excelUtil;
  2 
  3 import java.io.File;
  4 import java.io.FileInputStream;
  5 import java.io.IOException;
  6 import java.io.InputStream;
  7 import java.lang.reflect.Method;
  8 import java.util.ArrayList;
  9 import java.util.HashMap;
 10 import java.util.List;
 11 import java.util.Map;
 12 
 13 import org.apache.poi.ss.usermodel.Cell;
 14 import org.apache.poi.ss.usermodel.CellType;
 15 import org.apache.poi.ss.usermodel.Row;
 16 import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
 17 import org.apache.poi.ss.usermodel.Sheet;
 18 import org.apache.poi.ss.usermodel.Workbook;
 19 import org.apache.poi.ss.usermodel.WorkbookFactory;
 20 
 21 import cn.xiaobing.pojo.Cases;
 22 
 23 /**读取Excel
 24  * @author Administrator
 25  */
 26 public class ReadExcel {
 27     /**加载cases类添加到list集合
 28      * @return
 29      */
 30     public static List<Cases> loadCase(){
 31         //创建存放cases的List列表，将excel中每行的数据封装成对象再添加到list集合
 32         List listCases = new ArrayList();
 33         //创建workbook工作薄对象
 34         //文件目录写相对于项目的相对路径
 35         File file = new File("src/test/resources/cases.xls");
 36         InputStream inputStream = null;
 37         Workbook workbook = null;
 38         try {
 39             inputStream = new FileInputStream(file);
 40             workbook = WorkbookFactory.create(inputStream);
 41         } catch (Exception e) {
 42             e.printStackTrace();
 43         }finally {
 44             //finally必须执行的方法，利用finally关闭IO流
 45             if(inputStream != null) {
 46                 try {
 47                     inputStream.close();
 48                 } catch (IOException e) {
 49                     e.printStackTrace();
 50                 }
 51             }
 52         }
 53         //创建sheet表单对象
 54         Sheet sheet = workbook.getSheetAt(0);//根据sheet下标获取
 55 //        Sheet sheet = workbook.getSheet("接口用例");//根据sheet名获取
 56         //拿到excel的实际行数:返回行数下标=》Returns:last row contained n this sheet (0-based)
 57         int rowNum = sheet.getLastRowNum();
 58         //获取标题行index=0的列数
 59         int cellNum = sheet.getRow(0).getLastCellNum();
 60         //获取标题列索引和标题名的映射关系
 61         Map<Integer, String> indexAndCellNameMap = indexAndCellNameMap(sheet);
 62         //获取行,第一行为标题列，所以从下标1开始取行数据
 63         //循环取出每一行
 64         Object obj = null;
 65         for (int i = 1; i <= rowNum; i++) {
 66             try {
 67                 //每一行对应一个数据对象.调用一个newInstance方法得到一个对象
 68                 obj = Cases.class.newInstance();
 69             } catch (Exception e1) {
 70                 e1.printStackTrace();
 71             } 
 72             Row row = sheet.getRow(i);
 73             for (int j = 0; j < cellNum; j++) {
 74                 //如果row为空,跳过继续
 75                 if(row == null) {
 76                     continue;
 77                 }
 78                 //获取列,同时设置空列的处理策略MissingCellPolicy.CREATE_NULL_AS_BLANK
 79                 Cell cell = row.getCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
 80                 //取出前设置列的类型，所有的列都当做是字符串来处理
 81                 cell.setCellType(CellType.STRING);
 82                 String cellValue = cell.getStringCellValue();
 83                 //获取要反射的方法名即：列标题前面拼接一个set就得到了此列要反射的方法名
 84                 String methodName = "set"+ indexAndCellNameMap.get(j);
 85                 //拿到方法对象
 86                 Method method =null;
 87                 try {
 88                     method = Cases.class.getMethod(methodName, String.class);
 89                     //反射调用方法
 90                     method.invoke(obj, cellValue);
 91                 } catch (Exception e) {
 92                     e.printStackTrace();
 93                 }
 94             }
 95             listCases.add(obj);
 96         }
 97         return listCases;
 98     }
 99     /**获取标题行，取出cell值，将标题列索引和其标题存到Map，构成映射关系
100      * @param sheet
101      * @return
102      */
103     public static Map<Integer, String> indexAndCellNameMap(Sheet sheet) {
104         //将标题行索引和标题行列名添加到indexAndCellNameMap保存后续使用
105         Map<Integer, String> indexAndCellNameMap = new  HashMap<Integer, String>();
106         //获取第一行titleRow，由titleRow获取行列数cellNum
107         Row titleRow = sheet.getRow(0);
108         //拿到execl的实际列数cellNum,返回列数 1-based
109         int cellNum =titleRow.getLastCellNum();
110         //获取标题行每一列
111         for (int i = 0; i < cellNum; i++) {
112             Cell titleCell = titleRow.getCell(i);
113             titleCell.setCellType(CellType.STRING);
114             //取出列名
115             String cellValue = titleCell.getStringCellValue();
116             //将列索引和其标题存到map，构成映射
117             indexAndCellNameMap.put(i, cellValue);
118         }
119         return indexAndCellNameMap;
120     }
121     public static void main(String[] args) {
122         List<Cases> lst= ReadExcel.loadCase();
123         for (Cases cases : lst) {
124             System.out.println(cases);
125         }
126     }
127 }
Console：
Cases [caseId=1, interfaceName=Register, url=https://www.zhihu.com/signup?next=%2F, submitType=post, dataType=form, desc=手机号, mobilephone=18335198888, pwd=]
Cases [caseId=2, interfaceName=Register, url=https://www.zhihu.com/signup?next=%2F, submitType=post, dataType=form, desc=密码, mobilephone=, pwd=123456]
Cases [caseId=3, interfaceName=Register, url=https://www.zhihu.com/signup?next=%2F, submitType=post, dataType=form, desc=错误手机号+密码, mobilephone=123, pwd=123456]
Cases [caseId=4, interfaceName=Register, url=https://www.zhihu.com/signup?next=%2F, submitType=post, dataType=form, desc=正确手机号+错密码, mobilephone=18813989449, pwd=12345]
Cases [caseId=5, interfaceName=Register, url=https://www.zhihu.com/signup?next=%2F, submitType=post, dataType=form, desc=合格手机号+合格密码, mobilephone=18813989449, pwd=123456]

三、Excel 写入

1.例如用例执行结果回写到Excel中的Result列：

2.修改实体类Cases，添加result属性

 1 package cn.xiaobing.pojo;
 2 
 3 /**1.创建实体类
 4  * 2.生成get和set方法
 5  * 3.提供有参和无参构造器
 6  * 4.重写toString方法
 7  * @author Administrator
 8  */
 9 public class Cases {
10     private String caseId;
11     private String interfaceName;
12     private String url;
13     private String submitType;
14     private String dataType;
15     private String desc;
16     private String mobilephone;
17     private String pwd;
18     private String result;
19     public String getCaseId() {
20         return caseId;
21     }
22     public void setCaseId(String caseId) {
23         this.caseId = caseId;
24     }
25     public String getInterfaceName() {
26         return interfaceName;
27     }
28     public void setInterfaceName(String interfaceName) {
29         this.interfaceName = interfaceName;
30     }
31     public String getUrl() {
32         return url;
33     }
34     public void setUrl(String url) {
35         this.url = url;
36     }
37     public String getSubmitType() {
38         return submitType;
39     }
40     public void setSubmitType(String submitType) {
41         this.submitType = submitType;
42     }
43     public String getDataType() {
44         return dataType;
45     }
46     public void setDataType(String dataType) {
47         this.dataType = dataType;
48     }
49     public String getDesc() {
50         return desc;
51     }
52     public void setDesc(String desc) {
53         this.desc = desc;
54     }
55     public String getMobilephone() {
56         return mobilephone;
57     }
58     public void setMobilephone(String mobilephone) {
59         this.mobilephone = mobilephone;
60     }
61     public String getPwd() {
62         return pwd;
63     }
64     public void setPwd(String pwd) {
65         this.pwd = pwd;
66     }
67     public String getResult() {
68         return result;
69     }
70     public void setResult(String result) {
71         this.result = result;
72     }
73     
74     public Cases() {
75         super();
76     }
77     public Cases(String caseId, String interfaceName, String url, String submitType, String dataType, String desc,
78             String mobilephone, String pwd, String result) {
79         super();
80         this.caseId = caseId;
81         this.interfaceName = interfaceName;
82         this.url = url;
83         this.submitType = submitType;
84         this.dataType = dataType;
85         this.desc = desc;
86         this.mobilephone = mobilephone;
87         this.pwd = pwd;
88         this.result = result;
89     }
90     @Override
91     public String toString() {
92         return "Cases [caseId=" + caseId + ", interfaceName=" + interfaceName + ", url=" + url + ", submitType="
93                 + submitType + ", dataType=" + dataType + ", desc=" + desc + ", mobilephone=" + mobilephone + ", pwd="
94                 + pwd + ", result=" + result + "]";
95     }
96 }

3.写入实现类WriteExcel

 1 package cn.xiaobing.excelUtil;
 2 
 3 import java.io.File;
 4 import java.io.FileInputStream;
 5 import java.io.FileOutputStream;
 6 import java.io.IOException;
 7 import java.io.InputStream;
 8 import java.io.OutputStream;
 9 
10 import org.apache.poi.ss.usermodel.Cell;
11 import org.apache.poi.ss.usermodel.CellType;
12 import org.apache.poi.ss.usermodel.Row;
13 import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
14 import org.apache.poi.ss.usermodel.Sheet;
15 import org.apache.poi.ss.usermodel.Workbook;
16 import org.apache.poi.ss.usermodel.WorkbookFactory;
17 
18 /**实现测试结果result回写excel
19  * @author Administrator
20  */
21 public class WriteExcel {
22     /**实现测试结果result回写excel的方法
23      * @param sheetNum 回写sheet下标数
24      * @param rowNum 回写行下标数
25      * @param cellNum 回写列下标数
26      * @param Result 回写表单列result内容
27      */
28     public static void writeExcel(int sheetNum,int rowNum,int cellNum,String Result) {
29         ////文件目录写相对于项目的相对路径
30         File file = new File("src/test/resources/cases.xls");
31         //输入流
32         InputStream inputStream = null;
33         Workbook workbook = null;
34         try {
35             inputStream = new FileInputStream(file);
36             //创建workbook工作薄对象
37             workbook =WorkbookFactory.create(inputStream);
38         } catch (Exception e) {
39             e.printStackTrace();
40         }finally {
41             if(inputStream != null) {
42                 try {
43                     inputStream.close();
44                 } catch (IOException e) {
45                     e.printStackTrace();
46                 }
47             }
48         }
49         //根据传入的表单下标获取表单
50         Sheet sheet = workbook.getSheetAt(sheetNum);
51         //根据传入的行索引rowNum获取行
52         Row row = sheet.getRow(rowNum);
53         //根据传入的列索引cellNum获取列cell,同时设置空列的处理策略MissingCellPolicy.CREATE_NULL_AS_BLANK
54         Cell cell = row.getCell(cellNum,MissingCellPolicy.CREATE_NULL_AS_BLANK);
55         //取出前设置列的类型，所有的列都当做是字符串来处理
56         cell.setCellType(CellType.STRING);
57         //set执行结果result到cell列
58         cell.setCellValue(Result);
59         OutputStream outputStream = null;
60         try {
61             //准备输出流对象
62             outputStream = new FileOutputStream(file);
63             //将数据写入文件
64             workbook.write(outputStream);
65         } catch (Exception e) {
66             e.printStackTrace();
67         }finally {
68             if(outputStream != null) {
69                 try {
70                     outputStream.close();
71                 } catch (IOException e) {
72                     // TODO Auto-generated catch block
73                     e.printStackTrace();
74                 }
75             }
76         }
77     }
78 
79     public static void main(String[] args) {
80         /**例如实际执行自动化测试中
81          * 已知写入的sheet表单下标
82          * 已知本条用例的caseID可以获取rowNum
83          * 已知写入列名可以获取cellNum
84          * 已知测试结果result
85          */
86         WriteExcel.writeExcel(0, 1, 8, "不通过，测试失败！");
　　　　　　 System.out.println("执行成功！");
87     }
88 }

总结：亲测后总结，分享给需要的人，不足之处后续修改补充！　

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/xiaozhaoboke/p/11027744.html