[FIXED] Download a List as Excel in Spring Boot + Nextjs

Issue

I'm trying to download a list as excel on clicking a button from my Nextjs frontend. My backend is written in Spring boot, from where I need to send the file so that user can download it on clicking the link.

My questions are:

1. What is the best practice if in this case if I want to download the file on button click?
2. What should be the return type and content type?

The things I have already tried: By changing content type & media type. I think somehow I'm messing up with the content type and return type of the triggered API.

My backend code files:

ExcelExporter.java

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

public class ExcelExporter {
    private final XSSFWorkbook workbook;
    private XSSFSheet sheet;
    private final List<Debt> debts;

    String[] columns = {"Reference", "Title", "Descrip", "Gateway", "Status", "Amount (USD)", "Paid", "Name"};

    public ExcelExporter(List<Debt> debts) {
        this.debts = debts;
        workbook = new XSSFWorkbook();
    }


    private void writeHeaderLine() {
        sheet = workbook.createSheet("Payment Report");

        CellStyle headerCellStyle = workbook.createCellStyle();

        // setting up header fonts
        XSSFFont headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeight(16);

        // setting up header cell style
        headerCellStyle.setFont(headerFont);

        // header row creation
        Row headerRow = sheet.createRow(0);

        for(int col=0; col <columns.length; col++){
            createCell(headerRow, col, columns[col], headerCellStyle);
        }

    }

    private void createCell(Row row, int columnCount, String value, CellStyle style) {
        sheet.autoSizeColumn(columnCount);
        Cell cell = row.createCell(columnCount);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }

    private void writeDataLines() {
        int rowIdx = 1;

        CellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeight(14);
        style.setFont(font);

        for (Debt debt : debts) {
            Row dataRow = sheet.createRow(rowIdx++);
            int columnCount = 0;

            createCell(dataRow, columnCount++, debt.getRef(), style);
            createCell(dataRow, columnCount++, debt.getTitle(), style);
            createCell(dataRow, columnCount++, debt.getDescrip(), style);
            createCell(dataRow, columnCount++, debt.getGateway(), style);
            createCell(dataRow, columnCount++, debt.getStat(), style);
            createCell(dataRow, columnCount++, debt.getAmount(), style);
            createCell(dataRow, columnCount++, debt.getPaid(), style);
            createCell(dataRow, columnCount++, debt.getName(), style);

        }
    }

    public ByteArrayInputStream export() throws IOException {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        try {
            writeHeaderLine();
            writeDataLines();
            workbook.write(outputStream);
            return new ByteArrayInputStream(outputStream.toByteArray());
        }catch (IOException e){
            throw new RuntimeException("fail To Import Data To Excel File: " + e.getMessage());
        }

    }
}

Controller:

import lombok.RequiredArgsConstructor;
import org.apache.commons.io.IOUtils;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RestController
@RequiredArgsConstructor
@RequestMapping("report")
public class ReportController {

    @GetMapping(value = "export-to-excel", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    public byte[] exportReport() throws IOException {

        List<Debt> debtSummaries = new ArrayList<Debt>();
        debt.add(new Debt("AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH"));
        debt.add(new Debt("AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH"));
        debt.add(new Debt("AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH"));
        debt.add(new Debt("AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH"));
        
        DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
        String currentDateTime = dateFormatter.format(new Date());

        String headerKey = HttpHeaders.CONTENT_DISPOSITION;
        String headerValue = "attachment; filename=PaymentReport_" + currentDateTime + ".xlsx";
        HttpHeaders httpHeaders = new HttpHeaders();
        httpHeaders.add(headerKey, headerValue);

        ExcelExporter excelExporter = new ExcelExporter(debtSummaries);
        ByteArrayInputStream in = excelExporter.export();


        return IOUtils.toByteArray(in);
}

Debt.java

@Getter
@Setter
@AllArgsConstructor
public class Debt {

    String ref;

    String title;

    String descrip;

    String gateway;

    String stat;

    String amount;

    String paid;

    String name;
}

Solution

It is not the right approach to simply return the byte array directly. I can't guarantee you that this is the best way. But you can do it this way.

import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.Resource;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;


@RestController
@RequiredArgsConstructor
@RequestMapping("report")
public class ReportController {

    @GetMapping(value = "export-to-excel", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    public ResponseEntity<Resource> exportReport() throws IOException {

        //Other actions you want to do... 

        ExcelExporter excelExporter = new ExcelExporter(debtSummaries);
        ByteArrayInputStream in = excelExporter.export();

        ByteArrayResource resource = new ByteArrayResource(in.readAllBytes());

        return ResponseEntity.ok()
                .headers(headers)
                .contentLength(resource.contentLength())
                .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
                .body(resource);
    }
}

Answered By - fatih
Answer Checked By - Terry (JavaFixing Volunteer)