需求
后臺生成周報月報季報年報Excel，將文件下載鏈接推送給對應(yīng)客戶
開發(fā)思路：
1.根據(jù)選定日期生成周報，月報，季報，年報數(shù)據(jù)
2.將這些數(shù)據(jù)報告生成Excel表格
3.把生成的文件上傳到騰訊云對象存儲服務(wù)器
4.將服務(wù)器返回的url存儲到數(shù)據(jù)庫
工具
數(shù)據(jù)
獲取數(shù)據(jù)部分省略了
代碼
主方法
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("案場數(shù)據(jù)報.xls"), byteInput); <2>
addReportExcelToDB(rr, rs); <3>
return true;
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
<1> 將數(shù)據(jù)生成二進制Excel文件 (方法詳細見下面代碼)
<2> 將生成的二進制文件上傳到騰訊云對象存儲服務(wù)器 (方法詳細見下面代碼)
<3> 將服務(wù)器返回的url存儲到數(shù)據(jù)庫 (方法詳細見下面代碼)
/**
* 周報年報生成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 += "時間段內(nèi)";
reportName += "案場階段報";
}
report.setReportName(reportName);
// 聲明一個工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一個表格
HSSFSheet sheet = workbook.createSheet(report.getReportName() + report.getStartTime() + " - " + report.getEndTime());
// 設(shè)置表格默認列寬度為100個字節(jié)
sheet.setDefaultColumnWidth((short) 100);
/** ----------樣式一：標題 ------------ **/
HSSFCellStyle style = workbook.createCellStyle();
// 設(shè)置這些樣式
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);
// 把字體應(yīng)用到當前的樣式
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);
// 把字體應(yīng)用到當前的樣式
style2.setFont(font2);
/*** 樣式三：右側(cè)日期 ***/
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);
/** 樣式四：主內(nèi)容 ***/
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);
/** 樣式五：底側(cè)空內(nèi)容 ***/
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));
// 設(shè)置注釋內(nèi)容
comment.setString(new HSSFRichTextString("數(shù)據(jù)報"));
// 設(shè)置注釋作者，當鼠標移動到單元格上是可以在狀態(tài)欄中看到該內(nèi)容.
comment.setAuthor("saas");
// 產(chǎn)生表格標題行 -- 項目名稱
HSSFRow row = sheet.createRow(0);
createCellAndRow(style4, report.getProjectName(), row);
// 產(chǎn)生表格標題行 -- 周報名稱
row = sheet.createRow(1);
createCellAndRow(style, report.getReportName(), row);
// 產(chǎn)生表格標題行 -- 起始時間-終止時間
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);
// 接訪客戶組數(shù)
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 + "%，來訪轉(zhuǎn)儲客的概率";
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 + "%，顯示老客戶關(guān)注度";
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);
// 新增認購套數(shù)
row = sheet.createRow(15);
Integer subscribeHouseCount = report.getSubscribeHouseCount();
Double subscribeHouseRate = new Double(report.getSubscribeHouseRate());
String subscribeHouseStr = "1、" + dateStr + "新增認購套數(shù)" + 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);
// 新增簽約套數(shù)
row = sheet.createRow(17);
Integer signCount = report.getSignCount();
Double signRate = new Double(report.getSignRate());
String signStr = "2、新增簽約套數(shù)" + 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 += "尚可，一定數(shù)量退訂或拒簽";
} else if (contratCuSignedRate >= 97 && contratCuSignedRate <= 99) {
contratCuSignedStr += "很高";
} else if (contratCuSignedRate > 99) {
contratCuSignedStr += "非常高";
}
createCellAndRow(style4, contratCuSignedStr, row);
//空行
row = sheet.createRow(23);
createCellAndRow(style4, "", row);
//底側(cè)
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){
// 設(shè)置用戶屬性, 包括appid, secretId和SecretKey
// 這些屬性可以通過cos控制臺獲取(https://console.qcloud.com/cos)
String version = PropertiesUtil.getValue("version");
long appId = "你的appId";
String secretId = "你的secretId ";
String secretKey = "你的secretKey ";
// 設(shè)置要操作的bucket
String bucketName = "root";
// 初始化客戶端配置
ClientConfig clientConfig = new ClientConfig();
// 設(shè)置bucket所在的區(qū)域，比如廣州(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下的根分區(qū)下,并命名為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;
}
生成的文件示例
周報或者其他報告都是后臺自動根據(jù)時間進行判斷的
周報
這里寫圖片描述
季報
這里寫圖片描述
以上
微信公眾號
關(guān)注我的微信公眾號：CodeD
不定期分享新的技術(shù)知識
微信公眾號
