How to Convert Excel to CSV in Java

Java doesn’t have a class library for converting an Excel file to a CSV file. Though you can use poi, jxl or other third-party packages to handle the conversion, you need to write a lot of code and the code is unreusable.

Here’s an Excel file of xls or xlsx format, as shown below:

We want to convert the Excel table to a heading-less CSV file that include only the detailed data rows, as shown below:

It’s convenient to get this done with esProc.
Download esProc installation package and free license file HERE.

1. Write script excel2csv.dfx in esProc:

Note: esProc can automatically identify the xls or xlsx format, and then use f.xlsimport() function to read data.

2. Execute the above script to get the expected CSV file sales.csv.

3. Integrate the esProc script into a Java program.

esProc offers JDBC driver to integrate an esProc script in the following way:

public static void testDataServer() {
    Connection con = null;
    java.sql.PreparedStatement st;
    try {
        Class.forName(“com.esproc.jdbc.InternalDriver”);
        con = DriverManager.getConnection(“jdbc:esproc:local://”);
        // Call script excel2csv.dfx
        st = con.prepareCall(“call excel2csv()”);
        st.execute();
        System.out.println(“finish”);
    } catch (Exception e) {
        System.out.println(e);
    } finally {
        // Close database connection
        if (con != null) {
            try {
                con.close();
            } catch (Exception e) {
                System.out.println(e);
            }
        }
    }
}

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

To import column headers to the CSV file, just use @t option in A2’s export()function, that is, =file(“sales.csv”).export@tc(A1). Learn more about f.export().

When the requirement for export is complicated, such as retrieving data in a specific sheet, say sales, by skipping the first three rows (which are the multilevel headings) to begin from the third row, we just need to rewrite A1 as =file(“sales.xlsx”).xlsimport@c(;”sales”,4:). Learn more about f.xlsimport().