esProc Assists Java in Merging Text Files to Excel

Problem source：http://bbs.csdn.net/topics/390842850 .

In this case, you need to merge data from all text files in a given directory and export it to an Excel file. Each of these text files has tab-seperated data with one row and three columns.

JAVA approaches the problem by running a loop to retrieve all files in the directory and then writing data into the Excel file. The open source package (for example Apache POI) JAVA uses to handle Excel files provides a low-level API, which makes the coding really tedious.

esProc gets POI encapsulated and thus makes the operation quite easy：

A1：List all files in E:\\test. @p option indicates returning file names with full paths

A2：Retrieve all files under the directoy and combine their data together

A3：Export the merged data to an Excel file of xlsx format (the xlsx format can hold a million rows of data)

The script can be called by the JAVA program via JDBC interface provided by esProc. This is the code used for Java program to call the script through esProc JDBC (save the above code as test.dfx):

  // establish a connection via esProc jdbc

  Class.forName(“com.esproc.jdbc.InternalDriver”);

  con= DriverManager.getConnection(“jdbc:esproc:local://”);

  // call esProc script test.dfx (similar to the stored procedure)

  com.esproc.jdbc.InternalCStatement st =(com.esproc.jdbc.InternalCStatement)con.prepareCall(“call test()”);

  // execute esProc program

  st.executeQuery();

For scripts with very small amount of code, they can be written directly in the JAVA program that calls the esProc JDBC, without the need of writing it (test.dfx) separately:

st.executeQuery(“file(\”E:\\\\test\\\\result.xlsx\”).exportxls(directory@p(\”E:\\\\test\”).conj(file(~).import()))”);

This line of JAVA code calls directly a line of esProc code, which is equal to the above three-line script.