How to get value from a specific cell from XLSX file using java Apache POI library

user_5297266 0
Viewed 86324

I am writing a Java program to read data from excel sheet (having XLSX extension) using Apache POI library. 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?

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();
        }
    }   
}
excel java
5 Answers

Just version-up the getCell method

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")
user_12489880
Related