Generate a Excel file from Spring


We often need to generate a spreadsheet file from server and allow users to download the file. Here is how to do that with Spring using the Apache POI library.

Dependency:

We need to add the following dependencies in our spring application. Here is the Gradle dependencies.

compile group: 'org.apache.poi', name: 'poi', version: '3.15'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.15'Once we add the dependencies we'll be able to access the POI library in our application.

Generate WorkBook

We need to generate a Workbook object in POI. Here is a sample method that returns a Workbook object

public Workbook downloadPersonList() throws IOException {

    // sample list. this can be the output of a DB query
    List<Person> personList = new ArrayList<>(); 
    personList.add(new Person("nayan", 29));

    personList.add(new Person("rafiq", 35));
    personList.add(new Person("sundor", 18));

    XSSFWorkbook wb = new XSSFWorkbook(); 
    XSSFSheet mySheet = wb.createSheet(); 

    // get the last row number to append new data 
    int rownum = mySheet.getLastRowNum(); 

    Row headerRow = mySheet.createRow(rownum++); 
    Cell headerRowCell1 = headerRow.createCell(0); 
    Cell headerRowCell2 = headerRow.createCell(1); 
    headerRowCell1.setCellValue("Name");  
    headerRowCell2.setCellValue("Age"); 

    for(Person person : personList){ 
        Row row = mySheet.createRow(rownum++); 
        row.createCell(0).setCellValue(person.getName());     
        row.createCell(1).setCellValue(person.getAge()); 
    } 
    return wb; 
}

Allow users to download the Workbook

Now we need to send the workbook as response from spring controller. We also need to send some additional headers so that user gets a save file prompt in their browser.

@RequestMapping(value="person/download",method=RequestMethod.GET) 
public void getPersonList(HttpServletResponse response) throws IOException {
    Workbook workbook = reportService.downloadPersonList(); // call to the above method 
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); 
    response.setHeader("Content-Disposition", "inline; filename=person-list.xlsx"); 
    ServletOutputStream outputStream = response.getOutputStream(); 
    workbook.write(outputStream); 
    outputStream.close(); 
    workbook.close(); 
}
 
That's it!
 

Comments

Post a Comment

Popular posts from this blog

Run tasks in background in Spring

Why background task? There are tons of examples where we often need to run a task in background e.g. sending an email, sms or notification, writing some log in file etc. If a task is not required to maintain atomicity and can be executed after some delay, it's a good candidate to run in background.   How to run tasks in background in Java? In Java, the easiest way to run a task in background is to create a new thread and execute it. It's quite simple. Here is how to do this: new Thread(new Runnable(){ @Override public void run(){ // your background task } }).start(); It's easy! But there are some maintenance problem with the above code. The major problem is - there is no way to control how many background tasks we want to run simultaneously. For example, if 1000 users registers in your application at a time and you create 1000 background threads to send them a confirmation email, maybe you'll be in trouble. So you ne
Read more

Conditional field inclusion in Jackson and Spring Boot

When we write JSON API with spring boot, we often need to customize which fields should be included in our response JSON and which should not. For example, suppose we've a Model like the following: public class User {     private Long id;     private String name;     private String password;     private List<String> children;     // getters and setters here } Now we've the following controller: @RestController @RequestMapping("/api/users") public class UserController {     @GetMapping     public List<User> userList(){         User user = new User();         user.setId(10L);         user.setName("Rafiqunnabi Nayan");         user.setPassword("abcd");         user.setChildren(Arrays.asList("Child 1", "Child 2"));         return Arrays.asList(user);     }     @GetMapping("{id}")     public User userDetails(@PathVariable Long id){         User user = new User();         user.set
Read more

How to configure Wildfly 10 to use MySQL

Follow these steps to configure MySQL in Wildfly 10 Step 1 : Download MySQL connector jar from mysql downloads page . Step 2 : Go to modules\system\layers\base\com of your Wildfly home Step 3 : Create folder like mysql\main and go to mysql\main Step 4 : Place the downloaded connector jar in the main folder  Step 5 : Create a file named module.xml in the main folder and put the following content in it. Set the path variable so that it matches the name of the jar file. <?xml version="1.0" encoding="UTF-8"?> <module xmlns="urn:jboss:module:1.1" name="com.mysql">     <resources>         <resource-root path="mysql-connector-java-5.1.39-bin.jar"/>                  </resources>     <dependencies>         <module name="javax.api"/>         <module name="javax.transaction.api"/>     </dependencies> </module> Step 6 : Go to standalone\configuration dir
Read more