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(