So entfernen Sie @（implizite Schnittpersonal）, während Sie Excel -Formel von Java POI schreiben ⇐ Java

[Anonymous]

Viele @ Symbole werden in der Formel generiert, was zu Fehlern in der Formel und dem Nichtberechnen der Normalität führt.

Code: Select all

String formula = getDosFormula(row, column, ctx.getFlatDynamicHeaderList());

writeCell(excelWriter, column, row, new FormulaCellValue(formula), headCellStyle);

public static String getDosFormula(int row, int column, List flatDynamicHeaderList) {

String[] sRefs = new String[13];
String[] daysRefs = new String[13];

int addJumpMonthLength = 0;
for (int i = 0; i < 13; i++) {

int sCol = column + 1 + 2 + i * 5 + addJumpMonthLength;
int daysCol = column + 1 + 5 + i * 5 + addJumpMonthLength;
if (flatDynamicHeaderList.get(sCol).contains(PsiMajorApplianceHeaderEnum.DEMAND_S.getName())) {
addJumpMonthLength += 5;
sCol = sCol + 5;
daysCol = daysCol + 5;
}

sRefs[i] = getExcelCol(sCol) + (row + 1);
daysRefs[i] = getExcelCol(daysCol) + (row + 1);
}

String sList = "CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13}," + String.join(",", sRefs) + ")";
String daysList = "CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13}," + String.join(",", daysRefs) + ")";
String sumS = "SUM(" + String.join(",", sRefs) + ")";
String sumDays = "SUM(" + String.join(",", daysRefs) + ")";
String curCell = getExcelCol(column - 1) + (row + 1);
int numberOfWeeks = sRefs.length;

String letFormula = String.format(
"LET(" +
"s_list,%s," +
"days_list,%s," +
"cumulative_S,SCAN(0,s_list,LAMBDA(a,v,a+v))," +
"total_days,SUM(days_list)," +

"break_week_idx,IFERROR(MATCH(TRUE, cumulative_S >= %s, 0), %d)," +

"IF(break_week_idx > %d," +
"ROUNDUP(total_days,0)," +

"LET(" +

"days_before_break, IF(break_week_idx = 1, 0, SUM(INDEX(days_list, SEQUENCE(break_week_idx - 1))))," +

"inv_start_of_break_week, %s - IF(break_week_idx > 1, INDEX(cumulative_S, break_week_idx - 1), 0)," + // %s 是 initialInvRef

"sales_in_break_week, INDEX(s_list, break_week_idx)," +
"days_in_break_week, INDEX(days_list, break_week_idx)," +

"partial_days_in_break, IFERROR(IF(sales_in_break_week = 0, 0, inv_start_of_break_week / sales_in_break_week * days_in_break_week), 0)," +

"ROUNDUP(days_before_break + partial_days_in_break,0)" +
")" +
")" +
")",
sList, daysList, curCell, numberOfWeeks + 1, numberOfWeeks, curCell
);

String formula = String.format(
"IF(%s > %s,ROUNDUP(%s/(%s/%s),0),%s)",
curCell, sumS, curCell, sumS, sumDays, letFormula
);

return "=" + formula;
}

endgültige Formel:

Code: Select all

=@IF(BA4 > SUM(BE4,BJ4,BO4,BY4,CD4,CI4,CN4,CS4,DC4,DH4,DM4,DR4,DW4),ROUNDUP(BA4/(SUM(BE4,BJ4,BO4,BY4,CD4,CI4,CN4,CS4,DC4,DH4,DM4,DR4,DW4)/SUM(BH4,BM4,BR4,CB4,CG4,CL4,CQ4,CV4,DF4,DK4,DP4,DU4,DZ4)),0),LET(s_list,CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13},BE4,BJ4,BO4,BY4,CD4,CI4,CN4,CS4,DC4,DH4,DM4,DR4,DW4),days_list,CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13},BH4,BM4,BR4,CB4,CG4,CL4,CQ4,CV4,DF4,DK4,DP4,DU4,DZ4),cumulative_S,SCAN(0,s_list,LAMBDA(a,v,@a+@v)),total_days,SUM(days_list),break_week_idx,IFERROR(MATCH(TRUE,@ cumulative_S >= BA4, 0), 14),IF(@break_week_idx > 13,ROUNDUP(@total_days,0),LET(days_before_break, IF(@break_week_idx = 1, 0, SUM(INDEX(days_list,@ SEQUENCE(@break_week_idx - 1)))),inv_start_of_break_week, BA4 -@ IF(@break_week_idx >  1, INDEX(cumulative_S,@ break_week_idx - 1), 0),sales_in_break_week, INDEX(s_list,@ break_week_idx),days_in_break_week, INDEX(days_list,@ break_week_idx),partial_days_in_break, IFERROR(IF(@sales_in_break_week = 0, 0,@ inv_start_of_break_week /@ sales_in_break_week *@ days_in_break_week), 0),ROUNDUP(@days_before_break +@ partial_days_in_break,0)))))

Ich frage mich, warum einige einzelne Elemente auch mit @ -Symbol (implizitem Schnittpunkt Operator) hinzugefügt werden.>