Home / Questions / 183501
Accepted
EduBw
Asked: 2020-07-25 00:32:49 +0800 CST 2020-07-25 00:32:49 +0800 CST

Send excel file to client SpringBoot-Angular2

  • 772

I have to send an Excel from the SpringBoot server to the client in Angular2, The Excel doesn't exist and can't be created because I don't have write permissions, so I'll have to put it in a buffer or something...

The problem I'm having is that I can't put the HSSFWorkbook where I have the data to the response that I have to send to the client.

 @RequestMapping(method = RequestMethod.GET, path = "/{download}/{data}/{nameFile}", produces = MediaType.APPLICATION_JSON_VALUE)
            public synchronized void download(HttpServletResponse response , @PathVariable("data") Boolean data, @PathVariable("nameFile") String nameFile) {

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
             response.setHeader("Content-disposition", "attachment; filename="+nameFile+"result.xlsx");

             String eyelash = "People";

             try {  
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet sheet = workbook.createSheet(eyelash);

                String[] header = { "Name","Surname","Dni","Age","Tlf" };
                HSSFRow rowhead = sheet.createRow((short) 0);
                HSSFCell cell;
                int cellnum = 0;

                if (data) {
                    int myRowData = 1;
                    HSSFRow row = sheet.createRow((short) myRowData);
                    ArrayList list = mlService.selectAll();

                    for (int i = 0; i < list.size(); i++) {
                        row.createCell(0).setCellValue(list.get(i).getName().substring(0, 4));
                        row.createCell(1).setCellValue(list.get(i).getSurname().substring(4, 6));
                        row.createCell(2).setCellValue(list.get(i).getDni());
                        row.createCell(3).setCellValue(list.get(i).getYear());
                        row.createCell(4).setCellValue(list.get(i).getTlf());

                        myRowData++;
                        row = sheet.createRow((short) myRowData);
                    }
                } 

                final InputStream is = new FileInputStream(workbook) ; //error
                IOUtils.copy(is, response.getOutputStream());
                response.flushBuffer();

            } catch (Exception ex) {
                ex.printStackTrace();
                System.out.println(ex.getMessage());
                // deal with error, e.g. response.setStatus(500) to signal an internal server error
            }
    }

Then I expect a boolean and the type of file (there can be many), in this example I have put that it is always the same.

Then the Http response where I add the headers and tell the browser what kind of format the file has.

Then I build the file with HSSFWorkbook, the build is correct And finally I want to send it to the client where I try to put the woorkbook into the file inputStream but it doesn't leave because it's not a file or something like that and supposedly when I get it, it would get into the buffer of the response...

I don't know if I'm on the right track or what... apart from the fact that it gives me an error...

Finally, SpringBoot will create the Excel and send it to the client without having created it.

Thanks.

angular

1 Answers

  1. Best Answer

    The following changes occur to me:

    @RequestMapping(method = RequestMethod.GET,
    path = "/download/{data}/{nameFile}",
    produces = MediaType.APPLICATION_JSON_VALUE)
public void download(HttpServletResponse response , @PathVariable("data") Boolean data, @PathVariable("nameFile") String nameFile) {
    try {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-disposition", "attachment; filename="+nameFile+"result.xlsx");

        response.setHeader("Cache-Control", "max-age=30");
        response.setHeader("Pragma", "No-cache");
        response.setDateHeader("Expires", 0);
        response.setHeader("Content-disposition", "inline; filename=" + nameFile);
        response.setContentLength(excelFileByte.length);

        try {  
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(eyelash);

        String[] header = { "Name","Surname","Dni","Age","Tlf" };
        Row rowhead = sheet.createRow((short) 0);
        Cell cell;
        int cellnum = 0;

        if (data) {
            int myRowData = 1;
            Row row = sheet.createRow((short) myRowData);
            ArrayList list = mlService.selectAll();

            for (int i = 0; i < list.size(); i++) {
                row.createCell(0).setCellValue(list.get(i).getName().substring(0, 4));
                row.createCell(1).setCellValue(list.get(i).getSurname().substring(4, 6));
                row.createCell(2).setCellValue(list.get(i).getDni());
                row.createCell(3).setCellValue(list.get(i).getYear());
                row.createCell(4).setCellValue(list.get(i).getTlf());

                myRowData++;
                row = sheet.createRow((short) myRowData);
            }
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        book.write(baos);
        byte[] excelFileByte = baos.toByteArray();

        ServletOutputStream out = response.getOutputStream();
        out.write(excelFileByte, 0, excelFileByte.length);
        out.flush();
        out.close();

    } catch (Exception ex) {
        ex.printStackTrace();
        System.out.println(ex.getMessage());

    }
}

    I have changed from HSSF to XSSF, because HSSF is the format of XLS files (Excel before 2007) and not XLSX.

    I have removed synchronizedbecause I see no reason to add it to the method.

    I have very similar code and it works fine. To download it with Angular I recommend you to create a link on your page instead of using AJAX: it is direct and works correctly, something like

    <a href="mi_aplicacion/download..." class="btn"> Descarga Excel </a>
    • 1