Excel Tools

Upgraded log4j to fix vulnerabilities

Added optional parameter to PagedReadExcelFile Function to support raw value extraction

Updated the DatabaseUtils to fix JNDI injection vulnerabilities.

Updated the DatabaseUtils to fix JNDI injection vulnerabilities.

• Updated the getworkbookandsheetmetadata function to gracefully handle errors like when a Strict Open XML Excel document is provided

• Added getworkbookandsheetmetadata function which returns metadata about the excel workbook and the sheets found in the workbook

Fixing bugs associated with library dependencies

Upgraded log4j-core and poi-ooxml to fix status logger error and vulnerability fix.

Updated to Java 17 (min version now 24.2) Can now use excel functions on temporary documents before they are submitted on a form

Added a new readExcelByHeaders function to enable advanced Excel data extraction based on header matching. This function supports exact, synonym, and fuzzy matching, includes regex-based header normalization, and allows for configurable matching rules to enhance data processing flexibility.

Added support for PostreSQL.

Added a new readExcelByHeaders function to enable advanced Excel data extraction based on header matching. This function supports exact, synonym, and fuzzy matching, includes regex-based header normalization, and allows for configurable matching rules to enhance data processing flexibility.

Added support for PostreSQL.

Upgraded Commons Compress Version

Added license file

Replace deprecated content APIs with modernized file management APIs. See the 23.2 Release notes here https://docs.appian.com/suite/help/23.2/Appian_Release_Notes.html#modernized-file-management-apis

• Removed previously deprecated ExportProcessMetrics Smart Service.
• Added 'Start Row' parameter to Import CSV to Database, allowing to skip a given amount of rows at the start of the file.

• Fixed bug in validatedocumentheader function

• Allow designers to map the CSV headers to database column names
• Allow designers to validate the presence of specific headers in CSV and Excel files

• Allow designers to map the Excel headers to database column names
• Allow Excel headers to begin at any cell instead of only A1

Security Updates

Security Updates

Security Updates

Added Smart Service to Encrypt an Excel workbook

• Changed dependency commons-compress from 1.20 to 1.21
• Changed dependency org.jsoup from 1.13.1 to 1.14.3

Added support to Import CSV to DB smart service for UTF-8 BOM encoded files.

• Resolved issues with Export CDT to Excel introduced in version 2.2.13 (base template would be overwritten)
• Added a new version of Export SQL to Flat File that now outputs an error should one occur during execution.

Deprecated the old Export Sql to Flat File and created a new one which outputs error messages. Previously, error messages resulted in a silent failure. The node would pass and the designer/admin would not know that there was an error with the plugin.

James Carter Reviewed Changes Here: https://github.com/appianps/ps-plugin-ExcelTools/pull/127

Resolved an export defect.

2.2.13 - Release Notes Functionality Updates: REMOVED Export servlets NEW function readexcelsheetpaging DEPRECATED readexcelsheet Added an explicit commit in Export SQL to Flat File to avoid a defect in MySQL and MariaDB Improved MergeExcelDocuments memory overhead

Added a new function readexcelpaging that replaced the previous unpaged readexcel function. Allows for safe consumption of larger Excel files.

v2.2.11

• Fixed backwards compatibility issues with 2.2.10

v2.2.10 Functionality Updates:

• Replacing deprecated APIs
• The "Import CSV to DB" smart service supports adding static columns and static values to the records provided in the CSV file
• The "Import CSV to DB" smart service does not fail if header/column list does not match the table structure. Only columns that match the table will be processed. Empty column data in CSV will result in NULL values in the corresponding fields in the table.
• The "Import CSV to DB" smart service supports datetime format in the "ISO-8601" standard
• The "Load CSV To Database" smart service has been deprecated

v2.2.9

• The “Import CSV to DB” smart service now rolls back the transaction when an exception is thrown

v2.2.8

• Fix defects with missing full file path structure when exporting data

v2.2.7 -The "Import Excel to DB" smart service no longer errors if no value is passed for the inputs “New Columns” and “New Columns Values” -The “Import CSV to DB” smart service now returns a readable error message if there are extra blank columns in the CSV file -The "Query Appian Logs" function no longer skips the first row of data is the input “hasHeader” is passed as false -The “Convert Excel To CSV” smart service has been updated to utilize the inputs “numberOfColumnsToReadInRow” and “rowNumberToReadFrom” -Documentation updates

v2.2.6

• Provides charset input for import csv to database for non UTF-8 encoded files

v2.2.5

• More memory efficient and faster Export SQL to Excel when used with MySQL
• Much faster CSV imports. To see the improvement with MySQL, configure the database connection string with rewriteBatchedStatements=true
• The following smart services have moved to the deprecated palette. It is recommended to use base product functionality where possible: Export CDT to Excel, Export SQL to Excel, Export SQL to FlatFile

v2.2.9 Reverting to 2.2.9 due to backwards compatibility issues with 2.2.10

v2.2.10 Functionality Updates:

• Replacing deprecated APIs
• The "Import CSV to DB" smart service supports adding static columns and static values to the records provided in the CSV file
• The "Import CSV to DB" smart service does not fail if header/column list does not match the table structure. Only columns that match the table will be processed. Empty column data in CSV will result in NULL values in the corresponding fields in the table.
• The "Import CSV to DB" smart service supports datetime format in the "ISO-8601" standard
• The "Load CSV To Database" smart service has been deprecated

v2.2.9

• The “Import CSV to DB” smart service now rolls back the transaction when an exception is thrown

v2.2.8

• Fix defects with missing full file path structure when exporting data

v2.2.7 -The "Import Excel to DB" smart service no longer errors if no value is passed for the inputs “New Columns” and “New Columns Values” -The “Import CSV to DB” smart service now returns a readable error message if there are extra blank columns in the CSV file -The "Query Appian Logs" function no longer skips the first row of data is the input “hasHeader” is passed as false -The “Convert Excel To CSV” smart service has been updated to utilize the inputs “numberOfColumnsToReadInRow” and “rowNumberToReadFrom” -Documentation updates

v2.2.6

• Provides charset input for import csv to database for non UTF-8 encoded files

v2.2.5

• More memory efficient and faster Export SQL to Excel when used with MySQL
• Much faster CSV imports. To see the improvement with MySQL, configure the database connection string with rewriteBatchedStatements=true
• The following smart services have moved to the deprecated palette. It is recommended to use base product functionality where possible: Export CDT to Excel, Export SQL to Excel, Export SQL to FlatFile

v2.2.10 Functionality Updates:

• Replacing deprecated APIs
• The "Import CSV to DB" smart service supports adding static columns and static values to the records provided in the CSV file
• The "Import CSV to DB" smart service does not fail if header/column list does not match the table structure. Only columns that match the table will be processed. Empty column data in CSV will result in NULL values in the corresponding fields in the table.
• The "Import CSV to DB" smart service supports datetime format in the "ISO-8601" standard
• The "Load CSV To Database" smart service has been deprecated

Documentation updates:

• For "Import CSV to DB" acceptable datetime format has been updated
• "Setup Eclipse" section updated with instructions for configuration of build path
• "Previous Release Notes" section created at the bottom of this document and previous release notes moved there
• Other minor documentation updates

v2.2.9

• The “Import CSV to DB” smart service now rolls back the transaction when an exception is thrown

v2.2.8

• Fix defects with missing full file path structure when exporting data

v2.2.7 -The "Import Excel to DB" smart service no longer errors if no value is passed for the inputs “New Columns” and “New Columns Values” -The “Import CSV to DB” smart service now returns a readable error message if there are extra blank columns in the CSV file -The "Query Appian Logs" function no longer skips the first row of data is the input “hasHeader” is passed as false -The “Convert Excel To CSV” smart service has been updated to utilize the inputs “numberOfColumnsToReadInRow” and “rowNumberToReadFrom” -Documentation updates

v2.2.6

• Provides charset input for import csv to database for non UTF-8 encoded files

v2.2.5

• More memory efficient and faster Export SQL to Excel when used with MySQL
• Much faster CSV imports. To see the improvement with MySQL, configure the database connection string with rewriteBatchedStatements=true
• The following smart services have moved to the deprecated palette. It is recommended to use base product functionality where possible: Export CDT to Excel, Export SQL to Excel, Export SQL to FlatFile

v2.2.8

• Fix defects with missing full file path structure when exporting data

v2.2.7 -The "Import Excel to DB" smart service no longer errors if no value is passed for the inputs “New Columns” and “New Columns Values” -The “Import CSV to DB” smart service now returns a readable error message if there are extra blank columns in the CSV file -The "Query Appian Logs" function no longer skips the first row of data is the input “hasHeader” is passed as false -The “Convert Excel To CSV” smart service has been updated to utilize the inputs “numberOfColumnsToReadInRow” and “rowNumberToReadFrom” -Documentation updates

v2.2.6

• Provides charset input for import csv to database for non UTF-8 encoded files

v2.2.5

• More memory efficient and faster Export SQL to Excel when used with MySQL
• Much faster CSV imports. To see the improvement with MySQL, configure the database connection string with rewriteBatchedStatements=true
• The following smart services have moved to the deprecated palette. It is recommended to use base product functionality where possible: Export CDT to Excel, Export SQL to Excel, Export SQL to FlatFile

v2.2.7 -The "Import Excel to DB" smart service no longer errors if no value is passed for the inputs “New Columns” and “New Columns Values” -The “Import CSV to DB” smart service now returns a readable error message if there are extra blank columns in the CSV file -The "Query Appian Logs" function no longer skips the first row of data is the input “hasHeader” is passed as false -The “Convert Excel To CSV” smart service has been updated to utilize the inputs “numberOfColumnsToReadInRow” and “rowNumberToReadFrom” -Documentation updates

v2.2.6

• Provides charset input for import csv to database for non UTF-8 encoded files

v2.2.5

• More memory efficient and faster Export SQL to Excel when used with MySQL
• Much faster CSV imports. To see the improvement with MySQL, configure the database connection string with rewriteBatchedStatements=true
• The following smart services have moved to the deprecated palette. It is recommended to use base product functionality where possible: Export CDT to Excel, Export SQL to Excel, Export SQL to FlatFile

v2.2.6

• Provides charset input for import csv to database for non UTF-8 encoded files

v2.2.5

• More memory efficient and faster Export SQL to Excel when used with MySQL
• Much faster CSV imports. To see the improvement with MySQL, configure the database connection string with rewriteBatchedStatements=true
• The following smart services have moved to the deprecated palette. It is recommended to use base product functionality where possible: Export CDT to Excel, Export SQL to Excel, Export SQL to FlatFile

v2.2.5

• More memory efficient and faster Export SQL to Excel when used with MySQL
• Much faster CSV imports. To see the improvement with MySQL, configure the database connection string with rewriteBatchedStatements=true
• The following smart services have moved to the deprecated palette. It is recommended to use base product functionality where possible: Export CDT to Excel, Export SQL to Excel, Export SQL to FlatFile

spreadsheet

*Version 1.8.7 - 16Oct17

• Adding better support for Oracle SPs
• New example usage application for Export SQL to Flat File

*Version 1.8.6 - 11Oct17

• Bug fix to fix 404 when accessing servlets

*Version 1.8.5 - 19jun17 Changes to the Export SQL to Excel Smart Service ONLY:

• Hyperlink creation when URLs are exported

• 13Sep17

README Update

*New in version 1.8.4 - 21jun17

• Decimals and integers formatted correctly when using the expressions:

excelreadcellsbyname() excelreadcellsbynumber() readexcelsheet()

*New in version 1.8.3 - 20mar17 Changes to the Export CDT to Excel Smart Service ONLY:

• Trigger a reevaluation of all formulas in the workbook after writing all data
• Note: Review the APACHE POI docs to view which Excel functions are supported for reevaluation and which functions are not supported
• Preserve column styling set on the Excel template for integer, decimal, and text datatypes.

*new in version 1.8.2 - 09Mar17

• Temp file cleanup in Export SQL to Excel
• Forced unattended settings on all services
• Improved CSV parsing

*new in version 1.8.1 - 24Feb17

• Improved deployment configurations

*new in version 1.8 - 30Jan17

• Query Appian Logs function

*new in version 1.7 - 09Jan17

• Read Excel Cell functions

*new in version 1.6.1 - 11Nov16

• Updated smart service ExportProcessReportToExcel to use Text and Number for static cells values

*new in version 1.6 - 6Oct16

• Added support for Weblogic application server - certain services such as readexcel would fail to execute in Weblogic.
• Updated Import CSV to Database - new delimiter input. Drag the updated node from the palette to use.

*new in version 1.4 - 13Sep16*

• changes to Export SQL data to CSV to drastically reduce memory footprint

*new in version 1.3*

• bug fixes in Parse Excel to CDT, Get Datasubset
• new smart service - Import CSV to Database

Smart services, expressions and servlets for handling data in Excel