Excel Tools

Hi Mike ,

We facing the below error message , while uploading the excel file.

below is the formula we are having for one column

=IF(COUNTBLANK(B3:H3)=7,"-",IF(B3="","Subprocess,","")&IF(IFERROR(XLOOKUP(TRUE,EXACT(B3,'Manual Reporting Actions'!$B$1:$B$10),
'Manual Reporting Actions'!$B$1:$B$10,1),1)=1,"SubProcess,","")&IF(IFERROR(XLOOKUP(TRUE,EXACT(C3,'Manual Reporting Actions'!$C$1:$C$10),
'Manual Reporting Actions'!$C$1:$C$10,1),1)=1,"Action,","")&IF(XLOOKUP(C3,'Manual Reporting Actions'!$C$1:$C$10,'Manual
Reporting Actions'!$B$1:$B$10,",SubProcess not matched with Action,",0)=wft_manal_reporting_details!B3,"",",
Subprocess Not Matched with Action")&IF(D3="","Company,","")&IF(IFERROR(XLOOKUP(TRUE,EXACT(D3,'Manual Reporting Actions'!$E$1:$E$10),
'Manual Reporting Actions'!$E$1:$E$10,1),1)=1,"Company,","")&IF(IFERROR(VLOOKUP(E3,'Manual Reporting Actions'!$H$1:$H$10,1,0),1)=1,"Site,","")&
IF(AND((F3>=IF(TEXT(TODAY(),"DD")>=6,EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),-2)+1)),(F3<=IF(TEXT(TODAY(),"DD")>6,
TODAY(),DATE(TEXT(TODAY(),"YYY"),TEXT(TODAY(),"MM"),6)))),"","Start Date,")&IFERROR(IF(ISNUMBER(MONTH(G3)),"","End Date,"),"End Date,")
&IFERROR(IF(AND(F3<=G3,G3<=TODAY()),"","End Date,"),"End Date,")&IF(AND((F3>=EOMONTH(TODAY(),-2)+1)),"",
"Start Date,")&IF(AND((G3>=EOMONTH(TODAY(),-2)+1)),"","End Date,")&IF(ISNUMBER(H3),"",",TotalVolume"))

from the logs we came to know that XLOOKUP is not supported by this plugin, check the below logs.

2022-02-25 04:25:02,859 [Appian AppianServerThreadPoolProvider 9870672] ERROR com.appiancorp.ps.exceltools.expression.ReadExcelFileFunction - Error evaluating cell wft_manal_reporting_details!A2

org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell wft_manal_reporting_details!A2

        at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:347)

com.appiancorp.object.AppianThreadFactory$LogRunnable.run(AppianThreadFactory.java:89)

        at java.lang.Thread.run(Thread.java:748)

Caused by: org.apache.poi.ss.formula.eval.NotImplementedFunctionException: _xlfn.XLOOKUP

        at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:56)

        at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)

        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:514)

Hi Mike ,

We facing the below error message , while uploading the excel file.

below is the formula we are having for one column

=IF(COUNTBLANK(B3:H3)=7,"-",IF(B3="","Subprocess,","")&IF(IFERROR(XLOOKUP(TRUE,EXACT(B3,'Manual Reporting Actions'!$B$1:$B$10),
'Manual Reporting Actions'!$B$1:$B$10,1),1)=1,"SubProcess,","")&IF(IFERROR(XLOOKUP(TRUE,EXACT(C3,'Manual Reporting Actions'!$C$1:$C$10),
'Manual Reporting Actions'!$C$1:$C$10,1),1)=1,"Action,","")&IF(XLOOKUP(C3,'Manual Reporting Actions'!$C$1:$C$10,'Manual
Reporting Actions'!$B$1:$B$10,",SubProcess not matched with Action,",0)=wft_manal_reporting_details!B3,"",",
Subprocess Not Matched with Action")&IF(D3="","Company,","")&IF(IFERROR(XLOOKUP(TRUE,EXACT(D3,'Manual Reporting Actions'!$E$1:$E$10),
'Manual Reporting Actions'!$E$1:$E$10,1),1)=1,"Company,","")&IF(IFERROR(VLOOKUP(E3,'Manual Reporting Actions'!$H$1:$H$10,1,0),1)=1,"Site,","")&
IF(AND((F3>=IF(TEXT(TODAY(),"DD")>=6,EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),-2)+1)),(F3<=IF(TEXT(TODAY(),"DD")>6,
TODAY(),DATE(TEXT(TODAY(),"YYY"),TEXT(TODAY(),"MM"),6)))),"","Start Date,")&IFERROR(IF(ISNUMBER(MONTH(G3)),"","End Date,"),"End Date,")
&IFERROR(IF(AND(F3<=G3,G3<=TODAY()),"","End Date,"),"End Date,")&IF(AND((F3>=EOMONTH(TODAY(),-2)+1)),"",
"Start Date,")&IF(AND((G3>=EOMONTH(TODAY(),-2)+1)),"","End Date,")&IF(ISNUMBER(H3),"",",TotalVolume"))

from the logs we came to know that XLOOKUP is not supported by this plugin, check the below logs.

2022-02-25 04:25:02,859 [Appian AppianServerThreadPoolProvider 9870672] ERROR com.appiancorp.ps.exceltools.expression.ReadExcelFileFunction - Error evaluating cell wft_manal_reporting_details!A2

org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell wft_manal_reporting_details!A2

        at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:347)

com.appiancorp.object.AppianThreadFactory$LogRunnable.run(AppianThreadFactory.java:89)

        at java.lang.Thread.run(Thread.java:748)

Caused by: org.apache.poi.ss.formula.eval.NotImplementedFunctionException: _xlfn.XLOOKUP

        at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:56)

        at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)

        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:514)