poi读取excel,包含所有excel版本，兼容excel2024和2024以上版本

package com.excel;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream;

import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.xmlbeans.impl.piccolo.io.FileFormatException; /**

* 读取EXCEL内容 * @author lxr * */

public class TestReadExcelAll { private static final String EXTENSION_XLS = \ private static final String EXTENSION_XLSX = \ /** * 判断EXCEL版本 * @param in * @param filename * @return * @throws IOException */ public static Workbook getWorkbook(InputStream in,String filename)throws IOException {

}

Workbook wb = null;

if(filename.endsWith(EXTENSION_XLS)){ wb = new HSSFWorkbook(in);//Excel 2003 } else if(filename.endsWith(EXTENSION_XLSX)){ wb = new XSSFWorkbook(in);//Excel 2007 }

return wb;

/**

* 文件校验是否是excel *

* @param filePath

* @throws FileNotFoundException * @throws FileFormatException */

private void preReadCheck(String filePath) throws FileNotFoundException, FileFormatException { // 常规检查 File file = new File(filePath); if (!file.exists()) { }

/**

* 读取EXCEL * @param filePath

* @throws FileNotFoundException * @throws FileFormatException */

public void readExcel(String filePath) throws FileNotFoundException,FileFormatException { // 检查 preReadCheck(filePath); // 获取workbook对象 Workbook workbook = null;

}

throw new FileNotFoundException(\传入的文件不存在：\

if (!(filePath.endsWith(EXTENSION_XLS) || filePath .endsWith(EXTENSION_XLSX))) { }

throw new FileFormatException(\传入的文件不是excel\

InputStream is = new FileInputStream(filePath); try { workbook = getWorkbook(is, filePath); // workbook = WorkbookFactory.create(is); // 读文件 一个sheet一个sheet地读取 for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) { Sheet sheet = workbook.getSheetAt(numSheet); if (sheet == null) { continue; } int sheetCount = workbook.getNumberOfSheets(); //Sheet的数量 int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数 int firstRowIndex = sheet.getFirstRowNum(); int lastRowIndex = sheet.getLastRowNum(); if (firstRowIndex != lastRowIndex && lastRowIndex != 0) { System.out.println(\+ sheet.getSheetName()+ \ // 读取数据行 for (int rowIndex = 0; rowIndex <= lastRowIndex; rowIndex++) { Row currentRow = sheet.getRow(rowIndex);// 当前行 int firstColumnIndex = currentRow.getFirstCellNum(); // 首列 int lastColumnIndex = currentRow.getLastCellNum();// 最后一列 for (int columnIndex = firstColumnIndex; columnIndex <= lastColumnIndex; columnIndex++) { Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格 String currentCellValue = this.getCellValue(currentCell, true);// 当前单元格的值 System.out.print(currentCellValue + \ } System.out.println(\ } System.out.println(\ System.out.println(\

// // // // // // //

}

}

System.out.println(\

}

} catch (Exception e) { e.printStackTrace(); } finally { if (workbook != null) { try { workbook.close(); } catch (IOException e) { e.printStackTrace(); } } }

/**

* 取单元格的值 *

* @param cell

* 单元格对象 * @param treatAsStr

* 为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”) * @return */

private String getCellValue(Cell cell, boolean treatAsStr) { if (cell == null) { return \ }

if (treatAsStr) {

// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0” // 加上下面这句，临时把它当做文本来读取 cell.setCellType(Cell.CELL_TYPE_STRING); }

SimpleDateFormat fmt = new SimpleDateFormat(\String cellValue = null;

int cellType = cell.getCellType(); switch (cellType) {

case Cell.CELL_TYPE_STRING: // 文本 cellValue = cell.getStringCellValue(); break;

} /**

case Cell.CELL_TYPE_NUMERIC: // 数字、日期 if (DateUtil.isCellDateFormatted(cell)) { cellValue = fmt.format(cell.getDateCellValue()); // 日期型 } else { cell.setCellType(Cell.CELL_TYPE_STRING);

cellValue = String.valueOf(cell.getNumericCellValue()); // 数字 }

break;

case Cell.CELL_TYPE_BOOLEAN: // 布尔型 cellValue = String.valueOf(cell.getBooleanCellValue()); break;

case Cell.CELL_TYPE_BLANK: // 空白 cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_ERROR: // 错误 cellValue = \错误\ break; case Cell.CELL_TYPE_FORMULA: // 公式 cellValue = \错误\ break; default: cellValue = \错误\}

return cellValue;

* 测试

* @param args

* @throws FileNotFoundException * @throws FileFormatException */

public static void main(String[] args) throws FileNotFoundException, FileFormatException { TestReadExcelAll e3 = new TestReadExcelAll(); e3.readExcel(\ e3.readExcel(\}

}