1.控制层
@GetMapping("/exportDataDynamic")
@ApiOperation("一二次目标奖金制季度年度导出(动态表头)")
public void exportDataDynamic(
@ApiParam("公司主体id")@RequestParam Long firmId,
@ApiParam("部门id")@RequestParam Long deptId,
@ApiParam("年度")@RequestParam Integer year,
@ApiParam("周期")@RequestParam String cycle,
@ApiParam("激励模式 10-提成制 20-目标奖金制")@RequestParam Integer incentiveMode,
@ApiParam("适用二层分配科目id,逗号隔开")@RequestParam String performanceDivideOurseTypeIds,
@ApiParam("空间类型id")@RequestParam Long allocateSpaceTypeId,
HttpServletResponse response) {
oneTwoTargetBonusService.exportDataDynamic(firmId,deptId,year,cycle,incentiveMode,performanceDivideOurseTypeIds,allocateSpaceTypeId,response);
}
2.服务层
/**
* 一二次目标奖金制年度季度导出(动态表头)
* @param firmId
* @param deptId
* @param year
* @param cycle
* @param incentiveMode
* @param performanceDivideOurseTypeIds
* @param allocateSpaceTypeId
* @param response
*/
public void exportDataDynamic(Long firmId, Long deptId, Integer year, String cycle, Integer incentiveMode, String performanceDivideOurseTypeIds,Long allocateSpaceTypeId, HttpServletResponse response) {
DistributionModel loadYearTargetBonusDetailData;
if("年度".equals(cycle)){
loadYearTargetBonusDetailData = loadYearTargetBonusDetailData(firmId,deptId,year,cycle,allocateSpaceTypeId,performanceDivideOurseTypeIds,incentiveMode);
}else{
loadYearTargetBonusDetailData = loadQuarterDetailData(firmId, deptId, year, cycle, allocateSpaceTypeId, performanceDivideOurseTypeIds, incentiveMode);
}
if (Objects.nonNull(loadYearTargetBonusDetailData) && CollectionUtils.isNotEmpty(loadYearTargetBonusDetailData.getHeads())) {
List<DistributionHeaderModel> heads = loadYearTargetBonusDetailData.getHeads();
ExcelData data = new ExcelData();
String name = "目标奖金制员工"+year+cycle+"绩效分配.xlsx";
data.setName(name);
//渲染动态表头第一行
data.setTitles(getFirstTitlesDynamic(heads,cycle));
//渲染动态表头第二行
data.setSubTitles(getSubTitleDynamic(heads,cycle));
//渲染动态表头第三行
data.setSubTwoTitles(getThreeTitleDynamic(heads,cycle));
log.info("部门id:{} {}{}表头动态列数:{}",deptId,year,cycle,data.getTitles().size());
//合并表头三层单元格
data.setExcelMergedRegionList(fillYearMergedRegionDynamic(heads,cycle));
List<String> codeList = readHeadCode(heads);
log.info("数据动态列数:"+codeList.size());
log.info("数据编码:"+JSON.toJSONString(codeList));
List<List<Object>> rows = new ArrayList<>();
loadYearTargetBonusDetailData.getData().forEach(map->{
try {
List<Object> row = new ArrayList<>();
for (int i = 0; i < 5; i++) {
row.add(map.getOrDefault(codeList.get(i),"0"));
}
row.add(Objects.nonNull(GeneralYesNoEnum.getByCode(Integer.valueOf(String.valueOf(map.getOrDefault("isSpecialPerson","0"))))) ? GeneralYesNoEnum.getByCode(Integer.valueOf(String.valueOf(map.getOrDefault("isSpecialPerson","0")))).getMessage() : "");
for (int i = 6; i < codeList.size(); i++) {
row.add(map.getOrDefault(codeList.get(i),"0"));
}
rows.add(row);
} catch (Exception e) {
log.error("异常原因：{}",e);
log.info("一二次目标奖金制导出(动态表头)导出数据异常,主键{}", map.get("employeeNo"));
}
});
data.setRows(rows);
try {
ExportExcelUtils.exportExcel(response, name, data);
} catch (Exception e) {
log.error("一二次目标奖金制导出(动态表头)导出异常原因：{}",e);
}
}
}/**
* 渲染年度动态表头第一行
* @param heads
* @param cycle
* @return
*/
private List<String> getFirstTitlesDynamic(List<DistributionHeaderModel> heads,String cycle) {
List<String> list = new ArrayList<>();
//一级部门
list.add(heads.stream().filter(h -> "deptName".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
list.add(heads.stream().filter(h -> "secondDeptName".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
list.add(heads.stream().filter(h -> "thirdDeptName".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
list.add(heads.stream().filter(h -> "employeeNo".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
list.add(heads.stream().filter(h -> "employeeName".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
//是否特殊人员
list.add(heads.stream().filter(h -> "isSpecialPerson".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
//年度兑现合计(年度) or 本次兑现合计(季度)
addOneCol(list,heads,"totalCashed");
if("年度".equals(cycle)){
//季度已兑现合计
list.add(heads.stream().filter(h -> "totalQuarterlyCash".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
//本次对赌(可能没有)
DistributionHeaderModel distributionHeaderModel = heads.stream().filter(h -> "totalCurrentBetAdd".equals(h.getCode())).findFirst().orElse(null);
if(Objects.nonNull(distributionHeaderModel)){
list.add(distributionHeaderModel.getName());
}
//本次兑现合计
list.add(heads.stream().filter(h -> "total_summary".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
}
//一次分配
DistributionHeaderModel oneDistribution = getDistributionHeaderModelByCode(heads,"oneDistribution");
if(Objects.nonNull(oneDistribution)){
addOneCol(list,heads,"oneDistribution");
}
//二次分配
DistributionHeaderModel twoDistribution = getDistributionHeaderModelByCode(heads,"twoDistribution");
if(Objects.nonNull(twoDistribution)){
addOneCol(list,heads,"twoDistribution");
}
//资产对赌兑现表头(可能没有)
DistributionHeaderModel betDistributionHead = getDistributionHeaderModelByCode(heads,"betDistribution");
if(Objects.nonNull(betDistributionHead)){
addOneCol(list,heads,"betDistribution");
}
//新增对赌事项(可能没有)
DistributionHeaderModel betAddDistribution = getDistributionHeaderModelByCode(heads,"betAddDistribution");
if(Objects.nonNull(betAddDistribution)){
addOneCol(list,heads,"betAddDistribution");
}
//激励说明
list.add(heads.stream().filter(h -> "incentiveDescription".equals(h.getCode())).findFirst().orElse( new DistributionHeaderModel()).getName());
return list;
}/**
* 渲染年度动态表头第二行
* @param heads
* @param cycle
* @return
*/
private List<String> getSubTitleDynamic(List<DistributionHeaderModel> heads,String cycle) {
List<String> list = new ArrayList<>();
for (int i = 0; i < 6; i++) {
list.add("");
}
//年度兑现合计/本次兑现下的第二层
addTwoCol(list,heads,"totalCashed");
if("年度".equals(cycle)){
//季度已兑现合计下的第二层
list.add("");
//本次对赌(可能没有)
DistributionHeaderModel distributionHeaderModel = heads.stream().filter(h -> "totalCurrentBetAdd".equals(h.getCode())).findFirst().orElse(null);
if(Objects.nonNull(distributionHeaderModel)){
list.add("");
}
//本次兑现合计下的第二层
list.add("");
}
//一次分配下的第二层
DistributionHeaderModel oneDistribution = getDistributionHeaderModelByCode(heads,"oneDistribution");
if(Objects.nonNull(oneDistribution)){
addTwoCol(list,heads,"oneDistribution");
}
//二次分配下的第二层
DistributionHeaderModel twoDistribution = getDistributionHeaderModelByCode(heads,"twoDistribution");
if(Objects.nonNull(twoDistribution)){
addTwoCol(list,heads,"twoDistribution");
}
//资产对赌兑现表头下的第二层
DistributionHeaderModel betDistributionHead = getDistributionHeaderModelByCode(heads,"betDistribution");
if(Objects.nonNull(betDistributionHead)){
addTwoCol(list,heads,"betDistribution");
}
//新增对赌事项下的第二层
DistributionHeaderModel betAddDistribution = getDistributionHeaderModelByCode(heads,"betAddDistribution");
if(Objects.nonNull(betAddDistribution)){
addTwoCol(list,heads,"betAddDistribution");
}
//激励说明下的第二层
list.add("");
return list;
}/**
* 渲染年度动态表头第三行
* @param heads
* @return
*/
private List<List<String>> getThreeTitleDynamic(List<DistributionHeaderModel> heads,String cycle) {
List<List<String>> mainList = new ArrayList<>();
List<String> list = new ArrayList<>();
for (int i = 0; i < 6; i++) {
list.add("");
}
//年度兑现合计/本次兑现下的第三层
addThreeCol(list,heads,"totalCashed");
if("年度".equals(cycle)){
//季度已兑现合计下的第三层
list.add("");
//本次对赌(可能没有)
DistributionHeaderModel distributionHeaderModel = heads.stream().filter(h -> "totalCurrentBetAdd".equals(h.getCode())).findFirst().orElse(null);
if(Objects.nonNull(distributionHeaderModel)){
list.add("");
}
//本次兑现合计下的第三层
list.add("");
}
//一次分配下的第三层
DistributionHeaderModel oneDistribution = getDistributionHeaderModelByCode(heads,"oneDistribution");
if(Objects.nonNull(oneDistribution)){
addThreeCol(list,heads,"oneDistribution");
}
//二次分配下的第三层
DistributionHeaderModel twoDistribution = getDistributionHeaderModelByCode(heads,"twoDistribution");
if(Objects.nonNull(twoDistribution)){
addThreeCol(list,heads,"twoDistribution");
}
//资产对赌兑现表头下的第三层
DistributionHeaderModel betDistributionHead = getDistributionHeaderModelByCode(heads,"betDistribution");
if(Objects.nonNull(betDistributionHead)){
addThreeCol(list,heads,"betDistribution");
}
//新增对赌事项下的第三层
DistributionHeaderModel betAddDistribution = getDistributionHeaderModelByCode(heads,"betAddDistribution");
if(Objects.nonNull(betAddDistribution)){
addThreeCol(list,heads,"betAddDistribution");
}
//激励说明下的第三层
list.add("");
mainList.add(list);
return mainList;
}/**
* 年度动态表头合并三层单元格
* @param heads
* @param cycle
* @return
*/
private List<ExcelMergedRegion> fillYearMergedRegionDynamic(List<DistributionHeaderModel> heads,String cycle) {
List<ExcelMergedRegion> excelMergedRegionList = new ArrayList<>();
//一级部门-->是否特殊人员
for (int i = 0; i < 6; i++) {
excelMergedRegionList.add(new ExcelMergedRegion(0, 2, i, i));
}
//年度兑现合计/本次兑现合计
int currentCol = mergedYearDynamic(excelMergedRegionList,heads, "totalCashed",6);
if("年度".equals(cycle)){
//季度已兑现合计
excelMergedRegionList.add(new ExcelMergedRegion(0, 2, currentCol, currentCol));
currentCol+=1;
//本次对赌(可能没有)
DistributionHeaderModel distributionHeaderModel = heads.stream().filter(h -> "totalCurrentBetAdd".equals(h.getCode())).findFirst().orElse(null);
if(Objects.nonNull(distributionHeaderModel)){
excelMergedRegionList.add(new ExcelMergedRegion(0, 2, currentCol, currentCol));
currentCol += 1;
}
//本次兑现合计
excelMergedRegionList.add(new ExcelMergedRegion(0, 2, currentCol, currentCol));
currentCol += 1;
}
//一次分配
DistributionHeaderModel oneDistribution = getDistributionHeaderModelByCode(heads,"oneDistribution");
if(Objects.nonNull(oneDistribution)){
currentCol = mergedYearDynamic(excelMergedRegionList,heads, "oneDistribution",currentCol);
}
//二次分配
DistributionHeaderModel twoDistribution = getDistributionHeaderModelByCode(heads,"twoDistribution");
if(Objects.nonNull(twoDistribution)){
currentCol = mergedYearDynamic(excelMergedRegionList,heads, "twoDistribution",currentCol);
}
//资产对赌兑现
DistributionHeaderModel betDistributionHead = getDistributionHeaderModelByCode(heads,"betDistribution");
if(Objects.nonNull(betDistributionHead)){
currentCol = mergedYearDynamic(excelMergedRegionList,heads, "betDistribution",currentCol);
}
//新增对赌事项
DistributionHeaderModel betAddDistribution = getDistributionHeaderModelByCode(heads,"betAddDistribution");
if(Objects.nonNull(betAddDistribution)){
currentCol = mergedYearDynamic(excelMergedRegionList,heads, "betAddDistribution",currentCol);
}
//激励说明
excelMergedRegionList.add(new ExcelMergedRegion(0, 2, currentCol, currentCol));
log.info("动态表格合并:{}",JSON.toJSONString(excelMergedRegionList));
return excelMergedRegionList;
}/**
* 合并年度兑现合计,一次分配,二次分配,资产对赌兑现,新增对赌事项单元格(树形结构)
* @param excelMergedRegionList
* @param heads
* @param code
* @param currentCol
* @return
*/
private int mergedYearDynamic(List<ExcelMergedRegion> excelMergedRegionList, List<DistributionHeaderModel> heads, String code, int currentCol) {
//计算树形结构总共占用多少列
int total = getDistributionColTotal(heads, code);
//一层
DistributionHeaderModel oneDistributionHeaderModel = getDistributionHeaderModelByCode(heads, code);
//表头第一行
excelMergedRegionList.add(new ExcelMergedRegion(0, 0, currentCol, currentCol+total-1));
//二层
List<DistributionHeaderModel> twoDistributionHeaderModel = oneDistributionHeaderModel.getSub();
for(DistributionHeaderModel two : twoDistributionHeaderModel){
//三层
List<DistributionHeaderModel> threeDistributionHeaderModel = two.getSub();
if(CollectionUtils.isEmpty(threeDistributionHeaderModel)){
//三层没数据则第二行第三行合并
excelMergedRegionList.add(new ExcelMergedRegion(1, 2, currentCol, currentCol));
currentCol += 1;
}else{
//第三层表头两列以上需要合并第二层
if(threeDistributionHeaderModel.size() > 1){
excelMergedRegionList.add(new ExcelMergedRegion(1, 1, currentCol, currentCol+threeDistributionHeaderModel.size()-1));
currentCol += threeDistributionHeaderModel.size();
}else{
currentCol += 1;
}
}
}
return currentCol;
}3.导出工具类
package com.firefly.performance.core.utils;
import com.firefly.performance.api.enums.performance.ExcelComputeTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Objects;
@Slf4j
public class ExportExcelUtils {
public ExportExcelUtils() {
}
public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
response.setContentType("application/x-download");
response.setCharacterEncoding("utf-8");
exportExcel(data, response);
}
public static void exportExcel(ExcelData data, HttpServletResponse response) {
try (XSSFWorkbook wb = new XSSFWorkbook();OutputStream out = response.getOutputStream()){
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet1";
}
//合并表头写入数据
mergeTitleExport(sheetName,data,wb);
wb.write(out);
} catch (Exception e) {
log.error("异常原因：{}",e);
}
}
public static void exportExcelHideCol(HttpServletResponse response, String fileName, ExcelData data,int colIndex ) throws Exception {
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
response.setContentType("application/x-download");
response.setCharacterEncoding("utf-8");
exportExcelHideCol(data, response, colIndex);
}
public static void exportExcelHideCol(ExcelData data, HttpServletResponse response,int colIndex) {
try (XSSFWorkbook wb = new XSSFWorkbook();OutputStream out = response.getOutputStream()){
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet1";
}
XSSFSheet sheet = wb.createSheet(sheetName);
writeExcel(wb, sheet, data);
// sheet.setColumnWidth(colIndex,0);
//设置列隐藏
sheet.getColumnHelper().setColHidden((long)colIndex, true);
wb.write(out);
} catch (Exception e) {
log.error("异常原因：{}",e);
}
}
public static void exportExcels(HttpServletResponse response, String fileName, List<ExcelData> dataList) throws Exception {
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
response.setContentType("application/x-download");
response.setCharacterEncoding("utf-8");
exportExcels(dataList, response.getOutputStream());
}
public static void exportExcels(List<ExcelData> dataList, OutputStream out) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
try {
for(int i =0; i<dataList.size();i++){
ExcelData data = dataList.get(i);
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet"+(i+1);
}
XSSFSheet sheet = wb.createSheet();
wb.setSheetName(i,sheetName);
writeExcel(wb, sheet, data);
}
wb.write(out);
} finally {
wb.close();
out.close();
}
}
private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
int rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
writeRowsToExcel(wb, sheet, data, rowIndex);
}
/**
* 合并两行表头
* @param wb
* @param sheet
* @param titles
* @param rowIndex
* @param excelMergedRegionList
* @return
*/
private static int writeSubTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles, int rowIndex,List<ExcelMergedRegion> excelMergedRegionList) {
Font titleFont = wb.createFont();
titleFont.setFontName("微软雅黑");
titleFont.setBold(true);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
Row titleRow = sheet.createRow(rowIndex);
int colIndex = 0;
for (Iterator var8 = titles.iterator(); var8.hasNext(); ++colIndex) {
String field = (String) var8.next();
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
}
if (CollectionUtils.isNotEmpty(excelMergedRegionList)){
for (ExcelMergedRegion region : excelMergedRegionList) {
sheet.addMergedRegion(new CellRangeAddress(region.getFirstRow(),region.getLastRow(),region.getFirstCol(),region.getLastCol()));
}
}
return rowIndex + 1;
}
/**
* 合并三行以上表头
* @param wb
* @param sheet
* @param subTitles
* @param subTwoTitles
* @param rowIndex
* @param excelMergedRegionList
* @return
*/
private static int writeSubTwoTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> subTitles,List<List<String>> subTwoTitles, int rowIndex,List<ExcelMergedRegion> excelMergedRegionList,List<CellBackGroundColor> cellBackGroundColorList) {
Font titleFont = wb.createFont();
titleFont.setFontName("微软雅黑");
titleFont.setBold(true);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
//合并第二行表头
Row titleRow = sheet.createRow(rowIndex);
int colIndex = 0;
for (Iterator var8 = subTitles.iterator(); var8.hasNext(); ++colIndex) {
String field = (String) var8.next();
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
}
//合并从第三行开始的表头
if(CollectionUtils.isNotEmpty(subTwoTitles)){
for (List<String> subTwoTitle : subTwoTitles) {
rowIndex = rowIndex+1;
Row titleRow2 = sheet.createRow(rowIndex);
int colIndex2 = 0;
for (Iterator var8 = subTwoTitle.iterator(); var8.hasNext(); ++colIndex2) {
String field = (String) var8.next();
Cell cell = titleRow2.createCell(colIndex2);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
}
}
}
if (CollectionUtils.isNotEmpty(excelMergedRegionList)){
for (ExcelMergedRegion region : excelMergedRegionList) {
//一个单元格不需要合并
if(region.getFirstRow() != region.getLastRow() || region.getFirstCol() != region.getLastCol()){
sheet.addMergedRegion(new CellRangeAddress(region.getFirstRow(),region.getLastRow(),region.getFirstCol(),region.getLastCol()));
}
}
}
return rowIndex+1;
}
private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
int rowIndex = 0;
Font titleFont = wb.createFont();
titleFont.setFontName("微软雅黑");
titleFont.setBold(true);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
Row titleRow = sheet.createRow(rowIndex);
int colIndex = 0;
for(Iterator var8 = titles.iterator(); var8.hasNext(); ++colIndex) {
String field = (String)var8.next();
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
}
rowIndex = rowIndex + 1;
return rowIndex;
}
/**
* 导出数据
* @param wb
* @param sheet
* @param data
* @param rowIndex
* @return
*/
private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data, int rowIndex) {
List<List<Object>> rows = data.getRows();
DecimalFormat df = new DecimalFormat("#,###");
Font dataFont = wb.createFont();
dataFont.setFontName("微软雅黑");
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
dataStyle.setFont(dataFont);
setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
//单元格属性设置为千分位 数值
XSSFCellStyle dataStyle2 = wb.createCellStyle();
dataStyle2.setFont(dataFont);
setBorder(dataStyle2, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
dataStyle2.setDataFormat(wb.createDataFormat().getFormat("#,##0_ "));
for(Iterator var8 = rows.iterator(); var8.hasNext(); ++rowIndex) {
List<Object> rowData = (List)var8.next();
Row dataRow = sheet.createRow(rowIndex);
int colIndex = 0;
for(Iterator var11 = rowData.iterator(); var11.hasNext(); ++colIndex) {
Object cellData = var11.next();
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
cell.setCellValue(cellData.toString());
if (cellData instanceof Integer) {
cell.setCellValue(df.format(cellData));
}
} else {
cell.setCellValue("");
}
if(CollectionUtils.isNotEmpty(data.getNumberColList()) && data.getNumberColList().contains(Integer.valueOf(colIndex))){
//单元格属性设置为千分位 数值
cell.setCellStyle(dataStyle2);
}else{
//常规
cell.setCellStyle(dataStyle);
}
}
}
return rowIndex;
}
/**
* 写入计算公式
* @param sheet
* @param excelComputeList
*/
private static void writeComputeToExcel(Sheet sheet, List<ExcelCompute> excelComputeList) {
if(CollectionUtils.isNotEmpty(excelComputeList)){
for(ExcelCompute excelCompute : excelComputeList){
switch (ExcelComputeTypeEnum.getByCode(excelCompute.getType())){
case APPOINT:
Row row = sheet.getRow(excelCompute.getOperationOne());
Cell cell = row.getCell(excelCompute.getOperationTwo());
cell.setCellFormula(excelCompute.getComputeFormula());
cell.setCellType(CellType.FORMULA);
break;
case ADD:
if(CollectionUtils.isNotEmpty(excelCompute.getCols())){
int lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
//String str = "";
List<String> colStrList = new ArrayList<>();
for(Integer col :excelCompute.getCols()){
//str +=excelIndexToStr(col)+(i+1)+"+";
colStrList.add(excelIndexToStr(col)+(i+1));
}
//str = str.substring(0,str.lastIndexOf("+"));
//特殊函数两者取最大值
if(Objects.nonNull(excelCompute.getOperationOne())){
cell.setCellFormula("MAX("+excelIndexToStr(excelCompute.getOperationOne())+(i+1)+",SUM(" + StringUtils.join(colStrList, "+") + "))");
}else if(Objects.nonNull(excelCompute.getOperationTwo())){
cell.setCellFormula("MAX(0,SUM(" + StringUtils.join(colStrList, "+") + "))");
}else{
cell.setCellFormula("SUM(" + StringUtils.join(colStrList, "+") + ")");
}
//cell.setCellFormula(str);
cell.setCellType(CellType.FORMULA);
}
}
break;
case SUBTRACT:
int lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
String f = excelIndexToStr(excelCompute.getOperationOne())+(i+1)+ExcelComputeTypeEnum.getByCode(excelCompute.getType()).getDescription()+excelIndexToStr(excelCompute.getOperationTwo())+(i+1);
cell.setCellFormula("MAX(0,"+f+")");
cell.setCellType(CellType.FORMULA);
}
break;
case MULTIPLY:
lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
String f = excelIndexToStr(excelCompute.getOperationOne())+(i+1)+ExcelComputeTypeEnum.getByCode(excelCompute.getType()).getDescription()+excelIndexToStr(excelCompute.getOperationTwo())+(i+1);
cell.setCellFormula("ROUND("+f+",0)");
cell.setCellType(CellType.FORMULA);
}
break;
case DIVIDE:
lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
String f = "CONCATENATE(ROUND("+excelIndexToStr(excelCompute.getOperationOne())+(i+1)+ExcelComputeTypeEnum.getByCode(excelCompute.getType()).getDescription()+excelIndexToStr(excelCompute.getOperationTwo())+(i+1)+"*100,2),\"%\")";
cell.setCellFormula(f);
cell.setCellType(CellType.FORMULA);
}
break;
case DIVIDE_SUBTRACT_ONE:
lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
String f = "CONCATENATE(ROUND(("+excelIndexToStr(excelCompute.getOperationOne())+(i+1)+ExcelComputeTypeEnum.getByCode(excelCompute.getType()).getDescription()+excelIndexToStr(excelCompute.getOperationTwo())+(i+1)+"-1)*100,2),\"%\")";
cell.setCellFormula(f);
cell.setCellType(CellType.FORMULA);
}
break;
case SINGLE_COL:
List<Integer> cols = excelCompute.getCols();
Integer firstRow = excelCompute.getFirstRow();
lastRowNum = sheet.getLastRowNum();
row = sheet.getRow(excelCompute.getOperationOne());
cell = row.getCell(excelCompute.getOperationTwo());
String str = "";
// for (int i = firstRow; i <= lastRowNum; i++) {
// str +=excelIndexToStr(cols.get(0))+(i+1)+"+";
// }
// str = str.substring(0,str.lastIndexOf("+"));
//cell.setCellFormula("SUM(" + StringUtils.join(colStrList, ",") + ")");
List<String> colStrList = new ArrayList<>();
for (int i = firstRow; i <= lastRowNum; i++) {
colStrList.add(excelIndexToStr(cols.get(0))+(i+1));
}
//str = str.substring(0,str.lastIndexOf("+"));
cell.setCellFormula("SUM(" + StringUtils.join(colStrList, "+") + ")");
//cell.setCellFormula("SUM("+excelIndexToStr(cols.get(0))+(firstRow+1)+":"+excelIndexToStr(cols.get(0))+(lastRowNum+1)+")+0");
//cell.setCellFormula(str);
cell.setCellType(CellType.FORMULA);
break;
case MORE_COL_ADD:
cols = excelCompute.getCols();
firstRow = excelCompute.getFirstRow();
lastRowNum = sheet.getLastRowNum();
row = sheet.getRow(excelCompute.getOperationOne());
cell = row.getCell(excelCompute.getOperationTwo());
cell.setCellFormula("SUM("+excelIndexToStr(cols.get(0))+(firstRow+1)+":"+excelIndexToStr(cols.get(0))+(lastRowNum+1)+")"+excelCompute.getComputeFormula()+"SUM("+excelIndexToStr(cols.get(1))+(firstRow+1)+":"+excelIndexToStr(cols.get(1))+(lastRowNum+1)+")");
cell.setCellType(CellType.FORMULA);
break;
case COL_SUBTRACT:
lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
String f = excelIndexToStr(excelCompute.getOperationOne())+(i+1)+"-"+excelIndexToStr(excelCompute.getCols().get(0))+(i+1)+"-"+excelIndexToStr(excelCompute.getCols().get(1))+(i+1);
cell.setCellFormula("MAX(0,"+f+")");
cell.setCellType(CellType.FORMULA);
}
break;
case EQUAL_TO:
lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
String f = excelIndexToStr(excelCompute.getOperationOne())+(i+1);
if(Objects.nonNull(excelCompute.getOperationTwo())){
cell.setCellFormula("MAX("+excelIndexToStr(excelCompute.getOperationTwo())+(i+1)+","+f+")");
}else{
cell.setCellFormula(f);
}
cell.setCellType(CellType.FORMULA);
}
break;
case MORE_COL_ADD_SUBTRACT:
lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
str = "";
//List<String> colStrList = new ArrayList<>();
for(Integer col :excelCompute.getCols()){
str +=excelIndexToStr(col)+(i+1)+"+";
//colStrList.add(excelIndexToStr(col)+(i+1));
}
str = str.substring(0,str.lastIndexOf("+"));
//cell.setCellFormula("SUM(" + StringUtils.join(colStrList, ",") + ")");
cell.setCellFormula("MAX(0,"+str+"-"+excelIndexToStr(excelCompute.getOperationOne())+(i+1)+")");
cell.setCellType(CellType.FORMULA);
}
break;
case ADD_COLON:
lastRowNum = sheet.getLastRowNum();
//从开始行到结束行
for (int i = excelCompute.getFirstRow(); i <= lastRowNum; i++) {
row = sheet.getRow(i);
cell = row.getCell(excelCompute.getResultCol());
cell.setCellFormula("SUM("+excelIndexToStr(excelCompute.getCols().get(0))+(i+1)+":"+excelIndexToStr(excelCompute.getCols().get(1))+(i+1)+")");
cell.setCellType(CellType.FORMULA);
}
break;
default:
}
}
}
}
/**
* 列索引转换为A，B，C...
*如：cellIndex=0 --> A
* 如：cellIndex=1 --> B
*如：cellIndex=25 --> Z
*如：cellIndex=26 --> AA
*
* @param cellIndex Excel列索引
*/
private static String excelIndexToStr(int cellIndex) {
// 转26进制 0-25
// int calculateValue = value - 1; (value = cellIndex + 1)
// 本身传进来的就是列索引0-25，的26进制
int calculateValue = cellIndex;
// 取高位
int high = calculateValue / 26;
// 取低位
int low = calculateValue % 26;
// 低位可直接取出对应的字母
String transStr = String.valueOf((char) (low + 65));
if (high > 0) {
// 高位递归取出字母
transStr = excelIndexToStr(high-1) + transStr;
}
return transStr;
}
private static void autoSizeColumns(Sheet sheet, int columnNumber) {
for(int i = 0; i < columnNumber; ++i) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = sheet.getColumnWidth(i) + 100;
if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}
private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
style.setBorderTop(border);
style.setBorderLeft(border);
style.setBorderRight(border);
style.setBorderBottom(border);
style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
}
/**
* excel导出多sheet
* @param response
* @param fileName
* @param excelDatalist
*/
public static void exportExcelMoreSheet(HttpServletResponse response, String fileName, List<ExcelData> excelDatalist) throws Exception{
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
response.setContentType("application/x-download");
response.setCharacterEncoding("utf-8");
exportMoreExcel(excelDatalist, response);
}
/**
* 导出多sheet
* @param excelDatalist
* @param response
*/
public static void exportMoreExcel(List<ExcelData> excelDatalist, HttpServletResponse response) {
try (XSSFWorkbook wb = new XSSFWorkbook();OutputStream out = response.getOutputStream()){
for (int i = 0; i < excelDatalist.size(); i++) {
ExcelData data = excelDatalist.get(i);
String sheetName = data.getName();
if (null == sheetName) {
sheetName = "Sheet"+(i+1);
}
//合并表头写入数据
mergeTitleExport(sheetName,data,wb);
}
wb.write(out);
} catch (Exception e) {
log.error("异常原因：{}",e);
}
}
/**
* 合并表头写入数据
* @param sheetName
* @param data
* @param wb
*/
private static void mergeTitleExport(String sheetName, ExcelData data,XSSFWorkbook wb) {
XSSFSheet sheet = wb.createSheet(sheetName);
//合并表头
if (CollectionUtils.isNotEmpty(data.getSubTitles()) && CollectionUtils.isNotEmpty(data.getExcelMergedRegionList())){
int rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
if(CollectionUtils.isNotEmpty(data.getSubTwoTitles())){
//合并三行以上表头
rowIndex = writeSubTwoTitlesToExcel(wb, sheet, data.getSubTitles(),data.getSubTwoTitles(), rowIndex,data.getExcelMergedRegionList(),data.getCellBackGroundColorList());
}else{
//合并两行表头
rowIndex = writeSubTitlesToExcel(wb, sheet, data.getSubTitles(), rowIndex,data.getExcelMergedRegionList());
}
//写入数据
writeRowsToExcel(wb, sheet, data, rowIndex);
}else{
//一行表头
writeExcel(wb, sheet, data);
}
//写入计算公式
if(CollectionUtils.isNotEmpty(data.getExcelComputeList())){
writeComputeToExcel(sheet,data.getExcelComputeList());
}
//设置表头文本居中
if(CollectionUtils.isNotEmpty(data.getCellCenterList())){
setCellCenter(wb,sheet,data.getCellCenterList());
}
//设置单元格背景颜色
if(CollectionUtils.isNotEmpty(data.getCellBackGroundColorList())){
setCellBackGround(wb,sheet,data.getCellBackGroundColorList());
}
//设置行背景颜色
if(CollectionUtils.isNotEmpty(data.getRowBackGroundColorList())){
setRowBackGround(wb,sheet,data.getRowBackGroundColorList());
}
//设置单元格样式
if(CollectionUtils.isNotEmpty(data.getBorderList())){
setBorder(wb,sheet,data.getBorderList());
}
if(data.getColSize() > 0){
for (int i = 0; i < data.getColSize(); i++) {
//设置宽度自适应
sheet.autoSizeColumn(i,true);
}
for (int i = 0; i < data.getColSize(); i++) {
// 调整每一列宽度
sheet.autoSizeColumn(i, true);
// 解决自动设置列宽中文失效的问题
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
}
}
}
/**
* 设置边框
* @param wb
* @param sheet
* @param borderCenterList
*/
private static void setBorder(XSSFWorkbook wb, XSSFSheet sheet, List<CellBackGroundColor> borderCenterList) {
borderCenterList.stream().forEach(r ->{
// 创建单元格样式
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("微软雅黑");
if(Objects.nonNull(r.getBold())){
//加粗
font.setBold(r.getBold());
}
style.setFont(font);
//设置单元格为数值
if(Objects.nonNull(r.getIsNumber()) && r.getIsNumber()){
style.setDataFormat(wb.createDataFormat().getFormat("#,##0_ "));
}
if(Objects.nonNull(r.getTop())){
style.setBorderTop(r.getTop());//上边框
}
if(Objects.nonNull(r.getBottom())){
style.setBorderBottom(r.getBottom());//下边框
}
if(Objects.nonNull(r.getLeft())){
style.setBorderLeft(r.getLeft());//左边框
}
if(Objects.nonNull(r.getRight())){
style.setBorderRight(r.getRight());//右边框
}
Row row = sheet.getRow(r.getRow());
Cell cell = row.getCell(r.getCol());
if(Objects.nonNull(cell)){
cell.setCellStyle(style);
}
});
}
/**
* 设置行背景颜色
* @param wb
* @param sheet
* @param rowBackGroundColorList
*/
private static void setRowBackGround(XSSFWorkbook wb, XSSFSheet sheet, List<RowBackGroundColor> rowBackGroundColorList) {
rowBackGroundColorList.stream().forEach(r ->{
Row row = sheet.getRow(r.getRow());
// 创建单元格样式并设置背景颜色
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(r.getBackGroundColor()); // 设置背景颜色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充图案为实心
style.setBorderRight(BorderStyle.THIN);//右边框
for (int i = 0; i < r.getSize(); i++) {
Cell cell = row.getCell(i);
cell.setCellStyle(style);
}
});
}
/**
* 设置表头文本居中
* @param wb
* @param sheet
* @param cellCenterList
*/
private static void setCellCenter(Workbook wb, Sheet sheet, List<CellBackGroundColor> cellCenterList) {
// 创建单元格样式并设置背景颜色
CellStyle style = wb.createCellStyle();
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
Font font = wb.createFont();
font.setFontName("微软雅黑");
//加粗
font.setBold(true);
style.setFont(font);
//上边框
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
if(CollectionUtils.isNotEmpty(cellCenterList)){
for (CellBackGroundColor cellCenter : cellCenterList) {
// 将样式应用于单元格
Row row = sheet.getRow(cellCenter.getRow());
Cell cell = row.getCell(cellCenter.getCol());
cell.setCellStyle(style);
}
}
}
/**
* 设置单元格背景颜色
* @param wb
* @param sheet
* @param cellBackGroundColorList
*/
private static void setCellBackGround(XSSFWorkbook wb,Sheet sheet, List<CellBackGroundColor> cellBackGroundColorList) {
cellBackGroundColorList.stream().forEach(c ->{
Row row = sheet.getRow(c.getRow());
Cell cell = row.getCell(c.getCol());
// 创建单元格样式并设置背景颜色
XSSFCellStyle style = wb.createCellStyle();
//style.setFillForegroundColor(c.getBackGroundColor()); // 设置背景颜色
//自定义颜色对象
XSSFColor color = new XSSFColor();
//根据你需要的rgb值获取byte数组
color.setRGB(intToByteArray(getIntFromColor(c.getRed(),c.getGreen(),c.getBlue())));
//自定义颜色
style.setFillForegroundColor(color);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置填充图案为实心
style.setBorderRight(BorderStyle.THIN);//右边框
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
Font font = wb.createFont();
font.setFontName("微软雅黑");
if(Objects.nonNull(c.getFontColor())){
font.setColor(c.getFontColor());
}
//加粗
font.setBold(true);
style.setFont(font);
//上边框
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
// 将样式应用于单元格
cell.setCellStyle(style);
});
}
/**
* rgb转int
*/
private static int getIntFromColor(int Red, int Green, int Blue){
Red = (Red << 16) & 0x00FF0000;
Green = (Green << 8) & 0x0000FF00;
Blue = Blue & 0x000000FF;
return 0xFF000000 | Red | Green | Blue;
}
/**
* int转byte[]
*/
public static byte[] intToByteArray(int i) {
byte[] result = new byte[4];
result[0] = (byte)((i >> 24) & 0xFF);
result[1] = (byte)((i >> 16) & 0xFF);
result[2] = (byte)((i >> 8) & 0xFF);
result[3] = (byte)(i & 0xFF);
return result;
}
}
动态渲染excel数据格式:
{
"code": 0,
"msg": "success",
"data": {
"heads": [
{
"code": "deptName",
"name": "一级部门",
"sort": 1,
"computeWay": null,
"level": 1,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "secondDeptName",
"name": "二级部门",
"sort": 2,
"computeWay": null,
"level": 1,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "thirdDeptName",
"name": "三级部门",
"sort": 3,
"computeWay": null,
"level": 1,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "employeeNo",
"name": "员工编号",
"sort": 4,
"computeWay": null,
"level": 1,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "employeeName",
"name": "员工姓名",
"sort": 5,
"computeWay": null,
"level": 1,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "isSpecialPerson",
"name": "是否特殊人员",
"sort": 5,
"computeWay": null,
"level": 1,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "totalCashed",
"name": "本次兑现合计",
"sort": 9,
"computeWay": null,
"level": 1,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "total_oneDistribution",
"name": "一次分配合计",
"sort": null,
"computeWay": "+",
"level": 2,
"sum": [
"oneDistributionHP10000000redemptionAmount",
"oneDistributionHP10000013redemptionAmount"
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "total_twoDistribution",
"name": "二次分配合计",
"sort": null,
"computeWay": "+",
"level": 2,
"sum": [
"twoDistributionHP10000119redemptionAmountTwo",
"twoDistributionHP10000171redemptionAmountTwo",
"twoDistributionHP10000232redemptionAmountTwo",
"twoDistributionHP10000293redemptionAmountTwo"
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "total_summary",
"name": "本次兑现合计",
"sort": null,
"computeWay": "+",
"level": 2,
"sum": [
"total_oneDistribution",
"total_twoDistribution"
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
}
]
},
{
"code": "oneDistribution",
"name": "一次分配",
"sort": 10,
"computeWay": "+",
"level": 1,
"sum": [
"oneDistributionHP10000000redemptionAmount",
"oneDistributionHP10000013redemptionAmount"
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "oneDistributionHP10000000",
"name": "拐点酬",
"sort": null,
"computeWay": null,
"level": 2,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "oneDistributionHP10000000convertedValue",
"name": "拐点酬折算值",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "oneDistributionHP10000000calculateCoefficient",
"name": "计算系数",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 20,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "oneDistributionHP10000000consensusCoefficient",
"name": "公议系数",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 20,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "oneDistributionHP10000000adjustmentCoefficient",
"name": "（+）调节系数",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": true,
"adjustmentMeans": 20,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "oneDistributionHP10000000vestingCoefficient",
"name": "兑现系数",
"sort": null,
"computeWay": "+",
"level": 3,
"sum": [
"oneDistributionHP10000000consensusCoefficient",
"oneDistributionHP10000000adjustmentCoefficient"
],
"editFlag": false,
"adjustmentMeans": 20,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "oneDistributionHP10000000redemptionAmount",
"name": "拐点酬本次兑现",
"sort": null,
"computeWay": "*",
"level": 3,
"sum": [
"oneDistributionHP10000000convertedValue",
"oneDistributionHP10000000vestingCoefficient"
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
}
]
},
{
"code": "oneDistributionHP10000013",
"name": "带教津贴",
"sort": null,
"computeWay": null,
"level": 2,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "oneDistributionHP10000013exAnte",
"name": "事前标准",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
},
{
"code": "oneDistributionHP10000013redemptionAmount",
"name": "本次兑现",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": true,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
}
]
}
]
},
{
"code": "twoDistribution",
"name": "二次分配",
"sort": 20,
"computeWay": "+",
"level": 1,
"sum": [
"twoDistributionHP10000119redemptionAmountTwo",
"twoDistributionHP10000171redemptionAmountTwo",
"twoDistributionHP10000232redemptionAmountTwo",
"twoDistributionHP10000293redemptionAmountTwo"
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "twoDistributionHP10000119",
"name": "拼搏奋斗奖",
"sort": null,
"computeWay": null,
"level": 2,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "twoDistributionHP10000119redemptionAmountTwo",
"name": "本次兑现",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": true,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
}
]
},
{
"code": "twoDistributionHP10000171",
"name": "二次德玛西亚杯",
"sort": null,
"computeWay": null,
"level": 2,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "twoDistributionHP10000171redemptionAmountTwo",
"name": "本次兑现",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": true,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
}
]
},
{
"code": "twoDistributionHP10000232",
"name": "今天二次",
"sort": null,
"computeWay": null,
"level": 2,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "twoDistributionHP10000232redemptionAmountTwo",
"name": "本次兑现",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": true,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
}
]
},
{
"code": "twoDistributionHP10000293",
"name": "陈奇新增",
"sort": null,
"computeWay": null,
"level": 2,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
{
"code": "twoDistributionHP10000293redemptionAmountTwo",
"name": "本次兑现",
"sort": null,
"computeWay": null,
"level": 3,
"sum": [
],
"editFlag": true,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
}
]
}
]
},
{
"code": "incentiveDescription",
"name": "激励说明",
"sort": 70,
"computeWay": null,
"level": 1,
"sum": [
],
"editFlag": false,
"adjustmentMeans": 10,
"funcName": null,
"compareType": -1,
"compareVal": null,
"extInfo": null,
"sub": [
]
}
],
"data": [
{
"incentiveDescription": "",
"deptName": "金融科技部",
"secondDeptName": "产品组",
"employeeName": "张樱",
"twoDistributionHP10000119redemptionAmountTwo": "8.00",
"twoDistributionHP10000232redemptionAmountTwo": "10.00",
"total_summary": "166.00",
"oneDistributionHP10000000redemptionAmount": "130.00",
"twoDistributionHP10000171redemptionAmountTwo": "10.00",
"thirdDeptName": "",
"isSpecialPerson": 20,
"oneDistributionHP10000000vestingCoefficient": "0.01",
"employeeNo": "220402",
"declareStatus": 500,
"communicationStatus": 0,
"oneDistributionHP10000000consensusCoefficient": "0.00",
"oneDistributionHP10000000adjustmentCoefficient": "0.01",
"oneDistributionHP10000000convertedValue": "13000",
"oneDistributionHP10000013redemptionAmount": "7.00",
"total_oneDistribution": "137",
"twoDistributionHP10000293redemptionAmountTwo": "1.00",
"oneDistributionHP10000000calculateCoefficient": "0.57",
"total_twoDistribution": "29",
"oneDistributionHP10000013exAnte": "2000.00"
},
{
"incentiveDescription": "",
"deptName": "金融科技部",
"secondDeptName": "产品组",
"employeeName": "陈玉君",
"twoDistributionHP10000119redemptionAmountTwo": "8.00",
"twoDistributionHP10000232redemptionAmountTwo": "10.00",
"total_summary": "53979.00",
"oneDistributionHP10000000redemptionAmount": "53950.00",
"twoDistributionHP10000171redemptionAmountTwo": "9.00",
"thirdDeptName": "",
"isSpecialPerson": 20,
"oneDistributionHP10000000vestingCoefficient": 4.15,
"employeeNo": "180683",
"declareStatus": 500,
"communicationStatus": 0,
"oneDistributionHP10000000consensusCoefficient": 1.15,
"oneDistributionHP10000000adjustmentCoefficient": 3,
"oneDistributionHP10000000convertedValue": "13000",
"oneDistributionHP10000013redemptionAmount": "0",
"total_oneDistribution": "53950",
"twoDistributionHP10000293redemptionAmountTwo": "2.00",
"oneDistributionHP10000000calculateCoefficient": 1.15,
"total_twoDistribution": "29",
"oneDistributionHP10000013exAnte": "0"
}
]
}
}
4737
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
