How to Get Excel Cell Values and Make Judges by the Specific Condition in Java

 

Below is part of the 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

We are trying to traverse all cells in the above file to find those whose values are the specified string and return value of the cells on the right of those eligible cells. That is to say, if the eligible cell is A1, then the to-be-returned cell is B1. If there are multiple eligible cells, the same number of cells will be returned (Defined in the code).

Suppose the specified string is “James” and we are trying to get values of cells on the right of the eligible cells. Below is the desired result:

Williams

Wilson

 

We can get it done effortlessly with esProc.

We can directly execute the following statement in esProc within Java:

            public static void test() {

                        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);

                                                }

                                    }

                        }

            }

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