Schedule a Task(Upload Excel Data To Database) in a Specified Time Period
Get the source code form this path DOwnload Code
CallSchduler.java
//Main Class
package Scheduler; import java.io.*; import java.util.*; public class CallSchduler { UpdateSchduler upObj = null; public void stopScheduler() { upObj.cancelSchedule(); } public static void main(String args[]) { // System.out.println("Scheduler is called by main method"); try { File propertyFile = new File("D:\\Demo File\\Resources.properties"); if (propertyFile.exists()) { Properties properties = new Properties(); FileInputStream fileInputStream = new FileInputStream(propertyFile); properties.load(fileInputStream); int minutes = Integer.parseInt(properties.getProperty("Minutes")); String uploadFilePath = properties.getProperty("myExcelPath").toString(); String failedRecordsPath = properties.getProperty("failedFiles").toString(); UpdateSchduler upObj = new UpdateSchduler(minutes); upObj.updateFile(uploadFilePath, failedRecordsPath); } else { System.out.println("File not found!"); } } catch (Exception ex) { System.out.println("in Exception: " + ex.getMessage()); } } } UpdateSchduler.java package Scheduler; import java.io.File; import java.io.FileInputStream; import java.util.Properties; import java.util.Timer; import java.util.TimerTask; public class UpdateSchduler { private final Timer timer = new Timer(); private final int minutes; private String uploadFilePath = ""; private String FailedRecordsPath = ""; public UpdateSchduler(int minutes) { this.minutes = minutes; } public void start() { timer.schedule(new TimerTask() { public void run() { fileSelection(uploadFilePath, FailedRecordsPath); } }, 0, minutes * 60 * 1000); } public void cancelSchedule() { timer.cancel(); } public void updateFile(String uploadFilePath, String FailedRecordsPath) { this.uploadFilePath = uploadFilePath; this.FailedRecordsPath = FailedRecordsPath; start(); } public void stopUpdating() { cancelSchedule(); } public void fileSelection(String uploadRecordPath, String failedRecordsPath) { System.out.println("Upload File Path:" + uploadRecordPath); System.out.println("Failed Record File Path:" + failedRecordsPath); String filesInTheUplaodPath = null; File uplaodFile = new File(uploadRecordPath); if (uplaodFile.exists()) { if (uplaodFile.listFiles().length != 0) { File[] listOfFiles = uplaodFile.listFiles(); System.out.println("No. Files In the UPload Record path:" + listOfFiles.length); for (int i = 0; i < listOfFiles.length; i++) { if (listOfFiles[i].isFile()) { filesInTheUplaodPath = listOfFiles[i].getName(); //System.out.println("the files are " + files); JavaSheet readObj = new JavaSheet(); readObj.javaSheet(uploadRecordPath + filesInTheUplaodPath, failedRecordsPath + filesInTheUplaodPath); } } } else { System.out.println("No files Found"); } } else { System.out.println("File Path Not Found"); } } } JavaSheet .java package Scheduler; import java.sql.Statement; import java.io.DataInputStream; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.util.List; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import org.apache.commons.fileupload.FileItemFactory; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; /** * * @author Sravan */ public class JavaSheet { StringBuffer sb = new StringBuffer(); int count = 0; String EmpName = ""; String Dept = ""; String mobileNO = ""; String file_name = ""; String extension = ""; InputStream input = null; Connection con = null; FileItemFactory factory = new DiskFileItemFactory(); // System.out.println("1"); ServletFileUpload upload = new ServletFileUpload(factory); //System.out.println("2"); List items = null; CallableStatement insertQuery; DataInputStream in; private boolean isFileWrite = false; private FileInputStream fis; private File inputWorkbook; public void javaSheet(String file_name1, String failedFIle) { this.file_name = file_name1; sb.append("Employee Name" + ","); sb.append("Department" + ","); sb.append("MobileNO" + ","); sb.append("Reason" + ","); sb.append("\n"); extension = file_name.substring(file_name.indexOf("."), file_name.length()); System.out.println("" + extension); try { if (extension.equalsIgnoreCase(".xls") || extension.equalsIgnoreCase(".xlsx")) { inputWorkbook = new File(file_name); fis = new FileInputStream(inputWorkbook); Workbook w; ResultSet rs = null; w = Workbook.getWorkbook(fis); Sheet sheet = w.getSheet(0); String reason[] = new String[sheet.getRows() + 1]; String rowValues[] = new String[sheet.getColumns()]; System.out.println("no of rows :" + sheet.getRows()); if (sheet.getRows() > 1) { for (int row = 1; row < sheet.getRows(); row++) { for (int col = 0; col < sheet.getColumns(); col++) { Cell cell = sheet.getCell(col, row); rowValues[col] = cell.getContents(); } String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String connectionURL = "jdbc:sqlserver://Ip:portNo;databaseName=Dbname;user=sa;password=xyz"; Class.forName(driver); con = DriverManager.getConnection(connectionURL); reason[row] = ""; EmpName = rowValues[0]; Dept = rowValues[1]; mobileNO = rowValues[2]; if (EmpName == null || EmpName.trim().equals("")) { reason[row] = reason[row] + "Emp Name is Null"; } if (Dept == null || Dept.trim().equals("")) { reason[row] = reason[row] + "Department Value is Null"; } if (mobileNO == null || mobileNO.trim().equals("")) { reason[row] = reason[row] + "Mobile No is Null"; } if (reason[row] == null) { String insertQuery = "insert into EMPTable (EMP_Name ,EMP_Dept ,EMP_MobileNO) values ('" + EmpName + "','" + Dept + "','" + mobileNO + "')"; System.out.println(insertQuery); Statement stmt = con.createStatement(); int res = stmt.executeUpdate(insertQuery); stmt.close(); if (res == 0) { reason[row] = "Failed When Inserting"; } else { count++; System.out.println("rows inserted:" + count); } } else { System.out.println(" In Fail records"); // // sb.append("Employee Name" + ","); // sb.append("Department" + ","); // sb.append("MobileNO" + ","); // sb.append("Reason" + ","); // sb.append("\n"); //END isFileWrite = true; if (EmpName == null || EmpName.equals("")) { EmpName = " "; } if (Dept == null || Dept.equals("")) { Dept = " "; } if (mobileNO == null || mobileNO.equals("")) { mobileNO = " "; } sb.append(EmpName + ","); sb.append(Dept + ","); sb.append(mobileNO + ","); sb.append(reason[row] + ","); sb.append("\n"); } System.out.println("ZZZZZZZZZ" + isFileWrite); System.out.println("" + sb.length()); System.out.println("" + sb); if (isFileWrite || sb.length() != 0) { new FailedRecords().getFialedRecords(failedFIle, sb.toString()); } } System.out.println("END"); } else { System.out.println(" Empty XLS File"); } } else { System.out.println(" Invalid XLS File"); } } catch (Exception e) { System.out.println("Pls Select The Given format of xl file" + e.getMessage()); e.printStackTrace(); } finally { try { if (insertQuery != null) { insertQuery.close(); } if (con != null) { con.close(); } if (fis != null) { fis.close(); inputWorkbook.delete(); } } catch (Exception e) { System.out.println("EXCEPTION IN CLOSING CONNECTION:" + e); } } System.out.println("END"); } } FailedRecords.java package Scheduler; import java.io.BufferedWriter; import java.io.File; import java.io.FileWriter; import java.text.SimpleDateFormat; /** * * @author Sravan */ public class FailedRecords { public void getFialedRecords(String failedRecordsFile, String FailedRecords) { System.out.println("Inisde Fialed Records"); String file_name = failedRecordsFile; File file = new File(file_name); System.out.println("Filename::::" + file_name); String buffer = FailedRecords; String date = ""; String filename = ""; SimpleDateFormat df1 = new SimpleDateFormat("dd-MMM-yyyy-kk.mm.ss"); date = df1.format(new java.util.Date()); try { BufferedWriter out = new BufferedWriter(new FileWriter(file)); out.write(buffer); out.close(); } catch (Exception e) { System.out.println("Ex:" + e); } } }
Great collection.
ReplyDeleteI hope to see these kind of stuff more often from you.
One suggestion to you is, format the code before posting so that we can understand it easily...