Excel作为数据源TesTNG做数据驱动完整代码

说明：EXCEL 支持xls 和xlsx 俩种格式；
已经过测试！
  1 package main.java;
  2 
  3 import org.apache.poi.ss.usermodel.*;
  4 
  5 import java.io.File;
  6 import java.io.FileInputStream;
  7 import java.io.IOException;
  8 import java.util.ArrayList;
  9 import java.util.HashMap;
 10 import java.util.List;
 11 import java.util.Map;
 12 
 13 public class ExcelReader {
 14     private String filePath;
 15     private String sheetName;
 16     private Workbook workBook;
 17     private Sheet sheet;
 18     private List<String> columnHeaderList;
 19     private List<List<String>> listData;
 20     private List<Map<String, String>> mapData;
 21     private boolean flag;
 22     public Object[][] results;
 23 
 24     public ExcelReader(String filePath, String sheetName) {
 25         this.filePath = filePath;
 26         this.sheetName = sheetName;
 27         this.flag = false;
 28         this.load();
 29     }
 30 
 31     private void load() {
 32         FileInputStream inStream = null;
 33         try {
 34             inStream = new FileInputStream(new File(filePath));
 35             workBook = WorkbookFactory.create(inStream);
 36             sheet = workBook.getSheet(sheetName);
 37         } catch (Exception e) {
 38             e.printStackTrace();
 39         } finally {
 40             try {
 41                 if (inStream != null) {
 42                     inStream.close();
 43                 }
 44             } catch (IOException e) {
 45                 e.printStackTrace();
 46             }
 47         }
 48     }
 49 
 50     private String getCellValue(Cell cell) {
 51         String cellValue = "";
 52         DataFormatter formatter = new DataFormatter();
 53         if (cell != null) {
 54             switch (cell.getCellType()) {
 55                 case Cell.CELL_TYPE_NUMERIC:
 56                     if (DateUtil.isCellDateFormatted(cell)) {
 57                         cellValue = formatter.formatCellValue(cell);
 58                     } else {
 59                         double value = cell.getNumericCellValue();
 60                         int intValue = (int) value;
 61                         cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
 62                     }
 63                     break;
 64                 case Cell.CELL_TYPE_STRING:
 65                     cellValue = cell.getStringCellValue();
 66                     break;
 67                 case Cell.CELL_TYPE_BOOLEAN:
 68                     cellValue = String.valueOf(cell.getBooleanCellValue());
 69                     break;
 70                 case Cell.CELL_TYPE_FORMULA:
 71                     cellValue = String.valueOf(cell.getCellFormula());
 72                     break;
 73                 case Cell.CELL_TYPE_BLANK:
 74                     cellValue = "";
 75                     break;
 76                 case Cell.CELL_TYPE_ERROR:
 77                     cellValue = "";
 78                     break;
 79                 default:
 80                     cellValue = cell.toString().trim();
 81                     break;
 82             }
 83         }
 84         return cellValue.trim();
 85     }
 86 
 87     private void getSheetData() {
 88 
 89         listData = new ArrayList<>();
 90         mapData = new ArrayList<>();
 91         columnHeaderList = new ArrayList<>();
 92         int numOfRows = sheet.getLastRowNum() + 1;
 93         for (int i = 0; i < numOfRows; i++) {
 94             Row row = sheet.getRow(i);
 95             Map<String, String> map = new HashMap<>();
 96             List<String> list = new ArrayList<>();
 97 
 98             if (row != null) {
 99                 for (int j = 0; j < row.getLastCellNum(); j++) {
100                     Cell cell = row.getCell(j);
101                     if (i == 0) {
102                         columnHeaderList.add(getCellValue(cell));
103                     } else {
104 
105                         map.put(columnHeaderList.get(j), this.getCellValue(cell));
106 
107                     }
108                     list.add(this.getCellValue(cell));
109                 }
110             }
111             if (i > 0) {
112                 mapData.add(map);
113             }
114             listData.add(list);
115 
116 
117         }
118 
119         flag = true;
120     }
121 
122     public String getCellData(int row, int col) {
123         if (row <= 0 || col <= 0) {
124             return null;
125         }
126         if (!flag) {
127             this.getSheetData();
128         }
129         if (listData.size() >= row && listData.get(row - 1).size() >= col) {
130             return listData.get(row - 1).get(col - 1);
131         } else {
132             return null;
133         }
134     }
135 
136     public String getCellData(int row, String headerName) {
137         if (row <= 0) {
138             return null;
139         }
140         if (!flag) {
141             this.getSheetData();
142         }
143         if (mapData.size() >= row && mapData.get(row - 1).containsKey(headerName)) {
144             return mapData.get(row - 1).get(headerName);
145         } else {
146             return null;
147         }
148     }
149 
150 
151     private Object[][] getSheetData2() {
152 
153         List<Object[]> result = new ArrayList<>();
154         listData = new ArrayList<>();
155         mapData = new ArrayList<>();
156         columnHeaderList = new ArrayList<>();
157 
158         int numOfRows = sheet.getLastRowNum() + 1;
159         Object[] o1 = new Object[numOfRows];
160         for (int i = 0; i < numOfRows; i++) {
161             Row row = sheet.getRow(i);
162             Map<String, String> map = new HashMap<>();
163             List<String> list = new ArrayList<>();
164 
165             if (row != null) {
166                 for (int j = 0; j < row.getLastCellNum(); j++) {
167                     Cell cell = row.getCell(j);
168                     if (i == 0) {
169 
170                         columnHeaderList.add(getCellValue(cell));
171                     } else {
172                         o1[i] = this.getCellValue(cell);
173                         map.put(columnHeaderList.get(j), this.getCellValue(cell));
174 
175                     }
176                     list.add(this.getCellValue(cell));
177                 }
178             }
179             if (i > 0) {
180                 mapData.add(map);
181             }
182             listData.add(list);
183             result.add(o1);
184 
185         }
186        results = new Object[result.size()][];
187         for (int i = 0; i < result.size(); i++) {
188             results[i] = result.get(i);
189         }
190         flag = true;
191         return results;
192     }
193 
194     public static void main(String[] args) {
195         Object[][] obj1;
196         ExcelReader eh = new ExcelReader("C:\Users\linbo.yang\Desktop\TEST.xlsx", "Sheet1");
197        /* for (int i = 0; i < 10; i++) {
198             System.out.println(eh.getCellData(i, i));
199         }
200         System.out.println(eh.getCellData(1, 1));
201         // System.out.println(eh.getCellData(1, "test1"));
202         System.out.println(eh.getCellData(1, 1));*/
203         Object[][] sheetData2 = eh.getSheetData2();
204         for (int i = 0; i < sheetData2.length; i++) {
205             for (int j = 0; j < i; j++) {
206                 System.out.println(sheetData2[i][j]);
207 
208             }
209 
210         }
211 
212 
213     }
214 }