Looking for an open source java 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:

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

--

--

--

Products and resources that simplify hard data processing tasks. If you have any questions, send me a message. https://www.linkedin.com/in/witness998

Recommended from Medium

Deploying a Python Web App on AWS

Airflow on AWS EC2 - Python 3 Virtual Environment

The illusion of pass-by-reference in C

Pitfalls Of Using Native Compiler For Embedded software Testing

An Intro to the Differences Between Programming Languages

Creating a Multicloud Setup of Kubernetes using Ansible Roles

How to optimise MongoDB Performance

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jerry Cruise

Jerry Cruise

Products and resources that simplify hard data processing tasks. If you have any questions, send me a message. https://www.linkedin.com/in/witness998

More from Medium

AlexNet Architecture

Java Lesson 20: Lines, Colors and Basic Java Graphics

How c program is compiled and executed

Quicksort as a problem-solving tool

Partitioning