参考文章：(327条消息) Excel树状数据绘制导出_excel导出树形结构_Deja-vu xxl的博客-CSDN博客https://blog.csdn.net/weixin_45873182/article/details/120132409?spm=1001.2014.3001.5502
@Override
public Integer exportPlus(String yearMonth, HttpServletRequest request ,HttpServletResponse response) throws IOException {
String fileName = "周进度填报";
ServletOutputStream out = response.getOutputStream();
HSSFWorkbook workbook = new HSSFWorkbook();
String projectId = tokenService.getProjectId(request);
List<SysDept> deptIdsByProjectId = sysDeptMapper.getDeptIdsByPeojectId(Long.valueOf(projectId));
List<SysDept> collect = deptIdsByProjectId.stream().filter(s -> s.getEmail() == null).collect(Collectors.toList());
int size = collect.size();
if(size > 0){
for (int i = 0; i < size; i++) {
SysDept sysDept = collect.get(i);
String email = sysDept.getEmail();
if (email == null){
exportPlusByProject(sysDept.getDeptId(),yearMonth,workbook, i, sysDept.getDeptName(), out);
}
}
}
//创建统计sheet
exportStatistics(yearMonth,workbook, size);
FileUtil.downloadExcelHSSFWorkbook(fileName, response, workbook);
return 0;
}
public void exportPlusByProject(Long zhgdDeptId,String yearMonth,HSSFWorkbook workbook, int sheetNum,String sheetTitle,
ServletOutputStream out) {
/**
* 查询数据库中所有的数据
* 后期可以更换为根据id查询
* 查询本月的数据数据
*/
//存放数据的二维集合，twoDimensional 中每个List是树状结构的一个分支的所有数据
List<List<JSONObject>> twoDimensional = new ArrayList<>();
//------------------------------表头-----------------------------------
//keys 是表头的字段
List<String> keys = new ArrayList<>();
List<String> keys2 = new ArrayList<>();
//*************************样式*****************************
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//居中
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font headerFont = workbook.createFont();
// 设置字体
headerFont.setFontName("黑体");
// 设置字体大小
headerFont.setFontHeightInPoints((short) 13);
// 字体加粗
headerFont.setBold(false);
// 斜体
headerFont.setItalic(false);
// 字体颜色
headerFont.setColor(IndexedColors.BLACK.getIndex());
//设置字体高度
headerFont.setFontHeightInPoints((short) 13);
Font font = workbook.createFont();
// 设置字体
font.setFontName("方正小标宋简体");
// 设置字体大小
font.setFontHeightInPoints((short) 16);
// 字体加粗
font.setBold(true);
// 斜体
font.setItalic(false);
// 字体颜色
font.setColor(IndexedColors.BLACK.getIndex());
//设置字体高度
font.setFontHeightInPoints((short) 16);
//*************************样式*****************************
//tier 是动态项目清单的总层级
Integer tier = 3;
Integer headNum = 3;
//创建对象
//创建工作表
Sheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
sheet.setDefaultColumnWidth(36);
short s1 = 2 * 360 ;
sheet.setDefaultRowHeight(s1);
CellStyle styleTltles = workbook.createCellStyle();
styleTltles.setFillForegroundColor(IndexedColors.GREEN.getIndex());
//下边框
styleTltles.setBorderBottom(BorderStyle.THIN);
//左边框
styleTltles.setBorderLeft(BorderStyle.THIN);
//上边框
styleTltles.setBorderTop(BorderStyle.THIN);
//右边框
styleTltles.setBorderRight(BorderStyle.THIN);
//自动换行
styleTltles.setWrapText(true);
String[] split = yearMonth.split("-");
//columnMap 是一个对象，转换成Json
//*****************************下面数据请求数据库***********************
LambdaQueryWrapper<ZhgdWeeklyProgressReport> weeklyProgressReportLambdaQueryWrapper = new LambdaQueryWrapper<>();
weeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getCurrentDates,yearMonth)
.eq(ZhgdWeeklyProgressReport::getZhgdDeptId,zhgdDeptId)
.isNotNull(ZhgdWeeklyProgressReport::getFillingTime)
.groupBy(ZhgdWeeklyProgressReport::getFillingTime)
.orderByAsc(ZhgdWeeklyProgressReport::getFillingTime)
.select(ZhgdWeeklyProgressReport::getFillingTime);
List<ZhgdWeeklyProgressReport> weeklyProgressReportList = weeklyProgressReportService.list(weeklyProgressReportLambdaQueryWrapper);
HashMap<String, Object> columnMap = new HashMap<>(5);
HashMap<String, Object> columnMap1 = new HashMap<>(5);
AtomicReference<Integer> size = new AtomicReference<>(0);
if (weeklyProgressReportList.size() > 0){
columnMap.put("单价(元)","单价(元)");
columnMap1.put("单价(元)","单价(元)");
keys.add("单价(元)");
keys2.add("单价(元)");
columnMap.put("总量","总量");
columnMap1.put("总量","总量");
keys.add("总量");
keys2.add("总量");
if (weeklyProgressReportList.size()>0){
weeklyProgressReportList.stream().forEach(entity->{
String fillingTime = entity.getFillingTime();
String[] split1 = fillingTime.split("-");
String s = split1[0]+"月"+ split1[1]+ "日完成工程量";
String s2 = split[1] + "月份完成工程量";
keys2.add(s2);
columnMap.put(s,s);
columnMap1.put(s2,s2);
keys.add(s);
});
}
columnMap.put("完成工程量合计","完成工程量合计");
columnMap1.put("完成工程量合计","完成工程量合计");
keys.add("完成工程量合计");
keys2.add(split[1]+"月份完成工程量");
if (weeklyProgressReportList.size()>0){
weeklyProgressReportList.stream().forEach(entity->{
String fillingTime = entity.getFillingTime();
String[] split1 = fillingTime.split("-");
String s = split1[0]+"月"+ split1[1]+ "月完成产值（万元）";
String s2 = split[1] + "月完成产值（万元）";
keys2.add(s2);
columnMap.put(s,s);
columnMap1.put(s2,s2);
keys.add(s);
});
}
columnMap.put("完成产值（万元）合计","完成产值（万元）合计");
columnMap1.put("完成产值（万元）合计","完成产值（万元）合计");
keys.add("完成产值（万元）合计");
keys2.add(split[1]+"月完成产值（万元）");
columnMap.put("截止上月累计完成工程量","截止上月累计完成工程量");
columnMap1.put("截止上月累计完成工程量","截止上月累计完成工程量");
keys.add("截止上月累计完成工程量");
keys2.add("截止上月累计完成工程量");
columnMap.put("截止上月累计完成产值","截止上月累计完成产值");
columnMap1.put("截止上月累计完成产值","截止上月累计完成产值");
keys.add("截止上月累计完成产值");
keys2.add("截止上月累计完成产值");
columnMap.put("截止本月累计完成工程量","截止本月累计完成工程量");
columnMap1.put("截止本月累计完成工程量","截止本月累计完成工程量");
keys.add("截止本月累计完成工程量");
keys2.add("截止本月累计完成工程量");
columnMap.put("累计完成总量占比","累计完成总量占比");
columnMap1.put("累计完成总量占比","累计完成总量占比");
keys.add("累计完成总量占比");
keys2.add("累计完成总量占比");
columnMap.put("截止本月累计完成产值（万元）","截止本月累计完成产值（万元）");
columnMap1.put("截止本月累计完成产值（万元）","截止本月累计完成产值（万元）");
keys.add("截止本月累计完成产值（万元）");
keys2.add("截止本月累计完成产值（万元）");
//***********************************正文数据查询**********************************
ZhgdWeeklyProgressReport zhgdWeeklyProgressReport = new ZhgdWeeklyProgressReport();
zhgdWeeklyProgressReport.setZhgdDeptId(zhgdDeptId);
zhgdWeeklyProgressReport.setCurrentDates(yearMonth);
zhgdWeeklyProgressReport.setIsExists(2);
ZhgdWeeklyProgressReport zhgdWeeklyProgressReport1 = weeklyProgressReportService.getlastMonthData(zhgdWeeklyProgressReport);
String fillingTime1 = zhgdWeeklyProgressReport1.getFillingTime();
LambdaQueryWrapper<ZhgdWeeklyProgressReport> ZhgdWeeklyProgressReportWrapper = new LambdaQueryWrapper<>();
ZhgdWeeklyProgressReportWrapper.eq(ZhgdWeeklyProgressReport::getZhgdDeptId,zhgdDeptId)
.eq(ZhgdWeeklyProgressReport::getCurrentDates,yearMonth)
.eq(ZhgdWeeklyProgressReport::getFillingTime,fillingTime1)
.eq(ZhgdWeeklyProgressReport::getIsExists,2)
.orderByAsc(ZhgdWeeklyProgressReport::getParentId);
List<ZhgdWeeklyProgressReport> list2 = weeklyProgressReportService.list(ZhgdWeeklyProgressReportWrapper);
if (list2.size()>0){
String finalYearMonth = yearMonth;
String finalYearMonth1 = yearMonth;
String finalYearMonth2 = yearMonth;
list2.stream().forEach(entity->{
//本月完成工程量总和
AtomicReference<BigDecimal> completedQuantitiesTotal = new AtomicReference<>(new BigDecimal(0));
//本月完成产值总和
AtomicReference<BigDecimal> completedOutputValueTotal = new AtomicReference<>(new BigDecimal(0));
HashMap<String, Object> map = new HashMap<>();
map.put("dictLabel",entity.getUnitName());
map.put("单价(元)",entity.getUnitPrice());
map.put("总量",entity.getTotalNum());
if (weeklyProgressReportList.size() > 0){
weeklyProgressReportList.stream().forEach(one->{
String fillingTime = one.getFillingTime();
// String s = fillingTime + "日完成工程量";
String[] split1 = fillingTime.split("-");
String s = split1[0]+"月"+ split1[1]+ "日完成工程量";
LambdaQueryWrapper<ZhgdWeeklyProgressReport> zhgdWeeklyProgressReportLambdaQueryWrapper = new LambdaQueryWrapper<>();
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getFillingTime,fillingTime);
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getZhgdDeptId,zhgdDeptId);
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getCurrentDates, finalYearMonth);
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getMainId,entity.getMainId());
List<ZhgdWeeklyProgressReport> list = weeklyProgressReportService.list(zhgdWeeklyProgressReportLambdaQueryWrapper);
BigDecimal completedQuantities = null;
if(list.size() >0 ){
completedQuantities = list.get(0).getCompletedQuantities();
}
if (completedQuantities == null){
completedQuantities = new BigDecimal(0);
}
BigDecimal finalCompletedQuantities = completedQuantities;
completedQuantitiesTotal.updateAndGet(v -> v.add(finalCompletedQuantities));
map.put(s,completedQuantities);
});
}
map.put("完成工程量合计",completedQuantitiesTotal);
if (weeklyProgressReportList.size() > 0){
weeklyProgressReportList.stream().forEach(one->{
String fillingTime = one.getFillingTime();
String[] split1 = fillingTime.split("-");
String s = split1[0]+"月"+ split1[1]+ "月完成产值（万元）";
LambdaQueryWrapper<ZhgdWeeklyProgressReport> zhgdWeeklyProgressReportLambdaQueryWrapper = new LambdaQueryWrapper<>();
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getFillingTime,fillingTime);
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getZhgdDeptId,zhgdDeptId);
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getCurrentDates, finalYearMonth1);
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getMainId,entity.getMainId());
List<ZhgdWeeklyProgressReport> list = weeklyProgressReportService.list(zhgdWeeklyProgressReportLambdaQueryWrapper);
BigDecimal completedOutputValue = null;
if (list.size() >0){
completedOutputValue = list.get(0).getCompletedOutputValue();
}
if (completedOutputValue == null){
completedOutputValue = new BigDecimal(0);
}
BigDecimal finalCompletedOutputValue = completedOutputValue;
completedOutputValueTotal.updateAndGet(v -> v.add(finalCompletedOutputValue));
map.put(s,completedOutputValue);
});
}
map.put("完成产值（万元）合计",completedOutputValueTotal);
map.put("截止上月累计完成工程量",entity.getLastMonthWork());
map.put("截止上月累计完成产值",entity.getLastMonthPrice());
map.put("截止本月累计完成工程量",entity.getMonthQuantity());
map.put("累计完成总量占比",entity.getCompletionPercentage()+"%");
map.put("截止本月累计完成产值（万元）",entity.getMonthOutputValue());
size.set(map.size());
//查询entity 的上级
LambdaQueryWrapper<ZhgdWeeklyProgressReport> zhgdWeeklyProgressReportLambdaQueryWrapper = new LambdaQueryWrapper<>();
zhgdWeeklyProgressReportLambdaQueryWrapper.eq(ZhgdWeeklyProgressReport::getZhgdDeptId,zhgdDeptId)
.eq(ZhgdWeeklyProgressReport::getCurrentDates, finalYearMonth2)
.eq(ZhgdWeeklyProgressReport::getMainId,entity.getParentId());
List<ZhgdWeeklyProgressReport> list3 = weeklyProgressReportService.list(zhgdWeeklyProgressReportLambdaQueryWrapper);
ZhgdWeeklyProgressReport byId = new ZhgdWeeklyProgressReport();
if(list3.size() > 0){
byId = list3.get(0);
}
if (byId == null){
//如果entity.parentId 为0，则为一层级数据
HashMap<String, Object> stringObjectHashMap11 = new HashMap<>();
stringObjectHashMap11.put("dictLabel",entity.getUnitName());
HashMap<String, Object> stringObjectHashMap12 = new HashMap<>();
stringObjectHashMap12.put("dictLabel",entity.getUnitName());
HashMap<String, Object> stringObjectHashMap13 = new HashMap<>();
stringObjectHashMap13.put("dictLabel",entity.getUnitName());
ArrayList<JSONObject> list1 = new ArrayList<>();
list1.add(new JSONObject(map));
list1.add(new JSONObject(stringObjectHashMap12));
list1.add(new JSONObject(stringObjectHashMap13));
twoDimensional.add(list1);
} else if (!byId.getParentId().equals(0L)){
//如果父类不为0,就说明上面还有层级，
LambdaQueryWrapper<ZhgdWeeklyProgressReport> weeklyProgressReportQueryWrapper = new LambdaQueryWrapper<>();
weeklyProgressReportQueryWrapper.eq(ZhgdWeeklyProgressReport::getZhgdDeptId,zhgdDeptId)
.eq(ZhgdWeeklyProgressReport::getCurrentDates, finalYearMonth2)
.eq(ZhgdWeeklyProgressReport::getMainId,String.valueOf(byId.getParentId()));
List<ZhgdWeeklyProgressReport> list = weeklyProgressReportService.list(weeklyProgressReportQueryWrapper);
HashMap<String, Object> stringObjectHashMap11 = new HashMap<>();
HashMap<String, Object> stringObjectHashMap12 = new HashMap<>();
if (list.size() >0){
stringObjectHashMap11.put("dictLabel",byId.getUnitName());
stringObjectHashMap12.put("dictLabel",list.get(0).getUnitName());
}
ArrayList<JSONObject> list1 = new ArrayList<>();
list1.add(new JSONObject(map));
list1.add(new JSONObject(stringObjectHashMap11));
list1.add(new JSONObject(stringObjectHashMap12));
twoDimensional.add(list1);
}else if(byId.getParentId().equals(0L)){
//如果父类直接为0，说明是个二级分类
HashMap<String, Object> stringObjectHashMap11 = new HashMap<>();
stringObjectHashMap11.put("dictLabel",entity.getUnitName());
HashMap<String, Object> stringObjectHashMap12 = new HashMap<>();
stringObjectHashMap12.put("dictLabel",byId.getUnitName());
ArrayList<JSONObject> list1 = new ArrayList<>();
list1.add(new JSONObject(map));
list1.add(new JSONObject(stringObjectHashMap11));
list1.add(new JSONObject(stringObjectHashMap12));
twoDimensional.add(list1);
}
});
}
}
//*******************************正文数据查询结束*******************************
Row row1 = sheet.createRow(0);
Cell cell1 = row1.createCell(0);
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, size.get()+headNum);
sheet.addMergedRegionUnsafe(region1);
cell1.setCellValue("徐淮阜高速阜阳段"+split[0]+"年度"+split[1]+"月份进度完成情况（"+sheetTitle+"）");
cell1.setCellStyle(style);
CellUtil.setAlignment(cell1, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell1, VerticalAlignment.CENTER);
CellUtil.setFont(cell1,font);
// 3.设置合并单元格边框
setBorderStyle(sheet, region1);
//动态创建首行表头
//查询各个月日的完成产值和合计
Row firstRow = sheet.createRow(2);
JSONObject cm = new JSONObject(columnMap);
for (int i = 0; i < tier+keys.size(); i++) {
Cell cell = firstRow.createCell(i);
if (i<tier){
String s="一";
switch (i){
case 1 : s="二";break;
case 2 : s="三";break;
}
cell.setCellValue("项目名称");
cell.setCellStyle(style);
}else {
cell.setCellValue(cm.getString(keys.get(i - tier)));
cell.setCellStyle(style);
}
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell, headerFont);
}
Row firstRow1 = sheet.createRow(1);
JSONObject cm1 = new JSONObject(columnMap1);
for (int i = 0; i < tier+keys2.size(); i++) {
Cell cell = firstRow1.createCell(i);
if (i<tier){
String s="一";
switch (i){
case 1 : s="二";break;
case 2 : s="三";break;
}
cell.setCellValue("项目名称");
cell.setCellStyle(style);
}else {
cell.setCellValue(cm1.getString(keys2.get(i - tier)));
cell.setCellStyle(style);
}
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell, headerFont);
}
//动态绘制数据，tier是层级数，根据业务最多三级
for (int i = 0; i < twoDimensional.size(); i++) {
Row row = sheet.createRow(i+headNum);
List<JSONObject> list = twoDimensional.get(i);
for (int j = 0; j < keys.size()+tier; j++) {
Cell cell = row.createCell(j);
if ( j < tier){
//绘制层级标签
if (j==0){
JSONObject jsonObject = list.get(tier - 1);
cell.setCellValue(jsonObject.getString("dictLabel"));
cell.setCellStyle(style);
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell, headerFont);
continue;
}
if (j==1){
Map map = list.get(tier - 2);
cell.setCellValue((String)map.get("dictLabel"));
cell.setCellStyle(style);
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell, headerFont);
continue;
}
if (j==2){
Map map = list.get(tier - 3);
cell.setCellValue((String)map.get("dictLabel"));
cell.setCellStyle(style);
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell, headerFont);
continue;
}
}else {
//绘制层级的数据
JSONObject bean = new JSONObject(list.get(0));
cell.setCellValue(bean.getString(keys.get(j - tier)));
cell.setCellStyle(style);
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell, headerFont);
}
}
}
com.gexin.fastjson.JSONObject topBean = queryValueSum(weeklyProgressReportList, yearMonth, zhgdDeptId);
//添加总合计行，并合并单元格
Row totalRow = sheet.createRow(twoDimensional.size()+headNum);
for (int j = 0; j < keys.size()+tier; j++) {
Cell cell = totalRow.createCell(j);
if (j<tier){
cell.setCellValue("产值完成情况（万元）");
cell.setCellStyle(style);
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell, headerFont);
}else {
cell.setCellValue(topBean.getString(keys.get(j - tier)));
cell.setCellStyle(style);
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell, headerFont);
}
}
if (tier != 1){
sheet.addMergedRegionUnsafe(new CellRangeAddress(twoDimensional.size()+headNum,twoDimensional.size()+headNum,0,tier-1));
}
//合并相同的单元格
int lastRowNum = sheet.getLastRowNum();
int index = 0;
//根据业务只有标签这几列需要合并
for (int i = 0; i < tier-1; i++) { //tier-1
//比较相邻cell的值是否相同，并记录
for (int j = 3; j < lastRowNum; j++) {
Row row = sheet.getRow(j);
Cell cell = row.getCell(i);
Cell cell2 = row.getCell(i+1);
String stringCellValue = cell.getStringCellValue();
String stringCellValueNextRow = cell2.getStringCellValue();
if (stringCellValue != null && "合计".equals(stringCellValue)){
sheet.addMergedRegionUnsafe(new CellRangeAddress(j,j,i,i+1));
continue;
}
Row nextRow = sheet.getRow(j+1);
Cell nextcell = nextRow.getCell(i);
String nextStringCellValue = nextcell.getStringCellValue();
if (stringCellValue != null && stringCellValue.equals(nextStringCellValue)){
if (j+1 == lastRowNum){
if (index != 0){
sheet.addMergedRegionUnsafe(new CellRangeAddress(j-index,j+1,i,i));
index = 0;
}
}else {
index++;
}
}else {
if (index != 0){
sheet.addMergedRegionUnsafe(new CellRangeAddress(j-index,j,i,i));
index = 0;
}
}
//横向合并
// row-- 相邻的如果相同合并
if (stringCellValueNextRow != null && stringCellValue !=null && stringCellValueNextRow.equals(stringCellValue) && j != 0 ){
sheet.addMergedRegionUnsafe(new CellRangeAddress(j,j,i,i+1));
}
}
}
int index2 = 0;
for (int i = 0; i < keys2.size()+tier ; i++) { //tier-1 size.get()+1
//比较相邻cell的值是否相同，并记录
for (int j = 1; j < 3; j++) {
Row row = sheet.getRow(j);
Cell cell = row.getCell(i);
String stringCellValue = cell.getStringCellValue();
String stringCellValueNextRow = null;
Cell cell2 = row.getCell(i+1);
if (i+1 < keys2.size()+tier){
stringCellValueNextRow = cell2.getStringCellValue();
}
//横向合并
// row-- 相邻的如果相同合并
if (stringCellValueNextRow != null && stringCellValue !=null && stringCellValueNextRow.equals(stringCellValue) && j != 0 ){
sheet.addMergedRegionUnsafe(new CellRangeAddress(j,j,i,i+1));
}
if (stringCellValue != null && "合计".equals(stringCellValue)){
sheet.addMergedRegionUnsafe(new CellRangeAddress(j,j,i,i+1));
continue;
}
Row nextRow = sheet.getRow(j+1);
Cell nextcell = nextRow.getCell(i);
String nextStringCellValue = nextcell.getStringCellValue();
if (stringCellValue != null && stringCellValue.equals(nextStringCellValue)){
if (j+1 == lastRowNum){
if (index2 != 0){
sheet.addMergedRegionUnsafe(new CellRangeAddress(j-index2,j+1,i,i));
index2 = 0;
}
}else {
index2++;
}
}else {
if (index2 != 0){
sheet.addMergedRegionUnsafe(new CellRangeAddress(j-index2,j,i,i));
index2 = 0;
}
}
}
}
styleTltles.setAlignment(HorizontalAlignment.CENTER);
styleTltles.setVerticalAlignment(VerticalAlignment.CENTER);
}
//创建统计sheet
public void exportStatistics(String yearMonth,HSSFWorkbook workbook, int sheetNum){
Sheet sheet = workbook.createSheet();
sheet.setDefaultRowHeight((short) 700);
sheet.setDefaultColumnWidth(25);
workbook.setSheetName(sheetNum, "产值统计");
// 设置单元格字体
Font headerFont = workbook.createFont();
headerFont.setFontName("宋体"); // 设置字体
headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
headerFont.setBold(true); // 字体加粗
headerFont.setItalic(false);// 斜体
headerFont.setColor(IndexedColors.BLACK.getIndex()); // 字体颜色
headerFont.setFontHeightInPoints((short)22);//设置字体高度
// 设置单元格字体
Font contextFont = workbook.createFont();
contextFont.setFontName("宋体"); // 设置字体
contextFont.setFontHeightInPoints((short) 12); // 设置字体大小
contextFont.setBold(true); // 字体加粗
contextFont.setItalic(false);// 斜体
contextFont.setColor(IndexedColors.BLACK.getIndex()); // 字体颜色
contextFont.setFontHeightInPoints((short)12);//设置字体高度
// 1.创建一个合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
sheet.addMergedRegion(region);
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//居中
style.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 2.设置合并单元格内容
Cell cell = sheet.createRow(0).createCell(0);
cell.setCellStyle(style);
String[] split = yearMonth.split("-");
List<String> stringList = Arrays.asList(split);
cell.setCellValue("徐淮阜高速阜阳段"+stringList.get(0)+"年度"+stringList.get(1)+"月份产值汇总");
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
CellUtil.setFont(cell,headerFont);
// 3.设置合并单元格边框
setBorderStyle(sheet, region);
Row row1 = sheet.createRow(1);
Cell cell8 = row1.createCell(0);
Cell cell9 = row1.createCell(1);
Cell cell10 = row1.createCell(2);
Cell cell11 = row1.createCell(3);
cell8.setCellValue("序号");
cell9.setCellValue("标段");
cell10.setCellValue("本月完成产值（万元）");
cell11.setCellValue("累计完成产值（万元）");
cell8.setCellStyle(style);
cell9.setCellStyle(style);
cell10.setCellStyle(style);
cell11.setCellStyle(style);
CellUtil.setFont(cell8,contextFont);
CellUtil.setFont(cell9,contextFont);
CellUtil.setFont(cell10,contextFont);
CellUtil.setFont(cell11,contextFont);
//*********************本月完成产值***********************
List<String> list = new ArrayList();
list.add(xhfDeptOneId);
list.add(xhfDeptOnetId);
String completeMonth = weeklyProgressReportMapper.completeMonth(yearMonth,list,null);
if (completeMonth == null){
completeMonth = "0";
}
List<String> twoSecList = new ArrayList();
twoSecList.add(xhfDeptTwoId);
twoSecList.add(xhfDeptTwotId);
String twoSecCompleteMonth = weeklyProgressReportMapper.completeMonth(yearMonth,twoSecList,null);
if (twoSecCompleteMonth == null){
twoSecCompleteMonth = "0";
}
BigDecimal bigDecimal = new BigDecimal(completeMonth);
BigDecimal twoSecBigDecimal = new BigDecimal(twoSecCompleteMonth);
BigDecimal completeMonthSum = bigDecimal.add(twoSecBigDecimal);
completeMonthSum = completeMonthSum.setScale(2, BigDecimal.ROUND_HALF_UP);
//****************************累计完成产值（万元）*******************************************
ZhgdWeeklyProgressReport weeklyProgressReport1 = new ZhgdWeeklyProgressReport();
weeklyProgressReport1.setCurrentDates(yearMonth);
weeklyProgressReport1.setIsExists(2);
ZhgdWeeklyProgressReport weeklyProgressReport2 = weeklyProgressReportMapper.getlastMonthData(weeklyProgressReport1);
String fillingTime = null;
if (Optional.ofNullable(weeklyProgressReport2).isPresent()){
fillingTime = weeklyProgressReport2.getFillingTime();
}
String oneSecCompletedOutputValue = weeklyProgressReportMapper.completedOutputValue(yearMonth,list,fillingTime);
String twoSecCompletedOutputValue = weeklyProgressReportMapper.completedOutputValue(yearMonth,twoSecList,fillingTime);
BigDecimal oneSecCumulativeValue = new BigDecimal(oneSecCompletedOutputValue);
BigDecimal twoSecCumulativeValue = new BigDecimal(twoSecCompletedOutputValue);
BigDecimal cumulativeValueSum = oneSecCumulativeValue.add(twoSecCumulativeValue);
cumulativeValueSum = cumulativeValueSum.setScale(2, BigDecimal.ROUND_HALF_UP);
//*********************累计完成产值（万元） ***********************
List<ZhgdWeeklyProgressReport> weeklyProgressReportList = weeklyProgressReportService.selectStatisticsData(yearMonth);
for (int i = 0; i < weeklyProgressReportList.size(); i++) {
ZhgdWeeklyProgressReport zhgdWeeklyProgressReport = weeklyProgressReportList.get(i);
row1 = sheet.createRow(i+2);
Cell cell0 = row1.createCell(0);
Cell cell1 = row1.createCell(1);
Cell cell2 = row1.createCell(2);
Cell cell3 = row1.createCell(3);
cell0.setCellValue(i+1);
if (i == 0 ){
cell1.setCellValue("施工01标");
cell2.setCellValue(completeMonth);
cell3.setCellValue(oneSecCompletedOutputValue);
} else if(i == 1){
cell1.setCellValue("施工02标");
cell2.setCellValue(twoSecCompleteMonth);
cell3.setCellValue(twoSecCompletedOutputValue);
} else if ( i== 2 ){
cell1.setCellValue("合计");
cell2.setCellValue(String.valueOf(completeMonthSum));
cell3.setCellValue(String.valueOf(cumulativeValueSum));
}
cell0.setCellStyle(style);
cell1.setCellStyle(style);
cell2.setCellStyle(style);
cell3.setCellStyle(style);
CellUtil.setFont(cell0,contextFont);
CellUtil.setFont(cell1,contextFont);
CellUtil.setFont(cell2,contextFont);
CellUtil.setFont(cell3,contextFont);
}
//项目清单数据统计
Row row = sheet.createRow(weeklyProgressReportList.size() + 3);
//设置合并单元格范围
CellRangeAddress cellAddresses = new CellRangeAddress(weeklyProgressReportList.size() + 3, weeklyProgressReportList.size() + 3, 0, 3);
sheet.addMergedRegion(cellAddresses);
//设置合并单元格内文本
Cell cell3 = sheet.createRow(weeklyProgressReportList.size() + 3).createCell(0);
cell3.setCellValue("数据统计");
setBorderStyle(sheet, cellAddresses);
CellUtil.setAlignment(cell3, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell3, VerticalAlignment.CENTER);
CellUtil.setFont(cell3,headerFont);
row = sheet.createRow(weeklyProgressReportList.size() + 4);
Cell cell4 = row.createCell(0);
Cell cell5 = row.createCell(1);
Cell cell6 = row.createCell(2);
Cell cell7 = row.createCell(3);
cell4.setCellValue("项目名称");
cell5.setCellValue("总量");
cell6.setCellValue("累计完成量");
cell7.setCellValue("占比");
cell4.setCellStyle(style);
cell5.setCellStyle(style);
cell6.setCellStyle(style);
cell7.setCellStyle(style);
CellUtil.setFont(cell4,contextFont);
CellUtil.setFont(cell5,contextFont);
CellUtil.setFont(cell6,contextFont);
CellUtil.setFont(cell7,contextFont);
Row row2 = sheet.createRow(weeklyProgressReportList.size() + 5);
// List<ZhgdWeeklyProgressReport> zhgdWeeklyProgressReportList = weeklyProgressReportService.selectStatistics(yearMonth);
// ************************************清表*******************************
String projectId = tokenService.getProjectId(ServletUtils.getRequest());
String unitName = "清表（㎡）";
String totalNum = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId,unitName);
if (totalNum == null){
totalNum = "0";
}
BigDecimal totalNumDecimal = new BigDecimal(totalNum);
BigDecimal bigDecimal1 = new BigDecimal(10000);
BigDecimal divide4 = totalNumDecimal.divide(bigDecimal1, 2, BigDecimal.ROUND_HALF_UP);
ZhgdWeeklyProgressReport weeklyProgressReport = new ZhgdWeeklyProgressReport();
weeklyProgressReport.setDeptId(Long.valueOf(projectId));
weeklyProgressReport.setUnitName(unitName);
weeklyProgressReport.setCurrentDates(yearMonth);
String cumulativeCompletions = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if ( cumulativeCompletions == null){
cumulativeCompletions = "0";
}
BigDecimal bigDecimal2 = new BigDecimal(cumulativeCompletions);
//此为清表的累计完成量
BigDecimal divide5 = bigDecimal2.divide(bigDecimal1, 2, BigDecimal.ROUND_HALF_UP);
//占比
BigDecimal divide = new BigDecimal("0");
if (!(divide4.compareTo(BigDecimal.ZERO) == 0)) {
BigDecimal divide1 = divide5.divide(divide4, 4, BigDecimal.ROUND_HALF_UP);
divide = new BigDecimal(String.valueOf(divide1));
}
row2 = sheet.createRow(0+weeklyProgressReportList.size() + 5);
Cell cell0 = row2.createCell(0);
Cell cell1 = row2.createCell(1);
Cell cell2 = row2.createCell(2);
Cell cell13 = row2.createCell(3);
cell0.setCellValue("清表（万m2）");
cell1.setCellValue(String.valueOf(divide4));
cell2.setCellValue(String.valueOf(divide5));
BigDecimal multiply = divide.multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP);
cell13.setCellValue(String.valueOf(multiply)+"%");
setStyle(cell0,style,contextFont);
setStyle(cell1,style,contextFont);
setStyle(cell2,style,contextFont);
setStyle(cell13,style,contextFont);
// ************************************清表*******************************
//*****************************便道（Km）******************************
// 便道总量计算
unitName = "便道便桥（延米）";
totalNum = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId,unitName);
if (totalNum == null){
totalNum = "0";
}
//便道总量：bdTotalNum
BigDecimal bdTotalNum = new BigDecimal(totalNum).divide(new BigDecimal(1000), 2, BigDecimal.ROUND_HALF_UP);
// 便道累计完成量计算
weeklyProgressReport.setUnitName(unitName);
String bdCumulativeCompletions = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if ( bdCumulativeCompletions == null){
bdCumulativeCompletions = "0";
}
BigDecimal bigDecimal3 = new BigDecimal(bdCumulativeCompletions);
// 便道 累计完成量:divide1
BigDecimal divide1 = bigDecimal3.divide(new BigDecimal(1000), 2, BigDecimal.ROUND_HALF_UP);
//便道占比:bigDecimal15
BigDecimal bigDecimal15 = divide1.divide(bdTotalNum,4,BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP);
row2 = sheet.createRow(1+weeklyProgressReportList.size() + 5);
cell0 = row2.createCell(0);
cell1 = row2.createCell(1);
cell2 = row2.createCell(2);
cell13 = row2.createCell(3);
cell0.setCellValue("便道（Km）");
cell1.setCellValue(String.valueOf(bdTotalNum));
cell2.setCellValue(String.valueOf(divide1));
cell13.setCellValue(String.valueOf(bigDecimal15)+"%");
setStyle(cell0,style,contextFont);
setStyle(cell1,style,contextFont);
setStyle(cell2,style,contextFont);
setStyle(cell13,style,contextFont);
//******************************便道结束*********************
//**********************土方（万方）************************
// 土方总量计算
unitName = "石灰土（万方）";
String totalNum1 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (totalNum1 == null){
totalNum1 = "0";
}
unitName = "3%石灰土回填";
String totalNum2 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
//土方总量
BigDecimal tfBigDecimal = new BigDecimal(totalNum1).add(new BigDecimal(totalNum2)).setScale(2,BigDecimal.ROUND_HALF_UP);
//土方累计完成量计算
weeklyProgressReport.setUnitName("石灰土（万方）");
String cumulativeCompletions1 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if ( cumulativeCompletions1 == null){
cumulativeCompletions1 = "0";
}
weeklyProgressReport.setUnitName("3%石灰土回填");
String cumulativeCompletions2 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if ( cumulativeCompletions2 == null){
cumulativeCompletions2 = "0";
}
//土方累计完成量：bigDecimal4
BigDecimal bigDecimal4 = new BigDecimal(cumulativeCompletions1).add(new BigDecimal(cumulativeCompletions2)).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal multiply1 = bigDecimal4.divide(tfBigDecimal,4,BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
row2 = sheet.createRow(2 + weeklyProgressReportList.size() + 5);
cell0 = row2.createCell(0);
cell1 = row2.createCell(1);
cell2 = row2.createCell(2);
cell13 = row2.createCell(3);
cell0.setCellValue("土方（万方）");
cell1.setCellValue(String.valueOf(tfBigDecimal));
cell2.setCellValue(String.valueOf(bigDecimal4));
cell13.setCellValue(String.valueOf(multiply1)+"%");
setStyle(cell0,style,contextFont);
setStyle(cell1,style,contextFont);
setStyle(cell2,style,contextFont);
setStyle(cell13,style,contextFont);
//**********************土方（万方）结束************************
//**********************小型结构物（道）***********************
unitName = "圆管涵";
String totalNum3 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (totalNum3 == null){
totalNum3 = "0";
}
//小型结构物（道） 累计完成量计算
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions3 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (!Optional.ofNullable(cumulativeCompletions3).isPresent()){
cumulativeCompletions3 = "0";
}
BigDecimal bigDecimal6 = new BigDecimal(cumulativeCompletions3);
unitName = "水箱涵";
weeklyProgressReport.setUnitName(unitName);
String totalNum4 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (totalNum4 == null){
totalNum4 = "0";
}
unitName = "人机箱";
String totalNum8 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum8).isPresent()){
totalNum8 = "0";
}
unitName = "现浇盖板（道）";
String totalNum9 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum9).isPresent()){
totalNum9 = "0";
}
//小型结构物总量:bigDecimal5
BigDecimal bigDecimal5 = new BigDecimal(totalNum3).add(new BigDecimal(totalNum4))
.add(new BigDecimal(totalNum8)).add(new BigDecimal(totalNum9)).setScale(2, BigDecimal.ROUND_HALF_UP);
//小型结构物（道） 累计完成量计算
BigDecimal divide2 = new BigDecimal("0");
if (!(bigDecimal5.compareTo(BigDecimal.ZERO) == 0)){
BigDecimal bigDecimal7 = bigDecimal6.divide(bigDecimal5,4,BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP);
divide2 = new BigDecimal(String.valueOf(bigDecimal7));
}
row2 = sheet.createRow(3 + weeklyProgressReportList.size() + 5);
cell0 = row2.createCell(0);
cell1 = row2.createCell(1);
cell2 = row2.createCell(2);
cell13 = row2.createCell(3);
cell0.setCellValue("小型结构物（道）");
cell1.setCellValue(String.valueOf(bigDecimal5));
cell2.setCellValue(String.valueOf(bigDecimal6));
cell13.setCellValue(String.valueOf(divide2)+"%");
setStyle(cell0,style,contextFont);
setStyle(cell1,style,contextFont);
setStyle(cell2,style,contextFont);
setStyle(cell13,style,contextFont);
//**********************小型结构物（道） 结束******************
//**********************桩基（根） ******************
//桩基（根） 01标（安徽恒桥）标计算
//1,计算总量
unitName = "1.0m";
String totalNum16 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum16).isPresent()){
totalNum16 = "0";
}
BigDecimal bigDecimal22 = new BigDecimal(totalNum16);
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions7 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (!Optional.ofNullable(cumulativeCompletions7).isPresent()){
cumulativeCompletions7 = "0";
}
BigDecimal bigDecimal23 = new BigDecimal(cumulativeCompletions7);
unitName = "1.3m";
String totalNum10 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum10).isPresent()){
totalNum10 = "0";
}
BigDecimal bigDecimal16 = new BigDecimal(totalNum10);
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions8 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (!Optional.ofNullable(cumulativeCompletions8).isPresent()){
cumulativeCompletions8 = "0";
}
BigDecimal bigDecimal24 = new BigDecimal(cumulativeCompletions8);
unitName = "1.4m";
String totalNum11 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum11).isPresent()){
totalNum11 = "0";
}
BigDecimal bigDecimal17 = new BigDecimal(totalNum11);
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions9 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (!Optional.ofNullable(cumulativeCompletions9).isPresent()){
cumulativeCompletions9 = "0";
}
BigDecimal bigDecimal25 = new BigDecimal(cumulativeCompletions9);
unitName = "1.5m";
String totalNum12 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum12).isPresent()){
totalNum12 = "0";
}
BigDecimal bigDecimal18 = new BigDecimal(totalNum12);
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions10 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (!Optional.ofNullable(cumulativeCompletions10).isPresent()){
cumulativeCompletions10 = "0";
}
BigDecimal bigDecimal26 = new BigDecimal(cumulativeCompletions10);
unitName = "1.6m";
String totalNum13 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum13).isPresent()){
totalNum13 = "0";
}
BigDecimal bigDecimal19 = new BigDecimal(totalNum13);
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions11 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (!Optional.ofNullable(cumulativeCompletions11).isPresent()){
cumulativeCompletions11 = "0";
}
BigDecimal bigDecimal27 = new BigDecimal(cumulativeCompletions11);
unitName = "1.8m";
String totalNum15 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum15).isPresent()){
totalNum15 = "0";
}
BigDecimal bigDecimal21 = new BigDecimal(totalNum15);
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions12 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (!Optional.ofNullable(cumulativeCompletions12).isPresent()){
cumulativeCompletions12 = "0";
}
BigDecimal bigDecimal28 = new BigDecimal(cumulativeCompletions12);
unitName = "2.0m";
String totalNum14 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (!Optional.ofNullable(totalNum14).isPresent()){
totalNum14 = "0";
}
BigDecimal bigDecimal20 = new BigDecimal(totalNum14);
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions13 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (!Optional.ofNullable(cumulativeCompletions13).isPresent()){
cumulativeCompletions13 = "0";
}
BigDecimal bigDecimal29 = new BigDecimal(cumulativeCompletions13);
//桩基（根） 总量:add
BigDecimal add = bigDecimal20.add(bigDecimal21).add(bigDecimal19).add(bigDecimal18).add(bigDecimal17).add(bigDecimal16).add(bigDecimal22);
//桩基（根）累计完成总量:add1
BigDecimal add1 = bigDecimal29.add(bigDecimal28).add(bigDecimal27).add(bigDecimal26).add(bigDecimal25).add(bigDecimal24).add(bigDecimal23);
//桩基（根）占比: bigDecimal30
BigDecimal bigDecimal30 = new BigDecimal("0");
if (add.compareTo(BigDecimal.ZERO) == 1){
BigDecimal bigDecimal7 = add1.divide(add, 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP);
bigDecimal30 = new BigDecimal(String.valueOf(bigDecimal7));
}
// 桩基（根） 总量计算
row2 = sheet.createRow(4 + weeklyProgressReportList.size() + 5);
cell0 = row2.createCell(0);
cell1 = row2.createCell(1);
cell2 = row2.createCell(2);
cell13 = row2.createCell(3);
cell0.setCellValue("桩基（根）");
cell1.setCellValue(String.valueOf(add));
cell2.setCellValue(String.valueOf(add1));
cell13.setCellValue(bigDecimal30+"%");
setStyle(cell0,style,contextFont);
setStyle(cell1,style,contextFont);
setStyle(cell2,style,contextFont);
setStyle(cell13,style,contextFont);
// 立柱 总量计算
unitName = "立柱（根）";
String totalNum5 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (totalNum5 == null){
totalNum5 = "0";
}
//立柱 累计完成量 计算
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions4 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (cumulativeCompletions4 == null){
cumulativeCompletions4 = "0";
}
//立柱 总量:bigDecimal8
BigDecimal bigDecimal8 = new BigDecimal(totalNum5);
//立柱 累计完成量：bigDecimal7
BigDecimal bigDecimal7 = new BigDecimal(cumulativeCompletions4);
BigDecimal bigDecimal9 = new BigDecimal("0");
if (!(bigDecimal8.compareTo(BigDecimal.ZERO) == 0)){
BigDecimal bigDecimal10 = bigDecimal7.divide(bigDecimal8,4,BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP);
bigDecimal9 = new BigDecimal(String.valueOf(bigDecimal10));
}
row2 = sheet.createRow(5 + weeklyProgressReportList.size() + 5);
cell0 = row2.createCell(0);
cell1 = row2.createCell(1);
cell2 = row2.createCell(2);
cell13 = row2.createCell(3);
cell0.setCellValue("立柱（根）");
cell1.setCellValue(String.valueOf(bigDecimal8));
cell2.setCellValue(String.valueOf(cumulativeCompletions4));
cell13.setCellValue(String.valueOf(bigDecimal9)+"%");
setStyle(cell0,style,contextFont);
setStyle(cell1,style,contextFont);
setStyle(cell2,style,contextFont);
setStyle(cell13,style,contextFont);
//**********************立柱（根 结束******************
//**********************盖梁（个） ******************
unitName = "盖梁（个）";
String totalNum6 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (totalNum6 == null){
totalNum6 = "0";
}
//盖梁 累计完成量 计算
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions5 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (cumulativeCompletions5 == null){
cumulativeCompletions5 = "0";
}
// 盖梁累计完成量：bigDecimal10
BigDecimal bigDecimal10 = new BigDecimal(cumulativeCompletions5);
// 盖梁 总量：bigDecimal11
BigDecimal bigDecimal11 = new BigDecimal(totalNum6);
BigDecimal bigDecimal12 = new BigDecimal("0");
if (!(bigDecimal11.compareTo(BigDecimal.ZERO) == 0)){
BigDecimal bigDecimal13 = bigDecimal10.divide(bigDecimal11,4,BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP);
bigDecimal12 = new BigDecimal(String.valueOf(bigDecimal13));
}
row2 = sheet.createRow(6 + weeklyProgressReportList.size() + 5);
cell0 = row2.createCell(0);
cell1 = row2.createCell(1);
cell2 = row2.createCell(2);
cell13 = row2.createCell(3);
cell0.setCellValue("盖梁（个）");
cell1.setCellValue(String.valueOf(totalNum6));
cell2.setCellValue(String.valueOf(cumulativeCompletions5));
cell13.setCellValue(String.valueOf(bigDecimal12)+"%");
setStyle(cell0,style,contextFont);
setStyle(cell1,style,contextFont);
setStyle(cell2,style,contextFont);
setStyle(cell13,style,contextFont);
//**********************盖梁（个） 结束******************
//**********************梁板预制（片）******************
//梁板预制片 总量计算
unitName = "梁板预制";
String totalNum7 = zhgdWeeklyProgressInventoryMapper.getTotalNum(projectId, unitName);
if (totalNum7 == null){
totalNum7 = "0";
}
// 梁板预制片 累计完成量 计算
weeklyProgressReport.setUnitName(unitName);
String cumulativeCompletions6 = weeklyProgressReportMapper.getCumulativeCompletions(weeklyProgressReport);
if (cumulativeCompletions6 == null){
cumulativeCompletions6 = "0";
}
//梁板预制片总量
BigDecimal bigDecimal13 = new BigDecimal(totalNum7);
//梁板预制片累计完成量
BigDecimal bigDecimal14 = new BigDecimal(cumulativeCompletions6);
BigDecimal divide3 = new BigDecimal("0");
if (!(bigDecimal13.compareTo(BigDecimal.ZERO) == 0)){
BigDecimal bigDecimal31 = bigDecimal14.divide(bigDecimal13,4,BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2, BigDecimal.ROUND_HALF_UP);
divide3 = new BigDecimal(String.valueOf(bigDecimal31));
}
row2 = sheet.createRow(7 + weeklyProgressReportList.size() + 5);
cell0 = row2.createCell(0);
cell1 = row2.createCell(1);
cell2 = row2.createCell(2);
cell13 = row2.createCell(3);
cell0.setCellValue("梁板预制（片）");
cell1.setCellValue(String.valueOf(totalNum7));
cell2.setCellValue(String.valueOf(cumulativeCompletions6));
cell13.setCellValue(String.valueOf(divide3)+"%");
setStyle(cell0,style,contextFont);
setStyle(cell1,style,contextFont);
setStyle(cell2,style,contextFont);
setStyle(cell13,style,contextFont);
//**********************梁板预制（片） 结束******************
}
导出样式文章来源：https://www.toymoban.com/news/detail-612878.html
文章来源地址https://www.toymoban.com/news/detail-612878.html
到了这里，关于POI 导出 树形结构的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
![java八股文面试[数据库]——MySQL索引的数据结构](https://imgs.yssmx.com/Uploads/2024/02/685933-1.png)
