Get Cell Value from Excel Sheet with Apache Poi

Get Cell Value from Excel Sheet with Apache Poi

You have to use the FormulaEvaluator, as shown here. This will return a value that is either the value present in the cell or the result of the formula if the cell contains such a formula :

FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
Sheet sheet = wb.getSheetAt(0);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

// suppose your formula is in B3
CellReference cellReference = new CellReference("B3"); 
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol()); 

if (cell!=null) {
    switch (evaluator.evaluateFormulaCell(cell)) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            System.out.println(cell.getErrorCellValue());
            break;

        // CELL_TYPE_FORMULA will never occur
        case Cell.CELL_TYPE_FORMULA: 
            break;
    }
}

if you need the exact contant (ie the formla if the cell contains a formula), then this is shown here.

Edit :
Added a few example to help you.

first you get the cell (just an example)

Row row = sheet.getRow(rowIndex+2);    
Cell cell = row.getCell(1);   

If you just want to set the value into the cell using the formula (without knowing the result) :

 String formula ="ABS((1-E"+(rowIndex + 2)+"/D"+(rowIndex + 2)+")*100)";    
 cell.setCellFormula(formula);    
 cell.setCellStyle(this.valueRightAlignStyleLightBlueBackground);

if you want to change the message if there is an error in the cell, you have to change the formula to do so, something like

IF(ISERR(ABS((1-E3/D3)*100));"N/A"; ABS((1-E3/D3)*100))

(this formula check if the evaluation return an error and then display the string "N/A", or the evaluation if this is not an error).

if you want to get the value corresponding to the formula, then you have to use the evaluator.

Hope this help,

Guillaume

How do i search and print a specific cell from an excel sheet using Apache POI?

That's because the method getRows returns all the rows where there is a cell with the wanted criteria, but it doesn't tell you which cell exactly, so what you need to do is return the cells.

getRows -> getCells

and the code for that method will be :

static List<Cell> getCells(Sheet sheet, DataFormatter formatter, FormulaEvaluator evaluator, String searchValue) {
    List<Cell> result = new ArrayList<>();
    String cellValue = "";
    for (Row row : sheet) {
        for (Cell cell : row) {
            cellValue = formatter.formatCellValue(cell, evaluator);
            if (cellValue.contains(searchValue)) {
                result.add(cell);
                break;
            }
        }
    }
    return result;
}

and as far as printing goes, you just loop through the list of cells and you pritn them normally, and if you for whatever reason need the row for a particular cell, you just call cell.getRow()

List<Cell> filteredCells = getCells(sheet, formatter, evaluator, "whatever");

for (Cell cell : filteredCells) {
    System.out.print(formatter.formatCellValue(cell, evaluator));
    System.out.print("\t \t");
    Row cellsRow = cell.getRow();// if you need the full row
}

by the way, I don't know about the specifications that you have, but the break that you have in your filtering method means that if a row has multiple cells with the wanted value, you'll only get the first cell, so be careful about that, i would recommend removing that break unless it's exactly what you want

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

For example, to get E10 of the first worksheet:

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.

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

How do I read cell value from Excel sheet based on the cell attribute (keyword) using Apache POI integration for Selenium

To solve this you need to reverse the data getting logic.
So here we first need to get the column data and then traverse all its row.

ie. Nick -> Fury -> nick-fury@example.com and then moving to another column and fetch Jack -> Ryan -> jack-ryan@example.com

Screenshot:

Important Note:

This code is to fetch xls file data using POI, kindly change the code as
per your requirement.

(1). HSSFWorkbook: This class has methods to read
and write Microsoft Excel files in .xls format.

(2).XSSFWorkbook: This class has methods to read and write Microsoft
Excel and OpenOffice xml files in .xls or .xlsx format.

Code:

@Test(dataProvider = "getExcelData")
    public void testSheet(String firstName, String lastName, String personalEmail) {

    System.out.println(firstName+"  "+lastName+" "+personalEmail);
}

@DataProvider
public Object[][] getExcelData(){
    String excelSheetPath = System.getProperty("user.dir")+"/data.xls";
    String sheetName = "Sheet1";
    return getExcelData(excelSheetPath, sheetName);
}

public Object[][] getExcelData(String excelSheetPath, String sheetName) {
    Object[][] arrayExcelData = null;
    try (
            FileInputStream fileStream = new FileInputStream(excelSheetPath)
    ) {
        HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
        HSSFSheet sheet = workbook.getSheet(sheetName);
        Row row = sheet.getRow(0);
        int lastRowIndex = sheet.getLastRowNum() + 1;
        System.out.println("Last row index :" + lastRowIndex);
        int totalNoOfCols = row.getLastCellNum() - 1;
        System.out.println("Total columns :" + totalNoOfCols);

        arrayExcelData = new Object[totalNoOfCols][lastRowIndex];
        DataFormatter df = new DataFormatter();

        for (int i = 1; i <= totalNoOfCols ; i++) {
            for (int j = 0; j < lastRowIndex; j++) {
                row = sheet.getRow(j);
                Cell c = row.getCell(i);
                String cellData = df.formatCellValue(c);
                System.out.println(cellData);
                arrayExcelData[i-1][j] = cellData;
            }
            System.out.println("-----------");
        }
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println(e.getMessage());
    }
    return arrayExcelData;
}

Read data from excel sheet in java with Apache POI

tl;dr

row                              // Starting with a Poi `Row` object.
.getCell(6)                      // Extract a Poi `Cell` object.
.getDateCellValue()              // Extract value from spreadsheet as a `java.util.Date` object. This terrible class is now legacy. Immediately convert to java.time.Instant as seen on next line.
.toInstant()                     // Convert from legacy `java.util.Date` class to modern `java.time.Instant`. Both represent a moment in UTC.
.atZone(                         // Adjusting from UTC to a time zone.
    ZoneId.of( "Asia/Tokyo" )    // Specify a time zone to adjust from UTC (an offset of zero hours-minutes-seconds). 
)                                // Returns a `ZonedDateTime` object. Same moment, different wall-clock time.
.toLocalDate()                   // Extract the date-only portion. Or call `toLocalTime` for the time-of-day portion.

Details

The other Answers correctly show how to get a java.util.Date object. But that class is (a) terrible, and (b) now legacy, supplanted years ago by the modern java.time classes. You need to go further to get a proper solution.

After getting your java.util.Date object:

java.util.Date javaUtilDate = row.getCell(6).getDateCellValue() ;

…you have a moment in time in UTC. Unfortunately, following the advice to call Date::toString:

row.getCell(6).getDateCellValue().toString() 

…creates a confusing the result. That method applies the JVM’s current default time zone, adjusting from UTC to some zone. That creates the illusion that this time zone was part of the java.util.Date, but that is not true. And this behavior means your results will vary at runtime depending on whatever the current default time zone might be. Be aware that not only the user can change the default time zone, but so can any code in any thread of any app running within that JVM.

For predictable results, you should specify the desired/expected time zone.

To do so, immediately convert your java.util.Date to its replacement, the Instant class. Both represent a moment in UTC. Notice the new conversion methods added to the old legacy classes.

Instant instant = javaUtilDate.toInstant() ;

Specify a proper time zone name in the format of Continent/Region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 2-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId zoneId = ZoneId.of( "Africa/Tunis" ) ;

If you want to use the JVM’s current default time zone, ask for it explicitly, and pass as an argument. If omitted, the code becomes ambiguous to read in that we do not know for certain if you intended to use the default or if you, like so many programmers, were unaware of the issue.

ZoneId zoneId = ZoneId.systemDefault() ;  // Get JVM’s current default time zone.

Apply that time zone to your moment in UTC.

ZonedDateTime zdt = instant.atZone( zoneId ) ;

The resulting ZonedDateTime object represents the same moment, the same simultaneous point on the timeline as the Instant. But its wall-clock time has been adjusted to that used by the people of a particular region (a time zone).

You asked:

How can I read the time?

How can I read the date correctly?

You can use a DateTimeFormatter to print either or both in any format you desire, or even automatically localize to the human language and cultural norms defined in a Locale object.

Or you can extract the date and time values as separate objects.

LocalDate localDate = zdt.toLocalDate() ;
LocalTime localTime = zdt.toLocalTime() ;

---

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  
  
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Apache poi populate the cell value by formula

As @Axel Richter mentioned using == is invalid.

cell.setCellFormula("IF(AND(LEN(A1:A3)>0,(B1:B3)==UK),1,0)");

Mistakes with your formula.

#1. The error...

Parse error near char 25 '=' in specified formula 'IF(AND(LEN(A1:A3)>0,(B1:B3)==UK),1,0)'. Expected cell ref or constant literal` 

…implies that you are using an additional = in the formula.

#2. (B1:B3)==UK should be (B1:B3)="UK". You are comparing a String value so it should be in double quotes.

Code:

cell.setCellFormula("IF(AND(LEN(A1:A3)>0,(B1:B3)=\"UK\"),1,0)");

Output:

How can I read numeric strings in Excel cells as string (not numbers)?

I had same problem. I did cell.setCellType(Cell.CELL_TYPE_STRING); before reading the string value, which solved the problem regardless of how the user formatted the cell.

---

Related Topics