• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Readin excel files from Java & output

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It is not possible to read Row Numbers with Java. Try using JNI.
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic