Spring Boot Web Application Download Excel File

Tags: Java Spring Boot Apache POI Excel Download

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

Table of contents

  1. Creating Spring Boot web project using Spring Tool Suite
  2. Set up dependencies for gradle or maven project
  3. Creating data model class
  4. Export Excel file from list of customer objects
  5. Implement Controller Class
  6. Implement HTML View
  7. Complete Application
  8. Download Source Code

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

You can also creating new Spring Boot project using Spring initializr online tool at start.spring.io

Set up dependencies for gradle or maven project

If you are using Gradle build, add the following dependencies into the build.gradle file. 

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 build, add the following dependencies into the pom.xml file. 

<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;
		}
	}
}

Implement Controller Class

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;
    }
}

Implement HTML View

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>

Complete Application

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

Download Source Code

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

or clone at:

git clone https://github.com/simplesolutiondev/DownloadExcelFile.git

or download at:

Download Source Code

Happy Coding 😊

Spring Boot Download Excel File Export from MySQL Database

Spring Boot Web Application Download CSV File

Writing Excel File Using Apache POI Library in Java

Spring Boot Web Application Export and Download Text File

Spring Boot Web Application Export and Download JSON File

Creating Spring Boot Application with Spring Tool Suite

Java Create Excel File .xlsx using Apache POI

Java Read Excel File using Apache POI

Java Read Excel Workbook from File using Apache POI

Java Read Excel Workbook from InputStream using Apache POI

Java Read Password Protected Excel File using Apache POI

Java How to Iterate over Sheets Rows and Cells of Excel file using Apache POI

Java Add Rows to Existing Excel File using Apache POI

Java Add Sheet to Existing Excel File using Apache POI

Java Remove Sheet from Existing Excel File using Apache POI

Java Create Formula Excel Cells using Apache POI