以我这里为例，我这个是批量导入装备，因为装备有主键（id），装备还有类型(type),所有
第一步先建立两个Dto第一个Dto有一个类型第一个是id，用来接受你的勾选导出，代码如下
public class BizMaterialIdsDto {
/**
* materialIds 拼接的字符串，多个用 ","拼接
*/
private String materialIds;
public String getMaterialIds() {
return materialIds;
}
public void setMaterialIds(String materialIds) {
this.materialIds = materialIds;
}
}
第二个Dto是用来接受返回映射的
public class BizMaterialIdAndMaterialNameDto {
private String materialId;
private String materialCode;
private String materialName;
private String materialLevelCode;
private String aIndex;
private String parentId;
private String type;
public String getMaterialId() {
return materialId;
}
public void setMaterialId(String materialId) {
this.materialId = materialId;
}
private List<BizMaterialIdAndMaterialNameDto> childList = new ArrayList<>();
public String getMaterialCode() {
return materialCode;
}
public void setMaterialCode(String materialCode) {
this.materialCode = materialCode;
}
public String getMaterialName() {
return materialName;
}
public void setMaterialName(String materialName) {
this.materialName = materialName;
}
public String getMaterialLevelCode() {
return materialLevelCode;
}
public void setMaterialLevelCode(String materialLevelCode) {
this.materialLevelCode = materialLevelCode;
}
public String getaIndex() {
return aIndex;
}
public void setaIndex(String aIndex) {
this.aIndex = aIndex;
}
public String getParentId() {
return parentId;
}
public void setParentId(String parentId) {
this.parentId = parentId;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public List<BizMaterialIdAndMaterialNameDto> getChildList() {
return childList;
}
public void setChildList(List<BizMaterialIdAndMaterialNameDto> childList) {
this.childList = childList;
}
}
第二步，写一个导出类，用来哪些需要导出的数据，并不是所有数据都要导出，一般情况下只需要导出部门数据以我这里为例只导出了6个数据
public class BizMaterialExportVo {
//@ExcelProperty这个注解表示导出的数据如果不需要导出就给@ExcelIgnore这个注解
//并且index下标是冲0开始的，而且这个字段必须跟数据库里面的字段一样
/**
* 物资编码
*/
@ExcelProperty(value = "物资编码", index = 0)
private String materialCode;
/**
* 物资名字
*/
@ExcelProperty(value = "物资名称", index = 1)
private String materialName;
/**
* 等级标码
*/
@ExcelProperty(value = "等级编码", index = 2)
private String materialLevelCode;
/**
* 序号
*/
@ExcelProperty(value = "序号", index = 3)
private String aIndex;
/**
* 类型(1.装备,2.车辆,3.物资,4.其他)
*/
@ExcelProperty(value = "物资类型", index = 4)
private String type;
@ExcelProperty(value = "部门架构", index = 5)
private String materialURL;
@ExcelIgnore
private String materialId;
public String getMaterialURL() {
return materialURL;
}
public void setMaterialURL(String materialURL) {
this.materialURL = materialURL;
}
public String getMaterialId() {
return materialId;
}
public void setMaterialId(String materialId) {
this.materialId = materialId;
}
public String getMaterialCode() {
return materialCode;
}
public void setMaterialCode(String materialCode) {
this.materialCode = materialCode;
}
public String getMaterialName() {
return materialName;
}
public void setMaterialName(String materialName) {
this.materialName = materialName;
}
public String getMaterialLevelCode() {
return materialLevelCode;
}
public void setMaterialLevelCode(String materialLevelCode) {
this.materialLevelCode = materialLevelCode;
}
public String getaIndex() {
return aIndex;
}
public void setaIndex(String aIndex) {
this.aIndex = aIndex;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
}
第三步就是写sql了需要写三个sql，在xml里面写两个sql一个是不勾选直接导出所有的sql，一个是勾选批量导出的sql
他的返回值必须是上面写的那个vo返回类
<!--查找所有要导出的数据-->
<select id="selectAllExportList" resultType="com.jiuqi.base.vo.BizMaterialExportVo">
select
MATERIAL_ID As materialId, MATERIAL_CODE As materialCode,
MATERIAL_NAME As materialName ,MATERIAL_LEVEL_CODE As materialLevelCode,
INDEX_ As aIndex, PARENT_ID As parentId, TYPE_ As type
from
biz_material
ORDER BY INDEX_ asc,CREATE_DATE asc
</select>
<!--根据id查找所有要导出的数据-->
//并且遍历所有需要导出的id
<select id="selectExportList" resultType="com.jiuqi.base.vo.BizMaterialExportVo">
select
MATERIAL_ID AS materialId,MATERIAL_CODE As materialCode,
MATERIAL_NAME As materialName ,MATERIAL_LEVEL_CODE As materialLevelCode,
INDEX_ As aIndex, PARENT_ID As parentId, TYPE_ As type
from
biz_material
<where>
MATERIAL_ID in
<foreach collection="materialIds" item="materialId" separator="," open="("
close=")">
#{materialId}
</foreach>
</where>
ORDER BY INDEX_ asc,CREATE_DATE asc
</select>
//查询所有
<select id="selectAll" resultType="com.jiuqi.base.dto.BizMaterialIdAndMaterialNameDto">
select
MATERIAL_ID AS materialId,MATERIAL_CODE As materialCode,
MATERIAL_NAME As materialName ,MATERIAL_LEVEL_CODE As materialLevelCode,
INDEX_ As aIndex, PARENT_ID As parentId, TYPE_ As type
from biz_material
where STATUS_ = 1
</select>第四步就是在mapper层写两个映射了
// 不需要条件，直接导出所有
List<BizMaterialExportVo> selectAllExportList();
//根据勾选的id导出所需要的数据
List<BizMaterialExportVo> selectExportList(@Param("materialIds") List<Integer>
materialIds);
//直接查询所有
List<BizMaterialIdAndMaterialNameDto> selectAllMaterialIdAndMaterialName();
第五步就是在service层写一个接口
/**
* 导出
* @param bizMaterialIdsDto
* @return response
*/
void export(BizMaterialIdsDto bizMaterialIdsDto, HttpServletResponse response) throws IOException;第六步就是serviceIpml层实现这个方法
/**
* 批量导出
*
* @param bizMaterialIdsDto
* @param response
*/
@Override
public void export(BizMaterialIdsDto bizMaterialIdsDto, HttpServletResponse response) throws IOException {
//第一步先new一个ExportVo对象
List<BizMaterialExportVo> data;
//如果ids为空的话就吊第一个sql直接导出所有，如果不为空的话就导出勾选的，
if(org.apache.commons.lang3.StringUtils.isBlank(bizMaterialIdsDto.getMaterialIds()){
data = bizMaterialMapper.selectAllExportList(bizMaterialIdsDto.getType());
}else {
List<Integer> materialIds =Splitter.on(",").splitToList(bizMaterialIdsDto.
getMaterialIds()).stream().map(Integer::parseInt).collect(Collectors.toList());
data = bizMaterialMapper.selectExportList(materialIds, bizMaterialIdsDto.getType());
}
//导出 --> 第二步 --> 查询物资相关数据,以供导出填充
List<BizMaterialIdAndMaterialNameDto> bizMaterialIdAndMaterialNameDtoList =
bizMaterialMapper.selectAllMaterialIdAndMaterialName();
Map<String, List<BizMaterialIdAndMaterialNameDto>> materialIdMap =
bizMaterialIdAndMaterialNameDtoList.stream().collect(Collectors.groupingBy(BizMaterialIdAndMaterialNameDto::getMaterialId));
//导出 --> 第三步 --> 将BizMaterialExportVo转为 material
List<Map<String,Object>> materialMaps = new ArrayList<>();
for (BizMaterialExportVo bizMaterialExportVo : data){
Map<String, Object> maps = JSON.parseObject(JSON.toJSONString(bizMaterialExportVo), Map.class);
materialMaps.add(maps);
}
//导出 --> 第四步 --> 填充fullPathName
for (Map<String,Object> map: materialMaps){
String materialId = getFullPathMaterialName(map.get("materialId").toString(), materialIdMap);
map.put("materialURL",materialId);
int materialLevel = materialId.split("/").length;
map.put("materialLevel",materialLevel);
}
//导出 --> 第五步 --> 导出相关数据
List<BizMaterialExportVo> bizMaterialExportVos = JSON.parseArray(JSON.toJSONString(materialMaps), BizMaterialExportVo.class);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//写到同一个sheet
String fileName = URLEncoder.encode("物资导出", "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcelFactory.write(response.getOutputStream(), BizMaterialExportVo.class).sheet("模板").doWrite(bizMaterialExportVos);
}
第七步就是controller层代码了
/**
* 导出
*
* @param
* @param response
*/
@PostMapping(value = "/export")
public void export(@RequestBody BizMaterialIdsDto dto, HttpServletResponse response) {
try {
if (null == dto) {
dto = new BizMaterialIdsDto();
}
bizMaterialService.export(dto, response);
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage());
}
}最后就能导出数据了
制作不易，如果觉得对您有用希望给个评论有用，谢谢！！！！
1405
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
