一、示例
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;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 WorkBookReadExcel { public Workbook readWorkBook(File file) { Workbook wb = null; InputStream is = null; try { // 判断文件是否存在 if (null == file || !file.exists()) { return null; } is = new FileInputStream(file); /** 使用WorkbookFactory不用再去判断Excel因为版本不同而使用不同的方法 */ wb = WorkbookFactory.create(is); is.close(); } catch (IOException e) { e.printStackTrace(); } catch (InvalidFormatException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (null != is) { try { is.close(); } catch (Exception e) { is = null; e.printStackTrace(); } } } return wb; } public List<List<String>> read(Workbook wb) { List<List<String>> dataList = new ArrayList<List<String>>(); /** 得到第一个shell */ Sheet sheet = wb.getSheetAt(0); /** 得到Excel的总行数 */ int rowCount = sheet.getPhysicalNumberOfRows(); /** 循环Excel的行 */ for (int i = 0; i < rowCount; i++) { Row row = sheet.getRow(i); if (null == row) { continue; } List<String> rowList = new ArrayList<String>(); /** 循环Excel的列 */ for (int c = 0; c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil .isCellDateFormatted(cell)) { SimpleDateFormat fmt = new SimpleDateFormat( "yyyy-MM-dd"); cellValue = fmt.format(cell.getDateCellValue()); } else { Double d = cell.getNumericCellValue(); cellValue = d.toString(); // 解决1234.0 去掉后面的.0 if (null != cellValue && !"".equals(cellValue.trim())) { String[] item = cellValue.split("[.]"); if (1 < item.length && "0".equals(item[1])) { cellValue = item[0]; } } } 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 = cell.getStringCellValue(); break; default: cellValue = cell.getStringCellValue(); } } // System.out.print(cellValue + "\t"); rowList.add(cellValue); } dataList.add(rowList); } return dataList; }}
