Hi.. how to write more than one sheets in a excel file... pls anybody help me....

Hi.. how to write more than one sheets in a excel file... pls anybody help me....

i can write only one sheet in a excel file.. but i want to write more than one sheets.. For example: first sheet have complete mean automatically go to second sheet... pls help me.. here my code..

import java.awt.BorderLayout;


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

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

        JCheckBox checkBox_1 = new JCheckBox("New check box");
        getContentPane().add(checkBox_1, BorderLayout.WEST);
    }

            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);  
            }  
        }  
    });  


    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();

    }   
    }
View Answers









Related Tutorials/Questions & Answers:
Hi.. how to write more than one sheets in a excel file... pls anybody help me....
Hi.. how to write more than one sheets in a excel file... pls anybody help me....  i can write only one sheet in a excel file.. but i want to write more than one sheets.. For example: first sheet have complete mean automatically
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... db having more than 1 crore data.. pls help me... here is my code.. public class
Advertisements
hi , i cant make this programmer , can any one help me to make it pls - Java Beginners
hi , i cant make this programmer , can any one help me to make it pls †... a new text file ? -Open and append (add new information to ) an existing file ? -Update record(s)? -Delete record(s)? -Make one copy of a specific text file
More than one Faces Configuration file
More than one Faces Configuration file  Is it possible to have more than one Faces Configuration file
more than one struts-config.xml file
more than one struts-config.xml file  Can we have more than one struts-config.xml file for a single Struts application
Hi Every One , please give me some idia to opning audio File . please Help ..
Hi Every One , please give me some idia to opning audio File . please Help ..  hi Dear Friend , please give me a program to opening audio file... me .please Help
pls help me with this question
pls help me with this question  how to write a program in C++ using encapsulation,polymorphism,inheritance and abstraction?(all these concepts should come in a single program
pls help me it urgent
pls help me it urgent  hey, pls help me i want to know that can we call java/.bat file from plsql/proceudre /trigger
pls help me it urgent
pls help me it urgent  hey, pls help me i want to know that can we call java/.bat file from plsql/proceudre /trigger
pls help me it urgent
pls help me it urgent  hey, pls help me i want to know that can we call java/.bat file from plsql/proceudre /trigger
Pls help me...........
Pls help me...........  how to use the text file as input to insert the values injdbc?   Please visit the following link: Insert text file data into database
please any one can help me to write a code for this question?
please any one can help me to write a code for this question?  1) Copy one file content to other? 2) Count the number of words in a file
Pls help me with strings
Pls help me with strings  To reverse a every other word in a string. Example :- If the input is "This is a line of message" the output should be "sihT is a line fo message
Pls help me with strings
Pls help me with strings  To reverse a every other word in a string. Example :- If the input is "This is a line of message" the output should be "sihT is a line fo message
how to use Excel Templet to write excel file using java.
how to use Excel Templet to write excel file using java.  how to use Excel Templet to write excel file using java
how to write greater than symbol in a file using java
how to write greater than symbol in a file using java  Hello I want to write < and > symbol in a file but its coming like > and < Please help me   Hi Friend, You can write the symbols by taking them
how to use Excel Template to write excel file using java
how to use Excel Template to write excel file using java  How to use Excel template to write data in that file using java
how to handle action events in case of more than one JFrame
how to handle action events in case of more than one JFrame   I have... is not moving and i am not getting to point how to resolve this problem .. So plz help me out
please any one can help me to write a code for this question?
please any one can help me to write a code for this question?  Q 1) In a class first day 25 students are joined. After two days that total students will increased to 60. We can develop a program by using ArrayList concept
how to write build file for one project - Ant
how to write build file for one project   hi This is kishore, i want to know how to write build file for one sample project in java. if u have example send to me. Tx in adv.  Hi Kisore, We
How to get more than one value from ajax
How to get more than one value from ajax  I have multiple select list box in php. i filled the value using ajax. how to get different value to fill the same list box Ex. option value as id (adsbygoogle
pls help me!!!!!!!
how to implement the push operation  how to implement the push operation to get a new screen for developing iphone application   In iPhone applications "PUSH NOTIFICAION" notify users when a specific update occurs
How to Display Duplicate elements from more than one List in java?
How to Display Duplicate elements from more than one List in java?  How to Display Duplicate elements from more than one List in java? //I mean... How to Display common elements Display   Hare is an example
Can anybody help me with this simple MySql code
Can anybody help me with this simple MySql code  select c.countryid... cad 2013-03-16 18:26:31 can any one help me with the query because i want to select al of them but it should select for me only 1 duplicate countryid
pls. help me - Java Beginners
pls. help me  please help me i do need now if its okay... Consider the method headings: void funcOne(int[] alpha, int size) int funcSum(int...[] list = new int[50]; int[] Alist = new int[60]; int num; Write Java
Apply more than one xsl for single XML
Apply more than one xsl for single XML  How to apply more than one xsl for same xml file
How to Retrieve Data from the database and write into excel file using Java
How to Retrieve Data from the database and write into excel file using Java  Hi, I am trying to develop an small application where i trying to retrieve Data from the database and store the details in excel file. Please can
hi frnds.. help me..
hi frnds.. help me..  i ve a doubt in incompatible type error (block letter). plz help me clear this error! thanks in advance. This is my code. double velo[][] = new double [task] [resource]; for(int i=0;iList<Double>
i want to find the byte code of a image file ... for my project..plz if anybody help me for java coding i will grateful..
i want to find the byte code of a image file ... for my project..plz if anybody help me for java coding i will grateful..   i want to convert a image file to its byte code format that help me for the pattern matching in my
Can a Class extend more than one Class?
Can a Class extend more than one Class?   Hi, Can a Class extend more than one Class? thanks
how to count no.of sheets in excel through POI jar as well as through Java?
how to count no.of sheets in excel through POI jar as well as through Java?  I have an excel file, it contains 'n' no.of sheets(the no.of sheets...)). But I am not able to read multiple sheets one by one. Could you please let me
importing excel file and drawing multiple graphs from one excel file
and then plotting grph:) it really helped me:):) If you can please help me with one more thing... can i do that?? and also help me how can i get date and time value from excel...importing excel file and drawing multiple graphs from one excel file †
How to write content of one file to another file.
; In this example we will open a file and then write the content in new file... the associated stream. void write(int b) Method writes one... the data into second file and also calculates and report the checksum with the help
Writing more than one cards in a WML deck.
   Writing more than one cards in a deck.In this lesson we will write application that uses two cards in a deck
how to restrict user against marking same event more than one times - JSP-Servlet
how to restrict user against marking same event more than one times  Hi I am developing student feedback system for educational institution...(event) is stored in final database. Now how can I restrict the student against
pls help me sir its urgent
pls help me sir its urgent  thanks for reply, but i am getting this error pls help me its urgent type Exception report message description The server encountered an internal error () that prevented it from
To read & write a excel file using the core java
To read & write a excel file using the core java  Hai, I'm new to JavaProgram.But now i need java program to read & write a excel file so, can anyone help me to learn the above mentioned topic(link for the portion
how can retrieve more than one values in text field using ajax?
how can retrieve more than one values in text field using ajax?  im... in while loop why if write this so it is unsuccessful while($row=mysql_fetch_array..._db\findseatno.php on line 23 but if i write this code with option menu
Implementing more than one Job Details and Triggers
will learn how to implement more than one triggers and jobs with a quartz... Implementing more than one Job Details and Triggers... of more than one job details and triggers. Description of program:ADS
Can anybody tell me how to resolve this issue?
Can anybody tell me how to resolve this issue?  java.lang.Exception: Exception : java.lang.Exception: Generic Errors = java.util.MissingResourceException: Can't find bundle for base name Connection, locale en_US
could any one help me?
could any one help me?  I'm a student in the faculty of computer and information science we will make graduation project "taxi dispatcher" we need... use ? and what is the program will write this language
Hii i want a JMS application can anybody help me
Hii i want a JMS application can anybody help me  jms application
hi... pls give me the example of java coding for following question
hi... pls give me the example of java coding for following question   Write a program that computes loan payments. The loan can be a car loan, a student loan, or a home mortgage loan. The program lets the user enter the interest
hi... pls give me the example of java coding fo following question
hi... pls give me the example of java coding fo following question  Write a java program that reads the following information and prints a payroll statement: Employee‚??s name (e.g: Sam) Number of hours worked in a week (e.g: 10
write excel file into the oracle database
write excel file into the oracle database  dear sir, i need the jsp code that reads the excel file and stores it into the oracle database table..and also i need the code to connect oracle database? thank u in advance
sir, how to convert excel file to csv file using java? please send me sample code.
sir, how to convert excel file to csv file using java? please send me sample code.  please send me sample code for converting excel file into csv file uisng java
Creating Excel sheets - Java Beginners
Creating Excel sheets  Hi, I want the java code for creating excels sheets with two workbooks using POI, and to find the difference between the particular value of one cell and others. asuming the contents of teh files adn
hi... pls give me the example of java coding fo following question
hi... pls give me the example of java coding fo following question  example java coding that reads in the radius and length of cylinder and computes volume. formula: Area = radius * radius * PI Volume = area * length
Can anybody tell me how to use #parameters in struts 2 ?
Can anybody tell me how to use #parameters in struts 2 ?  I have a login page, And by using struts.xml and #parameters I want to display error.../Login.jsp ADS_TO_REPLACE_1 Its urgent. Pl help me out
for writting java program why we are taking more than one class
for writting java program why we are taking more than one class  for writting java program only one class is enough but why we are taking more than one class

Ads