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









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 struts-config.xml file
More than 1 preparedStatement object - Java Beginners
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
Implementing more than one Job Details and Triggers
Writing more than one cards in a WML deck.
how to restrict user against marking same event more than one times - JSP-Servlet
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.
can i restrict a class that it will not allows create more than two objects - Java Interview Questions
J query event for selecting the more than one div in tablet browser(Select and drag)?
Use of "|" separator for selecting more than one path
To get the value of more than one text box in an HTML page to a jsp page - JSP-Interview Questions
How to insert rows from Excel spreadsheet into database by browsing the excel file?
Error in connecting to the mySQL database in TOMCAT using more than one PC (database connection pooling)
how to use Excel Templet to write excel file using java.
why java ignore backspace event when there are more then 1 keychar for a single keystroke.
how to use Excel Template to write excel file using java
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
More About the CronTrigger
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
Retrieve Data from the database and write into excel file
How to write in File in Java
Writing more than one cards in a WML deck.
How to Write To File BufferedWriter in Java
write a program in java to read a text file and write the output to an excel file using filereader and filewriter?
How to write in File in Java
How to Write to file in Java?
Is DataCamp better than coursera?
Is DataCamp better than codecademy?

Ads