readfromexcel.py

"""Read columns from multiple excel files and perform actions on them"""
import os
import argparse
from collections import Counter
import openpyxl


def cli():
    """Command line interface"""
    parser = argparse.ArgumentParser(description="Sum excel files")
    parser.add_argument("-p", "--path", help="Path to excel file")
    parser.add_argument("-s", "--sheet", help="Sheet name")
    parser.add_argument("-c", "--column", help="Column name")
    parser.add_argument(
        "-a",
        "--action",
        help="Action to perform: sum, unique, count",
        required=True,
        choices=["sum", "unique", "count"],
    )
    args = parser.parse_args()
    return args


def get_column_index(sheet, column_header):
    """Get the column index based on the header name"""
    for cell in sheet[1]:
        if cell.value == column_header:
            return cell.column_letter
    return None


def main():
    """Main function"""
    args = cli()

    total_sum = 0
    values_counter = Counter()

    for filename in os.listdir(args.path):
        if filename.endswith(".xlsx"):
            file = os.path.join(args.path, filename)
            workbook = openpyxl.load_workbook(file, read_only=True)
            if args.sheet in workbook.sheetnames:
                sheet = workbook[args.sheet]
                column_index = get_column_index(sheet, args.column)
                if column_index is not None:
                    for row in sheet.iter_rows(
                        min_col=openpyxl.utils.column_index_from_string(column_index),
                        max_col=openpyxl.utils.column_index_from_string(column_index),
                    ):
                        if row[0].row == 1:
                            continue
                        if row[0].value is not None:
                            try:
                                if args.action == "sum":
                                    total_sum += float(row[0].value)
                                elif args.action == "count":
                                    values_counter[row[0].value] += 1
                            except ValueError:
                                pass

    if args.action == "sum":
        print(f"Total sum: {total_sum}")
    elif args.action == "count":
        for value, count in values_counter.items():
            print(f"{value} - {count}")


if __name__ == "__main__":
    main()