1.导出复杂excel 使用的是 阿里巴巴 Excel工具easyExcel
2.先看模板
3.目录位置
4.存放项目下的excel是这个样子的 使用 {.xxx}
5.看实体类
@Data
public class ExportExcelVo {
@ExcelProperty(value = "风格")
private String genre;
@ExcelProperty(value = "工艺")
private String craft;
@ExcelProperty(value = "图案")
private String graphic;
@ExcelProperty(value = "袖长")
private String sleeve;
@ExcelProperty(value = "厚薄")
private String thickness;
@ExcelProperty(value = "上市年份/季节")
private String year;
@ExcelProperty(value = "适合人群")
private String person;
@ExcelProperty(value = "版型")
private String model;
@ExcelProperty(value = "货源类别")
private String category;
@ExcelProperty(value = "面料名称")
private String liningName;
@ExcelProperty(value = "主面料成分")
private String element;
@ExcelProperty(value = "质检报告")
private String report;
@ExcelProperty(value = "货源")
private String source;
@ExcelProperty(value = "产品类别")
private String worksCategory;
@ExcelProperty(value = "款式")
private String style;
@ExcelProperty(value = "品牌")
private String brand;
@ExcelProperty(value = "适合季节")
private String season;
@ExcelProperty(value = "适用性别")
private String sex;
@ExcelProperty(value = "适用年龄")
private String age;
@ExcelProperty(value = "款式细节")
private String categoryDetail;
@ExcelProperty(value = "领型")
private String collarType;
@ExcelProperty(value = "是否连帽")
private String cap;
@ExcelProperty(value = "适用场景")
private String scene;
@ExcelProperty(value = "误差范围")
private String scope;
@ExcelProperty(value = "印花主题")
private String theme;
@ExcelProperty(value = "淘货类型")
private String taock;
@ExcelProperty(value = "是否跨境")
private String crossBorder;
@ExcelProperty(value = "领口形状")
private String shape;
@ExcelProperty(value = "主要下游平台")
private String terrace;
@ExcelProperty(value = "销售地区")
private String region;
@ExcelProperty(value = "有可授权的自有品牌")
private String accredit;
@ExcelProperty(value = "是否库存")
private String repertory;
@ExcelProperty(value = "最快出货时间")
private String fastestDate;
@ExcelProperty(value = "网上订阅")
private String take;
@ExcelProperty(value = "按产品规格报价")
private String worksOffer;
@ExcelProperty(value = "最小起批数量")
private Integer minNumber;
@ExcelProperty(value = "包换服务")
private String guarantee;
@ExcelProperty(value = "到货服务")
private String arrival;
@ExcelProperty(value = "退货服务")
private String salesReturn;
@ExcelProperty(value = "图片私密")
private String imgPrivate;
@ExcelProperty(value = "价格私密")
private String pricePrivate;
/**
* 男装男式衬衫-314
*/
@ExcelProperty(value = "库存类型")
private String inventoryType;
}
@Data
@ApiModel("导出刊登")
@NoArgsConstructor
@AllArgsConstructor
public class ExportExcelTemplateVo extends ExportExcelVo{
@ExcelProperty(value = "货号")
private String worksItemNo;
@ExcelProperty(value = "款式编号")
private String styleNo;
@ExcelProperty(value = "标题")
private String title;
@ExcelProperty(value = "主面料成分的含量")
private String ingredient;
@ExcelProperty(value = "产品图1")
private String worksImgOne;
@ExcelProperty(value = "产品图2")
private String worksImgTwo;
@ExcelProperty(value = "产品图3")
private String worksImgThree;
@ExcelProperty(value = "产品图4")
private String worksImgFour;
@ExcelProperty(value = "产品图5")
private String worksImgFive;
@ExcelProperty(value = "尺码")
private String size;
@ExcelProperty(value = "款式名称")
private String styleName;
@ExcelProperty(value = "sku图")
private String skuImage;
@ExcelProperty(value = "组合=套 不组合=件")
private String unit;
@ExcelProperty(value = "销售价格")
private BigDecimal price;
@ExcelProperty(value = "作品编号")
private String worksNo;
@ExcelProperty(value = "数量")
private Integer number;
@ExcelProperty("单品货号")
private String itemStyle;
@ExcelProperty(value = "发货时效")
private String theDeliveryTime;
@ExcelProperty(value = "发货天数")
private String day;
@ExcelProperty(value = "混批设置")
private String set;
@ExcelProperty(value = "图文详情")
private String imageDetails;
@ExcelProperty(value = "重量")
private BigDecimal weight;
@ExcelProperty(value = "款式细节")
private String categoryDetail;
@ExcelProperty(value = "模板名称")
private String templateName;
}
6.直接上代码 一般只需要注释的这一点就行了，我这个是比较复杂的所有写的就复杂，可能是技术还待提升…
```c
/**
* 1、创建日期文件夹 如果有就复制进去 如果没有就创建
*/
// //拼接好文件夹的位置
// String path = "D:/template";
//
// //存放压缩包的路径
// String zipPath = "D:/zip";
//
// //调用查询文件夹方法
// this.createdDirectory(path, zipPath);
/**
* 2、复制到临时文件夹
*/
// //获取resources路径
// ClassPathResource resource = new ClassPathResource("template");
// //获取当前项目路径下的模板
// String currentPath = resource.getAbsolutePath() + "/男装男式T恤-315.xlsx";
// //获取uuid已经拼接后缀名
// String uuid = UUID.randomUUID().toString();
// //加上临时文件的后缀名
// String localPath = path + "/" + uuid;
/**
* 3、赋值到表格中
*/
// //获取当前项目路径
// ExcelWriter workBook = EasyExcel.write(localPath + ".xlsx", ExportExcelTemplateVo.class).withTemplate(currentPath).build();
// //创建工作对象
// WriteSheet sheet = EasyExcel.writerSheet().build();
// //多组填充
// workBook.fill(this.groupByTemplateName(collect), sheet);
// //关闭流
// workBook.finish();
/**
* 4、压缩成zip
*/
// //日期格式化
// SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
// //压缩包命名
// String zipName = uuid + "-" + format.format(new Date());
//
// // 压缩 参数1：被压缩的路径 参数2：压缩后存放的路径 参数3：压缩后的名称
// File2ZipUtil.createZip(path, zipPath + "/", zipName);
/**
* 5.上传到oss
*/
// //获取本地需要上传的路径
// String originalPath = zipPath + "/" + zipName + ".zip";
// //文件名
// String fileName = prefix + format.format(new Date()) + "-" + UserContext.getCurrentUser().getNickName() + ".zip";
// //获取文件
// InputStream inputStream = new FileInputStream(originalPath);
// //获取文件大小
// int fileSize = inputStream.available();
// //上传oss
// String fileUrl = fileManagerPlugin.inputStreamUpload(inputStream, uuid);//上传OSS,返回素材图片OSS路径
/**
* 6.删除临时文件夹
*/
// //创建删除路径集合
// List<String> deletePath=new ArrayList<>();
// deletePath.add(originalPath);
// deletePath.add(localPath+".xlsx");
// //删除文件
// deleteFile(deletePath);```
7.我的D盘位置
8.上传到oss是公司大佬写的我只管用
/**
* 导出excel
*
* @param dto
* @return
*/
@Override
public ResultMessage<Object> privateImportExcel(PublishWorksRemitDeleteDto dto) throws IOException {
//获取用户信息
AuthUser currentUser = UserContext.getCurrentUser();
if (null == currentUser) {
return ResultUtil.error(ResultCode.TOKEN_LOSE_EFFICACY);
} else if (dto.getIds() == null) {
return ResultUtil.error(ResultCode.PARAMS_ERROR);
} else if (dto.getIds().size() > 20) {
return ResultUtil.error(500, "长度不能大于20");
}
//查询1688刊登信息
List<PublishWorksRemit> publishWorksRemits = publishWorksRemitMapper.selectBatchIds(dto.getIds());
if (publishWorksRemits.size() <= 0) {
return ResultUtil.error(500, "没有该信息");
}
//赋值集合
List<ExportExcelTemplateVo> list = this.queryRemitDetail(publishWorksRemits);
/**
* 根据作品编码分组
*/
Map<String, List<ExportExcelTemplateVo>> groupBy = list.stream().collect(Collectors.groupingBy(ExportExcelTemplateVo::getWorksItemNo));
//根据模板名称分组
Map<String, List<ExportExcelTemplateVo>> collect = privateList(groupBy).stream().collect(Collectors.groupingBy(ExportExcelTemplateVo::getTemplateName));
/**
* 1、创建日期文件夹 如果有就复制进去 如果没有就创建
*/
// //拼接好文件夹的位置
// String path = "D:/template";
//
// //存放压缩包的路径
// String zipPath = "D:/zip";
//
// //调用查询文件夹方法
// this.createdDirectory(path, zipPath);
/**
* 2、复制到临时文件夹
*/
// //获取resources路径
// ClassPathResource resource = new ClassPathResource("template");
// //获取当前项目路径下的模板
// String currentPath = resource.getAbsolutePath() + "/男装男式T恤-315.xlsx";
// //获取uuid已经拼接后缀名
// String uuid = UUID.randomUUID().toString();
// //加上临时文件的后缀名
// String localPath = path + "/" + uuid;
/**
* 3、赋值到表格中
*/
// //获取当前项目路径
// ExcelWriter workBook = EasyExcel.write(localPath + ".xlsx", ExportExcelTemplateVo.class).withTemplate(currentPath).build();
// //创建工作对象
// WriteSheet sheet = EasyExcel.writerSheet().build();
// //多组填充
// workBook.fill(this.groupByTemplateName(collect), sheet);
// //关闭流
// workBook.finish();
/**
* 4、压缩成zip
*/
// //日期格式化
// SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
// //压缩包命名
// String zipName = uuid + "-" + format.format(new Date());
//
// // 压缩 参数1：被压缩的路径 参数2：压缩后存放的路径 参数3：压缩后的名称
// File2ZipUtil.createZip(path, zipPath + "/", zipName);
/**
* 5.上传到oss
*/
// //获取本地需要上传的路径
// String originalPath = zipPath + "/" + zipName + ".zip";
// //文件名
// String fileName = prefix + format.format(new Date()) + "-" + UserContext.getCurrentUser().getNickName() + ".zip";
// //获取文件
// InputStream inputStream = new FileInputStream(originalPath);
// //获取文件大小
// int fileSize = inputStream.available();
// //上传oss
// String fileUrl = fileManagerPlugin.inputStreamUpload(inputStream, uuid);//上传OSS,返回素材图片OSS路径
/**
* 6.删除临时文件夹
*/
// //创建删除路径集合
// List<String> deletePath=new ArrayList<>();
// deletePath.add(originalPath);
// deletePath.add(localPath+".xlsx");
// //删除文件
// deleteFile(deletePath);
/**
* 7.保存到数据库
* 1.修改刊登状态为已刊登
* 2.存储到文件表里面去
*/
// //创建文件对象
// PublishWorksRemitFile file=new PublishWorksRemitFile();
// file.setFileName(fileName);//赋值文件名称
// file.setFileUrl(fileUrl);//赋值文件路径
// file.setFileSize(Double.valueOf(fileSize));//赋值文件大小
// file.setCreateTime(new Date());//赋值创建时间
// file.setCreateBy(currentUser.getId());//赋值用户id
// file.setDeleteFlag(0);//赋值为未删除
//文件id
String fileId = this.groupByTemplateName(collect);
for (String ids : dto.getIds()) {
//创建产品汇出对象
PublishWorksRemit publishWorksRemit = new PublishWorksRemit();
publishWorksRemit.setId(ids);//赋值id
publishWorksRemit.setIsExport(1);//是否导出 是
publishWorksRemit.setUpdateBy(currentUser.getId());
publishWorksRemit.setUpdateTime(new Date());//赋值修改时间
publishWorksRemitMapper.updateById(publishWorksRemit);//修改状态为已导出
//创建文件对象
PublishWorksFile publishWorksFile = new PublishWorksFile();
publishWorksFile.setPublishWorksRemitFileId(fileId);//赋值文件id
publishWorksFile.setPublishWorksRemitId(ids);//赋值产品汇出id
publishWorksFileMapper.insert(publishWorksFile);//添加产品汇出和文件中间件表
}
return ResultUtil.success(ResultCode.PUBLISH_SUCCESS);
}
/**
* 根据模板名称分组并且创
*
* @param collect
* @return
*/
public String groupByTemplateName(Map<String, List<ExportExcelTemplateVo>> collect) throws IOException {
/**
* 1、创建日期文件夹 如果有就复制进去 如果没有就创建
*/
//拼接好文件夹的位置
String path = "D:/template";
//存放压缩包的路径
String zipPath = "D:/zip";
//调用查询文件夹方法
this.createdDirectory(path, zipPath);
//创建集合
List<ExportExcelTemplateVo> list = new ArrayList<>();
//创建删除路径集合
List<String> deletePath = new ArrayList<>();
//创建路径对象
List<DeriveVo> pathList = new ArrayList<>();
//遍历map
for (Map.Entry<String, List<ExportExcelTemplateVo>> stuMap : collect.entrySet()) {
//赋值成集合
List<ExportExcelTemplateVo> studentList = stuMap.getValue();
/**
* 2、复制到临时文件夹
*/
//获取resources路径
ClassPathResource resource = new ClassPathResource("template");
//获取当前项目路径下的模板
String currentPath = resource.getAbsolutePath() + "/" + stuMap.getKey() + ".xlsx";
//获取uuid已经拼接后缀名
String uuid = UUID.randomUUID().toString();
//加上临时文件的后缀名
String localPath = path + "/" + uuid;
boolean flag = false;
//获取文件路径
File dir = new File(currentPath);
if (dir.exists()) {// 判断目录是否存在
flag = true;//有这个模板就添加数据
}
if (flag == true) {
//判断集合为0时
if (pathList.size() == 0) {
//创建路径对象
DeriveVo deriveVo = new DeriveVo();
deriveVo.setCurrentPath(currentPath);//赋值模板路径
deriveVo.setLocalPath(localPath + ".xlsx");//赋值文件路径
pathList.add(deriveVo);//获取路径
} else {
//没有相同的菜再次添加
for (DeriveVo paths : pathList) {
if (!paths.getCurrentPath().equals(currentPath)) {
//创建路径对象
DeriveVo deriveVo = new DeriveVo();
deriveVo.setCurrentPath(currentPath);//赋值模板路径
deriveVo.setLocalPath(localPath + ".xlsx");//赋值文件路径
pathList.add(deriveVo);//获取路径
break;
}
}
}
//100条数据以前
List<ExportExcelTemplateVo> templateVoList = new ArrayList<>();
//100条数据以后
List<ExportExcelTemplateVo> student = new ArrayList<>();
for (int i = 0; i < studentList.size(); i++) {
if (i < 100) {
templateVoList.add(studentList.get(i));
} else {
student.add(studentList.get(i));
}
}
//判断数据小于100
if (studentList.size() <= 100) {
//遍历路径集合
for (DeriveVo paths : pathList) {
//判断路径和集合中的路径相同时，拿集合中路径
if (paths.getCurrentPath().equals(currentPath)) {
//获取当前项目路径
ExcelWriter workBook = EasyExcel.write(paths.getLocalPath(), ExportExcelTemplateVo.class).withTemplate(currentPath).build();
//创建工作对象
WriteSheet sheet = EasyExcel.writerSheet().build();
//多组填充
workBook.fill(templateVoList, sheet);
//关闭流
workBook.finish();
//需要删除的文件
deletePath.add(paths.getLocalPath());
}
}
} else {
String newPath=path + "/" + UUID.randomUUID().toString() + ".xlsx";
//获取当前项目路径
ExcelWriter workBook = EasyExcel.write(path, ExportExcelTemplateVo.class).withTemplate(currentPath).build();
//创建工作对象
WriteSheet sheet = EasyExcel.writerSheet().build();
//多组填充
workBook.fill(student, sheet);
//关闭流
workBook.finish();
//需要删除的文件
deletePath.add(newPath);
}
}
}
//获取uuid
String uuid = UUID.randomUUID().toString();
/**
* 4、压缩成zip
*/
//日期格式化
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//压缩包命名
String zipName = uuid + "-" + format.format(new Date());
// 压缩 参数1：被压缩的路径 参数2：压缩后存放的路径 参数3：压缩后的名称
File2ZipUtil.createZip(path, zipPath + "/", zipName);
/**
* 5.上传到oss
*/
//获取本地需要上传的路径
String originalPath = zipPath + "/" + zipName + ".zip";
//文件名
String fileName = prefix + format.format(new Date()) + "-" + UserContext.getCurrentUser().getNickName() + ".zip";
//获取文件
InputStream inputStream = new FileInputStream(originalPath);
//获取文件大小
int fileSize = inputStream.available();
//上传oss
String fileUrl = fileManagerPlugin.inputStreamUpload(inputStream, uuid);//上传OSS,返回素材图片OSS路径
/**
* 6.删除临时文件夹
*/
deletePath.add(originalPath);
//删除文件
deleteFile(deletePath);
/**
* 7.保存到数据库
* 1.修改刊登状态为已刊登
* 2.存储到文件表里面去
*/
//创建文件对象
PublishWorksRemitFile file = new PublishWorksRemitFile();
file.setFileName(fileName);//赋值文件名称
file.setFileUrl(fileUrl);//赋值文件路径
file.setFileSize(Double.valueOf(fileSize));//赋值文件大小
file.setCreateTime(new Date());//赋值创建时间
file.setCreateBy(UserContext.getCurrentUser().getId());//赋值用户id
file.setDeleteFlag(0);//赋值为未删除
publishWorksRemitFileMapper.insert(file);//添加文件
return file.getId();
}
/**
* 查询组合
*
* @param publishWorksRemits
* @return
*/
public List<ExportExcelTemplateVo> queryRemitDetail(List<PublishWorksRemit> publishWorksRemits) {
//创建集合
List<ExportExcelTemplateVo> list = new ArrayList<>();
//遍历1688
for (PublishWorksRemit items : publishWorksRemits) {
//查询明细
List<PublishWorksRemitDetail> publishWorksRemitDetails = publishWorksRemitDetailMapper
.selectList(new LambdaQueryWrapper<PublishWorksRemitDetail>().eq(PublishWorksRemitDetail::getPublishWorksRemitId, items.getId())
.eq(PublishWorksRemitDetail::getDeleteFlag, false));
//遍历明细
for (PublishWorksRemitDetail item : publishWorksRemitDetails) {
//获取模板名称
PublishCategory publishCategory = publishCategoryMapper.selectOne(new LambdaQueryWrapper<PublishCategory>().eq(PublishCategory::getStyleNo, item.getStyleNo()));
//判断价格不为空和不能小于等于0
if (item.getMarketPrice() == null || item.getMarketPrice().compareTo(new BigDecimal(0)) < 1 || publishCategory == null || publishCategory.getExcelTemplateName() == null) {
log.info("--->>>价格为空");
} else {
//获取json数据
ImageJsonVo<PublishWorksRemitImage> jsonVo = JSON.parseObject(item.getImgJson(), new TypeReference<ImageJsonVo<PublishWorksRemitImage>>() {
});
// //获取尺码
// PublishWorksRemitSize publishWorksRemitSize = JSON.parseObject(item.getSizeJson(), new TypeReference<PublishWorksRemitSize>() {
// });
//查询尺码
List<PublishWorksRemitSize> publishWorksRemitSizes = publishWorksRemitSizeMapper.selectList(new LambdaQueryWrapper<PublishWorksRemitSize>().eq(PublishWorksRemitSize::getPublishWorksRemitId, item.getId()));
//查询款式
QrStyle qrStyle = qrStyleMapper.selectOne(new LambdaQueryWrapper<QrStyle>().eq(QrStyle::getId, item.getStyleId()).eq(QrStyle::getDeleteFlag, false));
if (qrStyle != null) {
//判断是2D
if (qrStyle.getStyleType() == 1) {
//查询颜色
List<PublishWorksRemitColor> publishWorksRemitColors = publishWorksRemitColorMapper
.selectList(new LambdaQueryWrapper<PublishWorksRemitColor>().eq(PublishWorksRemitColor::getPublishWorksRemitId, item.getId()));
for (PublishWorksRemitSize size : publishWorksRemitSizes) {
//遍历颜色
for (PublishWorksRemitColor color : publishWorksRemitColors) {
//创建excel对象
ExportExcelTemplateVo templateVo = new ExportExcelTemplateVo();
BeanUtil.copyProperties(this.privateObject(item, jsonVo, items), templateVo);//复制
templateVo.setStyleName(color.getColor());//赋值颜色
templateVo.setSkuImage(color.getUrl());//赋值图片
templateVo.setSize(size.getSize());//赋值尺码
//获取颜色编号
QrColorComparison qrColorComparison = qrColorComparisonMapper.selectOne(new LambdaQueryWrapper<QrColorComparison>().eq(QrColorComparison::getChinese, color.getColor()));
//赋值货号
templateVo.setItemStyle(item.getErpMaterialNo() + "-" + item.getStyleNo() + "-" + size.getSize() + "-" + qrColorComparison.getCode());//赋值单品货号
//拼接图片
String str = Joint(jsonVo);
str += "<img alt=\"颜色\" src='" + color.getUrl() + "' width=\"790\"/><br /><br />\"</p>\""; //赋值颜色图
//赋值图文详情
templateVo.setImageDetails(str);
//赋值集合
list.add(templateVo);
}
}
} else {
//累加=循环次数
int count = 0;
for (PublishWorksRemitSize size : publishWorksRemitSizes) {
//累加次数
count++;
Character strs = (char) (96 + count);//获取字母
String styleName = strs.toString().toUpperCase(); //转换为大写
//创建excel对象
ExportExcelTemplateVo templateVo = new ExportExcelTemplateVo();
BeanUtil.copyProperties(this.privateObject(item, jsonVo, items), templateVo);//复制
templateVo.setStyleName(styleName + "款");//赋值颜色
templateVo.setSkuImage(jsonVo.getSkuImage().getUrl());//赋值sku图片
templateVo.setSize(size.getSize());
//赋值货号
templateVo.setItemStyle(item.getErpMaterialNo() + "-" + item.getStyleNo() + "-" + size.getSize());//赋值单品货号
//拼接图片
String str = Joint(jsonVo) + "</p>";
//赋值图文详情
templateVo.setImageDetails(str);
//赋值集合
list.add(templateVo);
}
}
}
}
}
}
return list;
}
/**
* 拼接图片
*
* @return
*/
public String Joint(ImageJsonVo<PublishWorksRemitImage> jsonVo) {
//赋值通用图
String str = "";
if(jsonVo.getCommonImage()!=null){
str+="<div></div><p><img alt=\"通用图\" src='" + jsonVo.getCommonImage().getUrl() + "' width=\"790\" />\n<br /><br />";
}
if (jsonVo.getSizeImage() != null) {
str += "<img alt=\"尺码图\" src='" + jsonVo.getSizeImage().getUrl() + "' width=\"790\"/><br /><br />";//赋值尺码图
}
//遍历主图
for (PublishWorksRemitImage worksRemitImage : jsonVo.getWorksImgList()) {
str += "<img alt=\"主图\" src='" + worksRemitImage.getUrl() + "' width=\"790\"/><br /><br />";//赋值组主图
}
if(jsonVo.getSkuImage()!=null) {
str += "<img alt=\"sku图\" src='" + jsonVo.getSkuImage().getUrl() + "' width=\"790\"/><br /><br />"; //赋值sku图
}
return str;
}
/**
* 封装对象
*
* @param item
* @param jsonVo
* @param items
* @return
*/
public ExportExcelVo privateObject(PublishWorksRemitDetail item, ImageJsonVo<PublishWorksRemitImage> jsonVo, PublishWorksRemit items) {
//创建excel模板对象
ExportExcelTemplateVo templateVo = new ExportExcelTemplateVo();
//查询模板名称
PublishCategory publishCategory = publishCategoryMapper.selectOne(new LambdaQueryWrapper<PublishCategory>().eq(PublishCategory::getStyleNo, item.getStyleNo()));
templateVo.setTemplateName(publishCategory.getExcelTemplateName());//赋值模板名称
templateVo.setTitle(item.getTitle());//赋值标题
templateVo.setWorksItemNo(item.getWorksNo());//赋值作品编码
templateVo.setSkuImage(jsonVo.getSkuImage().getUrl());//赋值sku图
templateVo.setUnit(items.getIsGroup() == 0 ? "件" : "套");//如何是组合就是套 不是的话就是件
templateVo.setPrice(item.getMarketPrice());//赋值销售价格
templateVo.setNumber(9999);//销售数量
templateVo.setWeight(items.getWeight());//赋值重量
templateVo.setTheDeliveryTime("72");//赋值小时
templateVo.setDay("3");//赋值天数
templateVo.setStyleNo(item.getStyleNo());//赋值款式编号
//图片url集合
List<String> urlList = new ArrayList<>();
//获取主图
for (PublishWorksRemitImage image : jsonVo.getWorksImgList()) {
//赋值url
urlList.add(image.getUrl());
}
//复制sku和尺码图
if (urlList.size() < 5) {
urlList.add(jsonVo.getSkuImage() != null ? jsonVo.getSkuImage().getUrl() : null);
urlList.add(jsonVo.getSizeImage() != null ? jsonVo.getSizeImage().getUrl() : null);
}
//遍历图片
for (int j = 0; j < urlList.size(); j++) {
if (j == 0) {
templateVo.setWorksImgOne(urlList.get(j));
} else if (j == 1) {
templateVo.setWorksImgTwo(urlList.get(j));
} else if (j == 2) {
templateVo.setWorksImgThree(urlList.get(j));
} else if (j == 3) {
templateVo.setWorksImgFour(urlList.get(j));
} else if (j == 4) {
templateVo.setWorksImgFive(urlList.get(j));
}
}
return templateVo;
}
/**
* 封装集合
*
* @param groupBy
* @return
*/
public List<ExportExcelTemplateVo> privateList(Map<String, List<ExportExcelTemplateVo>> groupBy) {
//创建集合
List<ExportExcelTemplateVo> list1 = new ArrayList<>();
//遍历map
for (Map.Entry<String, List<ExportExcelTemplateVo>> stuMap : groupBy.entrySet()) {
//获取模板名称
PublishCategory publishCategory = publishCategoryMapper.selectOne(new LambdaQueryWrapper<PublishCategory>().eq(PublishCategory::getStyleNo, stuMap.getValue().get(0).getStyleNo()));
//判断不为空
if (publishCategory != null) {
//赋值成集合
List<ExportExcelTemplateVo> studentList = stuMap.getValue();
//遍历集合
for (int i = 0; i < studentList.size(); i++) {
//创建对象
ExportExcelTemplateVo templateVo = new ExportExcelTemplateVo();
BeanUtil.copyProperties(studentList.get(i), templateVo);//复制
if (i == 0) {
if (publishCategory.getExcelTemplateName().equals("男装男式T恤-315")) {
templateVo.setGenre("休闲");
templateVo.setCraft("印花/印染");
templateVo.setGraphic("印花");
templateVo.setSleeve("短袖");
templateVo.setThickness("普通");
templateVo.setYear("2021年春季");
templateVo.setPerson("青少年");
templateVo.setModel("宽松型");
templateVo.setCategory("现货");
templateVo.setLiningName("棉类混纺");
templateVo.setElement("聚酯纤维（涤纶）");
templateVo.setReport("否");
templateVo.setIngredient("100");//赋值主要成分
templateVo.setSource("否");
templateVo.setWorksCategory("T恤");
templateVo.setStyle("套头");
templateVo.setBrand("其他");
templateVo.setSeason("夏季,冬季");
templateVo.setSex("男");
templateVo.setAge("成人");
templateVo.setCategoryDetail("印花");
templateVo.setCollarType("无领");
templateVo.setCap("不连帽");
templateVo.setScene("休闲");
templateVo.setScope("1-3cm");
templateVo.setTheme("图案");
templateVo.setTaock("青春流行（18-24岁）");
templateVo.setCrossBorder("是");
templateVo.setShape("圆领");
templateVo.setTerrace("ebay,亚马逊,wish,速卖通,独立站,LAZADA");
templateVo.setRegion("非洲,欧洲,南美,东南亚,北美,东北亚,中东");
templateVo.setAccredit("否");
templateVo.setRepertory("否");
templateVo.setFastestDate("1-3天");
templateVo.setTake("支持网上订购");
templateVo.setWorksOffer("按产品规格报价");
templateVo.setMinNumber(2);
templateVo.setGuarantee("暂不支持");
templateVo.setArrival("暂不支持");
templateVo.setSalesReturn("暂不支持");
templateVo.setImgPrivate("否");
templateVo.setPricePrivate("否");
}else if(publishCategory.getExcelTemplateName().equals("男装男式衬衫-314")){
templateVo.setGenre("休闲");
templateVo.setCraft("数码印花");
templateVo.setGraphic("图案");
templateVo.setSleeve("长袖");
templateVo.setThickness("普通");
templateVo.setYear("2020年夏季");
templateVo.setPerson("青少年");
templateVo.setModel("宽松型");
templateVo.setCategory("现货");
templateVo.setLiningName("聚酯纤维");
templateVo.setElement("聚酯纤维（涤纶）");
templateVo.setReport("否");
templateVo.setIngredient("90");//赋值主要成分
templateVo.setSource("否");
templateVo.setWorksCategory("衬衫");
templateVo.setStyle("开衫");
templateVo.setBrand("其他");
templateVo.setSeason("夏季");
templateVo.setSex("男");
templateVo.setAge("成人");
templateVo.setCategoryDetail("印花");
templateVo.setCollarType("无领");
templateVo.setCap("不连帽");
templateVo.setScene("休闲");
templateVo.setScope("1-3cm");
templateVo.setTheme("图案");
templateVo.setTaock("青春流行（18-24岁）");
templateVo.setCrossBorder("是");
templateVo.setInventoryType("整单");
templateVo.setShape("立领");
templateVo.setTerrace("ebay,亚马逊,wish,速卖通,独立站,LAZADA");
templateVo.setRegion("非洲,欧洲,南美,东南亚,北美,东北亚,中东");
templateVo.setAccredit("否");
templateVo.setRepertory("否");
templateVo.setFastestDate("1-3天");
templateVo.setTake("支持网上订购");
templateVo.setWorksOffer("按产品规格报价");
templateVo.setMinNumber(2);
templateVo.setGuarantee("暂不支持");
templateVo.setArrival("暂不支持");
templateVo.setSalesReturn("暂不支持");
templateVo.setImgPrivate("否");
templateVo.setPricePrivate("否");
}
} else {
templateVo.setWorksImgOne("");
templateVo.setWorksImgTwo("");
templateVo.setWorksImgThree("");
templateVo.setWorksImgFour("");
templateVo.setWorksImgFive("");
}
list1.add(templateVo);
}
}
}
return list1;
}
/**
* 删除文件方法
*
* @return
*/
public void deleteFile(List<String> path) {
path.stream().forEach(item -> {
File file = new File(item);
if (file.exists()) {
file.delete();
}
});
}
/**
* 创建文件夹
*
* @param filePath
*/
private void createdDirectory(String filePath, String zipPath) {
File dir = new File(filePath);
if (!dir.exists()) {// 判断目录是否存在
dir.mkdir();
}
File zip = new File(zipPath);
if (!zip.exists()) {// 判断目录是否存在
zip.mkdir();
}
}
9.oss代码，虽然看不懂
package com.qrkjdiy.publish.service.impl.plugin;
import cn.hutool.core.util.StrUtil;
import com.aliyun.oss.ClientException;
import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import com.aliyun.oss.OSSException;
import com.aliyun.oss.model.DeleteObjectsRequest;
import com.aliyun.oss.model.ObjectMetadata;
import com.google.gson.Gson;
import com.qrkjdiy.common.enums.ResultCode;
import com.qrkjdiy.common.exception.ServiceException;
import com.qrkjdiy.publish.service.plugin.QrFileManagerPlugin;
import com.qrkjdiy.base.dto.system.OssSetting;
import com.qrkjdiy.base.entity.system.Setting;
import com.qrkjdiy.common.enums.SettingEnum;
import com.qrkjdiy.publish.service.system.SettingService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.io.File;
import java.io.InputStream;
import java.util.List;
/**
* 阿里oss 文件操作
*
* @author qr.lihailin
*/
@Primary
@Component
@Slf4j
public class QrAliFileManagerPlugin implements QrFileManagerPlugin {
@Resource
private SettingService settingService;
/**
* 下一个初始化配置参数的时间
* 这里为了防止多次调用redis，减少与redis的交互时间
*/
private static Long nextInitSetting;
/**
* 暂时设定3分账请求一次设置
*/
private static final Long INTERVAL = 60 * 3 * 1000L;
/**
* 静态设置，最快三分钟更新一次
*/
private static OssSetting ossSetting;
/**
* 获取oss restHighLevelClient
*
* @return
*/
private OSS getOssClient() {
OssSetting ossSetting = getSetting();
return new OSSClientBuilder().build(
ossSetting.getEndPoint(),
ossSetting.getAccessKeyId(),
ossSetting.getAccessKeySecret());
}
/**
* 获取配置
*
* @return
*/
private OssSetting getSetting() {
//如果没有配置，或者没有下次刷新时间，或者下次刷新时间小于当前时间，则从redis 更新一次
// if (ossSetting == null || nextInitSetting == null || nextInitSetting < System.currentTimeMillis()) {
Setting setting = settingService.get(SettingEnum.OSS_SETTING.name());
if (setting == null || StrUtil.isBlank(setting.getSettingValue())) {
throw new ServiceException(ResultCode.OSS_NOT_EXIST);
}
nextInitSetting = System.currentTimeMillis() + INTERVAL;
ossSetting = new Gson().fromJson(setting.getSettingValue(), OssSetting.class);
return ossSetting;
// }
// return ossSetting;
}
/**
* 获取配置前缀
*
* @return
*/
private String getUrlPrefix() {
OssSetting ossSetting = getSetting();
return "https://" + ossSetting.getBucketName() + "." + ossSetting.getEndPoint() + "/";
}
@Override
public String pathUpload(String filePath, String key) {
OSS ossClient = getOssClient();
try {
ossClient.putObject(ossSetting.getBucketName(), key, new File(filePath));
} catch (OSSException oe) {
log.error("Caught an OSSException, which means your request made it to OSS, "
+ "but was rejected with an error response for some reason.");
log.error("Error Message: " + oe.getErrorMessage());
log.error("Error Code: " + oe.getErrorCode());
log.error("Request ID: " + oe.getRequestId());
log.error("Host ID: " + oe.getHostId());
throw new ServiceException(ResultCode.OSS_EXCEPTION_ERROR);
} catch (ClientException ce) {
log.error("Caught an ClientException, which means the restHighLevelClient encountered "
+ "a serious internal problem while trying to communicate with OSS, "
+ "such as not being able to access the network.");
log.error("Error Message: " + ce.getMessage());
throw new ServiceException(ResultCode.OSS_EXCEPTION_ERROR);
} finally {
/*
* Do not forget to shut down the restHighLevelClient finally to release all allocated resources.
*/
ossClient.shutdown();
}
ossClient.shutdown();
return getUrlPrefix() + key;
}
@Override
public String inputStreamUpload(InputStream inputStream, String key) {
OSS ossClient = getOssClient();
try {
ObjectMetadata meta = new ObjectMetadata();
meta.setContentType("image/jpg");
ossClient.putObject(getSetting().getBucketName(), key, inputStream, meta);
} catch (OSSException oe) {
log.error("Caught an OSSException, which means your request made it to OSS, "
+ "but was rejected with an error response for some reason.");
log.error("Error Message: " + oe.getErrorMessage());
log.error("Error Code: " + oe.getErrorCode());
log.error("Request ID: " + oe.getRequestId());
log.error("Host ID: " + oe.getHostId());
throw new ServiceException(ResultCode.OSS_EXCEPTION_ERROR);
} catch (ClientException ce) {
log.error("Caught an ClientException, which means the restHighLevelClient encountered "
+ "a serious internal problem while trying to communicate with OSS, "
+ "such as not being able to access the network.");
log.error("Error Message: " + ce.getMessage());
throw new ServiceException(ResultCode.OSS_EXCEPTION_ERROR);
} finally {
/*
* Do not forget to shut down the restHighLevelClient finally to release all allocated resources.
*/
ossClient.shutdown();
}
ossClient.shutdown();
return getUrlPrefix() + key;
}
@Override
public void deleteFile(List<String> key) {
OSS ossClient = getOssClient();
try {
ossClient.deleteObjects(
new DeleteObjectsRequest(getSetting().getBucketName()).withKeys(key));
} catch (OSSException oe) {
log.error("Caught an OSSException, which means your request made it to OSS, "
+ "but was rejected with an error response for some reason.");
log.error("Error Message: " + oe.getErrorMessage());
log.error("Error Code: " + oe.getErrorCode());
log.error("Request ID: " + oe.getRequestId());
log.error("Host ID: " + oe.getHostId());
throw new ServiceException(ResultCode.OSS_DELETE_ERROR);
} catch (ClientException ce) {
log.error("Caught an ClientException, which means the restHighLevelClient encountered "
+ "a serious internal problem while trying to communicate with OSS, "
+ "such as not being able to access the network.");
log.error("Error Message: " + ce.getMessage());
throw new ServiceException(ResultCode.OSS_DELETE_ERROR);
} finally {
/*
* Do not forget to shut down the restHighLevelClient finally to release all allocated resources.
*/
ossClient.shutdown();
}
}
@Override
public String getUrl(String url, Integer width, Integer height) {
//缩略图全路径
//返回缩略图全路径
return url + "?x-oss-process=style/" + width + "X" + height;
}
}
package com.qrkjdiy.publish.service.impl.qrfile;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.qrkjdiy.common.enums.ResultCode;
import com.qrkjdiy.common.exception.ServiceException;
import com.qrkjdiy.common.mybatis.util.PageUtil;
import com.qrkjdiy.common.security.AuthUser;
import com.qrkjdiy.common.utils.StringUtils;
import com.qrkjdiy.common.vo.PageVO;
import com.qrkjdiy.common.vo.SearchVO;
import com.qrkjdiy.base.entity.qrfile.QrFile;
import com.qrkjdiy.base.dto.qrfile.FileOwnerDTO;
import com.qrkjdiy.base.dto.qrfile.QrFileDTO;
import com.qrkjdiy.base.mapper.qrfile.QrFileMapper;
import com.qrkjdiy.publish.service.plugin.QrFileManagerPlugin;
import com.qrkjdiy.publish.service.qrfile.QrFileService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* 文件管理业务层实现
*
* @author qr.lihailin
* @since 2021/10/26 17:50
*/
@Primary
@Service
@Transactional(rollbackFor = Exception.class)
public class QrFileServiceImpl extends ServiceImpl<QrFileMapper, QrFile> implements QrFileService {
@Autowired
private QrFileManagerPlugin qrFileManagerPlugin;
@Override
public void batchDelete(List<String> ids) {
LambdaQueryWrapper<QrFile> queryWrapper = new LambdaQueryWrapper();
queryWrapper.in(QrFile::getId, ids);
List<QrFile> files = this.list(queryWrapper);
List<String> keys = new ArrayList<>();
files.forEach(item -> keys.add(item.getFileKey()));
qrFileManagerPlugin.deleteFile(keys);
this.remove(queryWrapper);
}
@Override
public void batchDelete(List<String> ids, AuthUser authUser) {
LambdaQueryWrapper<QrFile> queryWrapper = new LambdaQueryWrapper();
queryWrapper.in(QrFile::getId, ids);
queryWrapper.eq(QrFile::getUserEnums, authUser.getRole().name());
//操作图片属性判定
switch (authUser.getRole()) {
case MEMBER:
queryWrapper.eq(QrFile::getOwnerId, authUser.getId());
break;
case STORE:
queryWrapper.eq(QrFile::getOwnerId, authUser.getStoreId());
break;
case MANAGER:
break;
default:
throw new ServiceException(ResultCode.USER_AUTHORITY_ERROR);
}
List<QrFile> files = this.list(queryWrapper);
List<String> keys = new ArrayList<>();
files.forEach(item -> keys.add(item.getFileKey()));
qrFileManagerPlugin.deleteFile(keys);
this.remove(queryWrapper);
}
@Override
public IPage<QrFile> customerPage(QrFile file, SearchVO searchVO, PageVO pageVo) {
LambdaQueryWrapper<QrFile> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.like(StringUtils.isNotEmpty(file.getName()), QrFile::getName, file.getName())
.like(StringUtils.isNotEmpty(file.getFileKey()), QrFile::getFileKey, file.getFileKey())
.like(StringUtils.isNotEmpty(file.getFileType()), QrFile::getFileType, file.getFileType())
.between(StringUtils.isNotEmpty(searchVO.getStartDate()) && StringUtils.isNotEmpty(searchVO.getEndDate()),
QrFile::getCreateTime, searchVO.getStartDate(), searchVO.getEndDate());
IPage<QrFile> page = this.page(PageUtil.initPage(pageVo), queryWrapper);
return page;
}
@Override
public IPage<QrFile> customerPageOwner(FileOwnerDTO ownerDTO, QrFile file, SearchVO searchVO, PageVO pageVo) {
LambdaQueryWrapper<QrFile> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(StringUtils.isNotEmpty(ownerDTO.getOwnerId()), QrFile::getOwnerId, ownerDTO.getOwnerId())
.eq(QrFile::getUserEnums, ownerDTO.getUserEnums())
.like(StringUtils.isNotEmpty(file.getName()), QrFile::getName, file.getName())
.like(StringUtils.isNotEmpty(file.getFileKey()), QrFile::getFileKey, file.getFileKey())
.like(StringUtils.isNotEmpty(file.getFileType()), QrFile::getFileType, file.getFileType())
.between(StringUtils.isNotEmpty(searchVO.getStartDate()) && StringUtils.isNotEmpty(searchVO.getEndDate()),
QrFile::getCreateTime, searchVO.getStartDate(), searchVO.getEndDate());
IPage<QrFile> page = this.page(PageUtil.initPage(pageVo), queryWrapper);
return page;
}
@Override
public int saveFile(QrFile qrFile) {
qrFile.setCreateTime(new Date());
qrFile.setUpdateTime(new Date());
int result = this.baseMapper.insert(qrFile);
return result;
}
@Override
public String add(QrFileDTO dto) {
try {
MultipartFile file = dto.getFile();
QrFile newFile = new QrFile();
newFile.setFileKey(dto.getReName());
newFile.setFileSize(file.getSize());
String contentType = file.getContentType();
if (contentType.startsWith("image/")) {
BufferedImage bi = ImageIO.read(file.getInputStream());
newFile.setImgWidth(bi.getWidth());
newFile.setImgHeight(bi.getHeight());
}
newFile.setCreateBy(dto.getUsername());
newFile.setOwnerId(dto.getUserId());
newFile.setFileType(file.getContentType());
newFile.setUrl(dto.getOssUrl());
newFile.setName(file.getOriginalFilename());
newFile.setCreateTime(new Date());
newFile.setUpdateTime(new Date());
newFile.setDeleteFlag(false);
this.baseMapper.insert(newFile);
return newFile.getId();
} catch (Exception e) {
log.error("素材上传保存文件异常:",e);
return null;
}
}
}
package com.qrkjdiy.publish.service.qrfile;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.qrkjdiy.common.security.AuthUser;
import com.qrkjdiy.common.vo.PageVO;
import com.qrkjdiy.common.vo.SearchVO;
import com.qrkjdiy.base.entity.qrfile.QrFile;
import com.qrkjdiy.base.dto.qrfile.FileOwnerDTO;
import com.qrkjdiy.base.dto.qrfile.QrFileDTO;
import java.util.List;
/**
* 文件管理业务层
*
* @author qr.lihailin
*/
public interface QrFileService extends IService<QrFile> {
/**
* 批量删除
*
* @param ids
*/
void batchDelete(List<String> ids);
/**
* 所有者批量删除
*
* @param ids ID
* @param authUser 操作者
*/
void batchDelete(List<String> ids, AuthUser authUser);
/**
* 自定义搜索分页
*
* @param file
* @param searchVO
* @param pageVo
* @return
*/
IPage<QrFile> customerPage(QrFile file, SearchVO searchVO, PageVO pageVo);
/**
* 所属文件数据查询
*
* @param file
* @param searchVO
* @param pageVo
* @param ownerDTO
* @return
*/
IPage<QrFile> customerPageOwner(FileOwnerDTO ownerDTO, QrFile file, SearchVO searchVO, PageVO pageVo);
int saveFile(QrFile qrFile);
/**
* 添加文件
* @param
* @return
*/
String add(QrFileDTO dto);
}
10.那一条很固定的值，到时候会从数据库取出来
扫一扫
5384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
