java excel解析_Java解析Excel

packagecom.util.datadriver;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.util.ArrayList;importjava.util.List;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;/*** 通过POI对Excel中的数据进行基本的操作

*

* 需要导入额jar包： poi-3.11-20141221.jar、 poi-ooxml-3.11-20141221.jar、

* poi-ooxml-schemas-3.11-20141221.jar、 xmlbeans-2.6.0.jar

**/

public classExcelController {

Workbook workbook= null;

Sheet sheet= null;public voidsetExcelFile(String filePath, String fileName, String sheetName) {try{

FileInputStream fis= new FileInputStream(newFile(filePath));

String type= fileName.substring(fileName.indexOf("."));if (type.equals(".xlsx")) {

workbook= newXSSFWorkbook(fis);

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

workbook= newHSSFWorkbook(fis);

}

sheet=workbook.getSheet(sheetName);

fis.close();

}catch(Exception e) {

e.printStackTrace();

}

}//获取单元格的值

public String getCellData(int row, intcolumn){try{//获得Cell单元格对象

Cell cell =sheet.getRow(row).getCell(column);//设置cell返回值的类型

cell.setCellType(Cell.CELL_TYPE_STRING);//获取到cell单元格中的值

String cellData =cell.getStringCellValue();returncellData;

}catch(Exception e) {throw(e);

}

}//设置单元格的值

@SuppressWarnings("static-access")public void setCellData(String value, int rowCount, intcolumnCount, String filePath){try{

Row row=sheet.getRow(rowCount);

Cell cell=row.getCell(columnCount,row.RETURN_BLANK_AS_NULL);if(cell == null){

row.createCell(columnCount).setCellValue(value);

}else{

cell.setCellValue(value);

}

FileOutputStream fos= new FileOutputStream(newFile(filePath));

workbook.write(fos);

fos.flush();

fos.close();

}catch(Exception e) {

e.printStackTrace();

}

}//获取Excel的行数

public intgetColCount(Sheet sheet1){int firstColCount =sheet1.getFirstRowNum();int lastColCount =sheet1.getLastRowNum();int sumColCount = lastColCount - firstColCount + 1;returnsumColCount;

}//获取Excel中每行的数据，并用数组返回每行所有数据,方便与TestNG做数据驱动

publicObject[][] getExcelData(String filePath, String fileName,

String sheetName)throwsException {int sumRowCount =getColCount(sheet);;

List list = new ArrayList();//获取每行的行对象,第一行为信息栏，不计入，所以从1开始

for (int i = 1; i < sumRowCount; i++) {

Row row=sheet.getRow(i);//获得一行中最后单元格的count

int lastCellCount =row.getLastCellNum();//定义一个数组来存放cell中值，根据cell的长度来定义数组的长度

String[] fileds = newString[lastCellCount];for (int j = 0; j < lastCellCount; j++) {

String cellValue=row.getCell(j).getStringCellValue();

fileds[j]=cellValue;

}

list.add(fileds);

}//定义一个object[][] 的二维数组，存放list中的值

Object[][] results = newObject[list.size()][];//设置二维数组每行的值，

for (int a = 0; a < list.size(); a++) {

results[a]=list.get(a);

}returnresults;

}

}