poi-3.7读取excel,支持excel 97~03 / excel 07

/**

* 需要如下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包冲突异常
*/
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class POIExcelReader {   
    public POIExcelReader() {
    }

    /**
     * 读取excel，支持excel 97~03 / excel 07
     * @param fileName : 文件名
     */
    public void read(String fileName) {
       Workbook wb = null;
       File f = new File(fileName);
       FileInputStream is;
       try {
           is = new FileInputStream(f);
           wb = WorkbookFactory.create(is);
           readWB(wb);
           is.close();
       } catch (FileNotFoundException e) {
           e.printStackTrace();
       } catch (IOException e) {
       e.printStackTrace();
       } catch (InvalidFormatException e) {
       e.printStackTrace();
       }     
    }
   
    /**  
     * 读取Workbook  
     * @param wb  
     * @throws Exception  
     */  
    private void readWB(Workbook wb){   
        try {   
        // 读取sheet0
            //for (int k = 0; k < wb.getNumberOfSheets(); k++) {      
                //sheet   
                //Sheet sheet = wb.getSheetAt(k);  
             Sheet sheet = wb.getSheetAt(0);
             int totalRow = 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 (Exception e) {      
        e.printStackTrace();
        }   
    }

    /**
     * 读取excel数据
     * @param sheet
     */
    private void readDatas(Sheet sheet) {
    for (Row row : sheet) {
for (Cell cell : row) {
// System.out.println("列索引："+cell.getColumnIndex());

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.print(cell.getDateCellValue());
} else {
System.out.print(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.print(cell.getCellFormula()); //取得公式
System.out.println("公式值："+cell.getNumericCellValue()); //取得公式值
break;
default:
System.out.println("");
}
}
System.out.println("\n");
}
    }   
  
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();

for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();

if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);

return getCellValue(fCell);
}
}
}

return null;
}
   
    public static void main(String[] args) {
// POIExcelReader poie = new POIExcelReader();
// poie.read("c:\\users.xlsx");
   
}

评论

1 楼 wmm890122 2012-04-19  

需要将xbean-2.2.0.jar更新到xbean-2.3.0.jar，否则读取2007时会出现jar包冲突异常 .

是直接引入的包为
xbean-2.3.0.jar就可以了吗？