Results 1 to 2 of 2
  1. #1
    bdtuhin007 is offline Member
    Join Date
    Oct 2011
    Posts
    5
    Rep Power
    0

    Red face Java Excel insert/update/delete/view

    dear all,
    I have a problem that I can't insert data in my excel sheet (already made from MS excel).
    can any one help me please?

    here is the full code:


    package write;

    import java.io.File;
    import java.io.IOException;
    import java.util.Locale;

    import jxl.CellView;
    import jxl.Workbook;
    import jxl.WorkbookSettings;
    import jxl.format.UnderlineStyle;
    import jxl.write.Formula;
    import jxl.write.Label;
    import jxl.write.Number;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;
    import jxl.write.biff.RowsExceededException;

    import java.awt.BorderLayout;
    import java.awt.Component;
    import java.awt.EventQueue;
    import java.awt.Frame;

    import javax.swing.JFrame;
    import javax.swing.JPanel;
    import javax.swing.border.EmptyBorder;
    import javax.swing.ButtonGroup;
    import javax.swing.JLabel;
    import javax.swing.JOptionPane;
    import javax.swing.SwingConstants;
    import javax.swing.JTextField;
    import javax.swing.JComboBox;
    import javax.swing.JRadioButton;
    import java.awt.Font;
    import javax.swing.JCheckBox;
    import javax.swing.JSplitPane;
    import javax.swing.JToolBar;
    import javax.swing.JInternalFrame;
    import javax.swing.JLayeredPane;
    import java.awt.Panel;
    import javax.swing.JRadioButtonMenuItem;
    import javax.swing.JButton;
    import java.awt.event.ActionListener;
    import java.awt.event.ActionEvent;
    import java.awt.event.KeyEvent;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    import javax.swing.DefaultComboBoxModel;


    public class Lib_Mngt extends JFrame {

    private JPanel contentPane;
    private JTextField textName;
    private JTextField textRoad;
    private JTextField textZIP;
    private JTextField textState;
    private JTextField textMbl;

    /**
    * Launch the application.
    */
    public static void main(String[] args)
    {
    EventQueue.invokeLater(new Runnable()
    {
    public void run()
    {
    try
    {
    Lib_Mngt frame = new Lib_Mngt();
    frame.setVisible(true);
    }
    catch (Exception e)
    {
    e.printStackTrace();
    }
    }
    });
    }

    /**
    * Create the frame.
    */


    void saveData() throws Exception
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String myDB = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=I:/Java/Library Mangement/bin/LMS.xls;" + "DriverID=22;READONLY=false";
    Connection con = DriverManager.getConnection(myDB, "", "");
    Statement stmt = null;
    ResultSet rs = null;

    stmt = con.createStatement();
    String excelQuery = "select * from [Sheet1$]";
    rs = stmt.executeQuery(excelQuery);

    while (rs.next())
    {
    System.out.println(rs.getString("ID") );
    //System.out.println(rs.getString("ID") + " " + rs.getString("Name") + " " + rs.getString("Road") + " " + rs.getString("ZIP") + " " + rs.getString("State") + " " + rs.getString("Country") + " " + rs.getString("Marritial Status") + " " + rs.getString("Book Types") + " " + rs.getString("Mobile"));
    }

    try
    {
    rs.close();
    stmt.close();
    con.close();

    }
    catch (SQLException e)

    {
    e.printStackTrace();
    }

    }




    public Lib_Mngt()
    {

    setTitle("Library Management");
    setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    setBounds(100, 100, 576, 266);
    contentPane = new JPanel();
    contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
    setContentPane(contentPane);
    contentPane.setLayout(null);

    JPanel panel = new JPanel();
    panel.setBounds(9, 44, 537, 140);
    contentPane.add(panel);
    panel.setLayout(null);

    JLabel lblNewLabel = new JLabel("Name:");
    lblNewLabel.setFont(new Font("Tahoma", Font.BOLD, 11));
    lblNewLabel.setHorizontalAlignment(SwingConstants. RIGHT);
    lblNewLabel.setBounds(-30, 11, 89, 14);
    panel.add(lblNewLabel);

    JLabel lblNewLabel_1 = new JLabel("Road:");
    lblNewLabel_1.setFont(new Font("Tahoma", Font.BOLD, 11));
    lblNewLabel_1.setHorizontalAlignment(SwingConstant s.RIGHT);
    lblNewLabel_1.setBounds(-30, 36, 89, 14);
    panel.add(lblNewLabel_1);

    JLabel lblNewLabel_2 = new JLabel("Mobile:");
    lblNewLabel_2.setFont(new Font("Tahoma", Font.BOLD, 11));
    lblNewLabel_2.setHorizontalAlignment(SwingConstant s.RIGHT);
    lblNewLabel_2.setBounds(196, 61, 89, 14);
    panel.add(lblNewLabel_2);

    JLabel lblNewLabel_3 = new JLabel("ZIP:");
    lblNewLabel_3.setFont(new Font("Tahoma", Font.BOLD, 11));
    lblNewLabel_3.setHorizontalAlignment(SwingConstant s.RIGHT);
    lblNewLabel_3.setBounds(-30, 61, 89, 14);
    panel.add(lblNewLabel_3);

    JLabel lblNewLabel_4 = new JLabel("State:");
    lblNewLabel_4.setFont(new Font("Tahoma", Font.BOLD, 11));
    lblNewLabel_4.setHorizontalAlignment(SwingConstant s.RIGHT);
    lblNewLabel_4.setBounds(-30, 86, 89, 14);
    panel.add(lblNewLabel_4);

    JLabel lblNewLabel_5 = new JLabel("Country:");
    lblNewLabel_5.setFont(new Font("Tahoma", Font.BOLD, 11));
    lblNewLabel_5.setHorizontalAlignment(SwingConstant s.RIGHT);
    lblNewLabel_5.setBounds(-30, 111, 89, 14);
    panel.add(lblNewLabel_5);

    textName = new JTextField();
    textName.setHorizontalAlignment(SwingConstants.LEF T);
    textName.setBounds(63, 11, 126, 20);
    panel.add(textName);
    textName.setColumns(10);

    textRoad = new JTextField();
    textRoad.setHorizontalAlignment(SwingConstants.LEF T);
    textRoad.setBounds(63, 36, 126, 20);
    panel.add(textRoad);
    textRoad.setColumns(10);

    textZIP = new JTextField();
    textZIP.setHorizontalAlignment(SwingConstants.LEFT );
    textZIP.setBounds(63, 61, 126, 20);
    panel.add(textZIP);
    textZIP.setColumns(10);

    textState = new JTextField();
    textState.setHorizontalAlignment(SwingConstants.LE FT);
    textState.setBounds(63, 86, 126, 20);
    panel.add(textState);
    textState.setColumns(10);

    textMbl = new JTextField();
    textMbl.setHorizontalAlignment(SwingConstants.LEFT );
    textMbl.setBounds(294, 61, 150, 20);
    panel.add(textMbl);
    textMbl.setColumns(10);

    JComboBox comboBoxCntry = new JComboBox();
    comboBoxCntry.setModel(new DefaultComboBoxModel(new String[] {"Bangladesh", "Sweden", "Italy", "Germany", "Pakistan", "India", "Others..."}));
    comboBoxCntry.setBounds(63, 111, 126, 20);
    panel.add(comboBoxCntry);

    JLabel lblNewLabel_6 = new JLabel("Maritial Status:");
    lblNewLabel_6.setHorizontalAlignment(SwingConstant s.RIGHT);
    lblNewLabel_6.setFont(new Font("Tahoma", Font.BOLD, 11));
    lblNewLabel_6.setBounds(195, 12, 89, 14);
    panel.add(lblNewLabel_6);

    JLabel lblNewLabel_7 = new JLabel("Book Types:");
    lblNewLabel_7.setHorizontalAlignment(SwingConstant s.RIGHT);
    lblNewLabel_7.setFont(new Font("Tahoma", Font.BOLD, 11));
    lblNewLabel_7.setBounds(195, 37, 89, 14);
    panel.add(lblNewLabel_7);

    JCheckBox chckbxNewCheckPoem = new JCheckBox("Poem");
    chckbxNewCheckPoem.setFont(new Font("Tahoma", Font.BOLD, 11));
    chckbxNewCheckPoem.setBounds(452, 33, 57, 23);
    panel.add(chckbxNewCheckPoem);

    JCheckBox chckbxNewCheckHistry = new JCheckBox("History");
    chckbxNewCheckHistry.setFont(new Font("Tahoma", Font.BOLD, 11));
    chckbxNewCheckHistry.setBounds(369, 33, 75, 23);
    panel.add(chckbxNewCheckHistry);

    JCheckBox chckbxNewCheckRmnt = new JCheckBox("Romantic");
    chckbxNewCheckRmnt.setFont(new Font("Tahoma", Font.BOLD, 11));
    chckbxNewCheckRmnt.setBounds(290, 33, 89, 23);
    panel.add(chckbxNewCheckRmnt);

    JRadioButton radioBtnMarried = new JRadioButton("Married");
    radioBtnMarried.setFont(new Font("Tahoma", Font.BOLD, 11));
    radioBtnMarried.setBounds(369, 7, 75, 23);
    panel.add(radioBtnMarried);
    radioBtnMarried.setMnemonic(KeyEvent.VK_1);
    radioBtnMarried.setActionCommand(getTitle());

    JRadioButton radioBtnUnmrd = new JRadioButton("Unmaried");
    radioBtnUnmrd.setFont(new Font("Tahoma", Font.BOLD, 11));
    radioBtnUnmrd.setBounds(290, 7, 83, 23);
    panel.add(radioBtnUnmrd);
    radioBtnUnmrd.setMnemonic(KeyEvent.VK_2);
    radioBtnUnmrd.setActionCommand(getTitle());

    JRadioButton radioBtnDvrc = new JRadioButton("Divorced");
    radioBtnDvrc.setFont(new Font("Tahoma", Font.BOLD, 11));
    radioBtnDvrc.setBounds(452, 9, 90, 23);
    panel.add(radioBtnDvrc);
    radioBtnDvrc.setMnemonic(KeyEvent.VK_3);
    radioBtnDvrc.setActionCommand(getTitle());

    ButtonGroup group = new ButtonGroup();
    group.add(radioBtnMarried);
    group.add(radioBtnUnmrd);
    group.add(radioBtnDvrc);

    JLabel lblNewLabel_8 = new JLabel("Personal Library Management System");
    lblNewLabel_8.setFont(new Font("Times New Roman", Font.BOLD, 18));
    lblNewLabel_8.setBounds(125, 11, 305, 28);
    contentPane.add(lblNewLabel_8);

    JButton btnClose = new JButton("Close");
    btnClose.setFont(new Font("Tahoma", Font.BOLD, 11));
    btnClose.addActionListener(new ActionListener() {
    public void actionPerformed(ActionEvent arg0)
    {
    //String strClos = JOptionPane.showConfirmDialog(frame ,"Would You Like to Exit ?", "Exit ?", JOptionPane.YES_NO_OPTION);

    System.exit(0);
    }
    });
    btnClose.setBounds(438, 195, 108, 23);
    contentPane.add(btnClose);

    JButton btnShowData = new JButton("Show Data");
    btnShowData.setFont(new Font("Tahoma", Font.BOLD, 11));
    btnShowData.addActionListener(new ActionListener() {
    public void actionPerformed(ActionEvent arg0)
    {
    try {
    showData();
    } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }

    }
    });
    btnShowData.setBounds(322, 195, 108, 23);
    contentPane.add(btnShowData);

    JButton btnSave = new JButton("Save");
    btnSave.setFont(new Font("Tahoma", Font.BOLD, 11));
    btnSave.addActionListener(new ActionListener() {
    public void actionPerformed(ActionEvent e)
    {
    try {
    saveData();
    } catch (Exception e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
    }

    }
    });
    btnSave.setBounds(204, 195, 108, 23);
    contentPane.add(btnSave);

    JButton btnNew = new JButton("New");
    btnNew.setFont(new Font("Tahoma", Font.BOLD, 11));
    btnNew.setBounds(86, 195, 108, 23);
    contentPane.add(btnNew);
    }
    }

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    7

Similar Threads

  1. how to insert a file object in excel sheet using java
    By jyoti.dce in forum Advanced Java
    Replies: 1
    Last Post: 08-11-2010, 06:08 PM
  2. Replies: 2
    Last Post: 07-28-2010, 04:14 PM
  3. Replies: 2
    Last Post: 03-16-2010, 08:24 PM
  4. how to fetch 20,000 records from excel sheet & insert into access database using Java
    By santosh_tamse in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 02-22-2009, 10:24 AM
  5. Replies: 0
    Last Post: 07-31-2008, 10:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •