Excel Advanced

Contents

1. Checkpoints
2. No functions can be inserted
3. Tips when reading from an Excel file
4. Tips when writing to an Excel file
5. How to use the Pivot option
6. Return Values
7. Data Only and Allow Overwrite options

8. Other useful functions

1. Checkpoints

First here are the checkpoints when using Excel Advanced plugin!

1. Main usage/purpose of this plugin is to “READ”. It would READ from an existing excel and store data internal to the Bot as CSV.
2. This plugin is best when reading a “table” section only from an Excel file and prepare data for further processing.
3. Caution --- Return Value is recommended to be either CSV or FILE. String will return the data itself, not the file path.
4. WRITE menu is secondary but useful.
5. When WRITE, the target Excel file needs not to exist but unless you need to write to an existing Excel file, we recommend using Return Value (.csv) and then convert it to .xlsx with other plugins such as File Conv.

THIS PLUGIN IS DESIGNED TO “READ” BEFORE “WRITE”.

Be careful! Return-Value stores data, not file path even when you “WRITE”.

2. No functions can be inserted.

3. Tips when reading from an Excel file

• You need a source file either xlsx or csv.
• You can specify sheet and range.
• You will read the data and store it in forms of String, CSV (Internal to bot), or File (usually CSV or TXT, external file location).

4. Tips when writing to an Excel file

• You need a source file either xlsx or csv.
• You can specify sheet and range to read from.
• You specify a target file either xlsx or csv.
• If target file is not found, the Excel Advanced plugin will automatically create the file.
• If target file exists, the Excel Advance plugin will overwrite the specified cells.
• The data will be simultaneously and store it in forms of String, CSV (Internal to bot), or File (usually CSV or TXT, external file location).

More tips about the Plugin [Return Value] types.

5. How to use the Pivot option

•  The settings look like this.

• If you want an internally stored CSV, the setting will look like this.

Then your plugin variable {{test.A2(4)}} will produce E2.

Your row 1 will become headers. Row 2 to 5 are the data.

Plugin variable does not support 1 row CSV.

6. Return Values

• Result Type : String

Variable name 
String will be stored internally.
You must set user-variable and assigned it here. (not an array)

• Result Type : CSV
  

Group name
CSV will be stored internally.
No need for pre-setting the user-variable. Group name becomes the variable group name. Columns and rows are indicated as in example below.

{{group name.A(1)}}

Note: Column is uppercase only.

• Result Type : File
  

Valuable name
In case, you desire to store the File path in a preset user variable, you can specify it here.

File path
File will be stored externally. It is either csv or txt. Full path need to be specified at File path. File does not need to be present. If the file not found, it would be generated automatically. If it is present, it would be overwritten.

7. “Data Only” and “Allow Overwrite” options

1. Data only

By checking this checkbox, all functions (formulas) will be deleted and the new sheet/book will only contain data (values).
WARNING! All functions and formulas will be deleted and lost forever.

2. Allow Overwrite
By checking this checkbox, the plugin will overwrite existing sheet/book and save it. The original sheet/book will be lost forever.
WARNING! This function can result in loss of functions/formulas.

8. Other useful functions

1. Find String

• • Input
    Required
          - .xlsx or .csv file to read from.
    
    Optional
         - Sheet name
         - Cell ID/Range
    
    

• • Output/Return Value
    Cell ID/Cell IDs in CSV format.

2. Pivot

• • Input
    Required
          - .xlsx or .csv file to read from
          - .xlsx or .csv file to write to
    
    Optional
         -  Sheet name of the source file.
         -  Cell ID/Range of the source file.
         -  Sheet name of the target file.
         -  Cell ID/Range of the target file.
    
    Even when you are writing the result to the same file, you must declare the target file.
    

• • Output/Return Value
    File path of the target file.

3. Show Data Range

• • Input
    Required
         - .xlsx or .csv file to read from

 
     Optional
          -  Sheet name
          -  Cell ID/Range

• • Output/Return Value
    Top left cell ID and bottom right cell ID that contains some data on the sheet.

4. Clear Cell

• • Input
    Required
         - .xlsx or .csv file to read from
    
    Optional
         -  Sheet name
         -  Cell ID/Range

• Output/Return Value
  Top left cell ID and bottom right cell ID that contains some data on the sheet.

Return Code