Excel Tools

Provides the following custom smart services and functions for handling data in Excel.  An example usage application for Export SQL to Flat File is also included.

Smart Services included are:

  • Export SQL data to Excel
  • Export SQL data to CSV
  • Merge Excel Documents
  • Import CSV to Database
  • Import Excel to Database
  • Load CSV to Database
  • Convert HTML to CSV  
  • Convert Excel to CSV

Functions included are:

  • Read Excel File Paged
  • Read Excel Cell by Name
  • Read Excel Cell by Number
  • Query Appian Logs
  • Export Datasubset to Base64

 

NOTE: When deploying this plug-in, please delete all old versions of the ExportableDataSubset CDT AND update your model to point to the new version {urn:com:appian:ps:excel:types}ExportableDataSubset

Anonymous
  • Hi mike, we're using excel tools plug in but when we're trying use the smart services or functions with a .xls file, we get the following error "OOXML (Office Open XML) file". Can u help us?

  • Hi, While working on 'Import Excel to Database', if I save excel (open excel file and file-> save, appian recognized it and process it but if I just open it but did not File-> Save and upload it, this smart service just ignore/bypass it. Anyone know or seen this issue please? if yes please help. 

  • Hi,

    is there a plan to upgrade the plugin to be compatible with the version 22.1 +.

    Regards

  • Hi

    Using Merge Excel documents has been working very successfully. Now shows an error -

    Merge Excel Documents A part with the name '/docProps/core.xml' already exists : Packages shall not contain equivalent part names and package implementers shall neither create nor recognize packages with equivalent part names. [M1.12].

    Any ideas as to cause and how to overcome this error?

    Regards

  • Hello Mike 

    we are trying to read an excel file but we get this error.

    ERROR com.appiancorp.ps.exceltools.util.ExcelHelperUtils - Zip bomb detected! The file would exceed the max. ratio of compressed file size to the size of the expanded data.
    This may indicate that the file is used to inflate memory usage and thus could pose a security risk.
    You can adjust this limit via ZipSecureFile.setMinInflateRatio() if you need to work with files which exceed this limit.
    Uncompressed size: 3092989, Raw/compressed size: 30929, ratio: 0.010000
    Limits: MIN_INFLATE_RATIO: 0.010000, Entry: xl/styles.xml
    java.io.IOException: Zip bomb detected! The file would exceed the max. ratio of compressed file size to the size of the expanded data.
    This may indicate that the file is used to inflate memory usage and thus could pose a security risk.
    You can adjust this limit via ZipSecureFile.setMinInflateRatio() if you need to work with files which exceed this limit.
    Uncompressed size: 3092989, Raw/compressed size: 30929, ratio: 0.010000
    Limits: MIN_INFLATE_RATIO: 0.010000, Entry: xl/styles.xml

    We get this error using all the 3 function available with the plug in

    • Read Excel File Paged
    • Read Excel Cell by Name
    • Read Excel Cell by Number

    This error occur with the latest apache poi when in the excel file there are many rows/columns with the same content, and in my excel file i have this issue.

    could be possible to set: 

    ZipSecureFile.setMinInflateRatio(0); in oprder to avoid this error or some other fixing.

    Let me know if you need an exemple of my excel to read.

    Thank You

    Gianluca

  • Hi Folks,

    I'm getting below details in Heath Check report for Version 20.4

    Excel Tools (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.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()

     Can someone please help with the solution / suggestions for resolving this?

    Thanks,

    Aprajita

  • 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)

  • v2.2.18 Release Note
    • 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)