``RPA.Excel.Application`` Add new keywords

[mikahanninen]

• Library RPA.Excel.Application:

  • Add new keyword Create Pivot Field for creating pivot fields for the pivot table.
  • Add new keyword Create Pivot Table for creating pivot tables in a spreadsheet.
  • Add new keyword Create Table for creating a table in a spreadsheet.
  • Add new keyword Find for finding a value in a range.
  • Add new keyword Get Pivot Tables for getting pivot tables in a spreadsheet.
  • Add new keyword Get Range for getting a range in a spreadsheet.
  • Add new keyword List Tables for listing tables in a spreadsheet.

• For all *RPA..Application** libraries: Add new utility keyword for Robot Framework syntax Set Object Property

• Security dependency fix given the bump of cryptography package to version 42.0.2.

Some usage examples (in Python).

from robocorp.tasks import task
from RPA.Tables import Tables
from RPA.Excel.Application import Application

@task
def main():
    excel = Application()
    excel.open_application(visible=True)
    excel.open_workbook("inputdata.xlsx")
    ranges = excel.find("10.132.???.*", "IP!E1:E9999", max_results=6)
    for range in ranges:
        print(f"ADDR = {range.Address} VALUE = {range.Value}")
        range.Value = range.Value.replace("10.132.", "192.168.")
        range.BorderAround()
    excel.save_excel_as("inputdata_MODIFIED.xlsx")

    excel.open_workbook("testfile.xlsx")

    excel.get_range("E4").Formula = "=SUM(C4:D4)"
    destination = excel.get_range("E5:E10")
    excel.get_range("E4").Copy(destination)

    excel.add_new_sheet("test3")
    field = excel.create_pivot_field("price", "max", "\#")
    excel.create_pivot_table(
        "data", "test3", ["products", "expense"], [field], sort_field=field
    )
    excel.save_excel_as("testresult.xlsx")

    excel.open_workbook("testresult.xlsx")
    excel.set_active_worksheet("test3")
    tables = excel.get_pivot_tables()

    for table_name, table_list in tables.items():
        print(f"TABLE NAME: {table_name}")
        table = Tables().create_table(data=table_list[1:], columns=table_list[0])
        print(table)

Some usage examples (in Robot Framework).

*** Settings ***
Library     RPA.Excel.Application  WITH NAME    excel
Library     String
Library     RPA.Tables

*** Tasks ***
Examples
    excel.Open Application    visible=${TRUE}

    excel.Open Workbook    testdata.xlsx
    ${results}=    Find
    ...    search_string=Salmon
    ...    search_range=data!C910:C999
    ...    max_results=3
    ...    match_case=${FALSE}
    FOR    ${result}    IN    @{results}
        Log to console    FIND = ${result.Address}
    END

    Add New Sheet    pivots
    @{rows}=    Create List    products    expense
    ${field_count}=    Create Pivot Field    price    count    \#
    ${field_sum}=    Create Pivot Field    price    sum    \#,#0
    ${field_average}=    Create Pivot Field    price    average    \#,#0
    @{fields}=    Create List    ${field_count}    ${field_sum}    ${field_average}
    ${pivottable}=    Create Pivot Table
    ...    source_worksheet=data
    ...    pivot_worksheet=pivots!R5C5
    ...    rows=${rows}
    ...    fields=${fields}
    ...    sort_field=${field2}
    ${range}=    Get Range    data!A1:A4
    Call Method    ${range}    Merge
    ${range}=    Get Range    data!A1:A4
    FOR    ${r}    IN    @{range}
        Log To Console    ${r.Value}
    END
    Call Method    ${range}    UnMerge

    excel.Open Workbook   testresult.xlsx
    excel.Set Active Worksheet    pivots
    ${pivots}=    Get Pivot Tables
    FOR    ${tablename}    ${pivot}    IN    &{pivots}
        Log To Console    ${tablename}
        ${table}=    RPA.Tables.Create Table    data=${{$pivot[1:]}}    columns=${{$pivot[0]}}
        Log To Console    ${table}
    END