package cn.lili.modules.goods.serviceimpl;
|
|
import cn.hutool.core.convert.Convert;
|
import cn.hutool.core.text.CharSequenceUtil;
|
import cn.hutool.poi.excel.ExcelReader;
|
import cn.hutool.poi.excel.ExcelUtil;
|
import cn.lili.common.exception.ServiceException;
|
import cn.lili.common.security.context.UserContext;
|
import cn.lili.modules.goods.entity.dos.Category;
|
import cn.lili.modules.goods.entity.dos.GoodsUnit;
|
import cn.lili.modules.goods.entity.dto.GoodsImportDTO;
|
import cn.lili.modules.goods.entity.dto.GoodsOperationDTO;
|
import cn.lili.modules.goods.entity.vos.CategoryVO;
|
import cn.lili.modules.goods.service.CategoryService;
|
import cn.lili.modules.goods.service.GoodsImportService;
|
import cn.lili.modules.goods.service.GoodsService;
|
import cn.lili.modules.goods.service.GoodsUnitService;
|
import cn.lili.modules.store.entity.vos.FreightTemplateVO;
|
import cn.lili.modules.store.service.FreightTemplateService;
|
import cn.lili.modules.store.service.StoreDetailService;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.hssf.usermodel.DVConstraint;
|
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddressList;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.net.URLEncoder;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Objects;
|
|
@Slf4j
|
@Service
|
public class GoodsImportServiceImpl implements GoodsImportService {
|
|
@Autowired
|
private FreightTemplateService freightTemplateService;
|
@Autowired
|
private StoreDetailService storeDetailService;
|
@Autowired
|
private CategoryService categoryService;
|
@Autowired
|
private GoodsUnitService goodsUnitService;
|
@Autowired
|
private GoodsService goodsService;
|
|
private static final int COLUMS = 15;
|
|
@Override
|
public void download(HttpServletResponse response) {
|
// String storeId = "1376369067769724928";
|
String storeId = Objects.requireNonNull(UserContext.getCurrentUser()).getStoreId();
|
//创建Excel工作薄对象
|
Workbook workbook = new HSSFWorkbook();
|
//生成一个表格 设置：页签
|
Sheet sheet = workbook.createSheet("导入模板");
|
//创建第1行
|
Row row0 = sheet.createRow(0);
|
row0.createCell(0).setCellValue("商品名称");
|
row0.createCell(1).setCellValue("商品卖点");
|
row0.createCell(2).setCellValue("商品分类");
|
row0.createCell(3).setCellValue("运费模板");
|
row0.createCell(4).setCellValue("计量单位");
|
row0.createCell(5).setCellValue("发布状态");
|
row0.createCell(6).setCellValue("商品图片");
|
row0.createCell(7).setCellValue("成本价");
|
row0.createCell(8).setCellValue("销售价");
|
row0.createCell(9).setCellValue("库存");
|
row0.createCell(10).setCellValue("重量");
|
row0.createCell(11).setCellValue("货号");
|
row0.createCell(12).setCellValue("详情");
|
row0.createCell(13).setCellValue("规格项");
|
row0.createCell(14).setCellValue("规格值");
|
|
|
sheet.setColumnWidth(0, 7000);
|
sheet.setColumnWidth(1, 7000);
|
sheet.setColumnWidth(2, 7000);
|
sheet.setColumnWidth(3, 7000);
|
sheet.setColumnWidth(4, 7000);
|
sheet.setColumnWidth(5, 3000);
|
sheet.setColumnWidth(6, 7000);
|
sheet.setColumnWidth(7, 3000);
|
sheet.setColumnWidth(8, 3000);
|
sheet.setColumnWidth(9, 3000);
|
sheet.setColumnWidth(10, 3000);
|
sheet.setColumnWidth(11, 7000);
|
sheet.setColumnWidth(12, 7000);
|
sheet.setColumnWidth(13, 3000);
|
sheet.setColumnWidth(14, 3000);
|
|
String goodsManagementCategory = storeDetailService.getStoreDetail(storeId).getGoodsManagementCategory();
|
List<CategoryVO> categoryVOList = this.categoryService.getStoreCategory(goodsManagementCategory.split(","));
|
List<String> categoryNameList = new ArrayList<>();
|
|
//先简单写，后期优化
|
//循环三次添加值
|
//循环列表，存放ID-分类名称
|
for (CategoryVO categoryVO1 : categoryVOList) {
|
for (CategoryVO categoryVO2 : categoryVO1.getChildren()) {
|
for (CategoryVO categoryVO3 : categoryVO2.getChildren()) {
|
categoryNameList.add(categoryVO3.getId() + "-" + categoryVO3.getName());
|
}
|
}
|
}
|
|
List<String> freightTemplateNameList = new ArrayList<>();
|
//循环列表，存放ID-运费模板名称
|
for (FreightTemplateVO freightTemplateVO : freightTemplateService.getFreightTemplateList(storeId)) {
|
freightTemplateNameList.add(freightTemplateVO.getId() + "-" + freightTemplateVO.getName());
|
}
|
|
//获取计量单位
|
List<String> goodsUnitList = new ArrayList<>();
|
List<GoodsUnit> goodsUnitListVO = goodsUnitService.list();
|
for (GoodsUnit goodsUnit : goodsUnitListVO) {
|
goodsUnitList.add(goodsUnit.getId() + "-" + goodsUnit.getName());
|
}
|
|
//添加分类
|
this.excelTo255(workbook, "hiddenCategoryVO", 1, categoryNameList.toArray(new String[]{}), 1, 5000, 2, 2);
|
|
//添加运费模板
|
this.excelTo255(workbook, "hiddenFreightTemplateVO", 2, freightTemplateNameList.toArray(new String[]{}), 1, 5000, 3, 3);
|
|
//添加计量单位
|
this.excelTo255(workbook, "hiddenGoodsUnit", 3, goodsUnitList.toArray(new String[]{}), 1, 5000, 4, 4);
|
|
//添加计量单位
|
this.excelTo255(workbook, "hiddenRelease", 4, new String[]{"上架", "仓库中"}, 1, 5000, 5, 5);
|
|
|
ServletOutputStream out = null;
|
try {
|
//设置公共属性，列表名称
|
response.setContentType("application/vnd.ms-excel;charset=utf-8");
|
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("下载商品导入模板", "UTF8") + ".xls");
|
out = response.getOutputStream();
|
workbook.write(out);
|
} catch (Exception e) {
|
log.error("下载商品导入模板错误", e);
|
} finally {
|
try {
|
out.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
@Override
|
public void importExcel(MultipartFile files) throws Exception {
|
InputStream inputStream;
|
List<GoodsImportDTO> goodsImportDTOList = new ArrayList<>();
|
|
inputStream = files.getInputStream();
|
ExcelReader excelReader = ExcelUtil.getReader(inputStream);
|
// 读取列表
|
// 检测数据-查看分类、模板、计量单位是否存在
|
List<List<Object>> read = excelReader.read(1, excelReader.getRowCount());
|
for (List<Object> objects : read) {
|
GoodsImportDTO goodsImportDTO = new GoodsImportDTO();
|
if (objects.size() < COLUMS){
|
throw new ServiceException("请将表格内容填写完全！");
|
}
|
for (Object object : objects) {
|
if( CharSequenceUtil.isEmpty(object.toString()) || CharSequenceUtil.isBlank(object.toString())){
|
throw new ServiceException("请将表格内容填写完全！");
|
}
|
}
|
|
String categoryId = null;
|
try {
|
categoryId = objects.get(2).toString().substring(0, objects.get(2).toString().indexOf("-"));
|
} catch (Exception e) {
|
throw new ServiceException("请选择商品分类");
|
}
|
|
Category category = categoryService.getCategoryById(categoryId);
|
if (category == null) {
|
throw new ServiceException("商品分类不存在：" + objects.get(2).toString().substring(objects.get(2).toString().indexOf("-")));
|
}
|
|
String templateId = null;
|
try {
|
templateId = objects.get(3).toString().substring(0, objects.get(3).toString().indexOf("-"));
|
} catch (Exception e) {
|
throw new ServiceException("请选择物流模板");
|
}
|
FreightTemplateVO freightTemplateVO = freightTemplateService.getFreightTemplate(templateId);
|
if (freightTemplateVO == null) {
|
throw new ServiceException("配送模板不存在：" + objects.get(3).toString().substring(objects.get(3).toString().indexOf("-")));
|
}
|
|
goodsImportDTO.setGoodsName(objects.get(0).toString());
|
goodsImportDTO.setSellingPoint(objects.get(1).toString());
|
goodsImportDTO.setCategory(category);
|
goodsImportDTO.setTemplate(templateId);
|
goodsImportDTO.setGoodsUnit(objects.get(4).toString().substring(objects.get(4).toString().indexOf("-") + 1));
|
goodsImportDTO.setRelease(objects.get(5).toString().equals("上架"));
|
|
List<String> goodsGalleryList = new ArrayList<>();
|
goodsGalleryList.add(objects.get(6).toString());
|
goodsImportDTO.setImages(goodsGalleryList);
|
goodsImportDTO.setGoodsGalleryList(goodsGalleryList);
|
|
goodsImportDTO.setCost(Convert.toDouble(objects.get(7)));
|
goodsImportDTO.setPrice(Convert.toDouble(objects.get(8)));
|
goodsImportDTO.setQuantity(Convert.toInt(objects.get(9)));
|
goodsImportDTO.setWeight(Convert.toDouble(objects.get(10)));
|
goodsImportDTO.setSn(objects.get(11).toString());
|
goodsImportDTO.setIntro("<p>" + objects.get(12).toString() + "</p>");
|
goodsImportDTO.setSkuKey(objects.get(13).toString());
|
goodsImportDTO.setSkuValue(objects.get(14).toString());
|
goodsImportDTOList.add(goodsImportDTO);
|
}
|
//添加商品
|
addGoodsList(goodsImportDTOList);
|
|
}
|
|
/**
|
* 添加商品
|
*
|
* @param goodsImportDTOList
|
*/
|
private void addGoodsList(List<GoodsImportDTO> goodsImportDTOList) {
|
|
for (GoodsImportDTO goodsImportDTO : goodsImportDTOList) {
|
GoodsOperationDTO goodsOperationDTO = new GoodsOperationDTO(goodsImportDTO);
|
|
//获取父
|
Category parentCategory = categoryService.getCategoryById(goodsImportDTO.getCategory().getParentId());
|
goodsOperationDTO.setCategoryPath(parentCategory.getParentId() + "," + parentCategory.getId() + "," + goodsImportDTO.getCategory().getId());
|
//添加商品
|
goodsService.addGoods(goodsOperationDTO);
|
}
|
|
}
|
|
/**
|
* 表格
|
*
|
* @param workbook 表格
|
* @param sheetName sheet名称
|
* @param sheetNameIndex 开始
|
* @param sheetData 数据
|
* @param firstRow 开始行
|
* @param lastRow 结束行
|
* @param firstCol 开始列
|
* @param lastCol 结束列
|
*/
|
private void excelTo255(Workbook workbook, String sheetName, int sheetNameIndex, String[] sheetData,
|
int firstRow, int lastRow, int firstCol, int lastCol) {
|
//将下拉框数据放到新的sheet里，然后excle通过新的sheet数据加载下拉框数据
|
Sheet hidden = workbook.createSheet(sheetName);
|
|
//创建单元格对象
|
Cell cell = null;
|
//遍历我们上面的数组，将数据取出来放到新sheet的单元格中
|
for (int i = 0, length = sheetData.length; i < length; i++) {
|
//取出数组中的每个元素
|
String name = sheetData[i];
|
//根据i创建相应的行对象（说明我们将会把每个元素单独放一行）
|
Row row = hidden.createRow(i);
|
//创建每一行中的第一个单元格
|
cell = row.createCell(0);
|
//然后将数组中的元素赋值给这个单元格
|
cell.setCellValue(name);
|
}
|
// 创建名称，可被其他单元格引用
|
Name namedCell = workbook.createName();
|
namedCell.setNameName(sheetName);
|
// 设置名称引用的公式
|
namedCell.setRefersToFormula(sheetName + "!$A$1:$A$" + (sheetData.length > 0 ? sheetData.length : 1));
|
//加载数据,将名称为hidden的sheet中的数据转换为List形式
|
DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);
|
|
// 设置第一列的3-65534行为下拉列表
|
// (3, 65534, 2, 2) ====> (起始行,结束行,起始列,结束列)
|
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
|
// 将设置下拉选的位置和数据的对应关系 绑定到一起
|
DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
|
|
//将第二个sheet设置为隐藏
|
workbook.setSheetHidden(sheetNameIndex, true);
|
//将数据赋给下拉列表
|
workbook.getSheetAt(0).addValidationData(dataValidation);
|
}
|
}
|
