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;
  }
Example #2
0
    /**
     * 读取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;
    }