def doExportXls(self, abfrage, sql):
"""
Liefert die exportieren Daten als MS Excel.
@param abfrage Abfrage Objekt
@param sql SQL Anweisung
HINT: um Macros bereinigt
"""
c = self.db.cursorFactory()
c.row_factory = sqlite3.Row
xlsRow = 0
xlsCol = 0
dc = Dateconverter()
def Latin1ToUtf8(self, value):
if value is None: return None
try:
return value.decode('latin1').encode('utf8')
except:
return value
xlsFile = tempfile.NamedTemporaryFile(delete=False,
prefix='export-{0}'.format(
abfrage.abfID))
xlsFileName = xlsFile.name
xlsFile.close()
self.workbook = Workbook(encoding='UTF-8')
self.sheet = self.workbook.add_sheet('Data')
self.sheet.write(
xlsRow, 0,
'Auswertung: {0} vom: {1}'.format(abfrage.abfName,
dc.giveAsGermanDateTime('now')))
xlsRow += 1
cnt = 0
for row in c.execute(sql):
cnt += 1
xlsCol = 0
if cnt == 1:
colHeader = row.keys()
for colh in colHeader:
self.sheet.write(xlsRow, xlsCol, colh)
xlsCol += 1
xlsCol = 0
xlsRow += 1
for v in row:
self.sheet.write(xlsRow, xlsCol, v)
xlsCol += 1
try:
self.workbook.save(xlsFileName)
except Exception, e:
raise Exception(e)
def doExportXls(self,abfrage,sql):
"""
Liefert die exportieren Daten als MS Excel.
@param abfrage Abfrage Objekt
@param sql SQL Anweisung
HINT: um Macros bereinigt
"""
c = self.db.cursorFactory()
c.row_factory = sqlite3.Row
xlsRow=0
xlsCol=0
dc = Dateconverter()
def Latin1ToUtf8(self,value):
if value is None: return None
try:
return value.decode('latin1').encode('utf8')
except:
return value
xlsFile = tempfile.NamedTemporaryFile(delete=False,prefix='export-{0}'.format(abfrage.abfID))
xlsFileName = xlsFile.name
xlsFile.close()
self.workbook = Workbook(encoding='UTF-8')
self.sheet = self.workbook.add_sheet('Data')
self.sheet.write(xlsRow,0,'Auswertung: {0} vom: {1}'.format(abfrage.abfName,dc.giveAsGermanDateTime('now')))
xlsRow+=1
cnt = 0
for row in c.execute(sql):
cnt += 1
xlsCol=0
if cnt == 1:
colHeader = row.keys()
for colh in colHeader:
self.sheet.write(xlsRow,xlsCol,colh)
xlsCol+=1
xlsCol=0
xlsRow+=1
for v in row:
self.sheet.write(xlsRow,xlsCol,v)
xlsCol+=1
try:
self.workbook.save(xlsFileName)
except Exception,e:
raise Exception(e)
assert dc.giveAsANSIDate("31.12.2011 11:22") == "2011-12-31"
assert dc.giveAsANSIDateTime("31.12.2011 11:22") == "2011-12-31T11:22"
assert dc.giveAsANSIDateTime("31.12.2011 11:22:33", secs=True) == "2011-12-31T11:22:33"
assert dc.giveAsANSIDateTime("31.12.2011 11:22:33", True) == "2011-12-31T11:22:33"
assert dc.giveAsANSIDateTime(secs=True, value="31.12.2011 11:22") == "2011-12-31T11:22:00"
# Datumsfehler
assert hasException("dc.giveAsANSIDate('31.2.2011')")
dc.clear()
assert dc.giveAsGermanDate("2011-01-01") == "01.01.2011"
assert dc.giveAsGermanDate("2011-01-01", "%d. %b %Y") == "01. Jan 2011"
assert dc.giveAsGermanDate(format="%d. %b %Y", value="2011-01-01") == "01. Jan 2011"
assert dc.giveAsGermanDateTime("1.1.2011 11:22") == "01.01.2011 11:22"
assert dc.giveAsGermanDateTime("1.1.2011 11:22:33", secs=True) == "01.01.2011 11:22:33"
assert dc.giveAsGermanDateTime("1.1.2011 11:22", secs=True) == "01.01.2011 11:22:00"
assert dc.giveAsGermanDateTime("1.1.2011", secs=True) == "01.01.2011 00:00:00"
assert dc.giveAsGermanDate(value=None, nvl="now") == time.strftime("%d.%m.%Y", time.localtime())
assert dc.giveAsGermanDateTime(value=None, nvl="now") == time.strftime("%d.%m.%Y %H:%M", time.localtime())
assert dc.giveAsGermanDate(value=None, nvl="jetzt") == time.strftime("%d.%m.%Y", time.localtime())
assert dc.giveAsGermanDateTime(value=None, nvl="jetzt") == time.strftime("%d.%m.%Y %H:%M", time.localtime())
assert dc.giveAsGermanDateTime(value=None, nvl="jetzt", secs=True) == time.strftime(
"%d.%m.%Y %H:%M:%S", time.localtime()
)
print "done"
def export(self):
workbook = None
sheet = None
gruppe = GruppeDomain(db=self.db)
personen = PersonDomain(db=self.db)
xlsRow=0
xlsCol=0
dc = Dateconverter()
xlsFile = tempfile.NamedTemporaryFile(delete=False,prefix='export-personen')
xlsFileName = xlsFile.name
xlsFile.close()
self.workbook = Workbook(encoding='UTF-8')
self.sheet = self.workbook.add_sheet('Data')
self.insertList(sheet,xlsRow,['Auswertung vom: {0}'.format(dc.giveAsGermanDateTime('now'))])
xlsRow+=1
auxFilter = self.cgiparam('_filter')
_filter = Utility.normalizeFilter(auxFilter)
where = '(1=1)'
if _filter.startswith('id:'):
where = "perID={0}".format(_filter[3:])
elif _filter.startswith('tn:'):
where = "perArt like '%-{0}'".format(_filter[3:])
elif _filter.startswith('bvkey:'):
where = "perBVKey='{0}'".format(_filter[6:])
elif _filter == '%':
pass
else:
where = "lower(perNachname) like lower('%{0}%') or lower(perVorname) like lower('%{0}%')".format(_filter)
if self.cgiparam('_perStatus'):
where += " AND perStatus='{0}'".format(self.cgiparam('_perStatus'))
if self.cgiparam('_perArt'):
where += " AND perArt='{0}'".format(self.cgiparam('_perArt'))
if self.cgiparam('_grpID'):
where += " AND grpID='{0}'".format(self.cgiparam('_grpID'))
self.insertList(sheet,xlsRow,[
'ID',
'Vorname',
'Nachname',
'Gender',
'Kl.Gruppe',
'Status',
'Art',
'Verpflegung',
'EMail',
'Telefon'
])
xlsRow+=1
for person in personen.eachDomain(where=where):
gruppe.get(person.grpID or 0)
self.insertList(sheet,xlsRow,[
person.perID,
person.perVorname,
person.perNachname,
person.perGender,
person.kgrKennung,
person.perStatus,
person.perArt,
person.perVerpflegung,
person.perEMail or '',
person.perTelefon or ''
])
xlsRow+=1
try:
self.workbook.save(xlsFileName)
except Exception,e:
raise Exception(e)
secs=True) == '2011-12-31T11:22:33')
assert (dc.giveAsANSIDateTime('31.12.2011 11:22:33',
True) == '2011-12-31T11:22:33')
assert (dc.giveAsANSIDateTime(
secs=True, value='31.12.2011 11:22') == '2011-12-31T11:22:00')
# Datumsfehler
assert (hasException("dc.giveAsANSIDate('31.2.2011')"))
dc.clear()
assert (dc.giveAsGermanDate('2011-01-01') == '01.01.2011')
assert (dc.giveAsGermanDate('2011-01-01', '%d. %b %Y') == '01. Jan 2011')
assert (dc.giveAsGermanDate(format='%d. %b %Y',
value='2011-01-01') == '01. Jan 2011')
assert (dc.giveAsGermanDateTime('1.1.2011 11:22') == '01.01.2011 11:22')
assert (dc.giveAsGermanDateTime('1.1.2011 11:22:33',
secs=True) == '01.01.2011 11:22:33')
assert (dc.giveAsGermanDateTime('1.1.2011 11:22',
secs=True) == '01.01.2011 11:22:00')
assert (dc.giveAsGermanDateTime('1.1.2011',
secs=True) == '01.01.2011 00:00:00')
assert (dc.giveAsGermanDate(value=None, nvl='now') == time.strftime(
'%d.%m.%Y', time.localtime()))
assert (dc.giveAsGermanDateTime(value=None, nvl='now') == time.strftime(
'%d.%m.%Y %H:%M', time.localtime()))
assert (dc.giveAsGermanDate(value=None, nvl='jetzt') == time.strftime(
'%d.%m.%Y', time.localtime()))
assert (dc.giveAsGermanDateTime(value=None, nvl='jetzt') == time.strftime(
