文章/答案/技术大牛

发布

社区首页 >问答首页 >如何将列表数据报告到excel文件？

问如何将列表数据报告到excel文件？
EN

Stack Overflow用户

提问于 2019-01-28 09:07:00

回答 3查看 280关注 0票数 0

我使用restfull服务，我想导出数据到excel文件。我有ReportExcel类:格式化文件excel。

public class ReportExcel {
public static ByteArrayInputStream tasksToExcel(List<ReportTaskToTimeResponse> allTasks) throws IOException {
    String[] COLUMNs = { "Task", "User Name", "Project Name", "Hours" };
    try (Workbook workbook = new XSSFWorkbook(); 
        ByteArrayOutputStream out = new ByteArrayOutputStream();
    ) {
        Sheet sheet = workbook.createSheet("Report");

        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.BLUE.getIndex());

        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFont(headerFont);

        // Row for Header
        Row headerRow = sheet.createRow(0);

        // Header
        for (int col = 0; col < COLUMNs.length; col++) {
            Cell cell = headerRow.createCell(col);
            cell.setCellValue(COLUMNs[col]);
            cell.setCellStyle(headerCellStyle);
        }

        int rowIdx = 1;
        for (ReportTaskToTimeResponse reportTaskToTimeResponse : allTasks) {
            Row row = sheet.createRow(rowIdx++);

            row.createCell(0).setCellValue(reportTaskToTimeResponse.getTask());
            row.createCell(1).setCellValue(reportTaskToTimeResponse.getUserName());
            row.createCell(2).setCellValue(reportTaskToTimeResponse.getProjectName());
            row.createCell(2).setCellValue(reportTaskToTimeResponse.getHours());
        }

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

}

我是主计长：

 @RequestMapping(value = "/reports/tasks/allTasks.xlsx", method = RequestMethod.POST, produces = "text/csv")
public ResponseEntity<?> reportExcelTasks(@RequestParam("fromDate") String fromDate, @RequestParam("toDate") String toDate) throws IOException{

    LocalDateTime startDate = DateTimeUtils.convertStringToLocalDateTime(fromDate);
    LocalDateTime endDate = DateTimeUtils.convertStringToLocalDateTime(toDate);

    List<ReportTaskToTimeResponse> list = reportService.getTaskOfMemberToTime(startDate, endDate);

    ByteArrayInputStream in = ReportExcel.tasksToExcel(list);

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

    return ResponseEntity
                .ok()
                .headers(headers)
                .body(new InputStreamResource(in));
}

但是，当我调用这个api以使它不活动时，邮递员会为我返回：

PK�H<N_rels/.rels���j�0�_���8�`�Q��2�m��4[ILb��ږ���.[K

�n��"�H�׺؁��8�Z�^'�#��7m{��O�3��G�u�ܓ�'��y|a��D��l_EYȾ��vql3�ML��eh��\�($}��v？�I�Q.��uӂ�h��x>=��@��p�H)3�Y0��oJ׏�：��^��}PK��z��H6�PU�C��{�X�%��[8�R�Q�cfgfW�d�q�ZCB\��|��*h㻆}，^�{P��$��҆�d�c�D�j)；��ѝP�g��E�M‘O�ʕ��H7L�h��R��G��^�’�{��zސʮB��3�˙��h.�h�W�жF�j娄CQՠ똈��}ιL�U:d��%އ��，�B��[��；采购产品名称:y�%��49��`_��4G��F��J��Wg˱��˱��~��X��p�ykOL��kN�V��ܿBZ~��q��ar��(O�PKz��Q；PK�H��$；�SC；̢(�ra�g�l！L！

java

spring

export-to-csv

回答 3

Stack Overflow用户

回答已采纳

发布于 2019-01-28 11:32:01

目前，您的所有操作都是在内存中完成的，这在大文件的情况下是有问题的。尝试将文件保存在服务器上的某个位置，然后写入ServletResponse。请使用下面的示例代码下载excel文件作为附件。

private static final String FILENAME = "fileName.xls";


@PostMapping(value = "/reports/tasks/allTasks.xlsx", produces=MediaType.APPLICATION_OCTET_STREAM_VALUE)
public void reportExcelTasks(@RequestParam("fromDate") String fromDate, @RequestParam("toDate") String toDate, HttpServletResponse response) throws IOException {

LocalDateTime startDate = DateTimeUtils.convertStringToLocalDateTime(fromDate);
LocalDateTime endDate = DateTimeUtils.convertStringToLocalDateTime(toDate);

List<ReportTaskToTimeResponse> list = reportService.getTaskOfMemberToTime(startDate, endDate);

    ByteArrayInputStream in = ReportExcel.tasksToExcel(list);
    IOUtils.copy(in, response.getOutputStream());
    response.setContentType("application/x-download");
    response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(FILENAME, "UTF-8"));
    response.flushBuffer();
}

如果要测试输出，请在下面的示例html代码中尝试post操作。

<html>
 <body>
   <form method = "post" action="http://localhost:8080/reports/tasks/allTasks.xlsx">    
      <input type = "text" name = "fromDate" required/>
      <input type = "text" name = "toDate" required/>
      <input type = "submit"/>
  </body>
</html>

尝试在浏览器中打开此文件并添加所需的字段。这将在本地下载包含所有数据的文件。

票数 0

Stack Overflow用户

发布于 2019-01-28 09:42:56

private static final String COMPANY_ACCOUNT_FILENAME_EXCEL_EXPORT = "your FileName.xls";

@RequestMapping(value = "/reports/tasks/allTasks.xlsx", method = RequestMethod.POST, produces = "text/csv")
public void reportExcelTasks(HttpServletResponse response,
@RequestParam("fromDate") String fromDate, @RequestParam("toDate") String toDate) throws IOException{

    LocalDateTime startDate = DateTimeUtils.convertStringToLocalDateTime(fromDate);
    LocalDateTime endDate = DateTimeUtils.convertStringToLocalDateTime(toDate);

    List<ReportTaskToTimeResponse> list = reportService.getTaskOfMemberToTime(startDate, endDate);

    XSSFWorkbook workbook = ReportExcel.tasksToExcel(list);
    String fileName = COMPANY_ACCOUNT_FILENAME_EXCEL_EXPORT;

    response.setContentType("application/x-download");
    response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
    workbook.write(response.getOutputStream());
}

请更新以下代码：

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

将是：

return workbook;

票数 1

Stack Overflow用户

发布于 2019-01-28 09:17:13

您可以尝试以下方法：

public class FileResponse<T> {
    private final T file;
    private final String fileName;

    public FileResponse(T file, String fileName) {
        this.file = file;
        this.fileName = fileName;
    }
}

控制员：

Workbook workbook = yourService.createExcel(); // call tasksToExcel here
FileResponse<Workbook> excel = new FileResponse<>(workbook, "yourfilename.xlsx");
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
excel.getFile().write(outputStream);

return ResponseEntity.ok()
    .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
    .contentLength(outputStream.size()) // so web browsers will know the size of your file, this is just a nice-to-have feature
    .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + excel.getFileName())
    .body(outputStream.toByteArray());

在您将tasksToExcel方法更改为返回工作簿而不是ByteArrayInputStream之后，这将起作用。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/54398558

复制

相似问题

问如何将列表数据报告到excel文件？
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将列表数据报告到excel文件？EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将列表数据报告到excel文件？
EN