java apache poi (part 1)

问题:

• excel file (staff.xls)
  ID           name
  1             ali
  2             abu
  3             ahmad
  

• java code

  FileInputStream inputFile = new FileInputStream("staff.xls");
  XSSFWorkbook workbook = new XSSFWorkbook(inputFile);
  XSSFSheet spreadsheet = workbook.getSheetAt(0);
  XSSFRow row;
  Cell cell;
  
  Iterator<Row> rowIterator = spreadsheet.iterator();
  
  while(rowIterator.hasNext()){
       row = (XSSFRow)rowIterator.next();
  
       Iterator<Cell> cellIterator = row.cellIterator();
  
       while(cellIterator.hasNext()){
           cell = cellIterator.next();
           cell.setCellType(Cell.CELL_TYPE_STRING); 
  
       switch(cell.getCellType()){
           case Cell.CELL_TYPE_STRING:
              System.out.print(cell.getStringCellValue()+"|");
              break;
           case Cell.CELL_TYPE_NUMERIC:
              System.out.print(cell.getNumericCellValue()+"|");
              break;
           }
       }
  
       System.out.println();
  }
  

• My question is:
  (1) How to put the record into the array or arraylist?
  (2) After create, how to split the "|"?

回答1:

Just create a List of List variable before the first while, in the beginning of every iteration create a new List, put the elements to this list and add this list to the main List of Lists at the end of the iteration. You should get something like that:

...

List<List<String>> records = new ArrayList<List<String>>();

while(rowIterator.hasNext()){
    List<String> record = new ArrayList<String>();

    row = (XSSFRow)rowIterator.next();

    Iterator<Cell> cellIterator = row.cellIterator();

    while(cellIterator.hasNext()){
        cell = cellIterator.next();
        cell.setCellType(Cell.CELL_TYPE_STRING);

        switch(cell.getCellType()){
            case Cell.CELL_TYPE_STRING:
                record.add(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                record.add(Double.toString(cell.getNumericCellValue()));
                break;
        }
    }

    records.add(record);
}

for (List<String> record : records) {
    for (String s : record) {
        System.out.print(" " + s);
    }

    System.out.println();
}

...

Also notice that you don't need to add | symbol anymore, so no need to split eventually. But in general to split the string there is a method String#split() which accepts a regular expression. You need to use it like that to split by "|" (you need to put \ before | as it is a special regexp character):

for (String record : records) {
    System.out.println(record);

    String[] elements = record.split("\\|");
    for (String element : elements) {
        System.out.println(" -> " + element);
    }
}

回答2:

Here what i did is I removed the pipe symbol and i added into Array List. Hope this helps u.

FileInputStream inputFile = new FileInputStream("staff.xls");
XSSFWorkbook workbook = new XSSFWorkbook(inputFile);
XSSFSheet spreadsheet = workbook.getSheetAt(0);
XSSFRow row;
Cell cell;

List<String> l2 = new LinkedList<String>();
Iterator<Row> rowIterator = spreadsheet.iterator();

while(rowIterator.hasNext()){
    row = (XSSFRow)rowIterator.next();

    Iterator<Cell> cellIterator = row.cellIterator();

    while(cellIterator.hasNext()){
            cell = cellIterator.next();
            cell.setCellType(Cell.CELL_TYPE_STRING); 

            switch(cell.getCellType()){
            case Cell.CELL_TYPE_STRING:

            l2.add(cell.getStringCellValue());

                break;
            case Cell.CELL_TYPE_NUMERIC:

                l1.add((int) cell.getNumericCellValue());
                System.out.println(l1);
                break;
            }
        }
    }

    System.out.println(l2);
}
}