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 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 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 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 GetFont(fontname, bold, italic, fontsize): global dic_Font if not dic_Font.has_key((fontname, bold, italic, fontsize)): font = pyExcelerator.Font() font.name = fontname font.bold = bold font.italic = italic font.height = fontsize * 20 dic_Font[(fontname, bold, italic, fontsize)] = font return dic_Font[(fontname, bold, italic, fontsize)]
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 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 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')
def update(self): filename = str(self.context.id) + '-export-register.xls' id_form = int(self.context.forms_id) fields = ModelsFormFields().get_Fields_ByIdForm(int(id_form)) types = ['img', 'file'] form = self.request.form campos_vin = [] values = ModelsForm().get_FormValues(id_form) L = [] for item in values: if self.checkItem(item, form): L.append(item) values = L if fields: fields = fields.find(flag_ativo=True) # Create Excel workbook wb = xl.Workbook() # Create Excel sheet and header mysheet = wb.add_sheet(self.context.Title() or self.context.id) #Nome do sheet #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 for field in fields: titulo = field.title.replace(';', ',') if isinstance(titulo, str): titulo = titulo.decode('utf-8') campos_vin.append(titulo.decode('utf-8')) for col, value in enumerate(campos_vin): mysheet.write(0, col, value, header_style) if values: for row_num, row_value in enumerate(values): row_num += 1 #start at row 1 for col, field in enumerate(fields): #Campos de histórico if field.name_field in [ 'observacao_responsavel', 'my_observacao' ]: data = row_value.find(fields=field.name_field) valor = '' if data and data.count(): data = data[0] log_data = data.get_logField() if log_data and log_data.count(): for log in log_data: valor += str(log.valor_new).replace( '\n', '').replace('\r', '').replace(';', ',') valor += ' \\ ' else: data = row_value.find( fields=field.name_field).one() if not field.type_fields in types and data: if field.type_fields == 'list': valor = '' for i in self.decodePickle(data.value): valor += i + ',' elif field.type_fields == 'date': campo_data = self.decodePickle(data.value) valor = campo_data.strftime('%d/%m/%Y') else: valor = str(data.value).replace( '\n', '').replace('\r', '').replace(';', ',') else: valor = '' if isinstance(valor, str): valor = valor.decode('utf-8') mysheet.write(row_num, col, valor) # Write out Excel file wb.save(filename) self.request.response.setHeader( 'Content-Type', 'application/x-excel; charset=utf-8') self.request.response.setHeader( 'Content-Disposition', 'attachment; filename=%s' % (filename)) self.request.response.write(file(filename, "r").read())
import pyExcelerator as excel workBookDocument = excel.Workbook() docSheet1 = workBookDocument.add_sheet("sheet1") myFont = excel.Font() myFont.bold = True myFont.underline = True myFont.italic = True #myFont.struck_out = True myFont.colour_index = 3 myFont.outline = True myFontStyle = excel.XFStyle() myFontStyle.font = myFont docSheet1.row(0).set_style(myFontStyle) docSheet1.write(0, 0, 'value') docSheet1.write(0, 1, 'name', myFontStyle) workBookDocument.save('pytest.xls')
def export_data(self, cr, uid, ids, context=None): excel_enabled = True row_lst = [] prod_ids = [] try: mydoc = Workbook() except: excel_enabled = False if excel_enabled: #Add a worksheet mysheet = mydoc.add_sheet("Pricelist") #write headers header_font = xl.Font() #make a font object header_font.bold = True #font needs to be style actually header_style = xl.XFStyle() header_style.font = header_font product_obj = pooler.get_pool(cr.dbname).get('product.product') product = product_obj.browse(cr, uid, ids) wiz = self.browse(cr, uid, ids)[0] if wiz.format == 'xls': if not excel_enabled: raise osv.except_osv( _("Export Pricelist error"), _("Impossible to export the pricelist as Excel file. Please install PyExcelerator to enable this function." )) filename = 'PriceList.xls' else: filename = 'PriceList.csv' keys = ['', ''] file_csv = StringIO.StringIO() keys = [ 'Product Code', 'EAN13', 'Supplier', 'Supplier Delay', 'Supplier Min. Qty', 'Supplier Code', 'Supplier Name', 'Quantity', 'Price' ] #Encode keys key_values = [tools.ustr(k).encode('utf-8') for k in keys] if excel_enabled: for col, value in enumerate(key_values): mysheet.write(0, col, value, header_style) writer = csv.writer(file_csv, delimiter=';', lineterminator='\r\n') writer.writerow([s.encode("utf-8") for s in keys]) prod_ids = product_obj.search(cr, uid, [('default_code', '!=', False), ('default_code', '!=', '')]) if not prod_ids: prod_ids = product_obj.search(cr, uid, [('default_code', 'like', ''), ('ean13', '!=', False)]) if prod_ids == []: raise osv.except_osv('Error', 'We cannot find any product !') for prod in product_obj.browse(cr, uid, prod_ids): row_lst = [] row = [] if prod.seller_ids: for seller in prod.seller_ids: for supplier in seller.pricelist_ids: row = [] row.append(prod.default_code.encode("utf-8") or ' ') row.append(prod.ean13 or ' ') row.append(seller.name.name.encode("utf-8") or '') row.append(seller.delay or ' ') row.append(seller.qty or ' ') if seller.product_name: row.append( seller.product_name.encode("utf-8") or ' ') else: row.append(' ') if seller.product_code: row.append( seller.product_code.encode("utf-8") or ' ') else: row.append(' ') row.append(supplier.min_quantity or '') row.append(supplier.price or '') row_lst.append(row) writer.writerow(row) else: row.append(prod.default_code or ' ') row.append(prod.ean13 or ' ') row.append('-') row.append('') row.append('') row.append('') row.append('') row.append('') row.append('') row_lst.append(row) writer.writerow([s.encode("utf-8") for s in row]) if excel_enabled: for row_num, row_values in enumerate(row_lst): row_num += 1 #start at row 1 row_values = [ tools.ustr(x).encode('utf-8') for x in row_values ] for col, value in enumerate(row_values): #normal row mysheet.write(row_num, col, value) if wiz.format == 'xls': file_xls = StringIO.StringIO() out = mydoc.save(file_xls) out = base64.encodestring(file_xls.getvalue()) else: out = base64.encodestring(file_csv.getvalue()) #print out result = self.write(cr, uid, ids, { 'data': out, 'name': filename }, context=context) return result
#!/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 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 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 _create_simple(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_sample = pooler.get_pool(cr.dbname).get('labo.sample') keys = ['', ''] sample_ids = obj_sample.browse(cr, uid, data['ids'], context) flag_c = '' mark = [] row_lst = [] done = [] prog_nums = [] row_num = 1 file_n = [] for col, value in enumerate(keys): mysheet.write(0, col, value, header_style) for sample in sample_ids: cr.execute(" SELECT distinct r.marker_dog, s.create_date from labo_sample s, dog_allele r where s.progenus_number is not NULL and "\ "r.sample_id=s.id and s.progenus_number = '%s' order by s.create_date asc"%(sample.progenus_number)) prog_mark = cr.fetchall() for i in prog_mark: if i[0] not in prog_nums: prog_nums.append(i[0]) keys = keys[0:] + prog_nums[0:] for col, value in enumerate(keys): mysheet.write(0, col, value, header_style) for sample in sample_ids: if sample.progenus_number in done: continue fields = [] prog = sample.progenus_number or None doss = (sample.sample_id and sample.sample_id.type_id.code or None) + '/' + (sample.sample_id and sample.sample_id.name or None) if sample.sample_id and sample.sample_id.name: file_n.append( ((('/' + sample.sample_id.type_id.code or '') or None) + (sample.sample_id.name or '')).replace('/', '')) if sample.progenus_number not in done: while len(fields) < len(prog_nums) + 2: fields.append('') for mark in prog_nums: pos_mark = prog_nums.index(mark) # cr.execute("select a.allele_dog1, a.allele_dog2, r.name, t.code from dog_allele a, labo_sample s , labo_analysis_request r,"\ # " labo_analysis_type t where t.id=r.type_id and a.sample_id=s.id and r.id=s.sample_id and s.progenus_number= '%s' "\ # " and a.marker_dog ='%s'"%(sample.progenus_number,mark )) cr.execute("select a.allele_dog1, a.allele_dog2 from dog_allele a, labo_sample s "\ " where a.sample_id=s.id and s.progenus_number= '%s' "\ " and a.marker_dog ='%s'"%(sample.progenus_number,mark )) res_all = cr.fetchone() if res_all: flag_c = 'ok' all_sample = ((res_all[0]).zfill(3) or '') + ' ' + ( (res_all[1]).zfill(3) or '') fields.insert(pos_mark + 2, all_sample) if flag_c == 'ok' and sample.progenus_number not in done: done.append(sample.progenus_number) fields.__setitem__(0, prog) fields.__setitem__(1, doss or None) row_lst.append(fields) for col, value in enumerate(fields): mysheet.write(row_num, col, value) row_num += 1 file = StringIO.StringIO() out = mydoc.save(file) out = base64.encodestring(file.getvalue()) name = dict([i, 0] for i in file_n if i).keys() name_f = '' for i in name: name_f += i + '_' file_n = name_f[:-1] if len(file_n) > 30: file_n = file_n[:30] + '.xls' else: file_n = file_n + '.xls' return {'data': out, 'file_name': file_n}
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 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 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 _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 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 _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 _create(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_sample = pooler.get_pool(cr.dbname).get('labo.sample') sample_ids = obj_sample.browse(cr, uid, data['ids'], context) obj_dog = pooler.get_pool(cr.dbname).get('labo.dog') keys = ['', '', ''] fields = [] fields1 = [] fields2 = [] lst_rows = [] mark = [] cr.execute( "SELECT distinct(r.marker_dog) from dog_allele r, labo_dog d , labo_sample s where r.dog_id1=d.id and s.id in (" + ",".join(map(str, data['ids'])) + ") and ( s.dog_mother=d.id or s.dog_father=d.id or s.dog_child=d.id)" ) res = cr.fetchall() for r in res: mark.append(r[0]) keys = keys[0:] + mark[0:] for col, value in enumerate(keys): mysheet.write(0, col, value, header_style) file_n = [] for sample in sample_ids: fields = [] fields1 = [] fields2 = [] fields3 = [] flag_m = '' flag_c = '' flag_p = '' if sample.dog_child: file_n.append((sample.lp_doss or '') + '_' + str(sample.dog_child.seq or '') or '') if sample.dog_mother: file_n.append((sample.lp_doss or '') + '_' + str(sample.dog_mother.seq or '') or '') if sample.dog_father: file_n.append((sample.lp_doss or '') + '_' + str(sample.dog_father.seq or '') or '') if sample.dog_child: fields = [] child = sample.dog_child pos = 0 sex_c = 'C' fields.insert(0, sex_c) fields.insert(1, sample.lp_doss or None) fields.insert(2, child.progenus_number or None) for z in mark: while len(fields) < len(mark) + 3: fields.append('') pos_mark = mark.index(z) for all in child and child.allele_ids: if all.marker_dog == z: flag_c = 'ok' all_child = ( (all.allele_dog1).zfill(3) or '') + ' ' + ( (all.allele_dog2).zfill(3) or '') fields.insert(pos_mark + 3, all_child) if flag_c == 'ok': lst_rows.append(fields) if sample.dog_mother: mother = sample.dog_mother sex = 'M' fields1.append(sex) fields1.append(sample.lp_doss or '') fields1.append(mother.progenus_number or '') for z in mark: while len(fields1) < len(mark) + 3: fields1.append('') pos_mark = mark.index(z) for all in mother and mother.allele_ids: if all.marker_dog == z: flag_m = 'ok' all_mother = ( (all.allele_dog1).zfill(3) or '') + ' ' + ( (all.allele_dog2).zfill(3) or '') fields1.insert(pos_mark + 3, all_mother) if flag_m == 'ok': lst_rows.append(fields1) if sample.dog_father: fields2 = [] father = sample.dog_father sex = 'P' fields2.append(sex) fields2.append(sample.lp_doss or '') fields2.append(father.progenus_number or '') for z in mark: while len(fields2) < len(mark) + 3: fields2.append('') # pos_mark=mark.index(z) for all in father and father.allele_ids: if all.marker_dog == z: pos_mark = mark.index(z) flag_p = 'ok' all_father = ( (all.allele_dog1).zfill(3) or '') + ' ' + ( (all.allele_dog2).zfill(3) or '') fields2.insert(pos_mark + 3, all_father) if flag_p == 'ok': lst_rows.append(fields2) lst_rows.append(fields3) for row_num, row_values in enumerate(lst_rows): row_num += 1 #start at row 1 row_values = [x.decode('utf8') for x in row_values if x] 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()) name = dict([i, 0] for i in file_n if i).keys() name_f = '' for i in name: name_f += i + '_' file_n = name_f[:-1] # + '.csv' if len(file_n) > 30: file_n = file_n[:30] + '.xls' else: file_n = file_n + '.xls' return {'data': out, 'file_name': file_n} return {}