/**
* 需要如下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
*/
import java.io.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
public class POIExcelUtils {
public POIExcelUtils() {
}
/**
* 读取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) {
System.out.println(e.getMessage());
} catch (IOException e) {
System.out.println(e.getMessage());
} catch (InvalidFormatException e) {
System.out.println(e.getMessage());
}
}
/**
* 读取excel，支持excel 97~03 / excel 07
* @param is : 文件流
*/
public void read(InputStream is) {
Workbook wb;
try {
wb = WorkbookFactory.create(is);
readWB(wb);
is.close();
} catch (InvalidFormatException e) {
System.out.println(e.getMessage());
} catch (IOException e) {
System.out.println(e.getMessage());
}
}
/**
* 读取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);
readRows(sheet); // 按行读取
//-- test
/*
System.out.println("PhysicalNumberOfRows:"+sheet.getPhysicalNumberOfRows());
System.out.println("FirstRowNum:"+sheet.getFirstRowNum());
System.out.println("LastRowNum:"+sheet.getLastRowNum());
*/
//}
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
/**
* 读取每一行
* @param rows : 有效行数 /非空行数
*/
private void readRows(Sheet sheet) {
int rows = sheet.getPhysicalNumberOfRows();
System.out.println(rows);
int rowIndex = 0; //每行索引
int notnullRowIndex = 0; //非空行索引
while (notnullRowIndex < rows) {
Row row = sheet.getRow(rowIndex);
rowIndex++;
if (row != null) {
readCells(row);
notnullRowIndex++;
}
}
}
/**
* 读取每一行的单元格
* @param row : 所在行数据
*/
private void readCells(Row row) {
int cells = row.getPhysicalNumberOfCells();
int cellIndex = 0; //单元格索引
int notnullCellIndex = 0; //非空单元格索引
while(notnullCellIndex < cells) {
Cell cell = row.getCell(cellIndex);
cellIndex++;
if (cell != null) {
String value = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
value = "FORMULA value=" + cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)){
value = "DATE value=" + cell.getDateCellValue();
}else{
value = "NUMERIC value=" + cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_STRING:
value = "STRING value=" + cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = "BOOLEAN value="
+ cell.getBooleanCellValue();
break;
default:
}
notnullCellIndex++;
System.out.println(value);
}
}
}
}
