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

undefined

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.