其中:
a)Workbook、Sheet、Row、Cell等为接口;
b)HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell为97-2003版本对应的处理实现类;
c)XSSFWorkbook、XSSFSheet、XSSFRow、XSSFCell为2007+版本对应的处理实现类;
Selenium做自动化测试当然不能避免和Excel打交道。
由于Excel版本的关系,文件扩展名分xls和xlsx,
以往的经验都是使用HSSFWorkbook和XSSFWorkbook来分别处理。具体的方式就是先判断文件的类型,然后根据文件扩展名来选择方法。
大概处理方式如下:
1
2
3
4
5
6
7
8
9
10
|
String extention= getExtention(path);
if
(!EMPTY.equals(extention)) {
if
(XLS.equals(extention)) {
return
readXlsForAllSheets(path);
}
else
if
(XLSX.equals(extention)) {
return
readXlsxForAllSheets(path);
}
}
else
{
System.out.println(path +
" is not a excel file."
);
}
|
再接着实现readXlsForAllSheets和readXlsxForAllSheets两个方法。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
public
Object[][] readXlsxForAllSheets(String path)
throws
IOException{
System.out.println(path);
FileInputStream is =
new
FileInputStream(path);
XSSFWorkbook xssfWorkbook =
new
XSSFWorkbook(is);
System.out.println(
"There are totally "
+xssfWorkbook.getNumberOfSheets()+
" sheets in the workbook."
);
// Read the Sheet
List<Object[]> records1=
new
ArrayList<Object[]>();
for
(
int
numSheet =
0
; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
int
rowCount=xssfSheet.getLastRowNum()-xssfSheet.getFirstRowNum();
List<Object[]> records=
new
ArrayList<Object[]>();
String[] separative={
"This is sheet "
+xssfWorkbook.getSheetName(numSheet)};
records.add(separative);
for
(
int
rowNum =
1
;rowNum<rowCount+
1
; rowNum++){
XSSFRow xssfRow=xssfSheet.getRow(rowNum);
String fields[]=
new
String[xssfRow.getLastCellNum()];
for
(
int
colNum=
0
;colNum<xssfRow.getLastCellNum();colNum++){
XSSFCell xssfCell=xssfRow.getCell(colNum);
fields[colNum]=
this
.getXssfCellValue(xssfCell);
}
records.add(fields);
}
records1.addAll(records);
}
Object[][] results=
new
Object[records1.size()][];
for
(
int
i=
0
;i<records1.size();i++){
results[i]=records1.get(i);
}
if
(xssfWorkbook!=
null
){xssfWorkbook.close();}
return
results;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
public
Object[][] readXlsForAllSheets(String path)
throws
IOException{
System.out.println(PROCESSING + path);
FileInputStream is =
new
FileInputStream(path);
HSSFWorkbook hssfWorkbook =
new
HSSFWorkbook(is);
System.out.println(
"There are totally "
+hssfWorkbook.getNumberOfSheets()+
" sheets in the workbook."
);
// Read the Sheet
List<Object[]> records1=
new
ArrayList<Object[]>();
for
(
int
numSheet =
0
; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
int
rowCount=hssfSheet.getLastRowNum()-hssfSheet.getFirstRowNum();
List<Object[]> records=
new
ArrayList<Object[]>();
String[] separative={
"This is sheet "
+hssfWorkbook.getSheetName(numSheet)};
records.add(separative);
for
(
int
rowNum =
1
;rowNum<rowCount+
1
; rowNum++){
HSSFRow xssfRow=hssfSheet.getRow(rowNum);
String fields[]=
new
String[xssfRow.getLastCellNum()];
for
(
int
colNum=
0
;colNum<xssfRow.getLastCellNum();colNum++){
HSSFCell xssfCell=xssfRow.getCell(colNum);
fields[colNum]=
this
.getHssfCellValue(xssfCell);
}
records.add(fields);
}
records1.addAll(records);
}
Object[][] results=
new
Object[records1.size()][];
for
(
int
i=
0
;i<records1.size();i++){
results[i]=records1.get(i);
}
if
(hssfWorkbook!=
null
){hssfWorkbook.close();}
return
results;
}
|
再实现上两个方法中调用的getXssfCellValue和getHssfCellValue方法。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
private
String getXssfCellValue(XSSFCell xssfCell) {
String cellvalue=
""
;
DataFormatter formatter =
new
DataFormatter();
if
(
null
!= xssfCell) {
switch
(xssfCell.getCellType()) {
case
XSSFCell.CELL_TYPE_NUMERIC:
// 数字
if
(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(xssfCell)) {
cellvalue = formatter.formatCellValue(xssfCell);
}
else
{
double
value = xssfCell.getNumericCellValue();
int
intValue = (
int
) value;
cellvalue = value - intValue ==
0
? String.valueOf(intValue) : String.valueOf(value);
}
break
;
case
XSSFCell.CELL_TYPE_STRING:
// 字符串
cellvalue=xssfCell.getStringCellValue();
break
;
case
XSSFCell.CELL_TYPE_BOOLEAN:
// Boolean
cellvalue=String.valueOf(xssfCell.getBooleanCellValue());
break
;
case
XSSFCell.CELL_TYPE_FORMULA:
// 公式
cellvalue=String.valueOf(xssfCell.getCellFormula());
break
;
case
XSSFCell.CELL_TYPE_BLANK:
// 空值
cellvalue=
""
;
break
;
case
XSSFCell.CELL_TYPE_ERROR:
// 故障
cellvalue=
""
;
break
;
default
:
cellvalue=
"UNKNOWN TYPE"
;
break
;
}
}
else
{
System.out.print(
"-"
);
}
return
cellvalue.trim();
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
private
String getHssfCellValue(HSSFCell hssfCell) {
String cellvalue=
""
;
DataFormatter formatter =
new
DataFormatter();
if
(
null
!= hssfCell) {
switch
(hssfCell.getCellType()) {
case
HSSFCell.CELL_TYPE_NUMERIC:
// 数字
if
(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(hssfCell)) {
cellvalue = formatter.formatCellValue(hssfCell);
}
else
{
double
value = hssfCell.getNumericCellValue();
int
intValue = (
int
) value;
cellvalue = value - intValue ==
0
? String.valueOf(intValue) : String.valueOf(value);
}
break
;
case
HSSFCell.CELL_TYPE_STRING:
// 字符串
cellvalue=hssfCell.getStringCellValue();
break
;
case
HSSFCell.CELL_TYPE_BOOLEAN:
// Boolean
cellvalue=String.valueOf(hssfCell.getBooleanCellValue());
break
;
case
HSSFCell.CELL_TYPE_FORMULA:
// 公式
cellvalue=String.valueOf(hssfCell.getCellFormula());
break
;
case
HSSFCell.CELL_TYPE_BLANK:
// 空值
cellvalue=
""
;
break
;
case
HSSFCell.CELL_TYPE_ERROR:
// 故障
cellvalue=
""
;
break
;
default
:
cellvalue=
"UNKNOWN TYPE"
;
break
;
}
}
else
{
System.out.print(
"-"
);
}
return
cellvalue.trim();
}
|
最终整个解析Excel文件的功能才算完成,我们需要实现4个方法readXlsForAllSheets和readXlsxForAllSheets,getXssfCellValue和getHssfCellValue,那么有没有更加简单实用的方法呢?
下面要介绍的是POI jar包提供的WorkbookFactory类。需要加载poi-ooxm-3.15.jar到build path。
只需要两行就可以实例化workbook,而不用管它是xls还是xlsx。
1
2
|
inStream =
new
FileInputStream(
new
File(filePath));
Workbook workBook = WorkbookFactory.create(inStream);
|
后续可以直接操作sheet,Row,Cell,也不用管文件类型。
目前还没有发现这种方法的缺点。