Need to generate Excel file from Report Definition and save into shared location (it shoud not popup for download) and Send as mail also ?
Hi Friends,
Can any one help on my below requirements ?
1. I need to generate Excel file from Report Definition and save into shared location (it shoud not popup for download)
2. I need to mail this same generated Excel to user.
Thanks
***Updated by moderator: Lochan to add Categories***
Pegasystems Inc.
US
You cannot achieve this programmatically without the user's explicit permission to download generated Excel file into a location selected by the user, unless you develop specialized plugin, e.g. Silverlight control, to accomplish this.
The browser runs in a "sandbox" which precludes JavaScript from accessing any workstation resource, such as local or remote disk folder.
Hope this helps.
Pegasystems Inc.
SG
What does shared location mean? Do you mean a networked mapped location on the user's laptop/desktop? If so, why do you need to email it to the user?
My guess is that you want to generate an excel document from a report and simply send it as an email attachment. Where the file is created is not relevant? Is this understanding correct?
If the above assumption is correct, you are looking at combining two functions. One generate an excel document from a report. Two attach the document to an email before sending it out.
Refer to the OOTB activity that does an export of report data to excel on how to generate the excel document. Use the generated excel data in memory (this is the entire excel document) to create an attachment to the email before sending the email. Refer to SentEmailNotification on how to send email with an attachment.
Regards
Patrick
Cognizant
US
try the below code to place excel in shared drive make sure that you have to pass all the required params
-----
// Declare parameters
String HostName= tools.getParamValue("HostName");
String UserName = tools.getParamValue("UserName");
String Password = tools.getParamValue("Password");
String remotePath =tools.getParamValue("remotePath");
String localFile = tools.getParamValue("LocalFileName");
String fileName = (new java.io.File(localFile)).getName();
//Trim values
HostName=HostName.trim();UserName =UserName.trim();
Password =Password.trim();remotePath =remotePath.trim();
localFile =localFile.trim();
java.io.InputStream inputStream = null;
com.jcraft.jsch.JSch jsch = null;
com.jcraft.jsch.ChannelSftp sftpChannel=null;
com.jcraft.jsch.Session ftpSession=null;
try {
jsch = new com.jcraft.jsch.JSch();
oLog.debug("Getting jsch Session starts");
ftpSession = jsch.getSession(UserName,HostName,22);
ftpSession.setPassword(Password);
try the below code to place excel in shared drive make sure that you have to pass all the required params
-----
// Declare parameters
String HostName= tools.getParamValue("HostName");
String UserName = tools.getParamValue("UserName");
String Password = tools.getParamValue("Password");
String remotePath =tools.getParamValue("remotePath");
String localFile = tools.getParamValue("LocalFileName");
String fileName = (new java.io.File(localFile)).getName();
//Trim values
HostName=HostName.trim();UserName =UserName.trim();
Password =Password.trim();remotePath =remotePath.trim();
localFile =localFile.trim();
java.io.InputStream inputStream = null;
com.jcraft.jsch.JSch jsch = null;
com.jcraft.jsch.ChannelSftp sftpChannel=null;
com.jcraft.jsch.Session ftpSession=null;
try {
jsch = new com.jcraft.jsch.JSch();
oLog.debug("Getting jsch Session starts");
ftpSession = jsch.getSession(UserName,HostName,22);
ftpSession.setPassword(Password);
java.util.Properties config = new java.util.Properties();
config.put("StrictHostKeyChecking", "no");
ftpSession.setConfig(config);
ftpSession.connect();
oLog.debug("Getting jsch Session ends");
try {
sftpChannel = (com.jcraft.jsch.ChannelSftp) ftpSession.openChannel("sftp");
sftpChannel.connect();
sftpChannel.cd(remotePath);
java.io.File fileF=new java.io.File(localFile);
try {
inputStream = new java.io.FileInputStream(fileF);
sftpChannel.put(localFile,fileName);
}catch (java.io.FileNotFoundException exp)
{oLog.error(exp.toString());Error="true";}
}
catch (com.jcraft.jsch.SftpException e)
{oLog.error(e.toString());
}
}
catch (com.jcraft.jsch.JSchException e) {
oLog.error("SendFileBySFTP java : "+e.toString());
Error="true";
}
oLog.debug(" End SFTWorker ->getfileContent()");
if (sftpChannel != null && sftpChannel.isConnected()) {
sftpChannel.disconnect();
}
if (ftpSession != null && ftpSession.isConnected()) {
ftpSession.disconnect();
}
-------------------------
try this java code to generate the excel in backend and sending email. ( by passing .pyAccountName,.pyAccountType,CorrStream,CorrType and remaining required params )
String FileNameText = FileNametxt;
StringBuffer strData = new StringBuffer();
strData.append("<HTML><head><META HTTP-EQUIV=\"Content-Type\" CONTENT=\"application/vnd.ms-excel\"></head> <TABLE width='100%' border='1' colspan='7'><style>.excelText {vnd.ms-xls.numberformat:@;}.excelText{vnd.ms-excel.numberformat:@;}</style>");
ClipboardPage recPage = tools.findPage("FinalPage");
// To Create Header for Excel.
try{
strData.append("<TR>");
strData.append("<TD bgColor='#0000FF' ><B><FONT SIZE='2' COLOR='#FFFFFF'>Group Id</FONT><B></TD>");
strData.append("<TD bgColor='#0000FF' ><B><FONT SIZE='2' COLOR='#FFFFFF'>Group Name</FONT><B></TD>");
strData.append("<TD bgColor='#0000FF' ><B><FONT SIZE='2' COLOR='#FFFFFF'>Comments</FONT><B></TD>");
strData.append("</TR>");
}
catch(Exception exc){
oLog.error("Error when creating header in excel sheet");
oLog.error(exc.getStackTrace().toString());}
//---------------Start:File Saving-------------//
java.io.File excelFile = null;
try{
java.util.Calendar calendar = new java.util.GregorianCalendar();
java.util.Date date = calendar.getTime();
java.text.DateFormat format2 = new java.text.SimpleDateFormat( "dd/mm/yyyy" );
java.text.DateFormat format1 = new java.text.SimpleDateFormat( "yyyyMMdd" );
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("dd/mm/yyyy");
String filePath ="file://web:/StaticContent/global/log/MyFolder/";
String fileName = filePath+FileNameText;
String strStatus = null;
// Creating a Directory to store excel file
boolean isDirExists=true;
java.io.File cacheDir = new java.io.File(filePath);
if (cacheDir.exists() == false)
{
if (cacheDir.mkdirs() == false)
{
strStatus="Can't create directory " + filePath ;
isDirExists=false;
}
}
if(isDirExists){
excelFile = new java.io.File(fileName);
if(excelFile.exists()){
excelFile.delete();
}
}
java.io.PrintWriter out = null;
// write data to excel file
try{
out = new java.io.PrintWriter(new java.io.FileOutputStream(excelFile));
out.println(strData.toString());
strData.delete(0,strData.length());
out.flush();
recPage.putObject("OutObj", out);
}
catch(java.io.IOException IOEx) {
if (excelFile == null)
oLog.error("Error when creating excel sheet - File pointer is NULL");
else
oLog.error("Error when creating excel sheet @ ["+excelFile.getAbsoluteFile()+"]");
oLog.error(IOEx.getStackTrace().toString());
}
finally {
if (out != null) {
out.flush();
}
}
}
catch(Exception e) {
if (excelFile == null)
oLog.error("Error when creating excel sheet - File pointer is NULL");
else
oLog.error("Error when creating excel sheet @@ ["+excelFile.getAbsoluteFile()+"]");
oLog.error(e.getStackTrace().toString());
}
ClipboardProperty recPagePXResults = recPage.getProperty("FTENotifiedResults");
java.util.Iterator itFields = recPagePXResults.iterator();
ClipboardProperty resultsPage;
String Test11= ""; // For all the strings give your properties which needs to be pushed to excel.
String Test12= "";
String Test13= "";
while(itFields.hasNext()) {
resultsPage = (ClipboardProperty)itFields.next();
String Test11_temp="";
Test11 = resultsPage.getProperty("GRP_ID").getStringValue();
//oLog.error(" Test11 GRP_ID" + Test11);
Test12 = resultsPage.getProperty("GRP_NM").getStringValue();
Test13 = resultsPage.getProperty("PRCS_NOTE").getStringValue();
StringBuffer eachRecord = new StringBuffer();
eachRecord.append("<TR>");
eachRecord.append("<TD class='excelText'>"+Test11+"</TD>");
eachRecord.append("<TD class='excelText'>"+Test12+"</TD>");
eachRecord.append("<TD class='excelText'>"+Test13+"</TD>");
eachRecord.append("</TR>");
try{
StringBuffer strData1 = new StringBuffer();
strData1.append(eachRecord.toString());
java.io.PrintWriter out = (java.io.PrintWriter)recPage.getObject(".OutObj");
out.println(strData1.toString());
oLog.error(" strData1.toString() is" + strData1.toString() );
out.flush();
}catch(Exception execp)
{
oLog.error("Failed to create excel sheet");
oLog.error(execp.getStackTrace().toString());
}
}
strData.append("</TABLE></HTML>");
try{
java.io.PrintWriter out = (java.io.PrintWriter) recPage.getObject(".OutObj");
out.println(strData.toString());
}
catch(Exception e){
oLog.error("Error when closing excel sheet");
oLog.error(e.getStackTrace().toString());
}
// Define message
System.out.println("Enter Activity SendEmail: ");
String To= tools.getParamValue("To");
String mailAddressTo[] = To.split(",");
String From = tools.getParamValue("From");
String Subject = tools.getParamValue("Subject");
String emailBody = tools.getParamValue("Body");
String Password = tools.getParamValue("Password");
String smtpHost = tools.getParamValue("smtpHost");
String ReplyTo = tools.getParamValue("ReplyTo");
String Cc= tools.getParamValue("CC");
String mailAddressCC[] = Cc.split(",");
String directoryPath= tools.getParamValue("DirectoryPath");
String fileName= tools.getParamValue("FileName");
try {
java.util.Properties props = new java.util.Properties();
props.setProperty("mail.smtp.host", smtpHost );
javax.mail.Session session = javax.mail.Session.getDefaultInstance(props, null);
javax.mail.Message message = new javax.mail.internet.MimeMessage(session);
message.setFrom(new javax.mail.internet.InternetAddress(From));
javax.mail.internet.InternetAddress[] mailAddress_TO = new javax.mail.internet.InternetAddress [mailAddressTo.length];
for(int i=0;i<mailAddressTo.length;i++)
{
mailAddress_TO[i] = new javax.mail.internet.InternetAddress(mailAddressTo[i]);
}
message.addRecipients(javax.mail.Message.RecipientType.TO, mailAddress_TO);
/*
javax.mail.internet.InternetAddress[] mailAddress_CC = new javax.mail.internet.InternetAddress [mailAddressCC.length];
for(int i=0;i<mailAddressCC.length;i++)
{
mailAddress_CC[i] = new javax.mail.internet.InternetAddress(mailAddressCC[i]);
}
message.addRecipients(javax.mail.Message.RecipientType.CC, mailAddress_CC);
*/
message.setSubject(Subject);
javax.mail.internet.MimeBodyPart messageBodyPart = new javax.mail.internet.MimeBodyPart();
messageBodyPart.setContent(emailBody,"text/html");
javax.mail.Multipart multipart = new javax.mail.internet.MimeMultipart();
multipart.addBodyPart(messageBodyPart);
java.util.Calendar calendar = new java.util.GregorianCalendar();
java.util.Date date = calendar.getTime();
java.text.DateFormat format1 = new java.text.SimpleDateFormat( "yyyyMMdd" );
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("dd/mm/yyyy");
String filePath = directoryPath+fileName;
javax.mail.internet.MimeBodyPart messageBodyPart1 = new javax.mail.internet.MimeBodyPart();
javax.activation.DataSource source = new javax.activation.FileDataSource(filePath);
messageBodyPart1.setDataHandler(new javax.activation.DataHandler(source));
messageBodyPart1.setFileName(fileName);
multipart.addBodyPart(messageBodyPart1);
message.setContent(multipart);
javax.mail.Transport.send(message);
}
catch(Exception e){
}
----------------------------------