导出EXCEL表格

前端点击导出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;
    }