Home Answers Viewqa Java-Beginners Hi.. how to write more than one sheets in a excel file... pls anybody help me....

 
 


sathya narayanan
Hi.. how to write more than one sheets in a excel file... pls anybody help me....
0 Answer(s)      a year and 3 months ago
Posted in : Java Beginners

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 Pages:
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
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
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
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...........
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
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
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 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
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 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
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
More than one Faces Configuration file
More than one Faces Configuration file  Is it possible to have more than one Faces Configuration file
hi
can any one help me??????   We are providing you a simple application...hi   i want to develop a online bit by bit examination process as part of my project in this i am stuck at how to store multiple choice questions
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
Error in connecting to the mySQL database in TOMCAT using more than one PC (database connection pooling)
Error in connecting to the mySQL database in TOMCAT using more than one PC (database connection pooling)  how do i implement connection pooling... me? I tried using DriverManager.getConnection, and I can work with two or more
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!!!!!!!
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
Implementing more than one Job Details and Triggers
will learn how to implement more than one triggers and jobs with a quartz... are going to implement more than one job details and triggers with the help... Implementing more than one Job Details and Triggers
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 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
How to write to file using FileOutputStream
How to write to file using FileOutputStream  Hi friends, Please help me in java program. How to write to file using FileOutputStream? thanks,   Hi, To write a file using FileOutputStream, we have to use
How to write to xml file in Java?
How to write to xml file in Java?  Hi Friends, Can anyone help me how to write in xml file in Java programming. Please fill free to give example or reference website for getting example.   Hi, To write in xml file
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
hi
databases like the above i.e friends,family,etc pls can any one tell how can i do this.pls help...hi   on clicking on button on 1st jsp page i want to display
hi
databases like the above i.e friends,family,etc pls can any one tell how can i do this.pls help...hi   on clicking on button on 1st jsp page i want to display
How to add dynamically rows into database ?Need help pls
How to add dynamically rows into database ?Need help pls  Hi everyone... into the database.I have been trying to solve in for ages.Can anyone pls help me.Thanks... than one row! if (lastRow > 2) tbl.deleteRow(lastRow - 1); } <
hi
online multiple choice examination  hi i am developing online multiple choice examination for that i want to store questions,four options,correct answer in a xml file using jsp or java?can any one help me?   Please
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 - 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
hi
storing data in xml file using jsp  hi i am storing data in xml file using jsp.in this when i enter data into xml file i am getting xml declaration for each specified data?can any one help me to prevent this?   <
Help me
Help me  hello!!!!. i have a Excel file which includes 4 columns and somewhat 8500 rows.these rows i have printed in SQL thru Excel file and i have 1 MYSql database which includes all those 4 fields i have defined in excel file
help me
help me  hi sir pls tell me how to use ajax form validatin from very simple way with two ot three text field pls help me use ajax in php my email id is:- rohit11tripathi@gmail.com pls sir give me some vasic information i know
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
how to write to file from string in Java
how to write to file from string in Java  Hi, Please any one help me for how to write to file from string in Java. I am beginner in Java programming. Thanks,   Hi, Are you eager to learn how to write to file from
help me
help me  HI. Please help me for doing project. i want to send control from one jsp page to 2 jsp pages... is it possible? if possible how to do
plz help me - Java Beginners
is true...but i very confuse that how it is not displayed admin page plz any one give me reponse my persinal given id  Hi ragni, i am sending...plz help me  Thanks deepak continue response..i face some problem i
hi i m new to hibernate..plzzzzzzzzzzzzzz help me.... - Hibernate
hi i m new to hibernate..plzzzzzzzzzzzzzz help me....  hi friends i m......... plzzzzzzzzzzzzzzz help me whats the problem... its showing me //************Error... change my query ... now its showing me this error... plz help me..... MY PRG
i have a problem to do this question...pls help me..
i have a problem to do this question...pls help me..  Write a program that prompts the user to input an integer and the output the number....   Hi Friend, Try this: import java.util.*; public class ReverseNumber
write xml file with jsp useBean - JSP-Servlet
write xml file with jsp useBean  how to write into xml files with jsp.. the code for writing is in a class.. pls help me to solve this..thanx in advance...  Hi friend, The logic of the program is written inside
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
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 option name as some string
help me
help me... the flow of people , cars , airplanes , transactions , and so on . write a program... by pressing a key. you will need to determine how the customer will decide which line
do the combinations in java, pls help me its urhent - Development process
do the combinations in java, pls help me its urhent  import... one help me: action when condition1 and condition3 action when condition1... and condition3  Hi friend, We check your code having error and Plz check
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 Its urgent. Pl help me out
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
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
how to write in xml? - XML
how to write in xml?  can anybody give the code how to write in xml file. i wan to write inside the tag, so what i have to do? Thanks...  Hi friend, Code to help in solving the problem : import java.io.*; import

Ask Questions?

If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.

Ask your questions, our development team will try to give answers to your questions.