Sending an e-mail through java application with excel file attached - not working

Question

I am trying to send a mail through Java application with excel file as attachment without actually creating the file.The data in the excel file comes from the database. I am able to send the mail with attachment but the file is in Text(Tab Delimited) format. But I want the file to be in Excel format only.

Please help....

Following is the code:

      //Here goes my DBConnection and Query code

      while(rs.next())
      {             
         for(int i=1;i<13;i++)
         {
                   //tab for each column
                   exceldata = exceldata+""+"\t";

         }
                 // new line for end of eachrow 
                exceldata = exceldata+"\n";

     } 
     String data = exceldata;
     String filename="example";

     MimeMessage msg = new MimeMessage(session);

     //TO,From and all the mail details goes here

     DataSource fds = new ByteArrayDataSource(data,"application/vnd.ms-excel");

     MimeBodyPart mbp1 = new MimeBodyPart(); 
     mbp1.setText("Hi");

     MimeBodyPart mbp2 = new MimeBodyPart();
     mbp2.setDataHandler(new DataHandler(fds));   
     mbp2.setFileName(filename);    

     Multipart mp = new MimeMultipart();   
     mp.addBodyPart(mbp1);   
     mp.addBodyPart(mbp2);   
     msg.setContent(mp);   
     msg.saveChanges();  

     // Set the Date: header  
     msg.setSentDate(new java.util.Date()); 

     Transport.send(msg);            

Answer 1

You need to output your tab limited data into an excel file. Just tweaking the MIME type would not make Excel perceive your tab limited text file as an excel document.

Any spreadsheet file has a different binary structure altogether. It needs to have a Workbook, Worksheets and Rows of Cell data within; and they are clearly missing from your text file. That's why it doesn't work the way you expect it to.

Here's how you could use Apache POI to create a temporary excel file to be later used as a mail attachment.

Workbook xlsFile = new HSSFWorkbook(); // create a workbook
CreationHelper helper = xlsFile.getCreationHelper();
Sheet sheet1 = xlsFile.createSheet("Sheet #1"); // add a sheet to your workbook

while(rs.next())
{
 Row row = sheet1.createRow((short)0); // create a new row in your sheet
 for(int i = 0; i < 12; i++)
 {
   row.createCell(i).setCellValue(
     helper.createRichTextString(exceldata)); // add cells to the row
 }
} 

// Write the output to a temporary excel file
FileOutputStream fos = new FileOutputStream("temp.xls");
xlsFile.write(fos);
fos.close();

// Switch to using a `FileDataSource` (instead of ByteArrayDataSource)
DataSource fds = new FileDataSource("temp.xls");

If you don't want to create a temporary excel file to the dump the data here's how to achieve the same

ByteArrayOutputStream bos = new ByteArrayOutputStream();
xlsFile.write(bos); // write excel data to a byte array
fos.close();

// Now use your ByteArrayDataSource as
DataSource fds = new ByteArrayDataSource(bos.toByteArray(), "application/vnd.ms-excel");

Answer 2

if you write a txt file you get a text file, changing your contenttype to excel will not automagicaly convert a tab based text file to an excel file.

But luckily there are tricks. make sure your filename ends in .xls en most email programs will try to open it as an excel file even though its still a tab delimited text file.

same works with naming it.csv and using ; as delimiters.

The only way to have an actual excel file is to use tools that create excel files like Apache POI prject and several other tools.

Answer 3

try this.this code works fine for Attachments.

    public void sendMail(String receiverId) {

    try {
        // this below commented line for the HTML body text
        // MultiPartEmail htmlEmail = new HtmlEmail();
        // OR
        // HtmlEmail email = new HtmlEmail();

        MultiPartEmail email = new MultiPartEmail();
        // setting the port number
        email.setSmtpPort(getPortNumber());
        // authenticating the user
        email.setAuthenticator(new DefaultAuthenticator(getSenderID(),
                getSenderPassword()));
        // email.setDebug(true);
        email.setSSL(true);
        // setting the host name
        email.setHostName(getHostName());
        // setting the rciever id

        email.addTo(receiverId);

        // check for user enterd cc or not
        if (getCc() != null) {
            // add the cc
            email.addCc(getCc());
        }
        // check for user enterd bcc or not
        if (getBcc() != null) {
            // add the bcc
            email.addBcc(getBcc());
        }
        // setting the sender id
        email.setFrom(getSenderID());
        // setting the subject of mail
        email.setSubject(getSubject());
        // setting message body
        email.setMsg(getBody());
        // email.setHtmlMsg("<h1>"+getBody()+"</h1>");

        // checking for attachment attachment
        if (getAttachmentPath() != null) {
            // add the attachment
            EmailAttachment attachment = new EmailAttachment();
            attachment.setPath(getAttachmentPath());
            attachment.setDisposition(EmailAttachment.ATTACHMENT);
            email.attach(attachment);
        }

        // send the email
        email.send();
        // System.out.println("Mail sent!");
    } catch (Exception e) {
        // System.out.println("Exception :: " + e);
         e.printStackTrace();
        // gl.writeWarning("Error occured in SendMail.java of sendMailWithAttachment() ");
        // gl.writeError(e);
    }
}// sendmail()

Answer 4

You can use following code to send email through your java application with the attachment of excel file. One thing once you write all content from db to file then close file reader and check where file is exits at that location.

    // Define message
    Message message = new MimeMessage(session);
    message.setFrom(new InternetAddress(from));
    message.addRecipient(Message.RecipientType.TO,
      new InternetAddress(to));
    message.setSubject("Hello JavaMail Attachment");

    // Create the message part
    BodyPart messageBodyPart = new MimeBodyPart();

    // Fill the message
    messageBodyPart.setText("Pardon Ideas");

    Multipart multipart = new MimeMultipart();
    multipart.addBodyPart(messageBodyPart);

    // Part two is attachment
    messageBodyPart = new MimeBodyPart();
    DataSource source = new FileDataSource(filename);
    messageBodyPart.setDataHandler(new DataHandler(source));
    messageBodyPart.setFileName(filename);
    multipart.addBodyPart(messageBodyPart);

    // Put parts in message
    message.setContent(multipart);

    // Send the message
    Transport.send(message);

Answer 5

    try {

        int smtpPort = Integer.parseInt(port);
        LocalDate localDate = LocalDate.now();
        String date = DateTimeFormatter.ofPattern("yyy-MM-dd").format(localDate);

        java.util.Properties props = new java.util.Properties();
        props.put("mail.smtp.host", smtpHost);
        props.put("mail.smtp.port", "" + smtpPort);
        Session session = Session.getDefaultInstance(props, null);

        Workbook xlsFile = new HSSFWorkbook(); 
        CreationHelper helper = xlsFile.getCreationHelper();
        // add a sheet to your workbook
        Sheet sheet = xlsFile.createSheet("Lb Notification Records"); 

        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("test1 header");
        header.createCell(1).setCellValue("test2 header");
        header.createCell(2).setCellValue("test3 header");

        for(int i = 0; i <json.length(); i++) {

            JSONObject jobj = json.getJSONObject(i);
            Row dataRow = sheet.createRow(i+1);
            dataRow.createCell(0).setCellValue(jobj.getString("name_1"));
            dataRow.createCell(1).setCellValue(jobj.getString("name_2"));
            dataRow.createCell(2).setCellValue(jobj.getString("name_3"));
        }

        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        // write excel data to a byte array
        xlsFile.write(bos); 
        bos.close();

        String fileName = "AffectedWipVips-"+date+".xls";
        // Construct the message
        MimeMessage message = new MimeMessage(session);
        message.setFrom(new InternetAddress(from));
        InternetAddress[] addressTo = new InternetAddress[to.length];

        for (int i = 0; i < to.length; i++) {
            addressTo[i] = new InternetAddress(to[i]);
        }
        message.setRecipients(Message.RecipientType.TO, addressTo);

        InternetAddress[] addressCC = new InternetAddress[cc.length];
        for (int i = 0; i < cc.length; i++) {
          addressCC[i] = new InternetAddress(cc[i]);
        }
        message.setRecipients(Message.RecipientType.CC, addressCC);
        message.setSubject(subject);

        // Set the email message text.
        MimeBodyPart messagePart = new MimeBodyPart();
        messagePart.setContent(content, "text/html");
        // Set the email attachment file
        Multipart multipart = new MimeMultipart();
        multipart.addBodyPart(messagePart); 

        // Now use your ByteArrayDataSource as
        DataSource fds = new ByteArrayDataSource(bos.toByteArray(), "application/vnd.ms-excel");

        MimeBodyPart attachmentPart = new MimeBodyPart();
        attachmentPart.setDataHandler(new DataHandler(fds));

        attachmentPart.setFileName(fileName);
        multipart.addBodyPart(attachmentPart);

        message.setContent(multipart);
        Transport.send(message);

        return "Success";

    }  catch (Exception e) {
        log.error("Error in sending csv attachment  email " + e);
        e.printStackTrace();
        return "Error in sending csv attachment email";

    }

Answer 6

Try this,

Multipart multipart = new MimeMultipart();
  multipart.addBodyPart("some text");

  // Part two is attachment
  messageBodyPart = new MimeBodyPart();

  String filePath = "your file path";
 File f1 = new File(filePath);  
DataSource source = new FileDataSource(filePath);
  messageBodyPart.setDataHandler(new DataHandler(source));
 messageBodyPart.setFileName(f1.getName());
  multipart.addBodyPart(messageBodyPart);
  // Put parts in message
  m.setContent(multipart);
  //String msg="Hello Prabhakar";
  //m.setContent(msg,"text/html");
  transport.sendMessage(m,m.getAllRecipients());
  transport.close();