How to read and export data to excel in Java

1. JDBC: ODBC: Microsoft Excel driver
2. 3rd part package: jexcel
3. jacabo

 

 

Method 1:

This sample code was an outcome of a friend's requirement for reading an Excel file dynamically. he had an EJB layer for a flight management system for read/writes to database. it so happened that the data also started coming from varous attributes in a spread sheet format. so either he had to import the data to his Oracle database manually or re-design his EJB's for accomadating this new data input. so what I designed for him was a simple Facade Pattern classes run by a daemon process which makes use of the existing Enterprise Java Beans which enabled him to treat the spreadsheet data as no different. the scope of the design is beyond this article.

So what I will perform strate in this article is a simple way of accessing spreadsheets as if they were a database. this article holds good for Java running on Windows-servers. the access itself is through a JDBC-ODBC bridge.

Okie... so here we go...

Open the ODBC Data Administrator console and click on system DSN. select Add and add the Microsoft Excel driver from the list of drivers and give a name to the DSN (say exceltest) and select the workbook.

Then all one needs is to connect through this DSN just like connecting to the database and accessing records.

Here's the sample code

 

 

import java.io.*;import java.sql.*;    public class ExcelReadTest{        public static void main(String[] args){        Connection connection = null;            try{            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");            Connection con = DriverManager.getConnection( "jdbc:odbc:exceltest" );            Statement st = con.createStatement();            ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" );                        ResultSetMetaData rsmd = rs.getMetaData();            int numberOfColumns = rsmd.getColumnCount();                                        while (rs.next()) {                    for (int i = 1; i <= numberOfColumns; i++) {                    if (i > 1) System.out.print(", ");                    String columnValue = rs.getString(i);                    System.out.print(columnValue);                    }                    System.out.println("");                    }                                        st.close();                    con.close();                        } catch(Exception ex) {                        System.err.print("Exception: ");                        System.err.println(ex.getMessage());                        }                        }                    }

Answer to comments:
Hey John ur comment is right and well recieved. the reason for the first row not being printed is that the JDBC-bridge assumes the first row to be akin to column names in the database. hence the first available row is the name of the column... which explains why the third row is printed if the first row is missing.
Hope this helps and thanks for the comment

Standard SQL queries like

                    Select column_name1,column_name2 from [Sheet1$] where column_name3 like '%bob%';                    

Can be used on the spreadsheet.
You can use the following snippet to print the column names (which is the first row of the spread sheet ).

                        for (int i = 1; i <= numberOfColumns; i++) {                        if (i > 1) System.out.print(", ");                        String columnName = rsmd.getColumnName(i);                        System.out.print(columnName);                        }                        System.out.println("");

--------------------------------------------------------

Method 2:

http://www.ibm.com/developerworks/cn/java/l-javaExcel/index.html

--------------------------------------------------------------------

Method 3