Does Java have an open source library that can execute SQL on Excel files?

Java programs usually use the open-source package Apache POI to read data in Excel files. Although POI is powerful, it has a certain learning curve. For Excel files with a relatively regular format, if you can use Excel as a Java database, use SQL to calculate Data will be very convenient. Open-esProc is this type of open-source package. It is a comprehensive and professional computing package aimed at processing structured data. The SQL calculation method is also encapsulated in the SPL scripting language that performs Open-esProc calculation functions. In Java programs, Call the SPL script and return the ResultSet object. For example, the following info.xlsx file:

..

A
1 =connect()
2 =A1.query(“select * from   d:/excel/info.xlsx where Sex=’M’”)

This code can be debugged/executed in esProc IDE, then saved as a script file (such as condition.dfx), and called in JAVA through the JDBC interface. The specific code is as follows:

  package Test;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  public class test1 {
      public static void main(String[]   args)throws Exception {
          Class.forName("com.esproc.jdbc.InternalDriver");
          Connection connection   =DriverManager.getConnection("jdbc:esproc:local://");
          Statement statement =   connection.createStatement();
          ResultSet result =   statement.executeQuery("call condition()");
          printResult(result);
          if(connection != null)   connection.close();
      }
…
}

The above usage is similar to the stored procedure. SPL also supports the use similar to SQL, that is, without script files, directly embed SPL script in JAVA. The code is as follows:

…
ResultSet result = statement.executeQuery("
=connect().query(\“select * from  d:/excel/info.xlsx where Sex=’M’\”)");
…

For example,employee information and department information are stored in two Excel files, and find the department with the youngest department manager. Part of the file data is shown in the figure below:

QQ20210815164045png

QQ20210815163231png


A

1

$select   emp.BIRTHDAY as BIRTHDAY,emp.DEPT as DEPT
         from E:/data/DEPARTMENT.xlsx as dept
            join  E:/data/EMPLOYEE.xlsx emp
            on  dept.MANAGER=emp.EID
where
    emp.BIRTHDAY=(select   max(BIRTHDAY)
            from ( select emp1.BIRTHDAY as BIRTHDAY
                    from E:/data/DEPARTMENT.xlsx as   dept1
                        join E:/data/EMPLOYEE.xlsx as   emp1
                        on  dept1.MANAGER=emp1.EID
                   )
             )

For more SQL examples in SPL, please refer to SQL Query on File Examples


SPL Official Website 👉 http://www.scudata.com

SPL Feedback and Help 👉 https://www.reddit.com/r/esProc

SPL Learning Material 👉 http://c.raqsoft.com

SPL Source Code and Package 👉 https://github.com/SPLWare/esProc