Excel-Tabellenerstellung mit Python [Fortschrittsverwaltungstabelle]

Einführung

Ich dachte plötzlich, ich hätte eine einfache Fortschrittsmanagement-Tabelle für mich erstellt. ... Ist es nicht ärgerlich, dies jedes Mal zu machen? ??

Deshalb habe ich den Code geschrieben, der eine Tabelle erstellt, wenn ich sie nach dem Studium von Python starte.

↓ Das fertige Produkt sieht so aus

Lassen Sie uns jeden Teil erklären.

Dateierstellung

import openpyxl.utils
import os

from openpyxl.styles import PatternFill
from openpyxl.styles.borders import Border, Side
from openpyxl.formatting.rule import FormulaRule

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'Fortschrittsverwaltungstabelle'

Erstellen Sie zunächst eine Excel-Datei. Erstellen Sie eine neue Datei mit openpyxl.Workbook (). aktiv zielt auf das aktuell ausgewählte Blatt ab. Seit dem Start der Datei ist Blatt1 ausgewählt. Sie können den Namen des nach Titel ausgewählten Blattes ändern.

Zellgröße

def cellwidth(start, end, wide):
    for length in range(start, end):
        alpha = sheet.cell(row=1, column=length).coordinate[:-1]
        sheet.column_dimensions['{}'.format(alpha)].width = wide


cellwidth(1, 7, 8.38)
cellwidth(2, 3, 17.38)
cellwidth(8, 9, 0.38)
cellwidth(9, 335, 4.88)

for rows in range(1, 100):
    sheet.row_dimensions[rows].height = 19

Sie können die Spaltenbreite mit colomn_dimensions ['angegebene Spalte'] .width und die Zeilenhöhe mit row_dimensions ['angegebene Zeile'] .height festlegen. Ich wollte jedes angeben, also habe ich es zu einer Funktion gemacht und die for-Anweisung verwendet. Es ist schwierig, die Spalte zu verstehen, aber sie wird in A = 1, B = 2, C = 3 konvertiert ... (Beispiel: B1 wenn Zelle (Zeile = 1, Spalte = 2)) Da ich ein Alphabet in die angegebene Spalte einfügen wollte, habe ich cell (row =, column =) verwendet. Coordinate, um die Zellenposition zu erhalten, und [: -1], um das letzte -1-Zeichen von Anfang an zu erhalten. (Beispiel: Wenn es AAA1 in der Zelle ist, erhalten Sie AAA) Da die Linienbreite diesmal gleichmäßig ist, ist dies keine Funktion.

Bilderrandbereich

side1 = Side(style='thin', color='000000')
side2 = Side(style='double', color='000000')
side3 = Side(style='hair', color='000000')

for rows in sheet.iter_rows(min_row=1, min_col=1, max_row=100, max_col=9):
    for cell in rows:
        if cell.row == 5:
            cell.border = Border(right=side1, bottom=side2)
        else:
            cell.border = Border(top=None, right=side1, bottom=None, left=None)

for rows in sheet.iter_rows(min_row=1, min_col=9, max_row=100, max_col=335):
    for cell in rows:
        if cell.row == 5:
            cell.border = Border(right=side3, bottom=side2)
        else:
            cell.border = Border(top=side3, right=side3, bottom=side3, left=side3)

Definieren Sie den Rand mit Seite (style = 'Border style', color = 'Color'). Mit Rand (oben =, rechts =, unten =, links =) können Sie den oberen, unteren, linken und rechten Rand einer Zelle festlegen. Hier wird die for-Anweisung verwendet, um einen Rand für jede Zelle in dem durch min bis max angegebenen Bereich festzulegen. Nur die 5. Zeile ist mit einer if-Anweisung verzweigt, da ich eine Zeile unter den Rahmen setzen wollte. Beachten Sie, dass, wenn Sie oben und unten sowie rechts und links unterschiedliche Linientypen festlegen, die angrenzenden Teile mit den später geänderten Rändern überschrieben werden.

Zahlen und Ausdrücke einfügen

startYear = 2020
startMonth = 3
startDay = 1

sheet["B5"].value = "Aufgabe"
sheet["C4"].value = "Anfangsdatum"
sheet["C5"].value = "Pläne"
sheet["D5"].value = "Performance"
sheet["E4"].value = "Fertigstellungstermin"
sheet["E5"].value = "Pläne"
sheet["F5"].value = "Performance"
sheet["G5"].value = "Bemerkungen"
sheet["I2"].value = startYear
sheet["J2"].value = "Jahr"
sheet["I3"].value = startMonth
sheet["J3"].value = "Mond"

sheet["I4"].number_format = "d"
sheet["I4"].value = '=DATE(I2, I3, {})'.format(startDay)
sheet["I5"].number_format = "aaa"
sheet["I5"].value = '=I4'

for rows in sheet.iter_rows(min_row=4, min_col=10, max_row=5, max_col=334):
    for cell in rows:
        if cell.row == 4:
            cell.number_format = 'd'
            cell.value = '={}+1'.format(sheet.cell(row=cell.row, column=cell.column-1).coordinate)
        else:
            cell.number_format = 'aaa'
            cell.value = '={}'.format(sheet.cell(row=cell.row-1, column=cell.column).coordinate)

for rows in sheet.iter_rows(min_row=3, min_col=11, max_row=3, max_col=334):
    for cell in rows:
        cell.number_format = "m"
        cell.value = '=IF(DAY({0})=1, {1},"")'.format(sheet.cell(column=cell.column, row=cell.row + 1).coordinate,
                                                      sheet.cell(column=cell.column, row=cell.row + 1).coordinate)

Der Wert wird in die angegebene Zelle in der Zelle .value eingegeben. Wenn Sie einen Ausdruck eingeben möchten, verwenden Sie '= Gleichung'. Wenn Sie nur eine Formel eingeben, erhalten Sie nur das Berechnungsergebnis. Sie müssen also vorsichtig sein. Sie können das Anzeigeformat auch mit number_format ändern. Sie können das Anzeigeformat in Excel anzeigen oder im Druck (cell.number_format) überprüfen. D ist übrigens das Datum und aaa der Tag. Ich entscheide den Startmonat und füge das Datum von dort hinzu. Wenn es ein Tag ist, zeige ich auch den Monat an. Damit habe ich ungefähr 11 Monate lang die Monate, Tage und Tage eingegeben.

Bedingte Formatierung

def colorMake(types, start, end):
    return PatternFill(fill_type=types, start_color=start, end_color=end)

grayfill = colorMake('solid', 'd3d3d3', 'd3d3d3')
goldfill = colorMake('solid', 'ffd700', 'ffd700')
bluefill = colorMake('solid', '1e90ff', '1e90ff')

for rows in sheet.iter_rows(min_row=6, min_col=2, max_row=100, max_col=7):
    for cell in rows:
        cell.number_format = "m/d"
sheet.conditional_formatting.add('C6:G100', FormulaRule(formula=['NOT($F6="")'], stopIfTrue=True, fill=grayfill))

sheet.conditional_formatting.add('I4:ME100', FormulaRule(formula=['OR(WEEKDAY(I$5)=1, WEEKDAY(I$5)=7)'], stopIfTrue=True, fill=grayfill))

sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($D6<=I$4, $F6>=I$4)'], stopIfTrue=True, fill=goldfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($C6<=I$4, $E6>=I$4)'], stopIfTrue=True, fill=bluefill))

Sie können die Bedingungen in der Reihenfolge der Beschreibung im Auswahlblatt .conditional_formatting.add ('Anwendbarer Zellbereich', FormulaRule (Formel = [Anwendbare Bedingung], stopIfTrue = Richtig oder Falsch, Füllung = Farbe)) festlegen. Ich bin darauf gestoßen, aber die Formel = [anwendbare Bedingungen] und der Inhalt des bedingten Ausdrucks sollten nicht mit = beginnen. Ich erhalte keine Fehler, aber warum gibt es in Excel keine bedingte Formatierung? ?? ?? Es wird sein. stopIfTrue ist ein bedingtes Format "stop if met". Was ist, wenn Sie kein Excel verwenden? Es fühlt sich an wie ... Ich stelle auch die Zellenfarbe ein, wenn die Bedingung mit Füllung erfüllt ist. PatternFill (filltype = Füllmuster, start_color =, end_color =) Wenn Sie keine Abstufung verwenden, sollten start_color und end_color identisch sein.

sparen

desktop_path = os.getenv("HOMEDRIVE") + os.getenv("HOMEPATH") + "\\Desktop"
wb.save(desktop_path + '\\Fortschrittsverwaltungstabelle.xlsx')

Es ist das Ende. Sie können es als Arbeitsmappe .save (Dateiname) speichern. Bitte beachten Sie, dass ohne dies, egal wie viel Sie verdienen, es nicht gespeichert wird. Dieses Mal habe ich den absoluten Pfad zum Desktop angegeben und gespeichert. Wenn es sich nur um den Dateinamen handelt, wird er im aktuellen Verzeichnis gespeichert.

Am Ende

Da der Zellbereich diesmal zu fest ist, scheint sich der Freiheitsgrad zu erhöhen, wenn Sie eine Eigenschaftendatei für Variablen erstellen und diese nach Ihren Wünschen anpassen, indem Sie die numerischen Werte dort anpassen. Es war viel Studium, weil es mit verschiedenen Elementen gefüllt war. (Kleines Gefühl)

Ganzer Code

import openpyxl.utils
import os

from openpyxl.styles import PatternFill
from openpyxl.styles.borders import Border, Side
from openpyxl.formatting.rule import FormulaRule

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'Fortschrittsverwaltungstabelle'


def cellwidth(start, end, wide):
    for length in range(start, end):
        alpha = sheet.cell(row=1, column=length).coordinate[:-1]
        sheet.column_dimensions['{}'.format(alpha)].width = wide


cellwidth(1, 7, 8.38)
cellwidth(2, 3, 17.38)
cellwidth(8, 9, 0.38)
cellwidth(9, 335, 4.88)

for rows in range(1, 100):
    sheet.row_dimensions[rows].height = 19

side1 = Side(style='thin', color='000000')
side2 = Side(style='double', color='000000')
side3 = Side(style='hair', color='000000')

for rows in sheet.iter_rows(min_row=1, min_col=1, max_row=100, max_col=9):
    for cell in rows:
        if cell.row == 5:
            cell.border = Border(right=side1, bottom=side2)
        else:
            cell.border = Border(top=None, right=side1, bottom=None, left=None)

for rows in sheet.iter_rows(min_row=1, min_col=9, max_row=100, max_col=335):
    for cell in rows:
        if cell.row == 5:
            cell.border = Border(right=side3, bottom=side2)
        else:
            cell.border = Border(top=side3, right=side3, bottom=side3, left=side3)

startYear = 2020
startMonth = 2
startDay = 1

sheet["B5"].value = "Aufgabe"
sheet["C4"].value = "Anfangsdatum"
sheet["C5"].value = "Pläne"
sheet["D5"].value = "Performance"
sheet["E4"].value = "Fertigstellungstermin"
sheet["E5"].value = "Pläne"
sheet["F5"].value = "Performance"
sheet["G5"].value = "Bemerkungen"
sheet["I2"].value = startYear
sheet["J2"].value = "Jahr"
sheet["I3"].value = startMonth
sheet["J3"].value = "Mond"

sheet["I4"].number_format = "d"
sheet["I4"].value = '=DATE(I2, I3, {})'.format(startDay)
sheet["I5"].number_format = "aaa"
sheet["I5"].value = '=I4'

for rows in sheet.iter_rows(min_row=4, min_col=10, max_row=5, max_col=334):
    for cell in rows:
        if cell.row == 4:
            cell.number_format = 'd'
            cell.value = '={}+1'.format(sheet.cell(row=cell.row, column=cell.column-1).coordinate)
        else:
            cell.number_format = 'aaa'
            cell.value = '={}'.format(sheet.cell(row=cell.row-1, column=cell.column).coordinate)

for rows in sheet.iter_rows(min_row=3, min_col=11, max_row=3, max_col=334):
    for cell in rows:
        cell.number_format = "m"
        cell.value = '=IF(DAY({0})=1, {1},"")'.format(sheet.cell(column=cell.column, row=cell.row + 1).coordinate,
                                                      sheet.cell(column=cell.column, row=cell.row + 1).coordinate)


def colorMake(types, start, end):
    return PatternFill(fill_type=types, start_color=start, end_color=end)


grayfill = colorMake('solid', 'd3d3d3', 'd3d3d3')
goldfill = colorMake('solid', 'ffd700', 'ffd700')
bluefill = colorMake('solid', '1e90ff', '1e90ff')

for rows in sheet.iter_rows(min_row=6, min_col=2, max_row=100, max_col=7):
    for cell in rows:
        cell.number_format = "m/d"
sheet.conditional_formatting.add('C6:G100', FormulaRule(formula=['NOT($F6="")'], stopIfTrue=True, fill=grayfill))

sheet.conditional_formatting.add('I4:ME100', FormulaRule(formula=['OR(WEEKDAY(I$5)=1, WEEKDAY(I$5)=7)'], stopIfTrue=True, fill=grayfill))

sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($D6<=I$4, $F6>=I$4)'], stopIfTrue=True, fill=goldfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($C6<=I$4, $E6>=I$4)'], stopIfTrue=True, fill=bluefill))

desktop_path = os.getenv("HOMEDRIVE") + os.getenv("HOMEPATH") + "\\Desktop"
wb.save(desktop_path + '\\Fortschrittsverwaltungstabelle.xlsx')