java xls和xlsx_java读取xls和xlsx数据作为数据驱动来用

packagedataProvider;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.util.ArrayList;importjava.util.List;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.DateUtil;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;public classDP_Demo {public static Object[][] getTestData(String filePath) throwsFileNotFoundException, IOException {

File excelFile= newFile(filePath);/** 判断给定文件的类型; 1.如果是xls的问价那类型就创建XSSFWorkBook ;

* 2.如果是xlsx的文件类型就创建HSSFWorkBook ;*/String xls= filePath.substring(filePath.indexOf('.'));

System.out.println("传入文件的后缀是：" + xls + " ;");if (xls.equals(".xls")) {

HSSFWorkbook xlswb= new HSSFWorkbook(newFileInputStream(excelFile));

HSSFSheet sheet= xlswb.getSheetAt(0);int rowcount = sheet.getLastRowNum() -sheet.getFirstRowNum();

List list = new ArrayList();//System.out.println("---------该sheet总共有 ：" + rowcount + " ;");

Row row;

Cell cell;for (int i = 0; i < rowcount + 1; i++) {

row=sheet.getRow(i);if(row.getCell(i)==null){continue;

}else{

}

Object[] obj= newObject[row.getLastCellNum()];/** System.out.println("当前行是：" + (row.getRowNum() + 1) +

* " ;当前行的第一个单元格是：" + row.getFirstCellNum() + " ; 当前前的最后一个单元格是："

* + row.getLastCellNum() + "; ");*/

//System.out.println("obj 数组的长度是 ：" + obj.length + " ;");

for (int j = 0; j < row.getLastCellNum(); j++) {

cell=row.getCell(j);switch(cell.getCellType()) {caseCell.CELL_TYPE_STRING:

obj[j]=cell.getRichStringCellValue().getString();/** System.out.print(cell.getRichStringCellValue().

* getString()); System.out.print("|");*/ break;caseCell.CELL_TYPE_NUMERIC:if(DateUtil.isCellDateFormatted(cell)) {

obj[j]=cell.getDateCellValue();//System.out.print(String.valueOf(cell.getDateCellValue()));

} else{

obj[j]=cell.getNumericCellValue();//System.out.print(cell.getNumericCellValue());

}//System.out.print("|");

break;caseCell.CELL_TYPE_BOOLEAN:

obj[j]=cell.getBooleanCellValue();/** System.out.print(cell.getBooleanCellValue());

* System.out.print("|");*/ break;default:

}

}

list.add(obj);//System.out.println();

}//System.out.println("list.size()===" + list.size());

Object[][] object = newObject[list.size()][];for (int i = 0; i < object.length; i++) {

object[i]=list.get(i);

}returnobject;

}else if (xls.equals(".xlsx")) {

XSSFWorkbook wbxlsx= new XSSFWorkbook(newFileInputStream(excelFile));

XSSFSheet sheet= wbxlsx.getSheetAt(0);int rowcount = sheet.getLastRowNum() -sheet.getFirstRowNum();

List list = new ArrayList();//System.out.println("---------该sheet总共有 ：" + rowcount + " ;");

Row row;

Cell cell;for (int i = 0; i < rowcount + 1; i++) {

row=sheet.getRow(i);/** System.out.println("当前行是：" + (row.getRowNum() + 1) +

* " ;当前行的第一个单元格是：" + row.getFirstCellNum() + " ; 当前前的最后一个单元格是："

* + row.getLastCellNum() + "; ");*/Object[] obj= newObject[row.getLastCellNum()];//System.out.println("obj 数组的长度是 ：" + obj.length + " ;");

for (int j = 0; j < row.getLastCellNum(); j++) {

cell=row.getCell(j);switch(cell.getCellType()) {caseCell.CELL_TYPE_STRING:

obj[j]=cell.getRichStringCellValue().getString();/** System.out.print(cell.getRichStringCellValue().

* getString()); System.out.print("|");*/

break;caseCell.CELL_TYPE_NUMERIC:if(DateUtil.isCellDateFormatted(cell)) {

obj[j]=cell.getDateCellValue();//System.out.print(String.valueOf(cell.getDateCellValue()));

} else{

obj[j]=cell.getNumericCellValue();//System.out.print(cell.getNumericCellValue());

}//System.out.print("|");

break;caseCell.CELL_TYPE_BOOLEAN:

obj[j]=cell.getBooleanCellValue();/** System.out.print(cell.getBooleanCellValue());

* System.out.print("|");*/

break;default:

}

}

list.add(obj);

System.out.println();

}//System.out.println("list.size()===" + list.size());

Object[][] object = newObject[list.size()][];for (int i = 0; i < object.length; i++) {

object[i]=list.get(i);

}returnobject;

}else{

System.out.println("指定的文件不是excle文件！");

}return null;

}public static void main(String[] args) throwsFileNotFoundException, IOException {//String filePathxls = "D:\\2003table15.xls";

String filePathxlsx = "D:\\2007Text.xlsx";//Object [][] objxls =DP_Demo.getTestData(filePathxls);

Object[][] objxlsx =DP_Demo.getTestData(filePathxlsx);/** for (int i = 0; i < objxlsx.length; i++) {

* System.out.println(objxlsx[i]); }*/Object[] obj1= objxlsx[0];for (int i = 0; i < obj1.length; i++) {

System.out.print("[" + obj1[i] + "] ");

}

}

}