* How to Check & Get Specific Excel Cell Values in Java

 

Below is part of Excel file emp.xlsx:

EID

NAME

SURNAME

GENDER

STATE

BIRTHDAY

HIREDATE

DEPT

SALARY

1

Rebecca

Moore

F

California

1974-11-20

2005-03-11

R&D

7000

2

Ashley

Wilson

F

New York

1980-07-19

2008-03-16

Finance

11000

3

Rachel

Johnson

F

New Mexico

1970-12-17

2010-12-01

Sales

9000

4

Emily

Smith

F

Texas

1985-03-07

2006-08-15

HR

7000

5

Ashley

Smith

F

Texas

1975-05-13

2004-07-30

R&D

16000

6

Matthew

Johnson

M

California

1984-07-07

2005-07-07

Sales

11000

7

Alexis

Smith

F

Illinois

1972-08-16

2002-08-16

Sales

9000

8

Megan

Wilson

F

California

1979-04-19

1984-04-19

Marketing

11000

9

Victoria

Davis

F

Texas

1983-12-07

2009-12-07

HR

3000

10

Ryan

Johnson

M

Pennsylvania

1976-03-12

2006-03-12

R&D

13000

The task is to traverse all cells in the Excel table to find the one whose value is the specific string and return value of the cell directly on its right.  For instance, B1’s value will be returned if the eligible cell we find is A1. If there are multiple cells that meet the specific condition, just return values of the corresponding cells (output from the program). 

Suppose the specific string is “James”, we need to find the cell value on the right of the eligible cell. The result is as follows:

Williams

Wilson

 

It’s convenient to get this done with esProc.
Download esProc DSK edition and free license HERE.

We can execute the following statement with esProc directly from a Java program:

            publicstaticvoidtest() {

                        Connection con= null;

                        Statement st;

                        try{

                                    Class.forName("com.esproc.jdbc.InternalDriver");

                                    con= DriverManager.getConnection("jdbc:esproc:local://");

 

                                    st= con.createStatement();

                                    ResultSet rs= st

                                                            .executeQuery("=file(\"emp.xlsx\").xlsimport@w().(~.(if(~==\"James\",~[+1])).select(~)).conj()");

 

                                    while(rs.next()) {

                                                System.out.println(rs.getObject(1).toString());

                                    }

                                   

                        } catch(Exception e) {

                                    System.out.println(e);

                        } finally{

                                    // Close database connection

                                    if(con!= null) {

                                                try{

                                                            con.close();

                                                } catch(Exception e) {

                                                            System.out.println(e);

                                                }

                                    }

                        }

            }

ReadHow to Call an SPL Script in Javato learn more about integration of esProc script into a Java program.