提示:文章如有错误的地方请指出,以免误人子弟!
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")
public void exportPersonExcel(
@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
) throws IOException {
// 查询数据
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 (接口实现类)
@Override
public List<FieldPersonnelExcel> exportPersonExcel(String reason, String state, String startTime, String endTime) {
List<FieldPersonnel> fieldPersonnels = fieldPersonnelMapper.exportPersonExcel(reason, state, startTime, endTime);
return exportPersonExcelUtil(fieldPersonnels);
}
用到的封装方法。这边我是dao层的实体类和excel的实体类是分开的,看起来清爽点。然后这边我是一个一个set进去的,因为有的数据需要进行转换,
当然不需要转换并且对象参数都差不多一样的也可以使用 BeanUtils.copyProperties() 对象拷贝。
注意:
copyProperties()方法有两个包
封装方法:(具体数据转换就不用太关注了)
public List<FieldPersonnelExcel> exportPersonExcelUtil(List<FieldPersonnel> fieldPersonnels) {
// 导出实体类
List<FieldPersonnelExcel> fieldPersonnelExcels = new ArrayList<>(fieldPersonnels.size());
int index = 1;
for (FieldPersonnel fieldPersonnel :fieldPersonnels) {
FieldPersonnelExcel fieldPersonnelExcel = new FieldPersonnelExcel();
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 官网
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import lombok.Data;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import java.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)
public class FieldPersonnelExcel implements Serializable {
@ExcelIgnore
@ColumnWidth(8)
@ExcelProperty(value = {"外勤信息","id"}, index = 0)
private Integer id;
@ColumnWidth(8)
@ExcelProperty(value = {"外勤信息","状态"})
private String state;
@ColumnWidth(20)
@ExcelProperty(value = {"外勤信息","创建人姓名"})
private String createUser;
@ColumnWidth(90)
@ExcelProperty(value = {"外勤信息","外勤理由"})
private String reason;
@ColumnWidth(35)
@ExcelProperty(value = {"外勤信息","录入时间"})
private String createTime;
@ColumnWidth(30)
@ExcelProperty(value = {"外勤信息","项目"})
private String project;
@ColumnWidth(20)
@ExcelProperty(value = {"外勤信息","外勤理由申请人"})
private String applicant;
@ColumnWidth(35)
@ExcelProperty(value = {"外勤信息","外勤申请时间"})
private String applicationTime;
}
希望对你有所帮助!