• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Readin excel files from Java & output

 
gunter glue
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
My name is Gunter and I am trying to read an excel file from Java. It only reads the column, but I want to print the rows, which intersect with the SQL query. The following source displays only values of a column. No problem, but I would like to see the corresponding row numbers. Can anyone suggest me what to do?
Thanks,
Gunter
import java.io.*;
import java.sql.*;
import java.util.*;
public class ExcelReadTest1{
public static void main(String[] args){
Connection connection = null;
try{
//Vector vector = new Vector();
String myString="";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection( "jdbc dbc ata-list" );
Statement st = con.createStatement();
ResultSet rs = st.executeQuery( "Select caseid from [sa3$] where iSchool=2" );
//ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" );
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

int row_num=1;
while ((rs !=null) && rs.next()) {
myString += "\n Row " + row_num++ + " ";
for (int i = 1; i <= numberOfColumns; i++)
{
//myString += "\n " + rsmd.getColumnName(i);
myString += " : " + rs.getString(i);
System.out.println(myString);
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());
}
}
}
[ August 09, 2003: Message edited by: gunter glue ]
[ August 10, 2003: Message edited by: gunter glue ]
[ August 10, 2003: Message edited by: gunter glue ]
 
VIJAY Yadlapati
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is not possible to read Row Numbers with Java. Try using JNI.
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Couple ideas, not too pretty. Can you add a row-number column to the spreadsheet? You could select two columns instead of just one.
Other idea is select and loop through all rows, count them to get row number, do the selection on iSchool in code. Like I said, not pretty.
Just curious: When I've done SQL against Excel, the "table name" was actually a "range name". Is that what you're doing? Or are you actually specifying cells in the query?
 
gunter glue
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am actually specifying the cell or cells. In other words, you can use a query which invokes different sheets in that Excel file, and it finds intersections or unions. It would be very nice if it reads the rows numbers (just a counter in this case) or the rows that intersected given in the query.
Gunter
Just curious: When I've done SQL against Excel, the "table name" was actually a "range name". Is that what you're doing? Or are you actually specifying cells in the query?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic