提示:文章如有错误的地方请指出,以免误人子弟!
easyexcel 导出
提示:以下是本篇文章正文内容,下面案例可供参考
一、导入maven jar包
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency>
二、直接上代码
- controller
@RequestParam:将请求参数绑定到你控制器的方法参数上(是springmvc中接收普通参数的注解),参数:value:请求参数名(必须配置),required:是否必需,默认为 true,即 请求中必须包含该参数,如果没有包含,将会抛出异常(可选配置)
@GetMapping("/allExcel")publicvoidexportPersonExcel(@RequestParam(value="reason", required=false)String reason,@RequestParam(value="state", required=false)String state,@RequestParam(value="startTime", required=false)String startTime,@RequestParam(value="reason", required=false)String endTime,HttpServletResponse response)throwsIOException{// 查询数据List<FieldPersonnelExcel> fieldPersonnelExcels= fieldPersonnelService.exportPersonExcel(reason, state, startTime, endTime);// 生成时间戳String downloadExcelFile=SerialNumber.getInstance().getNumber();// 文件名
downloadExcelFile+="外勤理由"+".xlsx";
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition","attachment;fileName="+URLEncoder.encode(downloadExcelFile,"UTF-8").replaceAll("\\+","%20"));ServletOutputStream outputStream= response.getOutputStream();EasyExcel.write(outputStream,FieldPersonnelExcel.class).sheet("外勤理由").doWrite(fieldPersonnelExcels);
outputStream.close();}
- service
/**
* description: 外勤理由导出
*
* @param
* @return void
* @author Mr.Tiger
*/List<FieldPersonnelExcel>exportPersonExcel(String reason,String state,String startTime,String endTime);
- serviceImpl (接口实现类)
@OverridepublicList<FieldPersonnelExcel>exportPersonExcel(String reason,String state,String startTime,String endTime){List<FieldPersonnel> fieldPersonnels= fieldPersonnelMapper.exportPersonExcel(reason, state, startTime, endTime);returnexportPersonExcelUtil(fieldPersonnels);}
用到的封装方法。这边我是dao层的实体类和excel的实体类是分开的,看起来清爽点。然后这边我是一个一个set进去的,因为有的数据需要进行转换,
当然不需要转换并且对象参数都差不多一样的也可以使用 BeanUtils.copyProperties() 对象拷贝。注意:
copyProperties()方法有两个包
封装方法:(具体数据转换就不用太关注了)
publicList<FieldPersonnelExcel>exportPersonExcelUtil(List<FieldPersonnel> fieldPersonnels){// 导出实体类List<FieldPersonnelExcel> fieldPersonnelExcels=newArrayList<>(fieldPersonnels.size());int index=1;for(FieldPersonnel fieldPersonnel:fieldPersonnels){FieldPersonnelExcel fieldPersonnelExcel=newFieldPersonnelExcel();
fieldPersonnelExcel.setId(index);String state=null;if("1".equals(fieldPersonnel.getState())){
state="未使用";}else{
state="已使用";}
fieldPersonnelExcel.setState(state);
fieldPersonnelExcel.setCreateUser(fieldPersonnel.getCreateUser());
fieldPersonnelExcel.setReason(fieldPersonnel.getReason());
fieldPersonnelExcel.setCreateTime(fieldPersonnel.getCreateTime());
fieldPersonnelExcel.setProject(fieldPersonnel.getProject().getProjectName());
fieldPersonnelExcel.setApplicant(fieldPersonnel.getApplicant());
fieldPersonnelExcel.setApplicationTime(fieldPersonnel.getApplicationTime());
fieldPersonnelExcels.add(fieldPersonnelExcel);
index++;}return fieldPersonnelExcels;}
- mapper
/**
* description: 导出查询外勤信息
*
* @param
* @return com.github.pagehelper.Page<com.yrs.wcptc.entity.person.FieldPersonnel>
* @author Mr.Tiger
*/List<FieldPersonnel>exportPersonExcel(@Param("reason")String reason,@Param("state")String state,@Param("startTime")String startTime,@Param("endTime")String endTime);
- sql 就不贴出来了。
三、重点来了,ecxel导出的实体类
controller 里面导出方法需要导出的模版实体类
excel实体类:
更多的注解参数,请参考easyexcel 官网
importcom.alibaba.excel.annotation.ExcelIgnore;importcom.alibaba.excel.annotation.ExcelProperty;importcom.alibaba.excel.annotation.write.style.*;importlombok.Data;importorg.apache.poi.ss.usermodel.FillPatternType;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importjava.io.Serializable;/**
* @Description:
* @Author: Tigger
* @Date: 2021/10/15 13:24
* HeadStyle:表头背景颜色为白色
* ContentRowHeight:内容行高
* HeadRowHeight:表头行高
* ContentStyle:内容水平居中
*/@Data@HeadStyle(fillPatternType=FillPatternType.SOLID_FOREGROUND, fillForegroundColor=9)@ContentRowHeight(18)@HeadRowHeight(25)@ContentStyle(horizontalAlignment=HorizontalAlignment.CENTER)publicclassFieldPersonnelExcelimplementsSerializable{@ExcelIgnore@ColumnWidth(8)@ExcelProperty(value={"外勤信息","id"}, index=0)privateInteger id;@ColumnWidth(8)@ExcelProperty(value={"外勤信息","状态"})privateString state;@ColumnWidth(20)@ExcelProperty(value={"外勤信息","创建人姓名"})privateString createUser;@ColumnWidth(90)@ExcelProperty(value={"外勤信息","外勤理由"})privateString reason;@ColumnWidth(35)@ExcelProperty(value={"外勤信息","录入时间"})privateString createTime;@ColumnWidth(30)@ExcelProperty(value={"外勤信息","项目"})privateString project;@ColumnWidth(20)@ExcelProperty(value={"外勤信息","外勤理由申请人"})privateString applicant;@ColumnWidth(35)@ExcelProperty(value={"外勤信息","外勤申请时间"})privateString applicationTime;}
希望对你有所帮助!