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