JAVA PROGRAM ISSUE

Hi Venkatesh,

you can achieve it using   Apache POI API ,you need to add jars in your program which can be downloaded from JAVA2S website or
many other resources.

for sending mail please use javax mail, even you can find code snippets on java2s.any more help required please comment.

for connecting to the database the code will depend on the driver whether it is mysql,oracle or some other db.

thanks
gsk

Hi Gsk, Thanks for your reply.

Now I am able to connect to DB and write the records into an excel sheet successfully.

Now my requirement is to frame the excel sheets into an email body and add the excel sheet into the mail attachment and send an email to respective teams.

Also the excel sheet consists of 13 sheets, each sheet having one name, that name should be printed as table name. Please help me out, I am at the end of this coding..

I am really glad if you can help me on this.

Thanks
Venkatesh.

Hi Venkatesh,

please go through below code snippets,I am sure it will help you.Please go through examples of java excel sheet write operations
If you need more clarifications regarding these please comment
==============================================

//getting each worksheet code sample

            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Iterator rowIterator = sheet.iterator();
               //iterating over each row
                while (rowIterator.hasNext()) {
=====================================================================
for sending through mail


Properties props = System.getProperties();
props.put("mail.smtp.host", smtp);
Session session1 = Session.getDefaultInstance(props, null);
MimeMessage msg = new MimeMessage(session1);
msg.setFrom(new InternetAddress(from));
msg.setRecipients(Message.RecipientType.TO,InternetAddress.parse(to, false));
msg.setSubject(subject);
MimeBodyPart mimeBodyPart = new MimeBodyPart();
mimeBodyPart.setText(message);
MimeBodyPart mimeBodyPart2= new MimeBodyPart();
File temp= null;
 
temp= new File("C:/temp/sample.xls"); //here your folder name will go or eclipse resource folder where you want to store files
boolean ch=temp.createNewFile();
FileDataSource fds = new FileDataSource("C:/temp/sample.xls");
mbp2.setDataHandler(new DataHandler(fds));
mbp2.setFileName(fds.getName());
Multipart multiPart = new MimeMultipart();
multiPart.addBodyPart(mimeBodyPart);
multiPart.addBodyPart(mimeBodyPart2);
msg.setContent(multiPart);
Transport.send(msg);

Error in smtp and mbp2... what are those? how to fix those errors?

I used your code snippet and I am getting exception as Null here is my update code

            Properties props = System.getProperties();
            props.put("mail.smtp.host","smtp.office365.com");
            Session session1 = Session.getDefaultInstance(props, null);
            MimeMessage msg = new MimeMessage(session1);
            msg.setFrom(new InternetAddress("venkatesh.sarivisetty@gmail.com"));
            msg.setRecipients(Message.RecipientType.TO,InternetAddress.parse("venkatesh.sarivisetty@gmail.com", false));
            msg.setSubject("Hourly Report Statistics");
            MimeBodyPart mimeBodyPart = new MimeBodyPart();
            mimeBodyPart.setText("Here is the OnlyID Hourly Report Stats");
            MimeBodyPart mimeBodyPart2= new MimeBodyPart();
            File filename= null;
            filename= new File("C:/appl/data/"); //here your folder name will go or eclipse resource folder where you want to store files
            boolean ch=filename.createNewFile();
            FileDataSource fds = new FileDataSource("C:/appl/data/");
            MimeMessage mbp2 = null;
          mbp2.setDataHandler(new DataHandler(fds));
            mbp2.setFileName(fds.getName());
            Multipart multiPart = new MimeMultipart();
            multiPart.addBodyPart(mimeBodyPart);
            multiPart.addBodyPart(mimeBodyPart2);
            msg.setContent(multiPart);
            Transport.send(msg);

please use  below line  instead of Session session1 = Session.getDefaultInstance(props, null);

Session session = Session.getDefaultInstance(properties);

and
use below line instead of    MimeMessage mbp2 = null;
 MimeMessage mbp2 = new MimeMessage(session1);

thanks

I am getting below error message

Exception:530 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM [CY4PR14CA0045.namprd14.prod.outlook.com]

I am using Micorsoft Outlook 365 and
props.put("mail.smtp.host","smtp.outlook.com");

what else I can do now to send mai without any issues?

Any comments further, i am still looking for anyones help!

Hi Venakatesh,

please  check below things.
========================
1) please ensure that endpoint is smtp.office365.com or smtp.outlook.com.or you may use mail.protection.outlook.com(it is MX endpoint)
2) you may update 2007 to 2016
3) Please check with your IT admin guy for your account settings and if needed please remove your account and satrt afresh by adding your account.
4) also ensure whether  SMTP port is 25/TLS.

Hope it works.It would be better to check how the previous mail were going through outlook.same settings we need to hard code in our java code.I mean  below things can be possible

props.put("mail.smtp.host","smtp.outlook.com");
or
props.put("mail.smtp.host","smtp.office365.com");
or
props.put("mail.smtp.host","mail.protection.outlook.com");

If you are only handing these settings please check yourself or collect them from Admin  person.

thanks

Just now I heard from the team that we cant send mail from outlook 365 as it restricted.

Is there any other way to send this rather than using smtp or outlook??

Also, Now I need to convert this java file to jar file to schedule this in every hour.. in task scheduler and see how it is working.. ?? do you know how to convert this java file to jar file?

Hi Venkatesh,

if outlook is restricted ,please use free gmail smtp server,please google to find configuration parameters and others

if you working in eclipse ide please click on package explorer and click on export then export as jar,give suitable name and you  have jar ready :)

going to older days ,if u wanna through command then please go on command line (DOS) ,go upto project directory and run the command
jar cvf jarfilename inputfiles  eg( jar cvf javamail.jar  mail.java
oy try

jar cvf jarfilename *.java (to make jar together, better go by ide :) as u wish

even you can set cron jon in unix to schedule this job or by code in java

thanks

Hi Venkatesh ,

to add further, you can also try  this free smtp server

https://serversmtp.com/smtp-configuration/

thanks

where is the pacakge explorer exist in Eclipse Mars version

please click on window -preferences->show view it will give -package explorer

Hi,

Please click on just window ->show view and choose project explorer

on project explorer pls right click and select export
then in export wizard type -> jar
you will see java jar option

how to run this jar file now?

please double click it:)

I am able to create and schedule it in my machine, it is working as expected. but somehow the mail is not working.. I need to share this report or link to refer the hourly updates to team ..

Also is there a way to take the individual sheet output and send it as html body in outlook?

Any other way??

Thanks
Venkatesh.

Also how to create a date folder and write the sheet inside that date folder?

Hi Venkatesh,

It seems good that upto yet you have came so far.I had suggested you two free smtp server.which one you are using please specify.

if you are using java7 ,this is the way to create folder
===========================================
Path path = Paths.get("C:\\Mail_folder");  // I choosen name as Mail_folder ,please give whatever you like
Files.createDirectories(path);
==========================================================================
for mail part
---------------------

  // sets SMTP server properties
        Properties properties = new Properties();
        properties.put("mail.smtp.host", host);
        properties.put("mail.smtp.port", port);
        properties.put("mail.smtp.auth", "true");
        properties.put("mail.smtp.starttls.enable", "true");

like you can send text to html with adding below code snippet
 msg.setSubject(subject);
        msg.setSentDate(new Date());
        // set plain text message
        msg.setContent(message, "text/html");

=======================================
gmail smtp configuration
-------------------------------------------------------------
 String host = "smtp.gmail.com";
        String port = "587";
        String mailFrom = "venkat@gmail.com"; //whatever it is
        String password = "password"; //whatever you choose
 
        // outgoing message information
        String mailTo = "xyz@gmail.com";
        String subject = "Mail successful";
 
Thanks

I am trying like this but not working, I would like to create a date folder and write the file inside

                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyymmdd");
              String date = dateFormat.format("yyyymmdd");
                File dir = new File(date);
               filename="\\path\\"+dateFormat.format(dir)+"\\filename_";

please try this

 SimpleDateFormat sdfDate = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss.SSS");// dd/MM/yyyy
        Date now = new Date();
        String strDate = sdfDate.format(now);

File file = new File(filePath + "\\" + filename+strDate.replace(":","_").replace(".","_")+".txt");

looks like you understand in other way.
I am able to create the file now with timestamp as extenstion but now I want to keep current date files in one folder

For example, 10092018 folder consists of all files for today.

String filename="\\\\path\\path\\Venkatesh\\abc\\def(here I need to create a date folder)\\filename_";

Hi Venkatesh,

pls try this

File files = new File("C:\\Venkatesh\\abc\\def ||filename");
        if (!files.exists()) {
            if (files.mkdirs()) {
                System.out.println("the respective folder is created");
            } else {
                System.out.println("no folder created");
            }

Hi, I am still not able to send mail after completion of my program , I am getting outlook ole client issue .. please suggest me a mail send

Thanks
Venkatesh.