File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to upload xls file

 
saravanan rajendran
Ranch Hand
Posts: 75
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi, i trying to upload a xls.file and import to mysql database please help me

xls.file contain name and id value that i want to store in database

my html page contain browser button when i pressed that i can retrieve that xls file i have import button when i pressed that my xls file value should store in database

my problem:

i was retrieved my xls file in browser how i can get that xls file into that below code please help me

when i pressed import button it will go to below code

try
{
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
conn = DriverManager.getConnection("jdbc dbc river={Microsoft Excel Driver (*.xls)};DBQ=" + filename);//how i can get xls file into this code?
st = conn.createStatement();
String query = "Select * from [Sheet1$]" ;
rs = st.executeQuery( query );
while( rs.next() )
{
String name=rs.getString(1);
String loc=rs.getString(2);
try
{

Class.forName
("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/vbtest", "meetme", "meetme");
st = conn.createStatement();
st.executeUpdate("Insert INTO sample1(`name` , `location`)VALUES('"+ name +"' ,'"+ loc +"')");
rs1=st.executeQuery("select * from sample1");
if(rs1.next())
{
out.println(name);
}
}
catch(Exception e)
{
out.println(e);
}
}//while
}//try
catch( Exception e )
{
out.println(e);
}
 
Rob Spoor
Sheriff
Pie
Posts: 20380
46
Chrome Eclipse IDE Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check out java.sql.PreparedStatement, combined with a BLOB field in your database. Open an InputStream to the uploaded file, and call setBinaryStream with it.

There might be a problem with this if you don't know the file size and either your database driver is not built for Java 6, or you're stuck to Java 5.0 or below for some reason - you need to pass the InputStream length (as an int) to the only setBinaryStream method available in Java 5.0 and below.

A solution could be using ByteArrayInputStream and ByteArrayOutputStream:

This could be memory consuming (you're storing the entire XLS file in memory) so it's really preferred if you can just retrieve the XLS file size without reading the contents.
 
Rajkamal Pillai
Ranch Hand
Posts: 445
1
Java Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I suspect he wanted to actually get the data in the Excel worksheet into rows in the database (I am assuming this from the query written). For that you should check up on using some third party framework e.g. Apache POI HSSF or ActiveXLS You can then read the data from the Excel sheets and then run them against the database using the query as necessary.

Cheers,
Raj.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic