Excel File – Download from SpringBoot RestAPI + Apache POI + MySQL

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-excel-feature-image

In the tutorial, we show you how to create a SpringBoot RestAPIs application that uses Spring JPA to get data from MySQL records and uses Apache POI library to write data to a Excel file.

Related posts:
Java – How to read/write Excel file with Apache POI
How to use Spring JPA MySQL | Spring Boot
SpringBoot – Upload/Download MultipartFile to FileSystem – Bootstrap 4 + JQuery Ajax

Technologies

– Spring Boot – 2.0.6.RELEASE
– Spring JPA
– MySQL
– Apache POI

Practice

Project Structure ->

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-project-structure

Create SpringBoot project

Use SpringToolSuite to create a SpringBoot project with below dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>      
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>   
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>    
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

Customer Model

Customer.java ->


package com.ozenero.exceldownload.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "customers")
public class Customer {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	
	@Column(name = "name")
	private String name;
	
	@Column(name = "address")
	private String address;
	
	@Column(name = "age")
	private int age;
 
	public Customer() {
	}
 
	public Customer(Long id, String name, String address, int age) {
		this.id = id;
		this.name = name;
		this.address = address;
		this.age = age;
	}
 
	public Long getId() {
		return id;
	}
 
	public void setId(Long id) {
		this.id = id;
	}
 
	public String getName() {
		return name;
	}
 
	public void setName(String name) {
		this.name = name;
	}
 
	public String getAddress() {
		return address;
	}
 
	public void setAddress(String address) {
		this.address = address;
	}
 
	public int getAge() {
		return age;
	}
 
	public void setAge(int age) {
		this.age = age;
	}
 
	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]";
	}
 
}

JPA Customer Repository

CustomerRepository.java ->


package com.ozenero.exceldownload.repository;

import org.springframework.data.repository.CrudRepository;

import com.ozenero.exceldownload.model.Customer;
 
public interface CustomerRepository extends CrudRepository{
}

Implement Excel Generator

ExcelGenerator.java ->


package com.ozenero.exceldownload.util;

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.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
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 com.ozenero.exceldownload.model.Customer;

public class ExcelGenerator {
	
	public static ByteArrayInputStream customersToExcel(List customers) throws IOException {
		String[] COLUMNs = {"Id", "Name", "Address", "Age"};
		try(
				Workbook workbook = new XSSFWorkbook();
				ByteArrayOutputStream out = new ByteArrayOutputStream();
		){
			CreationHelper createHelper = workbook.getCreationHelper();
	 
			Sheet sheet = workbook.createSheet("Customers");
	 
			Font headerFont = workbook.createFont();
			headerFont.setBold(true);
			headerFont.setColor(IndexedColors.BLUE.getIndex());
	 
			CellStyle headerCellStyle = workbook.createCellStyle();
			headerCellStyle.setFont(headerFont);
	 
			// Row for Header
			Row headerRow = sheet.createRow(0);
	 
			// Header
			for (int col = 0; col < COLUMNs.length; col++) {
				Cell cell = headerRow.createCell(col);
				cell.setCellValue(COLUMNs[col]);
				cell.setCellStyle(headerCellStyle);
			}
	 
			// CellStyle for Age
			CellStyle ageCellStyle = workbook.createCellStyle();
			ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
	 
			int rowIdx = 1;
			for (Customer customer : customers) {
				Row row = sheet.createRow(rowIdx++);
	 
				row.createCell(0).setCellValue(customer.getId());
				row.createCell(1).setCellValue(customer.getName());
				row.createCell(2).setCellValue(customer.getAddress());
	 
				Cell ageCell = row.createCell(3);
				ageCell.setCellValue(customer.getAge());
				ageCell.setCellStyle(ageCellStyle);
			}
	 
			workbook.write(out);
			return new ByteArrayInputStream(out.toByteArray());
		}
	}
}

Implement RestAPI Controller

- CustomerExcelDownloadRestAPI.java ->


package com.ozenero.exceldownload.controller;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.ozenero.exceldownload.model.Customer;
import com.ozenero.exceldownload.repository.CustomerRepository;
import com.ozenero.exceldownload.util.ExcelGenerator;

@RestController
@RequestMapping("/api/customers")
public class CustomerExcelDownloadRestAPI {
    @Autowired
    CustomerRepository customerRepository;
 
    @GetMapping(value = "/download/customers.xlsx")
    public ResponseEntity excelCustomersReport() throws IOException {
        List customers = (List) customerRepository.findAll();
		
		ByteArrayInputStream in = ExcelGenerator.customersToExcel(customers);
		// return IOUtils.toByteArray(in);
		
		HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Disposition", "attachment; filename=customers.xlsx");
		
		 return ResponseEntity
	                .ok()
	                .headers(headers)
	                .body(new InputStreamResource(in));
    }
}

Implement Download View

– Create ViewController.java ->


package com.ozenero.exceldownload.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class ViewController {

	@RequestMapping("/")
	public String home() {
		return "home";
	}
}

– Create .html view home.html ->

<html>
	<head>
		<title>SpringBoot Excel</title>
	</head>
	<body>
		<h3>Download Excel Customers File</h3>
		<div>
			<a href="http://localhost:8080/api/customers/download/customers.xlsx">Customers .XLSX</a>
		</div>
	</body>
</html>

Initial Customers

– In main class, we use CommandLineRunner to init Customer’s records:


package com.ozenero.exceldownload;

import java.util.Arrays;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.ozenero.exceldownload.model.Customer;
import com.ozenero.exceldownload.repository.CustomerRepository;

@SpringBootApplication
public class SpringJpaExcelDownloaderApplication implements CommandLineRunner {

	@Autowired
	CustomerRepository repository;
	
	public static void main(String[] args) {
		SpringApplication.run(SpringJpaExcelDownloaderApplication.class, args);
	}
	
    @Override
    public void run(String... args) throws Exception {
    	
    	List customers = Arrays.asList(
    			new Customer(Long.valueOf(1), "Jack Smith", "Massachusetts", 23),
    			new Customer(Long.valueOf(2), "Adam Johnson", "New York", 27),
    			new Customer(Long.valueOf(3), "Katherin Carter", "Washington DC", 26),
    			new Customer(Long.valueOf(4), "Jack London", "Nevada", 33), 
    			new Customer(Long.valueOf(5), "Jason Bourne", "California", 36));
    	
		// save a list of Customers
		repository.saveAll(customers);
    }	
}

Database Configuration

application.properties ->


spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=12345
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create-drop

Run & Check Results

Run the SpringBoot project,

-> MySQL’s records:

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-mysql-tables

-> Excel Downloaded File:

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-view

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-network-logs

spring-boot-download-excel-file-restapi-mysql-spring-jpa-apache.poi-excel-downloaded-file

SourceCode

SpringJpaExcelDownloader

94 thoughts on “Excel File – Download from SpringBoot RestAPI + Apache POI + MySQL”

  1. You should set Content-Type in order to have a valid mime Type.
    For example
    headers.add(“Content-Type”, “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);

  2. I tried this, but getting error. Actually I have configured plsql db in application.properties seems profile is not activated.
    I want to pull the record from plsql and download as excel. Please help me.

    INFO: No Active profile set, falling back to default profiles: default
    INFO: Refreshing org.springframework.boot.context.embedded.annotationConfigEmbeddedWebapplicationcontect@66fd8833:startupdate ; root of context hierarchy
    WARN: Exception encountered during context initialization – cancelling refresh attempt: org.springframework.beans.factory.BeanDefinitionStoreException:Failed to parse configuration class [com.dataextract.SpringJpaExcelDownloaderApplication]; nested exception is java.io.FileNotFoundException: Class path resource[application.properties] cannot be opened because it does not exist.
    ERROR: Destroy method on bean withname ‘org.springframework.boot.autoconfigure.internalCachingMetadataReaderFactory’ threw an exception.

    I am new to spring boot. i want to finish this for my organization. Please help me.

  3. With little modification in code , able to create xls file . able to hit end point url from postman directly.Thanks a lot.

  4. Hi! I understand this is kind of off-topic however I needed to ask.
    Does operating a well-established blog such as
    yours take a lot of work? I’m brand new to blogging but I do write in my diary on a daily basis.

    I’d like to start a blog so I can easily share my own experience and feelings
    online. Please let me know if you have any ideas or
    tips for new aspiring bloggers. Appreciate it!

  5. Having read this I believed it was rather enlightening.

    I appreciate you taking the time and energy to put this short article together.
    I once again find myself spending way too much time both reading and leaving comments.
    But so what, it was still worthwhile!

  6. Hi would you mind letting me know which web host you’re
    using? I’ve loaded your blog in 3 different browsers and I must say this blog loads a lot quicker then most.
    Can you suggest a good web hosting provider at a fair price?
    Many thanks, I appreciate it!

  7. It’s really very difficult in this full of activity life to
    listen news on Television, thus I only use world wide web for that purpose, and get the hottest news.

  8. Hello, i think that i saw you visited my web site thus i came to “return the favor”.I am
    trying to find things to enhance my site!I suppose
    its ok to use a few of your ideas!!

  9. May I simply just say what a comfort to find somebody that really
    understands what they are talking about on the internet.
    You actually know how to bring an issue to light and make it
    important. More and more people have to read this and understand this side of
    your story. I can’t believe you’re not more popular since you
    definitely have the gift.

  10. Excellent blog here! Also your site loads up very fast!
    What host are you using? Can I get your affiliate link to your host?

    I wish my web site loaded up as quickly as yours lol

  11. I’ll immediately clutch your rss as I can not to find your
    email subscription link or e-newsletter service. Do you’ve any?
    Please let me understand in order that I may just subscribe.
    Thanks.

  12. I’m not that much of a online reader to be honest but
    your blogs really nice, keep it up! I’ll go ahead and bookmark your
    site to come back in the future. Many thanks

  13. My brother recommended I may like this web site.
    He used to be entirely right. This publish truly made my day.

    You can not imagine just how much time I had spent for this info!
    Thanks!

  14. Hi there, just became aware of your blog through Google, and found that it is truly informative.
    I am going to watch out for brussels. I will appreciate if you continue this in future.
    A lot of people will be benefited from your writing.
    Cheers!

  15. Nice post. I used to be checking continuously this blog and I am impressed!

    Extremely useful information particularly the
    remaining section 🙂 I deal with such info much. I used to
    be looking for this certain information for a long time.
    Thanks and best of luck.

  16. Magnificent goods from you, man. I’ve take note your stuff prior to
    and you are simply too wonderful. I really like what you’ve acquired right here,
    certainly like what you’re saying and the way wherein you
    are saying it. You make it entertaining and you
    continue to care for to stay it wise. I cant wait to read much more from you.

    This is really a wonderful web site.

  17. This design is steller! You certainly know how to
    keep a reader amused. Between your wit and your
    videos, I was almost moved to start my own blog (well, almost…HaHa!) Wonderful job.
    I really enjoyed what you had to say, and more than that, how you presented it.
    Too cool!

  18. Attractive section of content. I just stumbled upon your
    site and in accession capital to assert that I acquire actually enjoyed
    account your blog posts. Any way I’ll be subscribing to your
    feeds and even I achievement you access consistently rapidly.

  19. Hi there! Do you know if they make any plugins
    to protect against hackers? I’m kinda paranoid about losing everything I’ve
    worked hard on. Any recommendations?

  20. We’re a group of volunteers and starting a new scheme in our community.
    Your web site offered us with valuable information to work
    on. You have done an impressive job and our whole community will be grateful to you.

  21. Does your site have a contact page? I’m having
    trouble locating it but, I’d like to send you an e-mail.
    I’ve got some suggestions for your blog you might
    be interested in hearing. Either way, great site and I look
    forward to seeing it grow over time.

  22. Thanks , I have just been looking for information about this topic for ages and yours is the greatest I’ve discovered
    so far. But, what concerning the conclusion? Are you sure in regards to the source?

  23. Thank you for some other excellent article. Where else could anybody get that type of info in such
    an ideal method of writing? I have a presentation next week,
    and I’m at the look for such information.

  24. magnificent points altogether, you simply gained a emblem new reader.

    What might you recommend in regards to your publish that you made a few
    days in the past? Any positive?

  25. I’m not sure where you are getting your info, however good topic.
    I must spend some time learning much more or working out more.
    Thank you for excellent info I was on the lookout for this information for my mission.

  26. When I originally commented I clicked the “Notify me when new comments are added”
    checkbox and now each time a comment is added I get several emails with the same
    comment. Is there any way you can remove me from that service?
    Bless you!

  27. It’s perfect time to make some plans for the future and it is time to be happy.
    I have read this post and if I could I wish to
    suggest you few interesting things or suggestions.
    Maybe you could write next articles referring to this article.
    I desire to read even more things about it!

  28. Have you ever considered about including a little bit more than just your articles?
    I mean, what you say is fundamental and everything. However imagine
    if you added some great graphics or video clips to give your posts more, “pop”!
    Your content is excellent but with pics and clips, this website could undeniably be one of the most beneficial in its field.

    Terrific blog!

  29. magnificent post, very informative. I wonder why the other experts of this sector don’t notice this. You should continue your writing. I’m confident, you’ve a great readers’ base already!

  30. Howdy, I believe your website may be having internet browser compatibility issues.
    When I take a look at your website in Safari, it looks fine however,
    when opening in IE, it has some overlapping issues. I simply wanted to give you a quick heads up!
    Apart from that, excellent site!

  31. I have realized some new items from your internet site about computers. Another thing I’ve always considered is that laptop computers have become an item that each residence must have for a lot of reasons. They offer convenient ways to organize homes, pay bills, shop, study, hear music and even watch shows. An innovative approach to complete all of these tasks is by using a laptop. These computers are mobile ones, small, robust and lightweight.

  32. Thanks , I have recently been searching for information about this subject for ages and yours is the greatest I have found out till now.
    However, what in regards to the bottom line? Are you certain concerning the source?

  33. Woah! I’m really loving the template/theme of this website.
    It’s simple, yet effective. A lot of times it’s tough
    to get that “perfect balance” between usability and appearance.
    I must say you’ve done a superb job with this. In addition, the blog loads extremely fast for me on Internet explorer.
    Outstanding Blog!

  34. Cool blog! Is your theme custom made or did you download it from
    somewhere? A theme like yours with a few simple tweeks would really make
    my blog shine. Please let me know where you got your theme.
    Kudos

  35. Hello there, I found your site by means of Google at the same time as
    looking for a comparable matter, your website got here up, it seems to be good.
    I have bookmarked it in my google bookmarks.
    Hello there, just become aware of your weblog via Google, and located
    that it is truly informative. I’m gonna watch out for brussels.
    I’ll be grateful if you continue this in future.
    Numerous other people shall be benefited out of your writing.
    Cheers!

  36. Hey there! I realize this is sort of off-topic but I had to ask.
    Does managing a well-established website like yours take
    a lot of work? I am completely new to running a blog however I
    do write in my diary every day. I’d like to start a blog so I can easily share
    my own experience and views online. Please let me know if you have any
    kind of suggestions or tips for new aspiring bloggers.
    Appreciate it!

  37. I just like the valuable information you provide for
    your articles. I will bookmark your blog and test again right here frequently.
    I’m rather sure I’ll be informed many new stuff right here!
    Good luck for the next!

  38. I’m not sure why but this web site is loading very slow for me.
    Is anyone else having this problem or is it a problem on my end?
    I’ll check back later and see if the problem still exists.

  39. I loved as much as you’ll receive carried out right here. The sketch is tasteful, your authored
    material stylish. nonetheless, you command get bought an edginess over that you
    wish be delivering the following. unwell unquestionably come further formerly again as exactly the same nearly a lot often inside case you shield this hike.

  40. Hello there, just became alert to your blog through Google,
    and found that it is truly informative. I am going to watch
    out for brussels. I will be grateful if you continue this in future.
    A lot of people will be benefited from your writing.
    Cheers!

  41. I have read a few good stuff here. Definitely worth bookmarking for revisiting.
    I wonder how a lot effort you set to make this type of wonderful informative website.

  42. I was recommended this website by my cousin. I’m not sure whether this post is written by
    him as nobody else know such detailed about my difficulty.
    You’re wonderful! Thanks!

  43. It’s actually a great and useful piece of info.
    I am glad that you simply shared this useful information with us.
    Please stay us up to date like this. Thanks for sharing.

  44. I was extremely pleased to uncover this great site.
    I want to to thank you for your time just for this wonderful read!!
    I definitely enjoyed every little bit of it and I have you saved to fav to look at new information on your web site.

  45. Hi, i feel that i noticed you visited my website so i got here to go back
    the favor?.I am attempting to find issues to
    enhance my website!I suppose its good enough to make
    use of some of your ideas!!

  46. I am not sure where you’re getting your info, but
    good topic. I needs to spend some time learning more or understanding more.
    Thanks for fantastic info I was looking for this information for
    my mission.

  47. What’s Happening i am new to this, I stumbled upon this I’ve found It absolutely useful and it has helped
    me out loads. I hope to give a contribution & assist
    different customers like its aided me. Good job.

  48. With havin so much written content do you ever run into any issues
    of plagorism or copyright infringement? My blog has a lot
    of exclusive content I’ve either authored myself or outsourced but it looks like a lot of it is popping it up all over the internet without my authorization. Do you
    know any methods to help stop content from being ripped
    off? I’d certainly appreciate it.

  49. Generally I don’t learn post on blogs, however I would like to say that
    this write-up very compelled me to take a look at and do it!
    Your writing taste has been amazed me. Thank you, very nice post.

  50. Wow that was strange. I just wrote an incredibly long comment but after I clicked submit my comment didn’t show up.
    Grrrr… well I’m not writing all that over again. Regardless, just wanted to
    say great blog!

  51. Hey, I think your blog might be having browser compatibility issues.

    When I look at your blog in Ie, it looks fine but when opening in Internet Explorer, it has some overlapping.

    I just wanted to give you a quick heads up!
    Other then that, wonderful blog!

  52. Hello there! This is kind of off topic but I need some guidance from an established blog. Is it very difficult to set up your own blog? I’m not very techincal but I can figure things out pretty fast. I’m thinking about setting up my own but I’m not sure where to begin. Do you have any ideas or suggestions? Thanks

  53. What抯 Happening i am new to this, I stumbled upon this I’ve found It absolutely useful and it has helped me out loads. I hope to contribute & assist other users like its aided me. Great job.

  54. Howdy! This is kind of off topic but I need some help from an established blog.
    Is it hard to set up your own blog? I’m not very
    techincal but I can figure things out pretty quick. I’m thinking about
    making my own but I’m not sure where to start. Do you have any tips or suggestions?
    Appreciate it

  55. Thank you for the auspicious writeup. It if truth be told used to
    be a leisure account it. Look complicated to more delivered agreeable from you!
    However, how can we be in contact?

Leave a Reply

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