[Java Spring Boot] Returning a CSV file from an endpoint

Introduction

Imagine a situation where you have an extremely long database of information about students.
Your client has requested that you provide an endpoint to return all registered students.
You can combine with the front end to return this information in a paged form as presenting thousands of data directly on the screen would be terrible!

But, for some reason, the client also wants to have the complete list with all the information available, but in CSV format so that they can view it in Excel.

If you want to see how to return paginated information using java spring boot, you can see this other article I wrote by clicking here.

Let's focus, then, on how to develop this endpoint that will return a CSV file with the list of all students.

You can view the repository of this article by clicking here.
In this repository you will find a main branch, containing a controller that returns all student information, without pagination.

The branch that will have our csv return endpoint development is the feature/csv-downloader branch

I'll quickly introduce the main classes of our little project.

Development

Student Entity:

@Data
@Builder
public class StudentEntity {

    private Integer registration;

    private String name;
    private Integer grade;

}

As simple as possible, our goal is to build a CSV, for this we will use a simple entity class.

To be able to create CSV file we will need two dependencies in our POM.xml:

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

Now let's see what the service that will generate the csv looks like, I'll put it:

Class StudentServiceImpl.class:

Let's create a method called getStudentsCSV, this method will receive an object of type HttpServletResponse, which we will use for HTTP communication, being able to add header information and our CSV file to be returned to the browser.

@Override
    public void getStudentsCSV(HttpServletResponse response) {
        List<StudentEntity> students = StudentRepository.findAll();


    }

For now all it does is receive the list of students from the repository.
Let's now create some private methods that will be responsible for creating our csv file, our spreadsheet, our lines.

createSheet method:

private XSSFSheet createSheet(XSSFWorkbook workbook) {
        XSSFSheet sheet = workbook.createSheet("Student Report");
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 4000);
        sheet.setColumnWidth(2, 4000);
        return sheet;
    }

This method receives a Workbook, within it we will create our spreadsheet using the createSheet method, which receives the name of the spreadsheet we are going to create (A workbook can have several spreadsheets inside).
After creating the spreadsheet, we can customize the columns within it, in our case we will change the length of the first 3 columns to 4000.

Okay, now that we have our method that creates the spreadsheet, let's create the method that creates cells in our spreadsheet, being as generic as possible:

createCell method:

    private void createCell(XSSFRow row, int index, String value) {
        XSSFCell cell = row.createCell(index);
        cell.setCellValue(value);
    }

This method receives an object of type XSSFRow that represents a row in our spreadsheet, it also receives an index to know the position of the cell in the row, and a value that would be the content that that cell will receive.

So let's create an object of type XSSFCell and use the createCell method of the XSSFRow object to create a cell, passing the index, that is, the column that the cell will be in.
And finally, we will use the setCellValue method of our XSSFCell that we created before to place the content inside.
Very simple.

Now that we have a generic method that creates cells in a row of our spreadsheet, we can create headers and fill lines.

Let's start with the method that creates the header:

createHeader method:

private void createHeader(XSSFSheet sheet) {
        XSSFRow header = sheet.createRow(0);
        createCell(header, 0, "Registration");
        createCell(header, 1, "Name");
        createCell(header, 2, "Grade");
    }

This method receives an object referring to the worksheet.
First we create an XSSFRow object that will represent a row in our spreadsheet, as we are going to make a header, we are going to create the first row, so we will use the createRow method of the XSSFSheet class passing the value 0.

Now let's use the createCell method that we created previously by passing: the header (as this represents the spreadsheet row, the cell column and the content).
We are calling this method 3 times because we have 3 columns to add:
Registration - Student registration
Name - The student's name
Grade - the student's grade

Now that we have our first row in the spreadsheet (our header) we will populate the other rows with the return from the repository. To do this, we will create a method called createRow:

createRow method:

private void createRow(XSSFSheet sheet, List<StudentEntity> students) {
        XSSFRow row;
        int rowCounter = 1;

        for(var student : students) {
            row = sheet.createRow(rowCounter);
            createCell(row, 0, student.getRegistration().toString());
            createCell(row, 1, student.getName());
            createCell(row, 2, student.getGrade().toString());
            rowCounter++;
        }
    }

This method receives as a parameter our spreadsheet (XSSFSheet) and a list of students that returned from our repository.
First we will create an object of type XSSFRow and an integer that will be our spreadsheet row counter, starting at 1. It will start at 1 because row 0 has already been created, it is our header.

Now let's iterate through the list of students where, for each student record, we will create a row using the createRow method of the XSSFSheet class passing the row we are creating.
And then let's call the createCell method, created by us. Let's pass our XSSFRow, the column we are going to fill and the content to this method. In this case, we call it 3 times because we have 3 attributes in the entity, with column 0 being the student's registration, column 1 being the name and column 2 being the grade.
After making the assignments, we increment our line count so that, in the next iteration, the information is added in a new line.

Very well, now that we have the main classes for creating our spreadsheet, let's call these methods in our service in the report generation method that will be called by the controller, looking like this:

@Override
    public void getStudentsCSV(HttpServletResponse response) {
        List<StudentEntity> students = StudentRepository.findAll();

        try(XSSFWorkbook workbook = new XSSFWorkbook()){
            String headerKey = "Content-Disposition";
            String headerValue = "attachment; filename=report.csv";
            response.setHeader(headerKey, headerValue);

            XSSFSheet sheet = createSheet(workbook);
            createHeader(sheet);
            createRow(sheet, students);

            ServletOutputStream out = response.getOutputStream();
            out.flush();
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

NOTE: here we are throwing a RuntimeException because I am not concerned with exception handling here, I believe the correct option is to create a custom exception for possible errors and use a handler to handle this exception.

Let's understand what's going on here:

1 - We call our repository that will return all students.
2 - We create our workbook (Workbook), which is an object of type XSSFWorkbook.
3 - We create a header to add to our HttpServletResponse. This header is called "Content-Disposition" and has the value "attachment; filename=report.csv". It will inform the browser that we are returning an attachment and it already tells the name of the attachment that will be saved on the user's computer.
4 - We call our method create spreadsheet
5 - We call our method create the header
6 - We call our method of filling the lines with the student information returned by the repository
7 - We created a ServletOutputStream to return binary information to our client. This object receives getOutputStream() from our HttpServletResponse.
8 - We flush() the ServletOutputStream which will force the writing of information, add it to our workbook and then close it with the out() method

So, this service will return our CSV. Our complete Service was as follows:

package com.csv.downloader.service.Impl;


import com.csv.downloader.domain.entity.StudentEntity;
import com.csv.downloader.domain.response.StudentResponse;
import com.csv.downloader.repository.StudentRepository;
import com.csv.downloader.service.StudentService;
import com.csv.downloader.util.StudentMapper;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentMapper studentMapper;

    @Override
    public List<StudentResponse> getStudents() {
        List<StudentEntity> students = StudentRepository.findAll();
        return studentMapper.entityToResponseList(students);
    }

    @Override
    public void getStudentsCSV(HttpServletResponse response) {
        List<StudentEntity> students = StudentRepository.findAll();

        try(XSSFWorkbook workbook = new XSSFWorkbook()){
            String headerKey = "Content-Disposition";
            String headerValue = "attachment; filename=report.csv";
            response.setHeader(headerKey, headerValue);

            XSSFSheet sheet = createSheet(workbook);
            createHeader(sheet);
            createRow(sheet, students);

            ServletOutputStream out = response.getOutputStream();
            out.flush();
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    private XSSFSheet createSheet(XSSFWorkbook workbook) {
        XSSFSheet sheet = workbook.createSheet("Student Report");
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 4000);
        sheet.setColumnWidth(2, 4000);
        return sheet;
    }

    private void createHeader(XSSFSheet sheet) {
        XSSFRow header = sheet.createRow(0);
        createCell(header, 0, "Registration");
        createCell(header, 1, "Name");
        createCell(header, 2, "Grade");
    }

    private void createCell(XSSFRow row, int index, String value) {
        XSSFCell cell = row.createCell(index);
        cell.setCellValue(value);
    }

    private void createRow(XSSFSheet sheet, List<StudentEntity> students) {
        XSSFRow row;
        int rowCounter = 1;

        for(var student : students) {
            row = sheet.createRow(rowCounter);
            createCell(row, 0, student.getRegistration().toString());
            createCell(row, 1, student.getName());
            createCell(row, 2, student.getGrade().toString());
            rowCounter++;
        }
    }


}

The controller that calls this service is the StudentsController:

StudentController.class:

package com.csv.downloader.controller;

import com.csv.downloader.domain.response.StudentResponse;
import com.csv.downloader.service.StudentService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("students")
@Tag(name = "Student Controller", description = "Endpoint for returning csv with a list of students")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @GetMapping("all")
    @Operation(summary = "list all students")
    ResponseEntity<List<StudentResponse>> getAllStudents() {
        return ResponseEntity.ok().body(studentService.getStudents());
    }

    @GetMapping("download")
    @Operation(summary = "download a csv with all students info")
    void getStudentsCSVReport(HttpServletResponse response) {
        studentService.getStudentsCSV(response);
    }

}

Result

In the end, the CSV file looked like this:"

To call the endpoint, simply run the application and do a get to:
localhost:8080/students/download

or access the application's swagger:
http://localhost:8080/swagger-ui/index.html

With this we finish our implementation.
I hope this article helped you. If you have any questions or suggestions for improvements, feel free to comment and I will be happy to answer.