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 get employee information where the salary is below 10000 in Java. Below is the desired result:

We can get it done easily 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@t().select(SALARY<10000)");

 

                                    // Handle the result set simply and output field names and detailed data

                                    ResultSetMetaData rsmd = rs.getMetaData();

                                    int colCount = rsmd.getColumnCount();

                                    for (int c = 1; c <= colCount; c++) {

                                                String title = rsmd.getColumnName(c);

                                                if (c > 1) {

                                                            System.out.print("\t");

                                                } else {

                                                            System.out.print("\n");

                                                }

                                                System.out.print(title);

                                    }

                                    while (rs.next()) {

                                                for (int c = 1; c <= colCount; c++) {

                                                            if (c > 1) {

                                                                        System.out.print("\t");

                                                            } else {

                                                                        System.out.print("\n");

                                                            }

                                                            Object o = rs.getObject(c);

                                                            System.out.print(o.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.