How to write more than 65536 rows in single excel file but more than 1 sheets?

How to write more than 65536 rows in single excel file but more than 1 sheets?

Hi, i want to write more than 65536 rows in single excel file but more than 1 sheets.. now i can write only less than 65536 rows.. but in my office db having more than 1 crore data.. pls help me... here is my code..

public class ExcelInterface extends JFrame implements ActionListener{
    public ExcelInterface() {

        JCheckBox checkBox = new JCheckBox("");
        getContentPane().add(checkBox, BorderLayout.CENTER);
    }

            javax.swing.JPanel panel;
     static javax.swing.JComboBox dblist;
     static javax.swing.JTextField ip_add;
     static javax.swing.JButton jButton1;
     static javax.swing.JButton jButton2;
     static javax.swing.JButton jButton3;
     static javax.swing.JButton jButton4;
     static javax.swing.JButton jButton5;
     static javax.swing.JFileChooser jFileChooser1;
     static javax.swing.JLabel jLabel1;
     static javax.swing.JLabel jLabel2;
     static javax.swing.JLabel jLabel3;
     static javax.swing.JLabel jLabel7;
     static javax.swing.JLabel jLabel4;
     static  javax.swing.JLabel jLabel5;
     static javax.swing.JLabel jLabel6;
     static javax.swing.JTextField outputpath;
     static javax.swing.JTextField output;
     static javax.swing.JPasswordField password;
     static javax.swing.JComboBox table;
     static javax.swing.JTextField u_name;

     public ExcelDB db = null;
     public String conString;
     String filePath;
     DatabaseMetaData dbmd; 
     String tableName;
     static File    FilePath;
     public  void component(){

        JFrame f = new JFrame("Excel Interface");
         f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
         JPanel panel=new JPanel();
         panel.setLayout(null);
         Font ff=new Font("SERIF",Font.BOLD,13);
       jLabel1= new JLabel("IP Address");
    jLabel1.setFont(ff);
    jLabel1.setBounds(20,20, 100, 25);
    panel.add(jLabel1);

    jLabel2= new JLabel("UserName");
    jLabel2.setFont(ff);
    jLabel2.setBounds(20, 55, 100, 25);
     panel.add(jLabel2);

    jLabel3= new JLabel("Password");
    jLabel3.setFont(ff);
    jLabel3.setBounds(20,85, 100, 25);
    panel.add(jLabel3);

    jLabel4= new JLabel("DBList");
    jLabel4.setFont(ff);
    jLabel4.setBounds(20,115, 100, 25);
    panel.add(jLabel4);

    //jLabel5= new JLabel("Table");
   // jLabel5.setFont(ff);
    //jLabel5.setBounds(20,145, 100, 25);
    //panel. add(jLabel5);

    jLabel6= new JLabel("Select Folder");
    jLabel6.setFont(ff);
    jLabel6.setBounds(20,175,175, 25);
    panel.add(jLabel6);

   // jLabel7= new JLabel("Output File");
    //jLabel7.setFont(ff);
    //jLabel7.setBounds(20,205, 100, 25);
    //panel.add(jLabel7);

    ip_add=new JTextField(20);
    //ip_add.
    ip_add.setFont(ff);
    ip_add.setBounds(130,25, 110, 25);
    panel.add(ip_add);

    u_name =new JTextField(20);
    u_name.setFont(ff);
    u_name.setBounds(130,55, 110, 25);
    panel.add(u_name);

    password= new JPasswordField(20);
    password.setFont(ff);
    password.setBounds(130,85, 110, 25);
    panel.add(password);

    dblist=new JComboBox();
    dblist.setFont(ff);
    dblist.setBounds(130,115, 110, 25);
    panel.add(dblist);

    table=new JComboBox();
    table.setFont(ff);
    table.setBounds(130,145, 110, 25);
   // panel.add(table);

    output=new JTextField(20);
    output.setFont(ff);
    output.setBounds(130,175, 110, 25);
    panel.add(output);

    //output=new JTextField(20);
    //output.setFont(ff);
    //output.setBounds(130,205, 110, 25);
    //panel.add(output);

    jButton1=new JButton("CONNECT");
    jButton1.setFont(ff);
    jButton1.setBounds(270,115, 110, 35);
    jButton1.setEnabled(false);  
    panel.add(jButton1);

    //jButton2=new JButton("LIST");
   // jButton2.setFont(ff);
   // jButton2.setBounds(270,145, 110, 25);
    //panel.add(jButton2);

    jButton3=new JButton("BROWSE");
    jButton3.setFont(ff);
    jButton3.setBounds(270,165, 110, 35);
    panel.add(jButton3);

    jButton4=new JButton("PROCESS");
    jButton4.setFont(ff);
    jButton4.setBounds(135,215, 110, 35);
    panel.add(jButton4);

    jButton5=new JButton("CANCEL");
    jButton5.setFont(ff);
    jButton5.setBounds(270,215, 110, 35);
    panel.add(jButton5);

    jFileChooser1=new JFileChooser();
    jFileChooser1.setFont(ff);
    jFileChooser1.setBounds(135,230,10,20);
    panel.add(jFileChooser1);

    jButton1.addActionListener(this);
    //jButton2.addActionListener(this);
    jButton3.addActionListener(this);
    jButton4.addActionListener(this);
    jButton5.addActionListener(this);

    password.addKeyListener(new java.awt.event.KeyAdapter() {  
        public void keyReleased(java.awt.event.KeyEvent evt) {  
            if (!ip_add.getText().isEmpty() && !u_name.getText().isEmpty()) {
                jButton1.setEnabled(true);  
            } else {  
                jButton1.setEnabled(false);  
            }  
        }  
    });  


    dblist.addItemListener(new ItemListener(){
          public void itemStateChanged(ItemEvent ie){
             /* if (dblist.getItemCount() != 0) {
                    table.removeAllItems();
                  System.out.println("printing tableNames");

                    try {
                       // System.out.println("jdbc:mysql://" + ip_add.getText() + ":3306/mysql" + u_name.getText() + password.getText());
                        ExcelDB.connection("jdbc:mysql://" + ip_add.getText() + ":3306/" + dblist.getSelectedItem().toString(), u_name.getText(), password.getText());
                        dbmd = (DatabaseMetaData) ExcelDB.con.getMetaData();
                        ResultSet ctlgs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});
                        while (ctlgs.next()) {
                           //table.addItem(ctlgs.getString(3));
                             //@SuppressWarnings("unused")
                            //tableNames.add(ctlgs.getString(3));
                            String tableNames = ctlgs.getString(3);

                             System.out.println(tableNames);

                        }

                        ctlgs.close();
                    } catch (Exception ex) {
                        System.out.println("Error at focusEvent:" + ex.toString());
                        JOptionPane.showMessageDialog(null, "UserName or Password or ServerName is MisMatching");
                    }
                } else {
                    JOptionPane.showMessageDialog(null, "Choose Your DB");
                    //System.out.println("I'm here");
                }*/
        }
      });

    f.getContentPane().add(panel);
    f.setSize(450, 300);
    f.setVisible(true);

     }

    @SuppressWarnings({ "deprecation" })
    public void actionPerformed(ActionEvent ae) {

            String s= ae.getActionCommand();
    if(s.equals("CONNECT"))
        { 
        dblist.removeAllItems();
        if (!ip_add.getText().isEmpty() && !u_name.getText().isEmpty()) {
               // jButton1.setEnabled(true);
                //System.out.println("Inside if");

                try {
                    db = new ExcelDB();
                    System.out.println("jdbc:mysql://" + ip_add.getText() + ":3306/mysql" + u_name.getText() + password.getText());
                    ExcelDB.connection("jdbc:mysql://" + ip_add.getText() + ":3306/", u_name.getText(), password.getText());
                    dbmd = (DatabaseMetaData) ExcelDB.con.getMetaData();
                    ResultSet ctlgs = dbmd.getCatalogs();
                    while (ctlgs.next()) {
                        dblist.addItem(ctlgs.getString(1));
                    }
                    ExcelDB.con.close();
                    ctlgs.close();              }
                catch (Exception ex) {
                    System.out.println("Error at focusEvent:" + ex.toString());
                    JOptionPane.showMessageDialog(null, "UserName or Password or ServerName is MisMatching");
                    jButton1.setEnabled(false);
                    ip_add.setText(null);
                    u_name.setText(null);
                    password.setText(null);

                }
            } else {
                JOptionPane.showMessageDialog(null, "Enter connection Details");

        }

        } 
            /*  if(s.equals("LIST")){
            if (dblist.getItemCount() != 0) {
                table.removeAllItems();

                try {
                    System.out.println("jdbc:mysql://" + ip_add.getText() + ":3306/mysql" + u_name.getText() + password.getText());
                    ExcelDB.connection("jdbc:mysql://" + ip_add.getText() + ":3306/" + dblist.getSelectedItem().toString(), u_name.getText(), password.getText());
                    dbmd = (DatabaseMetaData) ExcelDB.con.getMetaData();
                    ResultSet ctlgs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});
                    while (ctlgs.next()) {
                        table.addItem(ctlgs.getString(3));
                    }
                    ctlgs.close();
                } catch (Exception ex) {
                    System.out.println("Error at focusEvent:" + ex.toString());
                    JOptionPane.showMessageDialog(null, "UserName or Password or ServerName is MisMatching");
                }
            } else {
                JOptionPane.showMessageDialog(null, "Choose Your DB");
            }
    } */    

       if(s.equals("BROWSE")){
            jFileChooser1.setVisible(true);
            // FolderChooser chooser = new FolderChooser();

            JFileChooser chooser = new JFileChooser();
            chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
            int o = chooser.showOpenDialog(this.panel);
           // chooser.setCurrentDirectory(new java.io.File("."));
            chooser.setDialogTitle("choosertitle");         
           // chooser.setAcceptAllFileFilterUsed(false);

            if (o == JFileChooser.APPROVE_OPTION) {
            FilePath = chooser.getSelectedFile();
                    {
                    output.setText(FilePath.toString());
                    }
                            }
        }
            /*jFileChooser1.setVisible(true);
            int o = jFileChooser1.showOpenDialog(this.panel);
             if (o == JFileChooser.APPROVE_OPTION) {
                filePath = jFileChooser1.getSelectedFile().toString();
                {
                    output.setText(filePath);
                }
            }
        }*/

            if(s.equals("PROCESS")){                              
                //table.removeAllItems();       
             if (output.getText().isEmpty()) {
                    JOptionPane.showMessageDialog(null, "Select Folder");

             } else {           
             if (dblist.getItemCount() != 0) {
                    table.removeAllItems();
              //System.out.println("printing tableNames");

                    try {
                       // System.out.println("jdbc:mysql://" + ip_add.getText() + ":3306/mysql" + u_name.getText() + password.getText());
                        ExcelDB.connection("jdbc:mysql://" + ip_add.getText() + ":3306/" + dblist.getSelectedItem().toString(), u_name.getText(), password.getText());
                        dbmd = (DatabaseMetaData) ExcelDB.con.getMetaData();
                        ResultSet ctlgs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});
                    /*  int coun=0;
                      while(ctlgs.next()) {
                          coun++;
                      }
                      System.out.println("Coun - " + coun);
                     for(int i=1; i<=coun;i++){  */

                      String tableNames = "";

                        while (ctlgs.next()) {
                            try{
                             tableNames = ctlgs.getString(3);                            
                             convertExcel(tableNames);
                                        // System.out.println(tableNames);

                                            }     catch (Exception e) {
                                           JOptionPane.showMessageDialog(null,"Error in table name: "+tableNames);
                                                //e.printStackTrace();
                                                //System.exit(0);

                                            }  }
                        //}
                        ctlgs.close();
                           JOptionPane.showMessageDialog(null, "Process Completed");

                    } catch (Exception ex) {
                        System.out.println("Error at focusEvent:" + ex.toString());
                        JOptionPane.showMessageDialog(null, "UserName or Password or ServerName is MisMatching");
                    }
                } else {
                    JOptionPane.showMessageDialog(null, "Choose Your DB");
                    //System.out.println("I'm here");
                }                               
                    }    }           

        if(s.equals("CANCEL")){
            System.exit(0);
        }

    }           
    @SuppressWarnings("deprecation")
    public static void convertExcel(String tableName) throws SQLException
    {
        System.out.println("Processing "+tableName);
        int rowCnt = 0;

        ResultSet rsCount = null;
        PreparedStatement stmt1 =ExcelDB.con.prepareStatement("select count(*) from "+tableName+"");
        rsCount = stmt1.executeQuery();
        rsCount.next();
        rowCnt = rsCount.getInt(1);
        System.out.println("rowcount:"+rowCnt);

        if(rowCnt<65536)
        {       

        try{
            HSSFWorkbook exbook=new HSSFWorkbook();
            HSSFSheet exsheet=exbook.createSheet();


            int rowIndex=0;
            ResultSet rs = null;
            try{
            //PreparedStatement stmt =ExcelDB.con.prepareStatement("select * from " +table.getSelectedItem() );
                /*PreparedStatement stmt1 =ExcelDB.con.prepareStatement("select count (*) from "+tableName+"");
                ResultSet rs1 = stmt1.executeQuery();
                if(rs1 != null){*/
                PreparedStatement stmt =ExcelDB.con.prepareStatement("select * from "+tableName+"");
                //stmt.setFetchSize(1000);
            rs = stmt.executeQuery();

            //System.out.println("select * from" +table.getSelectedItem());
            ResultSetMetaData metadata = rs.getMetaData();
            List<String> colNames = new ArrayList<String>();
            HSSFRow titleRow = exsheet.createRow(rowIndex++);

            for (int i = 1; i <= metadata.getColumnCount(); i++) {
                colNames.add(metadata.getColumnName(i));
                titleRow.createCell((short) (i-1)).setCellValue(
                  new HSSFRichTextString(metadata.getColumnName(i)));
                exsheet.setColumnWidth((short) (i-1), (short) 4000);
            }

                while (rs.next()) {
                    HSSFRow dataRow = exsheet.createRow(rowIndex++);
                   // System.out.println(rowIndex);
                    short colIndex = 0;
                    for (String colName : colNames) {
                      dataRow.createCell(colIndex++).setCellValue(
                        new HSSFRichTextString(rs.getString(colName)));
                      //System.out.println(rs.getString(colName));
                    }                                         
                        } 
                String FilePathName = FilePath.toString();
                FilePathName = FilePathName+"/"+tableName+".xls";
                  exbook.write(new FileOutputStream(FilePathName));     
                System.out.println("Completed "+tableName);

            }catch(Exception es){
                JOptionPane.showMessageDialog(null, es);
                //System.exit(0);
                //es.printStackTrace();
                }                           
        }           

    catch (Exception e1) {
        JOptionPane.showMessageDialog(null,"error");
        //JOptionPane.showMessageDialog(null,tableName);

       }        
        }//rowcount
        else{
            JOptionPane.showMessageDialog(null,"Table "+tableName+" having more than 65536 rows");
            System.out.println("Completed "+tableName);

        }
            }   

    public static void main(String args[]){
            /*try {
                System.setOut(new java.io.PrintStream(new java.io.FileOutputStream("Outputpart.txt")));

                System.setErr(new java.io.PrintStream(new java.io.FileOutputStream("Errors.txt")));
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }*/
        ExcelInterface ei=new ExcelInterface();
        ei.component();

    }


    }

It is database connection code...

import java.sql.*;

import javax.swing.JOptionPane;
public class ExcelDB {

    public  static Connection con;
    public Connection con1;
    PreparedStatement stmt=null;

        ExcelDB(){

        }
           public static void connection(String conString,String user,String pass)throws Exception{
                    try{
                        Class.forName("com.mysql.jdbc.Driver");
                        con=DriverManager.getConnection(conString,user,pass);
                        System.out.println("connected With DB");
            }catch(ClassNotFoundException cnfe){
                        System.out.println("Error..."+cnfe);
                        JOptionPane.showMessageDialog(null, "Class Not Found");
            }
            catch(SQLException sqle){
                        System.out.println("SQL Error..."+sqle);
                        JOptionPane.showMessageDialog(null, "Connection is Empty");
            }
            }
        public void closeDB()throws Exception{
                if(!con.isClosed()){
                con.close();}
        }


}

kindly pls rep me... thanks...

View Answers

Ads









Related Tutorials/Questions & Answers:
How to write more than 65536 rows in single excel file but more than 1 sheets?
Hi.. how to write more than one sheets in a excel file... pls anybody help me....
Advertisements
Apply more than one xsl for single XML
More than one Faces Configuration file
Is Python more powerful than C++?
How to get more than one value from ajax
how to handle action events in case of more than one JFrame
How to Display Duplicate elements from more than one List in java?
Can a Class extend more than one Class?
how to write greater than symbol in a file using java
how can retrieve more than one values in text field using ajax?
for writting java program why we are taking more than one class
problem in sending more than 500 values to a jsp page
Deployment on Server that can be used simultaneously by more than one user
mapping between java class and more than 2 tables
Server side validation on dynamically generated fields from more than one table on spring framework.
J query event for selecting the more than one div in tablet browser(Select and drag)?
How to insert rows from Excel spreadsheet into database by browsing the excel file?
how to use Excel Templet to write excel file using java.
how to use Excel Template to write excel file using java
why java ignore backspace event when there are more then 1 keychar for a single keystroke.
Combining records from two or more orders files into a single ordered file is called
How to import data from sql server table into an excel file by creating the rows dynamically in the excel according to the dataabase??
How to Retrieve Data from the database and write into excel file using Java
Replication factor: 1 larger than available brokers: 0
How to read a rows which have a values in a excel file using apache poi - JSP-Servlet
How to Display Font Other than english
write excel file into the oracle database
To read & write a excel file using the core java
Are containers faster than VMs
insert rows from Excel sheet into a database by browsing the file

Ads