前端点击导出EXCEL表格:
导出当前页:
F12查看Network查看请求路径:
controller:
@Controller
@RequestMapping("/admin/modules/salesman/salesmanproductcommissionsetup")
public class SalesmanProductCommissionSetupController extends BaseRestSpringController{
/**
* 导出Excel
* @param model
* @return
*/
@RequestMapping(value="/exportExcel" ,method=RequestMethod.POST)
//参数:产品ID
public String exportExcel(ModelMap model, @RequestBody SalesmanProductCommissionSetupExportColumns exportColumns) throws IOException, SolrServerException {
//导出路径
String xlsOutputPath = "xlsoutput/productCommissionExport/" + getUUIDPath();
salesmanProductCommissionSetupService.exportExcel(exportColumns,WebContextFactory.getWebRealPath() +xlsOutputPath);
model.put("fileNm", xlsOutputPath);
setSuccess(model);
return RESULT_ACTION;
}
//文档命名方法,登录用户ID+年月日+UUID+.xls
private String getUUIDPath() {
//文件后缀
String fileName = "x.xls";
StringBuilder builder = new StringBuilder();
GregorianCalendar cal = new GregorianCalendar();
cal.setTime(new Date());
String uuid = UUID.randomUUID().toString();
builder.append(cal.get(Calendar.YEAR))
.append("_").append(cal.get(Calendar.MONTH) + 1)
.append("_").append(cal.get(Calendar.DAY_OF_MONTH))
.append("_").append(uuid)
.append(fileName.substring(fileName.lastIndexOf('.')));
String userLoginId = WebContextFactory.getWebContext().getAdminUser().getLoginId();
fileName = userLoginId + "_" + builder.toString();
return fileName;
}
}
生成文件路径:
生成名字方法:
接口:
public interface ISalesmanProductCommissionSetupService extends IBaseEntityManager<SalesmanProductCommissionSetup, Integer>{
void exportExcel(SalesmanProductCommissionSetupExportColumns exportColumns, String xlsOutputPath) throws SolrServerException, IOException;
}
Service:
@Override
public void exportExcel(SalesmanProductCommissionSetupExportColumns exportColumns, String xlsOutputPath) throws SolrServerException, IOException {
List<SaelesmanProductCommissionSetupExcelObject> excelObjectList = new ArrayList<SaelesmanProductCommissionSetupExcelObject>();
List<SalesmanLevel> levels = ServiceManager.suborgSalesmanLevelService.findAll();//业务员等级列表
if (exportColumns.getIsExportCurPage() != null && BoolCodeEnum.YES.toCode().equals(exportColumns.getIsExportCurPage())) {
Integer i = 1;
for (Integer skuId : exportColumns.getExportSkuIds()) {
Sku sku = ServiceManager.skuService.getById(skuId);
SalesmanProductCommissionSetupListVo listVo = buildListVo(sku, levels);
if(null != listVo){
SaelesmanProductCommissionSetupExcelObject excelObject = new SaelesmanProductCommissionSetupExcelObject();
BeanUtils.copyProperties(listVo , excelObject);
StringBuilder commission = new StringBuilder();
List<SalesmanProductCommissionSetupVo> levelRateList = listVo.getLevelRateList();
for(SalesmanProductCommissionSetupVo rate:levelRateList){
commission.append(rate.getSalesmanLevelNm()).append(":").append(rate.getSalesmanOrderRatio()).append("%、");
}
excelObject.setCommission(commission.toString());
excelObject.setNo(i);
excelObjectList.add(excelObject);
i++;
}
}
} else {
SkuSearchParameters searchParameters = new SkuSearchParameters();
searchParameters.setSysOrgId(WebContextFactory.getWebContext().getAdminLoginOrgId());
searchParameters.setIsProductCommission(BoolCodeEnum.YES.toCode());
Page<Sku> skus = ServiceManager.skuService.searchSku(1, 100000, searchParameters);
Integer i = 1;
for (Sku sku : skus) {
SalesmanProductCommissionSetupListVo listVo = buildListVo(sku, levels);
if(null != listVo){
SaelesmanProductCommissionSetupExcelObject excelObject = new SaelesmanProductCommissionSetupExcelObject();
BeanUtils.copyProperties(listVo , excelObject);
StringBuilder commission = new StringBuilder();
List<SalesmanProductCommissionSetupVo> levelRateList = listVo.getLevelRateList();
for(SalesmanProductCommissionSetupVo rate:levelRateList){
commission.append(rate.getSalesmanLevelNm()).append(":").append(rate.getSalesmanOrderRatio()).append("%、");
}
excelObject.setCommission(commission.toString());
excelObject.setNo(i);
excelObjectList.add(excelObject);
i++;
}
}
}
String xlsTemplatePath = WebContextFactory.getWebRealPath() + "xlstemplate/salesmanProductCommissionSetupExport.xls";
ListExcelWriter listExcelWriter = new ListExcelWriter(xlsTemplatePath);
listExcelWriter.fillToFile(excelObjectList, xlsOutputPath);
}
private SalesmanProductCommissionSetupListVo buildListVo(Sku sku , List<SalesmanLevel> levels){
SalesmanProductCommissionSetupListVo vo = new SalesmanProductCommissionSetupListVo();
//基本信息
vo.setSkuId(sku.getSkuId());
vo.setProductId(sku.getProductId());
vo.setPriceSymbo(CurrencyCodeEnum.CHINA_YUAN.toSymbo()); //todo 目前只支持人民币
vo.setSalePrice(sku.getSalePrice());
vo.setCurrentNum(sku.getCurrentNum());
//获取商品的业务员等级提成比率列表
List<SalesmanProductCommissionSetup> setups = ServiceManager.salesmanProductCommissionSetupService.findByKey(ISalesmanProductCommissionSetup.SKU_ID, sku.getSkuId());
List<SalesmanProductCommissionSetupVo> setupVos = new ArrayList<SalesmanProductCommissionSetupVo>(setups.size());
for (SalesmanProductCommissionSetup setup : setups){
SalesmanProductCommissionSetupVo setupVo = new SalesmanProductCommissionSetupVo();
BeanUtils.copyProperties(setup , setupVo);
for(SalesmanLevel level : levels){//使用for去代替数据库查询,因为levels很小
if(level.getSalesmanLevelId().equals(setup.getSalesmanLevelId())){
setupVo.setSalesmanLevelNm(level.getSalesmanLevelNm());
}
}
setupVos.add(setupVo);
}
vo.setLevelRateList(setupVos);
//设置商品属性
Product product = ServiceManager.productService.getById(sku.getProductId());
ProductBaseInf productBaseInf = ServiceManager.productBaseInfService.getById(product.getProductBaseInfId());
if(null != productBaseInf) {
vo.setProductNm(productBaseInf.getProductNm());
vo.setProductCoding(productBaseInf.getProductCoding());
vo.setIsMedicinal(productBaseInf.getIsMedicinal());
vo.setPrescriptionTypeCode(productBaseInf.getPrescriptionTypeCode());
vo.setIsOnSale(product.getIsOnSale());
//商品图片
ProductImageValue imageValue = ServiceManager.objectEnginService.getObject(ObjectEngineTypeCodeEnum.PRODUCT_IMAGES, productBaseInf.getProductBaseInfId(), ProductImageValue.class);
if (null != imageValue && imageValue.getImageValueEntryList() != null && imageValue.getImageValueEntryList().size() > 0) {
String imageFiledId = imageValue.getImageValueEntryList().get(0).getSysFileId();
vo.setProductImageUrl(FileSystemEngin.getFileSystem().getUrl(imageFiledId));
}
//商品品牌
vo.setBrandId(productBaseInf.getBrandId());
Brand brand = ServiceManager.brandService.getById(productBaseInf.getBrandId());
if(null != brand){
vo.setBrandNm(brand.getBrandNm());
}else {
vo.setBrandNm("--");
}
//商品的扩展属性
AttrDictValue dictValue = ServiceManager.objectEnginService.getObject(ObjectEngineTypeCodeEnum.PRODUCT_DICT_VALUE, productBaseInf.getProductBaseInfId(), AttrDictValue.class);
//批准文号
String approvalNumCode = ServiceManager.productService.getApprovalNumCode(productBaseInf.getMedicinalTypeCode());
AttrDictValue.DicValueEntry approvalNum = dictValue.getByInnerCode(approvalNumCode);
vo.setApprovalNum(null != approvalNum ? approvalNum.getValueString() : "--");
//规格
if(StringUtils.isNotBlank(sku.getSpecNm())) {
vo.setSpecNm(sku.getSpecNm());
}else {
String specNmCode = ServiceManager.productService.getSpecNmCode(productBaseInf.getMedicinalTypeCode());
String unitCode = ServiceManager.productService.getUnitCode(productBaseInf.getMedicinalTypeCode());
AttrDictValue.DicValueEntry spec = dictValue.getByInnerCode(specNmCode);
AttrDictValue.DicValueEntry unit = dictValue.getByInnerCode(unitCode);
String specNm =(null == spec?"--/--":spec.getValueString()+"/"+ (null == unit?"--":unit.getValueString()));
vo.setSpecNm(specNm);
}
return vo;
}
return null;
}