private Collection<Object[]> loadFromSpreadsheet(final InputStream excelFile) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(excelFile); data = new ArrayList<Object[]>(); Sheet sheet = workbook.getSheetAt(0); int numberOfColumns = countNonEmptyColumns(sheet); List<Object[]> rows = new ArrayList<Object[]>(); List<Object> rowData = new ArrayList<Object>(); for (Row row : sheet) { if (isEmpty(row)) { break; } else { rowData.clear(); for (int column = 0; column < numberOfColumns; column++) { Cell cell = row.getCell(column); rowData.add(objectFrom(workbook, cell)); } rows.add(rowData.toArray()); } } return rows; }
/** * 读取97-2003格式 * * @param filePath 文件路径 * @throws java.io.IOException */ @SuppressWarnings("rawtypes") public static List<Map> readExcel2003(String filePath) throws IOException { // 返回结果集 List<Map> valueList = new ArrayList<Map>(); FileInputStream fis = null; try { fis = new FileInputStream(filePath); HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用 HSSFSheet sheet = wookbook.getSheetAt(0); // 在Excel文档中,第一张工作表的缺省索引是0 int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数 Map<Integer, String> keys = new HashMap<Integer, String>(); int cells = 0; // 遍历行(第1行 表头) 准备Map里的key HSSFRow firstRow = sheet.getRow(0); if (firstRow != null) { // 获取到Excel文件中的所有的列 cells = firstRow.getPhysicalNumberOfCells(); // 遍历列 for (int j = 0; j < cells; j++) { // 获取到列的值 try { HSSFCell cell = firstRow.getCell(j); String cellValue = getCellValue(cell); keys.put(j, cellValue); } catch (Exception e) { e.printStackTrace(); } } } // 遍历行(从第二行开始) for (int i = 1; i < rows; i++) { // 读取左上端单元格(从第二行开始) HSSFRow row = sheet.getRow(i); // 行不为空 if (row != null) { // 准备当前行 所储存值的map Map<String, Object> val = new HashMap<String, Object>(); boolean isValidRow = false; // 遍历列 for (int j = 0; j < cells; j++) { // 获取到列的值 try { HSSFCell cell = row.getCell(j); String cellValue = getCellValue(cell); val.put(keys.get(j), cellValue); if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) { isValidRow = true; } } catch (Exception e) { e.printStackTrace(); } } // 第I行所有的列数据读取完毕,放入valuelist if (isValidRow) { valueList.add(val); } } } } catch (IOException e) { e.printStackTrace(); } finally { fis.close(); } return valueList; }