需求
前台页面显示预约列表，点击导出按钮，导出数据
实现
使用easyExcel来实现这个需求
EasyExcel使用说明
alibaba/easyexcel
第一，创建实体类，写入excel的数据，OrderExcelEntity
public class OrderExcelEntity {
//设置表头名称
/**
* 姓名
*/
@ExcelProperty("姓名")
private String name;
/**
* 证件号码
*/
@ExcelProperty("证件号码")
private String cardId;
/**
* 居住地
*/
@ExcelProperty("居住地")
private String address;
/**
* 学校
*/
@ExcelProperty("学校")
private String school;
/**
* 接种点编码
*/
@ExcelProperty("接种点编码")
private String orgCode;
/**
* 接种点名称
*/
@ExcelProperty("接种点名称")
private String orgName;
/**
* 预约日期
*/
@ExcelProperty("预约日期")
private String appiontmentDate;
/**
* 预约时段
*/
@ExcelProperty("预约时段")
private String appointmentPeriod;
/**
* 疫苗
*/
@ExcelProperty("疫苗")
private String vaccineName;
/**
* 监护人姓名
*/
@ExcelProperty("监护人姓名")
private String guardianName;
/**
* 监护人手机号码
*/
@ExcelProperty("监护人手机号码")
private String guardianMobile;
/**
* 预约状态,1预约，0取消预约
*/
@ExcelProperty("预约状态")
private String appiontmentState;
/**
* 取消预约时间
*/
@ExcelProperty("取消预约时间")
private String cancleTime;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCardId() {
return cardId;
}
public void setCardId(String cardId) {
this.cardId = cardId;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getSchool() {
return school;
}
public void setSchool(String school) {
this.school = school;
}
public String getOrgCode() {
return orgCode;
}
public void setOrgCode(String orgCode) {
this.orgCode = orgCode;
}
public String getOrgName() {
return orgName;
}
public void setOrgName(String orgName) {
this.orgName = orgName;
}
public String getAppiontmentDate() {
return appiontmentDate;
}
public void setAppiontmentDate(String appiontmentDate) {
this.appiontmentDate = appiontmentDate;
}
public String getAppointmentPeriod() {
return appointmentPeriod;
}
public void setAppointmentPeriod(String appointmentPeriod) {
this.appointmentPeriod = appointmentPeriod;
}
public String getVaccineName() {
return vaccineName;
}
public void setVaccineName(String vaccineName) {
this.vaccineName = vaccineName;
}
public String getGuardianName() {
return guardianName;
}
public void setGuardianName(String guardianName) {
this.guardianName = guardianName;
}
public String getGuardianMobile() {
return guardianMobile;
}
public void setGuardianMobile(String guardianMobile) {
this.guardianMobile = guardianMobile;
}
public String getAppiontmentState() {
return appiontmentState;
}
public void setAppiontmentState(String appiontmentState) {
this.appiontmentState = appiontmentState;
}
public String getCancleTime() {
return cancleTime;
}
public void setCancleTime(String cancleTime) {
this.cancleTime = cancleTime;
}
}
第二，在service中编写生成excel的方法
第一种实现方式：指定文件位置，在本地生成excel，然后写入数据
/**
* 写入excel
* @return
*/
public ComServiceResVo writeExcel(String fileName, ServletOutputStream outputStream) {
List<OrderExcelEntity> list = new ArrayList<>();
List<VaccineOrderEntity> orders = repository.findAllByLogicDelFalse();
for (VaccineOrderEntity order : orders) {
OrderExcelEntity data = new OrderExcelEntity();
data.setName(order.getName()==null?"":order.getName()) ;
data.setCardId(order.getCardId()==null?"":order.getCardId()) ;
data.setAddress(order.getAddress()==null?"":order.getAddress()) ;
data.setSchool(order.getSchool()==null?"":order.getSchool()) ;
data.setOrgCode(order.getOrgCode()==null?"":order.getOrgCode()) ;
data.setOrgName(order.getOrgName()==null?"":order.getOrgName()) ;
data.setAppiontmentDate(order.getAppiontmentDate()==null?"":order.getAppiontmentDate()) ;
data.setAppointmentPeriod(order.getAppointmentPeriod()==null?"":order.getAppointmentPeriod()) ;
data.setVaccineName(order.getVaccineName()==null?"":order.getVaccineName()) ;
data.setGuardianName(order.getGuardianName()==null?"":order.getGuardianName()) ;
data.setGuardianMobile(order.getGuardianMobile()==null?"":order.getGuardianMobile()) ;
data.setAppiontmentState(order.getAppiontmentState()==0?"预约取消":"预约成功") ;
data.setCancleTime(order.getCancleTime()==null?"":order.getCancleTime()) ;
list.add(data);
}
String fileName = "D:\\预约详情"+System.currentTimeMillis()+".xlsx";
File file=new File(fileName);
if (!file.exists()){
try {
//可以表示xls和xlsx格式文件的类
XSSFWorkbook workbook = new XSSFWorkbook();
try {
//新创建的xls需要新创建新的工作簿，offine默认创建的时候会默认生成三个sheet
Sheet sheet = workbook.createSheet("预约详情");
sheet.setDefaultColumnWidth((short) 100);
FileOutputStream out = new FileOutputStream(fileName);
workbook.write(out);
System.out.println("createWorkBook success");
EasyExcel.write(fileName, OrderExcelEntity.class).sheet("预约详情").doWrite(list);
out.close();
return ComServiceResVo.ok("导出成功，保存在"+fileName);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return ComServiceResVo.badRequest("导出失败");
}
坑1：使用File createNewFile()方法创建 xlsx打不开
本地生产文件，一般是使用File类，先通过exist()方法来检测，然后通过createNewFile()方法生成。刚开始是使用这些方法生成了.xlsx文件，然后报错，刚开始以为是easyExcel方法写错的原因，后来发现生成的xlsx文件根本打不开，所以，如果要生成xlsx文件，不能使用File方法，要使用poi方式（XSSFWorkbook），poi提供了HSSFWorkbook和XSSFWorkbook两个实现类。区别在于HSSFWorkbook是针对.xls文件，XSSFWorkbook是针对.xslx文件。
创建excel文件
坑2使用XSSFWorkbook创建excel报错
pom.xml需要引入两个poi依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<properties>
...
<poi.version>4.1.2</poi.version>
</properties>
坑3报错 Can not close IO
原因要检查三个地方
1.检查:查看poi版本是否冲突
2.检查是否缺少依赖版本
3.检查poi的版本要大于等于3.17版本
我原来的版本比较小，所以poi版本要大于3.17，可以从官网上查看最新版本号
官网
第三，controller提供对外方法
@RequestMapping(value = "/excel", method = RequestMethod.GET)
@ResponseBody
public ComServiceResVo writeExcel() {
return service.writeExcel();
}
第四，js调用接口
$("#excel").click(function (e) {
$.ajax({
url: _CONTEXT_PATH + "mg/vaccine/order/excel",
error: function (errorRes, textStatus, errorThrown) {
if (errorRes.status == 401) {
window.location.replace(_CONTEXT_PATH + errorRes.responseText);
} else {
swal({title: "oops !!", text: errorRes.responseText, type: "error",}, function () {});
}
},
success:function (data) {
swal({title: "oops !!", text: data.body, type: "success",}, function () {});
}
})
}
这种实现的方式就是，前台页面点击导出，会在后台中指定位置生成文件，然后在前台弹框进行提示，"导出成功，保存在"XXXX.xlsx
另一种实现方式
不在本地保存指定位置，用户点击导出，直接从网页上下载
service中方法修改
public ComServiceResVo writeExcel(String fileName, ServletOutputStream outputStream) {
List<OrderExcelEntity> list = new ArrayList<>();
List<VaccineOrderEntity> orders = repository.findAllByLogicDelFalse();
for (VaccineOrderEntity order : orders) {
OrderExcelEntity data = new OrderExcelEntity();
data.setName(order.getName()==null?"":order.getName()) ;
data.setCardId(order.getCardId()==null?"":order.getCardId()) ;
data.setAddress(order.getAddress()==null?"":order.getAddress()) ;
data.setSchool(order.getSchool()==null?"":order.getSchool()) ;
data.setOrgCode(order.getOrgCode()==null?"":order.getOrgCode()) ;
data.setOrgName(order.getOrgName()==null?"":order.getOrgName()) ;
data.setAppiontmentDate(order.getAppiontmentDate()==null?"":order.getAppiontmentDate()) ;
data.setAppointmentPeriod(order.getAppointmentPeriod()==null?"":order.getAppointmentPeriod()) ;
data.setVaccineName(order.getVaccineName()==null?"":order.getVaccineName()) ;
data.setGuardianName(order.getGuardianName()==null?"":order.getGuardianName()) ;
data.setGuardianMobile(order.getGuardianMobile()==null?"":order.getGuardianMobile()) ;
data.setAppiontmentState(order.getAppiontmentState()==0?"预约取消":"预约成功") ;
data.setCancleTime(order.getCancleTime()==null?"":order.getCancleTime()) ;
list.add(data);
}
// LocalDateTime localDate=LocalDateTime.now();
/* String fileName = "D:\\预约详情"+System.currentTimeMillis()+".xlsx";
File file=new File(fileName);
if (!file.exists()){
try {
//可以表示xls和xlsx格式文件的类
XSSFWorkbook workbook = new XSSFWorkbook();
try {
//新创建的xls需要新创建新的工作簿，offine默认创建的时候会默认生成三个sheet
Sheet sheet = workbook.createSheet("预约详情");
sheet.setDefaultColumnWidth((short) 100);
FileOutputStream out = new FileOutputStream(fileName);
workbook.write(out);
System.out.println("createWorkBook success");
EasyExcel.write(fileName, OrderExcelEntity.class).sheet("预约详情").doWrite(list);
out.close();
return ComServiceResVo.ok("导出成功，保存在"+fileName);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
}*/
try {
//可以表示xls和xlsx格式文件的类
// XSSFWorkbook workbook = new XSSFWorkbook();
try {
//新创建的xls需要新创建新的工作簿，offine默认创建的时候会默认生成三个sheet
// Sheet sheet = workbook.createSheet("预约详情");
// sheet.setDefaultColumnWidth((short) 100);
// workbook.write(outputStream);
EasyExcel.write(outputStream, OrderExcelEntity.class).sheet("预约详情").doWrite(list);
outputStream.close();
return ComServiceResVo.ok("导出成功");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
return ComServiceResVo.badRequest("导出失败");
}
EasyExcel.write(outputStream, OrderExcelEntity.class).sheet("预约详情").doWrite(list);
outputStream.close();
这句话是关键，直接将数据写入到outputStream中，不用创建文件
controller方法修改
@RequestMapping(value = "/excel", method = RequestMethod.GET)
// @ResponseBody
public ComServiceResVo writeExcel(HttpServletResponse response) {
response.setContentType("application/binary;charset=ISO8859_1");
try {
//获取选中的column
ServletOutputStream outputStream = response.getOutputStream();
String fileName = new String(("预约详情").getBytes(), "ISO8859_1") +
DateUtil.fromInstantToString("MMddHHmm", Instant.ofEpochMilli(System.currentTimeMillis()));
response.setCharacterEncoding("utf-8");
// 组装附件名称和格式
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
service.writeExcel(DateUtil.fromInstantToString("MMddHHmm", Instant.ofEpochMilli(System.currentTimeMillis())),
outputStream);
}catch (Exception e){
e.printStackTrace();
}
return null;
// return service.writeExcel();
}
js方法修改
$("#excel").click(function (e) {
/* $.ajax({
url: _CONTEXT_PATH + "mg/vaccine/order/excel",
error: function (errorRes, textStatus, errorThrown) {
if (errorRes.status == 401) {
window.location.replace(_CONTEXT_PATH + errorRes.responseText);
} else {
swal({title: "oops !!", text: errorRes.responseText, type: "error",}, function () {});
}
},
success:function (data) {
swal({title: "oops !!", text: data.body, type: "success",}, function () {});
}
})*/
e.preventDefault();
window.location.href = _CONTEXT_PATH + "mg/vaccine/order/excel";
});
如果excel宽度不合适，可以设置样式
在实体类上添加注解
@ContentRowHeight(20)
@HeadRowHeight(20)
@ColumnWidth(20)
public class OrderExcelEntity {
2395
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
