Spaß mit Excel-Automation

Ich hatte letztens mal wieder mit „den Geistern, die ich selbst rief“ zu kämpfen: In Form von vielen CSV-Dateien, die ich eben einst selbst erzeugt hatte, sagen wir: auf verschlungenen Pfaden..

Es fing damit an, dass ich Excel verwenden musste, um Datenbankabfragen zu machen. „Hört sich komisch an?“, wird manche Eine(r) denken. Es wird noch viel besser. Excel also, weil: Die einzige Schnittstelle, die wir als Zugang zu einer kommerziellen Datenbank hatten, war ein Excel-AddOn. Es funktioniert, in dem Zellen spezielle Formel („=machAbfrage(a, b, c);“) enthalten, die Methoden des AddOns aufrufen; das schickt dann XML hin und her (wie mir irgendwann mal Wireshark verraten hat, dieses Wissen durfte aber keine Verwendung finden..)..
Wie auch immer, ich sollte 500 Dateien „befüllen“ jeweils mit IK-Spalten und knapp 600 Zeilen. Zu viel auf einmal für das AddOn. Öffnet man eine solche Datei und das AddOn ist auf „Automatisch befüllen“ gestellt, stürzt es ab, jedes Mal.
Jetzt kommt der Clou: Mittels der wunderbaren Blogs RubyOnWindows, das sehr viele hilfreiche Snippets zur Excel-Automation mit Ruby bereit stellt und ich wirklich nur empfehlen kann, habe ich ein Skript geschrieben, dass jeweils 20 Zellen auswählt und nur für diese quasi manuell die „Berechnung“ also Befüllung durchführt. Ein schrecklicher „Hack“ und man kriegt Augenschmerzen, wenn man ihn anschaut, deswegen bleibt er „geheim“ 😉
[spoiler show=“Zumindest mal ein Ausschnitt“]

require 'win32ole'

$CELLS = (("A".."Z").to_a + ("AA".."IK").to_a)

def excel();	WIN32OLE.connect('Excel.Application');end

# .... Jetzt nur die "Hauptschleife":

$CELLS[(excel().ActiveCell.Column-1)..-1].each { |col|
  if col_num % 2 == 0
    ## Sicherheitskopie alle 2 Spalten, Excel..
    backup_name = "#{$WORKDIR}tmp__bis#{col}.csv"
    ## Es hat mich allein 1h gekostet, herauszufinden, dass da '6' stehen muss..
    excel().ActiveSheet.SaveAs(backup_name, 6) ## 6 -> csv-Mode !
  end
  col_num += 1
  (1..29).to_a.each { |row|
    e = row*20
    s = e-19
    sh.Range("#{col}#{s}:#{col}#{e}").Select
    ## Nutze CommandBars.Controls, um einen Button aus den Menüs zu kriegen,
    ## der mit Execute "geklickt" wird, Kosten: 1d..
    excel().CommandBars(12).Controls(4).Controls(3).Execute
  }
}

[/spoiler]
Und fragt auch nicht, wie lange das „Befüllen“ dann gedauert hat..
Die Vorgeschichte geht noch ein bisschen weiter -ja ich weiß, die Aufmerksamkeitsspanne ist schon arg strapaziert, aber das muss ich einfach mal loswerden!

Nun habe ich also 500 CSV-Dateien, von Excel erzeugt. Sie sind alle unterschiedlich. Ich weiß nicht, warum.. Ich habe es nicht geschafft ein Skript zu schreiben, das mir diese Dateien in SQLite einliest, obwohl ich eins geschrieben habe, dass es wirklich sehr hart versucht. Die Dateien wurden alle mit Excel gespeichert, ich weiß nicht, wie da mindestens 10 Varianten rauskommen können!!

Also habe ich kurzerhand gerade ein Skript geschrieben, dass Excel automatisiert, um die Werte aus der CSV raus und in SQLite rein zu bekommen. (Merke: „ruby_string.encode(‚UTF-8‘)“ unter Ruby 1.9!!)
Ich hoffe jetzt auf die „Minus mal Minus“-Regel 😉

Edit: Auch Excel kann nicht so alle (immerhin „selbst“ erzeugten) Dateien öffnen.. Aber ich kann wenigstens zuverlässig diese Fälle abfangen (die einzelnen Werte der ersten Spalte sind dann deutlich zu lang..) und die „kaputten“ Dateien danach mit ein paar gsubs in der richtigen Reihenfolge (scheinbar) wieder hinbiegen..