Results 1 to 2 of 2
  1. #1
    emyk is offline Member
    Join Date
    Mar 2011
    Posts
    19
    Rep Power
    0

    Default Java Netbean Only one line gets written into a file

    I am trying to write oracle database select returns into an excel file. The script only wirites one line (first/last row ) into the file. I am trying to figure out what am I doing wrong here to get all the returned rows data written into the file.

    Java Code:
    package writefilestoexcel;
    
    import java.awt.Label;
    import java.io.FileNotFoundException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import javax.xml.parsers.ParserConfigurationException;
    import javax.xml.stream.XMLStreamException;
    import javax.xml.transform.TransformerException;
    import org.xml.sax.SAXException;
    import java.io.*;
    import java.io.File;
    import java.io.IOException;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    
    public class writefilestoexcel {
       
    
        /**
         * @param args the command line arguments
         */
         public static void main(String argv[]) throws FileNotFoundException, XMLStreamException, IOException, ParserConfigurationException, ClassNotFoundException, SQLException, SAXException, TransformerException, InterruptedException {
    
          try { 
              String inputfile = ("C:/NewExcelFile1.xls") ;
              File filename = new File(inputfile);
             
    
            HSSFWorkbook workbook=new HSSFWorkbook();
    
    
    Object UNIT = "Please Enter 5 Digit ID";
            String LocidInputDialog = javax.swing.JOptionPane.showInputDialog(
                  UNIT);
    System.out.println(LocidInputDialog);
    Class.forName("oracle.jdbc.driver.OracleDriver");
                       String url = "jdbc:oracle:thin:@dbserver:1535:database1";  
     Connection conn = DriverManager.getConnection(url,"ID","PASSWORD"); 
                   java.sql.Statement s=conn.createStatement();
    ResultSet rs; 
    HSSFSheet sheet =  workbook.createSheet("LID");  
    HSSFRow rowhead=   sheet.createRow((short)0);
            rowhead.createCell((short) 0).setCellValue("US_NAME");
            rowhead.createCell((short) 1).setCellValue("US_ABREV");
            rowhead.createCell((short) 2).setCellValue("GR_NAME");
            rowhead.createCell((short) 3).setCellValue("Email");
    rs = s.executeQuery("select * from us_locations ");
    
    FileOutputStream fileOut =  new FileOutputStream(filename);
     
    while ( rs.next() ) { 
    String LID = rs.getString("LOCID" ) + (",");
    String USNAME = rs.getString("US_NAME" ) + (",");
    String USABREV = rs.getString("US_ABREV" )+ (",");
    String GRNAME = rs.getString("GR_NAME" );
    
     
    
            HSSFRow row=   sheet.createRow((short)1);
            row.createCell((short) 0).setCellValue(LID);
            row.createCell((short) 1).setCellValue(USNAME);
            row.createCell((short) 2).setCellValue(USABREV);
            row.createCell((short) 3).setCellValue(GRNAME);
           
    
    System.out.println(LID + USNAME + USABREV + GRNAME); 
    
    
    
    } 
    workbook.write(fileOut);
    fileOut.close();
    
    
    
    
            System.out.println("Your excel file has been generated!");
    conn.close(); 
    
    } catch (Exception e) { 
    System.err.println("Got an exception! "); 
    System.err.println(e.getMessage()); 
    }
         }
    }

  2. #2
    emyk is offline Member
    Join Date
    Mar 2011
    Posts
    19
    Rep Power
    0

    Default Re: Java Netbean Only one line gets written into a file

    i have fixed my issue. I needed to initialize the loop and added a line at the end to increment the return. All working fine now.

    Quote Originally Posted by emyk View Post
    I am trying to write oracle database select returns into an excel file. The script only wirites one line (first/last row ) into the file. I am trying to figure out what am I doing wrong here to get all the returned rows data written into the file.

    Java Code:
    package writefilestoexcel;
    
    import java.awt.Label;
    import java.io.FileNotFoundException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import javax.xml.parsers.ParserConfigurationException;
    import javax.xml.stream.XMLStreamException;
    import javax.xml.transform.TransformerException;
    import org.xml.sax.SAXException;
    import java.io.*;
    import java.io.File;
    import java.io.IOException;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    
    public class writefilestoexcel {
       
    
        /**
         * @param args the command line arguments
         */
         public static void main(String argv[]) throws FileNotFoundException, XMLStreamException, IOException, ParserConfigurationException, ClassNotFoundException, SQLException, SAXException, TransformerException, InterruptedException {
    
                 int nRow = 1;
                String strQuery = null;
                Connection con = null;            
                HSSFRow row;
                HSSFCell cell;
          try { 
              String inputfile = ("C:/NewExcelFile1.xls") ;
              File filename = new File(inputfile);
             
    
            HSSFWorkbook workbook=new HSSFWorkbook();
    
    
    Object UNIT = "Please Enter 5 Digit ID";
            String LocidInputDialog = javax.swing.JOptionPane.showInputDialog(
                  UNIT);
    System.out.println(LocidInputDialog);
    Class.forName("oracle.jdbc.driver.OracleDriver");
                       String url = "jdbc:oracle:thin:@dbserver:1535:database1";  
     Connection conn = DriverManager.getConnection(url,"ID","PASSWORD"); 
                   java.sql.Statement s=conn.createStatement();
    ResultSet rs; 
    HSSFSheet sheet =  workbook.createSheet("LID");  
    HSSFRow rowhead=   sheet.createRow((short)0);
            rowhead.createCell((short) 0).setCellValue("US_NAME");
            rowhead.createCell((short) 1).setCellValue("US_ABREV");
            rowhead.createCell((short) 2).setCellValue("GR_NAME");
            rowhead.createCell((short) 3).setCellValue("Email");
    rs = s.executeQuery("select * from us_locations ");
    
    FileOutputStream fileOut =  new FileOutputStream(filename);
     
    while ( rs.next() ) { 
    String LID = rs.getString("LOCID" ) + (",");
    String USNAME = rs.getString("US_NAME" ) + (",");
    String USABREV = rs.getString("US_ABREV" )+ (",");
    String GRNAME = rs.getString("GR_NAME" );
    
     
    
            row=   sheet.createRow((short)nRow);
            row.createCell((short) 0).setCellValue(LID);
            row.createCell((short) 1).setCellValue(USNAME);
            row.createCell((short) 2).setCellValue(USABREV);
            row.createCell((short) 3).setCellValue(GRNAME);
          nRow++
    System.out.println(LID + USNAME + USABREV + GRNAME); 
    
    
    
    } 
    workbook.write(fileOut);
    fileOut.close();
    
    
    
    
            System.out.println("Your excel file has been generated!");
    conn.close(); 
    
    } catch (Exception e) { 
    System.err.println("Got an exception! "); 
    System.err.println(e.getMessage()); 
    }
         }
    }

Similar Threads

  1. Replies: 7
    Last Post: 09-17-2013, 04:42 AM
  2. First Element of Array being written to file twice Help
    By kingkongjaffa in forum New To Java
    Replies: 1
    Last Post: 08-16-2011, 01:10 AM
  3. Java- Writing a file and reading a file line by line
    By Nazneen Ali in forum New To Java
    Replies: 7
    Last Post: 07-20-2011, 08:56 AM
  4. can i run line by line command in netbean?
    By choconlongxu in forum NetBeans
    Replies: 1
    Last Post: 07-19-2010, 09:41 PM
  5. Text file over written
    By bugger in forum New To Java
    Replies: 2
    Last Post: 11-11-2007, 08:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •