代码之家 › 专栏 › 技术社区 › Richard

Spring引导控制器导出Excel

apache-poi spring-boot spring excel java

Richard · 技术社区 · 6 年前

我有一个java/spring引导应用程序,我想在其中构建一个API端点,该端点创建并返回一个可下载的excel文件。这是我的控制器端点:

@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);
    }
}

然后是服务类

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;
  }  
}

这段代码使用apachepoi库成功地创建了正确的excel文件。但这不能正确地将其从控制器中返回,因为 ByteArrayResource::getFilename

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

5 回复 | 直到 6 年前

Arun 6 年前

既然你用的是 ByteArrayResource ,您可以使用下面的控制器代码,假设 FooService 在控制器类中自动连线。

@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);
}

Sabir Khan 6 年前

基本上,您需要先了解几点,然后再决定要做什么,

是否需要在磁盘上创建excel,或者可以从内存中进行流式处理?

如果它是一个下载弹出窗口,用户可能会让它长时间打开&内存在此期间被占用(内存内方法的缺点)。

第三,API代码很难进行磁盘清理,因为您永远无法提前知道用户何时完成下载(磁盘内方法的缺点)。

Fizik26的答案是 内存中 不在磁盘上创建文件的方法。答案中唯一的一点是,您需要跟踪数组的长度 out.toByteArray() &这可以通过包装器类轻松完成。

2 下载文件时,您的代码需要一块一块地流式传输文件—这就是Java流的用途。

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()));

和 inputStreamWrapper

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;
    }

}

如果你用的话就不需要这个包装了- org.springframework.core.io.ByteArrayResource

pvpkiran 6 年前

必须使用 Content-disposition . 试试这个

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

像这样改变你的服务方式

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
     }
}

Ajay Kumar 6 年前

使用ReponseEntity让控制器知道它要写什么总是更好的。在服务级别上,只需创建和播放对象。@RestController或@Controller在这里并不重要。

@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));
}

生成Excel类-

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());

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

here , here 和 here

Fizik26 6 年前

您可以使用:

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

在服务的导出方法中,必须返回如下内容:

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