[python]Collecting values from xlsx

This script collects data from each column on each worksheet. The script has to be modified if used in real MS Excel.
Screen Shot 2018-05-10 at 10.16.35 PM.png
Screen Shot 2018-05-10 at 10.16.45 PM

from openpyxl import load_workbook

row = 3
source_id = 1
destination_id = 2
service_id = 3
sources = []
destinations = []
services = []

def get_rule_items(ws, row, column):
    items = []
    while ws.cell(row, column).value is not None:
        items.append(ws.cell(row, column).value)
        row += 1
    return items

rulebook = load_workbook("rules.xlsx")

rules = rulebook.sheetnames

for rule in rules:
    if rulebook[rule].cell(row, source_id).value is not None or rulebook[rule].cell(row, destination_id).value \
        is not None or rulebook[rule].cell(row, service_id).value is not None:

        sources = get_rule_items(rulebook[rule], row, source_id)
        destinations = get_rule_items(rulebook[rule], row, destination_id)
        services = get_rule_items(rulebook[rule], row, service_id)

        print(sources, destinations, services)

This is the result:

Sheet 1
['', ''] ['', ''] ['tcp80']
Sheet 2
[''] [''] ['Tcp80']

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s