1.在Bean实体层添加注解
@ExcelProperty(value = "年份", index = 0) //其中value为Excel的列名,index为索引,从0开始@ExcelIgnore //忽略该字段,一般id不会导出就会用到该注解
2.在controller层调用service层的方法
@RestController
@RequestMapping("/customManage")
public class CustomManageController {
@Autowired
private ICustomManageService customManageService;
@Autowired
private CustomManageMapper customManageMapper;
/**
* 销售列表导出
* @param response
* @return
* @throws Exception
*/
@PreAuthorize("hasPermit('sale:client')")
@ApiOperation(value = "销售导出-列表", tags = {"销售模块", "销售导出-列表"})
@RequestMapping(value = "/customManageListExport", method = RequestMethod.GET)
public Result customManageListExport(HttpServletResponse response) throws Exception {
try {
ExcelUtils.customerListExport(response, "客户管理", customManageMapper.findCustomManageByAll(null, null,null, null));
return Result.success(ResultCode.成功, ResultCode.成功.getCode());
} catch (Exception e) {
return Result.error(ResultCode.失败.getMsg(), ResultCode.失败.getCode(), null);
}
}
/**
* 销售详情导出
* @param
* @return
* @throws Exception
*/
@PreAuthorize("hasPermit('sale:client')")
@RequestMapping(value = "/detailExportExcel", method = RequestMethod.POST)
@ApiOperation(value = "导出详情", tags = {"销售模块", "导出详情"})
public Result detailExportExcel(HttpServletResponse response) throws Exception {
try {
ExcelUtils.detailExportExcel(response, "商业线索", customManageMapper.findCustomManageByAll(null, null,null, null));
return Result.success( ResultCode.成功, ResultCode.成功.getCode());
} catch (Exception e) {
return Result.error(ResultCode.失败.getMsg(), ResultCode.失败.getCode(), null);
}
}
}
3.当字段中出现用数字代替文字的字段时,在导出excel表格需要显示文字的形式不然看数字不知其意。此时可用公共枚举的方式来解决,先用一个数组存放这些枚举的值,再用一个for循环来循环这些枚举,并判断字段和属性是否相等,是就返回枚举对应的name(也就是对应的中文)。
package com.theiavis.workhour.project.sale.enums;
import com.baomidou.mybatisplus.annotation.EnumValue;
import com.fasterxml.jackson.annotation.JsonValue;
import lombok.Getter;
import lombok.NoArgsConstructor;
/**
* 公共枚举,其他模块的枚举也可放置这里
* @author xql
*/
@NoArgsConstructor
@Getter
public enum StatusEnum {
/**
* 客户来源
*/
错误信息("Source","0"),
客户需求("Source","1"),
自我开拓("Source","2"),
合作客户转介绍("Source","3"),
合作公司介绍("Source","4"),
展会信息("Source","5"),
协会资源转换("Source","6"),
其他渠道("Source","7"),
//此处忽略多个枚举
;//注意最后以分号结尾
/**
* 字段
*/
@EnumValue
private String key;
/**
* 属性
*/
@JsonValue
private String value;
StatusEnum(String key, String value) {
this.value = value;
this.key = key;
}
public String getKey() {
return key;
}
public String getValue() {
return value;
}
public static String getEnum(String key,String value){
//用数组存放这些属性(也就是枚举的值)
StatusEnum[] applicationStateEnums = values();
//循环判断key和value是否相等,相等就返回枚举对应的中文
for (StatusEnum itemEnum : applicationStateEnums) {
if (itemEnum.key.equals(key) && itemEnum.value.equals(value)) {
return itemEnum.name();
}
}
return null;
}
}
4.在service业务层impl里写关键代码
//销售导出-列表
public static int customerListExport(HttpServletResponse response, String excelName, List<CustomManageAndProjectVO> data) throws IOException {
//new一个链表存放customManageAndProjectExportList实体的值
List<CustomManageAndProjectExportList> customManageAndProjectExportList = new ArrayList<>();
//使用get,set封装数据,forEach循环设置并拿到每个值
data.forEach(i->{
CustomManageAndProjectExportList customManageAndProjectExport= new CustomManageAndProjectExportList();
customManageAndProjectExport.setCompany(i.getCompany());
customManageAndProjectExport.setCreaterName(i.getCreaterName());
customManageAndProjectExport.setCreateTime(i.getCreateTime());
customManageAndProjectExport.setFollowName(i.getFollowName());
customManageAndProjectExport.setProjName(i.getProjName());
customManageAndProjectExport.setSaleTimeMonth(i.getSaleTimeMonth());
customManageAndProjectExport.setSaleTimeYear(i.getSaleTimeYear());
//因为Stage是枚举类型,调用StatusEnum.getEnum()方法,拿到枚举对应的中文
customManageAndProjectExport.setStage(StatusEnum.getEnum( "Stage",i.getStage()));
//把customManageAndProjectExport中的数据添加到customManageAndProjectExportList链表中
customManageAndProjectExportList.add(customManageAndProjectExport);
});
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setCharacterEncoding("utf-8");
response.setHeader("content-disposition", " attachment;fileName=" + java.net.URLEncoder.encode(excelName+".xlsx", "UTF-8"));
EasyExcel.write(response.getOutputStream(), CustomManageAndProjectExportList.class).sheet(excelName).doWrite(customManageAndProjectExportList);
return 0;
}
//销售详情导出
public static int detailExportExcel(HttpServletResponse response, String excelName, List<CustomManageAndProjectVO> data) throws IOException {
//forEach循环拿到枚举对应的中文
data.forEach(i->{
i.setSource(StatusEnum.getEnum( "Source",i.getSource()));
i.setProjectType(StatusEnum.getEnum( "ProjectType",i.getProjectType()));
i.setCustomType(StatusEnum.getEnum( "CustomType",i.getCustomType()));
i.setFollowResult(StatusEnum.getEnum( "FollowResult",i.getFollowResult()));
});
/**
* response.setContentType()用于设置发送到客户端的响应的内容类型
* 参数类型有以下几种:
* text/html响应类型为HTML格式的,text/plain响应类型为文本格式的,application/msword响应类型为word格式的,application/vnd.ms-响应类型为excel格式的等
* 注意:该方法要在getWriter()方法之前使用,这样响应的字符编码将从给出的内容类型中设置,否则,将不会响应设置的字符编码
*/
//让服务器告诉浏览器它发送的数据属于excel文件类型
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//编码格式为UTF-8
response.setCharacterEncoding("utf-8");
/**
* 防止fileName为中文时乱码
* 告诉浏览器这个文件的名字和类型,attachment:作为附件下载;inline:直接打开
* Content-Disposition中指定的类型是文件的扩展名,并且弹出的下载对话框中的文件类型图片是按照文件的扩展名显示的,点保存后,文件以filename的值命名,保存类型以Content中设置的为准。
* 注意:在设置Content-Disposition头字段之前,一定要设置Content-Type头字段。
*/
response.setHeader("content-disposition", " attachment;fileName=" + java.net.URLEncoder.encode(excelName+".xlsx", "UTF-8"));
//write()方法 参数1:文件路径名称 参数2:实体类class
//sheet()方法 工作表对象(名称)
//doWrite()方法 写出数据
EasyExcel.write(response.getOutputStream(), CustomManageAndProjectVO.class).sheet(excelName).doWrite(data);
return 0;
}
5.当导出的数据要求需要用到其他表的数据时,可在mapper层进行连表操作。
<select id="findCustomManageByAll" resultType="com.theiavis.workhour.project.sale.vo.CustomManageAndProjectVO">
SELECT
<include refid="Base_Column_List"/>,<!-- 引用通用查询列 -->
c.source as source,
c.address as address,
<!-- c.project_type 对应数据库中的字段名,as后面跟着的是别名,'as'可省略,projectType在实体类中有定义,注意逗号不要忘了,最后一句无需加逗号 -->
c.project_type as projectType,
c.follow_result as followResult,
c.follow_fail_result as followFailResult,
c.proj_need as projNeed
<include refid="Condition"/>
</select>
<sql id="Condition">
FROM custom_manage AS a
LEFT JOIN proj_agreement c ON c.id =a.agreement_id
<!-- where后面跟着的是条件,可省略 -->
where
a.is_delete=0 and a.id
Order By a.create_time Desc
</sql>