DISQUS

anu · 5 months ago

package xl;

import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
import java.util.Properties;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.activation.DataHandler;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.WritableCellFormat;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class ReadWriteSheet {



public void createlogfile(String message){

try {
String name=this.readProperty("OUTPUT_FOLDER")+"//"+this.getCurrentDate("MMMyyyy", Calendar.getInstance().getTime())+".log";
FileWriter fstream = new FileWriter(name,true);
BufferedWriter out1 = new BufferedWriter(fstream);
out1.write(message);
out1.newLine();
//Close the output stream
out1.close();

}
catch(Exception e){//Catch exception if any
System.err.println("Error: " + e.getMessage());
}
}


public String readProperty(String strPropName)

{

// create an instance of properties class

String strValue = "";

Properties props = new Properties();

// try retrieve data from file

try {

File f = new File(System.getProperty("user.dir") + "\\xl\\"
+ "message.properties");
props.load(new FileInputStream(f));

strValue = props.getProperty(strPropName);

}

// catch exception in case properties file does not exist

catch (IOException e)

{

e.printStackTrace();

}

return strValue;

}

private static int searchRow(Sheet s, int from) {
try {
for (int i = from; i < s.getRows(); i++) {
Cell c = s.getCell(0, i);
if (c.getType() == CellType.LABEL) {
LabelCell l = (LabelCell) c;
if ("Supply Point Id".equals(l.getString()))
return i - 1;
}
}
} catch (Exception e) {
e.printStackTrace(new PrintStream(System.out));
}
return -1;
}



public void createZipFile(File f, ZipOutputStream out) {

final int BUFFER = 2048;


try {

BufferedInputStream origin = null;
byte data[] = new byte[BUFFER];

File [] files = f.listFiles();
for (int i = 0; i < files.length; i++) {
if(files[i].isDirectory()){
createZipFile(files[i], out);
continue;
}
if(!files[i].toString().contains(".xls"))
continue;
System.out.println("Adding: "+files[i]);

FileInputStream fi = new FileInputStream(files[i]);

origin = new BufferedInputStream(fi, BUFFER);
System.out.println(files[i].toString());

ZipEntry entry = new ZipEntry(files[i].toString());

out.putNextEntry(entry);

int count;

while ((count = origin.read(data, 0, BUFFER)) != -1) {
System.out.println(data);
System.out.println(count);
out.write(data, 0, count);

}

origin.close();
System.out.println(files[i]);
this.createlogfile("zipped the file "+files[i]+ " at" +this.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime()));


}



} catch (Exception e) {

e.printStackTrace();

}


}


private void createNewWorkBook(Sheet pSrcSheet, int pStartRow,
String pSiDateTime, String pOutPath, int intIndex, File fileName2) throws Exception {

LabelCell l = (LabelCell) pSrcSheet.getCell(0, pStartRow);
String ShipperCode = l.getString();
String filename = getFileName(ShipperCode, pSiDateTime,intIndex,fileName2);

//WorkbookSettings ws = new WorkbookSettings();
//ws.setLocale(new Locale("en", "EN"));
//WritableWorkbook workbook = Workbook.createWorkbook(new File(pOutPath,
// filename), ws);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
FileOutputStream fileOut = new FileOutputStream(new File(pOutPath,filename));
HSSFCellStyle cs = wb.createCellStyle();
HSSFCellStyle cs2 = wb.createCellStyle();
HSSFFont f = wb.createFont();

f.setColor( HSSFColor.BLACK.index );
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cs.setFont(f);
cs2.setFont(f);
cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cs2.setFillForegroundColor(HSSFColor.CORNFLOWER_BLUE.index);
//cs.setDataFormat(df.getFormat("#,##0.0"));





//WritableSheet dstSheet = workbook.createSheet("Sheet1", 0);
int dstRow = 0, srcRow = pStartRow;

while (true) {

Cell c = pSrcSheet.getCell(0, srcRow);
if (c.getType() == CellType.EMPTY) {
break;
}
//copyRow(pSrcSheet, srcRow, dstSheet, dstRow);
copyRow2(pSrcSheet, srcRow, sheet, dstRow,cs);
srcRow++;
dstRow++;
}
HSSFRow row2 = sheet.getRow(1);
int abc = row2.getLastCellNum();
for(int i = 0; i<abc; i++){
row2.getCell(i).setCellStyle(cs2);
}
wb.write(fileOut);
fileOut.close();
String strOutFolderName = this.readProperty("OUTPUT_FOLDER");
this.createlogfile("created file "+filename+"at "+this.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime()));
this.sendEmail(strOutFolderName+"\\"+filename,filename,ShipperCode);
//workbook.write();
//workbook.close();
}



@SuppressWarnings("deprecation")
private static void copyRow2(Sheet srcSheet, int startRow,
HSSFSheet dstSheet, int dstRow, HSSFCellStyle cs) throws Exception {
HSSFRow row = dstSheet.createRow((short)dstRow);
HSSFRow row1 = dstSheet.getRow(0);


int count=-1;
Cell[] cells = srcSheet.getRow(startRow);

for (Cell c : cells) {
if (c.getType() == CellType.LABEL) {
LabelCell l = (LabelCell) c;
@SuppressWarnings("unused")
WritableCellFormat f = new WritableCellFormat(c.getCellFormat());
//dstSheet.addCell(new Label(c.getColumn(), dstRow,
// l.getString(), f));
row.createCell(++count).setCellValue(l.getContents());


}
if (c.getType() == CellType.DATE) {
DateCell dt = (DateCell) c;
@SuppressWarnings("unused")
WritableCellFormat f = new WritableCellFormat(c.getCellFormat());
// dstSheet.addCell(new DateTime(c.getColumn(), dstRow, dt
// .getDate(), f));
row.createCell(++count).setCellValue(dt.getDate());
}
}
row1.getCell(0).setCellStyle(cs);

}
public static void main(String[] args) throws IOException {
ReadWriteSheet rws = new ReadWriteSheet();
//String files1[] = { "Supply point ids.xls" };
try {
rws.createlogfile("started process at "+ rws.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime()));
String strInpFolderName = rws.readProperty("INPUT_FOLDER");
String strOutFolderName = rws.readProperty("OUTPUT_FOLDER");
File f = new File(strInpFolderName); // here pass the directory

File files[] = f.listFiles(); // this will give you a list of excel
// files in the directory
Calendar cal = Calendar.getInstance();
Date d = cal.getTime();
String fileName = rws.getCurrentDate("ddMMyyyy", d)+".zip";
FileOutputStream dest = null;
try {
dest = new FileOutputStream(strOutFolderName
+ "\\" + fileName);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

ZipOutputStream out = new ZipOutputStream(dest);
for (int i = 0; i < files.length; i++) {
if(!files[i].toString().contains("."))
continue;
//for (String file : files)
rws.processWorkBook(files[i].toString(), "20090601", strOutFolderName,i);
}
rws.createZipFile(f, out);
out.close();
rws.deleteExcelFiles(f);



} catch (RuntimeException ex) {
ex.printStackTrace(new PrintStream(System.out));
}
}


private void deleteExcelFiles(File f) {
File [] filelist = f.listFiles();
for(int i=0;i<filelist.length;i++){
if(filelist[i].isDirectory()){
deleteExcelFiles(filelist[i]);
continue;
}
if(!filelist[i].toString().contains(".xls"))
continue;
filelist[i].delete();
this.createlogfile("deleted files" + filelist[i] + "at"+this.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime())+"\n");


}

}


/**
* Creates multiple excel files based on shipper code in input file
*
* @param pFileName
* input file name
* @param pSiDateTime
* BO variable
* @param pOutPath
* Output directory name to store generated files
* @param i
*/
public void processWorkBook(String pFileName, String pSiDateTime,
String pOutPath, int intIndex) {
try {
String outPath = ".";
if (pOutPath != null) {
outPath = pOutPath;
}
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));

Workbook workbook = Workbook.getWorkbook(new File(pFileName), ws);
Sheet s = workbook.getSheet(0);
int start = 0;

while ((start = searchRow(s, start + 2)) != -1) {
createNewWorkBook(s, start, pSiDateTime, outPath,intIndex,new File(pFileName));
}
workbook.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}

private String getCurrentDate(String format, Date d) {
Format formatter = new SimpleDateFormat(format);
return formatter.format(d);
}

private String getFileName(String ShipperCode, String siDateTime,
int intIndex, File fileName2) {

String fileName = "IP026.PN_%Parent File%_%Shipper Short Code%_%SI_DATETIME(Bo variable)%.xls";
String strInputFileName = fileName2.getName();
String strDay = new String(strInputFileName.substring(strInputFileName.indexOf("days")-2,strInputFileName.indexOf("days")));
String strParentFile = strInputFileName.substring(0,strInputFileName.indexOf("queries"));
if(strDay.equalsIgnoreCase("56")){
fileName = fileName.replace("IP026.PN_%","IP027.PN_%");
}else if(strDay.equalsIgnoreCase("70")){
fileName = fileName.replace("IP026.PN_%","IP028.PN_%");
}
fileName = fileName.replace("%Parent File%", strParentFile);
fileName = fileName.replace("%Shipper Short Code%", ShipperCode);
Calendar cal = Calendar.getInstance();
cal.add(Calendar.MONTH, -0);
Date d = cal.getTime();
String date1=getCurrentDate("yyyy-MM-dd",d);
String time1= getCurrentDate("hh-mm-ss",d);
fileName = fileName.replace("%SI_DATETIME(Bo variable)%",date1+"-"+time1);
fileName = fileName.replace(".xls",".xls");
return fileName;
}

@SuppressWarnings("unchecked")
public void sendEmail(String filename,String subject, String shipperCode) throws SQLException {

String from = "xoserve.ipwdevtandtest@xoserve.com";

String bodyText = "This is a important message with attachment"+"\n"+"Please do not reply to this mail as it is a system generated mail"+"\n"+"Incase of attachment problems please contact BO admin at rajendra.chandrashekar@tcs.com";
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String serverName = this.readProperty("SERVERNAME");
int port =Integer.valueOf( this.readProperty("PORT"));
String user = this .readProperty("USER");
String password = this.readProperty("PASSWORD");
String SID = this.readProperty("SID");
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
Connection conn = DriverManager.getConnection(URL, user, password);

PreparedStatement stat = conn.prepareStatement("SELECT EMAIL_ID FROM SHIPPER_EMAIL WHERE SHIPPER_SHORT_CODE =?");
stat.setString(1, shipperCode);
ResultSet rs = stat.executeQuery();




Properties properties = new Properties();
properties.put("mail.smtp.host", "10.140.84.69");
properties.put("mail.smtp.port", "25");
Session session = Session.getInstance( properties, null);

try {
ArrayList tolist=new ArrayList();
while(rs.next()){
tolist.add(rs.getString(1));
}

//String[] mail = (String[]) tolist.toArray();

MimeMessage message = new MimeMessage(session);
message.setFrom(new InternetAddress(from));

InternetAddress[] addressTo = new InternetAddress[tolist.size()];
for (int i = 0; i < tolist.size(); i++) {
addressTo[i] = new InternetAddress((String) tolist.get(i));
}
message.setRecipients(Message.RecipientType.TO, addressTo);
message.setSubject(subject);
message.setSentDate(new Date());

//
// Set the email message text.
//
MimeBodyPart messagePart = new MimeBodyPart();
messagePart.setText(bodyText);

//
// Set the email attachment file
//
MimeBodyPart attachmentPart = new MimeBodyPart();
FileDataSource fileDataSource = new FileDataSource(filename) {
@Override
public String getContentType() {
return "application/octet-stream";
}
};
attachmentPart.setDataHandler(new DataHandler(fileDataSource));
attachmentPart.setFileName(subject);

Multipart multipart = new MimeMultipart();
multipart.addBodyPart(messagePart);
multipart.addBodyPart(attachmentPart);

message.setContent(multipart);

Transport.send(message);
this.createlogfile("sent file "+subject+" to "+ tolist +" from "+from+" at "+this.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime())+"\n");


}catch (MessagingException e) {
e.printStackTrace();
}

}


}

anu · 5 months ago

package xl;

import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
import java.util.Properties;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.activation.DataHandler;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.WritableCellFormat;
import jxl.read.biff.*;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class ReadWriteSheet {



/**
* creates the log file with the name as the mmmyyyy.log
* @param message
*/
public void createlogfile(String message){

try {
String name=this.readProperty("OUTPUT_FOLDER")+"\\"+this.getCurrentDate("MMMyyyy", Calendar.getInstance().getTime())+".log";
FileWriter fstream = new FileWriter(name,true);
BufferedWriter out1 = new BufferedWriter(fstream);
out1.write(message);
out1.newLine();
//Close the output stream
out1.close();

}
catch(Exception e){//Catch exception if any
System.err.println("Error: " + e.getMessage());
}
}


/**
*
* @param strPropName
* @return
*/
public String readProperty(String strPropName)

{

// create an instance of properties class

String strValue = "";

Properties props = new Properties();

// try retrieve data from file

try {

File f = new File(System.getProperty("user.dir") + "\\xl\\"
+ "message.properties");
props.load(new FileInputStream(f));

strValue = props.getProperty(strPropName);

}

// catch exception in case properties file does not exist

catch (IOException e)

{

e.printStackTrace();

}

return strValue;

}

/**
* @param s
* @param from
* @return
*/
/*private static int searchRow(Sheet s, int from) {
try {
for (int i = from; i < s.getRows(); i++) {
Cell c = s.getCell(0, i);
if (c.getType() == CellType.LABEL) {
LabelCell l = (LabelCell) c;
if ("Supply Point Id".equals(l.getString()))
return i - 1;
}
}
} catch (Exception e) {
e.printStackTrace(new PrintStream(System.out));
}
return -1;
}*/
private static int searchRow(Sheet s, int from) {
try {
for (int i = from; i < s.getRows(); i++) {
Cell c = s.getCell(0, i);
if (c.getType() == CellType.LABEL) {
LabelCell l = (LabelCell) c;
if ("Supply Point Id".equals(l.getString()))
return i - 1;
}
c = s.getCell(1, i);
if (c.getType() == CellType.LABEL) {
LabelCell l = (LabelCell) c;
if ("Supply Point Id".equals(l.getString()))
return i - 1;
}
}
} catch (Exception e) {
e.printStackTrace(new PrintStream(System.out));
}
return -1;
}




/**
* @param f
* @param out
*/
public void createZipFile(File f, ZipOutputStream out) {

final int BUFFER = 2048;


try {

BufferedInputStream origin = null;
byte data[] = new byte[BUFFER];

File [] files = f.listFiles();
for (int i = 0; i < files.length; i++) {
if(files[i].isDirectory()){
createZipFile(files[i], out);
continue;
}
if(!files[i].toString().contains(".xls"))
continue;
System.out.println("Adding: "+files[i]);

FileInputStream fi = new FileInputStream(files[i]);

origin = new BufferedInputStream(fi, BUFFER);
System.out.println(files[i].toString());

ZipEntry entry = new ZipEntry(files[i].toString());

out.putNextEntry(entry);

int count;

while ((count = origin.read(data, 0, BUFFER)) != -1) {
System.out.println(data);
System.out.println(count);
out.write(data, 0, count);

}

origin.close();
System.out.println(files[i]);
this.createlogfile("zipped the file "+files[i]+ " at" +this.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime()));


}



} catch (Exception e) {

e.printStackTrace();

}


}


/**
* @param pSrcSheet
* @param pStartRow
* @param pSiDateTime
* @param pOutPath
* @param from
* @param intIndex
* @param fileName2
* @throws Exception
*/
/*private void createNewWorkBook(Sheet pSrcSheet, int pStartRow,
String pSiDateTime, String pOutPath, int intIndex, File fileName2) throws Exception {

LabelCell l = (LabelCell) pSrcSheet.getCell(0, pStartRow);
String ShipperCode = l.getString();
String filename = getFileName(ShipperCode, pSiDateTime,intIndex,fileName2);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
FileOutputStream fileOut = new FileOutputStream(new File(pOutPath,filename));
HSSFCellStyle cs = wb.createCellStyle();
HSSFCellStyle cs1 = wb.createCellStyle();
HSSFCellStyle cs2 = wb.createCellStyle();
HSSFFont f = wb.createFont();
HSSFFont f1 = wb.createFont();
HSSFDataFormat form = wb.createDataFormat();
f1.setColor(HSSFColor.WHITE.index);
f1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
f.setColor( HSSFColor.BLACK.index );
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cs.setFont(f);
cs1.setDataFormat(form.getFormat(this.readProperty("FORMAT")));
cs2.setFont(f1);
cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cs2.setFillForegroundColor(HSSFColor.BLUE_GREY.index);

int dstRow = 0, srcRow = pStartRow;

while (true) {

Cell c = pSrcSheet.getCell(0, srcRow);
if (c.getType() == CellType.EMPTY) {
break;
}
copyRow2(pSrcSheet, srcRow, sheet, dstRow,cs,cs1);
srcRow++;
dstRow++;
}
HSSFRow row2 = sheet.getRow(1);
int lastcol = row2.getLastCellNum();
for(int i = 0; i<lastcol; i++){
row2.getCell(i).setCellStyle(cs2);
sheet.autoSizeColumn((short) i);

}
wb.write(fileOut);
fileOut.close();
String strOutFolderName = this.readProperty("OUTPUT_FOLDER");
this.createlogfile("created file "+filename+"at "+this.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime()));
this.sendEmail(strOutFolderName+"\\"+filename,filename,ShipperCode);
}*/
private void createNewWorkBook(Sheet pSrcSheet, int pStartRow,
String pSiDateTime, String pOutPath, int from) throws Exception {
LabelCell l = (LabelCell) pSrcSheet.getCell(0, pStartRow);
String ShipperCode = l.getString();
String filename = getFileName(ShipperCode, pSiDateTime);

// WorkbookSettings ws = new WorkbookSettings();
// ws.setLocale(new Locale("en", "EN"));
// WritableWorkbook workbook = Workbook.createWorkbook(new
// File(pOutPath,
// filename), ws);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
FileOutputStream fileOut = new FileOutputStream(new File(pOutPath,
filename));

// WritableSheet dstSheet = workbook.createSheet("Sheet1", 0);
int dstRow = 0, srcRow = pStartRow;

while (true) {
Cell c = pSrcSheet.getCell(0, srcRow);
Cell c2 = pSrcSheet.getCell(1, srcRow);
if (c.getType() == CellType.EMPTY && c2.getType() == CellType.EMPTY) {
break;
}
// copyRow(pSrcSheet, srcRow, dstSheet, dstRow);
copyRow2(pSrcSheet, srcRow, sheet, dstRow,from);
srcRow++;
dstRow++;
}
wb.write(fileOut);
fileOut.close();

// workbook.write();
// workbook.close();
}



/**
* @param srcSheet
* @param startRow
* @param dstSheet
* @param dstRow
* @param cs
* @param cs1
* @throws Exception
*/
@SuppressWarnings("deprecation")
/*private static void copyRow2(Sheet srcSheet, int startRow,
HSSFSheet dstSheet, int dstRow, HSSFCellStyle cs, HSSFCellStyle cs1) throws Exception {
HSSFRow row = dstSheet.createRow((short)dstRow);
HSSFRow row1 = dstSheet.getRow(0);


int count=-1;
Cell[] cells = srcSheet.getRow(startRow);

for (Cell c : cells) {
if (c.getType() == CellType.LABEL) {
LabelCell l = (LabelCell) c;
@SuppressWarnings("unused")
WritableCellFormat f = new WritableCellFormat(c.getCellFormat());
row.createCell(++count).setCellValue(l.getContents());



}
if (c.getType() == CellType.DATE) {
DateCell dt = (DateCell) c;
@SuppressWarnings("unused")
WritableCellFormat f = new WritableCellFormat(c.getCellFormat());
row.createCell(++count).setCellValue(dt.getDate());
row.getCell(count).setCellStyle(cs1);

}
}
row1.getCell(0).setCellStyle(cs);

}*/
private static void copyRow2(Sheet srcSheet, int startRow,
HSSFSheet dstSheet, int dstRow,int from) throws Exception {
HSSFRow row = dstSheet.createRow((short) dstRow);
int count = 0;
Cell[] cells = srcSheet.getRow(startRow);
for (Cell c : cells) {
if (c.getType() == CellType.LABEL) {
LabelCell l = (LabelCell) c;

WritableCellFormat f = new WritableCellFormat(c.getCellFormat());
// dstSheet.addCell(new Label(c.getColumn(), dstRow,
// l.getString(), f));
row.createCell(count++).setCellValue(l.getContents());
}
if (c.getType() == CellType.DATE) {
DateCell dt = (DateCell) c;
WritableCellFormat f = new WritableCellFormat(c.getCellFormat());
// dstSheet.addCell(new DateTime(c.getColumn(), dstRow, dt
// .getDate(), f));
row.createCell(count++).setCellValue(dt.getDate());
}
}
}


/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
ReadWriteSheet rws = new ReadWriteSheet();
try {
rws.createlogfile("started process at "+ rws.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime()));
String strInpFolderName = rws.readProperty("INPUT_FOLDER");
String strOutFolderName = rws.readProperty("OUTPUT_FOLDER");
File f = new File(strInpFolderName); // here pass the directory

File files[] = f.listFiles(); // this will give you a list of excel
// files in the directory
Calendar cal = Calendar.getInstance();
Date d = cal.getTime();
String fileName = rws.getCurrentDate("ddMMyyyy", d)+".zip";
FileOutputStream dest = null;
try {
dest = new FileOutputStream(strOutFolderName
+ "\\" + fileName);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

ZipOutputStream out = new ZipOutputStream(dest);
for (int i = 0; i < files.length; i++) {
if(!files[i].toString().contains("."))
continue;
rws.processWorkBook(files[i].toString(), "20090601", strOutFolderName);
}
rws.createZipFile(f, out);
out.close();
rws.deleteExcelFiles(f);



} catch (RuntimeException ex) {
ex.printStackTrace(new PrintStream(System.out));
}
}


/**
* @param f
*/
private void deleteExcelFiles(File f) {
File [] filelist = f.listFiles();
for(int i=0;i<filelist.length;i++){
if(filelist[i].isDirectory()){
deleteExcelFiles(filelist[i]);
continue;
}
if(!filelist[i].toString().contains(".xls"))
continue;
filelist[i].delete();
this.createlogfile("deleted files" + filelist[i] + "at"+this.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime())+"\n");


}

}


/**
* Creates multiple excel files based on shipper code in input file
*
* @param pFileName
* input file name
* @param pSiDateTime
* BO variable
* @param pOutPath
* Output directory name to store generated files
* @param i
*/
/*public void processWorkBook(String pFileName, String pSiDateTime,
String pOutPath, int intIndex) {
try {
String outPath = ".";
if (pOutPath != null) {
outPath = pOutPath;
}
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));

Workbook workbook = Workbook.getWorkbook(new File(pFileName), ws);
Sheet s = workbook.getSheet(0);
int start = 0;

while ((start = searchRow(s, start + 2)) != -1) {
createNewWorkBook(s, start, pSiDateTime, outPath,intIndex,new File(pFileName));
}
workbook.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}*/

public void processWorkBook(String pFileName, String pSiDateTime,
String pOutPath) {
try {
String outPath = ".";
if (pOutPath != null) {
outPath = pOutPath;
}
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));

Workbook workbook = Workbook.getWorkbook(new File(pFileName), ws);
Sheet s = workbook.getSheet(0);
int start = -2;

while ((start = searchRow(s, start + 2)) != -1) {
createNewWorkBook(s, start, pSiDateTime, outPath,start + 2);
}
workbook.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}

/**
* @param format
* @param d
* @return
*/
private String getCurrentDate(String format, Date d) {
Format formatter = new SimpleDateFormat(format);
return formatter.format(d);
}

/**
* @param ShipperCode
* @param siDateTime
* @param intIndex
* @param fileName2
* @return
*/
/*private String getFileName(String ShipperCode, String siDateTime,
int intIndex, File fileName2) {

String fileName = "IP026.PN_%Parent File%_%Shipper Short Code%_%SI_DATETIME(Bo variable)%.xls";
String strInputFileName = fileName2.getName();
String strDay = new String(strInputFileName.substring(strInputFileName.indexOf("days")-2,strInputFileName.indexOf("days")));
String strParentFile = strInputFileName.substring(0,strInputFileName.indexOf("queries"));
if(strDay.equalsIgnoreCase("56")){
fileName = fileName.replace("IP026.PN_%","IP027.PN_%");
}else if(strDay.equalsIgnoreCase("70")){
fileName = fileName.replace("IP026.PN_%","IP028.PN_%");
}
fileName = fileName.replace("%Parent File%", strParentFile);
fileName = fileName.replace("%Shipper Short Code%", ShipperCode);
Calendar cal = Calendar.getInstance();
cal.add(Calendar.MONTH, -0);
Date d = cal.getTime();
String date1=getCurrentDate("yyyy-MM-dd",d);
String time1= getCurrentDate("hh-mm-ss",d);
fileName = fileName.replace("%SI_DATETIME(Bo variable)%",date1+"-"+time1);
fileName = fileName.replace(".xls",".xls");
return fileName;
}*/
private String getFileName(String ShipperCode, String siDateTime) {
String fileName = "IP00092.PN_%Shipper Short Code%_ADZ%Billing Month in MMYY format%_%SI_DATETIME(Bo variable)%.xls";
fileName = fileName.replace("%Shipper Short Code%", ShipperCode);
Calendar cal = Calendar.getInstance();
cal.add(Calendar.MONTH, -1);
Date d = cal.getTime();
fileName = fileName.replace("%Billing Month in MMYY format%",
getCurrentDate("MMyy", d));
fileName = fileName.replace("%SI_DATETIME(Bo variable)%", siDateTime);
return fileName;
}

/**
* @param filename
* @param subject
* @param shipperCode
* @throws SQLException
*/
@SuppressWarnings("unchecked")
public void sendEmail(String filename,String subject, String shipperCode) throws SQLException {

String from = "xoserve.ipwdevtandtest@xoserve.com";

String bodyText = "This is a important message with attachment"+"\n"+"Please do not reply to this mail as it is a system generated mail"+"\n"+"Incase of attachment problems please contact BO admin at rajendra.chandrashekar@tcs.com";
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String serverName = this.readProperty("SERVERNAME");
int port =Integer.valueOf( this.readProperty("PORT"));
String user = this .readProperty("USER");
String password = this.readProperty("PASSWORD");
String SID = this.readProperty("SID");
String URL = "jdbc:oracle:thin:@" + serverName + ":" + port + ":" + SID;
Connection conn = DriverManager.getConnection(URL, user, password);

PreparedStatement stat = conn.prepareStatement("SELECT EMAIL_ID FROM SHIPPER_EMAIL WHERE SHIPPER_SHORT_CODE =?");
stat.setString(1, shipperCode);
ResultSet rs = stat.executeQuery();




Properties properties = new Properties();
properties.put("mail.smtp.host", "10.140.84.69");
properties.put("mail.smtp.port", "25");
Session session = Session.getInstance( properties, null);

try {
ArrayList tolist=new ArrayList();
while(rs.next()){
tolist.add(rs.getString(1));
}

//String[] mail = (String[]) tolist.toArray();

MimeMessage message = new MimeMessage(session);
message.setFrom(new InternetAddress(from));

InternetAddress[] addressTo = new InternetAddress[tolist.size()];
for (int i = 0; i < tolist.size(); i++) {
addressTo[i] = new InternetAddress((String) tolist.get(i));
}
message.setRecipients(Message.RecipientType.TO, addressTo);
message.setSubject(subject);
message.setSentDate(new Date());

//
// Set the email message text.
//
MimeBodyPart messagePart = new MimeBodyPart();
messagePart.setText(bodyText);

//
// Set the email attachment file
//
MimeBodyPart attachmentPart = new MimeBodyPart();
FileDataSource fileDataSource = new FileDataSource(filename) {
@Override
public String getContentType() {
return "application/octet-stream";
}
};
attachmentPart.setDataHandler(new DataHandler(fileDataSource));
attachmentPart.setFileName(subject);

Multipart multipart = new MimeMultipart();
multipart.addBodyPart(messagePart);
multipart.addBodyPart(attachmentPart);

message.setContent(multipart);

Transport.send(message);
this.createlogfile("sent file "+subject+" to "+ tolist +" from "+from+" at "+this.getCurrentDate("yyyy-MM-dd hh:mm:ss", Calendar.getInstance().getTime())+"\n");


}catch (MessagingException e) {
e.printStackTrace();
}

}


}