前端发送请求:
window.location.href= config.base_server + “troutProve/ee?gida=”+aa+"&Authorization=" + config.getToken().access_token;
/**
* poi 批量导出excel
* @return
*/
@GetMapping("/ee")
public void Ee(String gida, HttpServletResponse response) throws IOException { //response 给前端显示
List a = null;
String[] gid = gida.split(",");
a= troutProveService.poi(gid);
HSSFWorkbook workbook = new HSSFWorkbook();
//2.2创建一个标签页
HSSFSheet sheet = workbook.createSheet("cesh");
//指定合并单元格
// CellRangeAddress cra=new CellRangeAddress(0, 2, 3, 6);
//在sheet里增加合并单元格
// sheet.addMergedRegion(cra);
//2.3创建标题行
HSSFRow headRow = sheet.createRow(0);
headRow.createCell(0).setCellValue("公司");
headRow.createCell(1).setCellValue("批号");
headRow.createCell(2).setCellValue("签证时间");
headRow.createCell(3).setCellValue("发送方式");
headRow.createCell(4).setCellValue("邮寄类型");
headRow.createCell(5).setCellValue("邮寄时间");
headRow.createCell(6).setCellValue("原料");
headRow.createCell(7).setCellValue("出口成品");
headRow.createCell(8).setCellValue("办证费用");
headRow.createCell(9).setCellValue("邮寄费用");
headRow.createCell(10).setCellValue("邮寄次数");
headRow.createCell(11).setCellValue("备注");
sheet.setColumnWidth(0,17*256); //设置sheet标签页列宽
sheet.setColumnWidth(2,17*256);
sheet.setColumnWidth(3,13*256);
sheet.setColumnWidth(4,13*256);
sheet.setColumnWidth(6,10*256);
sheet.setColumnWidth(7,10*256);
sheet.setColumnWidth(8,10*256);
sheet.setColumnWidth(9,10*256);
sheet.setColumnWidth(5,17*256);
sheet.setColumnWidth(10,17*256);
sheet.setColumnWidth(11,50*256);
/**
-
给表格填写数据
*/for (int i = 0; i < a.size(); i++) {
int di=1;
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum()+di); //创建一行,每条数据+1行dataRow.setHeight((short) (17*20));//设置行高 if (i<a.size()-1){ if (a.get(i+1).getCpName().equals(a.get(i).getCpName())&&(i<a.size()-1)){ dataRow.createCell(0).setCellValue(""); } } if (a.get(i).getCpName() == null) { dataRow.createCell(0).setCellValue(""); }else{ dataRow.createCell(0).setCellValue(a.get(i).getCpName()); } if (a.get(i).getBatchNumber() == null) { dataRow.createCell(1).setCellValue(""); }else{ dataRow.createCell(1).setCellValue(a.get(i).getBatchNumber()); } if (a.get(i).getCertificateTime() == null) { dataRow.createCell(2).setCellValue(""); }else{ dataRow.createCell(2).setCellValue(a.get(i).getCertificateTime()); } if (a.get(i).getSendType() == null) { dataRow.createCell(3).setCellValue(""); }else{ dataRow.createCell(3).setCellValue(a.get(i).getSendType()); } if (a.get(i).getMailType() == null) { dataRow.createCell(4).setCellValue(""); }else { dataRow.createCell(4).setCellValue(a.get(i).getMailType()); } if (a.get(i).getMailTime() == null) { dataRow.createCell(5).setCellValue(""); }else{ dataRow.createCell(5).setCellValue(a.get(i).getMailTime()); } if (a.get(i).getRawMaterial() == 0) { dataRow.createCell(6).setCellValue(0); }else{ dataRow.createCell(6).setCellValue(a.get(i).getRawMaterial()); } if (a.get(i).getExitAccomplish() == 0) { dataRow.createCell(7).setCellValue(0); }else{ dataRow.createCell(7).setCellValue(a.get(i).getExitAccomplish()); } if (a.get(i).getCost() == null) { dataRow.createCell(8).setCellValue(""); }else { dataRow.createCell(8).setCellValue(a.get(i).getCost()); } if (a.get(i).getMailPrice() == 0) { dataRow.createCell(9).setCellValue(0); }else{ dataRow.createCell(9).setCellValue(a.get(i).getMailPrice()); } if (a.get(i).getMailNum() == 0) { dataRow.createCell(10).setCellValue(0); }else{ dataRow.createCell(10).setCellValue(a.get(i).getMailNum()); } if (a.get(i).getRemark() == null) { dataRow.createCell(11).setCellValue(""); }else{ dataRow.createCell(11).setCellValue(a.get(i).getRemark()); }
}
String filename = “”;
try {
Date d = new Date();
long timeStemp = d.getTime(); //转换时间戳
//3.输出流进行文件下载
filename = "分区信息表.xls";
File file = new File(filename);
FileOutputStream file1 = new FileOutputStream(file);
workbook.write(file1);
file1.close();
//直接获取输出,直接输出excel(优先使用)
OutputStream output=response.getOutputStream();
response.reset();
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
response.setContentType("application/msexcel");
workbook.write(output);
output.close();
}catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
File fileDelete = new File(filename);
if (fileDelete.exists()&&fileDelete.isFile()){
fileDelete.delete();
}
/* Date d = new Date();
long timeStemp = d.getTime(); //转换时间戳
String outPath =timeStemp+filename;
OutputStream os = null;
InputStream fin = null;
try {
os =new FileOutputStream(outPath);
} catch (FileNotFoundException e) {
e.printStackTrace();
}*/
/* //设置头信息
OutputStream os = null;
try {
//根据路径创建输出流
//如果是在controller里可以使用reponse.getOutputStream()方法获取输出流
os = new FileOutputStream(outPath);
workbook.write(os);
} catch (Exception e) {
System.out.println("导出出错");
e.printStackTrace();
}finally {
try {
if (os != null) {
os.close();
}
workbook.close();
} catch (Exception e) {
System.out.println("workbook 流关闭失败");
}
}
*/
// String fileName = “鲑鱼.xls”;
/* try {
File file = new File(outPath);
FileOutputStream file1 = new FileOutputStream(file);
workbook.write(file1);
file1.close();
OutputStream out = response.getOutputStream();
response.reset();
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(outPath, "UTF-8"))));
response.setContentType("application/msexcel;charset=UTF-8");
workbook.write(out);
out.flush();
out.close();
//删除文件
File fileDelete = new File(outPath);
if (fileDelete.exists()&&fileDelete.isFile()){
fileDelete.delete();
}
} catch (FileNotFoundException e) {
JOptionPane.showMessageDialog(null, "导出失败!");
e.printStackTrace();
} catch (IOException e) {
JOptionPane.showMessageDialog(null, "导出失败!");
e.printStackTrace();
}
*/
}