How to Update newly inserted rows in the Excel through Java..Pls help..Explained well

[josephkarthic]

Hi,

Im a Beginner in Java and ive written a code to Update an Excelsheet from Java through JDBC:ODBC driver without DSN, that is we can directly update the Excel file by giving its path..

Below is my Requirement Scenario..

I have an Excel sheet with

Col1 Col2
240029996077835 8946070700080068352
240029996077836 8946070700080068360
240029996077837 8946070700080068378
240029996077838 8946070700080068386

1)Now I should update Col1 with a new user input value such as 12345..as below..

Col1 Col2
12345 8946070700080068352
12345 8946070700080068360
12345 8946070700080068378
12345 8946070700080068386

2)After that i should copy the whole rows and append it below the last row as below..

Col1 Col2
12345 8946070700080068352
12345 8946070700080068360
12345 8946070700080068378
12345 8946070700080068386

12345 8946070700080068352
12345 8946070700080068360
12345 8946070700080068378
12345 8946070700080068386

3)And then update the newly appended rows with another new user input value like 678910 keeping the above 12345 values as such...Below is the illustration..

Col1 Col2
12345 8946070700080068352
12345 8946070700080068360
12345 8946070700080068378
12345 8946070700080068386

678910 8946070700080068352
678910 8946070700080068360
678910 8946070700080068378
678910 8946070700080068386

Now Im able to update complete the first two steps but the third one is bit complicated for me..Below is my code snippets..pls help guys..

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con ;

con = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Documents and Settings/195602/Desktop/karthic/CDR tool/Sample.xls;ReadOnly=0;");

Statement stat = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

String sql = "UPDATE [Data$] SET [Col1] ='"+textfield1.getText()+"'";

String sql2 = "INSERT INTO [Data$] Select * from [Data$]";

if(actionevent.getSource().equals(buttonUpdate))
{
stat.executeUpdate(sql);  //first step done here
}

if(actionevent.getSource().equals(buttonUpdate2))
{
stat.executeUpdate(sql2); //Second step done here
}

How to accomplish the third step ?? Pls help..:)

Moderator Edit: Code tags added

[j2me64]

hallo josephkartthic

for excel manipulations in java i normally use jxl.jar, see here [jxl-2.6.jar - JAR Search - findJAR.com in this jar-file you will find a lot of classes for manipulating xls. check it out.

[j2me64]

How to accomplish the third step ?? Pls help..:)

suppose you have this data in an sql database. after step 2 the rows are not distinct, so it is not possible to update one row, except you use a olap function that says "if current row is the second row of the two rows, then update only the row with the olap.number=2"

try to get a olap connector for excel to accomplish this operation.