Java 使用Apache POI从excel文件中读取单元格值

Reading cell values from excel file using Apache POI

提问by RalphF

I'm using Selenium 2 and have set up a LoginTest.java file that reads from a data driven excel file. I read in username and password and run it on a login page. It actually runs fine. The data looks like the following:

我正在使用 Selenium 2 并设置了一个 LoginTest.java 文件，该文件从数据驱动的 excel 文件中读取。我读入用户名和密码并在登录页面上运行它。它实际上运行良好。数据如下所示：

user1 pw1
user2 pw2
user3 pw3
user4 pw4

user1 pw1
user2 pw2
user3 pw3
user4 pw4

The problem is this: if I modify the excel file and change the number of arguments to something like this:

问题是这样的：如果我修改 excel 文件并将参数的数量更改为这样的：

user1 pw1
user2 pw2

用户 1 密码 1 用户 2 密码
2

and run the same script, the ReadExcelclass file returns an error msg:

并运行相同的脚本，ReadExcel类文件返回错误消息：

"There is no support for this type of cell".

“不支持这种类型的单元格”。

What is happening I think, is that rows 3 and 4 usedto contain data in their cells (user3 pw3 and user4 pw4) and now they don't...so there is something different about those previously used cells that my ExcelRead()class isn't catching to ignore. I'll bet the cell is now 'null' where it previously wasn't or vice versa. Something is different about the cells which previously held data vs cells that have never held data.

我认为正在发生的事情是，第 3 行和第 4 行曾经在它们的单元格（user3 pw3 和 user4 pw4）中包含数据，现在它们没有......所以我的ExcelRead()班级以前使用的那些单元格有些不同t 捕捉忽略。我敢打赌，该单元格现在是“空”，而以前不是，反之亦然。以前保存数据的单元格与从未保存数据的单元格有些不同。

(I found ExcelReadon the internet and am using it. I didn't create it from scratch myself. It seems to work fine reading xcel files except for this one 'issue').

（我ExcelRead在互联网上找到并正在使用它。我没有自己从头开始创建它。除了这个“问题”之外，它似乎可以很好地读取 xcel 文件）。

Thanks for any help.

谢谢你的帮助。

public class ExcelRead {
    public Object[][] main( String[] args) throws Exception{        
    File excel = new File(args[0]);
    FileInputStream fis = new FileInputStream(excel);
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet ws = wb.getSheet(args[1]);

    int rowNum = ws.getLastRowNum() + 1;
    int colNum = ws.getRow(0).getLastCellNum();
    String[][] data = new String[rowNum][colNum];

    for (int i = 0 ; i < rowNum ; i++) {
        HSSFRow row = ws.getRow(i);
            for (int j = 0 ; j < colNum ; j++) {
                HSSFCell cell = row.getCell(j);
                String value = cellToString(cell);
                data[i][j] = value ;
                //System.out.println("the value is " + value);
            }
        }
    return data;
    }

public static String cellToString(HSSFCell cell) {  
    int type;
    Object result;
    type = cell.getCellType();

    switch (type) {

        case 0: // numeric value in Excel
            result = cell.getNumericCellValue();
            break;
        case 1: // String Value in Excel 
            result = cell.getStringCellValue();
            break;
        default:  
            throw new RuntimeException("There is no support for this type of cell");                        
    }

    return result.toString();
}

回答by Faiz

The code you have downloaded uses the Java Apache POI library to read the excel file. If you go through the code, you will see that the cellToString()method does not handle all types of cell types - it looks for only numeric and string cells, and throws the exception you see otherwise.

您下载的代码使用 Java Apache POI 库读取 excel 文件。如果您仔细阅读代码，您将看到该cellToString()方法不会处理所有类型的单元格类型 - 它仅查找数字和字符串单元格，并抛出您在其他情况下看到的异常。

Once you removed the cell values from the rows, the cell values are now blank and you get a cell type of CELL_TYPE_BLANK.

从行中删除单元格值后，单元格值现在为空白，并且单元格类型为CELL_TYPE_BLANK.

You need to extend the switchstatement in the cellToString()method to handle other cell types such as Cell.CELL_TYPE_BLANK, Cell.CELL_TYPE_BOOLEANetc.

您需要延长switch在声明cellToString()的方法来处理其他类型的细胞，例如Cell.CELL_TYPE_BLANK，Cell.CELL_TYPE_BOOLEAN等等。

Refer to the Apache POI documentationon the Cellinterface to see what the different cell types are and how to handle each.

请参阅界面上的Apache POI 文档，Cell了解不同的单元格类型是什么以及如何处理每个单元格类型。

public static String cellToString(HSSFCell cell) {  
    int type;
    Object result;
    type = cell.getCellType();

    switch (type) {

        case Cell.CELL_TYPE_NUMERIC: // numeric value in Excel
        case Cell.CELL_TYPE_FORMULA: // precomputed value based on formula
            result = cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_STRING: // String Value in Excel 
            result = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            result = "";
        case Cell.CELL_TYPE_BOOLEAN: //boolean value 
            result: cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_ERROR:
        default:  
            throw new RuntimeException("There is no support for this type of cell");                        
    }

    return result.toString();
}

回答by Faiz

Add this.

添加这个。

case 3: break;

In fact you need to check for empty Cell Type (HSSFCell.CELL_TYPE_BLANK).

事实上，您需要检查空单元格类型 ( HSSFCell.CELL_TYPE_BLANK)。

Also what you can do in case you find an empty cell, is stopping the forloop with i=rowNum.

如果您找到一个空单元格，您还可以做的是for使用i=rowNum.

There are more than two types of cell . The getCellType()method can return these integers:

有两种以上的细胞类型。该getCellType()方法可以返回这些整数：

Cell.CELL_TYPE_NUMERIC, Cell.CELL_TYPE_STRING, Cell.CELL_TYPE_FORMULA, Cell.CELL_TYPE_BLANK, Cell.CELL_TYPE_BOOLEAN, Cell.CELL_TYPE_ERROR

Edited:

编辑：

As per the observation of Faiz, this will get an exception as Object resultis not initialized and unassigned. Instead, write case 3: return "";it will return an empty string when a blank cell is found. Anyway, your algorithm is not the most efficient. Instead of searching through a pre-defined large area, you should search the cells until you find empty cells (if there is no empty row in your sheet). You should be able to use ArrayList instead of Array, and thus completely avoiding to process empty string during the password checking process.

根据Faiz的观察，这将得到一个Object result未初始化和未分配的异常。相反，写case 3: return "";它会在找到空白单元格时返回一个空字符串。无论如何，您的算法并不是最有效的。您应该搜索单元格，而不是搜索预定义的大区域，直到找到空单元格（如果工作表中没有空行）。您应该能够使用 ArrayList 而不是 Array，从而完全避免在密码检查过程中处理空字符串。

回答by UdayKiran Pulipati

Read Data from Excel using Java you need below jars. Download it from this link

使用 Java 从 Excel 中读取数据，您需要在 jars 下。从这个链接下载

poi-4.1.0.jar 
poi-ooxml-4.1.0.jar
xmlbeans-3.1.0.jar 
ooxml-schemas-1.4.jar 
commons-compress-1.18.jar
xlsx-streamer-2.1.0.jar

To Read Excel file place it in downloads or user desired location. Below program Uses following Excel file.

要读取 Excel 文件，请将其放置在下载或用户所需的位置。下面的程序使用以下Excel 文件。

Excel file may contains different types of CellTypes. While you read the columns data you need to mention mostly available CellTypes which is supported by Apache POI.

Excel 文件可能包含不同类型的 CellType。在阅读列数据时，您需要提及Apache POI支持的大部分可用 CellType 。

I refer following cases BOOLEAN, STRING, NUMERIC, FORMULA, BLANK, _NONE, ERROR,and if none of the case supports it goes to defaultin getCellValueAsStringmethod.

我是指下列情形BOOLEAN, STRING, NUMERIC, FORMULA, BLANK, _NONE, ERROR,，如果没有的情况下支持它去default的getCellValueAsString方法。

package com.java.file.excel;

import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.monitorjbl.xlsx.StreamingReader;

/**
 * 
 * @author udaykiran p
 *
 */
public class ReadExcelFile {

    public static void main(String[] args) {
        try {
            FileInputStream inputStream = new FileInputStream("C:\Users\udaykiranp\Downloads\Users.xlsx");// Read Excel file from this location
            if (inputStream != null) {
                Workbook wb = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (default to 10)
                        .bufferSize(4096) // buffer size is to use when reading InputStream to file (defaults to 1024)
                        .open(inputStream);
                Sheet sheet = wb.getSheetAt(0);//reading first sheet. You can pass argument as well.
                System.out.println("Excel Reading - Number Of Sheets: "+ wb.getNumberOfSheets() +", Active Sheet: "+ sheet.getSheetName());
                Map<String, String> map = new HashMap<String, String>();
                Iterator<Row> iterator = sheet.iterator();
                int rowCount = 0;
                while(iterator.hasNext()) {
                    Row row = iterator.next();
                    rowCount = row.getRowNum();
                    rowCount++;
                    int columnNum = 0;
                    String key = null, value = null;
                    for(Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) {
                        Cell cell = cellIterator.next();
                        columnNum = cell.getColumnIndex();
                        String cellData = getCellValueAsString(cell);
                        System.out.println("RowNumber: "+ rowCount +", CellData: "+ cellData +", CellNumber: "+ columnNum);
                        //Reading data from Excel upto 6 rows only
//                      if (rowCount == 6) {
                            //rowCount == 1 Headers Section(User ID, User Name)
                            if (rowCount > 1) {
                                if (columnNum == 0) {
                                    key = cellData;//User ID
                                }
                                if (columnNum == 1) {
                                    value = cellData;//User Name
                                }
                            }
//                      }
                    }
                    if (key != null && value != null) {
                        map.put(key, value);
                    }
                }
                String userID = "1";
                System.out.println("User ID: "+ userID +", User Name: "+ map.get("1"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String getCellValueAsString(Cell cell) {
        String cellValue = null;
        switch(cell.getCellType()) {
        case BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        case STRING:
            cellValue = String.valueOf(cell.getRichStringCellValue().toString());
            break;
        case NUMERIC:
            Double value = cell.getNumericCellValue();
            if (value != null) {
                String valueAsStr = value.toString();
                int indexOf = valueAsStr.indexOf(".");
                if (indexOf > 0) {
                    cellValue = valueAsStr.substring(0, indexOf);//decimal numbers truncated
                } else {
                    cellValue = value.toString();
                }
            }
            break;
        case FORMULA:
            //if the cell contains formula, this case will be executed.
            cellValue = cell.getStringCellValue();
            break;
        case BLANK:
            cellValue = "";
            break;
        case _NONE:
            cellValue = "";
            break;
        case ERROR:
            throw new RuntimeException("There is no support for this type of cell");
        default:
            cellValue = "";
        }
        return cellValue;
    }
}

OutPut:

输出：

DEBUG [main] (StreamingWorkbookReader.java:89) - Created temp file 

[C:\Users\UDAY~1\AppData\Local\Temp\tmp-6803178981463652112.xlsx]
Excel Reading - Number Of Sheets: 1, Active Sheet: Sheet1
RowNumber: 1, CellData: User ID, CellNumber: 0
RowNumber: 1, CellData: User Name, CellNumber: 1
RowNumber: 2, CellData: 1, CellNumber: 0
RowNumber: 2, CellData: Steve Jobs, CellNumber: 1
RowNumber: 3, CellData: 2, CellNumber: 0
RowNumber: 3, CellData: Bill Gates, CellNumber: 1
RowNumber: 4, CellData: 3, CellNumber: 0
RowNumber: 4, CellData: Sergey Brin, CellNumber: 1
RowNumber: 5, CellData: 4, CellNumber: 0
RowNumber: 5, CellData: Fritz Sennheiser, CellNumber: 1
RowNumber: 6, CellData: 5, CellNumber: 0
RowNumber: 6, CellData: Thomas Olsen, CellNumber: 1
User ID: 1, User Name: Steve Jobs