解析xlsx，xls，csv文件

• 预览添加的报表

@ApiOperation("预览添加的报表")
@PostMapping("/preview")
public List<UserExport> preview(MultipartFile file, Integer type) throws IOException, ParseException {
    BaseExport export = null;
    switch (type) {
        case 1:
            export = context.getBean(XlsxExport.class);
            break;
        case 2:
            export = context.getBean(CsvExport.class);
            break;
        case 3:
            export = context.getBean(XlsExport.class);
    }
    if (export == null) {
        throw WebException.error("请选择正确的类型");
    }
    List<UserExport> exportList = export.parse(file);
    return exportList;
}

• 确认保存预览的报表

@ApiOperation("确认保存预览的报表")
@PostMapping("/confirmSave")
public void confirmSave(@RequestBody List<UserExport> export) {
    userExportService.batchSave(export);
}

• Base

/**
 * 解析报表 流程 parse -> init -> dealRelated -> calc()
 */
@RequiredArgsConstructor
public abstract class BaseExport {
    protected final SettingService settingService;
    protected final UserAppService userAppService;
    protected final AdOperatorService operatorService;
    protected final UserService userService;

    protected String operatorName;

    protected BigDecimal cpmPercent;
    protected BigDecimal displayPercent;
    protected BigDecimal clickPercent;
    protected BigDecimal expectProfitPercent;


    public void init() {
        
    }


    /**
     * 开始解析
     *
     * @param file 文件
     * @return 报表数据
     * @throws IOException 异常
     */
    public List<UserExport> parse(MultipartFile file) throws IOException, ParseException {
        //初始化,查询数据库中各项百分比
        init();
        //设置广告商名称
        setOperatorName();
        //将上传文件解析为报表数据
        List<UserExport> exports = process(file);
        if (exports == null || exports.size() == 0)
            throw WebException.error("未解析到数据,请检查数据表格");
        for (UserExport export : exports) {
            //处理报表与其他表关联
            dealRelated(export);
            //计算
            calc(export);
        }
        return exports;
    }

    //处理报表关联
    protected void dealRelated(UserExport export) {
        //根据平台Id查询用户应用 
        //报表中设置用户Id 系统类型 代码位类型等 
        //根据广告名称查询广告商 
        //报表中设置广告商Id 名称 描述 等 
        //设置计算百分比  
	}
    // 设置报表类型 
    protected abstract void setOperatorName();
	//解析报表
    protected abstract List<UserExport> process(MultipartFile reader) throws IOException, ParseException;

}

• setCodeTypeByName

public void setCodeTypeByName(String codeTypeName) {
   if (Strings.isBlank(codeTypeName))
        return;
    this.codeTypeName = codeTypeName;
    codeTypeName = codeTypeName.trim().toLowerCase();
    switch (codeTypeName) {
        case "开屏":
            codeType = CodeType.Splash.getType();
            return;
        case "激励视频":
            codeType = CodeType.RewardVideo.getType();
            return;
        case "banner":
            codeType = CodeType.Banner.getType();
    }
}

• XLSX

@Component("xlsxExport")
public class XlsxExport extends BaseExport {

    @Override
    protected void setOperatorName() {
	        this.operatorName = "X";
    }

    @Override
    //xlsx
    public List<UserExport> process(MultipartFile file) throws IOException {
        ExcelReader reader = new ExcelReader(file.getInputStream(), 0, true);
        Map<String, String> headers = new HashMap<>();
        headers.put("时间", "day");
        headers.put("应用名称", "appName");
        headers.put("代码位类型", "codeTypeName");
        headers.put("代码位名称", "adPositionName");
        headers.put("代码位ID", "adPositionId");
        headers.put("展现量", "actualDisplayTimes");
        headers.put("点击量", "actualClickTimes");
        headers.put("CPM", "actualCpm");
        headers.put("应用ID", "platformAppId");
        headers.put("预估收益(人民币)", "actualProfit");
        reader.setHeaderAlias(headers);
        return reader.read(0, 2, UserExport.class);
    }

    @Override
    protected void dealRelated(UserExport export) {
        super.dealRelated(export);
        export.setCodeTypeByName(export.getCodeTypeName());
    }

    @Override
    protected void calc(UserExport export) {
       
    }
}

• CSV

@Component
public class CsvExport extends BaseExport {

    @Override
    protected void setOperatorName() {
        this.operatorName = "V";
    }

    public List<UserExport> process(MultipartFile file) throws IOException, ParseException {
        InputStreamReader reader = new InputStreamReader(file.getInputStream());
        CsvReader csvReader = CsvUtil.getReader();
        List<GDTCvs> read = csvReader.read(reader, GDTCvs.class);
        List<UserExport> exports = new ArrayList<>();
        for (GDTCvs gdtCvs : read) {
            exports.add(gdtCvs.getExport());
        }
        return exports;
    }

    @Override
    protected void dealRelated(UserExport export) {
        String positionName = export.getAdPositionName();
        positionName = positionName.trim().toLowerCase();
        CodeType codeType = null;
        if (positionName.contains("开屏"))
            codeType = CodeType.Splash;
        else if (positionName.contains("激励"))
            codeType = CodeType.RewardVideo;
        else if (positionName.contains("banner"))
            codeType = CodeType.Banner;
        if (codeType != null)
            export.setCodeType(codeType.getType());
        super.dealRelated(export);
    }
}

@Component("XlsExport")
public class XlsExport extends BaseExport {

    @Override
    protected void setOperatorName() {
        this.operatorName = "S";
    }

    @Override
    public List<UserExport> process(MultipartFile file) throws IOException {
        ExcelReader reader = new ExcelReader(file.getInputStream(), 0, true);
        Map<String, String> headers = new HashMap<>();
        headers.put("日期", "day");
        headers.put("应用名称", "appName");
        headers.put("应用id", "platformAppId");
        headers.put("广告类型", "codeTypeName");
        headers.put("广告单元", "adPositionName");
        headers.put("广告单元id", "adPositionId");
        headers.put("展示数", "actualDisplayTimes");
        headers.put("点击数", "actualClickTimes");
        headers.put("千次展示收入", "actualCpm");
        headers.put("预估收入", "actualProfit");
        reader.setHeaderAlias(headers);
        return reader.read(0, 1, UserExport.class);
    }

    @Override
    protected void dealRelated(UserExport export) {
        super.dealRelated(export);
        export.setCodeTypeByName(export.getCodeTypeName());
    }

    @Override
    protected void calc(UserExport export) {
        
    }
}

vue上传文件请点击vue解析文件