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 {}
