May 14, 2014

Using excel file as DB and read data from Java

We can use an excel file as a data base. Using java we can create connection to excel file and execute sql query as we do with database. Use the below code to use excel file as a DB and read data from it using java.

String stExcelFile=”c:\\temp\\test.xls”;
String stSheetName=”Sheet1”;
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection conExcel =
            DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" +
                                        stExcelFile +
                                        ";DriverID=22;READONLY=false");
        Statement stmtExcel = conExcel.createStatement();
        ResultSet rsExcel =stmtExcel.executeQuery("Select * from [" + stSheetName + "$]");
while (rsExcel.next()) {
System.out.println(“id: ”+rsExcel.getString(“id”)));
System.out.println(“name: ”+rsExcel.getString(“name”)));
}
rsExcel.close();
stmtExcel.close();
conExcel.close();