@RequestMapping(value="aa",method = RequestMethod.GET)
@ResponseBody
public ResponseWrapper aa(HttpServletResponse response,
){
//获取平台Id
String platFrom = SecurityUtils.getSubject().getSession().getAttribute("platFromId").toString();
try {
File file = buildingService.rentReport(platFrom,year,month);
FileInputStream stream = new FileInputStream(file);
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
String fileName = new String(file.getName().getBytes("gb2312"), "ISO8859-1");
response.setHeader("Content-Disposition", "attachment; fileName=" + fileName);
OutputStream os = response.getOutputStream();
byte[] b = new byte[2048];
int length;
while ((length = stream.read(b)) > 0) {
os.write(b, 0, length);
}
logger.info("生成报表成功");
return ResponseWrapper.markSuccess(null,"下载成功");
}catch (Exception ex){
logger.info("======>报表下载出错");
ex.printStackTrace();
return ResponseWrapper.markError("下载失败");
}
}
public File rentReport(String platFrom, Integer year, Integer month) {
LocalDate localDate = LocalDate.now();
//模板路径
String excelPath = this.getClass().getResource("/").getPath()+"template/rentReport.xls";
//处理兼容性问题
Workbook workbook=null;
try{
workbook =new HSSFWorkbook(new FileInputStream(new File(excelPath)));
}catch(Exception e){
try{
workbook=new XSSFWorkbook(new FileInputStream(new File(excelPath)));
}catch(Exception ex){
ex.printStackTrace();
}
}
//设置单元格格式
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
Sheet sheet = workbook.getSheetAt(0);
Row row = null;
Cell cell = null;
//处理数据
Page<Building> page = new Page<>();
page.setPageNum(1);
page.setPageSize(99999);
Page<BuildingFloor> page2 = new Page<>();
page2.setPageNum(1);
page2.setPageSize(99999);
//平台下楼宇列表
List<Building> list = buildingDao.getBuildingList(platFrom,page);
try {
BuildingFloor buildingFloor = new BuildingFloor();
for (int i = 0,length = list.size();i < length;i ++) {
Building building = list.get(i);
row = sheet.createRow(i+2);
//楼宇详情
Building buildingDetail = this.getBuildingDetail(building.getId());
buildingFloor.setBuildingId(building.getId());
//楼层详情
List<BuildingFloor> buildingFloors = buildingFloorService.findByPage(buildingFloor, page2).getResults();
boolean floorFlag = CollectionUtils.isEmpty(buildingFloors) ? false : true;
//序号
cell = row.createCell(0);
cell.setCellValue(i+1);
cell.setCellStyle(style);
//园区
cell = row.createCell(1);
LabelZone zone = labelZoneDao.getZoneByPlatfromAndZoneId(platFrom, buildingDetail.getZoneId() + "");
cell.setCellValue(zone.getName());
cell.setCellStyle(style);
//楼宇名称
cell = row.createCell(2);
cell.setCellValue(buildingDetail.getBuildingName());
cell.setCellStyle(style);
//面积 = 各层测绘面积之和
cell = row.createCell(3);
if(floorFlag){
Double surveyDrawSum = buildingFloors.stream().mapToDouble(buildingFloor1->{
double surveyDrawArea = buildingFloor1.getSurveyDrawArea() == null ? 0.00 : buildingFloor1.getSurveyDrawArea().doubleValue();
return surveyDrawArea;
}).sum();
cell.setCellValue(surveyDrawSum);
}
cell.setCellStyle(style);
//单价
cell = row.createCell(4);
cell.setCellValue(buildingDetail.getRentoutPrice());
cell.setCellStyle(style);
//整体租金 = 每层单价*每层测绘面积之和*12
Double allSum = 0.00;
cell = row.createCell(5);
if(floorFlag){
allSum = buildingFloors.stream().mapToDouble(buildingFloor1 -> {
double surveyDrawArea = buildingFloor1.getSurveyDrawArea() == null ? 0.00 : buildingFloor1.getSurveyDrawArea().doubleValue();
double rentPrice = buildingFloor1.getRentoutPrice() == null ? 0.00 : buildingFloor1.getRentoutPrice().doubleValue();
return surveyDrawArea*rentPrice*12;
}).sum();
cell.setCellValue(allSum);
}
cell.setCellStyle(style);
//应收租金 当年应收租金
cell = row.createCell(6);
Double needSum = buildingDetail.getReceivableRental() == null ? 0.00 : buildingDetail.getReceivableRental().doubleValue();
cell.setCellValue(needSum);
cell.setCellStyle(style);
//空置面积租金 = 每层空置面积*每层单价*12
cell = row.createCell(7);
if(floorFlag){
Double sum = buildingFloors.stream().mapToDouble(buildingFloor1 -> {
double empty = StringUtil.isEmpty(buildingFloor1.getVacantArea()) ? 0.00 : Double.valueOf(buildingFloor1.getVacantArea());
double rentPrice = buildingFloor1.getRentoutPrice() == null ? 0.00 : buildingFloor1.getRentoutPrice().doubleValue();
return empty*rentPrice*12;
}).sum();
cell.setCellValue(sum);
}
cell.setCellStyle(style);
//租金收益比 = 应收租金/整体租金
String percent = FormulaUtil.ADivideBPercent(new BigDecimal(needSum), new BigDecimal(allSum));
cell = row.createCell(8);
cell.setCellValue(percent);
cell.setCellStyle(style);
}
String dirPath=ReadConfig.UPLOAD_PATH;
//.xls后缀的文件
String filePath=dirPath+"aa报表-"+localDate+"-"+System.currentTimeMillis()+".xls";
File file1=new File(filePath);
FileOutputStream fout=FileUtils.openOutputStream(file1);
workbook.write(fout);
fout.close();
return file1;
}catch (Exception ex){
ex.printStackTrace();
return null;
}
}