Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



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

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


sathya narayanan
Hi.. how to write more than one sheets in a excel file... pls anybody help me....
0 Answer(s)      2 years and 11 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();

    }   
    }

Advertisement
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
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
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...........
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
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
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
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
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 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
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 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 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
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 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
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
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!!!!!!!
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
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 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
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
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
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
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
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
 

 

 

DMCA.com