java 使用 apache poi 读取 Excel 文件并将它们存储到数组中

Reading Excel file using apache poi and storing them into arrays

提问by Hussain

I'm new to the world of programming. well, im trying to read a excel file (5 rows, 5 cols)using apache-poi library. I have actually two implementation of the same problem. In the first code snippet, i just read my excel files and print them into console.

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

However now im trying to save the read excel data into an array. So i want to set the array size after getting the excel row and column size dynamically. But to my surprise, when i execute my second code snippet, it seems that "while(cellIterator.hasNext()" iterates continuously even though there are only 5 rows, 5 cols in my input excel file. Please guide me where im going wrong.

但是现在我试图将读取的 excel 数据保存到数组中。所以我想在动态获取excel行和列大小后设置数组大小。但令我惊讶的是，当我执行我的第二个代码片段时，即使我的输入 excel 文件中只有 5 行、5 列，“while(cellIterator.hasNext()”似乎也在不断迭代。请指导我哪里出错了.

Thanks Hussain Code snippet 1 (Working as expected)

谢谢侯赛因代码片段 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();
    }
}

Code snippet 2 (Not working as expected)

代码片段 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;
}

回答by Darius X.

To see how Java iterators work, read this: http://www.tutorialspoint.com/java/java_using_iterator.htmIn particular, notice the difference between hasNext() and next()

要了解 Java 迭代器如何工作，请阅读：http: //www.tutorialspoint.com/java/java_using_iterator.htm 特别注意 hasNext() 和 next() 之间的区别

You are checking:

您正在检查：

while (cellIterator.hasNext())

but you are never "reading" anything from that iterator, so it stays at its current position and keeps returning true for hasNext. You could push it formward with:

但是您永远不会从该迭代器“读取”任何内容，因此它保持在当前位置并为 hasNext 不断返回 true。您可以通过以下方式推动它：

cellIterator.next();

Also, I think the code is messy and difficult to follow. Instead of a String array, consider using a List. That way, you can populate it without knowing its size in advance.

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

回答by Anupam Maiti

 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.