[python-users] excel scripten vortrag

[Detlef Lannert]

Thomas Lenarz schrieb:
> Ich selbst nutze Excel sehr gerne (jetzt nicht lachen.... :-D) als
> Entwicklungswerkzeug im Zusammenhang mit SQL-Datenbanken:
 [...]
> Excel -> Datenbankinhalte: (brauche ich häufiger als die Gegenrichtung)
> -----------------------------------------------------------------------
>
> 1. Erzeuge leere Spalten zwischen den vorhandenen Datenfeldspalten und
> vor der ersten Spalte (ökonomisch mit "ganze Spalte markieren. STRG-+
> (im Nummernblock) )
>
> 2. Schreibe in die erste Spalte 'insert into tabelle values (
 [...]

Mit Python geht sowas viel bequemer ... Ich muß immer mal wieder aus einer
(von jemandem manuell gepflegten) Excel-Tabelle Konfigurationsdaten für
einen DHCP-Server generieren. Dazu nutze ich xlrd (aus dem Debian-Paket
python-xlrd; Homepage: http://www.lexicon.net/sjmachin/xlrd.htm). Es hat
sich als zuverlässiger erwiesen als die von mir ausprobierten Alternativen.

Die Daten schreibe ich anschließend im Textformat raus -- stattdessen
könnte man sie mit einem DBAPI-Datenbank-Adapter (oder einem ORM) auch in
eine relationale Datenbank schreiben. Als Anschauungsbeispiel hänge ich
unten eine geringfügig abgespeckte Fassung meines Skripts an, die auch
ohne viele Kommentare wohl selbsterklärend ist ...

Trotzdem zwei Hinweise dazu: Das Programm erwartet in der ersten Zeile der
ersten Tabelle Spaltenüberschriften; darunter müssen die wichtigen sein:
"IP", "Mac-Adresse" und "Benutzer". Danach kommen dann die Datenzeilen.

Beim Aufruf wird der Name der Excel-Datei erwartet, optional noch eine
Reihe von Mac-Adressen, die zu ignorieren sind.

Da ich nächste Woche nicht kommen kann, will ich so wenigstens einen
Beitrag zum xls-Thema leisten ... ;-)

Viele Grüße,
  Detlef

  --------------8<------------ snip --------------8<--------------
#! /usr/bin/python

# $Id: readdhcp.py,v ea7dc2e34bdb 2008/10/23 12:14:50 lannert $
# Author:  Detlef Lannert < lannert uni-duesseldorf de >
# Version: $Revision: ea7dc2e34bdb $

import xlrd
import sys

def read_xls(filename, sheet=None):
    book = xlrd.open_workbook(filename)
    for sheet_num, sheet_name in enumerate(book.sheet_names()):
        if sheet is None:
            sheet = sheet_name  # simply use the first sheet
        if sheet_name != sheet:
            continue
        sheet = book.sheet_by_index(sheet_num)
        return sheet

def read_addresses(d, filename, ignore=None):
    count = 0
    errcount = 0
    if ignore is None:
        ignore = []
    sheet = read_xls(filename)
    labels = sheet.row_values(0)
    try:
        ind_ip = labels.index("IP")
        ind_mac = labels.index("Mac-Adresse")
        ind_user = labels.index("Benutzer")
    except ValueError:
        raise ValueError("column headers not found in table")
    indmax = max((ind_ip, ind_mac, ind_user))
    row_num = 0
    while 1:
        row_num += 1
        try:
            row = sheet.row_values(row_num)
        except IndexError:
            break
        if len(row) < indmax + 1:
            sys.stderr.write("XXX short line: %s\n" % repr(row))
            errcount += 1
            continue
        ip = row[ind_ip]
        mac = row[ind_mac]
        user = row[ind_user]
        if not mac:
            continue
        ipnums = ip.split(".")  # some numbers have leading zeroes, some not
        if len(ipnums) != 4:
            raise ValueError("illegal IP address %s: %s" % (ip, repr(row)))
        ipnums = map(int, ipnums)
        ip = ".".join(map(str, ipnums))
        mac = mac.lower()
        mac = mac.replace(".", "")
        mac = mac.replace(":", "")
        mac = mac.replace("-", "")
        mac = ("%s%s:" * 5 + "%s%s") % tuple(mac)
        if not user:
            user = "pc_%s" % mac.replace(":", "")
        
        if mac in ignore:
            # e.g. the router address
            continue
        if mac in d:
            if d[mac][0] != ip:
                sys.stderr.write("duplicate MAC %s with IPs %s, %s\n" %
                        (mac, d[mac][0], ip))
                errcount += 1
            else:
                sys.stderr.write("duplicate entry %s %s\n" % (mac, ip))
            continue
        d[mac] = [ip, user]
        count += 1
    sys.stderr.write("%d entries used from %s\n" % (count, filename))
    if errcount:
        sys.stderr.write("%d errors\n" % errcount)

def main():
    if len(sys.argv) < 2:
        print "usage: %s <filename.xls> [<ignoremac> ...]" % sys.argv[0]
        sys.exit(1)
    filename = sys.argv[1]
    ignores = sys.argv[2:]
    
    arps = {}
    read_addresses(arps, filename, ignore=ignores)
    iplist = []
    for k in arps:
        iplist.append(map(int, arps[k][0].split(".")) + [k, arps[k][1]])
    iplist.sort()
    for entry in iplist:
        ipaddr = ".".join(map(str, entry[:4]))
        macaddr = entry[-2]
        user = entry[-1]
        name = "pc_%s" % macaddr.replace(":", "")
        print "host %s {\n\t# %s\n\thardware ethernet %s;\n\tfixed-address %s;\n}" %(
                name, user, macaddr, ipaddr)

if __name__ == "__main__":
    main()