Spring Boot Controller export an Excel

Question

I have a java/spring boot application where I want to build an API endpoint that creates and returns a downloadable excel file. Here is my controller endpoint:

@RestController
@RequestMapping("/Foo")
public class FooController {
    private final FooService fooService;

    @GetMapping("/export")
    public ResponseEntity export() {
        Resource responseFile = fooService.export();

        return ResponseEntity.ok()
                             .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename="+responseFile.getFilename())
                             .contentType(MediaType.MULTIPART_FORM_DATA)
                             .body(responseFile);
    }
}

Then the service class

public class FooService {
  public Resource export() throws IOException {
    StringBuilder filename = new StringBuilder("Foo Export").append(" - ")
                                                            .append("Test 1.xlsx");
    return export(filename);
  }

  private ByteArrayResource export(String filename) throws IOException {
      byte[] bytes = new byte[1024];
      try (Workbook workbook = generateExcel()) {
          FileOutputStream fos = write(workbook, filename);
          fos.write(bytes);
          fos.flush();
          fos.close();
      }

      return new ByteArrayResource(bytes);
  }

  private Workbook generateExcel() {
      Workbook workbook = new XSSFWorkbook();
      Sheet sheet = workbook.createSheet();

      //create columns and rows

      return workbook;
  }

  private FileOutputStream write(final Workbook workbook, final String filename) throws IOException {
      FileOutputStream fos = new FileOutputStream(filename);
      workbook.write(fos);
      fos.close();
      return fos;
  }  
}

This code successfully creates the proper excel file using the Apache POI library. But this won't return it out of the controller properly because ByteArrayResource::getFilename always returns null:

/**
 * This implementation always returns {@code null},
 * assuming that this resource type does not have a filename.
 */
@Override
public String getFilename() {
    return null;
}

What type of resource can I use to return the generated excel file?

Solution 1

Since you are using ByteArrayResource, you can use the below controller code assuming that the FooService is autowired in the controller class.

@RequestMapping(path = "/download_excel", method = RequestMethod.GET)
public ResponseEntity<Resource> download(String fileName) throws IOException {

ByteArrayResource resource = fooService.export(fileName);

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

Solution 2

Basically , there are few points that you first need to understand & then decide what you want to do ,

1.Is excel creation on disk needed or can you stream it from memory?

If its a download pop up, user might keep it open for long time & memory be occupied during that period ( disadvantage of in memory approach ) .

Secondly, if generated file has to be new for each request ( i.e. data to be exported is different ) then there is no point in keeping it at disk ( disadvantage of in disk approach ) .

Thirdly, it will be hard for an API code to do disk clean up because you never know in advance as when user will finish up his down load ( disadvantage of in disk approach ) .

Answer by Fizik26 is this In - Memory approach where you don't create a file on disk. . Only thing from that answer is that you need to keep track of length of array out.toByteArray() & that can easily be done via a wrapper class.

2.While downloading a file , your code needs to stream a file chunk by chunk - thats what Java streams are for. Code like below does that.

return ResponseEntity.ok().contentLength(inputStreamWrapper.getByteCount())
            .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
            .cacheControl(CacheControl.noCache())
            .header("Content-Disposition", "attachment; filename=" + "SYSTEM_GENERATED_FILE_NM")
            .body(new InputStreamResource(inputStreamWrapper.getByteArrayInputStream()));

and inputStreamWrapper is like ,

public class ByteArrayInputStreamWrapper {
    private ByteArrayInputStream byteArrayInputStream;
    private int byteCount;


    public ByteArrayInputStream getByteArrayInputStream() {
    return byteArrayInputStream;
    }


    public void setByteArrayInputStream(ByteArrayInputStream byteArrayInputStream) {
    this.byteArrayInputStream = byteArrayInputStream;
    }


    public int getByteCount() {
    return byteCount;
    }


    public void setByteCount(int byteCount) {
    this.byteCount = byteCount;
    }

}

Regarding file name, if file name is not an input to end point - that means ..its system generated ( a combination of constant string plus a variable part per user ). I am not sure why you need to get that from resource.

You won't need this wrapper if use - org.springframework.core.io.ByteArrayResource

Solution 3

Letting controller know is always better what it is going to write using ReponseEntity. At service level just create and play around the objects. @RestController or @Controller doesn't matter here.

What you are looking forward for in your controller is somewhat like this (sample) -

@GetMapping(value = "/alluserreportExcel")
public ResponseEntity<InputStreamResource> excelCustomersReport() throws IOException {
    List<AppUser> users = (List<AppUser>) userService.findAllUsers();
    ByteArrayInputStream in = GenerateExcelReport.usersToExcel(users);
    // return IO ByteArray(in);
    HttpHeaders headers = new HttpHeaders();
    // set filename in header
    headers.add("Content-Disposition", "attachment; filename=users.xlsx");
    return ResponseEntity.ok().headers(headers).body(new InputStreamResource(in));
}

Generate Excel Class -

public class GenerateExcelReport {

public static ByteArrayInputStream usersToExcel(List<AppUser> users) throws IOException {
...
...
//your list here
int rowIdx = 1;
        for (AppUser user : users) {
            Row row = sheet.createRow(rowIdx++);

            row.createCell(0).setCellValue(user.getId().toString());
            ...
        }

  workbook.write(out);
  return new ByteArrayInputStream(out.toByteArray());

and finally, somewhere, in your view -

<a href="<c:url value='/alluserreportExcel'  />"
                target="_blank">Export all users to MS-Excel</a>

For full example, take a peek - here, here and here.

Solution 4

You have to set the file name to the response header using Content-disposition. Try this

@GetMapping("/export")
public ResponseEntity export(HttpServletResponse response) {
        fooService.export(response);      
}

Change your service method like this

public Resource export(HttpServletResponse response) throws IOException {
    StringBuilder filename = new StringBuilder("Foo Export").append(" - ")
                                                        .append("Test 1.xlsx");
   return export(filename, response);
}

private void export(String filename,  HttpServletResponse response) throws IOException {
      try (Workbook workbook = generateExcel()) {
          FileOutputStream fos = write(workbook, filename);
          IOUtils.copy(new FileInputStream(fos.getFD()),               
                                     servletResponse.getOutputStream());//IOUtils is from apache commons io
          response.setContentType("application/vnd.ms-excel");
          response.setHeader("Content-disposition", "attachment; filename=" + filename);
     }catch(Exception e) {
       //catch if any checked exception
     }finally{
        //Close all the streams
     }
}

Solution 5

You can use this :

 headers.add("Content-Disposition", "attachment; filename=NAMEOFYOURFILE.xlsx");
ByteArrayInputStream in = fooService.export();
return ResponseEntity
            .ok()
            .headers(headers)
            .body(new InputStreamResource(in));

It will download the Excel file when you call this endpoint.

In your export method in your service, you have to return something like that :

    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        workbook.write(out);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return new ByteArrayInputStream(out.toByteArray());

View more solutions

Comments

[Richard]

this isn't really what I want. They are not passing in a filename into the controller. I've edited my post above to show what my controller looks like

[Arun]

Well... I just provided the sample code. Edit it for your purpose.

[Richard]

BufferedInputStream can't take a FileOutputStream

[Richard]

what are you returning out of the private export method?

[pvpkiran]

@Richard edited my answer a bit. I am not returning anything. I just write to the httpservletresponse outputstream