Controller
public void export() {
String filePath = "";
StringBuffer modelPath = new StringBuffer();
try {
filePath = getRequest().getServletContext().getResource("filemodel/excel/xxx.xlsx").getPath();
} catch (MalformedURLException e) {
e.printStackTrace();
renderErr("文件路径错误");
}
File file=new File(filePath);
List<Object[]> list = XXX.me.export(); // 查询到数据
Workbook book = XXX.me.getExcelModel(file, list); // Service方法 将数据与文件合并到 Workbook
try {
getResponse().setHeader("Content-Type","application/zip");
getResponse().setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode("XXX.xlsx", "UTF-8"));
book.write(getResponse().getOutputStream()); // 用数据流的方式响应给浏览器
} catch(Exception e) {
e.printStackTrace();
}
renderNull();
}
使用模板方法
public Workbook getExcelModel(File fileExcel, List<Object[]> list) {
Workbook book = getExcelWorkbook(fileExcel);
if(null == book) {
return null;
}
Row row = null;
Sheet sheet = book.getSheetAt(0);
for(int i=0; i<list.size(); i++) {
row = sheet.createRow(i+1);
for(int j=0; j<list.get(i).length; j++) {
row.createCell(j).setCellValue(list.get(i)[j] == null ? "" : list.get(i)[j].toString());
}
}
return book;
}
不使用模板方法
public Workbook getExcelModel(List<Object[]> list) {
Workbook book = new XSSFWorkbook();
if(null == book) {
return null;
}
Row row = null;
Sheet sheet = book.createSheet("指导书");
sheet.setDefaultColumnWidth(20);
try{
row = sheet.createRow(0);
row.createCell(0).setCellValue("XX");
row.createCell(1).setCellValue("XXX");
row.createCell(2).setCellValue("品牌");
row.createCell(3).setCellValue("检查内容");
row.createCell(4).setCellValue("检查方法");
row.createCell(5).setCellValue("推荐值");
row.createCell(6).setCellValue("判断标准");
row.createCell(7).setCellValue("整改类别");
row.createCell(8).setCellValue("整改建议");
row.createCell(9).setCellValue("问题描述");
for(int i=0; i<list.size(); i++) {
row = sheet.createRow(i+1);
for(int j=0; j<list.get(i).length; j++) {
row.createCell(j).setCellValue(list.get(i)[j] == null ? "" : list.get(i)[j].toString());
}
}
} catch(Exception e) {
e.printStackTrace();
}
return book;
}