Posts tagged ‘openpyxl’

Mit Python suchen und ersetzen in CSV Dateien

Nachdem wir bereits mit Excel und VBA Platzhalter in CSV Dateien gesucht und mit Inhalten ersetzt haben heute das ganze mit Python und OpenPyxl.

Ausgangspunkt ist eine Exceldatei „python_test.xlsx“ mit einer Named Range „Felder“ im Tabellenblatt „Tabelle2“.

Mit der openpyxl Bibliothek laden wir das Excel-Blatt und holen uns die Inhalte der Range in ein Dictionary. Jeden der Keys aus dem Dictionary suchen wir dann in der CSV Datei und ersetzen ihn gegen den Wert aus der Excel-Datei.

# -*- coding: utf-8 -*-
import openpyxl 
 
path = "python_test.xlsx"
workbook = openpyxl.load_workbook(path) 
 
def get_sheet_and_location(workbook, named_range):
    x = list(workbook.defined_names['Felder'].destinations)[0]
    return x[0], x[1].replace('$','').split(':')[0],x[1].replace('$','').split(':')[1]
 
 
sheet, start, stop = get_sheet_and_location(workbook,'Felder')
worksheet = workbook[sheet]
rng=worksheet[start:stop] 
 
replacements = {}
 
for row in rng:
    c1, c2 = row
    replacements[c1.value] = c2.value
 
 
 
with open('Python_test.txt') as input_file:
 
    text = input_file.read()
 
    for key in replacements:
        text = text.replace(key,str(replacements[key]))
 
    with open('Python_test_output.txt','w') as output_file:
        output_file.write(text)

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Excel-Dateien schreiben mit Openpyxl: (Bedingte) Formatierung

This entry is part 6 of 6 in the series Openpyxl

Mit Openpyxl lassen sich auch Zellformatierungen und bedingte Formatierungen setzen. Das folgende Beispiel formatiert die ersten beiden Spalten grün, wenn der Zellwert „1“ beträgt und rot für alle anderen Inhalte. Die dritte Spalte wird mit blauem Hintergrund formatiert, hier jedoch ohne Bedingung.

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.utils.cell import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.worksheet.table import Table, TableStyleInfo
 
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule, Rule
 
 
dataframe = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
rows = dataframe_to_rows(dataframe, index=False, header=True)
 
wb = Workbook()
ws = wb.active
 
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)
 
# Set column widths based on title width or fixed number
widths = {}
for column in ws.columns:
    if column[0].value is None: # no column header => Fixed with
        widths[column[0].column] = 10.5
    else: # if column header is present => min width resp. maximum
        widths[column[0].column] = max(len(str(column[0].value)) * 1.45, 10.5)
 
ws.column_dimensions[get_column_letter(column[0].column)].width = widths[column[0].column]
 
# Insert formatted table from A1 to max column/max row
tab = Table(displayName="MeineTabelle", ref='A1:' + get_column_letter(ws.max_column) + str(ws.max_row))
style = TableStyleInfo(name="TableStyleLight9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
 
# conditional formatting
redFill = PatternFill(start_color='EE1111',end_color='EE1111',fill_type='solid')
greenFill = PatternFill(start_color='EE1111',end_color='11EE11',fill_type='solid')
ws.conditional_formatting.add('A2:B'+str(ws.max_row),CellIsRule(operator='equal', formula=[1], stopIfTrue=True, fill=greenFill))
ws.conditional_formatting.add('A2:B'+str(ws.max_row),CellIsRule(operator='notEqual', formula=[1], stopIfTrue=True, fill=redFill))
 
lightbluefill = PatternFill(start_color='CCCCFF',end_color='CCCCFF',fill_type='solid')
 
for rowNum in range(2, ws.max_row + 1):
    ws.cell(row=rowNum, column=3).fill = lightbluefill
 
wb.save('07.xlsx')
wb.close()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Excel-Dateien schreiben mit Openpyxl: Kalkulationstabelle erstellen

This entry is part 5 of 6 in the series Openpyxl

Mit Openpyxl lassen sich auch einfach Excel-Daten in Kalkulationstabellen umwandeln, die eine ansprechende Formatierung, Unterstützung bei Formeln und Filter mitbringen. Im folgenden Beispiel nutzen wir noch die Hilfsfunktion get_column_letter(ws.max_column) aus dem utils.cell Modul, um den Excel-Spaltenname für die entsprechenden Spalten zu bestimmen.

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.utils.cell import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.worksheet.table import Table, TableStyleInfo
 
 
dataframe = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
rows = dataframe_to_rows(dataframe, index=False, header=True)
 
wb = Workbook()
ws = wb.active
 
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)
 
# Set column widths based on title width or fixed number
widths = {}
for column in ws.columns:
    if column[0].value is None: # no column header => Fixed with
        widths[column[0].column] = 10.5
    else: # if column header is present => min width resp. maximum
        widths[column[0].column] = max(len(str(column[0].value)) * 1.45, 10.5)
 
ws.column_dimensions[get_column_letter(column[0].column)].width = widths[column[0].column]
 
# Insert formatted table from A1 to max column/max row
tab = Table(displayName="MeineTabelle", ref='A1:' + get_column_letter(ws.max_column) + str(ws.max_row))
style = TableStyleInfo(name="TableStyleLight9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
 
wb.save('05.xlsx')
wb.close()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Excel-Dateien schreiben mit Openpyxl: Spaltenbreiten errechnen

This entry is part 4 of 6 in the series Openpyxl

Wir im letzten Teil erwähnt, lassen sich die Spaltenbreiten beim Export nach Excel auch errechnen. Dazu dient der Schnipsel von stackoverflow im folgenden Code. Ich habe den Code noch so abgewandelt, dass — wenn eine Spaltenüberschrift vorhanden ist — das Maximum aus dem Produkt der errechneten Breite und einem händisch festgelegten Faktor und einer fixen Zahl genutzt wird.

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
 
dataframe = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
rows = dataframe_to_rows(dataframe, index=False, header=True)
 
wb = Workbook()
ws = wb.active
 
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)
 
# Set column widths based on title width or fixed number
widths = {}
for column in ws.columns:
    if column[0].value is None: # no column header => Fixed with
        widths[column[0].column] = 10.5
    else: # if column header is present => min width resp. maximum
        widths[column[0].column] = max(len(str(column[0].value)) * 1.45, 10.5)
 
ws.column_dimensions[get_column_letter(column[0].column)].width = widths[column[0].column]
 
wb.save('04.xlsx')
wb.close()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Excel-Dateien schreiben mit Openpyxl: Spaltenbreiten manuell setzen

This entry is part 3 of 6 in the series Openpyxl

Mit openpyxl lassen sich auch die Spaltenbreiten für einzelne Spalten explizit festlegen. Dazu liefert das worksheet Objekt die Eigenschaft column_dimensions mit, die man einfach setzen kann. Dieses manuelle Setzen kann man auch weglassen, wenn man die Spaltenbreite errechnen lässt, dazu mehr im nächsten Teil.

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
 
dataframe = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
rows = dataframe_to_rows(dataframe, index=False, header=True)
 
wb = Workbook()
ws = wb.active
 
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)
 
ws.column_dimensions['A'].width = 5
ws.column_dimensions['B'].width = 10
ws.column_dimensions['C'].width = 15
 
wb.save('03.xlsx')
wb.close()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Excel-Dateien schreiben mit Openpyxl: Pandas Dataframes exportieren

This entry is part 2 of 6 in the series Openpyxl

Wichtiger als das manuelle Schreiben von Excel-Dateien ist für mich das Umwandeln von pandas Dataframes in Excel-Dateien.

Hier das passende Beispiel dazu, wie man einen Dataframe in eine Excel-Datei exportiert.

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
 
dataframe = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
rows = dataframe_to_rows(dataframe, index=False, header=True)
 
wb = Workbook()
ws = wb.active
 
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)
 
wb.save('02.xlsx')
wb.close()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website

Formatierte Excel-Dateien schreiben mit Openpyxl: „Hello World“ Beispiel

This entry is part 1 of 6 in the series Openpyxl

Üblicherweise exportiere ich meine pandas Dataframes mit der df.to_excel() Funktion. Diese hat leider den Nachteil, dass sie keine Formatierungen im Excel-Dokument unterstützt. Mit der Openpyxl Bibliothek gibt es genau diese Unterstützung. In dieser Post-Reihe beschreibe ich die wesentlichen Funktionen, die man zum Erzeugen formatierter Excel-Dateien benötigt.

Hier das obligatorische „Hello World“ Beispiel, das in die Zelle A1 einer Excel-Datei „Hallo Welt“ schreibt.

from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
ws['A1'] = 'Hallo Welt'
 
 
wb.save('01.xlsx')
wb.close()

Uwe

Uwe Ziegenhagen likes LaTeX and Python, sometimes even combined. Do you like my content and would like to thank me for it? Consider making a small donation to my local fablab, the Dingfabrik Köln. Details on how to donate can be found here Spenden für die Dingfabrik.

More Posts - Website