Excel Tools

Hi Zach, if possible can you provide any solution for the issue that we are facing with Excel sheet while uploading.

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:155) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:541) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:275)

• Added Smart Service to Encrypt an Excel workbook

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)

We are currently using this plug-in within our application. As per our latest Appian Health Check Report, it is flagging that this plugin is using either removed, private or depreciated APIs and risk breaking during an upgrade or between hotfixes. Can you please let me know if the plugin will work for Appian version 21.4. Below is the text from our Health Check Report

com.appiancorp.ps.exceltools (com.appiancorp.ps.exceltools) references private and deprecated Appian APIs
[private] com.appiancorp.common.xml.XmlBeanSerializer.populateObject(java.lang.Object, org.w3c.dom.Node)
[private] com.appiancorp.services.ASLIdentity.getIdentity()
[private] com.appiancorp.type.TypeRef.getId()
[deprecated] com.appiancorp.services.ServiceContext.getIdentity()
[deprecated] com.appiancorp.suiteapi.cfg.ConfigurationLoader.initializeConfigurations()
[deprecated] com.appiancorp.suiteapi.common.ServiceLocator.getContentService(com.appiancorp.services.ServiceContext)
[deprecated] com.appiancorp.suiteapi.common.ServiceLocator.getGroupService(com.appiancorp.services.ServiceContext)
[deprecated] com.appiancorp.suiteapi.common.ServiceLocator.getProcessAnalyticsService2(com.appiancorp.services.ServiceContext)
[deprecated] com.appiancorp.suiteapi.common.ServiceLocator.getProcessDesignService(com.appiancorp.services.ServiceContext)
[deprecated] com.appiancorp.suiteapi.common.ServiceLocator.getProcessExecutionService(com.appiancorp.services.ServiceContext)
[deprecated] com.appiancorp.suiteapi.common.ServiceLocator.getTypeService(com.appiancorp.services.ServiceContext)
[deprecated] com.appiancorp.suiteapi.common.ServiceLocator.getUserService(com.appiancorp.services.ServiceContext)
[deprecated] com.appiancorp.suiteapi.content.ContentService.getChildren(java.lang.Long, com.appiancorp.suiteapi.content.ContentFilter, java.lang.Integer)
[deprecated] com.appiancorp.suiteapi.process.GenericTypedVariable.<init>()
[deprecated] com.appiancorp.suiteapi.process.GenericTypedVariable.setType(java.lang.Long)
[deprecated] com.appiancorp.suiteapi.process.GenericTypedVariable.setValue(java.lang.Object)
[deprecated] com.appiancorp.suiteapi.process.analytics2.ProcessReport.copy()

Hi Mike. Looks like any errors during the Import Excel to Database smartservice leave a hung database connection?

If my file generates an error I can't get back to the cloud DB until it times out sometime later...

log4j is not bundled in the plugin - it uses the lib packaged in the base product.

100% no, but there isn't anything to prevent it from attempting. I suspect xlsm would be read just fine, xlsb probably will not be. Have you tried and are experiencing issues?

• Security Updates

Hi, does anyone know 100% if .xlsb or .xslm files can be imported with his plugin?

OK resolved. for benefit of others I just needed to add an additional column (same type eg. String), knew it was something simple!