Java导出Excel文档（poi），并上传到腾讯云对象存储服务器

古城微笑少年丶 2022-06-07 11:48 466阅读 0赞

### 需求 ###

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

### 开发思路： ###

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

### 工具 ###

**[poi-3.14-20160307.jar(点击可下载)][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;
        }

### 生成的文件示例 ###

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

**周报**  
![这里写图片描述][SouthEast]

**季报**

![这里写图片描述][SouthEast 1]

以上

[poi-3.14-20160307.jar]: http://download.csdn.net/download/yaosir12/9475344
[SouthEast]: /images/20220607/4db8c48b63c44a94af33658d3a0f4ebe.png
[SouthEast 1]: /images/20220607/772572c3a3fb4cf28c66a43aced4628f.png