Returning excel file using spring boot controller

Question

I was trying to make a rest endpoint in Spring Boot which reads from DB, generates an excel file(Using Apache POI) which is returned to the user using HttpServletResponse but when I invoke this, the excel is getting created but it's not downloading. I had some other code earlier in place which was working fine but I accidentally removed that and now I'm stuck. Any help/leads are appreciated.

@RequestMapping(path = "/save", method = RequestMethod.GET)
public ResponseEntity<String> saveToXls(@RequestParam String id, @RequestParam String appName, HttpServletResponse response) {
    AppInstance appInstance = appInstanceRepo.get(id);
    List<DownloadDetail> downloadDetailList = downloadDAO.searchByInstanceId(id);
    //List<DownloadDetail> downloadDetailList = appInstance.getDownloads();
    System.out.print("LIST SIZE:" + downloadDetailList.size());
    String fileName = appName + " report";
    File myFile = new File(fileName + ".xls");

    FileOutputStream fileOut;
    downloadDetailList.forEach(downloadDetail -> System.out.print(downloadDetail.getSid()));
    try {

        try (HSSFWorkbook workbook = new HSSFWorkbook()) {
            HSSFSheet sheet = workbook.createSheet("lawix10");
            HSSFRow rowhead = sheet.createRow((short) 0);
            rowhead.createCell((short) 0).setCellValue("SID");
            rowhead.createCell((short) 1).setCellValue("Download Time");
            rowhead.createCell((short) 2).setCellValue("OS Version");
            int i = 0;

            for (DownloadDetail downloadDetail : downloadDetailList) {
                System.out.print("In loop -2");
                HSSFRow row = sheet.createRow((short) i);
                row.createCell((short) 0).setCellValue(downloadDetail.getSid());
                row.createCell((short) 1).setCellValue(downloadDetail.getDownloadTime());
                row.createCell((short) 2).setCellValue(downloadDetail.getOsVersion());
                i++;
            }
            fileOut = new FileOutputStream(myFile);
            workbook.write(fileOut);
        }
        fileOut.close();

        byte[] buffer = new byte[10240];
        response.addHeader("Content-disposition", "attachment; filename=test.xls");
        response.setContentType("application/vnd.ms-excel");
        try (
                InputStream input = new FileInputStream(myFile);
                OutputStream output = response.getOutputStream();
        ) {
            for (int length = 0; (length = input.read(buffer)) > 0;) {
                output.write(buffer, 0, length);
            }
        }

        response.flushBuffer();

    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    } catch (IOException e1) {
        e1.printStackTrace();
    }


    return null;
}

EDIT: I tried to do it another way as shown below:

        try (InputStream is = new FileInputStream(myFile)) {
            response.addHeader("Content-disposition", "attachment; filename=test.xls");
            response.setContentType("application/vnd.ms-excel");
            IOUtils.copy(is, response.getOutputStream());

        }
    response.flushBuffer();

This also doesn't seem to cut it.

Innet · Accepted Answer · 2018-08-22 10:23:39Z

1

This is a my example. Probably the issue is how you manage the OutputStream:

ServletOutputStream os = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=\""+fileName+".xls\"");

workbook = excelStrategyMap.get(strategy).export(idList, status, params);

workbook.write(os);
workbook.close();
os.flush();

response.flushBuffer();

answered Aug 22, 2018 at 10:23

Innet

4593 gold badges5 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Deepesh Over a year ago

It doesn't do anything and still the file download is not happening.

Suhas Bhat · Accepted Answer · 2018-10-22 06:17:09Z

Once you get the workbook file, set the file name and file type. and add the response header and content type as mentioned below.

Then write the file to the response and flush it's buffer.

        XSSFWorkbook file = excelUploadService.downloadDocument();
        String filename = "Export.xlsx";
        String filetype = "xlsx";
        response.addHeader("Content-disposition", "attachment;filename=" + filename);
        response.setContentType(filetype);
        // Copy the stream to the response's output stream.

        file.write(response.getOutputStream());
        response.flushBuffer();

In the client side, get the response from the REST API and set the content type received by the response object. Using FileSaver library save the file into your local file system.

Here is the documentation for FileSaver js -- File saver JS Library

        var type = response.headers("Content-Type");
        var blob = new Blob([response.data], {type: type});

        saveAs(blob, 'Export Data'+ '.xlsx');

user666 · Accepted Answer · 2018-12-07 10:59:16Z

0

@GetMapping(value = "/", produces = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
@ResponseBody
public byte[] generateExcel() {
        byte[] res = statisticsService.generateExcel();
        return res;

answered Dec 7, 2018 at 10:59

user666

2,0514 gold badges25 silver badges38 bronze badges

3 Comments

Mahib Over a year ago

Its nice to add little explanation to the code for easier understanding.

user666 Over a year ago

It shows when you have a list of bytes that you want to return as excel sheet to be downloaded from the browser. So the key is to mention the produces as i did and to add the annotation @ResponseBody with a return type byte[]

klimksh Over a year ago

your generateExcel would need to return an array of bytes, that could be implemented like this:

ByteArrayOutputStream bos = new ByteArrayOutputStream();                         try {                             workbook.write(bos);                         } finally {                             bos.close();                         }                         workbook.close();                         return bos.toByteArray();

Collectives™ on Stack Overflow

Returning excel file using spring boot controller

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related