需求

后台生成周报月报季报年报Excel,将文件下载链接推送给对应客户

开发思路:

1.根据选定日期生成周报,月报,季报,年报数据 2.将这些数据报告生成Excel表格 3.把生成的文件上传到腾讯云对象存储服务器 4.将服务器返回的url存储到数据库

工具

poi-3.14-20160307.jar(点击可下载)

数据

获取数据部分省略了

代码

主方法

public boolean addReportExcelToCloud(ReportResult rr) {

        OutputStream out = new ByteArrayOutputStream();
        ExcelProjectUtils eu = new ExcelProjectUtils();
        eu.exportExcel(rr, out);   //<1>
        ConvertUtil cu = new ConvertUtil();
        try {
            ByteArrayInputStream byteInput = cu.parse(out);
            String rs = PicUploadToYun.uploadExcel(SysContent.getFileRename("案场数据报.xls"), byteInput);  //<2>
            addReportExcelToDB(rr, rs);  //<3>
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return false;
    }

<1> 将数据生成二进制Excel文件 (方法详细见下面代码)

<2> 将生成的二进制文件上传到腾讯云对象存储服务器 (方法详细见下面代码)

<3> 将服务器返回的url存储到数据库 (方法详细见下面代码)

/**
     * 周报年报生成excel
     * 
     * @param report
     * @param out
     */
    public void exportExcel(ReportResult report, OutputStream out) {

        // 判断传入的时间间隔
        String dateStr = "";
        String reportName = "";
        List<String> dateCount = DateUtil.getTwoDateEveryDay(report.getStartTime(), report.getEndTime());
        if (dateCount.size() <= 7) {
            dateStr += "本周";
            reportName += "案场周报";
        } else if (dateCount.size() >= 28 && dateCount.size() <= 31) {
            dateStr += "本月";
            reportName += "案场月报";
        } else if (dateCount.size() >= 85 && dateCount.size() <= 100) {
            dateStr += "本季度";
            reportName += "案场季报";
        } else if (dateCount.size() >= 180 && dateCount.size() <= 185) {
            dateStr += "本半年度";
            reportName += "案场半年报";
        } else if (dateCount.size() >= 360 && dateCount.size() <= 367) {
            dateStr += "本年度";
            reportName += "案场年报";
        } else {
            dateStr += "时间段内";
            reportName += "案场阶段报";
        }
        report.setReportName(reportName);
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(report.getReportName() + report.getStartTime() + " - " + report.getEndTime());
        // 设置表格默认列宽度为100个字节
        sheet.setDefaultColumnWidth((short) 100);
        /** ----------样式一:标题 ------------ **/
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        //font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 14);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        /***---------样式二:小标题---------***/
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        //style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        //font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 11);
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        /***    样式三:右侧日期       ***/
        HSSFCellStyle style3 = workbook.createCellStyle();
        //样式
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //字体
        HSSFFont font3 = workbook.createFont();
        font3.setFontName("宋体");
        font3.setFontHeightInPoints((short) 11);
        style3.setFont(font3);

        /**       样式四:主内容        ***/
        HSSFCellStyle style4 = workbook.createCellStyle();
        //样式
        style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        //字体
        HSSFFont font4 = workbook.createFont();
        font4.setFontName("宋体");
        font4.setFontHeightInPoints((short) 11);
        style4.setFont(font4);

        /**       样式五:底侧空内容       ***/
        HSSFCellStyle style5 = workbook.createCellStyle();
        //样式
        style5.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style5.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style5.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style5.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //字体
        HSSFFont font5 = workbook.createFont();
        font5.setFontName("宋体");
        font5.setFontHeightInPoints((short) 11);
        style5.setFont(font5);


        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("数据报"));
        // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("saas");

        // 产生表格标题行 -- 项目名称
        HSSFRow row = sheet.createRow(0);
        createCellAndRow(style4, report.getProjectName(), row);

        // 产生表格标题行 -- 周报名称
        row = sheet.createRow(1);
        createCellAndRow(style, report.getReportName(), row);

        // 产生表格标题行 -- 起始时间-终止时间
        row = sheet.createRow(2);
        String startTime = DateUtil.format(DateUtil.parse(report.getStartTime(), DateUtil.PATTERN_CLASSICAL_SIMPLE),
                DateUtil.PATTERN_CLASSICAL_SIMPLE_YMD);
        String endTime = DateUtil.format(DateUtil.parse(report.getEndTime(), DateUtil.PATTERN_CLASSICAL_SIMPLE),
                DateUtil.PATTERN_CLASSICAL_SIMPLE_YMD);
        String date = "日期:" + startTime + " - " + endTime;
        createCellAndRow(style3, date, row);

        // 接访情况标题
        row = sheet.createRow(3);
        createCellAndRow(style2, "·接访情况", row);

        // 接访客户组数
        row = sheet.createRow(4);
        Integer visitCount = report.getVisitCount();
        String visitNum = "1、" + dateStr + "共计接访客户" + visitCount + "组,来访量";
        if (visitCount < 40) {
            visitNum += "较少,有待提升";
        } else if (visitCount >= 41 && visitCount <= 99) {
            visitNum += "尚可,还有提高空间";
        } else if (visitCount >= 100 && visitCount <= 139) {
            visitNum += "很多";
        } else if (visitCount > 140) {
            visitNum += "火爆";
        }
        createCellAndRow(style4, visitNum, row);

        // 有效接访率
        row = sheet.createRow(5);
        Double visitRate = new Double(report.getValidVisitRate());
        String visitRateStr = "2、有效接访率为" + visitRate + "%,接访成效";
        if (visitRate < 50) {
            visitRateStr += "较低,有待提升";
        } else if (visitRate >= 50 && visitRate <= 65) {
            visitRateStr += "尚可,还有提高空间";
        } else if (visitRate >= 65 && visitRate <= 80) {
            visitRateStr += "很高";
        } else if (visitRate > 80) {
            visitRateStr += "极高";
        }
        createCellAndRow(style4, visitRateStr, row);

        // 首访有效率
        row = sheet.createRow(6);
        Double newVisitRate = new Double(report.getValidNewCuVisitRate());
        String newVisitStr = "3、首访有效率为" + newVisitRate + "%,来访转储客的概率";
        if (newVisitRate < 40) {
            newVisitStr += "较差,有待提升";
        } else if (newVisitRate >= 40 && newVisitRate <= 60) {
            newVisitStr += "尚可,还有提高空间";
        } else if (newVisitRate >= 60 && newVisitRate <= 75) {
            newVisitStr += "很高";
        } else if (newVisitRate > 75) {
            newVisitStr += "极高";
        }
        createCellAndRow(style4, newVisitStr, row);

        // 老客户接访占比
        row = sheet.createRow(7);
        Double oldVisitRate = new Double(report.getOldCuVisitRate());
        String oldVisitStr = "4、老客户接访比为" + oldVisitRate + "%,老客户接访的占比";
        if (oldVisitRate < 20) {
            oldVisitStr += "较低";
        } else if (oldVisitRate >= 20 && oldVisitRate <= 40) {
            oldVisitStr += "尚可";
        } else if (oldVisitRate >= 40 && oldVisitRate <= 60) {
            oldVisitStr += "很高";
        } else if (oldVisitRate > 60) {
            oldVisitStr += "极高";
        }
        createCellAndRow(style4, oldVisitStr, row);

        //空行
        row = sheet.createRow(8);
        createCellAndRow(style4, "", row);

        // 储客情况
        row = sheet.createRow(9);
        createCellAndRow(style2, "·储客情况", row);

        // 新增储客
        row = sheet.createRow(10);
        Integer newCuCount = report.getNewCuCount();
        String newCuStr = "1、" + dateStr + "新增储客" + newCuCount + "组,新增量";
        if (newCuCount < 30) {
            newCuStr += "较少,有待提升";
        } else if (newCuCount >= 31 && newCuCount <= 60) {
            newCuStr += "尚可,还有提高空间";
        } else if (newCuCount >= 61 && newCuCount <= 79) {
            newCuStr += "很多";
        } else if (newCuCount > 80) {
            newCuStr += "爆满";
        }
        createCellAndRow(style4, newCuStr, row);

        // 累计老客户
        row = sheet.createRow(11);
        Integer oldCuCount = report.getTotalOldCuCount();
        Integer totalCuCount = report.getTotalCuCount();
        Double oldCuRate = new Double(SysContent.getTwoNumberForValue(oldCuCount, totalCuCount));
        String oldCuStr = "2、累计老客户总量为" + oldCuCount + "组,老客户占比为" + oldCuRate + "%,显示老客户关注度";
        if (oldCuRate < 15) {
            oldCuStr += "较低,有待提升";
        } else if (oldCuRate >= 15 && oldCuRate <= 25) {
            oldCuStr += "尚可,还有提高空间";
        } else if (oldCuRate >= 25 && oldCuRate <= 40) {
            oldCuStr += "很高";
        } else if (oldCuRate > 40) {
            oldCuStr += "极高";
        }
        createCellAndRow(style4, oldCuStr, row);

        // 累计总储客
        row = sheet.createRow(12);
        String totalOldCuStr = "3、累计总储客" + totalCuCount + "组";
        createCellAndRow(style4, totalOldCuStr, row);

        // 成交情况(周报没有,其他有)
        if (report.getSubscribeHouseCount() != null) {

            //空行
            row = sheet.createRow(13);
            createCellAndRow(style4, "", row);

            row = sheet.createRow(14);
            createCellAndRow(style2, "·成交情况", row);

            // 新增认购套数
            row = sheet.createRow(15);
            Integer subscribeHouseCount = report.getSubscribeHouseCount();
            Double subscribeHouseRate = new Double(report.getSubscribeHouseRate());
            String subscribeHouseStr = "1、" + dateStr + "新增认购套数" + subscribeHouseCount + "套,较" + dateStr + "同期";
            if (subscribeHouseRate < 0) {
                subscribeHouseStr += "减少";
            } else {
                subscribeHouseStr += "增长";
            }
            subscribeHouseStr += Math.abs(subscribeHouseRate) + "%";
            createCellAndRow(style4, subscribeHouseStr, row);

            // 新增认购金额
            row = sheet.createRow(16);
            Long subscribeMoney = report.getSubscribeMoney();
            Double subscribeMoneyRate = new Double(report.getSubscribeMoneyRate());
            String subscribeMoneyStr = "   新增认购金额" + subscribeMoney + "万元,较" + dateStr + "同期";
            if (subscribeHouseRate < 0) {
                subscribeMoneyStr += "减少";
            } else {
                subscribeMoneyStr += "增长";
            }
            subscribeMoneyStr += Math.abs(subscribeMoneyRate) + "%";
            createCellAndRow(style4, subscribeMoneyStr, row);

            // 新增签约套数
            row = sheet.createRow(17);
            Integer signCount = report.getSignCount();
            Double signRate = new Double(report.getSignRate());
            String signStr = "2、新增签约套数" + signCount + "套,较" + dateStr + "同期";
            if (signRate < 0) {
                signStr += "减少";
            } else {
                signStr += "增长";
            }
            signStr += Math.abs(signRate) + "%";
            createCellAndRow(style4, signStr, row);

            // 新增签约金额
            row = sheet.createRow(18);
            Long signHouseMoney = report.getSignHouseMoney();
            Double signHouseMoneyRate = new Double(report.getSignHouseMoneyRate());
            String signHouseMoneyStr = "   新增签约金额" + signHouseMoney + "万元,较" + dateStr + "同期";
            if (signHouseMoneyRate < 0) {
                signHouseMoneyStr += "减少";
            } else {
                signHouseMoneyStr += "增长";
            }
            signHouseMoneyStr += Math.abs(signHouseMoneyRate) + "%";
            createCellAndRow(style4, signHouseMoneyStr, row);

            // 新接访签约率
            row = sheet.createRow(19);
            Double newCustomerSignedRate = new Double(report.getNewCustomerSignedRate());
            String newCustomerSignedStr = "3、" + dateStr + "新客户接访签约率" + newCustomerSignedRate + "%,接访签约概率";
            if (newCustomerSignedRate < 4) {
                newCustomerSignedStr += "较低,与理想值差距大";
            } else if (newCustomerSignedRate >= 4 && newCustomerSignedRate <= 6) {
                newCustomerSignedStr += "尚可,还有提高空间";
            } else if (newCustomerSignedRate >= 6 && newCustomerSignedRate <= 7) {
                newCustomerSignedStr += "很高";
            } else if (newCustomerSignedRate > 7) {
                newCustomerSignedStr += "非常高";
            }
            createCellAndRow(style4, newCustomerSignedStr, row);

            // 储客签约率
            row = sheet.createRow(20);
            Double momeryCustomerSignedRate = new Double(report.getMomeryCustomerSignedRate());
            String momeryCustomerSignedStr = "4、储客签约率" + momeryCustomerSignedRate + "%,储备客户签约概率";
            if (momeryCustomerSignedRate < 7) {
                momeryCustomerSignedStr += "较低,与理想值差距大";
            } else if (momeryCustomerSignedRate >= 7 && momeryCustomerSignedRate <= 12) {
                momeryCustomerSignedStr += "尚可,还有提高空间";
            } else if (momeryCustomerSignedRate >= 12 && momeryCustomerSignedRate <= 15) {
                momeryCustomerSignedStr += "很高";
            } else if (momeryCustomerSignedRate > 15) {
                momeryCustomerSignedStr += "非常高";
            }
            createCellAndRow(style4, momeryCustomerSignedStr, row);

            // 老客户签约率
            row = sheet.createRow(21);
            Double oldCustomerSignedRate = new Double(report.getOldCustomerSignedRate());
            String oldCustomerSignedStr = "5、老客户签约率为23.2%,高意向客户签约概率";
            if (oldCustomerSignedRate < 25) {
                oldCustomerSignedStr += "较低,与理想值差距大";
            } else if (oldCustomerSignedRate >= 25 && oldCustomerSignedRate <= 35) {
                oldCustomerSignedStr += "尚可,还有提高空间";
            } else if (oldCustomerSignedRate >= 35 && oldCustomerSignedRate <= 50) {
                oldCustomerSignedStr += "很高";
            } else if (oldCustomerSignedRate > 50) {
                oldCustomerSignedStr += "非常高";
            }
            createCellAndRow(style4, oldCustomerSignedStr, row);

            // 认购客户签约率
            row = sheet.createRow(22);
            Double contratCuSignedRate = new Double(report.getContratCuSignedRate());
            String contratCuSignedStr = "6、认购客户签约率为92%,已认购客户签约率";
            if (contratCuSignedRate < 95) {
                contratCuSignedStr += "不高,较多退订或拒签";
            } else if (contratCuSignedRate >= 95 && contratCuSignedRate <= 97) {
                contratCuSignedStr += "尚可,一定数量退订或拒签";
            } else if (contratCuSignedRate >= 97 && contratCuSignedRate <= 99) {
                contratCuSignedStr += "很高";
            } else if (contratCuSignedRate > 99) {
                contratCuSignedStr += "非常高";
            }
            createCellAndRow(style4, contratCuSignedStr, row);

            //空行
            row = sheet.createRow(23);
            createCellAndRow(style4, "", row);

            //底侧
            row = sheet.createRow(24);
            createCellAndRow(style5, "", row);

        }else{
            row = sheet.createRow(13);
            createCellAndRow(style5, "", row);
        }

        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void createCellAndRow(HSSFCellStyle style, String text, HSSFRow row) {
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(style);
        HSSFRichTextString rs = new HSSFRichTextString(text);
        cell.setCellValue(rs);
    }
/**
     * 上传Excel
     * @param fileNewName
     * @param uploadFile
     * @return
     */
    public static String uploadExcel(String fileNewName,ByteArrayInputStream uploadFile){
        // 设置用户属性, 包括appid, secretId和SecretKey
                // 这些属性可以通过cos控制台获取(https://console.qcloud.com/cos)
                String version = PropertiesUtil.getValue("version");
                     long appId = "你的appId";
                     String secretId = "你的secretId ";
                     String secretKey = "你的secretKey ";

                // 设置要操作的bucket
                String bucketName = "root";
                // 初始化客户端配置
                ClientConfig clientConfig = new ClientConfig();
                // 设置bucket所在的区域,比如广州(gz), 天津(tj)
                clientConfig.setRegion("sh");
                // 初始化秘钥信息
                Credentials cred = new Credentials(appId, secretId, secretKey);
                // 初始化cosClient
                COSClient cosClient = new COSClient(clientConfig, cred);
                // 文件操作 //
                // 1. 上传文件(默认不覆盖)
                // 将本地的local_file_1.txt上传到bucket下的根分区下,并命名为sample_file.txt
                // 默认不覆盖, 如果cos上已有文件, 则返回错误
                String cosFilePath = "/report/" + fileNewName;

                byte[] localFilePath1 = null;
                try {
                    localFilePath1 = ConvertUtil.toByteArray(uploadFile);
                } catch (IOException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }

                UploadFileRequest uploadFileRequest = new UploadFileRequest(bucketName, cosFilePath, localFilePath1);
                uploadFileRequest.setEnableShaDigest(false);
                String uploadFileRet = cosClient.uploadFile(uploadFileRequest);
                System.out.println("upload file ret:" + uploadFileRet);
                //获取保存路径
                ObjectMapper om = new ObjectMapper();
                HashMap map = new HashMap<>();
                try {
                    map = om.readValue(uploadFileRet, HashMap.class);
                } catch (JsonParseException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (JsonMappingException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                HashMap<String, String> value = (HashMap<String, String>) map.get("data");
                return value.get("source_url");

    }
public boolean addReportExcelToDB(ReportResult rr, String url) {

        if(StringUtils.isEmpty(url)){
            return false;
        }
        if(rr == null){
            return false;
        }

        ProjectReportRecord prr = new ProjectReportRecord();
        prr.setCreateTime(DateUtil.format(new Date()));
        prr.setProjectId(rr.getProjectId());
        prr.setProjectName(rr.getProjectName());
        prr.setStartTime(rr.getStartTime());
        prr.setEndTime(rr.getEndTime());
        prr.setUrl(url);
        String report = "";
        if("案场周报".equals(rr.getReportName())){
            report = "week";
        }else if("案场月报".equals(rr.getReportName())){
            report = "month";
        }else if("案场季报".equals(rr.getReportName())){
            report = "quarter";
        }else if("案场半年报".equals(rr.getReportName())){
            report = "half";
        }else if("案场年报".equals(rr.getReportName())){
            report = "year";
        }else{
            report = "other";
        }
        prr.setReportName(report);

        baseDao.save(prr);

        return true;
    }

生成的文件示例

周报或者其他报告都是后台自动根据时间进行判断的

周报 这里写图片描述

季报

这里写图片描述

以上