Uncategorized

Download Excel RESTFul Service

By Techi Works #RESTFul, #RESTFul Service, #web service

Download Excel RESTFul Service

Create report in excel or csv format is very common to any project. If you are actively using RESTFul service in your project and get requirement to generate download functionality of excel sheet through browser you could use below example.

Note if you are in hurry to just need code to send excel sheet in response please use below:

@GET
@Path("/downloadxls")
@Produces("application/vnd.ms-excel")
public Response downloadXLS() {
	
	File file = new File("C:\\LAS\\JavaHonk\\Restful\\test.xlsx");
	ResponseBuilder response = Response.ok((Object) file);
	response.header("Content-Disposition", "attachment; filename=new-excel-file.xlsx");
	response.header("Content-Type","application/vnd.ms-excel");
	return response.build();

}
  • Detail example: To show something working I will use Jetty server with maven and POI to generate and send excel sheet through RESTFul serivce. Please create maven project name: DownloadExcelRESTFulService below is project structure:

Download Excel RESTFul Service

  • All 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>com.wfs.otc.report</groupId>
  <artifactId>DownloadExcelRESTFulService</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
    <properties>
    	<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>
	</properties>
	
	<dependencies>
	
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>	
		
		<!-- Jetty/Jersey dependency -->
		<dependency>
			<groupId>com.sun.jersey</groupId>
			<artifactId>jersey-server</artifactId>
			<version>1.9</version>
		</dependency>
 
		<dependency>
			<groupId>com.sun.jersey</groupId>
			<artifactId>jersey-json</artifactId>
			<version>1.9</version>
		</dependency>
 
		<dependency>
			<groupId>com.sun.jersey</groupId>
			<artifactId>jersey-client</artifactId>
			<version>1.9</version>
		</dependency>
 
		<dependency>
			<groupId>org.codehaus.jackson</groupId>
			<artifactId>jackson-core-asl</artifactId>
			<version>1.8.8</version>
		</dependency>
 
		<dependency>
			<groupId>org.codehaus.jackson</groupId>
			<artifactId>jackson-mapper-asl</artifactId>
			<version>1.8.8</version>
		</dependency>
 
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
			<version>2.1.4</version>
		</dependency>
 
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-annotations</artifactId>
			<version>2.5.0</version>
		</dependency>
 
		<dependency>
			<groupId>org.eclipse.jetty</groupId>
			<artifactId>jetty-server</artifactId>
			<version>8.1.12.v20130726</version>
		</dependency>
 
		<dependency>
			<groupId>org.eclipse.jetty</groupId>
			<artifactId>jetty-servlet</artifactId>
			<version>8.1.12.v20130726</version>
		</dependency>
 
		<dependency>
			<groupId>commons-lang</groupId>
			<artifactId>commons-lang</artifactId>
			<version>2.5</version>
		</dependency>
 
		<!-- Apache HTTP client -->
		<dependency>
			<groupId>org.apache.httpcomponents</groupId>
			<artifactId>httpclient</artifactId>
			<version>4.3.6</version>
		</dependency>
 
 		<!-- 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>
		
		<!-- 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>
		
	</dependencies>
	
</project>
  • spring-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.report" />
	
	<bean id="propertyLoader" class="com.javahonk.report.util.PropertyLoader" />
	
	<bean id="excelGeneratorChild" class="com.javahonk.report.poi.ExcelGeneratorChild" />	
	
</beans>
  • 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{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
		</Console>

		<RollingFile name="RollingFile"
			fileName="logs/rolling-file.log"	filePattern="${sys:logging.dir}/logs/rolling-file-%d{yyyy-MM-dd}-%i.log">
			<PatternLayout pattern="%d{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>
  • PropertyLoader.java: To load all properties:
package com.javahonk.report.util;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;

import com.javahonk.report.poi.ExcelGeneratorChild;
import com.javahonk.report.restulservice.RESTFulResource;

public class PropertyLoader {
	
	@Autowired
	ExcelGeneratorChild excelGeneratorChild;
	
	public void storeAllProperties(ApplicationContext context) throws IOException{
		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("excelGeneratorChild", excelGeneratorChild);
		RESTFulResource.setMap(map);		
				
	}

}
  • RESTFulResource.java: Main RESTFul resource class where all request will come and here we have one method downloadXLS to download XLS sheet:
package com.javahonk.report.restulservice;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Response;
import javax.ws.rs.core.Response.ResponseBuilder;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Configurable;

import com.javahonk.report.model.XLSDataModel;
import com.javahonk.report.poi.ExcelGeneratorChild;

@Configurable
@Path("/RESTFul")
public class RESTFulResource {
	
	private static Map<String, Object> contextMap = new HashMap<String, Object>();
	
	private static final Logger logger = LogManager.getLogger(RESTFulResource.class);
	
	@GET
	@Path("/downloadxls")
	@Produces("application/vnd.ms-excel")
	public Response downloadXLS() {
		
		logger.info("downloading xls sheet...");
		
		List<XLSDataModel> dataModel = new ArrayList<XLSDataModel>();
		dataModel.add(new XLSDataModel("Java", "Honk", "NY"));
		dataModel.add(new XLSDataModel("Java2", "Honk2", "NY"));
		dataModel.add(new XLSDataModel("Java2", "Honk2", "NY"));
		dataModel.add(new XLSDataModel("Java2", "Honk2", "NY"));
		
		File file = ((ExcelGeneratorChild)contextMap.get("excelGeneratorChild")).createXLS("JavaHonk", dataModel);
		ResponseBuilder response = Response.ok((Object) file);
	    response.header("Content-Disposition", "attachment; filename=JavaHonk.xlsx");
	    response.header("Content-Type","application/vnd.ms-excel");
	    return response.build();
 
	}
	

	public static Map<String, Object> getMap() {
		return contextMap;
	}

	public static void setMap(Map<String, Object> map) {
		RESTFulResource.contextMap = map;
	}
	
 
}
  • ExcelGenerator.java: Abstract class to generate XLSX sheet:
package com.javahonk.report.poi;

import java.awt.Color;
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;

abstract class ExcelGenerator<T> {
	
	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);
		}
	}
	
	protected void processWorkSheet(XSSFSheet sheet, List<T> entries) {
		int exCount = 0;
		int lastCellNum = 0;
		
		for(T entry : entries){
			XSSFRow row = sheet.createRow(++exCount);	
			List<String> elements = getColumnElements(entry);
			writeRecord(row , elements);
			lastCellNum =  row.getLastCellNum();
		}
		
		for(int i=0; i < lastCellNum; i++){
			sheet.autoSizeColumn(i);
		}
	}
	
	protected 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));
		}		
	}
	
	abstract protected List<String> getColumnElements(T model);
}
  • ExcelGeneratorChild.java which extends ExcelGenerator.java to create XLSX sheet:
package com.javahonk.report.poi;

import java.awt.Color;
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;

abstract class ExcelGenerator<T> {
	
	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);
		}
	}
	
	protected void processWorkSheet(XSSFSheet sheet, List<T> entries) {
		int exCount = 0;
		int lastCellNum = 0;
		
		for(T entry : entries){
			XSSFRow row = sheet.createRow(++exCount);	
			List<String> elements = getColumnElements(entry);
			writeRecord(row , elements);
			lastCellNum =  row.getLastCellNum();
		}
		
		for(int i=0; i < lastCellNum; i++){
			sheet.autoSizeColumn(i);
		}
	}
	
	protected 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));
		}		
	}
	
	abstract protected List<String> getColumnElements(T model);
}
  • XLSDataModel.java: POJO class to store XLSX sheet data while creating sheet:
package com.javahonk.report.model;

public class XLSDataModel {
	
	private String firstName; 
	private String lastName; 
	private String location;
	
	public XLSDataModel(String firstName, String lastName, String location) {
		super();
		this.firstName = firstName;
		this.lastName = lastName;
		this.location = location;
	}
	public String getFirstName() {
		return firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public String getLocation() {
		return location;
	}
	
}
  • DownloadExcelRESTFul.java: Main class to load spring context and start jetty server:
package com.javahonk.report;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.eclipse.jetty.server.Server;
import org.eclipse.jetty.servlet.ServletContextHandler;
import org.eclipse.jetty.servlet.ServletHolder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.javahonk.report.util.PropertyLoader;
import com.sun.jersey.spi.container.servlet.ServletContainer;

public class DownloadExcelRESTFul {
	
	private static final Logger logger = LogManager.getLogger(DownloadExcelRESTFul.class);

	public static void main(String[] args) throws Exception {
		
		logger.info("Application is starting");
		
		ApplicationContext context = new ClassPathXmlApplicationContext("spring-context.xml");
		
		context.getBean(PropertyLoader.class).storeAllProperties(context);
						
		startJettyServer();
	    
	    ((AbstractApplicationContext) context).close();

	}

	private static void startJettyServer() throws Exception {
		
		ServletHolder sh = new ServletHolder(ServletContainer.class);    
	    sh.setInitParameter("com.sun.jersey.config.property.resourceConfigClass", "com.sun.jersey.api.core.PackagesResourceConfig");
	    sh.setInitParameter("com.sun.jersey.config.property.packages", "com.javahonk.report.restulservice");
	    sh.setInitParameter("com.sun.jersey.api.json.POJOMappingFeature", "true");
	    
	    Server server = new Server(10002);
	    ServletContextHandler servletContextHandler = new ServletContextHandler(server, "/", ServletContextHandler.SESSIONS);
	    servletContextHandler.addServlet(sh, "/*");
	    server.start();
	}	
	
}
  • That’s it. To start the applicatoin right click DownloadExcelRESTFul.java run as java application you will see on console it will start Jetty server on port 10002 as below:

Download Excel RESTFul Service

  • Now we are ready to download XLSX report. Open your browser and enter below URL: http://localhost:10002/RESTFul/downloadxls you will see XLSX sheet downloaded as below:

Download Excel RESTFul Service

  • Open XLSX sheet to see its content:

Download Excel RESTFul Service

  • For more information on RESTFul service please visit Spring documentation here

download Download Project:  DownloadExcelRESTFulService

By Techi Works

Related Post

Uncategorized

How to prevent Deadlock Java

Techi Works
Uncategorized

Face to Face Java Interview Series 57

Techi Works
Uncategorized

Face to Face Java Interview Series 56

Techi Works
2 thoughts on “Download Excel RESTFul Service”

  1. File is getting downloaded successfully but could see one more file is getting created inside project folder.How to avoid that?

    Reply

    1. I trying using spring boot framework instead of excel in response I am getting JSON as shown below
      {
      “statusType”: “OK”,
      “entity”: “C:\\my-api\\dce8b56654e04eea954f9b34782dad6f_invoice_Sat_Jun_30_22_54_53_IST_2018.xlsx”,
      “entityType”: “java.io.File”,
      “metadata”: {
      “Content-Disposition”: [
      “attachment; filename=JavaHonk.xlsx”
      ],
      “Content-Type”: [
      “application/vnd.ms-excel”
      ]
      },
      “status”: 200
      }

      Reply

Leave a Reply

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

You Missed

Interview Java

Mizuho Fixed Income Team Lead position Interview coding round questions

Interview Java

BNS Paribas Senior java Developer interview questions

KAFKA Messaging

Scalability in Apache Kafka Horizontal Scaling Explained

KAFKA Messaging

High Throughput Apache Kafka Handle High-Velocity Data Streams