Java Spring Boot Controller 导出 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/52078128/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring Boot Controller export an Excel
提问by Richard
I have a java/spring boot application where I want to build an API endpoint that creates and returns a downloadable excel file. Here is my controller endpoint:
我有一个 java/spring boot 应用程序，我想在其中构建一个 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);
}
}
Then the service class
然后是服务类
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;
}
}
This code successfully creates the proper excel file using the Apache POI library. But this won't return it out of the controller properly because ByteArrayResource::getFilenamealways returns null:
此代码使用 Apache POI 库成功创建了正确的 excel 文件。但这不会将它正确地从控制器中ByteArrayResource::getFilename返回，因为它总是返回 null：
/**
* This implementation always returns {@code null},
* assuming that this resource type does not have a filename.
*/
@Override
public String getFilename() {
return null;
}
What type of resource can I use to return the generated excel file?
我可以使用什么类型的资源来返回生成的 excel 文件？
回答by Arun
Since you are using ByteArrayResource, you can use the below controller code assuming that the FooServiceis autowired in the controller class.
由于您使用的是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);
}
回答by pvpkiran
You have to set the file name to the response header using Content-disposition. Try this
您必须使用 将文件名设置为响应标头Content-disposition。尝试这个
@GetMapping("/export")
public ResponseEntity export(HttpServletResponse response) {
fooService.export(response);
}
Change your service method like this
像这样改变你的服务方法
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
}
}
回答by Ajay Kumar
Letting controller know is always better what it is going to write using ReponseEntity. At service level just create and play around the objects. @RestController or @Controller doesn't matter here.
让控制器知道使用 ReponseEntity 将要编写的内容总是更好。在服务级别，只需创建和播放对象。@RestController 或 @Controller 在这里无关紧要。
What you are looking forward for in your controller is somewhat like this (sample) -
您对控制器的期待有点像这样（示例）-
@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));
}
Generate Excel Class -
生成 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());
and finally, somewhere, in your view -
最后，在你看来——
<a href="<c:url value='/alluserreportExcel' />"
target="_blank">Export all users to MS-Excel</a>
回答by Fizik26
You can use this :
你可以使用这个：
headers.add("Content-Disposition", "attachment; filename=NAMEOFYOURFILE.xlsx");
ByteArrayInputStream in = fooService.export();
return ResponseEntity
.ok()
.headers(headers)
.body(new InputStreamResource(in));
It will download the Excel file when you call this endpoint.
当您调用此端点时，它将下载 Excel 文件。
In your export method in your service, you have to return something like that :
在您的服务中的导出方法中，您必须返回如下内容：
ByteArrayOutputStream out = new ByteArrayOutputStream();
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
return new ByteArrayInputStream(out.toByteArray());
回答by Sabir Khan
Basically , there are few points that you first need to understand & then decide what you want to do ,
基本上，您首先需要了解以下几点，然后再决定要做什么，
1.Is excel creation on disk needed or can you stream it from memory?
1.需要在磁盘上创建 excel 还是可以从内存中流式传输？
If its a download pop up, user might keep it open for long time & memory be occupied during that period ( disadvantage of in memory approach ) .
如果它弹出下载，用户可能会长时间打开它并且在此期间占用内存（内存方法的缺点）。
Secondly, if generated file has to be new for each request ( i.e. data to be exported is different ) then there is no point in keeping it at disk ( disadvantage of in disk approach ) .
其次，如果生成的文件对于每个请求都必须是新的（即要导出的数据是不同的），那么将其保存在磁盘上是没有意义的（磁盘方法的缺点）。
Thirdly, it will be hard for an API code to do disk clean up because you never know in advance as when user will finish up his down load ( disadvantage of in disk approach ) .
第三，API 代码很难清理磁盘，因为您永远不会提前知道用户何时完成下载（磁盘方法的缺点）。
Answer by Fizik26 is this In - Memoryapproach where you don't create a file on disk. . Only thing from that answer is that you need to keep track of length of array out.toByteArray()& that can easily be done via a wrapper class.
Fizik26 的回答是这种In-Memory方法，您不在磁盘上创建文件。. 该答案中唯一的一点是您需要跟踪数组的长度，out.toByteArray()这可以通过包装类轻松完成。
2.While downloading a file , your code needs to stream a file chunk by chunk - thats what Java streams are for.Code like below does that.
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()));
and inputStreamWrapperis like ,
并且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;
}
}
Regarding file name, if file name is not an input to end point - that means ..its system generated ( a combination of constant string plus a variable part per user ). I am not sure why you need to get that from resource.
关于文件名，如果文件名不是端点的输入 - 这意味着..它的系统生成（常量字符串加上每个用户的可变部分的组合）。我不确定您为什么需要从资源中获取它。
You won't need this wrapper if use - org.springframework.core.io.ByteArrayResource
如果使用，您将不需要此包装器 - org.springframework.core.io.ByteArrayResource
