Results 1 to 4 of 4
  1. #1
    melvine is offline Member
    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0

    Default export data from oracle to excel

    Hi,

    I want to export data from oracle to excel.
    I try to create the following code

    Java Code:
    package export_data;
     
    import java.sql.DriverManager;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
     
     
    import java.io.File;
    import java.io.IOException;
     
     
     
    public class create_excel {
     
    	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
    	private static final String DB_CONNECTION = "jdbc:oracle:thin:@127.0.0.1:1522:adk";
    	private static final String DB_USER = "test";
    	private static final String DB_PASSWORD = "test";
     
     
    	public static void  main(String[] args) throws ClassNotFoundException {
    		// TODO Auto-generated method stub
    		System.out.println("Hello");
     
     
     
    		// start 
    		try {
     
    			// Class.forName ("oracle.jdbc.driver.OracleDriver");
     
    			selectRecordsFromTable();
     
     
     
    		} catch (SQLException e) {
     
    			System.out.println(e.getMessage());
     
    		}
     
     
     
     
    	// end
    	}
     
    	private static void selectRecordsFromTable() throws SQLException {
     
    		Connection dbConnection = null;
    		PreparedStatement preparedStatement = null;
    		ecrire_excel cree = new ecrire_excel();
    		cree.create_file("X:\\Utilisateurs\\pep\\Desktop\\test.xls");
     
    		String selectSQL = "select distinct table_name from all_tab_columns where owner='TEST'";
    		String selectSQL2 = "select * from test";
    		try {
    			dbConnection = getDBConnection();
    			preparedStatement = dbConnection.prepareStatement(selectSQL2);
     
     
    			// execute select SQL stetement
    			ResultSet rs = preparedStatement.executeQuery();
     
     
    			/*Statement st = dbConnection.createStatement();
    			// Active le curseur.
    			st.setFetchSize(50);
    			ResultSet rs1 = st.executeQuery("SELECT * FROM test");
    			while (rs1.next()) {
    			   System.out.println("une ligne a été renvoyée." );
    			}
    			rs1.close();*/
     
     
     
    			/*String[] noms = getNomsColonnes(rs);
    			for(int i = 0; i < noms.length; i++){
    			   System.out.print(noms[i] + " ,");
    			}*/
     
    		int i = 1;
    			while (rs.next()) {
     
     
     
    				//System.out.println(" ICI --> " + rs.getString(3));
     
    				//String id = rs.getString("TABLE_NAME");
    				//String attribute = rs.getString("COLUMN_ID");
    				//String complex = rs.getString("OWNER");
     
    				/*System.out.print("id : " + id + " ");
    				System.out.print("attribute : " + attribute + " ");
    				System.out.println("complex : " + complex);*/
     
    				ResultSetMetaData metadata = rs.getMetaData();
    				int nombreColonnes = metadata.getColumnCount();
    				//int nbligne = metadata.getColumnName(column)
    				//System.out.println("Ce ResultSet contient "+nombreColonnes+" colonnes.");
    				String vv = "";
    				for(int j = 1; j <= nombreColonnes ; j++){
    					vv = vv + rs.getString(j) + "|";	
    					cree.write_file(nombreColonnes,rs.getString(j),i,j);
    				}
    				vv = vv.substring(0,vv.length() - 1);
    				//System.out.println(vv);
     
     
    			i = i + 1;
    			}
     
    			cree.ferme();
     
    		} catch (SQLException e) {
     
    			System.out.println(e.getMessage());
     
    		} finally {
     
    			if (preparedStatement != null) {
    				preparedStatement.close();
    			}
     
    			if (dbConnection != null) {
    				dbConnection.close();
    			}
     
    		}
     
    	}
     
     
     
    	private static Connection getDBConnection() {
     
    		Connection dbConnection = null;
     
    		try {
     
    			Class.forName(DB_DRIVER);
     
    		} catch (ClassNotFoundException e) {
     
    			System.out.println(e.getMessage());
     
    		}
     
    		try {
     
    			dbConnection = DriverManager.getConnection(
                                 DB_CONNECTION, DB_USER,DB_PASSWORD);
    			return dbConnection;
     
    		} catch (SQLException e) {
     
    			System.out.println(e.getMessage());
     
    		}
     
    		return dbConnection;
     
    	}
     
     
     
    	public static String[] getNomsColonnes(ResultSet resultat) throws SQLException{
    		   ResultSetMetaData metadata = resultat.getMetaData();
    		   String[] noms = new String[metadata.getColumnCount()];
    		   for(int i = 0; i < noms.length; i++){
    		      String nomColonne = metadata.getColumnName(i+1);
    		      noms[i] = nomColonne;
    		   }
    		   return noms;
    		}
     
     
     
     
    	//end create_excel
    }
    and the ecrire_excel class :

    Java Code:
    package export_data;
     
    import java.io.File;
    import java.io.IOException;
     
    import jxl.Workbook;
    import jxl.write.Label;
    import jxl.write.Number;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;
    import jxl.write.biff.RowsExceededException;
     
    public class ecrire_excel {
    	WritableWorkbook workbook = null;
    	public void create_file (String path) {
     
    	try {
     
    		workbook = Workbook.createWorkbook(new File(path));
     
     
    	} 
    	catch (IOException e) {
    		e.printStackTrace();
    	} 
     
    	}
     
    	public void write_file (int nbcol,String chaine,int i,int j){
     
    WritableSheet sheet = workbook.createSheet("test", 0); 
     
    System.out.println("chaine i  " + i);
    System.out.println("chaine j  " + j);
    System.out.println("chaine    " + chaine);
     
    try
    {
     
     
    			Label var = new Label(j-1,i,chaine);
    			sheet.addCell(var); 
     
    		workbook.write(); 		
    		}
    catch (WriteException e) {
    	e.printStackTrace();
    } catch (IOException e) {
    	// TODO Auto-generated catch block
    	e.printStackTrace();
    }
     
    	}
     
     
    	public void ferme(){
    		try {
    			workbook.close();
    		} 
    		catch (WriteException e) {
    			e.printStackTrace();
    		} 
    		catch (IOException e) {
    			e.printStackTrace();
    		} 
    	}
    }
    I don't understand why only excel cell A2 contains a value.

    Thanks for helping

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default Re: export data from oracle to excel

    Stick some debugging in there to see what values are being passed around, specifically in the result set loop.
    Don't just rely on what you think should be coming back from the database...actually log the values (simple println() calls will do).
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    gimbal2 is online now Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,899
    Rep Power
    5

    Default Re: export data from oracle to excel

    Quote Originally Posted by Tolls View Post
    Don't just rely on what you think should be coming back from the database...actually log the values (simple println() calls will do).
    Measuring = knowing :)
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  4. #4
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,447
    Blog Entries
    7
    Rep Power
    20

    Default Re: export data from oracle to excel

    Quote Originally Posted by gimbal2 View Post
    Measuring = knowing :)
    An old saying goes around here in the physics test labs (TNO): 'measuring is knowing and testing is ruining' ;-)

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

Similar Threads

  1. Error by export Data from Excel File to MySql database
    By vector_ever in forum Apache POI
    Replies: 6
    Last Post: 08-01-2013, 11:15 PM
  2. Saving time export data to excel
    By nhoc_excel in forum JavaServer Pages (JSP) and JSTL
    Replies: 1
    Last Post: 06-23-2011, 09:54 AM
  3. Export to excel
    By nehaa in forum AWT / Swing
    Replies: 1
    Last Post: 04-30-2009, 02:44 PM
  4. Replies: 3
    Last Post: 08-05-2008, 03:19 AM
  5. export to excel
    By diana glz in forum Advanced Java
    Replies: 0
    Last Post: 03-13-2008, 11:01 PM

Posting Permissions

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