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.