Excel: Plugin automatisieren (mit VBA?) [gelöst: Alternativlösung mit C++]
- Ersteller Nightshift
- Erstellt am
Nightshift
Grand Admiral Special
- Mitglied seit
- 19.08.2002
- Beiträge
- 4.447
- Renomée
- 81
- Standort
- Tief im Weeeeeesss-teheheheeen ;-)
Hallo zusammen!
Ich habe eine Frage zu Excel bzw. zu Plugins in Excel:
Gibt es eine Möglichkeit solche Plugins zu automatisieren?
Eventuell mit VBA oder einem Makro?
Im vorliegenden Fall soll das installierte MySQL(5.6)-Plugin (mindestens) einmal täglich automatisch aufgerufen werden und alle Tabellen-Inhalte an eine der existierenden Datenbankverbindungen schicken, evtl. sogar mehrere Dokumente bzw. deren Inhalte.
Ich bin was diese höheren Weihen von Excel angeht ein totaler Noob und weiß daher noch nicht einmal ob ich nach den richtigen Begriffen gesucht habe. Habe auf jeden Fall per Suchmaschine keine Antworten gefunden, die mir weiterhelfen und hoffe, dass mir hier jemand weiterhelfen kann.
Es würde mir schon weiterhelfen, wenn mich jemand in die richtige Richtung weiterleiten kann.
Ich habe eine Frage zu Excel bzw. zu Plugins in Excel:
Gibt es eine Möglichkeit solche Plugins zu automatisieren?
Eventuell mit VBA oder einem Makro?
Im vorliegenden Fall soll das installierte MySQL(5.6)-Plugin (mindestens) einmal täglich automatisch aufgerufen werden und alle Tabellen-Inhalte an eine der existierenden Datenbankverbindungen schicken, evtl. sogar mehrere Dokumente bzw. deren Inhalte.
Ich bin was diese höheren Weihen von Excel angeht ein totaler Noob und weiß daher noch nicht einmal ob ich nach den richtigen Begriffen gesucht habe. Habe auf jeden Fall per Suchmaschine keine Antworten gefunden, die mir weiterhelfen und hoffe, dass mir hier jemand weiterhelfen kann.
Es würde mir schon weiterhelfen, wenn mich jemand in die richtige Richtung weiterleiten kann.
Zuletzt bearbeitet:
Nightshift
Grand Admiral Special
- Mitglied seit
- 19.08.2002
- Beiträge
- 4.447
- Renomée
- 81
- Standort
- Tief im Weeeeeesss-teheheheeen ;-)
Die Klicks waren ja recht hoch, Interesse an dem Thema scheint also da zu sein.
Vielleicht ist es also ganz interessant für ein paar Leute, dass ich im Netz keine direkte Lösung zu diesem Problem finden konnte, die mit Excel umsetzbar gewesen wäre - auch nicht auf Portalen auf denen entsprechende Fachkompetenz vorhanden war.
Daher bin ich dazu übergegangen das ganz von Grund auf in C++ aufzuziehen: Exceldokumente entpacken, parsen, Daten der benötigten Datenbankstruktur in MySQL anpassen, dann mit dem MySQL-Connector auf die DB zugreifen und die Daten darin abspeichern - fertig.
Vielleicht ist es also ganz interessant für ein paar Leute, dass ich im Netz keine direkte Lösung zu diesem Problem finden konnte, die mit Excel umsetzbar gewesen wäre - auch nicht auf Portalen auf denen entsprechende Fachkompetenz vorhanden war.
Daher bin ich dazu übergegangen das ganz von Grund auf in C++ aufzuziehen: Exceldokumente entpacken, parsen, Daten der benötigten Datenbankstruktur in MySQL anpassen, dann mit dem MySQL-Connector auf die DB zugreifen und die Daten darin abspeichern - fertig.
ghostadmin
Grand Admiral Special
Als CSV speichern und dann per perl skript oder dergleichen in MySQL importieren. Allerdings sobald irgendwas falsch in Excel eingeben wird, hat man Datenchaos. Vielleicht lieber ein Webfrontend für die Datenbank machen ...
Nightshift
Grand Admiral Special
- Mitglied seit
- 19.08.2002
- Beiträge
- 4.447
- Renomée
- 81
- Standort
- Tief im Weeeeeesss-teheheheeen ;-)
Aaaalso, nach längerer Zeit nochmal ein Update zu dem Ganzen.
@ flybyray: Danke für den Tip, hab mir das im August auch einmal kurz angeguckt, aber die Software hätte das Problem nicht ganz so gelöst wie gewünscht. Wobei ich jetzt aus dem Gedächtnis nicht mehr sagen kann wo genau der Haken gewesen war, zuerst dachte ich nämlich die Software würde es tun, so viel weiß ich noch.
@ ghostadmin: Auch ein interessanter Einwurf, allerdings bei der CSV-Speicherung wieder das Problem, dass auch die 1x pro Tag gemacht hätte werden müssen.
Hmm die Idee mit dem Frontend... du meinst also das Ganze Browserbasiert zu steuern? Interessante Idee, aber ich denke auch da hätte ich das Problem der Inhalte und des täglichen Updates gehabt, das automatisch durchgeführt werden sollte.
Nun einmal im Detail dazu wie ich das Ganze letztendlich gelöst habe, ich hatte den prinzipiellen Weg in meinem letzten Beitrag ja schon angedeutet:
Hier noch einmal präzise die Aufgabenstellung:
Die Inhalte diverser Exceldokumente, in unterschiedlichen Verzeichnissen, sollen 1x pro Tag automatisch ausgelesen und in eine MySQL-Datenbank übertragen werden. Das Format sollte so gehalten sein, dass diese Inhalte einfach mit dem Excel-Plugin der MySQL-Datenbank aus Excel heraus importiert werden konnten, wenn gewünscht.
Es muss eine Einstellungsmöglichkeit geben, welche Excel-Dokumente und welche Sheets aus diesen Dokumenten eingelesen werden sollen - und diese Einstellungen zu speichern. Dazu gab es noch eine Log-Funktion, zur Ereignisaufzeichnung.
Meine Lösung:
(1)
Der einfachste Part war natürlich, den C++-Code für die Dokumenten- und Sheet-Auswahl sowie die Speicherfunktionalität zu schreiben mitsamt dem restlichen Drumherum wie den Verwaltungs-, Steuerungs- und Logfunktionen.
Für die Dateisystemoperatioen war etwas Einarbeitung in die Windows-API notwendig, die ich bisher nie wirklich verwendet hatte, hier aber mal ausprobieren wollte, da sie doch ein paar nützliche Funktionen bietet - aber alles halb so wild.
(2)
Auch nicht so schwierig war die Automatisierung, nur ein kleiner Umweg:
Die Automatisierung habe ich in zwei Teile augesplittet:
a) Den täglichen Aufruf.
b) Die Automatisierung innerhalb der Software.
a) Der tägliche Aufruf wurde über den Aufgabenplaner von Windows 7 realisiert. Sehr praktisch und abgesehen von ein paar Kleinigkeiten, die bei Aufruf einer Konsolenanwendung mit Eingabeparametern beachtet werden müssen, auch sehr unkompliziert.
b) Der erste Versuch zielte darauf ab, die Automatisierung mit einem Timer bzw. den Wechsel zwischen Timer und manueller Eingabe in der Software selber zu gewährleisten. D.h. es wurde 30 Sekunden lang auf eine Eingabe (Tastendruck) gewartet und dann der Sicherungsprozess automatisch durchlaufen, oder (bei Tastendruck) in den Eingabemodus der Software gewechselt. Hier gab es aber Probleme (siehe Spoiler), weshalb ich diese Lösung verworfen und hin zu einer Argumentgesteuerten Lösung gewechselt habe.
(3)
Etwas komplizierter wurde es dann schon mit dem Einlesen der Excel-Dokumente:
Da Excel >= 2007 zum Einsatz kam, mussten .xlsx-Dateien (OfficeOpenXML-Format) verarbeitet werden. Dieses Dateiformat ist - verkürzt dargestellt - nichts anderes als eine ZIP-gepackte XML-Datei-Struktur. Das OpenOfficeXML-Format ist ziemlich gut dokumentiert, allein das Haupt-PDF fasst mehr als 5k (!) Seiten.
(*)
Der erste Schritt war also diese Dokumentenstruktur zu entpacken. Dazu habe ich auf die freie miniz-Bibliothek (ZIP-Bibliothek) zurückgegriffen: Die ist vom Funktionsumfang absolut ausreichend, sehr schnell und kann einfach als C-Datei in das eigene Projekt eingebunden werden. Es werden ein paar Codebeispiele zum Lernen mitgeliefert und am besten guckt man sich auch mal die benötigten Teile der Bibliothek in der C-Datei selber an, das fand ich sehr hilfreich.
Das unzippen war der erste Teil meines selbstgeschriebenen Excel-Parsers.
Nach dem Entpacken lagen die Inhalte der gewünschten Sheets auf dem Heap und konnten einfach in C++Strings überführt werden, jetzt konnte der zweite Teil beginnen:
Die XML-Dateien mussten geparst werden, um die Zellen-Inhalte aus dem XML-Wirrwarr herauszufilten. Das war nicht unbedingt schwer, aber dafür ziemlich nervig - siehe dazu nochmal Aussage (*) von oben. Wobei es ja zum Glück wirklich nur um die Inhalte und nicht auch noch um die Formatierung ging - die kann das Excel-Plugin von MySQL eh nicht verarbeiten - was den Parser erst richtig aufgebläht hätte.
Die Inhalte einer jeden Excel-Datei habe ich dann in einem 3D-Vektor abgelegt.
(4)
Letzter Schritt war es, diese 3D-Vektoren in eine MySQL-Datenbank zu übertragen:
Die Wahl dazu fiel auf den C++-Connector von MySQL.
Die API des Connectors zu nutzen ging dann auch ziemlich flott von der Hand, die Beispiele auf der MySQL-Seite sind da schon recht hilfreich. Im Endeffekt wirft man dann einfach vorher zusammengesetzte SQL-Statements auf die Funktionen des Connectors.
Z.B. so:
P.S.: Einfassung im PHP-Tag dient nur der farblichen Darstellung, Code wie gesagt C++.
Das einzige worauf man da ein bisschen achten muss ist, dass der Connector einen C-String als Argument erwartet, was wie ich vermute auf der Tatsache basiert, dass die eigentliche MySQL-API C-basiert ist.
Und damit war ich dann auch "schon" fertig.
Als Fazit lässt sich sagen, dass die - mit Abstand - meiste Zeit für die Einarbeitung in die verschiedenen Bibliotheken/APIs drauf ging, speziell für die Problemlösung mit dem C++-Connector. Die reine Coding-Zeit für die Funktionalität des Programmes (knapp über 1300 logisch ausführbare Codezeilen, ließe sich sicherlich noch optimieren) selber war wesentlich geringer, der größte Anteil hier ging noch für den Parser drauf, danach folgte das Timer-Problem, für dessen umfassendes Verständis ich mir viel Zeit genommen habe.
Herausstellen möchte ich hier nocheinmal miniz-Bibliothek, die mir sehr gut gefallen hat - zumal der Support vom Programmier sehr gut ist.
P.S.:
Bei Interesse an Code-Snippets einfach per PN melden.
@ flybyray: Danke für den Tip, hab mir das im August auch einmal kurz angeguckt, aber die Software hätte das Problem nicht ganz so gelöst wie gewünscht. Wobei ich jetzt aus dem Gedächtnis nicht mehr sagen kann wo genau der Haken gewesen war, zuerst dachte ich nämlich die Software würde es tun, so viel weiß ich noch.
@ ghostadmin: Auch ein interessanter Einwurf, allerdings bei der CSV-Speicherung wieder das Problem, dass auch die 1x pro Tag gemacht hätte werden müssen.
Hmm die Idee mit dem Frontend... du meinst also das Ganze Browserbasiert zu steuern? Interessante Idee, aber ich denke auch da hätte ich das Problem der Inhalte und des täglichen Updates gehabt, das automatisch durchgeführt werden sollte.
Nun einmal im Detail dazu wie ich das Ganze letztendlich gelöst habe, ich hatte den prinzipiellen Weg in meinem letzten Beitrag ja schon angedeutet:
Hier noch einmal präzise die Aufgabenstellung:
Die Inhalte diverser Exceldokumente, in unterschiedlichen Verzeichnissen, sollen 1x pro Tag automatisch ausgelesen und in eine MySQL-Datenbank übertragen werden. Das Format sollte so gehalten sein, dass diese Inhalte einfach mit dem Excel-Plugin der MySQL-Datenbank aus Excel heraus importiert werden konnten, wenn gewünscht.
Es muss eine Einstellungsmöglichkeit geben, welche Excel-Dokumente und welche Sheets aus diesen Dokumenten eingelesen werden sollen - und diese Einstellungen zu speichern. Dazu gab es noch eine Log-Funktion, zur Ereignisaufzeichnung.
Meine Lösung:
(1)
Der einfachste Part war natürlich, den C++-Code für die Dokumenten- und Sheet-Auswahl sowie die Speicherfunktionalität zu schreiben mitsamt dem restlichen Drumherum wie den Verwaltungs-, Steuerungs- und Logfunktionen.
Für die Dateisystemoperatioen war etwas Einarbeitung in die Windows-API notwendig, die ich bisher nie wirklich verwendet hatte, hier aber mal ausprobieren wollte, da sie doch ein paar nützliche Funktionen bietet - aber alles halb so wild.
(2)
Auch nicht so schwierig war die Automatisierung, nur ein kleiner Umweg:
Die Automatisierung habe ich in zwei Teile augesplittet:
a) Den täglichen Aufruf.
b) Die Automatisierung innerhalb der Software.
a) Der tägliche Aufruf wurde über den Aufgabenplaner von Windows 7 realisiert. Sehr praktisch und abgesehen von ein paar Kleinigkeiten, die bei Aufruf einer Konsolenanwendung mit Eingabeparametern beachtet werden müssen, auch sehr unkompliziert.
b) Der erste Versuch zielte darauf ab, die Automatisierung mit einem Timer bzw. den Wechsel zwischen Timer und manueller Eingabe in der Software selber zu gewährleisten. D.h. es wurde 30 Sekunden lang auf eine Eingabe (Tastendruck) gewartet und dann der Sicherungsprozess automatisch durchlaufen, oder (bei Tastendruck) in den Eingabemodus der Software gewechselt. Hier gab es aber Probleme (siehe Spoiler), weshalb ich diese Lösung verworfen und hin zu einer Argumentgesteuerten Lösung gewechselt habe.
Wen es interessiert:
Wenn mit der WinAPI-Funktion WaitForSingleObject() auf eine Eingabe gewartet wird, dann wird auch eine Maus-Bewegung als Eingabe gewertet. Das ist ein Verhalten der Funktion, das sich nicht abstellen lässt. Um selber einen Timer inkl. Tastatureingaben mit Threads zu bauen, der dann wirklich nur Tastatureingaben abfragt, fehlte mir leider die Zeit.
Gleichzeitig gab es ein Problem mit dem Fokus des Kommandozeilenprogrammes:
Wurde es gestartet, ohne dass es Fokus hatte, z.B. mit dem Aufgabenplaner, dann lief der Timer von WaitForSingleObject() ab - jedoch ohne in den automatischen Modus zu wechseln, stattdessen wurde der Wartezyklus fehlerhaft beendet und das Programm geschlossen. Den Fokus für das Programm zurückzuholen führte aber wiederum dazu, dass WaitForSingleObject() eine Eingabe registrierte. Yay.
Wenn mit der WinAPI-Funktion WaitForSingleObject() auf eine Eingabe gewartet wird, dann wird auch eine Maus-Bewegung als Eingabe gewertet. Das ist ein Verhalten der Funktion, das sich nicht abstellen lässt. Um selber einen Timer inkl. Tastatureingaben mit Threads zu bauen, der dann wirklich nur Tastatureingaben abfragt, fehlte mir leider die Zeit.
Gleichzeitig gab es ein Problem mit dem Fokus des Kommandozeilenprogrammes:
Wurde es gestartet, ohne dass es Fokus hatte, z.B. mit dem Aufgabenplaner, dann lief der Timer von WaitForSingleObject() ab - jedoch ohne in den automatischen Modus zu wechseln, stattdessen wurde der Wartezyklus fehlerhaft beendet und das Programm geschlossen. Den Fokus für das Programm zurückzuholen führte aber wiederum dazu, dass WaitForSingleObject() eine Eingabe registrierte. Yay.
(3)
Etwas komplizierter wurde es dann schon mit dem Einlesen der Excel-Dokumente:
Da Excel >= 2007 zum Einsatz kam, mussten .xlsx-Dateien (OfficeOpenXML-Format) verarbeitet werden. Dieses Dateiformat ist - verkürzt dargestellt - nichts anderes als eine ZIP-gepackte XML-Datei-Struktur. Das OpenOfficeXML-Format ist ziemlich gut dokumentiert, allein das Haupt-PDF fasst mehr als 5k (!) Seiten.
(*)
Wer das Format erfunden hat muss irgendein Problem haben. 
Das unzippen war der erste Teil meines selbstgeschriebenen Excel-Parsers.
Nach dem Entpacken lagen die Inhalte der gewünschten Sheets auf dem Heap und konnten einfach in C++Strings überführt werden, jetzt konnte der zweite Teil beginnen:
Die XML-Dateien mussten geparst werden, um die Zellen-Inhalte aus dem XML-Wirrwarr herauszufilten. Das war nicht unbedingt schwer, aber dafür ziemlich nervig - siehe dazu nochmal Aussage (*) von oben. Wobei es ja zum Glück wirklich nur um die Inhalte und nicht auch noch um die Formatierung ging - die kann das Excel-Plugin von MySQL eh nicht verarbeiten - was den Parser erst richtig aufgebläht hätte.
Die Inhalte einer jeden Excel-Datei habe ich dann in einem 3D-Vektor abgelegt.
(4)
Letzter Schritt war es, diese 3D-Vektoren in eine MySQL-Datenbank zu übertragen:
Die Wahl dazu fiel auf den C++-Connector von MySQL.
An dieser Stelle ein wichtiger Hinweis: Dieser Connector wird von Oracle mit VS2008 gebacken; das kann dazu führen, dass es unter Umständen mit höheren Versionen von Visual Studio zu Kompatibilitätsproblemen kommen kann!
War leider auch bei mir der Fall (Visual Studio 2010), ich habe weder die 32-Bit- noch die 64-Bit-Libs richtig zum Arbeiten bekommen, jeweils weder die Debug- noch die Release-Libs, und daher massiv Probleme mit nichtauflösbaren Symbolen. Dieses Problem mit den nichtaufzulösenden Symbolen hat mich echt Nerven (und viel Zeit) gekostet, weil ich mir zuerst einfach nicht erklären konnte warum es dazu kam. Erst nach viel Sucherei im Netz bin ich dann auf den Hinweis mit der Inkompatibilität der Versionen gekommen - dann war die Ansage: Connector aus den MySQL-Sourcen selber backen.
Wer also einmal selber auf ungeklärte unauflösbare Symbole mit dem C++-Conntector stößt: Selber backen erspart viele Tränen! Anleitungen dazu gibt es auf den Seiten von MySQL zu finden.
Bei einem Test mit VS 2012 hatte ich das Problem übrigens nicht. Und mit einer neueren Version des Connectors (1.1.3) dann auch bei VS 2010 nicht mehr (obwohl immer noch mit VS2008 gebacken).
War leider auch bei mir der Fall (Visual Studio 2010), ich habe weder die 32-Bit- noch die 64-Bit-Libs richtig zum Arbeiten bekommen, jeweils weder die Debug- noch die Release-Libs, und daher massiv Probleme mit nichtauflösbaren Symbolen. Dieses Problem mit den nichtaufzulösenden Symbolen hat mich echt Nerven (und viel Zeit) gekostet, weil ich mir zuerst einfach nicht erklären konnte warum es dazu kam. Erst nach viel Sucherei im Netz bin ich dann auf den Hinweis mit der Inkompatibilität der Versionen gekommen - dann war die Ansage: Connector aus den MySQL-Sourcen selber backen.
Wer also einmal selber auf ungeklärte unauflösbare Symbole mit dem C++-Conntector stößt: Selber backen erspart viele Tränen! Anleitungen dazu gibt es auf den Seiten von MySQL zu finden.
Bei einem Test mit VS 2012 hatte ich das Problem übrigens nicht. Und mit einer neueren Version des Connectors (1.1.3) dann auch bei VS 2010 nicht mehr (obwohl immer noch mit VS2008 gebacken).
Z.B. so:
PHP:
// Vor dem Try-Block:
string sDynamicTableCols = "(id INT"; // Spaltenaufzählung für Statement vorbereiten
for(int i = 0; i < iMySQLTableCol; i++)
{ // Liste der Spaltennamen in Form bringen für MySQL-Syntax
sDynamicTableCols += ", `"+vesDynamicColNames[i]+"` VARCHAR("+std::to_string((long long)iMaxVarCharLength)+")";
}
sDynamicTableCols += ")";
// später im Try-Block:
sqlMySQLStatement->execute(("CREATE TABLE "+sTableName+sDynamicTableCols).c_str());Da der C++-Connector allerdings eine C++-API sein soll, ist es meiner Ansicht nach stilistisch schlecht gelöst, dass dessen Funktionen nicht zumindest alternativ auch Strings der C++-Standard-Bibliothek als Argument annehmen können.
Und damit war ich dann auch "schon" fertig.
Als Fazit lässt sich sagen, dass die - mit Abstand - meiste Zeit für die Einarbeitung in die verschiedenen Bibliotheken/APIs drauf ging, speziell für die Problemlösung mit dem C++-Connector. Die reine Coding-Zeit für die Funktionalität des Programmes (knapp über 1300 logisch ausführbare Codezeilen, ließe sich sicherlich noch optimieren) selber war wesentlich geringer, der größte Anteil hier ging noch für den Parser drauf, danach folgte das Timer-Problem, für dessen umfassendes Verständis ich mir viel Zeit genommen habe.
Herausstellen möchte ich hier nocheinmal miniz-Bibliothek, die mir sehr gut gefallen hat - zumal der Support vom Programmier sehr gut ist.
P.S.:
Bei Interesse an Code-Snippets einfach per PN melden.
Zuletzt bearbeitet:
