In the following snippet .. i hav connected an excel sheet through jdbc
jdbc excel driver. i am having two columns in excel sheet naming X and Y.
the values of columns r stored in resultSet rs.
then i m printing those columns values.
in the method " public ArrayList resultSetToArrayList(ResultSet rs)'
i hav converted resulset to an arraylist so that i can work on it using java.
can anyone please tell me how to print that arraylist and find out the average. ??
import java.io.*;
import java.sql.*;
import java.util.*;
public class Test{
@SuppressWarnings("unchecked")
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 X from [Sheet1$]");
ResultSetMetaData rsmd = rs.getMetaData();
for(int i=1;i<=1;i++){
String columnName = rsmd.getColumnName(i);
System.out.println(columnName);
}
System.out.println("------");
while (rs.next()) {
for(int i=1;i<=1;i++){
String columnValue = rs.getString(i);
System.out.print(columnValue);
System.out.println("");
}
}
System.out.println("");
rs = st.executeQuery("Select Y from [Sheet1$]");
rsmd = rs.getMetaData();
for(int i=1;i<=1;i++){
String columnName = rsmd.getColumnName(i);
System.out.println(columnName);
}
System.out.println("------");
while (rs.next()) {
for(int i=1;i<=1;i++){
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());
}
}
public ArrayList resultSetToArrayList(ResultSet rs) throws SQLException{
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
System.out.println(columns);
ArrayList results = new ArrayList();
while (rs.next()){
HashMap row = new HashMap();
results.add(row);
for(int j=1; j<=columns; j++){
row.put(md.getColumnName(j),rs.getObject(j));
}
}
return results;
}
}