使用Apache POI生成的Excel已损坏，返回fronm API REST spring boot

Question

编辑：

如果我直接从浏览器命中endpoint，文件就会被正确下载。所以我猜问题出在前面，以及用接收到的数据创建和保存文件的方式。

我有一个Java/spring boot的应用程序，我想在其中构建一个APIendpoint，它创建并返回一个可下载的excel文件。以下是我的控制器endpoint：

 @GetMapping(path = "/informe/{informeDTO}")
    public ResponseEntity<InputStreamResource> generarInforme(@PathVariable(value = "informeDTO") String informeDTOString) throws JsonParseException, JsonMappingException, IOException {
    
        

 final InformeDTO informeDTO =
                new ObjectMapper().readValue(informeDTOString, InformeDTO.class);
     
    List<InformeDTO> listDatosinformeDTO = utilsService.getDatosInformeDTO(informeDTO);

    for (InformeDTO informeDTO2 : listDatosinformeDTO) {
        logger.debug(informeDTO2);
    }

    ByteArrayInputStream in = createReport(listDatosinformeDTO);

    HttpHeaders headers = new HttpHeaders();
    headers.add("Content-Disposition", "attachment; filename=IOPreport.xlsx");

    return ResponseEntity.ok().headers(headers)
            .contentType(
                    MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
            .body(new InputStreamResource(in));

}

这是角度控制器：

  function generarInformeIOP(){
            
  
            InformesService.generarInformeIOP($scope.informeView.sociedad,  $scope.informeView.area, $scope.informeView.epigrafe,
                     $scope.informeView.cuenta, $scope.informeView.status, $scope.informeView.organizationalUnit, 
                     $scope.informeView.societyGL, $scope.informeView.calculationType, $scope.informeView.provincia, $scope.informeView.financialSegment,
                     $scope.informeView.loadDateFrom, $scope.informeView.loadDateTo,  $scope.informeView.incomeDateFrom,  $scope.informeView.incomeDateTo)
             .then(
                 function(response)
                 {
                     var blob = new Blob([response.data], {
                         type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
                     });
                     saveAs(blob, "IOPreport.xlsx");
                     
                      $scope.informeFunctionality.errorMessage = '';
                 },
                 function(errResponse)
                 {
                     console.log("ERROR: " + errResponse.data);
                     $scope.informeFunctionality.errorMessage = "Ha ocurrido un error inesperado: " + errResponse.data.error + 
                         ": " + errResponse.data.message;
                 }
             )
            
        }

和服务：

....
    $http.get(urls.SERVICE_API + "informe/"+ angular.toJson(informeDTO)).then(
                                function(response) {
                                    console.log("GenerarInformeIOP - success");
                                    deferred.resolve(response);
                                }, function(errResponse) {

                                    console.log("GenerarInformeIOP - error");
                                    deferred.reject(errResponse);

                                });
...

生成成功，文件已下载，但我认为它已损坏，因为Excel无法打开它。

有什么不对劲吗？

编辑（添加createReport）：

        private ByteArrayInputStream createReport(List<InformeDTO> datosInforme) {
        ByteArrayInputStream result =null;
        
        try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) {
            Set<String> columns = new LinkedHashSet<String>();

            // Coumnas fijas
            columns.add("Cuenta");
            columns.add("Epigrafe");
            columns.add("Descripcion");
            columns.add("Total_Importe");

            // Columnas dinamicas
            /*
             * Tedremos que recorrer todas las filas puesto que no sabremos si una traera
             * menos periodos que otra de esta manera obtendremos todos los periodos
             */
            for (InformeDTO informeDTO : datosInforme) {
                for (Map.Entry<String, Double> entry : informeDTO.getTotalByPeriodoContable().entrySet()) {
                    columns.add(entry.getKey());
                }
            }

            /*
             * CreationHelper helps us create instances for various things like DataFormat,
             * Hyperlink, RichTextString etc in a format (HSSF, XSSF) independent way
             */
//          CreationHelper createHelper = workbook.getCreationHelper();

            // Create a Sheet
            Sheet sheet = workbook.createSheet("IOPReport");

            // Create a Font for styling header cells
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 14);
            headerFont.setColor(IndexedColors.RED.getIndex());

            // Create a CellStyle with the font
            CellStyle headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFont(headerFont);

            // Create a Row
            Row headerRow = sheet.createRow(0);

            // Creating cells
            int i = 0;
            for (String value : columns) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(value);
                cell.setCellStyle(headerCellStyle);
                i++;
            }

            // Create Other rows and cells with employees data
            int rowNum = 1;
            int cellDynamicNum = 0;
            for (InformeDTO informeDTO : datosInforme) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(informeDTO.getCuenta());
                row.createCell(1).setCellValue(informeDTO.getEpigrafe());
                row.createCell(2).setCellValue(informeDTO.getDescripcion_epigrafe());
                row.createCell(3).setCellValue("No Data");
                cellDynamicNum = 4;
                for (Map.Entry<String, Double> entry : informeDTO.getTotalByPeriodoContable().entrySet()) {
                    row.createCell(cellDynamicNum).setCellValue(entry.getValue());
                    cellDynamicNum++;
                }

            }

            // Resize all columns to fit the content size
            for (i = 0; i < columns.size(); i++) {
                sheet.autoSizeColumn(i);
            }

            // Write the output to a file

            workbook.write(out);
             result = new ByteArrayInputStream(out.toByteArray());
            out.close();
            workbook.close();

        } catch (Exception e) {
            logger.debug("Excepcion en la creacion del report " + e);
        }
        return result;
    }

问候

Answer 1

我不确定为什么使用responseEntity 。我有另一个使用字节数组responseEntity 的工作解决方案。下面附上了一些代码片段：

创建工作簿后,将其写入OutputStream：

private final MediaType mediaType = MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

...
...
// at the end
ByteArrayOutputStream stream = new ByteArrayOutputStream();
workbook.write(stream);
return getDownload(stream.toByteArray(), filename, mediaType);

....

下载方法：

    public static ResponseEntity<byte[]> getDownload(byte[] content, String filename, MediaType mediaType) {
        HttpHeaders headers = new HttpHeaders();
        headers.setContentLength(content.length);
        headers.set(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + filename);
        headers.set(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
        headers.setContentType(mediaType);

        return new ResponseEntity<>(content, headers, HttpStatus.OK);
    }

如果能行就告诉我。

Answer 2

在此处生成输出时：

result = new ByteArrayInputStream(out.toByteArray());

工作簿在关闭之前不会保存到out中。因此您需要将顺序更改为：

 workbook.close()
 result = new ByteArrayInputStream(out.toByteArray());

关闭ByteArrayOutputStream并不是必须的，但如果保留它也没关系。