Open an excel worksheet with password protection

Share your robot!
1

Saw nice slack reply from @mika and wanted to share also to here.
So if you want only to open password protected excel and retrieve its data for processing you can do it by following steps:

  1. Adjust your conda.yaml to this content:
# For more details on the format and content:
# https://github.com/robocorp/rcc/blob/master/docs/recipes.md#what-is-in-condayaml
# Tip: Adding a link to the release notes of the packages helps maintenance and security.

channels:
  - conda-forge

dependencies:
  # Define conda-forge packages here -> https://anaconda.org/search
  # When available, prefer the conda-forge packages over pip as installations are more efficient.
  - python=3.9.13               # https://pyreadiness.org/3.9/ 
  - pip=22.1.2                  # https://pip.pypa.io/en/stable/news/
  - msoffcrypto-tool=5.0.1      # https://anaconda.org/conda-forge/msoffcrypto-tool 
  - pip:
      # Define pip packages here -> https://pypi.org/
      - rpaframework==22.5.3    # https://rpaframework.org/releasenotes.html
  1. Create new file and name it utils.py with following content:
import msoffcrypto


# https://github.com/nolze/msoffcrypto-tool
def decrypt_office_file(filename: str, password: str, decrypted_filename: str):
    """
    Opens given password proteced excel file
    And saves as new unprotected copy
    """
    with open(filename, "rb") as officein:
        file = msoffcrypto.OfficeFile(officein)
        file.load_key(password=password)
        with open(decrypted_filename, "wb") as f:
            file.decrypt(f)
  1. Adjust tasks.robot to look like
*** Settings ***
Library     RPA.Excel.Files
Library     utils.py  #import for our helper function


*** Variables ***
${PASSWORD}     mark    #password for protected file


*** Tasks ***
Decrypt Excel File
    Decrypt Office File    protected.xlsx    ${PASSWORD}    decrypted.xlsx
    Open Workbook    decrypted.xlsx    # Open unprotected copy
    Set cell value    1    1    modified    # write some test values
    Save Workbook        # save changes
    Log    Done.
1 Like