UiPathTeam.XLExcel.Activities 1.0.6758.28946

Extra Large Excel

UiPathTeam.XLExcel.Activities

Installation

UiPath Studio

Package Manager -> Right Click -> Configure Sources -> Add https://gallery.uipath.com/api/v2.

Objectives:

  • Providing a robust and facile manner of processing very large excel files in the background and without relying on COM Interop.
  • The implementation of custom activities that will give the user the last row of the excel file (extremely useful when trying to process a file in chunks). Currently trying to read a file in chunks is impossible without knowing the number of rows and columns in the sheet.
  • The implementation of an Application Scope activity that will allow the grouping of the custom activities in this package.
  • Providing activities that are similar in structure and in the type of parameters to the current Excel application

Observation and limitations:
The activity will not keep track of the formats used to display values in the Excel appliation. For example, a cell with a value of 43006 formatted as date will be displayed as 9/28/2017 in Excel, but our activity will still retrieve it as: 43006.
Files in which the row starts with the index 0 (this index can be found in the xml markup of the row) will not be processed. The row number also needs to be under 1048576 (the maximum number of rows in an Excel Sheet). If a row number outside of this interval is found, the application will throw an exception. This limitation is also available for the Read Range in the Workbook activities package.
This package is not compatible with .xls files or .csv files. Only .xlsx files can be read with this activity. Reading Large XLS files can be done in conjunction with the Conversions Package which will convert the file to an xlsx file, thus making it compatible with this package.
Some very minor differences when deciding where a range ends between the standard Excel processing activities and mine

Technical Approach:
The files will be processed in the background using the Open XML SDK library and the SAX (Simple API for XML) approach which instead of reading the entire document and exposing its DOM, will independently read the XML files that comprise the Excel document.

Read Range

Principles

This custom activity reads a Range of cells from an excel Sheet has.
This custom activity achieves its goal by using the Open XML SDK framework and the OpenXmlReader class to parse the File in a SAX like approach. To get the number of rows, we simply parse the file and count the rows.
The custom activity is created by simply inheriting the CodeActivity class from the Windows Workflow Foundation framework.We define the logic of the function by implementing the Execute method and we define the input and output of the activity by fields of the InArgument and OutArgument types.
This activity can only be placed inside an XL Excel Application Scope activity (not necessarily directly inside, but one of its parents must be an XL Excel Application Scope).

Parameters

Input

  • FilePath (string) - received from the Parent XL Excel Application Scope activity
  • AddHeaders(bool)
  • SheetName(string)
  • Range(string) - must represent a valid Excel Range, otherwise, an exception is thrown

Output

  • Result(DataTable)

Get Number of Rows

This custom activity returns the number of rows an excel Sheet has.
This activity can only be placed inside an XL Excel Application Scope activity.

Parameters
Input

  • FilePath (string) - received from the Parent XL Excel Application Scope activity
  • SheetName(string)

Output

  • NumberOfRows (int)

Example
This activity is easy to use and the only parameter it needs is the Sheet Name. The file path parameter will be received from the parent: XL Excel Application Scope and the result will be an integer.
The Sheet Name can be found in the Properties panel of the activity:

Objectives:

  • Providing a robust and facile manner of processing very large excel files in the background and without relying on COM Interop.
  • The implementation of custom activities that will give the user the last row of the excel file (extremely useful when trying to process a file in chunks). Currently trying to read a file in chunks is impossible without knowing the number of rows and columns in the sheet.
  • The implementation of an Application Scope activity that will allow the grouping of the custom activities in this package.
  • Providing activities that are similar in structure and in the type of parameters to the current Excel application

Observation and limitations:
The activity will not keep track of the formats used to display values in the Excel appliation. For example, a cell with a value of 43006 formatted as date will be displayed as 9/28/2017 in Excel, but our activity will still retrieve it as: 43006.
Files in which the row starts with the index 0 (this index can be found in the xml markup of the row) will not be processed. The row number also needs to be under 1048576 (the maximum number of rows in an Excel Sheet). If a row number outside of this interval is found, the application will throw an exception. This limitation is also available for the Read Range in the Workbook activities package.
This package is not compatible with .xls files or .csv files. Only .xlsx files can be read with this activity. Reading Large XLS files can be done in conjunction with the Conversions Package which will convert the file to an xlsx file, thus making it compatible with this package.
Some very minor differences when deciding where a range ends between the standard Excel processing activities and mine

Technical Approach:
The files will be processed in the background using the Open XML SDK library and the SAX (Simple API for XML) approach which instead of reading the entire document and exposing its DOM, will independently read the XML files that comprise the Excel document.

Read Range

Principles

This custom activity reads a Range of cells from an excel Sheet has.
This custom activity achieves its goal by using the Open XML SDK framework and the OpenXmlReader class to parse the File in a SAX like approach. To get the number of rows, we simply parse the file and count the rows.
The custom activity is created by simply inheriting the CodeActivity class from the Windows Workflow Foundation framework.We define the logic of the function by implementing the Execute method and we define the input and output of the activity by fields of the InArgument and OutArgument types.
This activity can only be placed inside an XL Excel Application Scope activity (not necessarily directly inside, but one of its parents must be an XL Excel Application Scope).

Parameters

Input

  • FilePath (string) - received from the Parent XL Excel Application Scope activity
  • AddHeaders(bool)
  • SheetName(string)
  • Range(string) - must represent a valid Excel Range, otherwise, an exception is thrown

Output

  • Result(DataTable)

Get Number of Rows

This custom activity returns the number of rows an excel Sheet has.
This activity can only be placed inside an XL Excel Application Scope activity.

Parameters
Input

  • FilePath (string) - received from the Parent XL Excel Application Scope activity
  • SheetName(string)

Output

  • NumberOfRows (int)

Example
This activity is easy to use and the only parameter it needs is the Sheet Name. The file path parameter will be received from the parent: XL Excel Application Scope and the result will be an integer.
The Sheet Name can be found in the Properties panel of the activity:

Release Notes

First Published Version of the Package

Version History

Version Downloads Last updated
1.0.6814.30102 29,895 8/28/2018
1.0.6758.28946 (current version) 8,301 7/3/2018