Hobione's Weblog

Living & Breathing in Web 2.0 Era

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();
 }
 }
 }
Advertisements

January 12, 2011 - Posted by | Java

3 Comments »

  1. very well coded…thank you..

    Comment by suman. | January 20, 2012 | Reply

  2. Thank you, clear example worked first time

    Comment by velorichard | May 25, 2012 | Reply

  3. i have a problem while compiling my class.
    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/poi/hssf/u
    sermodel/HSSFWorkbook
    at met.Oldproj.ConvertCSVToXLS(Oldproj.java:285)
    at met.Oldproj.main(Oldproj.java:247)
    Caused by: java.lang.ClassNotFoundException: org.apache.poi.hssf.usermodel.HSSFW
    orkbook
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    … 2 more

    could u find me a Solution.
    here is the method from d class
    public static String ConvertCSVToXLS(String file) throws IOException {

    if (file.indexOf(“.csv”) < 0)
    return "Error converting file: .csv file not given.";

    File filepfad = new File(file);
    // String name = FileManager.getFileNameFromPath(file, false);
    String name = filepfad.getAbsolutePath();

    ArrayList<ArrayList> arList = new ArrayList<ArrayList>();
    ArrayList al = null;

    String thisLine;
    BufferedReader myInput = new BufferedReader(new FileReader(file));
    // DataInputStream myInput = new DataInputStream(new
    // FileInputStream(file));
    // List die Datei ein und sichert es in der ArryList
    while ((thisLine = myInput.readLine()) != null) {
    al = new ArrayList();
    String strar[] = thisLine.split(“;”);

    for (int j = 0; j < strar.length; j++) {

    String edit = strar[j].replace('\n', ' ');
    al.add(edit);
    }

    arList.add(al);

    }

    Comment by Jewel | August 29, 2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: