java - Writing excel row data to sql server table, same row is getting inserted every time -
i have , excel sheet, wherein have few rows have written sql server table. here code..
i perform few conversions before insertion, like, converting dates, etc. every time, same row getting inserted. if check output row number in loop, correct result, same row inserted every time. don't know going wrong. after insert, want remove row , write file. but, not able write file. file size increasing, no rows copied file.
public void runit() throws exception { boolean success; success = false; try { file folder = new file("c:\\dcin_log\\dcp_dcl_mfg_order_status\\sql data base dcp_2015\\sql database dcp.xls"); if(!(folder.exists())) joptionpane.showmessagedialog(null, "input file not found"); else { statement stat = con.createstatement(); fileinputstream fs = new fileinputstream(folder); hssfworkbook wb = new hssfworkbook(fs); hssfsheet sheet = wb.getsheetat(0); int rows = sheet.getphysicalnumberofrows(); system.out.println("rows : " + rows); int coln = 0, coln1 = 0, coln2 = 0, coln3 = 0, coln4 = 0, coln5 = 0, coln6 = 0, coln7 = 0, coln8 = 0, coln9 = 0, coln10 = 0, coln11 = 0, coln12 = 0, coln13 = 0; hssfrow rown = sheet.getrow(0); for(int c = 1; c < 15; c++) { hssfcell celln = rown.getcell((short) c, rown.create_null_as_blank); if(("sold-to pt").equals(celln.tostring().trim())) { coln = celln.getcolumnindex(); } if(("customer").equals(celln.tostring().trim())) { coln1 = celln.getcolumnindex(); } if(("purchase order no.").equals(celln.tostring().trim())) { coln2 = celln.getcolumnindex(); } if(("po date").equals(celln.tostring().trim())) { coln3 = celln.getcolumnindex(); } if(("so no.").equals(celln.tostring().trim())) { coln4 = celln.getcolumnindex(); } if(("so dtd.").equals(celln.tostring().trim())) { coln5 = celln.getcolumnindex(); } if(("line item").equals(celln.tostring().trim())) { coln6 = celln.getcolumnindex(); } if(("part no.").equals(celln.tostring().trim())) { coln7 = celln.getcolumnindex(); } if(("description").equals(celln.tostring().trim())) { coln8 = celln.getcolumnindex(); } if(("order qty. ").equals(celln.tostring().trim())) { coln9 = celln.getcolumnindex(); } if(("zone").equals(celln.tostring().trim())) { coln10 = celln.getcolumnindex(); } if(("kam").equals(celln.tostring().trim())) { coln11 = celln.getcolumnindex(); } if(("customer request dt:").equals(celln.tostring().trim())) { coln12 = celln.getcolumnindex(); } if(("delivery -full/partial").equals(celln.tostring().trim())) { coln13 = celln.getcolumnindex(); } } for(int r = 1; r < rows; r++) { hssfrow row = sheet.getrow(r); system.out.println("r in loop : " + r); if(row != null) { int k = 0; hssfcell cell = row.getcell((short) 1); hssfcell cell1 = row.getcell((short) 2); hssfcell cell2 = row.getcell((short) 3); hssfcell cell3 = row.getcell((short) 4); hssfcell cell4 = row.getcell((short) 5); hssfcell cell5 = row.getcell((short) 6); hssfcell cell6 = row.getcell((short) 7); hssfcell cell7 = row.getcell((short) 8); hssfcell cell8 = row.getcell((short) 9); hssfcell cell9 = row.getcell((short) 10); hssfcell cell10 = row.getcell((short) 11); hssfcell cell11 = row.getcell((short) 12); hssfcell cell12 = row.getcell((short) 13); hssfcell cell13 = row.getcell((short) 14); statement st = con.createstatement(); string rt = ""; resultset rs = st.executequery("select routingtime dcp.dbo.routing_time partdesc = '"+cell8.tostring().trim()+"'"); while(rs.next()) { rt = rs.getstring("routingtime"); system.out.println("routingtime : " + rt); } rs.close(); st.close(); string val = "", val1 = "", val2 = "", val3 = ""; val = cell3.tostring().trim(); val1 = cell5.tostring().trim(); val2 = cell12.tostring().trim(); simpledateformat sdf = new simpledateformat("dd-mmm-yyyy"); simpledateformat sdf1 = new simpledateformat("yyyy-mm-dd hh:mm:ss.s"); calendar cal = calendar.getinstance(); calendar cal1 = calendar.getinstance(); calendar cal2 = calendar.getinstance(); calendar cal3 = calendar.getinstance(); calendar cal4 = calendar.getinstance(); val3 = sdf.format(cal4.gettime()); cal4.settime(sdf.parse(val3)); val3 = sdf1.format(cal4.gettime()); system.out.println("current date : " + val3); cal1.settime(sdf.parse(val)); val = sdf1.format(cal1.gettime()); system.out.println("val : " + val); cal2.settime(sdf.parse(val1)); val1 = sdf1.format(cal2.gettime()); system.out.println("val1 : " + val1); cal3.settime(sdf.parse(val2)); val2 = sdf1.format(cal3.gettime()); system.out.println("val2 : " + val2); string = cell13.tostring().trim(); if(("full").equals(together.tolowercase().trim())) = "yes"; else = "no"; int index = cell4.tostring().trim().indexof("."); string sonum = cell4.tostring().trim().substring(0, index).trim(); int index1 = cell.tostring().trim().indexof("."); string stp = cell.tostring().trim().substring(0, index1).trim(); int index2 = cell6.tostring().trim().indexof("."); string lnum = cell6.tostring().trim().substring(0, index2).trim(); int index3 = cell9.tostring().trim().indexof("."); string qty = cell9.tostring().trim().substring(0, index3).trim(); k = stat.executeupdate("insert dcp.dbo.dcpdate(po, podate, soldtoparty, cust, createdby, sono, sodate, sordate, lineitemno, partno, partdesc, qty, customerrequestdate, routingtime, salesp, zone, together) values ('"+cell2.tostring().trim()+"', '"+val.trim()+"', '"+stp.trim()+"', '"+cell1.tostring().trim()+"', '"+cell11.tostring().trim()+"', '"+sonum.trim()+"', '"+val1.trim()+"', '"+val3.trim()+"', '"+lnum.trim()+"', '"+cell7.tostring().trim()+"', '"+cell8.tostring().trim()+"', '"+qty.trim()+"', '"+val2.trim()+"', '"+rt.trim()+"', '"+cell11.tostring().trim()+"', '"+cell10.tostring().trim()+"', '"+together.trim()+"')"); if(k > 0) { system.out.println("data inserted"); hssfworkbook workbook = new hssfworkbook(new fileinputstream("c:\\dcin_log\\dcp_dcl_mfg_order_status\\sql data base dcp_2015\\sql database dcp.xls")); hssfsheet sheet1 = workbook.getsheet("sheet1"); file folder1 = new file("c:\\dcin_log\\dcp_dcl_mfg_order_status\\sql data base dcp_2015\\output.xls"); final string filename1 = folder1.getabsolutepath(); fileinputstream myinput1 = new fileinputstream(filename1); poifsfilesystem myfilesystem1 = new poifsfilesystem(myinput1); hssfworkbook myworkbook1 = new hssfworkbook(myfilesystem1); hssfsheet mysheet1 = myworkbook1.getsheetat(0); int lastr = mysheet1.getlastrownum(); copyrow(wb, sheet, r, lastr); fileoutputstream out = new fileoutputstream("c:\\dcin_log\\dcp_dcl_mfg_order_status\\sql data base dcp_2015\\output.xls", true); workbook.write(out); out.close(); } else system.out.println("data not inserted"); system.out.println("r after insertion: " + r); } system.out.println("r after 1 iteration: " + r); } success = true; stat.close(); } } catch(exception e) { e.printstacktrace(); success = false; } system.out.println("success : " + success); } public void copyrow(hssfworkbook workbook, hssfsheet worksheet, int sourcerownum, int destinationrownum) { // source / new row hssfrow newrow = worksheet.getrow(destinationrownum); hssfrow sourcerow = worksheet.getrow(sourcerownum); // if row exist in destination, push down rows 1 else create new row if(newrow != null) worksheet.shiftrows(destinationrownum, worksheet.getlastrownum(), 1); else newrow = worksheet.createrow(destinationrownum); // loop through source columns add new row for(int = 0; < sourcerow.getlastcellnum(); i++) { // grab copy of old / new cell hssfcell oldcell = sourcerow.getcell(i); hssfcell newcell = newrow.createcell(i); // if old cell null jump next cell if(oldcell == null) { newcell = null; continue; } // copy style old cell , apply new cell hssfcellstyle newcellstyle = workbook.createcellstyle(); newcellstyle.clonestylefrom(oldcell.getcellstyle()); ; newcell.setcellstyle(newcellstyle); // if there cell comment, copy if(oldcell.getcellcomment() != null) newcell.setcellcomment(oldcell.getcellcomment()); // if there cell hyperlink, copy if(oldcell.gethyperlink() != null) newcell.sethyperlink(oldcell.gethyperlink()); // set cell data type newcell.setcelltype(oldcell.getcelltype()); // set cell data value switch(oldcell.getcelltype()) { case cell.cell_type_blank: newcell.setcellvalue(oldcell.getstringcellvalue()); break; case cell.cell_type_boolean: newcell.setcellvalue(oldcell.getbooleancellvalue()); break; case cell.cell_type_error: newcell.setcellerrorvalue(oldcell.geterrorcellvalue()); break; case cell.cell_type_formula: newcell.setcellformula(oldcell.getcellformula()); break; case cell.cell_type_numeric: newcell.setcellvalue(oldcell.getnumericcellvalue()); break; case cell.cell_type_string: newcell.setcellvalue(oldcell.getrichstringcellvalue()); break; } } // if there are merged regions in source row, copy new row for(int = 0; < worksheet.getnummergedregions(); i++) { cellrangeaddress cellrangeaddress = worksheet.getmergedregion(i); if(cellrangeaddress.getfirstrow() == sourcerow.getrownum()) { cellrangeaddress newcellrangeaddress = new cellrangeaddress(newrow.getrownum(), (newrow.getrownum() + (cellrangeaddress.getlastrow() - cellrangeaddress.getfirstrow())), cellrangeaddress.getfirstcolumn(), cellrangeaddress.getlastcolumn()); worksheet.addmergedregion(newcellrangeaddress); } } }
sorry... silly mistake, did using prepared statement , worked
Comments
Post a Comment