Java XLS Report Mail Sender Scheduler Example

Java XLS Report Mail Sender Scheduler Example

In last many tutorial you saw separately. In this example we will create Java application with below functionality:

  • Create multiple XLS sheet with data’s
  • Include creation of XLS through scheduler
  • Send email to one or many people or groups

Tools needed:

  • Any latest version of Eclipse
  • JDK 1.8
  • Quartz jar (Already included in pom.xml)
  • Maven (It comes with eclipse)
  • Java Mails jars (Already included in pom.xml)

Maven project name: ApacheXSSFReportJava

Java XLS Report Mail Sender Scheduler Example

  • Project dependencies: pom.xml:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>ApacheXSSFReportJava</groupId>
  <artifactId>ApacheXSSFReportJava</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>ApacheXSSFReportJava</name>
  <description>ApacheXSSFReportJava</description>
  
  <properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

		<maven.compiler.source>1.8</maven.compiler.source>
		<maven.compiler.target>1.8</maven.compiler.target>
		
		<org.springframework.version>4.1.5.RELEASE</org.springframework.version>
		<org.apache.log4j.version>2.1</org.apache.log4j.version>
		<org.apache.poi.version>3.11</org.apache.poi.version>	
		<quartz.version>2.1.7</quartz.version>
		<org.freemarker.version>2.3.20</org.freemarker.version>
		<javax.mail.version>1.4.6</javax.mail.version>
	</properties>

	<dependencies>	

		<!-- Spring -->
		<!-- Core utilities used by other modules. Define this if you use Spring 
			Utility APIs (org.springframework.core.*/org.springframework.util.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Expression Language (depends on spring-core) Define this if you use 
			Spring Expression APIs (org.springframework.expression.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-expression</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Bean Factory and JavaBeans utilities (depends on spring-core) Define 
			this if you use Spring Bean APIs (org.springframework.beans.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Aspect Oriented Programming (AOP) Framework (depends on spring-core, 
			spring-beans) Define this if you use Spring AOP APIs (org.springframework.aop.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aop</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Application Context (depends on spring-core, spring-expression, spring-aop, 
			spring-beans) This is the central artifact for Spring's Dependency Injection 
			Container and is generally always defined -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Various Application Context utilities, including EhCache, JavaMail, 
			Quartz, and Freemarker integration Define this if you need any of these integrations -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- JDBC Data Access Library (depends on spring-core, spring-beans, spring-context, 
			spring-tx) Define this if you use Spring's JdbcTemplate API (org.springframework.jdbc.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Object-to-Relation-Mapping (ORM) integration with Hibernate, JPA, 
			and iBatis. (depends on spring-core, spring-beans, spring-context, spring-tx) 
			Define this if you need ORM (org.springframework.orm.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Web application development utilities applicable to both Servlet and 
			Portlet Environments (depends on spring-core, spring-beans, spring-context) 
			Define this if you use Spring MVC, or wish to use Struts, JSF, or another 
			web framework with Spring (org.springframework.web.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Support for testing Spring applications with tools such as JUnit and 
			TestNG This artifact is generally always defined with a 'test' scope for 
			the integration testing framework and unit testing stubs -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${org.springframework.version}</version>
			<scope>test</scope>
		</dependency>
		
		<!-- Spring Aspects -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aspects</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>
		
		<!-- Spring Tx -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Spring JMS -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jms</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>		
		
		<!-- Log4j -->
        <dependency>
        	<groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>${org.apache.log4j.version}</version>
		</dependency>
        
        <dependency>
        	<groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>${org.apache.log4j.version}</version>
		</dependency>
		
		<!-- Apache POI jars -->
		
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>${org.apache.poi.version}</version>
		</dependency>
		<dependency>
    		<groupId>org.apache.poi</groupId>
    		<artifactId>poi-ooxml</artifactId>
    		<version>${org.apache.poi.version}</version>
		</dependency>
		
		<!-- Quartz job -->
		
		<!-- Quartz scheduler -->
		<dependency>
			<groupId>org.quartz-scheduler</groupId>
			<artifactId>quartz</artifactId>
			<version>${quartz.version}</version>
		</dependency>
		
		<!-- mail components -->
		<dependency>
			<groupId>javax.mail</groupId>
			<artifactId>mail</artifactId>
			<version>${javax.mail.version}</version>
		</dependency>
		<dependency>
			<groupId>org.freemarker</groupId>
			<artifactId>freemarker</artifactId>
			<version>${org.freemarker.version}</version>
		</dependency>

	</dependencies>
		
</project>
  • log4j2.xml:
<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="INFO" shutdownHook="disable">

	<Properties>
		<Property name="envrionment.target">DEV</Property>
	</Properties>

	<Properties>
		<Property name="logging.dir">./</Property>
	</Properties>

	<Appenders>
		<Console name="Console" target="SYSTEM_OUT">
			<PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
		</Console>

		<RollingFile name="RollingFile"
			fileName="JavaHonkReport.log"	filePattern="${sys:logging.dir}/logs/lifecyclemanager-service-%d{yyyy-MM-dd}-%i.log">
			<PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
			<!-- TODO:Change to time based policy -->
			<Policies>
				<TimeBasedTriggeringPolicy interval="1"	modulate="true" />
				<SizeBasedTriggeringPolicy size="100 MB" />
			</Policies>
			<DefaultRolloverStrategy max="4" />
		</RollingFile>
	</Appenders>

	<Loggers>
		<Root level="info">
			<AppenderRef ref="Console" />
			<!-- <AppenderRef ref="file" /> -->
			<AppenderRef ref="RollingFile" />
		</Root>
	</Loggers>
</Configuration>
  • JavaHonk.properties:
smtp.host=localhost
report.trigger.time = 0/30 * * 1/1 * ? *
javahonk.report.template=JavaHonk.ftl
javahonk_report_from_address=javahonk@javahonk.com
javahonk_report_to_address=javahonk@javahonk.com,javahonk@gmail.com
  • Sample cron and you could also validate is on CronMaker
0 0/1 * 1/1 * ? * -- Every minute
0/30 * * 1/1 * ? * - Every 30 seconds
0 00 17 ? * MON-FRI * - Every day at 17:00 PM Monday to Friday
  • JavaHonk.ftl: FreeMarker template which has been used to show up data summary in Outlook mail page:
<html>
<body>
  <h3>
    Java Honk sample report for ${date}
  </h3>

  <div>
  	<h5>
  	  Summary
  	</h5>  	
  	<p>
  	Number of records : ${noOfRecords}
  	<br/>
  </div>  
  
</body>
</html>
  • Spring context file where we load all configuration: common-context.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:util="http://www.springframework.org/schema/util"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd">

    <context:annotation-config />
    <context:component-scan base-package="com.javahonk" />
    
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>JavaHonk.properties</value>				
			</list>
		</property>
		<property name="ignoreUnresolvablePlaceholders" value="true"/>
	</bean>

    <import resource="scheduler-context.xml"/>
    <import resource="mailsettings-context.xml"/>

</beans>
  • mailsettings-context.xml: This file has all configuration which is needed for sending email:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd">
	
	<bean id="mailSender" class="org.springframework.mail.javamail.JavaMailSenderImpl">
    	<property name="host" value="${smtp.host}"/>
	</bean>
	
	<bean id="freemarkerConfiguration" class="org.springframework.ui.freemarker.FreeMarkerConfigurationFactoryBean">
		<property name="preferFileSystemAccess" value="false"></property>
  	    <property name="templateLoaderPath" value="classpath:/"></property>
	</bean>
	
	<bean id="riskTransferErrorMessage" class="org.springframework.mail.SimpleMailMessage">
	    <property name="from" value="javahonk@javahonk.com"/>
	    <property name="to" value="javahonk@javahonk.com"/>
	    <property name="subject" value="Testing Report"/>
	</bean>
	
	<bean id="mailHelper" class="com.javahonk.MailHelper"/>
	
	<!--
		<property name="mailSender" ref="mailSender"/>
    	<property name="templateMessage" ref="templateMessage"/>
    -->
	
</beans>
  • scheduler-context.xml: All scheduling related configuration:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd">
		
	<bean id="javaHonkScheduler" class="com.javahonk.JavaHonkReport"/>
	
	<bean id="javaHonkReportJob" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
    	<property name="targetObject" ref="javaHonkScheduler"/>
    	<property name="targetMethod" value="generateReport"/>
	</bean>
	
	<bean id="javaHonkReportCronTrigger" class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
    	<property name="jobDetail" ref="javaHonkReportJob"/>
    	<property name="cronExpression" value="${report.trigger.time}"/>
	</bean>
	
	<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
    <property name="triggers">
        <list>
        	<ref bean="javaHonkReportCronTrigger"/>
        </list>
    </property>
    <property name="quartzProperties">
    	<props>
    		<prop key="org.quartz.threadPool.class">org.quartz.simpl.SimpleThreadPool</prop>
        	<prop key="org.quartz.threadPool.threadCount">1</prop>
    	</props>
    </property>
    
</bean>
	
</beans>
  • JavaHonkReportFields: This class has been used to keep all XLS columns names:
package com.javahonk;

public class JavaHonkReportFields {

	public static String[] getJavaHonkReportFields(){
		
		String[] fields = new String[6];
		fields[0] = "First Name";
		fields[1] = "Last Name";
		fields[2] = "City";
		fields[3] = "Phone";
		fields[4] = "State";
		fields[5] = "Zip";
		
		return fields;
	}
	
}
  • JavaHonkReportModel.java: This class been used to create dummy data for XLS sheet:
package com.javahonk;

public class JavaHonkReportModel {
	
	private String firstName;
	private String lastName;
	private String city;
	private String phone;
	private String state;
	private String zip;
	
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getState() {
		return state;
	}
	public void setState(String state) {
		this.state = state;
	}
	public String getZip() {
		return zip;
	}
	public void setZip(String zip) {
		this.zip = zip;
	}
	@Override
	public String toString() {
		return "DSPSGalaxyReconModel [firstName=" + firstName + ", lastName="
				+ lastName + ", city=" + city + ", phone=" + phone + ", state="
				+ state + ", zip=" + zip + "]";
	}
	

}
  • JavaHonkExcelReportGenerator.java: This class has all code which is use to generate XLS sheet:
package com.javahonk;

import java.awt.Color;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class JavaHonkExcelReportGenerator {
	
	protected void setUpWorkSheets(
			XSSFWorkbook workbook, 
			XSSFSheet sheet, 
			String[] field) {
		
		XSSFRow row = sheet.createRow(0);
		
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFFont font = workbook.createFont();
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setColor(new XSSFColor(Color.WHITE));
	    style.setFont(font);
	    style.setFillForegroundColor(new XSSFColor(new byte[]{(byte)79,(byte)129,(byte)189}));
	    style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);


		int len = field.length;
		
		for(int i = 0; i < len; i++) {
			XSSFCell genericCell = row.createCell(i);
			genericCell.setCellValue(field[i]);
		}
		
		for(int k = 0; k <row.getLastCellNum(); k++){
			row.getCell(k).setCellStyle(style);
		}
	}
	
	private void writeRecord(XSSFRow row, List<String> elements){
		int len = elements.size();
		
		for (int i = 0; i < len; i++) {
			XSSFCell genericCell = row.createCell(i);
			genericCell.setCellValue(elements.get(i));
		}		 
	}
	
	public void processDSPSGalaxyWorkSheet(XSSFSheet sheet, List<JavaHonkReportModel> entries) {
		int exCount = 0;
		int lastCellNum = 0;
		
		for(JavaHonkReportModel entry : entries){
			XSSFRow row = sheet.createRow(++exCount);	
			List<String> elements = getDSPSGalaxyColumnElements(entry);
			writeRecord(row , elements);
			lastCellNum =  row.getLastCellNum();
		}
		
		for(int i=0; i < lastCellNum; i++){
			sheet.autoSizeColumn(i);
		}
	}
	
	private List<String> getDSPSGalaxyColumnElements(JavaHonkReportModel model){
		
		List<String> elements = new ArrayList<String>();
		
		elements.add(model.getFirstName());
		elements.add(model.getLastName());
		elements.add(model.getCity());
		elements.add(model.getPhone());
		elements.add(model.getState());
		elements.add(model.getZip());
		
		return elements;
		
	}
	
}
  • MailHelper.java: Helper class has all code to send email:
package com.javahonk;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.mail.internet.MimeMessage;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.mail.MailException;
import org.springframework.mail.MailSender;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.mail.javamail.MimeMessagePreparator;
import org.springframework.ui.freemarker.FreeMarkerTemplateUtils;

import freemarker.template.Configuration;
import freemarker.template.TemplateException;

public class MailHelper {

    private static final Logger logger = LogManager.getLogger(MailHelper.class.getName());

    @Autowired
    private MailSender mailSender;

    @Autowired
    @Qualifier("mailSender")
    private JavaMailSender javaMailSender;

    @Autowired
    Configuration freemarkerConfiguration;

    public void sendTemplatedMimeMessage(
            Map<String, Object> templatedMimeMessage,
            String template,
            String fromAddress,
            List<String> toAddresses,
            String mailSubject,
            Map<String, File> attachments) {

        MimeMessagePreparator preparator;
        try {
            preparator = new MimeMessagePreparator() {

                String messageText =
                        FreeMarkerTemplateUtils.processTemplateIntoString(
                                freemarkerConfiguration.getTemplate(template), templatedMimeMessage);

                @Override
                public void prepare(MimeMessage message) throws Exception {

                    MimeMessageHelper helper = new MimeMessageHelper(message, true);

                    for (String address : toAddresses) {
                        helper.addTo(address);
                    }

                    helper.setFrom(fromAddress);
                    helper.setSubject(mailSubject);

                    if (attachments != null) {

                        for (Entry<String, File> attachment : attachments.entrySet()) {

                            helper.addAttachment(attachment.getKey(), attachment.getValue());
                        }

                    }

                    helper.setText(messageText, true);
                }
            };

            this.javaMailSender.send(preparator);

        } catch (IOException | TemplateException | MailException ex) {

            logger.error("Failed to send email for templated message:" +
                    "\nFrom:" + fromAddress +
                    "\nTo:" + toAddresses +
                    "\nMessageMap:" + templatedMimeMessage, ex);

        }

    }

    public void sendMimeMessage(
            String mimeMessage,
            String fromAddress,
            List<String> toAddresses,
            String mailSubject,
            Map<String, File> attachments) {

        MimeMessagePreparator preparator = new MimeMessagePreparator() {

            @Override
            public void prepare(MimeMessage message) throws Exception {

                MimeMessageHelper helper = new MimeMessageHelper(message, true);

                for (String address : toAddresses) {
                    helper.addTo(address);
                }

                helper.setFrom(fromAddress);
                helper.setSubject(mailSubject);

                if (attachments != null) {

                    for (Entry<String, File> attachment : attachments.entrySet()) {

                        helper.addAttachment(attachment.getKey(), attachment.getValue());
                    }

                }

                helper.setText(mimeMessage);
            }
        };

        try {

            this.javaMailSender.send(preparator);

        } catch (MailException ex) {

            logger.error("Failed to send email for templated message:" +
                    "\nFrom:" + fromAddress +
                    "\nTo:" + toAddresses +
                    "\nMessage:" + mimeMessage, ex);
        }

    }

    public void sendSimpleMailMessage(
            SimpleMailMessage templateMessage,
            String message) {

        SimpleMailMessage msg = new SimpleMailMessage(templateMessage);

        msg.setText(message);

        try {

            this.mailSender.send(msg);

        } catch (MailException ex) {

            logger.error("Failed to send email for templated message:" +
                    "\nFrom:" + templateMessage.getFrom() +
                    "\nTo:" + templateMessage.getTo() +
                    "\nMessage:" + templateMessage.getText(), ex);

        }

    }

}
  • JavaHonkReport.java: Main class which creates XLS sheet put dummy data and send email:
package com.javahonk;

import java.io.File;
import java.io.FileOutputStream;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;


public class JavaHonkReport extends JavaHonkExcelReportGenerator{
	
	private static final Logger logger = LogManager.getLogger(JavaHonkReport.class);

	File attachment = null; 
	
	@Autowired
	public MailHelper mailHelper;
	
	@Value("${javahonk.report.template}")
	private String template;
	
	@Value("#{'${javahonk_report_to_address}'.split(',')}") 
	private List<String> javahonk_report_to_address;
	
	@Value("${javahonk_report_from_address}")
	private String javahonk_report_from_address;
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void generateReport() {

		logger.info("JavaHonk report...");

		attachment = createDSPSGalaxySheet("JavaHonk Report");
		
		String localDate = LocalDate.now().toString();			
		String subject = "JavaHonk Sample Report_" + localDate; 
		String reportName = "JavaHonkReport" + localDate+".xlsx";
		
		attachment = createDSPSGalaxySheet(reportName);
		Map<String,File> attachments = new HashMap<>();
		attachments.put(reportName, attachment);
		
		Map map = new HashMap();			
		map.put("date", LocalDate.now());
		map.put("noOfRecords", new Integer(10));
    	
		mailHelper.sendTemplatedMimeMessage(map, template, javahonk_report_from_address, javahonk_report_to_address, subject, attachments);

		logger.info("Java Honk report sent successfully...");

	}
    
    public File createDSPSGalaxySheet(String reportName) {
		
		File file = null;
		
		try {
			
			XSSFWorkbook workbook = new XSSFWorkbook();
			XSSFSheet javahonkSheet1 = workbook.createSheet("JAVAHONK_DATA1");
			XSSFSheet javahonkSheet2 = workbook.createSheet("JAVAHONK_DATA2");
			XSSFSheet javahonkSheet3 = workbook.createSheet("JAVAHONK_DATA3");

			setUpWorkSheets(workbook,javahonkSheet1, JavaHonkReportFields.getJavaHonkReportFields());
			setUpWorkSheets(workbook,javahonkSheet2, JavaHonkReportFields.getJavaHonkReportFields());
			setUpWorkSheets(workbook,javahonkSheet3, JavaHonkReportFields.getJavaHonkReportFields());			
			
			List<JavaHonkReportModel> javaHonkData1 = new ArrayList<JavaHonkReportModel>();
			
			for (int i = 0; i < 10; i++) {
				
				JavaHonkReportModel javaHonkReportModel = new JavaHonkReportModel();
				javaHonkReportModel.setFirstName("firstName");
				javaHonkReportModel.setLastName("lastName");
				javaHonkReportModel.setCity("city");
				javaHonkReportModel.setPhone("phone");
				javaHonkReportModel.setState("state");
				javaHonkReportModel.setZip("zip");
				javaHonkData1.add(javaHonkReportModel);
			}
			
			processDSPSGalaxyWorkSheet(javahonkSheet1, javaHonkData1);
			processDSPSGalaxyWorkSheet(javahonkSheet2, javaHonkData1);
			processDSPSGalaxyWorkSheet(javahonkSheet3, javaHonkData1);
						
			FileOutputStream out = new FileOutputStream(reportName+".xlsx");
			workbook.write(out);
			out.close();
			file = new  File(reportName+".xlsx");
			
		} 
		catch (Exception e) {
			
			logger.error("Error generating excel report:\n", e);
		}
	
		return file;
	}    
    
}
  • JavaHonkMainApp.java: Main class which loads all spring configuration and class JavaHonkReport class to create XLS sheet and send mail. As scheduler has been done through scheduler-context.xml file and it will run every 30 seconds to call “generateReport” operation in JavaHonkReport.java class:
package com.javahonk;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class JavaHonkMainApp {

    private static final Logger logger = LogManager.getLogger(JavaHonkMainApp.class);

    public static void main(String[] args) {

        logger.info("Starting JavaHonkMainApp...");

        ApplicationContext context = new ClassPathXmlApplicationContext("common-context.xml");
        
        registerShutdownHook(context);
    }

    private static void registerShutdownHook(ApplicationContext context) {
        Runtime.getRuntime().addShutdownHook(new Thread(() -> {
            logger.info("JavaHonkMainApp Exiting.");
            ((AbstractApplicationContext) context).close();
        }));
    }
}

Note: In this tutorial we are using email as well. So if you don’t have real SMTP host yet you could test it with dummy SMTP host. I have used FAKE SMTP to complete the test and validate the CRON time through CronMaker.

  • To run this project: Right click JavaHonkMainApp.java –> Run As –> Java Application (Note: To test this application you will have download FAKESMTP and start it as below:

Java XLS Report Mail Sender Scheduler Example

Java XLS Report Mail Sender Scheduler Example

  • You will in your Fake SMTP email is already published:

Java XLS Report Mail Sender Scheduler Example

Java XLS Report Mail Sender Scheduler Example

  • Double click to email to open it:

Java XLS Report Mail Sender Scheduler Example

Download project: apachexssfreportjava

Leave a Reply

Your email address will not be published. Required fields are marked *