Hello everyone,

Greetings today!

Today I will show you how to export data to Excel using Spring Boot Rest API and Apache Poi library. Let's get started.

First, go to Spring Initializr, create a Spring Boot project with the following dependencies, unzip the project, and open it in your preferred IDE.


  • Add database dependencies and configure database properties in the application.properties file.

  <dependency>
	<groupId>oracle</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>11.2.0.3</version>
  </dependency>


spring.datasource.initialize=true
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=system
spring.datasource.password=add-your-password
spring.datasource.driver-class-name
	=oracle.jdbc.driver.OracleDriver
spring.jpa.properties.hibernate.dialect
	=org.hibernate.dialect.Oracle10gDialect
spring.jpa.hibernate.ddl-auto = update
spring.datasource.initialization-mode=always

  • Add Apache POI dependencies to your pom.xml file

  <dependency>
	<groupIdvorg.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.2</version>
  </dependency>
  <dependency>
    <groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
  </dependency>

  • Add a User.java model entity file that maps to the test_user table in the database and also inserts some data using data.sql.

package com.usermgt.model;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

import javax.persistence.*;

@Entity
@Table(name = "test_user")
@Getter
@Setter
@ToString
public class User {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE,
    	generator = "id_Sequence")
    @SequenceGenerator(name = "id_Sequence",
    	sequenceName = "test_user_id_seq")
    private Long id;

    private String email;

    private String name;

    private String password;
}

Create a data.sql file in the /src/main/resources folder and add some records to the test_user table.

insert into test_users 
	values(1,"test77@yopmail.com","Rohan","Pass@123");
insert into test_users 
	values(2,"test0444@yopmail.com","Pooja","Pass@w44");
insert into test_users 
	values(3,"test5888@yopmail.com","Sanjay","Pass@6555");

  • Create a UserRepository, an interface that extends JpaRepository to retrieve data from the test_user table.

package com.usermgt.repository;

import com.usermgt.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends
	JpaRepository<User,Long> {
}

  • Create a class ResourceDTO.java with Resource and MediaType.

package com.usermgt.dto;

import lombok.Builder;
import lombok.Getter;
import lombok.Setter;
import org.springframework.core.io.Resource;
import org.springframework.http.MediaType;

@Builder
@Getter
@Setter
public class ResourceDTO {

    private Resource resource;

    private MediaType mediaType;

}

  • Create the ExcelService.java interface and its implementation class ExcelServiceImpl.java with the logic to create an Excel.

package com.usermgt.service;

import com.usermgt.dto.ResourceDTO;

public interface ExcelService {

    ResourceDTO exportUsers();
}

ExcelServiceImpl.java has various methods

  • The prepareHeaders method creates the first row of rows to use as headers, sets the font to bold, and then fills in the header cell values.

  private void prepareHeaders(Workbook workbook, 
  	Sheet sheet, String... headers) {
    
    Row headerRow=sheet.createRow(0);
    Font font=workbook.createFont();
    font.setBold(true);
    font.setFontName("Arial");

    CellStyle cellStyle=workbook.createCellStyle();
    cellStyle.setFont(font);

    int columnNo=0;
    for(String header:headers){
       Cell headerCell=headerRow.createCell(columnNo++);
       headerCell.setCellValue(header);
       headerCell.setCellStyle(cellStyle);
      }
    }

  • The populateCell method populates a cell value with the specified cell style under the specified row.
  private void populateCell(Sheet sheet,Row row,
  	int columnNo,String value,CellStyle cellStyle){
    
    Cell cell=row.createCell(columnNo);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(value);
    sheet.autoSizeColumn(columnNo);
}
  • The populateUserData method creates a new row for each user record and calls the populateCell method on the newly created row multiple times for each column of the test_user table.
    private void populateUserData
    	(Workbook workbook, Sheet sheet, 
        List<User> userList) {
    
    int rowNo=1;
    Font font=workbook.createFont();
    font.setFontName("Arial");

    CellStyle cellStyle=workbook.createCellStyle();
    cellStyle.setFont(font);

    for(User user:userList){
        int columnNo=0;
        Row row=sheet.createRow(rowNo);
        populateCell(sheet,row,columnNo++,
        	String.valueOf(user.getId()),cellStyle);
        populateCell(sheet,row,columnNo++,
        	user.getName(),cellStyle);
        populateCell(sheet,row,columnNo++,
        	user.getEmail(),cellStyle);
        rowNo++;
    }
}


  • Below is the complete code of ExcelServiceImpl.java for your reference.
package com.usermgt.service.impl;

@Service
@Transactional(readOnly = true)
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public ResourceDTO exportUsers() {
        List<User> userList= userRepository.findAll();
        Resource resource=prepareExcel(userList);
        return ResourceDTO.builder().resource(resource).
           mediaType(MediaType.parseMediaType
		 ("application/vnd.openxmlformats-officedocument
         	.spreadsheetml.sheet")).build();
    }

    private Resource prepareExcel(List<User> userList){
        Workbook workbook=new XSSFWorkbook();
        Sheet sheet=workbook.createSheet("USERS");

        prepareHeaders(workbook,sheet,"Id","Email","Name");
        populateUserData(workbook,sheet,userList);

        try(ByteArrayOutputStream byteArrayOutputStream
        	=new ByteArrayOutputStream()){
            
          workbook.write(byteArrayOutputStream);
          return new 
           ByteArrayResource
           	(byteArrayOutputStream.toByteArray());
        }catch (IOException e){
            e.printStackTrace();
            throw new RuntimeException
            	("Error while generating excel.");
        }
    }

    private void populateUserData(Workbook workbook, Sheet sheet, 
    		List<User> userList) {
        
        int rowNo=1;
        Font font=workbook.createFont();
        font.setFontName("Arial");

        CellStyle cellStyle=workbook.createCellStyle();
        cellStyle.setFont(font);

        for(User user:userList){
            int columnNo=0;
            Row row=sheet.createRow(rowNo);
            populateCell(sheet,row,columnNo++,
            	String.valueOf(user.getId()),cellStyle);
            populateCell(sheet,row,columnNo++,
            	user.getName(),cellStyle);
            populateCell(sheet,row,columnNo++,
            	user.getEmail(),cellStyle);
            rowNo++;
        }
    }

    private void populateCell(Sheet sheet,Row row,int columnNo,
    		String value,CellStyle cellStyle){
        
        Cell cell=row.createCell(columnNo);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(value);
        sheet.autoSizeColumn(columnNo);
    }

    private void prepareHeaders(Workbook workbook, 
    		Sheet sheet, String... headers) {
        
        Row headerRow=sheet.createRow(0);
        Font font=workbook.createFont();
        font.setBold(true);
        font.setFontName("Arial");

        CellStyle cellStyle=workbook.createCellStyle();
        cellStyle.setFont(font);

        int columnNo=0;
        for(String header:headers){
            Cell headerCell=headerRow.createCell(columnNo++);
            headerCell.setCellValue(header);
            headerCell.setCellStyle(cellStyle);
        }
    }
}

  • Create a UserController.java class with a GET API endpoint method to export all users to an Excel file. Call the exportUsers() method defined in ExcelService.
package com.usermgt.controller;

@RestController
@RequestMapping("user")
public class UserController {

    @Autowired
    private ExcelService excelService;

    @GetMapping("export")
    public ResponseEntity<Resource> exportUsers(){
        ResourceDTO resourceDTO=excelService.exportUsers();

        HttpHeaders httpHeaders=new HttpHeaders();
        httpHeaders.add("Content-Disposition",
        	"attachment; filename="+"User.xlsx");
        
        return ResponseEntity.ok()
        	.contentType(resourceDTO.getMediaType())
                .headers(httpHeaders)
                .body(resourceDTO.getResource());
    }
}


Now that the coding is complete, you can test the GET endpoint: localhost:8080/user/export by clicking the send and download option in postman.

Attached is the project structure for reference.
If you have any questions, feel free to leave a comment.

Thanks
Enjoy your learning!




Another post you may like to refer