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:

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:

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