You can use JDBC/SQL Query in Java for Excel files

Hi Freinds,

This is one java program which will use JDBC-ODBC driver to read Excel files.
We can connect to microsoft xls , same way as we do connect to our databases.
Then we can query these xls using select query , use joins , where clause , having , group by ASC .. and all.

This is very powerful way to handle xls from Java.
Every worksheet of xls is taken as seperate Table.
In this case , i wanted to compare growth of tables in my database for a period of 7 days. Hence each worksheet for me constituted same Table -  but data with different days.

First worksheet "qas1" having table records & size for 7th Feb'11.
Second worksheet "qas2" having table records & size for 8th Feb'11. etc ..

I tried to build query dynamically form provided parameters of no. of days  we want to do data analysis. In here it was for 8 days. Hence query was generated for comparison of 8 days.

This is very crude way of doing things .. however will give idea for Java developers to proceed if needed on similiar lines ...

Thanks

Rakesh

/**
 *
 * @author rakesh.praphullan
 */
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.logging.Level;
import java.util.logging.Logger;
public class ExcelReader {
    private static final int DATA_COUNT = 8;
    private static final String output_file = "D:\\D\\Rakesh\\test\\SqlUtils\\src\\Output.xls";
    private static final String TableAlias = "A";
    private static final String TableNamePrefix = "qas";
    private static final int pivotTable = 1;
    private static final String pivotTableAlias = TableAlias + pivotTable;
    private static final String tab = "\t";
    public static void main(String[] args) {
        Connection c = null;
        Statement stmnt = null;
        String generatedQuery = generateQuery();
        System.out.println(" Generated Query is :[" + generatedQuery + "]");
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            c = DriverManager.getConnection("jdbc:odbc:Java-Excel", "", "");
            stmnt = c.createStatement();
            String query = "select A.SCHEMA as Schema , A.TABLENAME as TableName," + " A.ROWCOUNT as Count1, B.ROWCOUNT as Count2, C.ROWCOUNT as Count3 ,D.ROWCOUNT as Count4," + " (B.ROWCOUNT-A.ROWCOUNT) as Increment1 , (C.ROWCOUNT-B.ROWCOUNT) as Increment2 , (D.ROWCOUNT-C.ROWCOUNT) as Increment3" + " from [qas1$] A , [qas2$] B , [qas3$] C , [qas4$] D " + " where A.TABLENAME =+ B.TABLENAME and A.TABLENAME =+ C.TABLENAME and A.TABLENAME =+ D.TABLENAME and A.SCHEMA='PINDBO' order by A.ROWCOUNT DESC"; // and Year=2001;";
            System.out.println(" Current Query is :[" + query + "]");
            ResultSet rs = stmnt.executeQuery(generatedQuery);
           
            StringBuffer outputBuffer = new StringBuffer();
            outputBuffer.append(getTableHeader()); //"Schema" + tab + "TableName" + tab + "Count1" + tab + "Count2" + tab + "Count3" + tab + "Count4" + tab + "Increment1" + tab + "Increment2" + tab + "Increment3\n");
            retrieveResults( rs , outputBuffer);
            /*while (rs.next()) {
                outputBuffer.append("" + rs.getString(1) + tab);
                outputBuffer.append("" + rs.getString(2) + tab);
                outputBuffer.append("" + rs.getInt(3) + tab);
                outputBuffer.append("" + rs.getInt(4) + tab);
                outputBuffer.append("" + rs.getInt(5) + tab);
                outputBuffer.append("" + rs.getInt(6) + tab);
                outputBuffer.append("" + rs.getInt(7) + tab);
                outputBuffer.append("" + rs.getInt(8) + tab);
                outputBuffer.append("" + rs.getInt(9) + tab);
                outputBuffer.append("\n");
            } */
            System.out.println(outputBuffer.toString());
            writeFile(output_file, outputBuffer);
        } catch (Exception e) {
            System.err.println(e);
        } finally {
            try {
                stmnt.close();
                c.close();
            } catch (Exception e) {
                System.err.println(e);
            }
        }
    }
    public static boolean retrieveResults(ResultSet rs ,StringBuffer outputBuffer) throws Exception
    {
        boolean okay = true;
        while (rs.next()) {
                outputBuffer.append("" + rs.getString(1) + tab);
                outputBuffer.append("" + rs.getString(2) + tab);
                // next the counts
                for (int i = 1; i <= DATA_COUNT; i++) {
                    outputBuffer.append("" + rs.getInt(2+i) + tab);
                }
                // next the increment counts
                for (int i = 1; i < DATA_COUNT; i++) {
                    outputBuffer.append("" + rs.getInt(2+DATA_COUNT+i) + tab);
                }
               
                outputBuffer.append("\n");
            }
        return okay;
    }
    public static String getTableHeader() {
        StringBuffer headerBuffer = new StringBuffer();
        StringBuffer queryHdr = new StringBuffer();
        queryHdr.append("Schema" + tab + "TableName" + tab + "");
//outputBuffer.append("Schema" + tab + "TableName" + tab + "Count1" + tab + "Count2" + tab + "Count3" + tab + "Count4" + tab + "Increment1" + tab + "Increment2" + tab + "Increment3\n");
        StringBuffer rowCountDetails = new StringBuffer();
        StringBuffer rowIncrementDetails = new StringBuffer();
        for (int i = 1; i <= DATA_COUNT; i++) {
            rowCountDetails.append(" Day_" + i + "_Count"+tab);
            if (i != 1) {
                // skipping first row
                //(B.ROWCOUNT-A.ROWCOUNT) as Increment1 ,
                rowIncrementDetails.append("Day_" + (i - 1) + "_Increment"+tab);
                if (i == DATA_COUNT) {
                    rowIncrementDetails.append("\n");
                }
            }           
        }
        headerBuffer.append(queryHdr.toString());
        headerBuffer.append(rowCountDetails.toString());
        headerBuffer.append(rowIncrementDetails.toString());
        return headerBuffer.toString();
    }
    public static String generateQuery() {
        StringBuffer queryBuffer = new StringBuffer();

        StringBuffer queryHdr = new StringBuffer();
        queryHdr.append("Select " + pivotTableAlias + ".SCHEMA as Schema ," + pivotTableAlias + ".TABLENAME as TableName,");
        StringBuffer rowCountDetails = new StringBuffer();
        StringBuffer rowIncrementDetails = new StringBuffer();
        StringBuffer fromTableDetails = new StringBuffer(" From ");
        StringBuffer whereClauseDetails = new StringBuffer(" Where ");
        StringBuffer postwhereClauseDetails = new StringBuffer(" " + pivotTableAlias + ".SCHEMA='PINDBO' order by " + pivotTableAlias + ".ROWCOUNT DESC");
        for (int i = 1; i <= DATA_COUNT; i++) {
            rowCountDetails.append(" " + TableAlias + i + ".ROWCOUNT as Day_" + i + "Count,");
            if (i != 1) {
                // skipping first row
                //(B.ROWCOUNT-A.ROWCOUNT) as Increment1 ,
                rowIncrementDetails.append(" (" + TableAlias + i + ".ROWCOUNT - " + TableAlias + (i - 1) + ".ROWCOUNT ) as Day_" + (i - 1) + "Increment");
                if (i != DATA_COUNT) {
                    rowIncrementDetails.append(",");
                }
            }
            // from [qas1$] A ,
            fromTableDetails.append(" [" + TableNamePrefix + i + "$] " + TableAlias + i + " ");
            if (i != DATA_COUNT) {
                fromTableDetails.append(",");
            }
            if (i != pivotTable) {
                // A.TABLENAME =+ B.TABLENAME and
                whereClauseDetails.append(pivotTableAlias + ".TABLENAME =+ " + TableAlias + i + ".TABLENAME and ");
            }
        }
        queryBuffer.append(queryHdr.toString());
        queryBuffer.append(rowCountDetails.toString());
        queryBuffer.append(rowIncrementDetails.toString());
        queryBuffer.append(fromTableDetails.toString());
        queryBuffer.append(whereClauseDetails.toString());
        queryBuffer.append(postwhereClauseDetails.toString());
        return queryBuffer.toString();
    }
    private static boolean writeFile(String filename, StringBuffer buff) {
        boolean writeDone = false;
        FileOutputStream fout = null;
        try {
            fout = new FileOutputStream(filename);
            String data = buff.toString();
            char current;
            for (int i = 0; i < data.length(); i++) {
                fout.write(data.charAt(i));
            }
            writeDone = true;
        } catch (IOException ex) {
            Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                fout.close();
            } catch (IOException ex) {
                Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return writeDone;
    }
}