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.