Java 如何从 Spring MVC 导出到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30049511/
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
How to export to Excel from Spring MVC
提问by Breon
I want to export some data which I have retrieved from the database to the controller level. From the controller I need to export this data to an Excel file without using a view.
我想将从数据库中检索到的一些数据导出到控制器级别。我需要从控制器将此数据导出到 Excel 文件而不使用视图。
I wrote:
我写：
ReportSearchVO searchL = formL.getObjReportSearchG();
loggerG.info("Resource List:" + searchL.getResourceListG());
projDetailReportL = reportServiceG.createProjectDetailReport(formL);
formL.setProjDetailReport(projDetailReportL);
formL.setReportTypeEnum(ReportTypeEnum.PROJECTDETAILREPORT);
formL.setObjReportSearchG(searchL);
requestR.setAttribute("resLevelForm", formL);
returnModelAndView = new ModelAndView(
ViewConstants.FINAL_VIEW_PROJECT_DETAILS_REPORT, "reportForm",
formL);
but this uses a view.
但这使用了一个视图。
回答by Brett Walker
In the past when I have need to generate Excel Documents I have used Apache POIto create the file.
过去，当我需要生成 Excel 文档时，我曾使用Apache POI创建文件。
回答by Vishnu
Apache POI is inhertly supported by spring, it provides AbstractExcelViewto provide excel downloads.
Apache POI 是spring 固有的支持，它AbstractExcelView提供excel 下载。
Sample code:
示例代码：
public class ExcelBuilder extends AbstractExcelView {
@Override
protected void buildExcelDocument(Map<String, Object> input,
HSSFWorkbook workbook, HttpServletRequest arg2, HttpServletResponse response)
throws Exception {
response.setHeader("Content-Disposition", "attachment; filename=\"sample.xls\"");
// create a new Excel sheet
HSSFSheet sheet = workbook.createSheet("Test");
sheet.setDefaultColumnWidth(30);
// create style for header cells
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName("Arial");
style.setFillForegroundColor(HSSFColor.BLUE.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.WHITE.index);
style.setFont(font);
// create header row
HSSFRow header = sheet.createRow(0);
header.createCell(0).setCellValue("Title");
header.getCell(0).setCellStyle(style);
header.createCell(1).setCellValue("col2");
header.getCell(1).setCellStyle(style);
header.createCell(2).setCellValue("col3");
header.getCell(2).setCellStyle(style);
header.createCell(3).setCellValue("col4");
header.getCell(3).setCellStyle(style);
header.createCell(4).setCellValue("col 5");
header.getCell(4).setCellStyle(style);
//Your data goes here
}
}
If you just want the excel download without poi, is just set the content dispostion header in your jsp and from controller direct return the view that suggests the jsp. Be warned that when you do like this you are just pasting the content of jsp as html in the excel (a valid file can be opened on Microsoft excel too), so no macro's or function work with that.
如果您只想要不带 poi 的 excel 下载，只需在您的 jsp 中设置内容处理标题，然后从控制器直接返回建议 jsp 的视图。请注意，当您这样做时，您只是将 jsp 的内容作为 html 粘贴到 excel 中（也可以在 Microsoft excel 上打开有效文件），因此没有宏或函数可以使用它。
回答by ares
without using a view
To not use a view, you have to make the return type of your request mapping method to void
要不使用视图，您必须将请求映射方法的返回类型设置为 void
@Controller
public class MyController{
@RequestMapping("/xyz")
public void getExcel(HttpServletRequest request, HttpServletResponse response){
// 1. Fetch your data
// 2. Create your excel
// 3. write excel file to your response.
}
}
I believe you've already done part 1. Part 2 is completely differnt thing and you have to use some third party api to do that. Apache POI is very simple and effective. https://poi.apache.org/spreadsheet/. Their quickguideis nice to sart with.
我相信你已经完成了第 1 部分。第 2 部分是完全不同的事情，你必须使用一些第三方 api 来做到这一点。Apache POI 非常简单有效。https://poi.apache.org/spreadsheet/。他们的快速指南很好用。
Once you have created your file, now you need to write it to response so that it can be downloaded to client end. Here's how you can do that. Lets say the excel you created is xyz.xls
创建文件后，现在需要将其写入响应，以便可以将其下载到客户端。以下是您如何做到这一点。假设您创建的 excel 是xyz.xls
response.setContentType("application/octet-stream"); // set content attributes for the response
FileInputStream inputStream = new FileInputStream(new File("xyz.xls"));
OutputStream outputStream = response.getOutputStream(); // get output stream of the response
byte[] buffer = new byte[1024];
int bytesRead = -1;
while ((bytesRead = inputStream.read(buffer)) != -1) { // write bytes read from the input stream into the output stream
outputStream.write(buffer, 0, bytesRead);
}
outputStream.flush();
回答by user3358994
Using AbstractExcelView and ModalAndView its possible in SpringMVC. Refer below for more details
在 SpringMVC 中使用 AbstractExcelView 和 ModalAndView 成为可能。请参阅下文了解更多详情
http://learnfromexamples.com/generate-excel-in-spring-mvc-application-using-apache-poi/
http://learnfromexamples.com/generate-excel-in-spring-mvc-application-using-apache-poi/
回答by Ibrahima Timera
it's work
这是工作
In your controller
在您的控制器中
@RequestMapping(value = "/downloadExcel", method = RequestMethod.GET)
public ModelAndView downloadExcel(Model model) {
List<String> usersGateways = uDAO.GetGwRoleUser();
List<User> users = gatewayManagedDAO.findAll();
return new ModelAndView(new ExcelView(), "users ", users );
}
}
In your ExcelView
在您的 Excel 视图中
public class ExcelView extends AbstractXlsView{
@Override
public void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
// change the file name
response.setHeader("Content-Disposition", "attachment; filename=\"my-exported-file.xls\"");
@SuppressWarnings("unchecked")
List<User> users= (List<GatewayManage>) model.get("users");
// create excel xls sheet
Sheet sheet = workbook.createSheet("Users Detail");
sheet.setDefaultColumnWidth(30);
// create style for header cells
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName("Arial");
style.setFillForegroundColor(HSSFColor.BLUE.index);
//style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//font.setBold(true);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
// create header row
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("First Name");
header.getCell(0).setCellStyle(style);
header.createCell(1).setCellValue("Last Name");
header.getCell(1).setCellStyle(style);
header.createCell(2).setCellValue("Number");
header.getCell(2).setCellStyle(style);
header.createCell(3).setCellValue("Age");
header.getCell(3).setCellStyle(style);
int rowCount = 1;
for(User user : users){
Row userRow = sheet.createRow(rowCount++);
gatewayRow.createCell(0).setCellValue(user.getFirstName());
gatewayRow.createCell(1).setCellValue(gateway.getLastName());
gatewayRow.createCell(2).setCellValue(gateway.getNumber());
gatewayRow.createCell(3).setCellValue(gateway.getAge());
}
}
}
You can replace my User class by yours (Studen, aBook ....) and it's work!
你可以用你的（Studen，aBook ....）替换我的用户类，它的工作！
回答by Ajay Kumar
Prepare your Excel sheet like this(using apache poi). And then (for example) in your Controller you can easily write it to the body :
像这样准备你的 Excel 工作表（使用 apache poi）。然后（例如）在您的 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));
}
The full blown example is here.
完整的例子在这里。
回答by BaiJiFeiLong
A complete example for serving XLSXgenerated in memory:
XLSX在内存中生成的服务的完整示例：
package io.github.baijifeilong.excel;
import lombok.SneakyThrows;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
/**
* Created by [email protected] at 2019-08-20 16:36
*/
@SpringBootApplication
@RestController
public class ExcelApp {
public static void main(String[] args) {
SpringApplication.run(ExcelApp.class, args);
}
@SneakyThrows
@GetMapping(value = "/")
public void index(HttpServletResponse response) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
XSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("OK");
response.addHeader("Content-Disposition", "attachment; filename=world.xlsx");
workbook.write(response.getOutputStream());
}
}
