Results 1 to 3 of 3
  1. #1
    josephkarthic is offline Member
    Join Date
    Mar 2010
    Posts
    1
    Rep Power
    0

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

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

    Java Code:
    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
    Last edited by Fubarable; 03-13-2010 at 06:12 PM. Reason: Moderator Edit: Code tags added

  2. #2
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    5

    Default

    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.

  3. #3
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    5

    Default

    Quote Originally Posted by josephkarthic View Post
    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.

Similar Threads

  1. How To Add More Rows To Excel Sheet In Java
    By JMartins in forum New To Java
    Replies: 0
    Last Post: 12-30-2009, 11:30 PM
  2. Counting Rows and Columns from Excel Sheet
    By Anish in forum Advanced Java
    Replies: 3
    Last Post: 12-24-2008, 06:56 AM
  3. compare newly added Vector Element with previous elements
    By nidhirastogi in forum Advanced Java
    Replies: 10
    Last Post: 09-10-2008, 01:32 AM
  4. Replies: 0
    Last Post: 11-14-2007, 03:22 PM
  5. Replies: 2
    Last Post: 08-10-2007, 12:36 AM

Posting Permissions

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