poi-3.7读取excel,支持excel 97~03 / excel 07
hanwentan 2012-02-13
/**
*需要如下jar包.
*poi-3.7-20101029.jar,poi-ooxml-3.7-20101029.jar,
*xmlbeans-2.3.0.jar,poi-ooxml-schemas-3.7-20101029.jar,dom4j-1.6.1.jar
*
*需要将xbean-2.2.0.jar更新到xbean-2.3.0.jar，否则读取2007时会出现jar包冲突异常
*/
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.FileNotFoundException;
importjava.io.IOException;
importjava.io.InputStream;
importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;
importorg.apache.poi.ss.usermodel.Cell;
importorg.apache.poi.ss.usermodel.DateUtil;
importorg.apache.poi.ss.usermodel.Row;
importorg.apache.poi.ss.usermodel.Sheet;
importorg.apache.poi.ss.usermodel.Workbook;
importorg.apache.poi.ss.usermodel.WorkbookFactory;
publicclassPOIExcelReader{
publicPOIExcelReader(){
}
/**
*读取excel，支持excel97~03/excel07
*@paramfileName:文件名
*/
publicvoidread(StringfileName){
Workbookwb=null;
Filef=newFile(fileName);
FileInputStreamis;
try{
is=newFileInputStream(f);
wb=WorkbookFactory.create(is);
readWB(wb);
is.close();
}catch(FileNotFoundExceptione){
e.printStackTrace();
}catch(IOExceptione){
e.printStackTrace();
}catch(InvalidFormatExceptione){
e.printStackTrace();
}
}
/**
*读取Workbook
*@paramwb
*@throwsException
*/
privatevoidreadWB(Workbookwb){
try{
//读取sheet0
//for(intk=0;k<wb.getNumberOfSheets();k++){
//sheet
//Sheetsheet=wb.getSheetAt(k);
Sheetsheet=wb.getSheetAt(0);
inttotalRow=sheet.getPhysicalNumberOfRows();
System.out.println("totalRow="+totalRow);
readDatas(sheet);//按行读取
//--test
/*
System.out.println("PhysicalNumberOfRows:"+sheet.getPhysicalNumberOfRows());
System.out.println("FirstRowNum:"+sheet.getFirstRowNum());
System.out.println("LastRowNum:"+sheet.getLastRowNum());
*/
//}
}catch(Exceptione){
e.printStackTrace();
}
}
/**
*读取excel数据
*@paramsheet
*/
privatevoidreadDatas(Sheetsheet){
for(Rowrow:sheet){
for(Cellcell:row){
//System.out.println("列索引："+cell.getColumnIndex());
switch(cell.getCellType()){
caseCell.CELL_TYPE_STRING:
System.out.print(cell.getRichStringCellValue().getString());
break;
caseCell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
System.out.print(cell.getDateCellValue());
}else{
System.out.print(cell.getNumericCellValue());
}
break;
caseCell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
caseCell.CELL_TYPE_FORMULA:
System.out.print(cell.getCellFormula());//取得公式
System.out.println("公式值："+cell.getNumericCellValue());//取得公式值
break;
default:
System.out.println("");
}
}
System.out.println("\n");
}
}
/**
*获取合并单元格的值
*@paramsheet
*@paramrow
*@paramcolumn
*@return
*/
publicStringgetMergedRegionValue(Sheetsheet,introw,intcolumn){
intsheetMergeCount=sheet.getNumMergedRegions();
for(inti=0;i<sheetMergeCount;i++){
CellRangeAddressca=sheet.getMergedRegion(i);
intfirstColumn=ca.getFirstColumn();
intlastColumn=ca.getLastColumn();
intfirstRow=ca.getFirstRow();
intlastRow=ca.getLastRow();
if(row>=firstRow&&row<=lastRow){
if(column>=firstColumn&&column<=lastColumn){
RowfRow=sheet.getRow(firstRow);
CellfCell=fRow.getCell(firstColumn);
returngetCellValue(fCell);
}
}
}
returnnull;
}
publicstaticvoidmain(String[]args){
//POIExcelReaderpoie=newPOIExcelReader();
//poie.read("c:\\users.xlsx");
}
