POI读取excel2003和2007方法是一样的,主要就是用得类不同而已,读取excel2003只需要一个jar包,而读取2007则需要poi3.5以上版本里面的所有jar包,下面将提供代码和jar:
/**
* Excel工具类
* @author Administrator
*
*/
public class Excel {
private Workbook workbook;
public Workbook getWorkbook() {
return workbook;
}
public void setWorkbook(Workbook workbook) {
this.workbook = workbook;
}
public Excel(File file) {
InputStream is = null;
POIFSFileSystem ps = null;
try {
is = new FileInputStream(file);
try{
ps = new POIFSFileSystem(is);
this.workbook = new HSSFWorkbook(ps);
}catch (Exception e) {
try {
OPCPackage opcPackage = OPCPackage.openOrCreate(file);
this.workbook = new XSSFWorkbook(opcPackage);
} catch (Exception e2) {
e2.printStackTrace();
System.out.println("要读取的excel文件必须为2003或2007格式");
}
}
} catch (FileNotFoundException e) {
System.out.println("找不到文件");
e.printStackTrace();
} finally {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public Sheet getSheet(String sheetName) {
return workbook.getSheet(sheetName);
}
/**
* 根据列名得到值
*
* @param columnName
* @return
*/
public String getValue(int rowIndex, int cellIndex) throws Exception {
String returnValue = "";
DecimalFormat decimalFormat = new DecimalFormat("#");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(rowIndex);
Cell cell = null;
if (row != null) {
cell = row.getCell(cellIndex);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 如果是date类型则 ,获取该cell的date值
returnValue = dateFormat.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
} else {// 纯数字
returnValue = decimalFormat.format(cell.getNumericCellValue());
}
break;
/* 此行表示单元格的内容为string类型 */
case HSSFCell.CELL_TYPE_STRING: // 字符串型
returnValue = cell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_FORMULA:// 公式型
// 读公式计算值
returnValue = String.valueOf(cell.getNumericCellValue());
if (returnValue.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
returnValue = cell.getRichStringCellValue().toString();
}
// cell.getCellFormula();读公式
break;
case HSSFCell.CELL_TYPE_BOOLEAN:// 布尔
returnValue = "" + cell.getBooleanCellValue();
break;
/* 此行表示该单元格值为空 */
case HSSFCell.CELL_TYPE_BLANK: // 空值
returnValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
returnValue = "";
break;
default:
returnValue = cell.getRichStringCellValue().toString();
}
} else {
returnValue = "";
}
}else{
returnValue = "";
}
return returnValue;
}
/**
* 得到所有的记录数
*
* @return
*/
public int getRowCount() {
return workbook.getSheetAt(0).getLastRowNum();
}
/**
* 得到Excel表里面的字段名
*
* @return
*/
public List<String> getColumnNames() {
List<String> columnNames = new ArrayList<String>();
Sheet sheet = workbook.getSheetAt(0);
Row columnNameRow = sheet.getRow(0);
for (int i = 0; i < columnNameRow.getLastCellNum(); i++) {
columnNames.add(columnNameRow.getCell(i).getStringCellValue());
}
return columnNames;
}
public static void main(String[] args) {
Excel excel = new Excel(new File("D:\\23.xlsx"));
System.out.println(excel.getRowCount());
}
}