Java - IF Function with OR using an array - Apache POI

user_18927058 0
Viewed 57

I have an array which length will change as the user enters new numbers, and I need the cell's value to be 0 when the condition is TRUE. For example the user enters: "201" and "500" into the array so the formula would be =IF(OR(N2=500, N2=201),0,F2), then =IF(OR(N3=500, N3=201),0,F3) till the final row. I came up with this code but the second OR condition it's the same because the i it's still the same

    int arr[] = {500,201};
    int i;
         
        for (i = 0; i < arr.length; i++){

int cRow2 = 1;
        int b = 2;
        Row newRow2 = firstSheet.getRow(cRow2);

while (newRow2 != null) {
            Cell cell3 = newRow2.createCell(12);
            cell3.setCellFormula("IF(OR(N"+b+"="+arr[i]+", N"+b+"="+arr[i]+"),0,F"+b+")");
            b++;
            cRow2++;
            newRow2 = firstSheet.getRow(cRow2);
        }
    }

Is there a way of doing it with the two spaces arr[0] and arr[1] but automatically as many times as the user enters numbers?

apache-poi if-statement excel-formula arrays java
1 Answers

You've to iterate over the array and create an 'entry' (NX=Value) for each value within the array. This can be done in multiple ways.

String joined = Arrays.stream( arr )
          .mapToObj( val -> String.format( "N%d=%d", current, val ) )
          .collect( Collectors.joining(",") );

In this example I am using a stream which does exactly that and joins all resulting parts by the "," character. The resulting String would be something like "N6=500,N6=201,N6=501,N6=211" which then can comfortably formatted into the formular:

String result = String.format( "IF(OR(%s),0,F%d)", joined, b );

And done. Now you've a formular for an arbitrary amount of values in you array.

My complete codeblock used for testing:

int[] arr = { 500, 201, 501, 211 };
int b = 2;

while( b < 10 )
{
    int current = b;
    String joined = Arrays.stream( arr )
        .mapToObj( val -> String.format( "N%d=%d", current, val ) )
        .collect( Collectors.joining(",") );
    String result = String.format( "IF(OR(%s),0,F%d)", joined, b );
    b++;
    System.out.println(result);
}

In case you're unfamilar with Java-streams yet you can simply replace the stream with this foreach-loop:

StringJoiner joiner = new StringJoiner( "," );
for( int val : arr )
{
    String format = String.format( "N%d=%d", current, val );
    joiner.add( format );
}
String joined = joiner.toString();
user_81817500
Related