def export_cobros_clientes(self, cr, uid, ids, sheet6, context=None): if context == None: context = {} boldFont=xl.Font() boldFont.bold=True boldS = xl.XFStyle() boldS.font = boldFont boldFont14=xl.Font() boldFont14.bold=True boldFont14.height=220 boldS14 = xl.XFStyle() boldS14.font = boldFont14 numS = xl.XFStyle() numS.num_format_str = '#,##0.00' tesoreria = self.browse(cr, uid, ids[0]) sheet6.write(0,0,'COBROS UNICOS',boldS14) sheet6.write(3,0,'FECHA',boldS) sheet6.write(3,1,'DESCRIPCION',boldS) sheet6.write(3,2,'DIARIO',boldS) sheet6.write(3,3,'TIPO DE PAGO',boldS) sheet6.write(3,4,'IMPORTE',boldS) lineKont = 5 for line in tesoreria.pagos_rece: if line.fecha: sheet6.write(lineKont,0,line.fecha) if line.name: sheet6.write(lineKont,1,line.name) if line.diario: sheet6.write(lineKont,2,line.diario.name) if line.payment_type: sheet6.write(lineKont,3,line.payment_type.name) sheet6.write(lineKont,4,str(line.importe),numS) lineKont += 1 return sheet6
def export_pagos_variables(self, cr, uid, ids, sheet5, context=None): if context == None: context = {} boldFont=xl.Font() boldFont.bold=True boldS = xl.XFStyle() boldS.font = boldFont boldFont14=xl.Font() boldFont14.bold=True boldFont14.height=220 boldS14 = xl.XFStyle() boldS14.font = boldFont14 numS = xl.XFStyle() numS.num_format_str = '#,##0.00' tesoreria = self.browse(cr, uid, ids[0]) sheet5.write(0,0,'PAGOS VARIABLES', boldS14) sheet5.write(3,0,'FECHA',boldS) sheet5.write(3,1,'DESCRIPCION',boldS) sheet5.write(3,2,'PROVEEDOR',boldS) sheet5.write(3,3,'TIPO DE PAGO',boldS) sheet5.write(3,4,'IMPORTE',boldS) lineKont = 5 for line in tesoreria.pagos_var: if line.fecha: sheet5.write(lineKont,0,line.fecha) if line.name: sheet5.write(lineKont,1,line.name) if line.partner_id: sheet5.write(lineKont,2,line.partner_id.name) if line.payment_type: sheet5.write(lineKont,3,line.payment_type.name) sheet5.write(lineKont,4,str(line.importe),numS) lineKont += 1 return sheet5
def export_desglose_saldo(self, cr, uid, ids, sheet1, context=None): if context == None: context = {} boldFont=xl.Font() boldFont.bold=True boldS = xl.XFStyle() boldS.font = boldFont boldFont14=xl.Font() boldFont14.bold=True boldFont14.height=220 boldS14 = xl.XFStyle() boldS14.font = boldFont14 numS = xl.XFStyle() numS.num_format_str = '#,##0.00' tesoreria = self.browse(cr, uid, ids[0]) sheet1.write(11,0,'DESGLOSE SALDO',boldS14) sheet1.write(14,0,'TIPO DE PAGO',boldS) sheet1.write(14,1,'MODO',boldS) sheet1.write(14,2,'IMPORTE',boldS) lineKont = 16 for line in tesoreria.desglose_saldo: if line.name: sheet1.write(lineKont,0,line.name) if line.type == 'in': sheet1.write(lineKont,1,'Entrada') elif line.type == 'out': sheet1.write(lineKont,1,'Salida') sheet1.write(lineKont,2,str(line.saldo), numS) lineKont += 1 return sheet1
def excel_file(self, dados, filename): try: #Open new workbook mydoc = xl.Workbook() for item in dados: #Add a worksheet mysheet = mydoc.add_sheet(item.get('title')) #write headers header_font = xl.Font() #make a font object header_font.bold = True header_font.underline = True #font needs to be style actually header_style = xl.XFStyle() header_style.font = header_font headers = item.get('head') values = item.get('values') for col, value in enumerate(headers): mysheet.write(0, col, value, header_style) #write values and highlight those that match my criteria highlighted_row_font = xl.Font( ) #no real highlighting available? highlighted_row_font.bold = True highlighted_row_font.colour_index = 2 #2 is red, highlighted_row_style = xl.XFStyle() highlighted_row_style.font = highlighted_row_font for row_num, row_values in enumerate(values): row_num += 1 #start at row 1 if row_values[1] == 'Manatee': for col, value in enumerate(row_values): #make Manatee's (sp) red mysheet.write(row_num, col, value, highlighted_row_style) else: for col, value in enumerate(row_values): #normal row mysheet.write(row_num, col, value) #save file mydoc.save(filename) return True except: return False
def export_facturas_recibidas(self, cr , uid, ids, sheet3, context=None): if context == None: context = {} boldFont=xl.Font() boldFont.bold=True boldS = xl.XFStyle() boldS.font = boldFont boldFont14=xl.Font() boldFont14.bold=True boldFont14.height=220 boldS14 = xl.XFStyle() boldS14.font = boldFont14 numS = xl.XFStyle() numS.num_format_str = '#,##0.00' tesoreria = self.browse(cr, uid, ids[0]) sheet3.write(0,0,'FACTURAS RECIBIDAS',boldS14) sheet3.write(3,0,'FECHA VENCIMIENTO',boldS) sheet3.write(3,1,'N. FACTURA',boldS) sheet3.write(3,2,'PROVEEDOR',boldS) sheet3.write(3,3,'DIARIO',boldS) sheet3.write(3,4,'TIPO DE PAGO',boldS) sheet3.write(3,5,'PLAZO DE PAGO',boldS) sheet3.write(3,6,'BASE',boldS) sheet3.write(3,7,'IMPUESTO',boldS) sheet3.write(3,8,'TOTAL',boldS) sheet3.write(3,9,'PENDIENTE',boldS) sheet3.write(3,10,'ESTADO',boldS) lineKont = 5 for line in tesoreria.facturas_rec: if line.factura_id: sheet3.write(lineKont,0,line.fecha_vencimiento) if line.partner_id: sheet3.write(lineKont,1,line.factura_id.number) if line.partner_id: sheet3.write(lineKont,2,line.partner_id.name) if line.diario: sheet3.write(lineKont,3,line.diario.name) if line.tipo_pago: sheet3.write(lineKont,4,line.tipo_pago.name) if line.payment_term: sheet3.write(lineKont,5,line.payment_term.name) sheet3.write(lineKont,6,str(line.base),numS) sheet3.write(lineKont,7,str(line.impuesto),numS) sheet3.write(lineKont,8,str(line.total),numS) sheet3.write(lineKont,9,str(line.pendiente),numS) sheet3.write(lineKont,10,line.estado) lineKont += 1 return sheet3
def writeExcel(self, headers, value, path): mydoc = xl.Workbook() #Add a worksheet mysheet = mydoc.add_sheet("anagrafiche") header_font = xl.Font() #make a font object header_font.bold = True header_font.underline = True #font needs to be style actually replies = {} header_style = xl.XFStyle() header_style.font = header_font xl.UnicodeUtils.DEFAULT_ENCODING = 'cp1251' for col, values in enumerate(headers): mysheet.write(0, col, values, header_style) for row in enumerate(value): Id = row[1].data(1).toInt()[0] if not replies.has_key(Id): replies[Id] = "" pv = self.db.getPvById(Id) packet = self.profile.getData(Id) for col in range(0, len(packet)): mysheet.write(row[0] + 1, col, unicode(packet[col])) if str(path) != "": mydoc.save(path) infoString = "%d anagrafiche esportate correttamente" % len( replies) d = QtGui.QMainWindow() QtGui.QMessageBox.information(d, "Info", infoString)
def xlformat_factory(format): """ copy the format, perform any overrides, and attach an xlstyle instance copied format is returned """ format = copy.deepcopy(format) xlstyle = excel.XFStyle() if isinstance(format, mlab.FormatPercent): zeros = ''.join(['0']*format.precision) xlstyle.num_format_str = '0.%s%%;[RED]-0.%s%%'%(zeros, zeros) format.scale = 1. elif isinstance(format, mlab.FormatFloat): zeros = ''.join(['0']*format.precision) xlstyle.num_format_str = '#,##0.%s;[RED]-#,##0.%s'%(zeros, zeros) elif isinstance(format, mlab.FormatInt): xlstyle.num_format_str = '#,##;[RED]-#,##' else: xlstyle = None format.xlstyle = xlstyle return format
def export_profit(self, sheet1): presence_obj = self.env['event.track.presence'] boldFont = xl.Font() boldFont.bold = True boldS = xl.XFStyle() boldS.font = boldFont boldFont14 = xl.Font() boldFont14.bold = True boldFont14.height = 220 boldS14 = xl.XFStyle() boldS14.font = boldFont14 sheet1.write(0, 0, 'CLIENTE', boldS14) sheet1.write(0, 1, 'DIRECCION', boldS14) sheet1.write(0, 2, 'DIA', boldS14) sheet1.write(0, 3, 'FECHA INICIO', boldS14) sheet1.write(0, 4, 'FECHA FIN', boldS14) line = 1 for p in presence_obj.browse(self.env.context.get('active_ids')): line += 1 partner = p.event.sale_order.partner_shipping_id sheet1.write(line, 0, partner.name) address = u"\n{} {} {}".format(partner.street or '', partner.street2 or '', partner.city or '') sheet1.write(line, 1, address) if p.session_day == '0': day = 'Lunes' elif p.session_day == '1': day = 'Martes' elif p.session_day == '2': day = 'Miércoles' elif p.session_day == '3': day = 'Jueves' elif p.session_day == '4': day = 'Viernes' elif p.session_day == '5': day = 'Sábado' else: day = 'Domingo' sheet1.write(line, 2, day) session_date = fields.Datetime.to_string( _convert_to_local_date(p.session_date, self.env.user.tz)) sheet1.write(line, 3, session_date) session_end_date = fields.Datetime.to_string( _convert_to_local_date(p.estimated_date_end, self.env.user.tz)) sheet1.write(line, 4, session_end_date) return sheet1
def GetRowStyle(height): global dic_RowStyle if not dic_RowStyle.has_key(height): fnt = pyExcelerator.Font() fnt.height = height * 15 rowStyle = pyExcelerator.XFStyle() rowStyle.font = fnt dic_RowStyle[height] = rowStyle return dic_RowStyle[height]
def __call__(self): lectures = self.request.db.query(models.Lecture) lectures = lectures.filter(models.Lecture.is_visible == True) header = [ 'Tutor FirstName', 'Tutor LastName', 'Tutor Email', 'Tutorial Information', 'Student Count', 'Tutorial Room', 'Time', 'Comments' ] w = self.w worksheet_tutorials = w.add_sheet('Tutorials') worksheet_tutorials.set_col_default_width(20) header_style = pyExcelerator.XFStyle() header_style.font.bold = True for i, h in enumerate(header): worksheet_tutorials.write(0, i, h, header_style) rowIndex = 1 for lecture in lectures.all(): tutorialList = [] lectureName = lecture.name for tutorial in lecture.tutorials: vtutor = tutorial.tutor.name( ) if tutorial.tutor != None else 'None' vtutor_firstName = tutorial.tutor.first_name if tutorial.tutor != None else 'None' vtutor_lastName = tutorial.tutor.last_name if tutorial.tutor != None else 'None' vemail = tutorial.tutor.email if tutorial.tutor != None else 'None' vplace = tutorial.place vtime = tutorial.time.__html__() vcomment = tutorial.comment vstudent = len(tutorial.students.all()) tutorialItem = (vtutor_firstName, vtutor_lastName, vemail, lectureName, vstudent, vplace, vtime, vcomment) tutorialList.append(tutorialItem) #sort by tutor fistName tutorialList = sorted(tutorialList) newList = [] tutorialIndex = 1 for item in tutorialList: newItem = (item[0], item[1], item[2], item[3] + ' Uebungsgruppe: ' + str(tutorialIndex), item[4], item[5], item[6], item[7]) newList.append(newItem) tutorialIndex = tutorialIndex + 1 #add sumary lecture lectureItem = ('', lecture.lecturer, '', lectureName, lecture.lecture_students.count(), '', lecture.term.__html__(), '') newList.append(lectureItem) #add to sheet for item in newList: for col, d in enumerate(item): worksheet_tutorials.write(rowIndex, col, d) rowIndex = rowIndex + 1 return self.createResponse()
def save_excel_file(excel_header, excel_data): workBookDocument = pyExcelerator.Workbook() docSheet1 = workBookDocument.add_sheet("sheet1") #Create a font object *j myFont = pyExcelerator.Font() # Change the font myFont.name = 'Times New Roman' # Make the font bold, underlined and italic myFont.bold = True myFont.underline = True # the font should be transformed to style * myFontStyle = pyExcelerator.XFStyle() myFontStyle.font = myFont # if you wish to apply a specific style to a specific row you can use the following command docSheet1.row(0).set_style(myFontStyle) # docSheet1.write(0,column, key,myFontStyle) for i in range(len(excel_header)): if type(excel_header[i]).__name__ in ["date", "datetime"]: entry = str(excel_header[i].day) + '-' + str( excel_header[i].month) + '-' + str(excel_header[i].year) else: entry = str(excel_header[i]) docSheet1.write(0, i, entry, myFontStyle) row = 0 for list in excel_data: row = row + 1 for i in range(len(list)): if list[i] is not None: if type(list[i]).__name__ in ["date", "datetime"]: entry = str(list[i].day) + '-' + str( list[i].month) + '-' + str(list[i].year) docSheet1.write(row, i, entry) elif type(list[i]).__name__ in ["unicode"]: entry = smart_unicode(list[i], encoding='utf-8', strings_only=False, errors='strict') # entry = unicode(list[i]).encode("iso-8859-1") docSheet1.write(row, i, entry) else: docSheet1.write(row, i, str(list[i])) filename = "report.xls" response = HttpResponse(mimetype="application/vnd.ms-excel") response['Content-Disposition'] = 'attachment; filename=%s' % filename workBookDocument.save(response) return response
def getCellStyle(self): fnt = pyExcelerator.Font() fnt.colour_index = 0 al = pyExcelerator.Alignment() al.horz = pyExcelerator.Alignment.HORZ_CENTER al.vert = pyExcelerator.Alignment.VERT_CENTER pattern = pyExcelerator.Pattern() style = pyExcelerator.XFStyle() style.font = fnt style.borders = self.getDaulftBorder() style.alignment = al style.pattern = pattern return style
def export_cash_flow(self, cr, uid, ids, sheet7, context=None): if context == None: context = {} boldFont=xl.Font() boldFont.bold=True boldS = xl.XFStyle() boldS.font = boldFont boldFont14=xl.Font() boldFont14.bold=True boldFont14.height=220 boldS14 = xl.XFStyle() boldS14.font = boldFont14 numS = xl.XFStyle() numS.num_format_str = '#,##0.00' tesoreria = self.browse(cr, uid, ids[0]) sheet7.write(0,0,'CASH FLOW',boldS14) sheet7.write(3,0,'FECHA',boldS) sheet7.write(3,1,'DESCRIPCION',boldS) sheet7.write(3,2,'DIARIO',boldS) sheet7.write(3,3,'TIPO DE PAGO',boldS) sheet7.write(3,4,'TIPO',boldS) sheet7.write(3,5,'IMPORTE',boldS) lineKont = 5 for line in tesoreria.pagos_cash: if line.fecha: sheet7.write(lineKont,0,line.fecha) if line.name: sheet7.write(lineKont,1,line.name) if line.diario: sheet7.write(lineKont,2,line.diario.name) if line.payment_type: sheet7.write(lineKont,3,line.payment_type.name) type = 'Entrada' if line.type == 'out': type = 'Salida' sheet7.write(lineKont,4,type) sheet7.write(lineKont,5,str(line.importe),numS) lineKont += 1 return sheet7
def email_excel_file(excel_header, excel_data): workBookDocument = pyExcelerator.Workbook() docSheet1 = workBookDocument.add_sheet("sheet1") #Create a font object *j myFont = pyExcelerator.Font() # Change the font myFont.name = 'Times New Roman' # Make the font bold, underlined and italic myFont.bold = True myFont.underline = True # the font should be transformed to style * myFontStyle = pyExcelerator.XFStyle() myFontStyle.font = myFont # if you wish to apply a specific style to a specific row you can use the following command docSheet1.row(0).set_style(myFontStyle) # docSheet1.write(0,column, key,myFontStyle) for i in range(len(excel_header)): if type(excel_header[i]).__name__ in ["date", "datetime"]: entry = str(excel_header[i].day) + '-' + str( excel_header[i].month) + '-' + str(excel_header[i].year) else: entry = str(excel_header[i]) docSheet1.write(0, i, entry, myFontStyle) row = 0 for list in excel_data: row = row + 1 for i in range(len(list)): if list[i] is not None: if type(list[i]).__name__ in ["date", "datetime"]: entry = str(list[i].day) + '-' + str( list[i].month) + '-' + str(list[i].year) docSheet1.write(row, i, entry) else: docSheet1.write(row, i, str(list[i])) email_subject = "Future Bazaar Feedbacks" email_body = "Please find attached the list of feedbacks received today." email_from = "Chaupaati Bazaar<*****@*****.**>" email_to = "*****@*****.**" email_bcc = "" mail_obj = EmailMessage(email_subject, email_body, email_from, email_to.split(','), email_bcc.split(','), None) mail_obj.attach('filename.xls', workBookDocument, 'text/xls') mail_obj.send()
def add_table_model(self, title, tmodel): ws = self.add_sheet(title) ncols = tmodel.getColumnCount() bold = pxls.XFStyle() bold.font.bold = True for c in range(ncols): ws.write(0, c, tmodel.getColumnName(c), bold) for r in range(0, tmodel.getRowCount()): for c in range(ncols): try: ws.write(r + 1, c, tmodel.getStringValueAt(r, c)) except: raise Exception( "Exception in %s row %d, column %d: %s" % (title, r, c, tmodel.getStringValueAt(r, c)))
def getTitleStyle(self): fnt = pyExcelerator.Font() fnt.colour_index = 0 fnt.bold = True al = pyExcelerator.Alignment() al.horz = pyExcelerator.Alignment.HORZ_CENTER al.vert = pyExcelerator.Alignment.VERT_CENTER pattern = pyExcelerator.Pattern() pattern.pattern = 2 pattern.pattern_fore_colour = 30 pattern.pattern_back_colour = 95 style = pyExcelerator.XFStyle() style.font = fnt style.borders = self.getDaulftBorder() style.alignment = al style.pattern = pattern return style
def GetStyle(top, bottom, left, right, bold=False, italic=False, fontname='Arial', fontsize=10, algn='center'): font = GetFont(fontname, bold, italic, fontsize) borders = GetBorder(top, bottom, left, right) alignment = GetAlignment(algn) global dic_Style if not dic_Style.has_key((font, borders, alignment)): style = pyExcelerator.XFStyle() style.font = font style.borders = borders style.alignment = alignment dic_Style[(font, borders, alignment)] = style return dic_Style[(font, borders, alignment)]
def saveToFile() : wb = pyExcelerator.Workbook() ws = wb.add_sheet('pages') myfont = pyExcelerator.Font() myfont.name = 'Times New Roman' mystyle = pyExcelerator.XFStyle() mystyle.font = myfont client = MongoClient('localhost', 27017) db = client['lianjia'] coll = db['saveinfo_20170709'] obj = coll.find_one() i = 0 for obj in coll.find(): ws.write(i,0,obj['link'],mystyle) ws.write(i,1,obj['title'],mystyle) ws.write(i,2,obj['image'],mystyle) ws.write(i,3,obj['community'],mystyle) ws.write(i,4,obj['community_title'],mystyle) ws.write(i,5,obj['model'],mystyle) ws.write(i,6,obj['area'],mystyle) ws.write(i,7,obj['tall'],mystyle) ws.write(i,8,obj['faceto'],mystyle) ws.write(i,9,obj['price'],mystyle) ws.write(i,10,obj['average_price'],mystyle) ws.write(i,11,obj['city'],mystyle) ws.write(i,12,obj['town'],mystyle) ws.write(i,13,obj['age'],mystyle) ws.write(i,14,obj['tag0'],mystyle) ws.write(i,15,obj['tag1'],mystyle) ws.write(i,16,obj['tag2'],mystyle) print obj['_id'] # ws.write(i,17,ss,mystyle) i = i + 1 wb.save('E:\\ddd1.xls')
#!/usr/bin/env python # -*- coding: utf-8 -*- # @Time : 2018/1/9 17:59 # @Author : lingxiangxiang # @File : demonpyexcele.py import pyExcelerator #创建workbook和sheet对象 wb = pyExcelerator.Workbook() ws = wb.add_sheet(u'第一页') #设置样式 myfont = pyExcelerator.Font() myfont.name = u'Times New Roman' myfont.bold = True mystyle = pyExcelerator.XFStyle() mystyle.font = myfont #写入数据,使用样式 ws.write(0, 0, u'hello lingxiangxinag!', mystyle) #保存该excel文件,有同名文件时直接覆盖 wb.save('mini.xls') print('创建excel文件完成!') import pyExcelerator #parse_xls返回一个列表,每项都是一个sheet页的数据。 #每项是一个二元组(表名,单元格数据)。其中单元格数据为一个字典,键值就是单元格的索引(i,j)。如果某个单元格无数据,那么就不存在这个值 sheets = pyExcelerator.parse_xls('mini.xls') print(sheets)
def saveXiaoquToFile() : wb = pyExcelerator.Workbook() ws = wb.add_sheet('pages1') myfont = pyExcelerator.Font() myfont.name = u'宋体' myfont.height = 0x00DC mystyle = pyExcelerator.XFStyle() mystyle.font = myfont badBG = Formatting.Pattern() badBG.pattern = badBG.NO_PATTERN badBG.pattern_back_colour = 'red' mystyle.pattern = badBG # mystyle.num_format_str = '0' client = MongoClient('localhost', 27017) db = client['lianjia'] # sDate = settings['MONGODB_DOCNAME'] + time.strftime('%Y%m%d', time.localtime(time.time())) # coll = db['house_20170712'] sDate = 'xiaoqu_' + time.strftime('%Y%m%d', time.localtime(time.time())) coll = db[sDate] # 城区 板块 小区名称 年代 编号 均价 坐标1 坐标2 在售 成交 看房 地铁距离 链接 i = 0 ws.write(i, 0, u'城区', mystyle) ws.write(i, 1, u'板块', mystyle) ws.write(i, 2, u'小区名称', mystyle) ws.write(i, 3, u'年代', mystyle) ws.write(i, 4, u'编号', mystyle) ws.write(i, 5, u'均价', mystyle) ws.write(i, 6, u'坐标1', mystyle) ws.write(i, 7, u'坐标2', mystyle) ws.write(i, 8, u'在售', mystyle) ws.write(i, 9, u'成交', mystyle) ws.write(i, 10, u'看房', mystyle) ws.write(i, 11, u'地铁距离', mystyle) ws.write(i, 12, u'链接', mystyle) badBG.pattern_back_colour = 'blue' mystyle.pattern = badBG for obj in coll.find(): i = i + 1 mystyle.num_format_str = 'general' ws.write(i, 0, obj['city'], mystyle) ws.write(i, 1, obj['town'], mystyle) ws.write(i, 2, obj['name'], mystyle) ws.write(i, 3, obj['age'], mystyle) ws.write(i, 4, obj['key'], mystyle) mystyle.num_format_str = '0.00' ws.write(i, 5, obj['average_price'] , mystyle) mystyle.num_format_str = 'general' ws.write(i, 6, obj['gps_y'], mystyle) ws.write(i, 7, obj['gps_x'], mystyle) mystyle.num_format_str = '0' ws.write(i, 8, obj['onsale_num'], mystyle) ws.write(i, 9, obj['deal_num'], mystyle) ws.write(i, 10, obj['watch_num'], mystyle) mystyle.num_format_str = '0.00' ws.write(i, 11, obj['m_dist'], mystyle) mystyle.num_format_str = 'general' ws.write(i, 12, obj['link'], mystyle) # print obj['_id'] # ws.write(i,17,ss,mystyle) sDate = 'E:\\' + sDate + '.xls' wb.save(sDate)
def export_csv(self, cr, uid, ids, context=None): adj_obj = self.pool.get('ir.attachment') if context == None: context = {} tesoreria = self.browse(cr, uid, ids[0]) fileDoc = xl.Workbook() boldFont16=xl.Font() boldFont16.bold=True boldFont16.height=260 boldS16 = xl.XFStyle() boldS16.font = boldFont16 boldFont14=xl.Font() boldFont14.bold=True boldFont14.height=220 boldS14 = xl.XFStyle() boldS14.font = boldFont14 boldFont=xl.Font() boldFont.bold=True boldS = xl.XFStyle() boldS.font = boldFont boldNS = xl.XFStyle() boldNS.font = boldFont boldNS.num_format_str = '#,##0.00' numS = xl.XFStyle() numS.num_format_str = '#,##0.00' sheet1 = fileDoc.add_sheet("Prevision Tesoreria") sheet1.write(1, 0, 'PREVISION TESORERIA',boldS16) sheet1.write(4, 0, 'Nombre:',boldS14) sheet1.write(4, 1, tesoreria.name,boldS14) sheet1.write(4, 3, 'SALDOS',boldS14) sheet1.write(6, 0, 'Fecha Inicio:',boldS) sheet1.write(6, 1, tesoreria.inicio_validez) sheet1.write(6, 3, 'Saldo Inicio:',boldS) sheet1.write(6, 4, str(tesoreria.saldo_inicial), numS) sheet1.write(7, 0, 'Fecha Final:',boldS) sheet1.write(7, 1, tesoreria.fin_validez) sheet1.write(7, 3, 'Saldo Final:',boldS) sheet1.write(7, 4, str(tesoreria.saldo_final),boldNS) if tesoreria.desglose_saldo: sheet1 = self.export_desglose_saldo(cr, uid, ids, sheet1, context) if tesoreria.facturas_emit: sheet2 = fileDoc.add_sheet("Facturas Emitidas") sheet2 = self.export_facturas_emitidas(cr, uid, ids, sheet2, context) if tesoreria.facturas_rec: sheet3 = fileDoc.add_sheet("Facturas Recibidas") sheet3 = self.export_facturas_recibidas(cr, uid, ids, sheet3, context) if tesoreria.pagos_period: sheet4 = fileDoc.add_sheet("Pagos Periodicos") sheet4 = self.export_pagos_periodicos(cr, uid, ids, sheet4, context) if tesoreria.pagos_var: sheet5 = fileDoc.add_sheet("Pagos Variables") sheet5 = self.export_pagos_variables(cr, uid, ids, sheet5, context) if tesoreria.pagos_rece: sheet6 = fileDoc.add_sheet("Cobros Unicos") sheet6 = self.export_cobros_clientes(cr, uid, ids, sheet6, context) if tesoreria.pagos_cash: sheet7 = fileDoc.add_sheet("Cash-Flow") sheet7 = self.export_cash_flow(cr, uid, ids, sheet7, context) fname = 'Tesoreria_' + tesoreria.name + '.xls' file = StringIO.StringIO() out = fileDoc.save(file) fileDocFin = base64.encodestring(file.getvalue()) res = { 'csv_file': fileDocFin, 'csv_fname': fname } wiz_id = self.pool.get('export.csv.wiz').create(cr, uid, res) adj_list = adj_obj.search(cr, uid, [('res_id', '=', tesoreria.id), ('res_model', '=', 'l10n.es.tesoreria')]) kont = 1 if adj_list: kont = len(adj_list) + 1 adj_obj.create(cr, uid, { 'name': ('Tesoreria ') + tesoreria.name + ' v.' + str(kont), 'datas': fileDocFin, 'datas_fname': fname, 'res_model': 'l10n.es.tesoreria', 'res_id': tesoreria.id, }, context=context) return { 'type': 'ir.actions.act_window', 'res_model': 'export.csv.wiz', 'view_type': 'form', 'view_mode': 'form', 'nodestroy': True, 'res_id': wiz_id, 'target': 'new', }
def saveHouseToFile() : wb = pyExcelerator.Workbook() ws = wb.add_sheet('pages1') myfont = pyExcelerator.Font() myfont.name = u'宋体' myfont.height = 0x00DC mystyle = pyExcelerator.XFStyle() mystyle.font = myfont badBG = Formatting.Pattern() badBG.pattern = badBG.NO_PATTERN badBG.pattern_back_colour = 'red' mystyle.pattern = badBG # mystyle.num_format_str = '0' client = MongoClient('localhost', 27017) db = client['lianjia'] # sDate = settings['MONGODB_DOCNAME'] + time.strftime('%Y%m%d', time.localtime(time.time())) # coll = db['house_20170712'] sDate = 'house_' + time.strftime('%Y%m%d', time.localtime(time.time())) coll = db[sDate] # 城区 板块 小区名称 户型 面积 价格 单价 楼层 朝向 房龄 小区ID 小区链接 标题 详情链接 图片 标签0 标签1 标签2 i = 0 ws.write(i, 0, u'城区', mystyle) ws.write(i, 1, u'板块', mystyle) ws.write(i, 2, u'小区名称', mystyle) ws.write(i, 3, u'户型', mystyle) ws.write(i, 4, u'面积', mystyle) ws.write(i, 5, u'价格', mystyle) ws.write(i, 6, u'单价', mystyle) ws.write(i, 7, u'楼层', mystyle) ws.write(i, 8, u'朝向', mystyle) ws.write(i, 9, u'房龄', mystyle) ws.write(i, 10, u'小区ID', mystyle) ws.write(i, 11, u'小区链接', mystyle) ws.write(i, 12, u'标题', mystyle) ws.write(i, 13, u'房源编号', mystyle) ws.write(i, 14, u'详情链接', mystyle) ws.write(i, 15, u'图片', mystyle) ws.write(i, 16, u'标签0', mystyle) ws.write(i, 17, u'标签1', mystyle) ws.write(i, 18, u'标签2', mystyle) badBG.pattern_back_colour = 'blue' mystyle.pattern = badBG for obj in coll.find(): i = i + 1 mystyle.num_format_str = 'general' ws.write(i, 0, obj['city'], mystyle) ws.write(i, 1, obj['town'], mystyle) ws.write(i, 2, obj['community_title'], mystyle) ws.write(i, 3, obj['model'], mystyle) ws.write(i, 4, obj['area'], mystyle) mystyle.num_format_str = '0.00' ws.write(i, 5, int( obj['price'].encode('gbk') ), mystyle) ws.write(i, 6, int( obj['average_price'].encode('gbk') ), mystyle) mystyle.num_format_str = 'general' ws.write(i, 7, obj['tall'], mystyle) ws.write(i, 8, obj['faceto'], mystyle) mystyle.num_format_str = '0' if len( obj['age'] ) <= 0 : theage = 0 else : theage = int(obj['age'].encode('gbk')) ws.write(i, 9, theage, mystyle) mystyle.num_format_str = 'general' ws.write(i, 10, obj['community_id'], mystyle) ws.write(i, 11, obj['community'], mystyle) ws.write(i, 12, obj['title'], mystyle) ws.write(i, 13, obj['house_id'], mystyle) ws.write(i,14,obj['link'],mystyle) ws.write(i,15,obj['image'],mystyle) ws.write(i,16,obj['tag0'],mystyle) ws.write(i,17,obj['tag1'],mystyle) ws.write(i,18,obj['tag2'],mystyle) # print obj['_id'] # ws.write(i,17,ss,mystyle) sDate = 'E:\\' + sDate + '.xls' wb.save(sDate)
def ExportExcel(self, event=None): """ Export Excel """ if self.mode == "attente": titre = _(u"Inscriptions en attente") else: titre = _(u"Inscriptions refusées") # Demande à l'utilisateur le nom de fichier et le répertoire de destination nomFichier = "ExportExcel_%s.xls" % datetime.datetime.now().strftime( "%Y%m%d%H%M%S") wildcard = "Fichier Excel (*.xls)|*.xls|" \ "All files (*.*)|*.*" sp = wx.StandardPaths.Get() cheminDefaut = sp.GetDocumentsDir() dlg = wx.FileDialog( None, message= _(u"Veuillez sélectionner le répertoire de destination et le nom du fichier" ), defaultDir=cheminDefaut, defaultFile=nomFichier, wildcard=wildcard, style=wx.FD_SAVE) dlg.SetFilterIndex(0) if dlg.ShowModal() == wx.ID_OK: cheminFichier = dlg.GetPath() dlg.Destroy() else: dlg.Destroy() return # Le fichier de destination existe déjà : if os.path.isfile(cheminFichier) == True: dlg = wx.MessageDialog( None, _(u"Un fichier portant ce nom existe déjà. \n\nVoulez-vous le remplacer ?" ), "Attention !", wx.YES_NO | wx.NO_DEFAULT | wx.ICON_EXCLAMATION) if dlg.ShowModal() == wx.ID_NO: return False dlg.Destroy() else: dlg.Destroy() # Export import pyExcelerator # Création d'un classeur wb = pyExcelerator.Workbook() # Création d'une feuille ws1 = wb.add_sheet(titre) fntLabel = pyExcelerator.Font() fntLabel.name = 'Verdana' fntLabel.bold = True al = pyExcelerator.Alignment() al.horz = pyExcelerator.Alignment.HORZ_LEFT al.vert = pyExcelerator.Alignment.VERT_CENTER ar = pyExcelerator.Alignment() ar.horz = pyExcelerator.Alignment.HORZ_RIGHT ar.vert = pyExcelerator.Alignment.VERT_CENTER pat = pyExcelerator.Pattern() pat.pattern = pyExcelerator.Pattern.SOLID_PATTERN pat.pattern_fore_colour = 0x01F styleDate = pyExcelerator.XFStyle() styleDate.alignment = al styleDate.font.bold = True # Entetes et largeurs des colonnes colonnes = [ (_(u"Date"), 8000), (_(u"Groupe"), 8000), (_(u"Dispo"), 2000), (_(u"N°"), 2000), (_(u"Individu"), 10000), (_(u"Date de saisie"), 10000), (_(u"Catégorie de tarif"), 10000), ] index = 0 for label, largeur in colonnes: ws1.col(index).width = largeur ws1.write(0, index, label) index += 1 # Contenu x = 1 for date, listeGroupes in self.listeImpression: for nomGroupe, listeIndividus in listeGroupes: for dictIndividu in listeIndividus: placeDispo = dictIndividu["placeDispo"] if placeDispo == True: placeDispoTxt = _(u"Oui") else: placeDispo = "" ws1.write(x, 0, date, styleDate) ws1.write(x, 1, nomGroupe) ws1.write(x, 2, placeDispo) ws1.write(x, 3, dictIndividu["num"]) ws1.write(x, 4, dictIndividu["nomIndividu"]) ws1.write(x, 5, dictIndividu["texteDateSaisie"]) ws1.write(x, 6, dictIndividu["nomCategorie"]) x += 1 # Finalisation du fichier xls wb.save(cheminFichier) # Confirmation de création du fichier et demande d'ouverture directe dans Excel txtMessage = _( u"Le fichier Excel a été créé avec succès. Souhaitez-vous l'ouvrir dès maintenant ?" ) dlgConfirm = wx.MessageDialog( None, txtMessage, _(u"Confirmation"), wx.YES_NO | wx.NO_DEFAULT | wx.ICON_QUESTION) reponse = dlgConfirm.ShowModal() dlgConfirm.Destroy() if reponse == wx.ID_NO: return else: FonctionsPerso.LanceFichierExterne(cheminFichier)
def write(seq_records, options): if options.input_type == 'prot': return #Open up TXT file and XLS record outfolder = options.full_outputfolder_path txtfile = open(path.join(outfolder, "geneclusters.txt"), "w") wb = Workbook() font1 = pyExcelerator.Font() style1 = pyExcelerator.XFStyle() style1.font = font1 font1.bold = True ws0 = wb.add_sheet('0') ws0.write(0, 0, "Input accession number", style1) ws0.write(0, 1, "Input name", style1) ws0.write(0, 2, "Gene cluster type", style1) ws0.write(0, 3, "Gene cluster genes", style1) ws0.write(0, 4, "Gene cluster gene accessions", style1) if options.knownclusterblast: ws0.write(0, 5, "Compound with gene cluster of highest homology", style1) #For each gene cluster, write out info column = 1 for seq_record in seq_records: clusters = utils.get_cluster_features(seq_record) for cluster in clusters: clustertype = utils.get_cluster_type(cluster) clusternr = utils.get_cluster_number(cluster) clustergenes = [ utils.get_gene_id(cds) for cds in utils.get_cluster_cds_features(cluster, seq_record) ] accessions = [ utils.get_gene_acc(cds) for cds in utils.get_cluster_cds_features(cluster, seq_record) ] ws0.write(column, 0, seq_record.id) try: ws0.write(column, 1, seq_record.description) except: ws0.write( column, 1, "Name to long to be contained in Excel cell; see txt file in downloadable zip archive." ) ws0.write(column, 2, clustertype) try: ws0.write(column, 3, ";".join(clustergenes)) except: ws0.write( column, 3, "Too many genes to be contained in Excel cell; see txt file in downloadable zip archive." ) try: ws0.write(column, 4, ";".join(accessions)) except: ws0.write( column, 4, "Too many genes to be contained in Excel cell; see txt file in downloadable zip archive." ) if hasattr(seq_record, 'closestcompounddict') and \ seq_record.closestcompounddict.has_key(clusternr): ws0.write(column, 5, seq_record.closestcompounddict[clusternr]) column += 1 txtfile.write("\t".join([ seq_record.id, seq_record.description, clustertype, ";".join( clustergenes), ";".join(accessions) ]) + "\n") wb.save(path.join(outfolder, "%s.geneclusters.xls" % seq_record.id))
def ExportExcel(listview=None, grid=None, titre=_(u"Liste"), listeColonnes=None, listeValeurs=None, autoriseSelections=True): """ Export de la liste au format Excel """ # Plus de sélection pour éviter les bugs !!!! autoriseSelections = False # Vérifie si données bien présentes if (listview != None and len(listview.donnees) == 0) or (grid != None and (grid.GetNumberRows() == 0 or grid.GetNumberCols() == 0)): dlg = wx.MessageDialog(None, _(u"Il n'y a aucune donnée dans la liste !"), "Erreur", wx.OK | wx.ICON_ERROR) dlg.ShowModal() dlg.Destroy() return # Récupération des valeurs if listview != None and listeColonnes == None and listeValeurs == None : listeColonnes, listeValeurs = GetValeursListview(listview, format="original") if grid != None and listeColonnes == None and listeValeurs == None : autoriseSelections = False listeColonnes, listeValeurs = GetValeursGrid(grid) # Selection des lignes if autoriseSelections == True : dlg = DLG_Selection_liste.Dialog(None, listeColonnes, listeValeurs, type="exportExcel") if dlg.ShowModal() == wx.ID_OK: listeSelections = dlg.GetSelections() dlg.Destroy() else: dlg.Destroy() return False # Choix Action dlg = DLG_Choix_action(None) reponse = dlg.ShowModal() dlg.Destroy() if reponse == 100 : mode = "enregistrer" elif reponse == 200 : mode = "email" else : return # Définit le nom et le chemin du fichier nomFichier = "ExportExcel_%s.xls" % datetime.datetime.now().strftime("%Y%m%d%H%M%S") # Mode Enregistrer if mode == "enregistrer" : # Demande à l'utilisateur le nom de fichier et le répertoire de destination wildcard = "Fichier Excel (*.xls)|*.xls|" \ "All files (*.*)|*.*" sp = wx.StandardPaths.Get() cheminDefaut = sp.GetDocumentsDir() dlg = wx.FileDialog( None, message = _(u"Veuillez sélectionner le répertoire de destination et le nom du fichier"), defaultDir=cheminDefaut, defaultFile = nomFichier, wildcard = wildcard, style = wx.SAVE ) dlg.SetFilterIndex(0) if dlg.ShowModal() == wx.ID_OK: cheminFichier = dlg.GetPath() dlg.Destroy() else: dlg.Destroy() return # Le fichier de destination existe déjà : if os.path.isfile(cheminFichier) == True : dlg = wx.MessageDialog(None, _(u"Un fichier portant ce nom existe déjà. \n\nVoulez-vous le remplacer ?"), "Attention !", wx.YES_NO | wx.NO_DEFAULT | wx.ICON_EXCLAMATION) if dlg.ShowModal() == wx.ID_NO : return False dlg.Destroy() else: dlg.Destroy() # Mode Envoyer par Email if mode == "email" : cheminFichier = UTILS_Fichiers.GetRepTemp(fichier=nomFichier) # Export import pyExcelerator # Création d'un classeur wb = pyExcelerator.Workbook() # Création d'une feuille ws1 = wb.add_sheet(titre) # Remplissage de la feuille al = pyExcelerator.Alignment() al.horz = pyExcelerator.Alignment.HORZ_LEFT al.vert = pyExcelerator.Alignment.VERT_CENTER ar = pyExcelerator.Alignment() ar.horz = pyExcelerator.Alignment.HORZ_RIGHT ar.vert = pyExcelerator.Alignment.VERT_CENTER styleEuros = pyExcelerator.XFStyle() styleEuros.num_format_str = '"$"#,##0.00_);("$"#,##' styleEuros.alignment = ar styleDate = pyExcelerator.XFStyle() styleDate.num_format_str = 'DD/MM/YYYY' styleDate.alignment = ar styleHeure = pyExcelerator.XFStyle() styleHeure.num_format_str = "[hh]:mm" styleHeure.alignment = ar # Création des labels de colonnes x = 0 y = 0 for labelCol, alignement, largeur, nomChamp in listeColonnes : try : if "CheckState" in unicode(nomChamp) : nomChamp = "Coche" except : pass ws1.write(x, y, labelCol) ws1.col(y).width = largeur*42 y += 1 # ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- # Création des lignes def RechercheFormatFromChaine(valeur): """ Recherche le type de la chaîne """ if valeur.endswith(SYMBOLE) : # Si c'est un montant en euros try : if valeur.startswith("- ") : valeur = valeur.replace("- ", "-") if valeur.startswith("+ ") : valeur = valeur.replace("+ ", "") nbre = float(valeur[:-1]) return (nbre, styleEuros) except : pass # Si c'est un nombre try : if valeur.startswith("- ") : valeur = valeur.replace("- ", "-") nbre = float(valeur) return (nbre, None) except : pass # Si c'est une date try : if len(valeur) == 10 : if valeur[2] == "/" and valeur[5] == "/" : return (valeur, styleDate) except : pass if type(valeur) == datetime.timedelta : return (valeur, styleHeure) # Si c'est une heure try : if len(valeur) > 3 : if ":" in valeur : separateur = ":" elif "h" in valeur : separateur = "h" else : separateur = None if separateur != None : heures, minutes = valeur.split(separateur) valeur = datetime.timedelta(minutes= int(heures)*60 + int(minutes)) # valeur = datetime.time(hour=int(valeur.split(separateur)[0]), minute=int(valeur.split(separateur)[1])) return (valeur, styleHeure) except : pass return unicode(valeur), None # ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- def RechercheFormat(valeur): """ Recherche le type de la donnée """ if type(valeur) == decimal.Decimal : valeur = float(valeur) return (valeur, styleEuros) if type(valeur) == float : return (valeur, None) if type(valeur) == int : return (valeur, None) if type(valeur) == datetime.date : valeur = UTILS_Dates.DateDDEnFr(valeur) return (valeur, styleDate) if type(valeur) == datetime.timedelta : return (valeur, styleHeure) try : if len(valeur) > 3 : if ":" in valeur : separateur = ":" elif "h" in valeur : separateur = "h" else : separateur = None if separateur != None : donnees = valeur.split(separateur) if len(donnees) == 2 : heures, minutes = donnees if len(donnees) == 3 : heures, minutes, secondes = donnees valeur = datetime.timedelta(minutes= int(heures)*60 + int(minutes)) # valeur = datetime.time(hour=int(valeur.split(separateur)[0]), minute=int(valeur.split(separateur)[1])) return (valeur, styleHeure) except : pass if type(valeur) in (str, unicode) : if len(valeur) == 10 : if valeur[2] == "/" and valeur[5] == "/" : return (valeur, styleDate) if valeur[4] == "-" and valeur[7] == "-" : return (UTILS_Dates.DateEngFr(valeur), styleDate) return unicode(valeur), None # ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- x = 1 y = 0 for valeurs in listeValeurs : if autoriseSelections == False or int(valeurs[0]) in listeSelections : for valeur in valeurs : if valeur == None : valeur = u"" # Recherche s'il y a un format de nombre ou de montant if listview != None : valeur, format = RechercheFormat(valeur) #RechercheFormatFromChaine(valeur) else : valeur, format = RechercheFormatFromChaine(valeur) # Enregistre la valeur if format != None : ws1.write(x, y, valeur, format) else: ws1.write(x, y, valeur) y += 1 x += 1 y = 0 # Finalisation du fichier xls try : wb.save(cheminFichier) except : dlg = wx.MessageDialog(None, _(u"Il est impossible d'enregistrer le fichier Excel. Veuillez vérifier que ce fichier n'est pas déjà ouvert en arrière-plan."), "Erreur", wx.OK | wx.ICON_ERROR) dlg.ShowModal() dlg.Destroy() return # Confirmation de création du fichier et demande d'ouverture directe dans Excel if mode == "enregistrer" : txtMessage = _(u"Le fichier Excel a été créé avec succès. Souhaitez-vous l'ouvrir dès maintenant ?") dlgConfirm = wx.MessageDialog(None, txtMessage, _(u"Confirmation"), wx.YES_NO|wx.NO_DEFAULT|wx.ICON_QUESTION) reponse = dlgConfirm.ShowModal() dlgConfirm.Destroy() if reponse == wx.ID_NO: return else: FonctionsPerso.LanceFichierExterne(cheminFichier) # Envoyer par Email if mode == "email" : from Dlg import DLG_Mailer dlg = DLG_Mailer.Dialog(None) dlg.ChargerModeleDefaut() dlg.SetPiecesJointes([cheminFichier,]) dlg.ShowModal() dlg.Destroy()
def _create_dog_letters(self, cr, uid, data, context): #Open new workbook mydoc = Workbook() #Add a worksheet mysheet = mydoc.add_sheet("test") #write headers header_font = xl.Font() #make a font object header_font.bold = True header_font.underline = True #font needs to be style actually header_style = xl.XFStyle() header_style.font = header_font obj_dogs = pooler.get_pool(cr.dbname).get('labo.dog') obj_ln = pooler.get_pool(cr.dbname).get('letters.numbers') dogs_ids = obj_dogs.browse(cr, uid, data['ids'], context) buf = StringIO.StringIO() keys = [ 'Progenus Num', 'Name', 'Breed', 'Tatoo', 'Chip', 'Origin', 'Sex', 'Date of birth', 'printed date', 'AHT121-1', 'AHT121-2', 'AHT137-1', 'AHT137-2', 'AHTh171-1', 'AHTh171-2', 'AHTh260-1', 'AHTh260-2', 'AHTk211-1', 'AHTk211-2', 'AHTk253-1', 'AHTk253-2', 'AMELOGENIN-1', 'AMELOGENIN-2', 'CXX279-1', 'CXX279-2', 'FH2054-1', 'FH2054-2', 'FH2848-1', 'FH2848-2', 'Inra21-1', 'Inra21-2', 'Inu005-1', 'Inu005-2', 'Inu030-1', 'Inu030-2', 'Inu055-1', 'Inu055-2', 'REN162C04-1', 'REN162C04-2', 'REN169D001-1', 'REN169D001-2', 'REN169O18-1', 'REN169O18-2', 'REN247M23-1', 'REN247M23-2', 'REN54P11-1', 'REN54P11-2' ] for col, value in enumerate(keys): mysheet.write(0, col, value, header_style) writer = csv.writer(buf, 'TINY', delimiter='\t', lineterminator='\r\n') writer.writerow(keys) row_lst = [] for dog in dogs_ids: row = [] if len(dog.allele_ids) and not dog.c_done: obj_dogs.write(cr, uid, [dog.id], {'c_done': True}) row.append(dog.progenus_number or ' ') row.append(dog.name or ' ') row.append(dog.race or ' ') row.append(dog.tatoo or ' ') row.append(dog.ship or ' ') row.append(dog.origin or '') row.append(dog.sex or ' ') row.append(dog.birthdate or ' ') row.append(data['form']['date_print'] or ' ') for c in keys[9:]: if keys.index(c) % 2 == 0: c = c.split('-')[0] cr.execute("SELECT r.allele_dog1, r.allele_dog2 "\ "from dog_allele r, labo_dog d where r.dog_id1 = d.id and "\ " d.id =%d and r.marker_dog ilike '%s' and d.progenus_number='%s'"%(dog.id, c,dog.progenus_number)) res = cr.fetchone() if res and res[0]: ln_ids_0 = obj_ln.search( cr, uid, [('name_number', '=', res[0]), ('name', 'ilike', c)]) try: a_1 = obj_ln.browse(cr, uid, ln_ids_0) row.append(a_1[0].name_letter or ' ') except: raise wizard.except_wizard( 'Error!', 'The code of the marker %s with the allele %s does not exist in the table' % (c, res[0])) else: row.append(' ') if res and res[1]: ln_ids_1 = obj_ln.search( cr, uid, [('name_number', '=', res[1]), ('name', 'ilike', c)]) try: a_2 = obj_ln.browse(cr, uid, ln_ids_1) row.append(a_2[0].name_letter or ' ') except: raise wizard.except_wizard( 'Error!', 'The code of the marker %s with the allele %s does not exist in the table' % (c, res[0])) else: row.append(' ') row_lst.append(row) writer.writerow(row) for row_num, row_values in enumerate(row_lst): row_num += 1 #start at row 1 row_values = [str(x).decode('utf8') for x in row_values] for col, value in enumerate(row_values): #normal row mysheet.write(row_num, col, value) file = StringIO.StringIO() out = mydoc.save(file) out = base64.encodestring(file.getvalue()) return {'data': out}
def rec2excel(r, ws, formatd=None, rownum=0, colnum=0): """ save record array r to excel pyExcelerator worksheet ws starting at rownum. if ws is string like, assume it is a filename and save to it start writing at rownum, colnum formatd is a dictionary mapping dtype name -> mlab.Format instances The next rownum after writing is returned """ autosave = False if cbook.is_string_like(ws): filename = ws wb = excel.Workbook() ws = wb.add_sheet('worksheet') autosave = True if formatd is None: formatd = dict() formats = [] font = excel.Font() font.bold = True stylehdr = excel.XFStyle() stylehdr.font = font for i, name in enumerate(r.dtype.names): dt = r.dtype[name] format = formatd.get(name) if format is None: format = mlab.defaultformatd.get(dt.type, mlab.FormatObj()) format = xlformat_factory(format) ws.write(rownum, colnum+i, name, stylehdr) formats.append(format) rownum+=1 ind = npy.arange(len(r.dtype.names)) for row in r: for i in ind: val = row[i] format = formats[i] val = format.toval(val) if format.xlstyle is None: ws.write(rownum, colnum+i, val) else: if mlab.safe_isnan(val): ws.write(rownum, colnum+i, 'NaN') elif mlab.safe_isinf(val): if val<0: sign='-' else: sign='+' ws.write(rownum, colnum+i, '%sInf'%sign) else: ws.write(rownum, colnum+i, val, format.xlstyle) rownum += 1 if autosave: wb.save(filename) return rownum
def _get_file(self, cr, uid, data, context): #Open new workbook mydoc = Workbook() #Add a worksheet mysheet = mydoc.add_sheet("test") #write headers header_font = xl.Font() #make a font object header_font.bold = True header_font.underline = True #font needs to be style actually header_style = xl.XFStyle() header_style.font = header_font row_lst = [] obj_sample = pooler.get_pool(cr.dbname).get('labo.sample') obj_r = pooler.get_pool(cr.dbname).get('labo.analysis.request') obj_dog = pooler.get_pool(cr.dbname).get('labo.dog') v_sample = obj_sample.browse(cr, uid, data['ids'], context) v_r = obj_sample.browse(cr, uid, data['ids'], context) # view_type=v_sample[0].cont seq_obj = pooler.get_pool(cr.dbname).get('ir.sequence') v_hist = pooler.get_pool(cr.dbname).get('file.history') keys = [ 'LNPRAP', 'LPSERV', 'LPDOSS', 'LPSEQ', 'LPENUM', 'LPENOM', 'LPENO2', 'LPEADR', 'LPELOC', 'LPRACE', 'LPFNOM', 'LPFNUM', 'LPFTAT', 'LPFPUC', 'LPFNOR', 'LPFDTN', 'LPFRLB', 'LPFLLB', 'LPFNLB', 'LPFNPR', 'LPMNOM', 'LPMNUM', 'LPMTAT', 'LPMPUC', 'LPMNOR', 'LPMDTN', 'LPMRLB', 'LPMLLB', 'LPMNLB', 'LPMNPR', 'LPCSEX', 'LPCTAT', 'LPCPUC', 'LPCDTN', 'LPCNPR', 'LPCFIL', 'LPFILE', 'LPDTRC', 'LPNOMT' ] view_b = v_r[0] view_type = view_b.sample_id.type_id.code buf = StringIO.StringIO() for col, value in enumerate(keys): mysheet.write(0, col, value, header_style) writer = csv.writer(buf, 'TINY', delimiter=',', lineterminator='\n') writer.writerow(keys) name_f = [] last_f1 = '' row = [] cr.execute( "SELECT number_next,code,id from ir_sequence where name='RCS'") res_cr2 = cr.fetchone() cr.execute( "SELECT number_next,code,id from ir_sequence where name='RFC'") res_cr1 = cr.fetchone() seq_t = seq_obj.browse(cr, uid, res_cr1[1]) # last_f1=seq_obj.get(cr,uid,res_cr1[1]) +'.xls' if (view_type == "EMPDOG" or view_type == "EMPCHE") and v_r and v_r[0]: for i in v_r: row = [] last_f1 = seq_obj.get(cr, uid, res_cr1[1]) + '.xls' if i.dog_child: # and i.dog_child.v_done2==0: num_req = i.sample_id.type_id.code + '/' + i.sample_id.name row.append(num_req or '') lp_serv = i.lp_serv or '' row.append(lp_serv) lp_doss = i.lp_doss or '' row.append(lp_doss) seq = i.dog_child and int( i.dog_child.seq) > 0 and i.dog_child.seq or '' row.append(seq) elev_id = i.preleveur1_id and i.preleveur1_id.ref or '' row.append(elev_id) elev_name = i.preleveur1_id and i.preleveur1_id.name or '' row.append(elev_name) elev_name2 = i.preleveur1_id and i.preleveur1_id.address and i.preleveur1_id.address[ 0].name or '' row.append(elev_name2) addr = i.preleveur1_id and i.preleveur1_id.address and i.preleveur1_id.address[ 0].street or '' row.append(addr) loc = i.preleveur1_id and i.preleveur1_id.address and i.preleveur1_id.address[ 0].city or '' zip = i.preleveur1_id and i.preleveur1_id.address and i.preleveur1_id.address[ 0].zip or '' row.append(zip + ' ' + loc) race = i.dog_child and i.dog_child.race or '' row.append(race) mother = i.dog_mother and i.dog_mother.name or '' row.append(mother) m_ped = i.dog_mother and i.dog_mother.pedigree or '' row.append(m_ped) m_tat = i.dog_mother and i.dog_mother.tatoo or '' row.append(m_tat) m_ship = i.dog_mother and i.dog_mother.ship or '' row.append(m_ship) m_org = i.dog_mother and i.dog_mother.origin or '' row.append(m_org) m_birth = i.dog_mother and i.dog_mother.birthdate or '' row.append(m_birth) m_labo = i.dog_mother and i.dog_mother.ref_dog or '' row.append(m_labo) m_labo_name = i.dog_mother and i.dog_mother.labo_id and i.dog_mother.labo_id.name or '' row.append(m_labo_name) m_labo_code = i.dog_mother and i.dog_mother.labo_id and i.dog_mother.labo_id.code or '' row.append(m_labo_code) m_prog = i.dog_mother and i.dog_mother.progenus_number or '' row.append(m_prog) father = i.dog_father and i.dog_father.name or '' row.append(father) f_ped = i.dog_father and i.dog_father.pedigree or '' row.append(f_ped) f_tat = i.dog_father and i.dog_father.tatoo or '' row.append(f_tat) f_ship = i.dog_father and i.dog_father.ship or '' row.append(f_ship) f_org = i.dog_father and i.dog_father.origin or '' row.append(f_org) f_birth = i.dog_father and i.dog_father.birthdate or '' row.append(f_birth) f_labo = i.dog_father and i.dog_father.ref_dog or '' row.append(f_labo) f_labo_name = i.dog_father and i.dog_father.labo_id and i.dog_father.labo_id.name or '' row.append(f_labo_name) f_labo_code = i.dog_father and i.dog_father.labo_id and i.dog_father.labo_id.code or '' row.append(f_labo_code) f_prog = i.dog_father and i.dog_father.progenus_number or '' row.append(f_prog) c_sex = i.dog_child and i.dog_child.sex or '' row.append(c_sex) c_tat = i.dog_child and i.dog_child.tatoo or '' row.append(c_tat) c_ship = i.dog_child and i.dog_child.ship or '' row.append(c_ship) c_bth = '' if i.dog_child and i.dog_child.birthdate: c_bth = time.strftime( '%d/%m/%y', time.strptime(i.dog_child.birthdate, '%Y-%m-%d')) row.append(c_bth) c_prognum = i.dog_child and i.dog_child.progenus_number or '' row.append(c_prognum) res_f = i.res_filiation or '' row.append(res_f) res_lp = i.lp_file or '' row.append(res_lp) res_rd = '' if i.date_reception: res_rd = time.strftime( '%d%m%y', time.strptime(i.date_reception, '%Y-%m-%d')) row.append(res_rd) res_tat = i.tatooer_id and i.tatooer_id.name or '' row.append(res_tat) print "row", row if i.dog_mother: obj_dog.write(cr, uid, [i.dog_mother.id], {'v_done2': True}) v_hist.create(cr, uid, { 'dog_id1': i.dog_mother.id, 'name': last_f1, }) if i.dog_father: obj_dog.write(cr, uid, [i.dog_father.id], {'v_done2': True}) v_hist.create(cr, uid, { 'dog_id1': i.dog_father.id, 'name': last_f1, }) obj_dog.write(cr, uid, [i.dog_child.id], {'v_done2': True}) v_hist.create(cr, uid, { 'dog_id1': i.dog_child.id, 'name': last_f1, }) row_lst.append(row) print "row lst", row_lst writer.writerow(row) elif view_type == "EMPDOG_2": last_f1 = seq_obj.get(cr, uid, res_cr1[1]) + '.xls' for i in v_sample: row = [] num_req = i.sample_id.type_id.code + '/' + i.sample_id.name last_f1 = seq_obj.get(cr, uid, res_cr2[1]) + '.xls' row.append(num_req or '') lp_serv = i.lp_serv or '' row.append(lp_serv) lp_doss = i.lp_doss or '' row.append(lp_doss) if i.dog_mother: seq = i.dog_mother and int( i.dog_mother.seq) > 0 and i.dog_mother.seq or '' row.append(seq) elif i.dog_father: seq = i.dog_father and int( i.dog_father.seq) > 0 and i.dog_father.seq or '' row.append(seq) elev_id = i.preleveur1_id and i.preleveur1_id.ref or '' row.append(elev_id) elev_name = i.preleveur1_id and i.preleveur1_id.name or '' row.append(elev_name) elev_name2 = i.preleveur1_id and i.preleveur1_id.address and i.preleveur1_id.address[ 0].name or '' row.append(elev_name2) addr = i.preleveur1_id and i.preleveur1_id.address and i.preleveur1_id.address[ 0].street or '' row.append(addr) loc = i.preleveur1_id and i.preleveur1_id.address and i.preleveur1_id.address[ 0].city or '' zip = i.preleveur1_id and i.preleveur1_id.address and i.preleveur1_id.address[ 0].zip or '' row.append(zip + ' ' + loc) if i.dog_mother: # and i.dog_mother.v_done2==0: race = i.dog_mother and i.dog_mother.race or '' row.append(race) mother = i.dog_mother and i.dog_mother.name or '' row.append(mother) m_ped = i.dog_mother and i.dog_mother.pedigree or '' row.append(m_ped) m_tat = i.dog_mother and i.dog_mother.tatoo or '' row.append(m_tat) m_ship = i.dog_mother and i.dog_mother.ship or '' row.append(m_ship) m_org = i.dog_mother and i.dog_mother.origin or '' row.append(m_org) m_birth = i.dog_mother and i.dog_mother.birthdate or '' row.append(m_birth) m_labo = i.dog_mother and i.dog_mother.labo_id and i.dog_mother.labo_id.ref or '' row.append(m_labo) m_labo_name = i.dog_mother and i.dog_mother.labo_id and i.dog_mother.labo_id.name or '' row.append(m_labo_name) m_labo_code = i.dog_mother and i.dog_mother.labo_id and i.dog_mother.labo_id.code or '' row.append(m_labo_code) m_prog = i.dog_mother and i.dog_mother.progenus_number or '' row.append(m_prog) while len(row) < len(keys): row.append('') res_rd = '' if i.date_reception: res_rd = time.strftime( '%d%m%y', time.strptime(i.date_reception, '%Y-%m-%d')) row.__setitem__(37, res_rd) res_tat = i.tatooer_id and i.tatooer_id.name or '' row.__setitem__(38, res_tat) obj_dog.write(cr, uid, [i.dog_mother.id], {'v_done2': True}) v_hist.create(cr, uid, { 'dog_id1': i.dog_mother.id, 'name': last_f1, }) elif i.dog_father: # and i.dog_father.v_done2==0: while len(row) < len(keys): row.append('') father = i.dog_father and i.dog_father.name or '' row.__setitem__(20, father) f_ped = i.dog_father and i.dog_father.pedigree or '' row.__setitem__(21, f_ped) f_tat = i.dog_father and i.dog_father.tatoo or '' row.__setitem__(22, f_tat) f_ship = i.dog_father and i.dog_father.ship or '' row.__setitem__(23, f_ship) f_org = i.dog_father and i.dog_father.origin or '' row.__setitem__(24, f_org) f_birth = i.dog_father and i.dog_father.birthdate or '' row.__setitem__(25, f_birth) f_labo = i.dog_father and i.dog_father.labo_id and i.dog_father.labo_id.ref or '' row.__setitem__(26, f_labo) f_labo_name = i.dog_father and i.dog_father.labo_id and i.dog_father.labo_id.name or '' row.__setitem__(27, f_labo_name) f_labo_code = i.dog_father and i.dog_father.labo_id and i.dog_father.labo_id.code or '' row.__setitem__(28, f_labo_code) f_prog = i.dog_father and i.dog_father.progenus_number or '' row.__setitem__(29, f_prog) # res_f=i.res_filiation or '' # row.insert(30,res_f) res_lp = i.lp_file or '' row.__setitem__(36, res_lp) res_rd = '' res_rd = '' if i.date_reception: res_rd = time.strftime( '%d%m%y', time.strptime(i.date_reception, '%Y-%m-%d')) row.__setitem__(37, res_rd) res_tat = i.tatooer_id and i.tatooer_id.name or '' row.__setitem__(38, res_tat) obj_dog.write(cr, uid, [i.dog_father.id], {'v_done2': True}) v_hist.create(cr, uid, { 'dog_id1': i.dog_father.id, 'name': last_f1, }) row_lst.append(row) writer.writerow(row) for row_num, row_values in enumerate(row_lst): print row_num, row_values row_num += 1 #start at row 1 row_values = [str(x).decode('utf8') for x in row_values] for col, value in enumerate(row_values): #normal row # print "ttt",col, value mysheet.write(row_num, col, value) file = StringIO.StringIO() out = mydoc.save(file) out = base64.encodestring(file.getvalue()) # buf.close() return {'data': out, 'file_name': last_f1}
def ExportExcel(self, event=None): """ Export Excel """ titre = _(u"Inscriptions") # Demande à l'utilisateur le nom de fichier et le répertoire de destination nomFichier = "ExportExcel_%s.xls" % datetime.datetime.now().strftime("%Y%m%d%H%M%S") wildcard = "Fichier Excel (*.xls)|*.xls|" \ "All files (*.*)|*.*" sp = wx.StandardPaths.Get() cheminDefaut = sp.GetDocumentsDir() dlg = wx.FileDialog( None, message = _(u"Veuillez sélectionner le répertoire de destination et le nom du fichier"), defaultDir=cheminDefaut, defaultFile = nomFichier, wildcard = wildcard, style = wx.SAVE ) dlg.SetFilterIndex(0) if dlg.ShowModal() == wx.ID_OK: cheminFichier = dlg.GetPath() dlg.Destroy() else: dlg.Destroy() return # Le fichier de destination existe déjà : if os.path.isfile(cheminFichier) == True : dlg = wx.MessageDialog(None, _(u"Un fichier portant ce nom existe déjà. \n\nVoulez-vous le remplacer ?"), "Attention !", wx.YES_NO | wx.NO_DEFAULT | wx.ICON_EXCLAMATION) if dlg.ShowModal() == wx.ID_NO : return False dlg.Destroy() else: dlg.Destroy() # Export import pyExcelerator # Création d'un classeur wb = pyExcelerator.Workbook() # Création d'une feuille ws1 = wb.add_sheet(titre) # Remplissage de la feuille fntLabel = pyExcelerator.Font() fntLabel.name = 'Verdana' fntLabel.bold = True al = pyExcelerator.Alignment() al.horz = pyExcelerator.Alignment.HORZ_LEFT al.vert = pyExcelerator.Alignment.VERT_CENTER ar = pyExcelerator.Alignment() ar.horz = pyExcelerator.Alignment.HORZ_RIGHT ar.vert = pyExcelerator.Alignment.VERT_CENTER pat = pyExcelerator.Pattern() pat.pattern = pyExcelerator.Pattern.SOLID_PATTERN pat.pattern_fore_colour = 0x01F styleLabel = pyExcelerator.XFStyle() styleLabel.alignment = al styleLabel.pattern = pat styleTotal = pyExcelerator.XFStyle() styleTotal.alignment = al styleTotal.pattern = pat styleTotal.font.bold = True styleTotalNbre = pyExcelerator.XFStyle() styleTotalNbre.alignment = ar styleTotalNbre.pattern = pat styleTotalNbre.font.bold = True styleEuros = pyExcelerator.XFStyle() styleEuros.num_format_str = '"$"#,##0.00_);("$"#,##' styleEuros.alignment = ar styleTotalEuros = pyExcelerator.XFStyle() styleTotalEuros.num_format_str = '"$"#,##0.00_);("$"#,##' styleTotalEuros.alignment = ar styleTotalEuros.pattern = pat styleTotalEuros.font.bold = True # Création des labels de colonnes x = 0 y = 0 for valeur in self.dictImpression["entete"] : ws1.write(x, y, valeur) ws1.col(y).width = 3000 y += 1 ws1.col(0).width = 10000 def RechercheFormat(valeur, titre): """ Recherche si la valeur est un nombre """ format = None # Si c'est un nombre try : nbre = float(valeur) if titre == True : format = styleTotalNbre return (nbre, format) except : pass return False, None # Contenu x = 1 y = 0 for ligne in self.dictImpression["contenu"] : for valeur in ligne : # Recherche si c'est un titre if x-1 in self.dictImpression["coloration"] : titre = True else: titre = False # Recherche s'il y a un format de nombre ou de montant nbre, format = RechercheFormat(valeur, titre) if nbre != False : valeur = nbre if nbre == False and titre == True and format == None : format = styleTotal # Enregistre la valeur if format != None : ws1.write(x, y, valeur, format) else: ws1.write(x, y, valeur) y += 1 x += 1 y = 0 # Total premiereLigne = True for ligne in self.dictImpression["total"] : for valeur in ligne : # Recherche si c'est un titre if premiereLigne == True : titre = True else: titre = False # Recherche s'il y a un format de nombre ou de montant nbre, format = RechercheFormat(valeur, titre) if nbre != False : valeur = nbre if nbre == False and titre == True and format == None : format = styleTotal # Enregistre la valeur if format != None : ws1.write(x, y, valeur, format) else: ws1.write(x, y, valeur) y += 1 premiereLigne = False x += 1 y = 0 # Finalisation du fichier xls wb.save(cheminFichier) # Confirmation de création du fichier et demande d'ouverture directe dans Excel txtMessage = _(u"Le fichier Excel a été créé avec succès. Souhaitez-vous l'ouvrir dès maintenant ?") dlgConfirm = wx.MessageDialog(None, txtMessage, _(u"Confirmation"), wx.YES_NO|wx.NO_DEFAULT|wx.ICON_QUESTION) reponse = dlgConfirm.ShowModal() dlgConfirm.Destroy() if reponse == wx.ID_NO: return else: FonctionsPerso.LanceFichierExterne(cheminFichier)
def detectTransits(inFile, basename, options): ''' @param inFile: open file like object containing data @param basename: prepend this str to filenames written @param options: FIX... list options TODO(schwehr):detect if the input is AIS VDM or XYMT messages ''' ships={} for line in inFile: x,y,m,t = line.split() if m not in ships: ships[m] = [(x,y,t),] continue ships[m].append((x,y,t)) # FIX: maybe not the best way to pick UTM zone utm_zone = lon_to_utm_zone(x) #sys.stderr.write print 'utm_zone:',utm_zone params = {'proj':'utm','zone':utm_zone} proj = Proj(params) transitsFile = None transitsFilename=basename+'.transits' if options.transitData: transitsFile = file(transitsFilename,'w') # Points for the transit if options.gmtMultiSeg: gmtMultiSegFile = file(basename+'.transits.psxy.multiseg','w') summaryFile=None if options.transitFile: summaryFile = file(basename+'.transits.summary.txt','w') # Summary list of transits if options.gnuplot: gp = file(basename+'.gp','w') os.chmod(basename+'.gp',0755) gp.write('#!/usr/bin/env gnuplot\n') gp.write('\n# Requires gnuplot 4.2 or newer\n') gp.write('# Written by ais_transits.py\n\n') if options.transitData: gp.write('set title "'+basename+' transits plot"\n') gp.write('set xlabel "Longitude"\n') gp.write('set ylabel "Latitude"\n') gp.write('set key off\n') gp.write('set grid x\n') gp.write('set grid y\n') gp.write('\n') gp.write('# set yrange [42:43]\n') gp.write('# set xrange [-70.75:-69.75]\n') gp.write('\n') gp.write('set xtics .25\n') gp.write('set ytics .25\n') gp.write('\n') gp.write('\n') gp.write('set terminal pdf\n') gp.write('set output "'+basename+'.pdf"\n') gp.write('plot "'+transitsFilename+'" with l') if options.gpFiles: for filename in options.gpFiles: gp.write(' \\\n ,"'+filename+'" with l title "'+filename+'"') if options.gpPointFiles: for filename in options.gpPointFiles: gp.write(' \\\n ,"'+filename+'" with p title "'+filename+'"') gp.write('\n') gp.write('set terminal gif\n') gp.write('set output "'+basename+'.gif"\n') gp.write('replot\n') gp.write('\n') if options.excel: workbook = excel.Workbook() ws_summary = workbook.add_sheet('Transit Summary') ws_summary_row = 0 ws_transits = workbook.add_sheet('Transits') ws_transits_row = 0 dateTimeStyle = excel.XFStyle() dateTimeStyle.num_format_str = 'M/D/YY h:mm:ss' ws_summary.write(ws_summary_row,0,'Summary of transits for each ship') ws_summary_row += 1 ws_summary.write(ws_summary_row,0,basename) ws_summary_row += 1 col=0 ws_summary.write(ws_summary_row,col,'MMSI'); col += 1 ws_summary.write(ws_summary_row,col,'Num transits'); col += 1 ws_summary.write(ws_summary_row,col,'Time in region (hours)'); col += 1 ws_summary.write(ws_summary_row,col,'AIS Position Count'); col += 1 ws_summary_row += 1 ws_transits.write(ws_transits_row,0,'Vessel Transits based on AIS data produced by ais_transits.py') ws_transits_row += 1 ws_transits.write(ws_transits_row,0,basename) ws_transits_row += 1 col = 0 #ws_transits.write(row,col,'Key');col+=1 ws_transits.write(ws_transits_row,col,'MMSI');col+=1 ws_transits.write(ws_transits_row,col,'Transit');col+=1 ws_transits.write(ws_transits_row,col,'Transit_ID');col+=1 ws_transits.write(ws_transits_row,col,'Start (UTC sec)');col+=1 ws_transits.write(ws_transits_row,col,'Start (UTC)');col+=1 ws_transits.write(ws_transits_row,col,'Start (EDT/local)');col+=1 ws_transits.write(ws_transits_row,col,'End (UTC sec)');col+=1 ws_transits.write(ws_transits_row,col,'End (UTC)');col+=1 ws_transits.write(ws_transits_row,col,'End (EDT/local)');col+=1 ws_transits.write(ws_transits_row,col,'Transit Duration (hours)');col+=1 ws_transits.write(ws_transits_row,col,'Transit Length (km)');col+=1 ws_transits.write(ws_transits_row,col,'AIS Position Count');col+=1 ws_transits_row += 1 totalTransits=0 shipList = ships.keys() shipList=[int(s) for s in shipList] shipList.sort() shipList=[str(s) for s in shipList] #for ship in ships: for ship in shipList: shipTransitFile=None if options.separateShips: shipTransitFile = file(basename+'.'+ship,'w') # shipTransitFile transits=[] # List of start_time,end_time,# of points start=None # Time the current transit started samples=0 # How many samples in the current transit shipTransits=1 t = None print ship if transitsFile: transitsFile.write('# '+ship+'\n') if options.gmtMultiSeg: gmtMultiSegFile.write('>\n') # This is the segment separator default character if options.separateShips: shipTransitFile.write('# '+ship+'\n') max_delta_t = 0 # maximum time between samples for pt in ships[ship]: x = pt[0] y = pt[1] newT = int(pt[2]) #max_delta_t = 0 if None==start: start = newT if t != None: dt = newT - t print dt, max_delta_t if dt > max_delta_t: #print 'setting!' max_delta_t = dt #print max_delta_t if newT>t+3600: shipTransits+=1 if transitsFile: transitsFile.write('\n\n#Begin transit # '+str(shipTransits)+'\n') if options.gmtMultiSeg: gmtMultiSegFile.write('>\n') # This is the segment separator default character if options.separateShips: shipTransitFile.write('\n\n#Begin transit # '+str(shipTransits)+'\n') transits.append((start,t,samples,max_delta_t)) samples = 0 start = newT samples += 1 t = newT if transitsFile: transitsFile.write(pt[0]+' '+pt[1]+' '+ship+' '+pt[2]+'\n') if options.gmtMultiSeg: gmtMultiSegFile.write(pt[0]+' '+pt[1]+'\n') # This is the segment separator default character if options.separateShips: shipTransitFile.write(pt[0]+' '+pt[1]+' '+ship+' '+pt[2]+'\n') print 'max_delta_t',max_delta_t transits.append((start,t,samples,max_delta_t)) # Catch the last transit if options.gnuplot and options.separateShips: #gpShip = file(basename+'.'+ship+'.gp','w') gp.write('\n######################################################################\n') gp.write('# Ship '+ship+'\n') gp.write('\n') gp.write('set title "Transits for MMSI '+ship+'"\n') gp.write('set key on\n') gp.write('\n') gp.write('set terminal gif\n') gp.write('set output "'+basename+'.'+ship+'.gif"\n') gp.write('plot "'+basename+'.'+ship+'" with l title "'+ship+'"') if options.gpFiles: for filename in options.gpFiles: gp.write(' \\\n ,"'+filename+'" with l title "'+filename+'"') if options.gpPointFiles: for filename in options.gpPointFiles: gp.write(' \\\n ,"'+filename+'" with p title "'+filename+'"') gp.write('\n') gp.write('\n') gp.write('set terminal pdf\n') gp.write('set output "'+basename+'.'+ship+'.pdf"\n') gp.write('replot\n') if options.excel: totalTime = 0 # Track total time by ship in region totalSamples =0 # What was the total number of AIS position messages from this ship? transitCount=0 # What transit number for THIS ship for tr in transits: transitCount+=1 start = tr[0] end = tr[1] samples = tr[2] totalSamples += samples dt_start = datetime.utcfromtimestamp(start) dt_start_local = datetime.fromtimestamp(start) dt_end = datetime.utcfromtimestamp(end) dt_end_local = datetime.fromtimestamp(end) totalTime += end-start col = 0 print 'transit:',tr # Excel does not seem to be able to handle large numbers ws_transits.write(ws_transits_row,col,str(ship));col+=1 ws_transits.write(ws_transits_row,col,transitCount);col+=1 ws_transits.write(ws_transits_row,col,ship+'_'+str(start));col+=1 ws_transits.write(ws_transits_row,col,int(start));col+=1 ws_transits.write(ws_transits_row,col,dt_start,dateTimeStyle);col+=1 ws_transits.write(ws_transits_row,col,dt_start_local,dateTimeStyle);col+=1 ws_transits.write(ws_transits_row,col,int(end));col+=1 ws_transits.write(ws_transits_row,col,dt_end,dateTimeStyle);col+=1 ws_transits.write(ws_transits_row,col,dt_end_local,dateTimeStyle);col+=1 ws_transits.write(ws_transits_row,col,(int(end)-int(start))/3600.);col+=1 ws_transits.write(ws_transits_row,col,samples);col+=1 ws_transits_row += 1 col=0 ws_summary.write(ws_summary_row,col,str(ship)); col += 1 ws_summary.write(ws_summary_row,col,len(transits)); col += 1 ws_summary.write(ws_summary_row,col,totalTime/3600.); col += 1 ws_summary.write(ws_summary_row,col,totalSamples); col += 1 #ws_summary.write(ws_summary_row,col,); col += 1 ws_summary_row += 1 #print transits if None != summaryFile: summaryFile.write(ship+' '+str(shipTransits)+'\n') if transitsFile: transitsFile.write('\n\n') if shipTransitFile: shipTransitFile.close() totalTransits+=shipTransits if transitsFile: transitsFile.write('#total transits = '+str(totalTransits)+'\n') print 'Total transits =',totalTransits if options.excel: workbook.save(basename+'.xls')