SheetKraft Functionality

This section describes the functionality available in the SheetKraft Excel addin. This functionality is used to setup the logic of individual activities in a business process that needs to be automated. SheetKraft enhances the Excel environment by adding

Addin Functionality

This section describes the functionality available in the SheetKraft Excel addin. This functionality is used to setup the logic of individual activities in a business process that needs to be automated. SheetKraft enhances the Excel environment by adding

1. A ribbon to provide user interface for various functionalities

   

2. Worksheet functions (UDFs) that can be used in cell formulas. Most formulas are inserted by the buttons in the ribbon shown above.

   

Importing Data {.my-class}

SheetKraft can import data from

• Text files

  1. CSV (comma separated values) files
  2. Tab delimited files
  3. Space delimited files
  4. Files with a custom delimiter (such as |)
  5. Fixed width files
• Excel files

• Relational databases

  1. Tables / Views with filter criteria
  2. Stored Procedures
  3. Custom SQL queries
     SheetKraft has been tested with SQL Server, MySQL, Oracle, and Access databases. Connectivity to databases via a generic ODBC or OLEDB connector is also supported.
• HTML files
• XML files
• JSON files
  UI is available for text files, excel files and relational databases.Files in a directory can be enumerated to source data dynamically. Files can be downloaded from urls constructed via formulas before they are imported.The data import functions can work on multiple files to collate data in one step. The collation handles differences in column order in different files correctly.

Simple Calculations

• Extracting values from text using patterns (Regular Expressions)
• Converting text to dates reliably by specifying formats
• Date operations (such as finding the last date in a quarter)
• AND and OR functions that deal with errors correctly

Data Operations

• Filtering on arbitrary criteria
• Repeating a formula for every row in a range
• Reordering columns
• Removing duplicates
• Partitioning data by (possibly multiple) columns and picking top n rows from each partition according to sort criteria
• Aggregating / pivoting
• Multi-column lookups with support for advanced matching conditions
• Generating all combinations (cross-product) of multiple values
• Reshaping / transposing data on rows and columns
• Combining data from multiple ranges to form a single range

Export Operations

SheetKraft can export data into various formats

• Text files (CSV, Tab delimited, or with a custom delimiter)
• Excel files (with support for creating multiple sheets or workbooks by partitioning data)
• SQL Server database (with support for inserting, replacing, updating, and overwriting batches of rows)
• Word files (based on specially created templates, limited to Word 2013 or above)
• XML files (based on specially created templates)
• Email body (plain text or based on specially created templates)
• Email attachments
• Exporting to the application database is the primary means for sharing data that will be consumed by multiple activities.

Format Operations

SheetKraft can format ranges on worksheets dynamically by copying the formats from a source range and repeating them over the destination range. This can be used along with the Export Worksheets functionality to generate nicely formatted reports. Some features such as merging cells dynamically and adding tables one below the other are not currently supported. These are planned for future releases.

Charts and Pivot Tables

SheetKraft can bind Excel charts and pivot tables to dynamically sized ranges. This can be used along with Export Worksheets functionality to generate charts and pivot tables in reports. When exporting chart sheets or pivot tables, sheets containing the source data must also be exported. These sheets may be hidden if desired.

Custom Functions

SheetKraft allows users to define custom functions based on a chain of logic in a workbook by simply identifying the input cells/ranges and the result range. These custom functions are usable within the workbook. This functionality can be used to repeat a chain of calculations for multiple inputs by setting up a sample calculation for one of the inputs.

Run Book

This button runs all the formulas in the workbook in the correct sequence. It delivers the key value of SheetKraft � running an entire sequence of steps in one click. While the automation is running, a Log window is displayed that shows any errors, warnings or messages generated.

Run Formula

This button runs a single formula (without running any precedents or dependents) and updates its results. This is useful when building the automation to quickly see the result of a local change without having to run the entire workbook (as this could take time).

Formula Explorer

The Formula Explorer button opens a user interface to modify an existing SheetKraft formula. Each SheetKraft function has a dedicated interface to make changes specific to that function. The formula explorer can also be used on normal Excel formulas.

Trace Value

This button opens a user interface that displays the precise source of the value in the current cell. This makes it easier to trace a value backwards through the calculation chain. This feature is under development and is supported for the following functions: StackRows, FillDown, SelectRows, VLookup, PivotTable. These functions make up almost all the data flow in a workbook.

Flow Chart

The flow chart shows a graphical representation of the data flow in a workbook with SheetKraft formulas shown as boxes. Clicking the box for a formula selects the corresponding range on the workbook where the formula is entered. This makes it easier to navigate the workbook. The formula explorer can also be launched for each represented formula from the flow chart.

Database Explorer

The database explorer allows execution of arbitrary SQL statements on a database. It provides an interface to infer the structure of a database table that can be used to store some sample data. The structure can be modified if necessary. The SQL needed to create a table with the chosen structure can then be generated. This SQL can be modified if required.

Login

This functionality can be used to login to a configured SheetKraft website. Once logged in, activities on the desktop can be synced with activities on the server.

Manage Activities

This screen provides a unified view of the activities on the desktop and on the server (if logged in). It can be used to download activities from the server, open and edit them within Excel and publish them back to the server directly from Excel. This functionality is under active development and will be improved significantly in future releases.

Publish Activity

This screen provides an interface to define the inputs and outputs of an activity and to configure the display of the activity on the SheetKraft website.

Website Functionality

SheetKraft has a website that provides a web interface to running the activities set up from Excel. It enforces user rights and provides access to prior runs of the activities. Some of the functionality described below will be inaccessible to some users because of limited user rights.

Activities Page

This page shows all the activities that the logged in user can view based on access rights. The activities are shown in a hierarchy of up to 3 levels. The user can see the last time at which each activity was run and whether it succeeded or failed at a glance. Clicking on an activity takes the user to a page where the user can provide inputs for the activity (if any) and start it. An options icon at the right of each activity provides access to more actions regarding the activity such as viewing the last run, viewing all previous runs, viewing older versions of the activity, and editing the activity.

New Activity Page

This page lets the user create a new activity by uploading a file published from the Excel addin. In most cases, the activity can be published directly from the addin itself and this page is only a fallback for cases when connectivity from the addin is broken for some reason.

Edit Activity Page

This page lets the user edit the configuration of an existing activity. The position of the activity in the hierarchy of activities, its description, pre-configured values for inputs, linkages of the activity to other activities, etc. can be edited from this page.

Run Activity Page

Each activity has its own page where the user can provide inputs and start the activity. This page displays the time and success status of the last run of the activity. The contents of this page depend on the inputs that were configured for the activity. Clicking the start button at the bottom of the page takes the user to a status page where the user can monitor the activity run and cancel it if desired.

Activity Status Page

Each run of an activity has a status page that shows the inputs, outputs and any messages generated during the run. If the run is currently in progress, a progress bar and cancel button is shown.

Activity Run History Page

Each activity has a run history page where the user can see details of previous runs in a tabular format. This page shows the status, run date and time, user name, and inputs and outputs for each run. The bottom of the page shows the space occupied by the runs and their input and output files older than a chosen point in time. These can be purged to clear space in the database.

Activity Version History Page

Each activity has a page that shows the history of all versions of the activity along with the change descriptions entered at the time of creating the versions. Older versions can be downloaded and restored (by re-uploading them) if necessary.

Checklists Page

This page shows all checklists that the logged in user can access. A checklist is a list of activities in a predefined sequence that can be run together instead of running them individually. Inputs of the individual activities can be mapped to inputs of the checklist so that the same inputs need not be entered separately for each activity. Clicking on a checklist takes the user to a page where the user can run the activities.

Add Checklist Page

This page lets the user create a checklist of activities and map the inputs of the activities to the inputs of the checklist.

Users Page

This page lists all the users. Users can be local (created and managed within the SheetKraft database) or linked to Active Directory. Admin users can edit the profiles and reset passwords of local users, or enable or disable any other user (both local and AD).

New User Page

This page lets an admin user create new user accounts - either local or linked to Active Directory. If the account is linked to Active Directory, the password and profile details are stored in Active Directory and cannot be edited within the SheetKraft website.

Roles Page

This page lists existing roles and allows creation of new roles. A user can be assigned multiple roles simultaneously. Roles can be enabled or disabled. Disabling a role prevents any access rights being granted to users through that role. Clicking on a role takes the user to a page where the assignment of users to that role can be managed.

Manage Users in Role Page

Each role has a page that lists all users and indicates which users are assigned to that role. Users can be selected and included or excluded from the role.

Rights Page

This page lists all the rights that may be granted or denied to users or roles. The rights are categorized by the resource that is controlled by that right and further categorized by the scope at which the right can be granted or denied. Clicking the right takes the user to a screen listing all resources at the applicable scope. Clicking on one of these resources takes the user to a screen listing all users and roles. The right can then be granted or denied to one or more users or roles or existing grants or denials can be reset by deleting them.

Security Matrix Page

This page lists all the rights granted or denied to each user at each applicable scope either directly or indirectly via roles or via grants to other related rights. Clicking on any row takes the user to a page that shows the underlying grants or denials to roles or related rights that result in the user having an effective grant or denial.

Security Actions Page

This page lists all the Security related actions performed by various users. The actions reported here include creating a role / deleting / enabling / disabling roles, enabling / disabling users, including or excluding users from roles, granting / denying / deleting rights to users or roles.