Results 1 to 3 of 3
- 03-11-2010, 12:01 PM #1
Member
- Join Date
- Mar 2010
- Posts
- 1
- Rep Power
- 0
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 }
Moderator Edit: Code tags addedLast edited by Fubarable; 03-13-2010 at 07:12 PM. Reason: Moderator Edit: Code tags added
- 03-13-2010, 07:07 PM #2
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.
- 03-16-2010, 09:24 PM #3
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
-
How To Add More Rows To Excel Sheet In Java
By JMartins in forum New To JavaReplies: 0Last Post: 12-31-2009, 12:30 AM -
Counting Rows and Columns from Excel Sheet
By Anish in forum Advanced JavaReplies: 3Last Post: 12-24-2008, 07:56 AM -
compare newly added Vector Element with previous elements
By nidhirastogi in forum Advanced JavaReplies: 10Last Post: 09-10-2008, 02:32 AM -
Iterating through ArrayList - using newly introduce for loop in Java 5.0
By Java Tip in forum Java TipReplies: 0Last Post: 11-14-2007, 04:22 PM -
Preventing inserted text from becoming colored from previous style
By jkhoa in forum AWT / SwingReplies: 2Last Post: 08-10-2007, 01:36 AM
Bookmarks