Die letzten beiden Nächte habe ich damit verbracht, meine MySQL Datenbank mit Excel zu verknüpfen und von dort aus dynamische Abfragen zu genieren. Damit Du nicht vor denselben Problemen stehst, möchte ich Dir in diesem Beitrag meine Erfahrungen & Umsetzungen mitteilen.
Vorweg: Eine SQL- Anweisung mit Zellenbezug, direkt in Excel, ist leider nicht möglich. Zu diesem Ergebnis bin ich nach gefühlten 10 Stunden Recherche & Ausprobieren gekommen. Eine alternative Lösung findest Du weiter unten.
Ein Tipp bevor es los geht: Du solltest als erstes sicherstellen, dass deine Datenbank den Zugriff von außen erlaubt, denn meistens lassen diese in der Standardkonfiguration keine externen Anfragen zu.
Meine Ausgangssituation
In einem Excel- Kalkulationstool sollen Sensordaten aus meiner MySQL- Datenbank, welche ich auf einem Raspberry Pi betreibe, in ein Arbeitsblatt eingelesen werden, um damit in einem zweiten Blatt Berechnungen durchzuführen. Aus Performance- Gründen sollen dazu die Daten zeilenweise, also anhand ihres Schlüssels, eingelesen werden. Somit möchte ich das Laden der ganzen Datenbank verhindern und mich nur auf den gewollten Datensatz konzentrieren.
Mein erste Gedanke war in meiner Excel- Arbeitsmappe ein neues Arbeitsblatt anzulegen. In diesem Blatt definiert man dann eine Zelle, in der man die gewünschte Schlüsselnummer für die Abfrage einträgt.
Beim Verbinden mit der Datenbank führt Excel dann eine SQL- Anweisung aus, welche einen Bezug zur Zelle mit der Schlüsselnummer hat.
So einfach wird es nicht
Nachdem ich beim allgemeinen Verbindungsaufbau schon den ersten Dämpfer bekommen habe – keine Verbindung zur Datenbank- folgte der zweite kurz danach.
Nach stundenlanger Suche bin ich zu dem Ergebnis gekommen, dass eine SQL- Anweisung mit Zellenbezug in Excel nicht möglich ist. Lediglich statische Abfragen beim Verbinden können ausgeführt werden.
Beispiel:
SELECT * FROM Tabellenname WHERE id = 1
Damit lädt Excel die Zeile mit der „Schlüsselnummer“ 1 aus der Tabelle „Tabellennamen“ in ein neues Arbeitsblatt.
Nett, aber für mein Vorhaben leider zu umständlich. Ich müsste bei diesem Vorgehen für jede Abfrage in die Verbindungseinstellungen gehen & die gewünschte ID ändern.
Workaround
Spoiler: Nicht direkt wegrennen, auch wenn man keine Ahnung vom Programmieren hat!
Bevor ich mich an die richtige Datenbank gewagt habe, habe ich mit einem kleinen Beispiel angefangen. Dazu habe ich eine SQL- Testtabelle erzeugt, welche Schulfächer, die jeweilige Note & als „Schlüssel“ das Schuljahr beinhaltet.
Excel vorbereiten
In Excel habe ich eine Arbeitsmappe angelegt, welche zwei Arbeitsblätter besitzt – „Datenbank“ & „Berechnung“.
Das Blatt „Berechnung“ enthält, wer hätte es gedacht, die Formel zur Berechnung des Notendurchschnitts. Das Blatt „Datenbank“ soll die ausgelesenen Fächer & Noten aus dem gewünschten Schuljahr beinhalten.
Es ist sinnvoll das Arbeitsblatt „Datenbank“ in einem ähnlichen Schema anzulegen wie die SQL- Tabelle. Somit habe ich in der Spalte A1 „Schuljahr“, in der Spalte B1 das erste Fach, in C1 das zweite Fach… stehen.
Das Blatt „Berechnungen“ hat einen direkten Zellenbezug zum Blatt „Datenbank“, um sich von dort die ausgelesen Noten zu ziehen und dann damit den Notendurchschnitt für das Schuljahr zu berechnen.
Datenbank auslesen
Da ich keine Ahnung von VBA habe, blieb mir nichts anderes übrig als auf eine andere Programmiersprache zu setzten.
Lass dich von dem Wort Programmieren nicht demotivieren, mit Python habe ich auch erst vor ein paar Tagen angefangen. Das schöne ist, Python ist so einfach, dass Du die Schritte mit Sicherheit auch ohne Ahnung vom Programmieren nachvollziehen kannst.
Ich habe den Code Schritt für Schritt mit Kommentaren versehen. Als Editor habe ich Visual Studio Code verwendet, du kannst aber auch jeden anderen benutzen.
Neben Python 3.8.1 auf Windows, natürlich auch für OSX vorhanden, habe ich noch folgende Module über die Eingabeaufforderung/ Terminal installiert:
py -m pip install msql-connector py -m pip install openpyxl
Der msql-connector erlaubt Python die Verbindung und Verwaltung zur Datenbank. Openpyxl gibt uns die Möglichkeit im Code mit Excel- Arbeitsmappen zu arbeiten.
Was macht der Code
Zunächst öffnet sich eine Eingabemaske, in der man den „Schlüssel“, in unserem Fall das gewollte Schuljahr eingibt.
Anschließend führt der Code die Datenbankabfrage aus:
SELECT * FROM noten WHERE schuljahr = id
In der abgebildeten Abfrage steht „id“ als Platzhalter und ist im Code mit der eingelesenen Variablen aus der Eingabemaske verknüpft.
Im Anschluss öffnet sich die generierte Excel- Arbeitsmappe mit den Datensätzen aus der Datenbank.
Das ganze sieht dann für den Nutzer so aus:
Python und Excel, so geht es:
Die Profis unter euch verzeihen mir bitte den Aufbau und Verzicht auf saubere Funktionen. Für mich zählte zum Ereigniszeitpunkt ausschließlich die Lauffähigkeit.
Ein Update mit einem sauberen Code folgt – bestimmt.
import mysql
import mysql.connector
import os
from time import sleep
import openpyxl
con = mysql.connector.connect(
host = "raspberrypi", #Serveradresse
user = "testbenutzer", #DB Benutzername
passwd = "raspberry", #Passwort der DB
db = "testdatenbank" #Datenbankname
)
#Eingabe der Schlüsselnummer
id = input("Welches Schuljahr soll geladen werden? ")
cursor = con.cursor()
#SQL- Anweisung
cursor.execute("SELECT * FROM noten WHERE schuljahr =" + str(id))
result = cursor.fetchall()
cursor.close()
#Dateiname der ExcelMappe -> muss im Verzeichnis des Codes liegen
file = 'Notendurchschnitt.xlsx'
#Öffnet Mappe in Variable wb => workbook
wb = openpyxl.load_workbook(filename=file)
#Benutze Arbeitsblatt ws => Worksheet
ws = wb.get_sheet_by_name('Datenbank')
for data in result:
schuljahr = data[0]
ws['A2'] = data[0]
ws['B2'] = data[1]
ws['C2'] = data[2]
ws['D2'] = data[3]
ws['E2'] = data[4]
#Kopie der Mappe mit Namen der ID speichern
wb.save("Durchschnitt_Fuer_Schuljahr_" + str(schuljahr) + ".xlsx")
#Aus Sicherheit 3 Sekunden warten => Speichervorgang
sleep(3)
#Arbeitsmappe mit Excel öffnen
os.system('start excel.exe Durchschnitt_Fuer_Schuljahr_' + str(schuljahr) + '.xlsx')
input("Mit Eingabetaste beenden")
Ich hoffe Dir hat der Eintrag ein wenig geholfen & Du kannst jetzt dynamische Inhalte in Excel verarbeiten. Solltest Du eine andere Lösung gefunden haben, teile sie mir gerne mit.
You might also like
