Connecting java to Excel
HI
Can anyone help me about the code.
I got the Exception:
Error while inserting to excel sheet!!!
java.sql.SQLException:[Microsoft][ODBC Excel Driver]Operation must use an updateable query.
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class InsertExcel
{
static JFrame frame;
static JPanel panel;
static JTextField textuserid;
static JTextField textfirstname;
static JTextField textlastname;
static JButton submit;
static JButton exit;
public static void main(String args[])
{
frame = new JFrame("Inserting to excel sheet");;
panel = new JPanel();
JLabel labeluserid = new JLabel("User id ");
JLabel labelfirstname = new JLabel("First Name");
JLabel labellastname = new JLabel("Last Name");
textuserid = new JTextField(20);
textfirstname = new JTextField(20);
textlastname = new JTextField(20);
submit = new JButton("Submit to Excel sheet");
exit = new JButton("Exit");
panel.add(labeluserid);
panel.add(textuserid);
panel.add(labelfirstname);
panel.add(textfirstname);
panel.add(labellastname);
panel.add(textlastname);
panel.add(submit);
panel.add(exit);
frame.getContentPane().add(panel);
frame.setSize(1024,100);
frame.setVisible(true);
frame.setDefaultCloseOperation(3);
submit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:population");
PreparedStatement stat2 = con.prepareStatement("insert into [Sheet1$ (USERID,FIRST_NAME,LAST_NAME) values (?,?,?)");
stat2.setString(1,textuserid.getText());
stat2.setString(2,textfirstname.getText());
stat2.setString(3,textlastname.getText());
stat2.executeUpdate();
JOptionPane.showMessageDialog(frame,new String("Successfully entered to excel sheet !!!"));
}
catch(Exception e1)
{
JOptionPane.showMessageDialog(frame,new String("Error while inserting to excel sheet !!! \n" +
e1));
}
}
});
exit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent ex)
{
System.exit(0);
}
});
}
}
Message was edited by:
lesbon
[2535 byte] By [
lesbona] at [2007-11-27 10:45:41]
Is the syntax you posted accurate? I'm looking at this line and wondering where the closing right square bracket is:
insert into [Sheet1$
Shouldn't that be
insert into [Sheet1$]
%
yeah, it should like this
PreparedStatement stat2 = con.prepareStatement("insert into [Sheet1$](USERID,FIRST_NAME,LAST_NAME) values (?,?,?)");
but id doesn't work.
Message was edited by:
lesbon
try to run my program.
then check the exception thread.
whats wrong to my code?
> but id doesn't work.
Well that's very informative. Since we cannot read minds perhaps you could explain what that means. Including error messages (in full) would also help.
Ok.
I want to insert the data in the jtextfield into excel probably Sheet1 but i got an error.
Please try to run my program in your system.
And find what's wrong with my program.
heres the full code:
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class InsertExcel
{
static JFrame frame;
static JPanel panel;
static JTextField textuserid;
static JTextField textfirstname;
static JTextField textlastname;
static JButton submit;
static JButton exit;
public static void main(String args[])
{
frame = new JFrame("Inserting to excel sheet");;
panel = new JPanel();
JLabel labeluserid = new JLabel("User id ");
JLabel labelfirstname = new JLabel("First Name");
JLabel labellastname = new JLabel("Last Name");
textuserid = new JTextField(20);
textfirstname = new JTextField(20);
textlastname = new JTextField(20);
submit = new JButton("Submit to Excel sheet");
exit = new JButton("Exit");
panel.add(labeluserid);
panel.add(textuserid);
panel.add(labelfirstname);
panel.add(textfirstname);
panel.add(labellastname);
panel.add(textlastname);
panel.add(submit);
panel.add(exit);
frame.getContentPane().add(panel);
frame.setSize(1024,100);
frame.setVisible(true);
frame.setDefaultCloseOperation(3);
submit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:population");
PreparedStatement stat2 = con.prepareStatement("insert into [Sheet1$](USERID,FIRST_NAME,LAST_NAME) values (?,?,?)");
stat2.setString(1,textuserid.getText());
stat2.setString(2,textfirstname.getText());
stat2.setString(3,textlastname.getText());
stat2.executeUpdate();
JOptionPane.showMessageDialog(frame,new String("Successfully entered to excel sheet !!!"));
}
catch(Exception e1)
{
JOptionPane.showMessageDialog(frame,new String("Error while inserting to excel sheet !!! \n" +
e1));
}
}
});
exit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent ex)
{
System.exit(0);
}
});
}
}
> Please try to run my program in your system.
WHY?
Why should I waste my time in creating an excel spreadsheet, copying your code and setting everything up? All you need to do is provide us with EXACT error messages and explain in detail what "it doesn't work" means.
sorry!!!!
heres the exact error i got i mean exception:
java.sql.SQLException:[Microsoft][ODBC Excel Driver]Operation must use an updateable query.
This works fine for me. Adapt it.
import java.sql.*;
public class ExcelInsert
{
public static final String DEFAULT_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
public static final String DEFAULT_URL = "jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls)};DriverID=22;READONLY=false;DBQ=";
public static final String DEFAULT_PATH = "C:\\Documents and Settings\\Michael\\My Documents\\Projects\\Java\\Forum\\data\\ExcelInsert.xls";
private static final String DEFAULT_SQL = "INSERT INTO [SHEET1$] VALUES(?, ?, ?)";
private Connection conn;
public static void main(String[] args)
{
Connection c = null;
try
{
c = createConnection(DEFAULT_DRIVER, DEFAULT_URL+DEFAULT_PATH);
ExcelInsert inserter = new ExcelInsert(c);
String v1 = ((args.length > 0) ? args[0] : "foo");
String v2 = ((args.length > 1) ? args[1] : "bar");
String v3 = ((args.length > 2) ? args[2] : "baz");
int numRows = inserter.insert(v1, v2, v3);
System.out.println("# rows inserted: " + numRows);
}
catch (Exception e)
{
e.printStackTrace(System.err);
}
finally
{
close(c);
}
}
public static Connection createConnection(String driver, String url) throws SQLException, ClassNotFoundException
{
Class.forName(driver);
return DriverManager.getConnection(url);
}
public ExcelInsert(Connection conn)
{
this.conn = conn;
}
public static void rollback(Connection c)
{
try
{
if (c != null)
{
c.rollback();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public static void close(Connection c)
{
try
{
if (c != null)
{
c.close();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public static void close(Statement s)
{
try
{
if (s != null)
{
s.close();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public int insert(String a1, String a2, String a3) throws SQLException
{
int numRows = 0;
PreparedStatement stmt = null;
try
{
stmt = this.conn.prepareStatement(DEFAULT_SQL);
stmt.setString(1, a1);
stmt.setString(2, a2);
stmt.setString(3, a3);
numRows = stmt.executeUpdate();
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
close(stmt);
}
return numRows;
}
}
%
I don't believe the ODBC bridge technique lets you update spreadsheets. Junked it for several reasons recently, and that could have been one of them.
ejpa at 2007-7-28 20:14:22 >
thanks for the help duffymo.
YOUR THE BEST!!!!!
> I don't believe the ODBC bridge technique lets you
> update spreadsheets. Junked it for several reasons
> recently, and that could have been one of them.
Wrong. It does. See my code.
%
> thanks for the help duffymo.
>
> YOUR THE BEST!!!!!
Your (sic) welcome.
%
guys it works....
but i have another problem..
the excel file should be open while the process is going on.
but when it is close it can't add the data.
what's wrong again.
heres the code:
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class InsertExcel
{
static JFrame frame;
static JPanel panel;
static JTextField textuserid;
static JTextField textfirstname;
static JTextField textlastname;
static JButton submit;
static JButton exit;
public static void main(String args[])
{
frame = new JFrame("Inserting to excel sheet");;
panel = new JPanel();
JLabel labeluserid = new JLabel("User id ");
JLabel labelfirstname = new JLabel("First Name");
JLabel labellastname = new JLabel("Last Name");
textuserid = new JTextField(20);
textfirstname = new JTextField(20);
textlastname = new JTextField(20);
submit = new JButton("Submit to Excel sheet");
exit = new JButton("Exit");
panel.add(labeluserid);
panel.add(textuserid);
panel.add(labelfirstname);
panel.add(textfirstname);
panel.add(labellastname);
panel.add(textlastname);
panel.add(submit);
panel.add(exit);
frame.getContentPane().add(panel);
frame.setSize(1024,100);
frame.setVisible(true);
frame.setDefaultCloseOperation(3);
submit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
String url ="jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls)};DriverID=22;READONLY=false;DBQ=";
String path ="C:\\population.xls" ;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(url+path);
PreparedStatement stat2 = con.prepareStatement("insert into [Sheet1$](USERID,FIRST_NAME,LAST_NAME) values (?,?,?)");
stat2.setString(1,textuserid.getText());
stat2.setString(2,textfirstname.getText());
stat2.setString(3,textlastname.getText());
stat2.executeUpdate();
JOptionPane.showMessageDialog(frame,new String("Successfully entered to excel sheet !!!"));
}
catch(Exception e1)
{
JOptionPane.showMessageDialog(frame,new String("Error while inserting to excel sheet !!! \n" +
e1));
}
}
});
exit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent ex)
{
System.exit(0);
}
});
}
}
guys help for the question above.
tnx in forward.
> the excel file should be open while the process is going on.
> but when it is close it can't add the data.
Err, so don't close it?
I can't make any sense of this.
ejpa at 2007-7-28 20:14:27 >
but how can i add data even if the excel file is close?
firstly Lesbon.. try 2 keep the code in the code tages..that increases the readablity of the program
I'll give my program just check out with that one.. cos itz working for me..
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:clus");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("Select "+selectedlist+" From [Sheet1$]");
ResultSetMetaData rsmd=rs.getMetaData();
selectedcolumncount=rsmd.getColumnCount();st.close();
con.close();
> but how can i add data even if the excel file is close?
but why is it closed? Don't close it!
ejpa at 2007-7-28 20:14:27 >
> guys it works....
>
> but i have another problem..
>
> the excel file should be open while the process is
> going on.
>
> but when it is close it can't add the data.
My code inserts a record whether the Excel file is open or not.
You're doing something else wrong.
%
I've closed the excel sheet after i've done all the computations that I'm supposed to do.. therez lot more code before i closed that sheet.. i didnt give that computations that i did
Hope i made my point clear
try to run this code and open the excel file.
then after that try to run again the close the excel file.
code:
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class InsertExcel
{
static JFrame frame;
static JPanel panel;
static JTextField textuserid;
static JTextField textfirstname;
static JTextField textlastname;
static JButton submit;
static JButton exit;
public static void main(String args[])
{
frame = new JFrame("Inserting to excel sheet");;
panel = new JPanel();
JLabel labeluserid = new JLabel("User id ");
JLabel labelfirstname = new JLabel("First Name");
JLabel labellastname = new JLabel("Last Name");
textuserid = new JTextField(20);
textfirstname = new JTextField(20);
textlastname = new JTextField(20);
submit = new JButton("Submit to Excel sheet");
exit = new JButton("Exit");
panel.add(labeluserid);
panel.add(textuserid);
panel.add(labelfirstname);
panel.add(textfirstname);
panel.add(labellastname);
panel.add(textlastname);
panel.add(submit);
panel.add(exit);
frame.getContentPane().add(panel);
frame.setSize(1024,100);
frame.setVisible(true);
frame.setDefaultCloseOperation(3);
submit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
String url ="jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls)};DriverID=22;READONLY=false;DBQ=";
String path ="C:\\population.xls" ;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(url+path);
PreparedStatement stat2 = con.prepareStatement("insert into [Sheet1$](USERID,FIRST_NAME,LAST_NAME) values (?,?,?)");
stat2.setString(1,textuserid.getText());
stat2.setString(2,textfirstname.getText());
stat2.setString(3,textlastname.getText());
stat2.executeUpdate();
JOptionPane.showMessageDialog(frame,new String("Successfully entered to excel sheet !!!"));
}
catch(Exception e1)
{
JOptionPane.showMessageDialog(frame,new String("Error while inserting to excel sheet !!! \n" +
e1));
}
}
});
exit.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent ex)
{
System.exit(0);
}
});
}
}
what's wrong with my coding?
> try to run this code and open the excel file.
> then after that try to run again the close the excel file.
What does this mean? The code already implicitly opens and closes the Excel file.
Do you mean you are also running Excel at the same time? If so please be more precise. When do you start Excel, when do you start your program, when do you exit Excel, when do you exit your program?
In any case please state what you are expecting to see, and what it is that you are actually seeing?
Be precise.
And you aren't closing the connection before you exit. See duffymo's code.
ejpa at 2007-7-28 20:14:27 >
Hi
While i opened excel sheet and try to send data through jdbc it works great ...
But the problem is while i closed the excel sheet and try to send data to excel sheet, it spells success.
But the data i entered is not in that excel sheet ...
What to do .?
Whats wrong with my code?
Guys help me with my code?
I've already made a suggestion, have you tried it?
Here's another: as well as closing the conneciton, close the statement. Have a good look at duffymo's code.
ejpa at 2007-7-28 20:14:27 >
Ok thanks for the suggestions.
