使用ApachePOI读取Excel文件并将其存储到数组中

Question

我是编程界的新手。嗯，我正在尝试使用ApachePOI库读取excel文件（5行5列）。我实际上有两个相同问题的实现。在第一个代码片段中，我只是读取excel文件并将其打印到控制台中。

然而，现在我正试图将读取的excel数据保存到一个数组中。所以我想在动态获取excel行和列大小后设置数组大小。但令我惊讶的是，当我执行第二个代码段时，似乎“while（cellIterator.hasNext（）”会不断迭代，尽管我的输入excel文件中只有5行5列。请告诉我哪里出了问题。

感谢侯赛因代码片段1（按预期工作）

public static void main(String args[]) {
    readFile(ConfigReader.readConfigValues("XLS-path"),
            ConfigReader.readConfigValues("SheetName"));
}

public static void readFile(String filePath, String sheetName) {

    try {

        FileInputStream file = new FileInputStream(new File(filePath));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheet(sheetName);

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;
                }
            }
            System.out.println("");
        }
        file.close();
        FileOutputStream out = new FileOutputStream(new java.io.File(
                "G:\\test1.xls"));
        workbook.write(out);
        out.close();

    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

代码片段2（未按预期工作）

public static void main(String args[]) {
    readFile(ConfigReader.readConfigValues("XLS-path"),
            ConfigReader.readConfigValues("SheetName"));
}

public static void readFile(String filePath, String sheetName) {

    try {

        FileInputStream file = new FileInputStream(new File(filePath));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheet(sheetName);

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        String[][] excelArray = null;
        excelArray = new String[getRowCount(rowIterator, excelArray)][];

        file.close();
        FileOutputStream out = new FileOutputStream(new java.io.File(
                "G:\\test1.xls"));
        workbook.write(out);
        out.close();

    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

public static int getRowCount(Iterator<Row> rowIterator,
        String[][] excelArray) {
    int sizeArrayRow = 0;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        excelArray[sizeArrayRow] = new String[getColCount(row)];
        sizeArrayRow++;

    }

    return sizeArrayRow;
}

public static int getColCount(Row row) {

    int sizeArrayCol = 0;
    // For each row, iterate through each columns
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        sizeArrayCol++;
    }
    return sizeArrayCol;
}

Answer 1

 public class ExcelToArrayConverter {
        public String[] excelvalue(String columnWanted,int sheet_no){
            int i=0;
            String[] column_content_array =new String[140];
            try{
                int instindicator=-1;       
                InputStream fileIn = this.getClass().getClassLoader().getResourceAsStream("db.xls");
                POIFSFileSystem fs = new POIFSFileSystem(fileIn);
                HSSFWorkbook filename = new HSSFWorkbook(fs);
                HSSFSheet sheet = filename.getSheetAt(sheet_no);                                                // in the row 0 (which is first row of a work sheet)                                                    // search for column index containing string "Inst_Code"
                Integer columnNo = null;
                Integer rowNo = null;
                List<Cell> cells = new ArrayList<Cell>();
                Row firstRow = sheet.getRow(0);
                for (Cell cell : firstRow) {
                    if (cell.getStringCellValue().equals(columnWanted)) {
                        columnNo = cell.getColumnIndex();
                        rowNo=cell.getRowIndex();
                    }
                }
                if (columnNo != null) {
                    for (Row row : sheet) {
                        Cell c = row.getCell(columnNo);
                        String cell_value=""+c;
                        cell_value=cell_value.trim();
                        try{
                            if((!cell_value.equals(""))&&(!cell_value.equals("null"))&&(!cell_value.equals(columnWanted))){ 
                                column_content_array[i]=cell_value;
                                i++;
                            }}
                        catch(Exception e){
                        }

                    }
                    return column_content_array;
                }}
            catch(Exception ex){
                return column_content_array;
            }
            return column_content_array;

        }}

 This method will convert any specific column of a specific sheet to an array.

Answer 2

要了解Java迭代器是如何工作的，请阅读以下内容：http://www.tutorialspoint.com/java/java_using_iterator.htm特别是，请注意hasNext（）和Next（）之间的区别

您正在检查：

while (cellIterator.hasNext())

但你永远不会从迭代器中“读取”任何内容，所以它会保持在当前位置，并在下一步返回true。你可以用以下方法把它推到前面：

cellIterator.next();

此外，我认为代码很混乱，很难理解。而不是使用字符串数组，考虑使用列表。这样，您就可以在不预先知道其大小的情况下填充它。