前端页面代码
方法一:post请求:可设置token
<Button type="primary" @click="exportData()"> 导出数据</Button>
<script>
//下载数据
exportData () {
const peram = {
title:this.activityApplicationPage.title,
contactPhone:this.activityApplicationPage.contactPhone,
applicationTime:this.activityApplicationPage.applicationTime,
applicationOverTime:this.activityApplicationPage.applicationOverTime,
enterpriseName:this.activityApplicationPage.enterpriseName,
}
this.$http({
method: 'post',
url: "http://localhost/8080"+"sa/api/activity/activityApplicationExcel",
data:peram,
responseType: 'blob'//这里的响应类型必须设置为blob
//这里可以加token,我设置了全局的,所有的请求都默认加了。
})
.then(function(res) {
console.log(res)
const link = document.createElement('a')
let blob = new Blob([res.data],{type: 'application/vnd.ms-excel'});
link.style.display = 'none'
link.href = URL.createObjectURL(blob);
link.setAttribute('download', 'xxxxx文件名称.xls')
document.body.appendChild(link)
link.click()
document.body.removeChild(link)
})
.catch(function(error) {
console.log(error);
});
},
</script>
后端代码
1.引入坐标
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
2.Contrpller
@PostMapping("/activity/activityApplicationExcel")
public void activityApplicationExcel(HttpServletResponse response, @RequestBody ActivityApplicationPage page) {
//条件的参数可以在这进行校验
//设置表格表头字段
String [] excelTitle = new String[]{"企业名称","联系人","联系电话","活动名称","报名人数","报名时间"};
//根据查询条件查询
List<ActivityApplicationExcel> list = activityApplicationService.activityApplicationExcel(page);//查询数据
//使用工具类创建表格
ExcelUtil.export(response,"活动报名数据",excelTitle,list);
}
3.工具类:最关键的是这几段
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;Filename=" + new String(sheetTitle.getBytes("utf-8"),"ISO-8859-1" ) + ".xls");
out = response.getOutputStream();
wb.write(out);
/**
* @作者 zcq
* @创建日期
* @版本 V1.2
* @描述 Excel 导出通用工具类
*/
public class ExcelUtil {
public static void export(HttpServletResponse response, String sheetTitle, String[] title, List<ActivityApplicationExcel> list) {
HSSFWorkbook wb = new HSSFWorkbook();//创建excel表
HSSFSheet sheet = wb.createSheet(sheetTitle);
sheet.setDefaultColumnWidth(20);//设置默认行宽
//表头样式(加粗,水平居中,垂直居中)
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置边框样式
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
HSSFFont fontStyle = wb.createFont();
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(fontStyle);
//标题样式(加粗,垂直居中)
HSSFCellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cellStyle2.setFont(fontStyle);
//设置边框样式
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//字段样式(垂直居中)
HSSFCellStyle cellStyle3 = wb.createCellStyle();
cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置边框样式
cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//创建表头
HSSFRow row = sheet.createRow(0);
row.setHeightInPoints(20);//行高
HSSFCell cell = row.createCell(0);
cell.setCellValue(sheetTitle);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,(title.length-1)));
//创建标题
HSSFRow rowTitle = sheet.createRow(1);
rowTitle.setHeightInPoints(20);
HSSFCell hc;
for (int i = 0; i < title.length; i++) {
hc = rowTitle.createCell(i);
hc.setCellValue(title[i]);
hc.setCellStyle(cellStyle2);
}
byte result[] = null;
OutputStream out = null;
try {
//创建表格数据
Field[] fields;
int i = 2;
for (Object obj : list) {
fields = obj.getClass().getDeclaredFields();
HSSFRow rowBody = sheet.createRow(i);
rowBody.setHeightInPoints(20);
int j = 0;
for (Field f : fields) {
f.setAccessible(true);
Object va = f.get(obj);
if (null == va) {
va = "";
}
hc = rowBody.createCell(j);
hc.setCellValue(va.toString());
hc.setCellStyle(cellStyle3);
j++;
}
i++;
}
//设置Http响应头告诉浏览器下载这个附件
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;Filename=" + new String(sheetTitle.getBytes("utf-8"),"ISO-8859-1" ) + ".xls");
out = response.getOutputStream();
wb.write(out);
} catch (Exception ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
} finally{
try {
if(null != out){
out.close();
}
} catch (IOException ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
} finally{
try {
wb.close();
} catch (IOException ex) {
Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
}
4.实体类
@Data
public class ActivityApplicationExcel {
private String firmName;//企业名称
private String contactPerson;//联系人
private String contactPhone;//联系电话
private String title;//活动名称
private Integer amount;//报名人数
private String createdDate;//报名时间
public ActivityApplicationExcel() {
}
public ActivityApplicationExcel(String firmName, String contactPerson, String contactPhone, String title, Integer amount, String createdDate) {
this.firmName = firmName;
this.contactPerson = contactPerson;
this.contactPhone = contactPhone;
this.title = title;
this.amount = amount;
this.createdDate = createdDate;
}
}
注意:表格的列要和实体类的中的个数一组,否则错位
方法2:请求方式无法加token
前端代码
<Button type="primary" @click="exportData()"> 导出数据</Button>
<script>
//下载数据
exportData () {
//参数全靠拼接,无法加token
window.location.href = http://localhost/8080 +"sa/api/activity/activityApplicationExcel?" +
"title="+this.activityApplicationPage.title+"&contactPhone="+this.activityApplicationPage.contactPhone+
"&applicationTime="+this.activityApplicationPage.applicationTime+"&applicationOverTime="+this.activityApplicationPage.applicationOverTime+
"&enterpriseName="+this.activityApplicationPage.enterpriseName;
},
</script>
后端代码
1.Contrpller
/**
* 管理端活动报名报表导出
* @param page
* @return
*/
@RequestMapping("/activity/activityApplicationExcel")
public void activityApplicationExcel(HttpServletResponse response,
@RequestParam String title,
@RequestParam String contactPhone,
@RequestParam String applicationTime,
@RequestParam String applicationOverTime,
@RequestParam String enterpriseName) {
ActivityApplicationPage page = new ActivityApplicationPage();
page.setTitle(title);
page.setContactPhone(contactPhone);
page.setApplicationTime(applicationTime);
page.setApplicationOverTime(applicationOverTime);
page.setEnterpriseName(enterpriseName);
//设置表格表头字段
String [] excelTitle = new String[]{"企业名称","联系人","联系电话","活动名称","报名人数","报名时间"};
//根据查询条件查询
List<ActivityApplicationExcel> list = activityApplicationService.activityApplicationExcel(page);//查询数据
//创建表格
ExcelUtil.export(response,"活动报名数据",excelTitle,list);
}
其他的工具类和实体类都和 方法一 的一样