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