Das deutsche Python-Forum

Code: Alles auswählen

"""
Overview of my bank accounts.
"""
import pandas as pd


class Sheet:
    def __init__(self, filename, sheet_name):
        self.filename = filename
        self.sheet_name = sheet_name
        df = pd.DataFrame(
            columns=[
                "Date",
                "Total amount",
                "Bank1",
                "Bank2",
                "Bank3",
                "Bank4",
                "Bank5",
                "Bank6",
                "Bank7",
                "Bank8",
                "Bank9",
            ]
        )
        with pd.ExcelWriter(self.filename, engine="xlsxwriter") as writer:
            df.to_excel(
                writer, sheet_name=self.sheet_name, header=False, index=False
            )
            header_format = writer.book.add_format(
                {
                    "bold": True,
                    "text_wrap": True,
                    "valign": "top",
                    "fg_color": "#D7E4BC",
                    "border": 1,
                }
            )
            worksheet = writer.sheets[self.sheet_name]
            worksheet.set_column("B:L", 10)
            for column_number, value in enumerate(df.columns.values, 1):
                worksheet.write(0, column_number, value, header_format)

    def add_header(self, header):
        df = pd.read_excel(
            self.filename, sheet_name=self.sheet_name, header=True
        )
        df.insert(loc=len(df.columns) + 1, column=header, value=0)
        with pd.ExcelWriter(self.filename, engine="xlsxwriter") as writer:
            df.to_excel(writer, sheet_name=self.sheet_name)


def main():
    sheet = Sheet("myBanking.xlsx", "Total amount")
    sheet.add_header("Test")


if __name__ == "__main__":
    main()

Code: Alles auswählen

from openpyxl import Workbook 
from openpyxl.styles import Font, Border, Side, NamedStyle, Alignment



def print_header():
    wb = Workbook()
    ws = wb.active

    black = "000000"
    font = Font(bold=True, 
                size=18,)
    thick = Side(style="thick", 
                    color=black)

    border = Border(left=thick, 
                    right=thick, 
                    top=thick, 
                    bottom=thick)

    named_style = NamedStyle(name="highlight", 
                            font=font, 
                            border=border)

    alignment = Alignment(horizontal='center',
                            vertical='bottom',
                            text_rotation=0,
                            wrap_text=True,
                            shrink_to_fit=False,
                            indent=0)

    #sheet.column_dimensions['B'].width = 25

    ws["A1"].style = named_style
    ws["A1"].alignment = alignment
    ws.cell(row=1, column=1).value = "Date"
    ws["B1"].style = named_style
    ws["B1"].alignment = alignment
    ws.cell(row=1, column=2).value = "Total amount"
    ws["C1"].style = named_style
    ws["C1"].alignment = alignment 
    ws.cell(row=1, column=3).value = "Bank1"
    ws["D1"].style = named_style
    ws["D1"].alignment = alignment
    ws.cell(row=1, column=4).value = "Bank1"
    ws["E1"].style = named_style
    ws["E1"].alignment = alignment
    ws.cell(row=1, column=5).value = "Bank1 Bank1"
    ws["F1"].style = named_style
    ws["F1"].alignment = alignment
    ws.cell(row=1, column=6).value = "Bank1 Bank1"
    ws["G1"].style = named_style
    ws["G1"].alignment = alignment
    ws.cell(row=1, column=7).value = "Bank1 Bank1"
    ws["H1"].style = named_style
    ws["H1"].alignment = alignment
    ws.cell(row=1, column=8).value = "Bank1 Bank1"
    ws["I1"].style = named_style
    ws["I1"].alignment = alignment
    ws.cell(row=1, column=9).value = "Bank1 Bank1"
    ws["J1"].style = named_style
    ws["J1"].alignment = alignment
    ws.cell(row=1, column=10).value = "Bank1 Bank1"
    ws["K1"].style = named_style
    ws["K1"].alignment = alignment
    ws.cell(row=1, column=11).value = "Cash"
    ws.auto_filter.ref = "A1:K1" # Set a Filter function to the sheet 
    ws.print_title_rows = '1:1'
    wb.save(filename="myfinance.xlsx")

    """
    Function to print the  rows of  xlsx File 
    """
    def print_rows():
     for row in ws.iter_rows(values_only=True):
            print(row)
    print_rows()

print_header()

Code: Alles auswählen

    values = [
        "Date", "Total amount", "Bank1", "Bank1", "Bank1 Bank1",
        "Bank1 Bank1", "Bank1 Bank1", "Bank1 Bank1", "Bank1 Bank1",
        "Bank1 Bank1", "Cash"
    ]
    for column, value in enumerate(values, 1):
        cell = ws.cell(row=1, column=column)
        cell.style = named_style
        cell.alignment = alignment
        cell.value = value

Code: Alles auswählen

#!/usr/bin/env python3
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, NamedStyle, Side

BLACK = "000000"


def main():
    workbook = Workbook()
    sheet = workbook.active

    thick = Side("thick", BLACK)
    style = NamedStyle(
        "highlight",
        Font(bold=True, size=18),
        border=Border(left=thick, right=thick, top=thick, bottom=thick),
    )
    alignment = Alignment(
        horizontal="center",
        vertical="bottom",
        wrap_text=True,
        shrink_to_fit=False,
    )
    for column_number, value in enumerate(
        [
            "Date",
            "Total amount",
            "Bank1",
            "Bank1",
            "Bank1 Bank1",
            "Bank1 Bank1",
            "Bank1 Bank1",
            "Bank1 Bank1",
            "Bank1 Bank1",
            "Bank1 Bank1",
            "Cash",
        ],
        1,
    ):
        cell = sheet.cell(1, column_number, value)
        cell.style = style
        cell.alignment = alignment

    sheet.auto_filter.ref = f"A1:{cell.coordinate}"
    sheet.print_title_rows = "1:1"
    workbook.save("myfinance.xlsx")

    for row in sheet.iter_rows(values_only=True):
        print(row)


if __name__ == "__main__":
    main()