* How to Filter an Excel File in Java

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 computing task is to get records of employees whose salaries are below 10000 in Java. The expected result is as follows:

undefined

It’s easy to do this 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@t().select(SALARY<10000)");

 

                                    // Output field names and data after a simple operation on the result set

                                    ResultSetMetaData rsmd= rs.getMetaData();

                                    intcolCount= rsmd.getColumnCount();

                                    for(intc= 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(intc= 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);

                                                }

                                    }

                        }

            }

 

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