如何使用 java apache poi 从 xlsx 文件的特定单元格中获取值

How to get value from a specific cell of an xlsx file using java apache poi

提问by Maruthi Srinivas

I am writing a java program to read an excel sheet (xlsx) using apache poi. I am able to iterate through all the cells and get all the values. But I am unable to get a specific cell value, say E10. Is there any way to do this?

我正在编写一个java程序来使用apache poi读取excel表（xlsx）。我能够遍历所有单元格并获取所有值。但是我无法获得特定的单元格值，例如 E10。有没有办法做到这一点？

Please see the code below that I used for iterating through the cells.

请参阅下面我用于迭代单元格的代码。

package application;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadFromXLSX {
    public static void readXLSXFile() throws IOException
    {
        InputStream ExcelFileToRead = new FileInputStream("C:\Test.xlsx");
        XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);
        XSSFWorkbook test = new XSSFWorkbook(); 
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row; 
        XSSFCell cell;
        Iterator rows = sheet.rowIterator();
        while (rows.hasNext())
        {
            row=(XSSFRow) rows.next();
            Iterator cells = row.cellIterator();
            while (cells.hasNext())
            {
                cell=(XSSFCell) cells.next();   
                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
                {
                    System.out.print(cell.getStringCellValue()+" ");
                }
                else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
                {
                    System.out.print(cell.getNumericCellValue()+" ");
                }
                else
                {
                }
            }
            System.out.println();
        }
    }   
}

采纳答案by wvdz

For example, to get E10 of the first worksheet:

例如，要获取第一个工作表的 E10：

wb.getSheetAt(0).getRow(9).getCell(4); 

Note: subtract one because the indices are null-based.

注意：减一，因为索引是基于空值的。

You can also use this convenience method to map E to 4.

您还可以使用这种方便的方法将 E 映射到 4。

wb.getSheetAt(0).getRow(9).getCell(CellReference.convertColStringToIndex("E"));

回答by Devanshu Dwivedi

XSSFSheet has the method getRow(int rownum) It returns the logical row ( 0-based). If you ask for a row that is not defined you get a null. This is to say row 4 represents the fifth row on a sheet.

XSSFSheet 有方法 getRow(int rownum) 它返回逻辑行（基于 0）。如果您要求未定义的行，您会得到一个空值。也就是说，第 4 行表示工作表上的第五行。

Once you get the row, you can call getCell(int cellnum) method of XSSFRow object. It returns the cell at the given (0 based) index.

获得行后，您可以调用 XSSFRow 对象的 getCell(int cellnum) 方法。它返回给定（基于 0）索引处的单元格。

回答by Maruthi Srinivas

To get a value from a specific cell in excel you can use the below code line.

要从 excel 中的特定单元格获取值，您可以使用以下代码行。

wb.getSheetAt(0).getRow(1).getCell(1);

回答by midishero

Just version-up the getCellmethod

只需对getCell方法进行版本升级

public XSSFCell getCell(String cellName){
    Pattern r = Pattern.compile("^([A-Z]+)([0-9]+)$");
    Matcher m = r.matcher(cellName);
    XSSFWorkbook wb = new XSSFWorkbook();
    if(m.matches()) {
        String columnName = m.group(1);
        int rowNumber = Integer.parseInt(m.group(2));
        if(rowNumber > 0) {
            return wb.getSheetAt(0).getRow(rowNumber-1).getCell(CellReference.convertColStringToIndex(columnName));
        }
    }
    return null;
}

Now you can get the cell easily by this line

现在您可以通过这条线轻松获取单元格

getCell("E10")

回答by Shriganesh jadhav

public class XmlFileRead {

public static void main(String[] args) throws IOException {
    FileInputStream fi = new FileInputStream("abc.xls");
    ArrayList<EmployeeVo> al = new ArrayList<>();
    EmployeeVo evo = null;
    Scanner scanner = null;

    Workbook wb = new XSSFWorkbook(fi);
    Sheet sh = wb.getSheet("Sheet0");
    int starRow = sh.getFirstRowNum();
    int endRow = sh.getLastRowNum();

    for (int i = starRow + 1; i < endRow; i++) {
        scanner = new Scanner(System.in);
        evo = new EmployeeVo();
        Cell c = wb.getSheetAt(0).getRow(i).getCell(1);
        evo.setEmployeeId((int) c.getNumericCellValue());

        Cell c2 = wb.getSheetAt(0).getRow(i).getCell(2);
        evo.setEmployeeName(c2.toString());
        // add to collection
        al.add(evo);
    } // for

    al.forEach(i -> {
        System.out.println(i.getEmployeeId() + " " + i.getEmployeeName());
    });

}
}