How to insert data into excel file using java code?

[minal silimkar]

I wrote below code to fetch data from excel work sheet. It is working file.

    
import java.io.*;
import java.sql.*;

public class ExcelReadTest{
	public static void main(String[] args){    
		Connection connection = null;
   		try{
   			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   			Connection con = DriverManager.getConnection( "jdbc:odbc:exceltest" );
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" );
            	
            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();
            	
            while (rs.next()) {
   				for (int i = 1; i <= numberOfColumns; i++) {
   					if (i > 1) 
   						System.out.print(", ");
             		String columnValue = rs.getString(i);
                    System.out.print(columnValue);
   				}
                System.out.println("");	
            }
                    	
            st.close();
            con.close();
   		} catch(Exception ex) {
   			System.err.print("Exception: ");
            System.err.println(ex.getMessage());
   		}
	}
}                

I want to transfer data from one sheet to another, so I wrote following code.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class Write2Excel {
	public static void main(String[] args){
		Connection connection = null;
		String sql=null;
   		try{
   			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   			Connection con = DriverManager.getConnection( "jdbc:odbc:exceltest" );
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" );
            	
            Statement st2 = con.createStatement();
            
            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();
            	
            while (rs.next()) {
   				for (int i = 1; i <= numberOfColumns; i++) {
   					if (i > 1) 
   						System.out.print(", ");
             		String columnValue = rs.getString(i);
             		sql="insert into [Sheet2$] Values ("+columnValue+")";
             		st.executeUpdate(sql);
                    System.out.print(columnValue);
   				}
                System.out.println("");	
            }                    	
            st.close();
            con.close();
   		} catch(Exception ex) {
   			ex.printStackTrace();
   		}
	}
}

It is giving error at insert statement.
How to insert data into excel file using java code?

[Ulf Dittmer]

What is the error? Post the full error message.

I don' think that ODBC can be used to alter Excel files - it can only read them. Either find a different Excel JDBC driver that can do this (although I doubt such a driver exists), or use a library like jExcelApi or Apache POI to alter the file programmatically.

[Swastik Dey]

You are missing the single quotes in the query formation.

sql="insert into [Sheet2$] Values ("+columnValue+")";

should be

sql="insert into [Sheet2$] Values ('"+columnValue+"')";

and make sure the dsn has not been configured as readonly.

[Raj Java Raj]

Hey Swastik Dey !
Great !! It working after removing Read Only DSN !!
Thanks.!!