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);
}
}
}
