Spring Boot Web Application Download Excel File

In this article we will go through step by step to create Spring Boot web application and implement downloading Excel file feature. I use Spring Tool Suite 4 for creating the application and code editor for demo.

Creating Spring Boot web project using Spring Tool Suite

Open Spring Tool Suite on your machine and click on “Create a project…” or choose menu File -> New -> Project

Create Spring Boot Web Application

On New Project window select Spring Starter Project and click Next

Create Spring Boot Web Application

On New Spring Starter Project window input the project information as below and click Next

New Spring Starter Project

On New Spring Starter Project Dependencies choose Thymeleaf and Spring Web dependencies and click Next

New Spring Starter Project Dependencies

Keep the information on next window as default and click Finish

New Spring Starter Project Finish

The application create success you can see the new project structure as below

New Spring Starter Project Finish

Set up dependencies for gradle or maven project

Open build.gradle file on your project and add below dependencies

compile group: 'org.apache.poi', name: 'poi', version: '4.0.1'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.0.1'
compile group: 'commons-io', name: 'commons-io', version: '2.6'

If you are using maven then adding below xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.6</version>
</dependency>

Creating data model class

In this example project we implement downloading function to download a list of customer. Firstly creating Customer class as below.

package simplesolution.dev.model;

public class Customer {
	private String firstName;
	private String lastName;
	private String mobileNumber;
	private String email;
	
	public Customer(String firstName, String lastName, String mobileNumber, String email) {
		super();
		this.firstName = firstName;
		this.lastName = lastName;
		this.mobileNumber = mobileNumber;
		this.email = email;
	}
	
	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 getMobileNumber() {
		return mobileNumber;
	}
	public void setMobileNumber(String mobileNumber) {
		this.mobileNumber = mobileNumber;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	
}

Export Excel file from list of customer objects

Implement ExcelFileExporter class as below to export list of Customer into a ByteArrayInputStream of Excel file.

package simplesolution.dev.exporter;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import simplesolution.dev.model.Customer;

public class ExcelFileExporter {
	
	public static ByteArrayInputStream contactListToExcelFile(List<Customer> customers) {
		try(Workbook workbook = new XSSFWorkbook()){
			Sheet sheet = workbook.createSheet("Customers");
			
			Row row = sheet.createRow(0);
	        CellStyle headerCellStyle = workbook.createCellStyle();
	        headerCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
	        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	        // Creating header
	        Cell cell = row.createCell(0);
	        cell.setCellValue("First Name");
	        cell.setCellStyle(headerCellStyle);
	        
	        cell = row.createCell(1);
	        cell.setCellValue("Last Name");
	        cell.setCellStyle(headerCellStyle);
	
	        cell = row.createCell(2);
	        cell.setCellValue("Mobile");
	        cell.setCellStyle(headerCellStyle);
	
	        cell = row.createCell(3);
	        cell.setCellValue("Email");
	        cell.setCellStyle(headerCellStyle);
	        
	        // Creating data rows for each customer
	        for(int i = 0; i < customers.size(); i++) {
	        	Row dataRow = sheet.createRow(i + 1);
	        	dataRow.createCell(0).setCellValue(customers.get(i).getFirstName());
	        	dataRow.createCell(1).setCellValue(customers.get(i).getLastName());
	        	dataRow.createCell(2).setCellValue(customers.get(i).getMobileNumber());
	        	dataRow.createCell(3).setCellValue(customers.get(i).getEmail());
	        }
	
	        // Making size of column auto resize to fit with data
	        sheet.autoSizeColumn(0);
	        sheet.autoSizeColumn(1);
	        sheet.autoSizeColumn(2);
	        sheet.autoSizeColumn(3);
	        
	        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
	        workbook.write(outputStream);
	        return new ByteArrayInputStream(outputStream.toByteArray());
		} catch (IOException ex) {
			ex.printStackTrace();
			return null;
		}
	}
}

In order to public the web page for downloading Excel file we create the DownloadExcelController as below.

package simplesolution.dev.controller;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.compress.utils.IOUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import simplesolution.dev.exporter.ExcelFileExporter;
import simplesolution.dev.model.Customer;

@Controller
public class DownloadExcelController {
	
	@RequestMapping("/")
    public String index() {
        return "index";
    }
	
	@GetMapping("/download/customers.xlsx")
    public void downloadCsv(HttpServletResponse response) throws IOException {
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment; filename=customers.xlsx");
        ByteArrayInputStream stream = ExcelFileExporter.contactListToExcelFile(createTestData());
        IOUtils.copy(stream, response.getOutputStream());
    }

	private List<Customer> createTestData(){
    	List<Customer> customers = new ArrayList<Customer>();
    	customers.add(new Customer("Vernon", "Barlow", "0123456789", "test1@simplesolution.dev"));
    	customers.add(new Customer("Maud", "Brock", "0123456788", "test2@simplesolution.dev"));
    	customers.add(new Customer("Chyna", "Cowan", "0123456787", "test3@simplesolution.dev"));
    	customers.add(new Customer("Krisha", "Tierney", "0123456786", "test4@simplesolution.dev"));
    	customers.add(new Customer("Sherry", "Rosas", "0123456785", "test5@simplesolution.dev"));
    	return customers;
    }
}

Creating new view file in your project at resources\templates\index.html as below

<html>
<head>
    <title>Download Excel File - simplesolution.dev</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
</head>
<body class="container">
	<h2>Spring Boot Web Application Download Excel File</h2>
	<a href="http://localhost:8080/download/customers.xlsx" class="btn btn-primary">Download</a>
</body>
</html>

At this step you have finished the project with struture as below

Finished Project

Run and run the project then open http://localhost:8080/ on browser you will got the web page as below

Download Excel File web page

Click on Download button to download customers.xlsx Open file on Excel application you will get the result as screenshot below.

Open by Excel application

The source code in this article can be found at: github.com/simplesolutiondev/DownloadExcelFile

or download at:

Download Source Code

Happy Coding 😊

Spring Boot Web Application Download CSV File

Writing Excel File Using Apache POI Library in Java

Creating Spring Boot Application with Spring Tool Suite