HSSFWorkbook object write it to Excel, send it as an attachment

public static void loadDailyDataToExcel(ResultSet rs, Statement st, Connection connection, Properties properties) {

 try {
 HSSFWorkbook workBook = new HSSFWorkbook();
 HSSFSheet sheet = workBook.createSheet("Excel Sheet");
 HSSFRow rowhead = sheet.createRow((short) 0);
 rowhead.createCell((short) 0).setCellValue("RESPONSE DATE");
 rowhead.createCell((short) 1).setCellValue("SERVICE ID");
 rowhead.createCell((short) 2).setCellValue("Q");
 rowhead.createCell((short) 3).setCellValue("T");
 rowhead.createCell((short) 4).setCellValue("V");
 rowhead.createCell((short) 5).setCellValue("CUSTOMER");
 rowhead.createCell((short) 6).setCellValue("OVERALL");
 rowhead.createCell((short) 7).setCellValue("COMMENTS");
 rowhead.createCell((short) 8).setCellValue("CONTACT");
 rowhead.createCell((short) 9).setCellValue("CONTACT INFO");

 int index = 1;
 Format formatter = new SimpleDateFormat("MM/dd/yy");

 while (rs.next()) {

 HSSFRow row = sheet.createRow((short) index);
 row.createCell((short) 0).setCellValue(formatter.format(rs.getDate(1)));
 row.createCell((short) 1).setCellValue(rs.getString(2));
 row.createCell((short) 2).setCellValue(rs.getString(3));
 row.createCell((short) 3).setCellValue(rs.getString(4));
 row.createCell((short) 4).setCellValue(rs.getString(5));
 row.createCell((short) 5).setCellValue(rs.getString(6));
 row.createCell((short) 6).setCellValue(rs.getString(7));
 String comments = rs.getString(8);
 if (comments != null) {
 row.createCell((short) 7).setCellValue(comments);
 }
 String contact = rs.getString(9);
 if (contact != null) {
 row.createCell((short) 8).setCellValue(contact);
 }
 String contactInfo = rs.getString(10);
 if (contactInfo != null) {
 row.createCell((short) 9).setCellValue(contactInfo);
 }
 index++;
 }//while

 if (index > 1) {
 String fileName = "SurveyResponsesDailyReport-TestServices.xls";
 String to = properties.getProperty("survey.emailToDailyReport3aTest"); //from property file
 String subject = "Customer Survey 'Please Contact' Daily Summary - Test Services";
 String body = "Daily Survey Report for Test Services in Microsoft excel format.\n\n";
 emailNotification = new EmailNotification();
 emailNotification.sendEmailWithAttachment(to, subject, body, fileName, workBook, index);

 try {
 String hobiOneDirectoryPath = properties.getProperty("survey.hobiOneDirectoryPath"); // c:\\temp\\hobi\\
 FileOutputStream fileOut = new FileOutputStream(hobiOneDirectoryPath+"SurveyResponsesDailyReport-TestServices.xls");
 workBook.write(fileOut);
 fileOut.close();
 System.out.println("SurveyResponsesDailyReport-TestServices.xls was copied to "+ hobiOneDirectoryPath);

 } catch (FileNotFoundException fnfe) {
 emailNotification = new EmailNotification();
 emailNotification.sendEmail(emailToSystemAdmin, "Survey error from : ExportSurveyResponsesTestServicesToExcel.jar", "Environment: " + serverName + " : " + fnfe);

 System.out.println("Oops, FileNotFoundException caught. " + fnfe);
 }
 }
 } catch (Exception e) {
 emailNotification = new EmailNotification();
 emailNotification.sendEmail(emailToSystemAdmin, "Survey error from : ExportSurveyResponsesTestServicesToExcel.jar", "Print Stack Trace " + e);
 e.printStackTrace();
 } finally {
 try {
 if (rs != null) {
 rs.close();
 }
 if (st != null) {
 st.close();
 }
 if (connection != null) {
 connection.close();
 }
 } catch (SQLException sx) {
 sx.getMessage();
 }
 }
 }//close method

 

public void sendEmailWithAttachment(String recipients, String subject, String body,String attachmentFileName, HSSFWorkbook hssWorkBook, int totalRows) {

 properties = new Properties();
 properties.put("mail.smtp.host", host);
 properties.put("mail.smtp.port", port);

 if (recipients.equals("") || subject.equals("") || body.equals("") || attachmentFileName.equals("") || hssWorkBook.getBytes().length < 0 ) {
 System.out.println("Usage: sendEmailAttachemt() method parameter might be missing, you may check for the /test/local/surveyToExcel-test.properties for any recent changes");
 System.exit(1);
 }
 Session session = Session.getInstance(properties, null);
 try {
 // create a message
 MimeMessage msg = new MimeMessage(session);
 DataSource ds = null;
 msg.setFrom(new InternetAddress(from));

 ArrayList recipientsArray = new ArrayList();
 StringTokenizer stringTokenizer = new StringTokenizer(recipients, ",");

 while (stringTokenizer.hasMoreTokens()) {
 recipientsArray.add(stringTokenizer.nextToken());
 }
 int sizeTo = recipientsArray.size();
 InternetAddress[] addressTo = new InternetAddress[sizeTo];
 for (int i = 0; i < sizeTo; i++) {
 addressTo[i] = new InternetAddress(recipientsArray.get(i).toString());
 }
 msg.setRecipients(Message.RecipientType.TO, addressTo);

 // Parse a comma-separated list of email addresses. Be strict.
//            msg.setRecipients(Message.RecipientType.CC,
//                    InternetAddress.parse(<a href="mailto:test@aa.test">test@aa.test</a>, true));

 msg.setSubject(subject);

 // create and fill the first message part
 MimeBodyPart mimeBodyPart1 = new MimeBodyPart();
 mimeBodyPart1.setText(body);

 // create the second message part
 MimeBodyPart mimeBodyPart2 = new MimeBodyPart();

 ByteArrayOutputStream baos = new ByteArrayOutputStream();
 try{
 hssWorkBook.write(baos);
 byte[] bytes = baos.toByteArray();
 ds = new ByteArrayDataSource(bytes, "application/excel");
 }catch (IOException ioe ){
 this.sendEmail("hobione@hobione.com", "Survey excel file send error", "ByteArrayOutputStream: " + ioe);
 ioe.printStackTrace();
 }
 DataHandler dh = new DataHandler(ds);
 mimeBodyPart2.setHeader("Content-Disposition", "attachment;filename="+attachmentFileName+".xls");
 mimeBodyPart2.setDataHandler(dh);
 mimeBodyPart2.setFileName(attachmentFileName);
 // create the Multipart and add its parts to it
 Multipart multiPart = new MimeMultipart();
 multiPart.addBodyPart(mimeBodyPart1);
 multiPart.addBodyPart(mimeBodyPart2);

 // add the Multipart to the message
 msg.setContent(multiPart);

 // set the Date: header
 msg.setSentDate(new Date());

 // send the message
 javax.mail.Transport.send(msg);
 System.out.println("Report emailed successfully to: " + recipients +" Total rows count:" + totalRows);

 }catch (MessagingException mex) {
 mex.printStackTrace();
 Exception ex = null;
 if ((ex = mex.getNextException()) != null) {
 this.sendEmail("hobione@hobion.com", "Survey excel file send error", "Print Stack Trace: " + ex);
 ex.printStackTrace();
 }
 }
 }

Advertisement