import xlwt # 关于样式 style_head = xlwt.XFStyle() # 初始化样式 red_style_head = xlwt.XFStyle() # 初始化样式 green_style_head = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 初始化字体相关 font.name = "微软雅黑" font.bold = True font.colour_index = 0 # TODO 必须是数字索引 font_s = xlwt.Font() # 初始化字体相关 font_s.name = "微软雅黑" font_s.bold = True font_s.colour_index = 1 # TODO 必须是数字索引 red_bg = xlwt.Pattern() # 初始背景图案 red_bg.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x1 red_bg.pattern_fore_colour = 2 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray green_bg = xlwt.Pattern() # 初始背景图案 green_bg.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x1 green_bg.pattern_fore_colour = 3 # 设置字体 style_head.font = font red_style_head.font = font_s green_style_head.font = font_s # 设置背景
# xsfile = r'./MoliAccurancyData/TestCase5Domain.xls'
xsfile = r'E:\Moli\测试用例\python脚本\Moli精度脚本\Moli精度数据\US\USTestCase5Domain.xls' #北美测试集
# xsfile = r'E:\Moli\测试用例\python脚本\Moli精度脚本\Moli精度数据\IN\TestCase5Domain.xls' #印度测试集
# xsfile = r'E:\Moli\测试用例\python脚本\Moli精度脚本\测试集\线上测试全集\TestCase5Domain.xls' #线上数据全集
# xsfile = r'E:\Moli\测试用例\python脚本\Moli精度脚本\测试集\线上测试全集\TestCase5Domain全集(未筛选上线subintent）.xls' #线上数据全集
workbook = xlrd.open_workbook(xsfile,
formatting_info=True,
encoding_override='utf-8')
worksheet = workbook.sheet_by_name('TestCase')
cbook = copy(workbook)
csheet = cbook.get_sheet('TestCase')
# 设置字体颜色为红色
style_red = xlwt.XFStyle() #初始化excel全局样式
font_red = xlwt.Font() #初始化font样式
font_red.colour_index = 2 #为font进行赋值
style_red.font = font_red #将赋值好的font 传递给excel全局样式
# 设置字体颜色为绿色
style_green = xlwt.XFStyle()
font_green = xlwt.Font()
font_green.colour_index = 50
style_green.font = font_green
# 引入时间戳作为userid
ticks = time.time()
URL = r'http://10.110.147.198:9090/moliAgent/caap/centralcontrol'
row = 2
def render_data(self, request):
import xlwt
from django.http import HttpResponse, HttpResponseRedirect
from mysite.urls import tmpDir, tmp_url
import os
rn = request.REQUEST.get("reportname", '')
title = rn and rn or self.title
sheet_name = u"%s_%s" % (
'Sheet', datetime.datetime.now().strftime("%Y%m%d%H%M%S"))
file_path = tmpDir() + "/report_file"
if not os.path.exists(file_path):
os.makedirs(file_path)
if self.head == "":
#print self.data[0]
heads = [k for k in self.data[0].keys()]
else:
heads = self.head
lines = [xls_format(a) for a in self.cells]
lines.insert(0, xls_format(self.head))
filecode = request.REQUEST.get('filecode', 'gb18030') #编码
wb = xlwt.Workbook(encoding=u"%s" % filecode)
ws = wb.add_sheet(sheet_name)
fnt = xlwt.Font()
fnt.name = 'Arial'
#fnt.colour_index = 4
#fnt.bold = True
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
align = xlwt.Alignment()
align.horz = xlwt.Alignment.HORZ_CENTER
align.vert = xlwt.Alignment.VERT_CENTER
style = xlwt.XFStyle()
style.font = fnt
style.borders = borders
style.alignment = align
row_index = 0
for row in lines:
col_index = 0
for col in row:
ws.write(row_index, col_index, col, style)
ws.col(col_index).width = 0x0d00 + 2000
col_index += 1
row_index += 1
filename = "%(d1)s_%(d2)s.xls" % {
"d1": u"%s" % title,
"d2": datetime.datetime.now().strftime("%Y%m%d%H%M%S")
}
# response = HttpResponse(mimetype=u"application/vnd.ms-excel; charset=utf-8")
# response["Content-Disposition"] = u"attachment;"
# filename=%(d1)s_%(d2)s.xls"%{
# "d1":title,
# "d2":datetime.datetime.now().strftime("%Y%m%d%H%M%S")
# }
# wb.save(response)
# return response
wb.save(u"%s/%s" % (file_path, filename))
f = "/" + tmp_url() + "report_file/" + filename
return HttpResponseRedirect(f.encode("utf-8"))
def write_medium_rebate_invoice_excel(orders):
xls = xlwt.Workbook(encoding='utf-8')
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = 'Times New Roman' # 选择字体
font.bold = True # 是否加粗
style.font = font # 为样式设置字体
# font.height = 500 #设置高度
al = xlwt.Alignment()
al.horz = xlwt.Alignment.HORZ_CENTER # 设置水平居中
al.vert = xlwt.Alignment.VERT_CENTER # 设置垂直居中
al.wrap = xlwt.Alignment.WRAP_AT_RIGHT # 设置文字可以换行
style.alignment = al
sheet = xls.add_sheet("Sheet")
keys = [
u'代理/直客', u'客户名称', u'Campaign', u'合同号', u'合同总金额', u'媒体总金额', u'媒体返点总金额',
u'已开发票总金额', u'未开发票总金额', u'申请通过个数', u'投放媒体', u'发票时间', u'公司名称', u'税号',
u'公司地址', u'联系电话', u'银行账号', u'开户行', u'发票内容', u'发票金额', u'发票类型', u'发票号',
u'回款时间'
]
h = 0
for k in range(len(keys)):
sheet.write(h, k, keys[k])
h += 1
for order in orders:
invoice_passes = [
x for x in MediumRebateInvoice.query.filter_by(client_order=order)
if x.invoice_status == INVOICE_STATUS_PASS
]
sheet.write_merge(h, h + len(invoice_passes) - 1, 0, 0,
order.agent.name, style)
sheet.write_merge(h, h + len(invoice_passes) - 1, 1, 1,
order.client.name, style)
sheet.write_merge(h, h + len(invoice_passes) - 1, 2, 2, order.campaign,
style)
sheet.write_merge(h, h + len(invoice_passes) - 1, 3, 3,
str(order.contract), style)
sheet.write_merge(h, h + len(invoice_passes) - 1, 4, 4,
str(order.money), style)
sheet.write_merge(h, h + len(invoice_passes) - 1, 5, 5,
str(order.mediums_money2), style)
sheet.write_merge(h, h + len(invoice_passes) - 1, 6, 6,
str(order.mediums_rebate_money), style)
sheet.write_merge(h, h + len(invoice_passes) - 1, 7, 7,
str(order.mediums_rebate_invoice_pass_sum), style)
sheet.write_merge(
h, h + len(invoice_passes) - 1, 8, 8,
str(order.mediums_rebate_money -
order.mediums_rebate_invoice_pass_sum), style)
sheet.write_merge(
h, h + len(invoice_passes) - 1, 9, 9,
str(len(order.get_medium_rebate_invoice_by_status(0))), style)
for invoice in invoice_passes:
sheet.write(h, 10, invoice.medium.name)
sheet.write(h, 11, invoice.create_time.strftime('%Y-%m-%d'))
sheet.write(h, 12, invoice.company)
sheet.write(h, 13, invoice.tax_id)
sheet.write(h, 14, invoice.address)
sheet.write(h, 15, invoice.phone)
sheet.write(h, 16, invoice.bank_id)
sheet.write(h, 17, invoice.bank)
sheet.write(h, 18, invoice.detail)
sheet.write(h, 19, invoice.money)
sheet.write(h, 20, INVOICE_TYPE_CN[invoice.invoice_type])
sheet.write(h, 21, invoice.invoice_num)
sheet.write(h, 22, str(invoice.back_time_cn))
h += 1
return xls
def get_result(self):
data = self.read()[0]
start_date = data.get('date_start', False)
end_date = data.get('date_end', False)
if start_date and end_date and end_date < start_date:
raise Warning(_("End date should be greater than start date!"))
res_user = self.env["res.users"].browse(self._uid)
# Create Inventory Export report in Excel file.
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
font = xlwt.Font()
font.bold = True
header = xlwt.easyxf('font: bold 1, height 280')
# start_date = datetime.strptime(str(context.get("date_from")), DEFAULT_SERVER_DATE_FORMAT)
# start_date_formate = start_date.strftime('%d/%m/%Y')
# end_date = datetime.strptime(str(context.get("date_to")), DEFAULT_SERVER_DATE_FORMAT)
# end_date_formate = end_date.strftime('%d/%m/%Y')
# start_date_to_end_date = tools.ustr(start_date_formate) + ' To ' + tools.ustr(end_date_formate)
style = xlwt.easyxf('align: wrap yes')
worksheet.row(0).height = 500
worksheet.row(1).height = 500
for x in range(0, 41):
worksheet.col(x).width = 6000
borders = xlwt.Borders()
borders.top = xlwt.Borders.MEDIUM
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
border_style1 = xlwt.easyxf('font: bold 1')
border_style1.borders = borders
style = xlwt.easyxf('align: wrap yes', style)
ids_location = []
ids_categ = []
ids_product = []
where_end_date_awal = " sm.date is null "
where_start_date = " 1=1 "
if start_date:
where_start_date = " sm.date + interval '7 hour' >= '%s 00:00:00' " % start_date
where_end_date_awal = " sm.date + interval '7 hour' < '%s 00:00:00' " % start_date
where_end_date = " 1=1 "
if end_date:
where_end_date = " sm.date + interval '7 hour' <= '%s 23:59:59'" % end_date
where_end_date_opn = " 1=1 "
if end_date:
where_end_date_opn = " si.date::text LIKE '%s%s'" % (end_date, "%")
where_location = " 1=1 "
if ids_location:
where_location = """(sm.location_id in %s
or sm.location_dest_id in %s)""" % (str(
tuple(ids_location)).replace(
',)', ')'), str(tuple(ids_location)).replace(',)', ')'))
where_location_opn = " sil.location_id = 12 "
where_categ = " 1=1 "
if ids_categ:
where_categ = "pt.categ_id in %s" % str(tuple(ids_categ)).replace(
',)', ')')
where_product = " 1=1 "
if ids_product:
where_product = "pp.id in %s" % str(tuple(ids_product)).replace(
',)', ')')
query = """
select pp.id as prod_id,
pt.default_code as prod_code,
pt.name as prod_name,
pu.name as uom,
sum(coalesce(awal.qty,0)) as saldo_awal,
sum(coalesce(saldo_masuk.qty,0)) as masuk,
sum(coalesce(saldo_keluar.qty,0)) as keluar,
sum(coalesce(prod_adj.qty,0)) as adjusment,
sum(coalesce(opn.qty,0)) as opname
from product_product pp
left join
(
select sm.product_id,
sum(case when sl.name = 'Pre-Production' and dl.name != 'Pre-Production'
then sm.product_qty*-1
when sl.name != 'Pre-Production' and dl.name = 'Pre-Production'
then sm.product_qty else 0 end) as qty
from stock_move sm
left join stock_location sl on sl.id = sm.location_id
left join stock_location dl on dl.id = sm.location_dest_id
left join product_product pp on pp.id=sm.product_id
left join product_template pt on pt.id=pp.product_tmpl_id
left join uom_uom pu on pu.id= sm.product_uom
where """ + where_end_date_awal + """ and """ + where_location + """
and sm.state = 'done'
group by sm.product_id
) awal on awal.product_id=pp.id
left join
(
select sm.product_id,
sum(sm.product_qty) as qty
from stock_move sm
left join stock_location sl on sl.id = sm.location_id
left join stock_location dl on dl.id = sm.location_dest_id
left join product_product pp on pp.id=sm.product_id
left join product_template pt on pt.id=pp.product_tmpl_id
left join uom_uom pu on pu.id= sm.product_uom
where sl.name != 'Pre-Production' and dl.name = 'Pre-Production' and sm.state = 'done' and sm.inventory_id is null
and """ + where_start_date + """ and """ + where_end_date + """ and """ + where_location + """
group by sm.product_id
) saldo_masuk on saldo_masuk.product_id=pp.id
left join
(
select sm.product_id, sum(sm.product_qty) as qty
from stock_move sm
left join stock_location sl on sl.id = sm.location_id
left join stock_location dl on dl.id = sm.location_dest_id
left join product_product pp on pp.id=sm.product_id
left join product_template pt on pt.id=pp.product_tmpl_id
left join uom_uom pu on pu.id= sm.product_uom
where sl.name = 'Pre-Production' and dl.name != 'Pre-Production' and sm.state = 'done' and sm.inventory_id is null
and """ + where_start_date + """ and """ + where_end_date + """ and """ + where_location + """
group by sm.product_id
) saldo_keluar on saldo_keluar.product_id=pp.id
left join
(
select sm.product_id,
sum(case when sl.name = 'Pre-Production' and dl.name != 'Pre-Production' and inventory_id is not null
then sm.product_qty*-1
when sl.name != 'Pre-Production' and dl.name = 'Pre-Production' and inventory_id is not null
then sm.product_qty else 0 end ) as qty
from stock_move sm
left join stock_location sl on sl.id = sm.location_id
left join stock_location dl on dl.id = sm.location_dest_id
left join product_product pp on pp.id=sm.product_id
left join product_template pt on pt.id=pp.product_tmpl_id
left join uom_uom pu on pu.id= sm.product_uom
where """ + where_start_date + """ and """ + where_end_date + """ and """ + where_location + """ and sm.state = 'done'
group by sm.product_id
) prod_adj on prod_adj.product_id=pp.id
left join
(
select sil.product_id,
sum(sil.product_qty) as qty
from stock_inventory_line sil
INNER JOIN stock_inventory si on sil.inventory_id = si.id
INNER JOIN stock_location sl on sl.id = sil.location_id
left join product_product pp on pp.id=sil.product_id
left join product_template pt on pt.id=pp.product_tmpl_id
left join uom_uom pu on pu.id= sil.product_uom_id
where """ + where_end_date_opn + """ and """ + where_location_opn + """ and si.state = 'done'
group by sil.product_id
) opn on opn.product_id=pp.id
left join product_template pt on pt.id=pp.product_tmpl_id
left join uom_uom pu on pt.uom_id=pu.id
left join product_category pc on pt.categ_id=pc.id
where """ + where_categ + """ and """ + where_product + """ and pc.report_type = 'bahan_baku'
and (coalesce(awal.qty,0) != 0
or coalesce(saldo_masuk.qty,0) != 0
or coalesce(saldo_keluar.qty,0) != 0
or coalesce(opn.qty,0) != 0)
group by pt.default_code, pt.name, pp.id , pu.name
order by pt.name asc
"""
list_data = []
company = self.env.user.company_id.name
start_date_format = start_date.strftime('%d/%m/%Y')
end_date_format = end_date.strftime('%d/%m/%Y')
self._cr.execute(query)
vals = self._cr.fetchall()
no = 1
for val in vals:
saldo_akhir = float(val[4]) + float(val[5]) - \
float(val[6]) + float(val[7])
list_data.append({
'kode_barang': val[1],
'nama_barang': val[2],
'satuan': val[3],
'jumlah': saldo_akhir,
'keterangan': ""
})
no += 1
hasil = list_data
return hasil
worksheet.col(statu).width = 256 * 12 worksheet.col(backup).width = 256 * 22 #创建一个样式---------------------------- stylefirstRow = xlwt.XFStyle() stylefirstRow.borders = borders pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['gold'] #设置单元格背景色为黄色 stylefirstRow.pattern = pattern al = xlwt.Alignment() al.horz = 0x02 # 设置水平居中 al.vert = 0x01 # 设置垂直居中 al.wrap = 1 #自动换行 stylefirstRow.alignment = al font = xlwt.Font() # 为样式创建字体 #font.name = 'Times New Roman' font.name = u'微软雅黑' #font.bold = True # 黑体 #font.underline = True # 下划线 #font.italic = True # 斜体字 font.height = 0x00C8 * 1.6 # C8 in Hex (in decimal) = 10 points in height. #font.colour_index=48#蓝色 #font.colour_index=2#红色 #font.colour_index=59#灰色 #font.colour_index=44#浅蓝色 stylefirstRow.font = font # 设定样式 #第二行样式---------------------------- stylesecondRow = xlwt.XFStyle() stylesecondRow.borders = borders font2 = xlwt.Font() # 为样式创建字体
result_file_name = result_file_path + '\\result.xls'
if not os.path.exists(testfilename): #check if trace file present
print '\n\n\nINPUT LOG FILE NOT FOUND \n\n'
sys.exit()
if not os.path.exists(result_file_path):
os.mkdir(result_file_path)
file = open(result_file_name, 'w') #open the test file
file.close()
file = open(testfilename, 'r') #open the test file
contents_of_trace_file = file.read() #store its contents
file.close() #close the file
style = xlwt.easyxf("", "###,###") #1000 seperator enable
font = xlwt.Font() #Create Style for headings
font.bold = True
style_for_heading = xlwt.XFStyle()
style_for_heading.font = font
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 7
style_for_heading.pattern = pattern
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style_for_heading.borders = borders
starting_indicator = 'start_xti_profiler_' #define starting_indicator
def check_report(self):
cr, uid, context = self.env.args
context = dict(context)
fl = StringIO()
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
font = xlwt.Font()
font.bold = True
header = xlwt.easyxf('font: bold 1, height 180;')
header1 = xlwt.easyxf(
'font: bold on, height 200;borders : top thin, bottom thin, left thin, right thin;align: wrap off , vert centre, horiz center;'
)
header2 = xlwt.easyxf(
'font: bold on, height 180;borders : top thin, bottom thin, left thin, right thin;align: wrap on , vert centre, horiz center;'
)
align_left = xlwt.easyxf(
'font: height 200, bold on, name Arial, colour_index periwinkle;borders : top thin, bottom thin, left thin, right thin; align: wrap on,vert centre, horiz left;'
)
align_border = xlwt.easyxf(
'font: height 180, name Arial, colour_index black;borders : top thin, bottom thin, left thin, right thin; align: wrap on,vert centre, horiz left;'
)
wiz_rec = self.browse(self._ids[0])
bed_obj = self.env['beds.beds']
visa_obj = self.env['visa.quota']
room_obj = self.env['room.room']
list_new = []
country_dict = {}
for country in wiz_rec.nationality_ids:
acco_dict = {}
country_id = country.id
visa_ids = visa_obj.search([('nationality_id', '=', country_id)])
for visa in visa_ids:
acco_name = visa.accommodation_id.name
acc_dict = {}
company_dict = {}
visa_total = visa.number_of_quota
visa_avail = visa.quota_available
visa_occupied = visa_total - visa_avail
country_comp_total = 0
room_ids = room_obj.search([('accommodation_id', '=',
visa.accommodation_id.id)])
for room in room_ids:
room_id = room
occupied_beds = bed_obj.search([
('employee_id.emp_country_id', '=',
visa.nationality_id.id), ('employee_id', '!=', False),
('room_id', '=', room_id.id)
])
occu_beds = []
counter = 0
for bed in occupied_beds:
bed_dict = {}
counter += 1
bed_dict['sr_no'] = counter or ''
bed_dict[
'id_no'] = bed.employee_id.identification_id or ''
bed_dict['bed_no'] = bed.name or ''
bed_dict['name'] = bed.employee_id.name or ''
bed_dict['wp_number'] = bed.employee_id.wp_number or ''
bed_dict['com'] = bed.employee_id.company_id.name or ''
bed_dict['dialect'] = bed.employee_id.dialect or ''
bed_dict['country'] = False
bed_dict['location'] = False
bed_dict['room'] = False
bed_dict['bold'] = False
occu_beds.append(bed_dict)
if occu_beds:
company_dict[room.name] = occu_beds
if acco_name in acco_dict.keys():
acco_dict[acco_name]['room_list'].append(company_dict)
else:
acco_dict[acco_name] = {'room_list': [company_dict]}
country_dict[country.name] = acco_dict
final_dict = {}
final_list = []
for con_key, con_val in country_dict.iteritems():
country = False
con_dict = {
'sr_no': con_key,
'bed_no': '',
'id_no': '',
'name': '',
'wp_number': '',
'com': '',
'dialect': '',
'country': True,
'location': False,
'room': False,
'bold': True
}
for loc_key, loc_val in con_val.iteritems():
location = False
room_bold_dict = {
'sr_no': loc_key,
'bed_no': '',
'id_no': '',
'name': '',
'wp_number': '',
'com': '',
'dialect': '',
'country': False,
'location': True,
'room': False,
'bold': True
}
for room in loc_val.get('room_list', [{}]):
for room_key, room_value in room.iteritems():
room_dict = {
'sr_no': room_key,
'bed_no': '',
'id_no': '',
'name': '',
'wp_number': '',
'com': '',
'dialect': '',
'country': False,
'location': False,
'room': True,
'bold': True
}
if not country:
final_list.append(con_dict)
country = True
if not location:
final_list.append(room_bold_dict)
location = True
final_list.append(room_dict)
final_list += room_value
row = 1
for lst in final_list:
if lst['country'] == True:
worksheet.write_merge(
row, row + 1, 0, 3,
'LIST OF "' + lst['sr_no'] + '" WORKERS ACCOMMODATION',
align_left)
row += 2
if lst['location'] == True:
worksheet.write(row, 0, 'LOCATION :', header1)
worksheet.write(row, 1, lst['sr_no'], align_left)
row += 1
if lst['room'] == True:
worksheet.write(row, 0, 'ROOM :', header1)
worksheet.write(row, 1, lst['sr_no'], align_left)
row += 2
worksheet.write_merge(row, row + 1, 0, 0, 'NO', header2)
worksheet.write_merge(row, row + 1, 1, 1, 'BED NO', header2)
worksheet.write_merge(row, row + 1, 2, 2, 'CODE NO', header2)
worksheet.write_merge(row, row + 1, 3, 3, 'NAME', header2)
worksheet.write_merge(row, row + 1, 4, 4, 'W/P NUMBER',
header2)
worksheet.write_merge(row, row + 1, 5, 5, 'COM', header2)
worksheet.write_merge(row, row + 1, 6, 6, 'DIALECT', header2)
worksheet.write_merge(row, row + 1, 7, 7, 'SITE', header2)
worksheet.write_merge(row, row + 1, 8, 8,
'Date of Application', header2)
worksheet.write_merge(row, row + 1, 9, 9, 'REMARKS', header2)
row += 2
if lst['bold'] == False:
worksheet.write(row, 0, lst['sr_no'], align_border)
worksheet.write(row, 1, lst['bed_no'], align_border)
worksheet.write(row, 2, lst['id_no'], align_border)
worksheet.write(row, 3, lst['name'], align_border)
worksheet.write(row, 4, lst['wp_number'], align_border)
worksheet.write(row, 5, lst['com'], align_border)
worksheet.write(row, 6, lst['dialect'], align_border)
worksheet.write(row, 7, 'static', align_border)
worksheet.write(row, 8, 'static', align_border)
worksheet.write(row, 9, 'static', align_border)
row += 2
workbook.save(fl)
fl.seek(0)
buf = base64.encodestring(fl.read())
ctx = dict(context)
ctx.update({'file': buf})
return {
'type': 'ir.actions.act_window',
'view_type': 'form',
'view_mode': 'form',
'res_model': 'acco.nationality.report',
'target': 'new',
'context': ctx,
}
def __init__(self):
"""
init
"""
# init excel
self.workbook = xlwt.Workbook(encoding = 'utf-8')
self.worksheet_p4 = self.workbook.add_sheet("p4(anakin2 vs tensorRT)")
# set excel style
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
# word is black
self.style = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style.font = font
self.style.alignment = alignment
# word is red
self.style_red = xlwt.XFStyle()
font_red = xlwt.Font()
font_red.name = 'PT Mono'
font_red.height = 240
font_red.colour_index = 2
self.style_red.font = font_red
self.style_red.alignment = alignment
# background is color
col = self.worksheet_p4.col(0)
col.width = 256 * 25
self.style_background0 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background0.font = font
self.style_background0.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 22
self.style_background0.pattern = pattern
self.worksheet_p4.write(0, 0, "Net_Name", self.style_background0)
col = self.worksheet_p4.col(1)
col.width = 256 * 15
self.style_background1 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background1.font = font
self.style_background1.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 26
self.style_background1.pattern = pattern
self.worksheet_p4.write(0, 1, "Batch_Size", self.style_background1)
col = self.worksheet_p4.col(2)
col.width = 256 * 15
self.style_background2 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background2.font = font
self.style_background2.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 44
self.style_background2.pattern = pattern
self.worksheet_p4.write(0, 2, "Library", self.style_background2)
col = self.worksheet_p4.col(3)
col.width = 256 * 20
self.style_background3 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background3.font = font
self.style_background3.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 42
self.style_background3.pattern = pattern
self.worksheet_p4.write(0, 3, "RT latency (ms)", self.style_background3)
col = self.worksheet_p4.col(4)
col.width = 256 * 20
self.style_background4 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background4.font = font
self.style_background4.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 47
self.style_background4.pattern = pattern
self.worksheet_p4.write(0, 4, "RT Memory (MB)", self.style_background4)
col = self.worksheet_p4.col(5)
col.width = 256 * 15
self.style_background5 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background5.font = font
self.style_background5.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 44
self.style_background5.pattern = pattern
self.worksheet_p4.write(0, 5, "Library", self.style_background5)
col = self.worksheet_p4.col(6)
col.width = 256 * 20
self.style_background6 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background6.font = font
self.style_background6.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 42
self.style_background6.pattern = pattern
self.worksheet_p4.write(0, 6, "anakin2 Latency (ms)", self.style_background6)
col = self.worksheet_p4.col(7)
col.width = 256 * 20
self.style_background7 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background7.font = font
self.style_background7.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 47
self.style_background7.pattern = pattern
self.worksheet_p4.write(0, 7, "anakin2 Memory (MB)", self.style_background7)
col = self.worksheet_p4.col(8)
col.width = 256 * 20
self.style_background8 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background8.font = font
self.style_background8.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 53
self.style_background8.pattern = pattern
self.worksheet_p4.write(0, 8, "anakin/tensorRT\nlatency", self.style_background8)
col = self.worksheet_p4.col(9)
col.width = 256 * 20
self.style_background9 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background9.font = font
self.style_background9.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 53
self.style_background9.pattern = pattern
self.worksheet_p4.write(0, 9, "anakin/tensorrt\nmemory", self.style_background9)
col = self.worksheet_p4.col(10)
col.width = 256 * 20
self.style_background10 = xlwt.XFStyle() # Create Style
font = xlwt.Font()
font.name = 'PT Mono'
font.height = 240
font.colour_index = 0
self.style_background10.font = font
self.style_background10.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 53
self.style_background10.pattern = pattern
self.worksheet_p4.write(0, 10, "accuracy rate\n(%)", self.style_background10)
def print_xls(self):
report_obj = self.env[
'report.flexiretail_com_advance.report_financial']
data = {}
data['form'] = \
self.read(
['account_report_id', 'date_from_cmp', 'date_to_cmp', 'journal_ids', 'filter_cmp', 'target_move'])[0]
for field in ['account_report_id']:
if isinstance(data['form'][field], tuple):
data['form'][field] = data['form'][field][0]
comparison_context = self._build_comparison_context(data)
# res['data']['form']['comparison_context'] = comparison_context
if comparison_context.get('date_from'):
comparison_context['date_from'] = str(self.date_from_cmp)
if comparison_context.get('date_to'):
comparison_context['date_to'] = str(self.date_to_cmp)
datas = {}
datas['account_report_id'] = [
self.account_report_id.id, 'Profit and Loss'
]
datas['company_id'] = [self.company_id.id, self.company_id.name]
# datas['comparison_context'] = {
# 'date_from':str(self.date_from),
# 'date_to':str(self.date_to),
# 'journal_ids':False,
# 'state':self.target_move,
# 'strict_range': comparison_context.get('strict_range') if comparison_context.get('strict_range') else False,
# }
datas['comparison_context'] = comparison_context
datas['date_from'] = str(self.date_from) if self.date_from else False
datas['date_from_cmp'] = str(
self.date_from_cmp) if self.date_from_cmp else False
datas['date_to'] = str(self.date_to) if self.date_to else False
datas['date_to_cmp'] = str(
self.date_to_cmp) if self.date_to_cmp else False
datas['debit_credit'] = str(
self.debit_credit) if self.debit_credit else False
datas['enable_filter'] = self.enable_filter
datas['filter_cmp'] = self.filter_cmp
datas['id'] = self.id
datas['journal_ids'] = False
datas['label_filter'] = self.label_filter
datas['target_move'] = self.target_move
datas['used_context'] = {
'company_id':
1,
'date_from':
str(self.date_from) if self.date_from else False,
'date_to':
str(self.date_to) if self.date_to else False,
'journal_ids':
False,
'lang':
self.env.user.lang,
'state':
self.target_move,
'strict_range':
comparison_context.get('strict_range')
if comparison_context.get('strict_range') else False,
}
lines = report_obj.get_account_lines(datas)
styleP = xlwt.XFStyle()
stylePC = xlwt.XFStyle()
styleBorder = xlwt.XFStyle()
fontbold = xlwt.XFStyle()
normal_style = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment_lft = xlwt.Alignment()
alignment_lft.horz = xlwt.Alignment.HORZ_LEFT
alignment_rgt = xlwt.Alignment()
alignment_rgt.horz = xlwt.Alignment.HORZ_RIGHT
font = xlwt.Font()
fontP = xlwt.Font()
fontN = xlwt.Font()
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
font.bold = False
fontP.bold = True
styleP.font = font
# stylePC.font = fontP
stylePC.alignment = alignment_lft
fontbold.alignment = alignment_rgt
styleBorder.font = fontP
stylePC.font = fontP
# fontbold.font = fontP
styleBorder.alignment = alignment
styleBorder.borders = borders
workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("Aged Payable")
worksheet.col(0).width = 5600
worksheet.col(1).width = 5600
worksheet.col(2).width = 5600
worksheet.col(3).width = 5600
worksheet.write_merge(0,
2,
0,
3,
self.company_id.name + '\n' +
self.company_id.email + '\n' +
self.company_id.phone,
style=styleBorder)
worksheet.write_merge(
3,
3,
0,
3,
'Balance Sheet'
if self._context.get('from_balance_sheet') else 'Profit and Loss',
style=styleBorder)
worksheet.write_merge(
4, 4, 0, 3, 'Target Moves : ' + 'All Entries' if self.target_move
== 'all' else 'Target Moves : All Posted Entries')
if self.date_from and self.date_to:
worksheet.write_merge(
5, 5, 0, 3, 'Date From : ' + str(self.date_from) + ' ' +
'Date To : ' + str(self.date_to))
i = 8
if self.debit_credit:
fontbold.font = fontP
worksheet.write_merge(7, 7, 0, 0, 'Name', stylePC)
worksheet.write_merge(7, 7, 1, 1, 'Debit', stylePC)
worksheet.write_merge(7, 7, 2, 2, 'Credit', stylePC)
worksheet.write_merge(7, 7, 3, 3, 'Balance', fontbold)
for each in lines:
fontbold.font = fontN
normal_style.font = fontN
if not each.get('level') > 3:
fontbold.font = fontP
normal_style.font = fontP
if each.get('level') != 0:
worksheet.write_merge(i, i, 0, 0, each.get('name'),
normal_style)
worksheet.write_merge(
i, i, 1, 1,
formatLang(
self.env,
float(each.get('debit')),
currency_obj=self.env.user.company_id.currency_id),
fontbold)
worksheet.write_merge(
i, i, 2, 2,
formatLang(
self.env,
float(each.get('credit')),
currency_obj=self.env.user.company_id.currency_id),
fontbold)
worksheet.write_merge(
i, i, 3, 3,
formatLang(
self.env,
float(each.get('balance')),
currency_obj=self.env.user.company_id.currency_id),
fontbold)
i = i + 1
if not self.enable_filter and not self.debit_credit:
fontbold.font = fontP
worksheet.write_merge(7, 7, 0, 0, 'Name', stylePC)
worksheet.write_merge(7, 7, 1, 1, 'Balance', fontbold)
for each in lines:
fontbold.font = fontN
normal_style.font = fontN
if not each.get('level') > 3:
fontbold.font = fontP
normal_style.font = fontP
if each.get('level') != 0:
worksheet.write_merge(i, i, 0, 0, each.get('name'),
normal_style)
worksheet.write_merge(
i, i, 1, 1,
formatLang(
self.env,
float(each.get('balance')),
currency_obj=self.env.user.company_id.currency_id),
fontbold)
i = i + 1
if self.enable_filter and not self.debit_credit:
fontbold.font = fontP
worksheet.write_merge(7, 7, 0, 0, 'Name', stylePC)
worksheet.write_merge(7, 7, 1, 1, 'Debit', fontbold)
worksheet.write_merge(
7, 7, 2, 2,
str(self.label_filter) if self.label_filter else '', fontbold)
for each in lines:
if each.get('level') != 0:
fontbold.font = fontN
normal_style.font = fontN
if not each.get('level') > 3:
fontbold.font = fontP
normal_style.font = fontP
worksheet.write_merge(i, i, 0, 0, each.get('name'),
normal_style)
worksheet.write_merge(
i, i, 1, 1,
formatLang(
self.env,
float(each.get('balance')),
currency_obj=self.env.user.company_id.currency_id),
fontbold)
worksheet.write_merge(
i, i, 2, 2,
formatLang(
self.env,
float(each.get('balance_cmp')),
currency_obj=self.env.user.company_id.currency_id),
fontbold)
i = i + 1
file_data = io.BytesIO()
workbook.save(file_data)
self.write({
'state':
'get',
'data':
base64.encodestring(file_data.getvalue()),
'name':
'balance_sheet.xls'
if self._context.get('from_balance_sheet') else 'profit_loss.xls'
})
return {
'name': 'Profit & Loss',
'type': 'ir.actions.act_window',
'res_model': 'accounting.report.profit.loss',
'view_mode': 'form',
'view_type': 'form',
'res_id': self.id,
'target': 'new'
}
def __init__(self,file_name ='',sheet='AutoTestResult',debug_flag='1'):
self.wbk=xlwt.Workbook(encoding = 'gb18030')
self.sheet = self.wbk.add_sheet(sheet,cell_overwrite_ok=True)
self.filename = self.save_result(file_name)
self.tmp_modulename = ''
self.start_module_row = 13
self.dstRst_total_mod ={'DoneNum':'0','OKNum':'0','NGNum':'0'}
self.mod_row_offset = 0
try:
font1 = xlwt.Font()
font1.name = 'Times New Roman'
font1.bold = True
borders1 = xlwt.Borders()
borders1.left = xlwt.Borders.DASHED
borders1.right = xlwt.Borders.DASHED
borders1.bottom = xlwt.Borders.DASHED
borders1.top = xlwt.Borders.DASHED
self.style1 = xlwt.XFStyle()
self.style1.font = font1
self.style1.borders = borders1
self.style2 = xlwt.easyxf('pattern: pattern solid, fore_colour sea_green;')
font2 = xlwt.Font()
font2.name = 'SimSun'
font2.height = 0x00E6
font2.bold = True
self.style2.font = font2
self.style2.borders = borders1
self.style3 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;')
font3 = xlwt.Font()
font3.bold = True
font3.name = 'SimSun'
font3.height = 0x00C8
self.style3.font = font3
self.style3.borders = borders1
self.style4 = xlwt.easyxf('pattern: pattern solid, fore_colour ice_blue;')
font4 = xlwt.Font()
font4.name = 'SimSun'
font4.height = 0x00C8
font4.bold = True
self.style4.font = font4
self.style4.borders = borders1
self.style5 = xlwt.easyxf('pattern: pattern solid, fore_colour light_blue;')
self.style5.font = font4
self.style5.borders = borders1
self.style6 = xlwt.XFStyle()
font5 = xlwt.Font()
font5.name = 'Verdana'
font5.bold = True
font5.underline = xlwt.Font.UNDERLINE_DOUBLE
font5.colour_index = 4
self.style6.font =font5
self.style6.borders = borders1
self.style7 = xlwt.XFStyle()
font6 = xlwt.Font()
font6.name = 'Verdana'
font6.bold = True
font6.colour_index = 6
self.style7.font =font6
self.style7.borders = borders1
self.style8 = xlwt.XFStyle()
font7 = xlwt.Font()
font7.name = 'Verdana'
font7.bold = True
font7.colour_index = 8
self.style8.font =font7
self.style8.borders = borders1
self.style9 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;')
borders2 = xlwt.Borders()
borders2.left = xlwt.Borders.DASHED
borders2.right = xlwt.Borders.DASHED
borders2.bottom = xlwt.Borders.DASHED
borders2.top = xlwt.Borders.DASHED
borders2.bottom_colour = 0x40
borders2.top_colour = 0x40
self.style9.font = font1
self.style9.borders = borders2
self.style10 = xlwt.easyxf('pattern: pattern solid, fore_colour sea_green;')
font8 = xlwt.Font()
font8.height = 0x00E6
font8.bold = True
self.style10.font = font8
self.style10.borders = borders2
self.style11 = xlwt.XFStyle()
font9 = xlwt.Font()
font9.name = 'Verdana'
font9.bold = True
font9.colour_index = 0x28
self.style11.font =font9
self.style11.borders = borders1
except Exception,e:
log_print(e)
pass
# for reading excel docs import xlrd book = xlrd.open_workbook(excel_file_name) #print "The number of worksheets is", book.nsheets #print "Worksheet name(s):", book.sheet_names() sh = book.sheet_by_index(0) #print sh.name, sh.nrows, sh.ncols global current_check current_check = "blank" global section section = "blank" #for writing to excell docs import xlwt import time font0 = xlwt.Font() font0.name = 'Times New Roman' font0.colour_index = 3 # 2 is red, 3 is green font0.bold = True style0 = xlwt.XFStyle() style0.font = font0 wb = xlwt.Workbook() user_answer = "BLANK" from xlutils.copy import copy from xlrd import * w = copy(open_workbook(excel_file_name)) global first_check first_check = 17 global check_number
def data_excel_write(listData, queue, file_path):
result_data = []
for item in listData:
temp = list(filter(lambda x: x.orderno == item.orderno, result_data))
if len(temp) == 0:
result_data.append(item)
pass
# 创建Excel工作薄,编码方式为：utf-8
myWorkbook = xlwt.Workbook(encoding='utf-8')
# 添加Excel工作表
mySheet = myWorkbook.add_sheet('对比结果', cell_overwrite_ok=True)
# 使用样式
style = xlwt.XFStyle() # 创建一个样式
# 为样式创建字体
font = xlwt.Font()
font.name = '宋体'
font.bold = True # 是否加粗，默认值为false不加粗
style.font = font
# 设置对齐方式，文本的对齐方式
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平居中
alignment.vert = xlwt.Alignment.VERT_CENTER # 垂直居中
style.alignment = alignment
mySheet.write(0, 0, "订单号", style)
mySheet.write(0, 1, "购买数量-平台", style)
mySheet.write(0, 2, "购买数量-OTA", style)
mySheet.write(0, 3, "核销数量-平台", style)
mySheet.write(0, 4, "核销数量-OTA", style)
mySheet.write(0, 5, "退款数量-平台", style)
mySheet.write(0, 6, "退款数量-平台", style)
mySheet.write(0, 7, "对比时间-线上", style)
mySheet.write(0, 8, "对比时间-OTA", style)
mySheet.write(0, 9, "异常原因", style)
if len(listData) == 0:
mySheet.write(1, 0, '没毛病！')
queue.put('没有异常单,完全没毛病！')
pass
else:
rowIndex = 1
for item in listData:
mySheet.write(rowIndex, 0, item.orderno)
mySheet.write(rowIndex, 1, item.buycnt)
mySheet.write(rowIndex, 2, item.buycntota)
mySheet.write(rowIndex, 3, item.checkcnt)
mySheet.write(rowIndex, 4, item.checkcntota)
mySheet.write(rowIndex, 5, item.refundcnt)
mySheet.write(rowIndex, 6, item.refundcntota)
mySheet.write(rowIndex, 7, item.comtime)
mySheet.write(rowIndex, 8, item.comtimeota)
mySheet.write(rowIndex, 9, item.reason)
rowIndex += 1
queue.put("异常单: 订单号: %s \t 异常原因：%s" % (item.orderno, item.reason))
pass
file_dir = file_path[0:file_path.rfind('/', 0)]
file_name = file_path[file_path.rfind('/', 0):]
fullPath = file_dir + '/' + ('对比结果%s%s' % (
int(time.time()),
file_name[1:],
))
myWorkbook.save(fullPath)
queue.put('结果文件地址：' + fullPath)
pass
def SankeyExport(
self, Year, Path, Element
): # Export data for given year in excel format for the D3.js Circular Sankey method
""" Exports MFAsystem to xls Template for the Circular Sankey method."""
TimeIndex = Year - self.Time_Start
myfont = xlwt.Font()
myfont.bold = True
mystyle = xlwt.XFStyle()
mystyle.font = myfont
Result_workbook = xlwt.Workbook(encoding='ascii')
Result_worksheet = Result_workbook.add_sheet('Nodes')
Result_worksheet.write(0, 0, label='Name', style=mystyle)
Result_worksheet.write(0, 1, label='Color', style=mystyle)
Result_worksheet.write(0, 2, label='Orientation', style=mystyle)
Result_worksheet.write(0, 3, label='Width', style=mystyle)
Result_worksheet.write(0, 4, label='Height', style=mystyle)
Result_worksheet.write(0, 5, label='x_position', style=mystyle)
Result_worksheet.write(0, 6, label='y_position', style=mystyle)
for m in range(0, len(self.ProcessList)):
if self.ProcessList[m].Graphical is None:
raise ValueError(
'Graphical properties of process number {foo} are not set. No export to Sankey possible, as position of process on canvas etc. needs is not specified.'
.format(foo=m))
Result_worksheet.write(m + 1,
0,
label=self.ProcessList[m].Graphical['Name'])
Result_worksheet.write(
m + 1, 1, label=self.ProcessList[m].Graphical['Color'])
Result_worksheet.write(
m + 1, 2, label=self.ProcessList[m].Graphical['Angle'])
Result_worksheet.write(
m + 1, 3, label=self.ProcessList[m].Graphical['Width'])
Result_worksheet.write(
m + 1, 4, label=self.ProcessList[m].Graphical['Height'])
Result_worksheet.write(m + 1,
5,
label=self.ProcessList[m].Graphical['xPos'])
Result_worksheet.write(m + 1,
6,
label=self.ProcessList[m].Graphical['yPos'])
Result_worksheet = Result_workbook.add_sheet('Flows')
Result_worksheet.write(0, 0, label='StartNode', style=mystyle)
Result_worksheet.write(0, 1, label='EndNode', style=mystyle)
Result_worksheet.write(0, 2, label='Value', style=mystyle)
Result_worksheet.write(0, 3, label='Color', style=mystyle)
for key in self.FlowDict:
Result_worksheet.write(m + 1, 0, label=self.FlowDict[key].P_Start)
Result_worksheet.write(m + 1, 1, label=self.FlowDict[key].P_End)
Result_worksheet.write(m + 1,
2,
label=float(
self.Flow_Sum_By_Element(key)[TimeIndex,
Element]))
Result_worksheet.write(m + 1, 3, label=self.FlowDict[key].Color)
Result_workbook.save(Path + self.Name + '_' + str(TimeIndex) + '_' +
str(Element) + '_Sankey.xls')
return style
styleOK = xlwt.easyxf()
pattern = xlwt.Pattern() # 一个实例化的样式类
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # 固定的样式
pattern.pattern_fore_colour = xlwt.Style.colour_map['white'] # 背景颜色
borders = xlwt.Borders() # 为样式创建边框
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
font = xlwt.Font() # 为样式创建字体
font.name = '华文中宋'
font.bold = False
font.height = 240
styleOK.pattern = pattern
styleOK.borders = borders
styleOK.font = font
#检测输入表格并计算它们的数量
filenames = []
outputdir = ".\输入表格"
for item in os.listdir(outputdir):
if item.split('.')[-1] != 'py' and item.split('.')[
-1] == 'xlsx' or item.split('.')[-1] == 'csv' and os.path.isdir(
item) == False or item.split('.')[-1] == 'xls':
def export_xls(result):
url = '/static/export/%s/%s.xls' % (time.strftime('%Y%m'),
time.strftime('%Y%m%d-%H%M%S'))
filename = 'xd_REST' + url
export_dir, _ = os.path.split(filename)
if not os.path.exists(export_dir):
os.makedirs(export_dir)
#写文件
wbk = xlwt.Workbook(encoding='utf-8')
sheet1 = wbk.add_sheet('sheet1', cell_overwrite_ok=True)
#字体
font = xlwt.Font()
font.name = 'Calibri' #Calibri
font.height = 220 #11
#设置默认字体
xlwt.Style.default_style.font = font
#合并居中分格
style = xlwt.XFStyle()
style.alignment.vert = xlwt.Alignment.VERT_CENTER
style.font = font
i = 0
for row in result:
#输出表头
if i == 0:
export_headers(sheet1, row)
current_row = []
for header in g.rpt_headers:
col = row[header.name]
if col == None:
col = ''
if isinstance(col, datetime.datetime):
if col.year >= 1900:
col = col.strftime("%Y-%m-%d %H:%M:%S")
else:
col = '{0.year:04d}-{0.month:02d}-{0.day:02d} {0.hour:02d}:{0.minute:02d}:{0.second:02d}'.format(
col)
current_row.append(col)
#分组合并单元格
if g.rpt_report.rowspan == 1:
if i == 0:
rowspan = [2] * len(current_row)
else:
for col in g.grouped_cols:
if current_row[col] != prev_row[col]:
r1 = rowspan[col]
r2 = i + 1
c1 = c2 = col
rowspan[col] = r2 + 1
if r2 - r1 > 1:
sheet1.write_merge(r1,
r2,
c1,
c2,
prev_row[col],
style=style)
#小计
if len(g.grouped_cols) > 0:
if i == 0:
subtotal = acc_init(current_row)
else:
different = False
for col in g.grouped_cols:
if current_row[col] != prev_row[col]:
different = True
break
if different:
#切换分组，输出小计行，重新开始小计
subtotal[0] = '小计'
writerow(sheet1, i, subtotal)
subtotal = acc_init(current_row)
i = i + 1
if g.rpt_report.rowspan == 1:
for col in range(len(rowspan)):
rowspan[col] = i + 2
else:
#小计
subtotal = acc(subtotal, current_row)
#合计
if g.rpt_report.acc_total == 1:
if i == 0:
total = acc_init(current_row)
else:
total = acc(total, current_row)
prev_row = current_row
writerow(sheet1, i, current_row)
#行号自增
i = i + 1
#最后一个分组合并单元格
if i > 0 and g.rpt_report.rowspan == 1:
for col in g.grouped_cols:
r1 = rowspan[col]
r2 = i + 1
c1 = c2 = col
rowspan[col] = r2 + 1
if r2 - r1 > 1:
sheet1.write_merge(r1, r2, c1, c2, prev_row[col], style=style)
#最后一个小计
if i > 0 and len(g.grouped_cols) > 0:
subtotal[0] = '小计'
writerow(sheet1, i, subtotal)
i = i + 1
#输出合计
if i > 0 and g.rpt_report.acc_total == 1:
total[0] = '合计'
writerow(sheet1, i, total)
if i == 0:
sheet1.write(0, 0, '根据过滤条件，查找不到任何数据')
wbk.save(filename)
return redirect(url)
] = read_bds_sheet(ARINC_format_labels, ARINC_deformat_labels,
bds_sheet_name, bds_formating, bds_format)
#Open Excel MICD template file for writing
appdir = os.path.dirname(sys.argv[0])
if appdir == '':
appdir = '.'
MICD_template_file = xlrd.open_workbook(filename=appdir + '/ICD_template.xls',
formatting_info=True,
logfile=sys.stdout,
verbosity=0)
MICD_file = xlutils.copy.copy(MICD_template_file)
MICD_title_style = xlwt.XFStyle()
MICD_title_style.font = xlwt.Font()
MICD_title_style.font.bold = True
MICD_title_style.pattern = xlwt.Pattern()
MICD_title_style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
MICD_title_style.pattern.pattern_fore_colour = 7
MICD_title_style.alignment = xlwt.Alignment()
MICD_title_style.alignment.wrap = True
MICD_title_style.alignment.vert = 0
#Create basic FUN_IN and the title line
MICD_fun_in_titles = [
'Name', 'Type', 'Unit', 'Description', 'Convention', 'Dim1', 'Dim2',
'Com Format', 'Com Mode', 'From', 'Refresh\nRate', 'Min', 'Max', 'Enum',
'Consumed\nIf', 'Aircraft Signal\nName', 'Interface\nLevel',
'Status (SSM/FS/Refresh)', 'Simulation\nLevel [1]', 'Init\nValue',
'Custom', 'Comment', 'Last Modification'
#列幅 newSheet_1_column_1 = newsheet_1.col(2) newSheet_1_column_1.width = 5000 # Alignment c_align = xlwt.Alignment() c_align.horz = xlwt.Alignment.HORZ_CENTER # 背景 c_pattern = xlwt.Pattern() c_pattern.pattern = xlwt.Pattern.SOLID_PATTERN c_pattern.pattern_fore_colour = 0x16 #Font c_font_1 = xlwt.Font() c_font_1.bold = True c_font_1.colour_index = 0x35 c_style_1 = xlwt.XFStyle() c_style_1.font = c_font_1 c_style_1.num_format_str = 'yyyy-mm-dd' c_font_2 = xlwt.Font() c_font_2.name = 'Arial' c_style_2 = xlwt.XFStyle() c_style_2.font = c_font_2 c_style_2.alignment = c_align c_style_2.pattern = c_pattern # 上書きはダメ？ #Exception: Attempt to overwrite cell: sheetname='Newsheet_1' rowx=1 colx=0
def Writexls(addlist, resultdict):
CapTime = time.strftime('%Y_%m_%d-%H_%M_%S', time.localtime()) #取个时间
# 公共表格属性
# 对齐方式：上下居中，左右居中
Alignment = xlwt.Alignment()
Alignment.horz = 0x02
Alignment.vert = 0x02
# 字体：consolas 14号
Font = xlwt.Font()
Font.name = 'Consolas'
Font.height = 20 * 14
# 结果为NO的单元格（地址可用）颜色-绿色
PatternYes = xlwt.Pattern()
PatternYes.pattern = xlwt.Pattern.SOLID_PATTERN
PatternYes.pattern_fore_colour = xlwt.Style.colour_map['green']
# 结果为Yes的单元格（地址不可用）颜色-红色
PatternNo = xlwt.Pattern()
PatternNo.pattern = xlwt.Pattern.SOLID_PATTERN
PatternNo.pattern_fore_colour = xlwt.Style.colour_map['red']
# 设置地址可用时的格式
StlYes = xlwt.XFStyle()
StlYes.pattern = PatternYes
StlYes.alignment = Alignment
StlYes.font = Font
# 设置地址不可用的格式
StlNo = xlwt.XFStyle()
StlNo.pattern = PatternNo
StlNo.alignment = Alignment
StlNo.font = Font
# 设置第一行的格式
StlTitle = xlwt.XFStyle()
StlTitle.alignment = Alignment
StlTitle.font = Font
ListLenth = len(addlist)
OutXls = xlwt.Workbook()
#创建一个列表，将每个sheet作为一个对象放入列表中。多sheet写入的情况，在此处卡了很久。
SheetList = []
for i in range(0, ListLenth):
SheetList.append(OutXls.add_sheet(addlist[i].replace('/', '_')))
#两个列表元素，双循环，省事。
for sht, addr in zip(SheetList, addlist):
# 写表头：描述是哪个网段
# worksheet.write_merge(开始行,结束行, 开始列, 结束列, '内容', style)
abc = '' #这个就是用来处理个字段没啥用。
for i in addr.split('.')[:3]:
abc = abc + i + '.'
sht.write_merge(0, 0, 1, 16, addr, style=StlTitle)
for i in range(0, 16):
for j in range(0, 16):
if i * 16 + j == 0 or i * 16 + j == 255:
sht.write(i + 1, j + 1, label=str(i * 16 + j), style=StlNo)
else:
if resultdict[abc + str(i * 16 + j)] == 'No':
sht.write(i + 1,
j + 1,
label=str(i * 16 + j),
style=StlYes)
elif resultdict[abc + str(i * 16 + j)] == 'Yes':
sht.write(i + 1,
j + 1,
label=str(i * 16 + j),
style=StlNo)
else:
sht.write(i + 1, j + 1, label=str(i * 16 + j))
OutXls.save(r'/root/Mping/Xls_file/%s_IPused.xls' % CapTime)
return
# mysqlcursor.execute('''SELECT DISTINCT(date) from temp where date=DATE(DATE_SUB(NOW(), INTERVAL %s DAY))''',(inter))
#
# dateList=mysqlcursor.fetchall()
# # dateList=list()
rowNumber = 1
# for one_temp_date in dateList:
# if one_temp_date[0] is not None:
# print one_temp_date[0]
m = 1
today = datetime.date.today()
oneday = datetime.timedelta(days=1)
yesterday = today - oneday
print yesterday
ws = w.add_sheet(str(yesterday).decode('utf-8'), cell_overwrite_ok=True)
font = xlwt.Font() # Create the Font
font.name = 'Times New Roman'
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour = 0x3A
style = xlwt.XFStyle()
style.borders = borders
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style = xlwt.XFStyle()
style.borders = borders
style.alignment = alignment
from xlutils.copy import copy import xlrd import xlwt temp_excel = xlrd.open_workbook(r"C:\Users\Admin\Desktop\heping.xls",formatting_info=True) temp_sheet = temp_excel.sheet_by_index(0) print(temp_sheet.cell(2,2).value) new_excel = copy(temp_excel) new_sheet = new_excel.get_sheet(0) style = xlwt.XFStyle() #设置字体 font = xlwt.Font() font.name = "微软雅黑" font.bold = True font.height = 360 style.font = font #设置边框 borders = xlwt.Borders() borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN style.borders = borders #设置对齐方式 alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_CENTER alignment.vert = xlwt.Alignment.VERT_CENTER style.alignment = alignment
def action_excel(self, cr, uid, ids, chart_account_id, form=None, context=None):
if not ids:
return {'type_hr': 'ir.actions.act_window_close'}
if context is None:
context = {}
wb = pycel.Workbook(encoding='utf-8')
style_cabecera = pycel.easyxf('font: colour black, bold True;'
'align: vertical center, horizontal center;'
)
style_header = pycel.easyxf('font: bold True;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
view_style = pycel.easyxf('font: bold True, height 160;'
'align: vertical center, horizontal left, wrap on;')
linea_izq = pycel.easyxf('font: colour black, height 150;'
'align: vertical center, horizontal left, wrap on;'
)
linea_izq_n = pycel.easyxf('font: colour black, height 140;'
'align: vertical center, horizontal left, wrap on;'
)
linea_der = pycel.easyxf('font: colour black, height 150;'
'align: vertical center, horizontal right;'
)
ws = wb.add_sheet('HOJA DE BALANCE')
ws.show_grid = False
ws.header_str = u"&LFecha de Impresion: &D Hora: &T&RPagina &P de &N"
ws.footer_str = u""
compania = self.pool.get('res.users').browse(cr, uid, uid).company_id
# x0 = 11
ws.write_merge(1, 1, 1, 5, compania.name, style_cabecera)
ws.write_merge(2, 2, 1, 5, 'Direccion: ' + compania.partner_id.street, style_cabecera)
ws.write_merge(3, 3, 1, 5, 'Ruc: ' + compania.partner_id.part_number, style_cabecera)
ws.write_merge(5, 5, 1, 5, datetime.today().strftime('%Y-%m-%d'), style_cabecera)
if form['filter'] == 'filter_period':
period_from = 'Desde:' + self.pool.get('account.period').browse(cr, uid, form['period_from']).name
period_to = 'Hasta' + self.pool.get('account.period').browse(cr, uid, form['period_to']).name
ws.write(6, 2, period_from, style_cabecera)
ws.write(6, 4, period_to, style_cabecera)
elif form['filter'] == 'filter_date':
ws.write(6, 2, ('Desde:' + form['date_from']), style_cabecera)
ws.write(6, 4, ('Hasta:' + form['date_to']), style_cabecera)
ws.fit_num_pages = 1
ws.fit_height_to_pages = 0
ws.fit_width_to_pages = 1
ws.portrait = 1
align = pycel.Alignment()
align.horz = pycel.Alignment.HORZ_RIGHT
align.vert = pycel.Alignment.VERT_CENTER
font1 = pycel.Font()
font1.colour_index = 0x0
font1.height = 140
linea_izq_n.width = 150
#Formato de Numero
style = pycel.XFStyle()
style.num_format_str = '#,##0.00'
style.alignment = align
style.font = font1
#Formato de Numero Saldo
font = pycel.Font()
font.bold = True
font.colour_index = 0x27
style1 = pycel.XFStyle()
style1.num_format_str = '#,##0.00'
style1.alignment = align
style1.font = font
font2 = pycel.Font()
font2.bold = True
font2.colour_index = 0x0
style2 = pycel.XFStyle()
style2.num_format_str = '#,##0.00'
style2.alignment = align
style2.font = font2
style3 = pycel.XFStyle()
style3.num_format_str = '0'
style3.alignment = align
style3.font = font1
#info = self.get_payroll(cr, uid, form)
xi = 8 # Cabecera de Cliente
sec = 1
# if type_hr == 'rol':
ws.write(xi, 1, 'Codigo', style_header)
ws.write(xi, 2, 'Cuenta', style_header)
ws.write(xi, 3, 'Cod.Matriz', style_header)
ws.write(xi, 4, 'Cuenta Matriz', style_header)
ws.write(xi, 5, 'Saldo Ant.', style_header)
ws.write(xi, 6, 'Debe', style_header)
ws.write(xi, 7, 'Haber', style_header)
ws.write(xi, 8, 'Saldo', style_header)
# lst_titulos = self.get_titulos_report(cr, uid, form)
col_t = 8
# for titulo in lst_titulos:
# col_t+=1
# ws.write(xi, col_t, titulo, style_header)
xi += 1
# seq = 0
rf = rr = ri = 0
account_pool = self.pool.get('account.account')
ctx = dict()
if context['filter'] != 'filter_no':
cr.execute('SELECT MIN(date_start) FROM account_period WHERE fiscalyear_id = %s', (form['fiscalyear_id'],))
dict_date = cr.dictfetchall()
if context['filter'] == 'filter_period':
date_from = self.pool.get('account.period').browse(cr, uid, context['period_from']).date_start
else:
date_from = form['date_from']
if date_from > dict_date[0]['min']:
date_from = datetime.strptime(date_from, "%Y-%m-%d") - relativedelta(days=1)
date_from = date_from.strftime("%Y-%m-%d")
ctx['fiscalyear'] = form['fiscalyear_id']
ctx['date_from'] = dict_date[0]['min']
ctx['date_to'] = date_from
ctx['state'] = form['target_move']
lines = self.lines(cr, uid, [chart_account_id], False, form, context=context)
for linea in lines:
# seq += 1
# ws.write(xi, 1, seq, linea_center)
account = account_pool.browse(cr, uid, linea['id'], ctx)
ws.write(xi, 1, account.code, linea_izq if account.type != 'view' else view_style)
ws.write(xi, 2, account.name, linea_izq if account.type != 'view' else view_style)
ws.write(xi, 3, account.code_tiw, linea_izq if account.type != 'view' else view_style)
ws.write(xi, 4, account.name_tiw, linea_izq if account.type != 'view' else view_style)
ws.write(xi, 5, account.balance if ctx else 0.00, linea_der)
ws.write(xi, 6, linea.get('debit', ''), linea_der)
ws.write(xi, 7, linea.get('credit', ''), linea_der)
ws.write(xi, 8, linea.get('balance', ''), linea_der)
col = 8
xi += 1
ws.col(0).width = 2000
ws.col(1).width = 5800
ws.col(2).width = 9900
ws.col(3).width = 5000
ws.col(4).width = 6900
ws.col(5).width = 6500
ws.col(6).width = 5500
ws.col(7).width = 5500
ws.col(8).width = 5500
ws.col(9).width = 5500
ws.col(10).width = 6500
ws.col(11).width = 6500
ws.col(12).width = 6500
ws.col(13).width = 6500
ws.col(14).width = 6500
ws.col(15).width = 6500
ws.col(16).width = 6500
ws.col(17).width = 6500
ws.col(18).width = 6500
ws.col(19).width = 6500
ws.col(20).width = 6500
ws.col(21).width = 6500
ws.col(22).width = 6500
ws.col(23).width = 6500
ws.col(24).width = 6500
ws.col(25).width = 6500
ws.col(26).width = 6500
ws.col(27).width = 6500
ws.col(28).width = 6500
ws.col(29).width = 6500
ws.col(30).width = 6500
ws.row(8).height = 750
buf = cStringIO.StringIO()
# name = "%s%s%s.xls" % (path, "Reporte_RR_HH", datetime.datetime.now())
# exists_path = os.path.exists(path)
# print 'esta ruta existe?', exists_path
# print('sys.argv[0] =', sys.argv[0])
# a = sys.path
# pathname = os.path.dirname(os.getcwd())
# print('path =', a)
# print('full path =', os.path.abspath(pathname))
try:
# wb.save(name)
# raise Warning('Archivo salvado correctamente')
buf = cStringIO.StringIO()
wb.save(buf)
out = base64.encodestring(buf.getvalue())
buf.close()
data_fname = "Balance de Comprobacion_%s.xls"
archivo = '/opt/temp/' + data_fname
res_model = 'account.balance.report'
id = ids and type(ids) == type([]) and ids[0] or ids
self.load_doc(cr, uid, out, id, data_fname, archivo, res_model)
return self.write(cr, uid, ids, {'data': out, 'txt_filename': data_fname, 'name': 'Reporte_Balance.xls'})
# return self.write(cr, uid, ids, {'data': out, 'txt_filename': name})
except ValueError:
raise Warning('Error a la hora de salvar el archivo')
def font_style(position='left',
bold=0,
fontos=0,
font_height=200,
border=0,
color=False):
font = xlwt.Font()
font.name = 'Verdana'
font.bold = bold
font.height = font_height
center = xlwt.Alignment()
center.horz = xlwt.Alignment.HORZ_CENTER
center.vert = xlwt.Alignment.VERT_CENTER
center.wrap = xlwt.Alignment.VERT_JUSTIFIED
left = xlwt.Alignment()
left.horz = xlwt.Alignment.HORZ_LEFT
left.vert = xlwt.Alignment.VERT_CENTER
left.wrap = xlwt.Alignment.VERT_JUSTIFIED
borders = xlwt.Borders()
borders.right = 1
borders.left = 1
borders.top = 1
borders.bottom = 1
orient = xlwt.Alignment()
orient.orie = xlwt.Alignment.ORIENTATION_90_CC
style = xlwt.XFStyle()
if border == 1:
style.borders = borders
if fontos == 'red':
font.colour_index = 2
style.font = font
else:
style.font = font
if position == 'center':
style.alignment = center
else:
style.alignment = left
if color == 'grey':
badBG = xlwt.Pattern()
badBG.pattern = badBG.SOLID_PATTERN
badBG.pattern_fore_colour = 22
style.pattern = badBG
if color == 'red':
badBG = xlwt.Pattern()
badBG.pattern = badBG.SOLID_PATTERN
badBG.pattern_fore_colour = 5
style.pattern = badBG
if color == 'yellow':
badBG = xlwt.Pattern()
badBG.pattern = badBG.SOLID_PATTERN
badBG.pattern_fore_colour = 0x0D
style.pattern = badBG
return style
def get_xls_file(self):
'''
The method used to call download file of wizard
@self : Record Set
@api.multi : The decorator of multi
@return: Return of wizard of action in dictionary
-----------------------------------------------------
'''
cpf_binary_obj = self.env['cpf.binary.wizard']
cr, uid, context = self.env.args
if context is None: context = {}
context = dict(context)
payment_wiz_data = self.read([])
data = {}
if payment_wiz_data:
data = payment_wiz_data[0]
start_date = data.get('date_start', False) or False
stop_date = data.get('date_stop', False) or False
end_date = data.get('date_stop', False) or False
emp_ids = data.get('employee_ids', False) or []
if start_date >= end_date:
raise ValidationError(_("You must be enter start date less than end date !"))
for employee in self.env['hr.employee'].browse(emp_ids):
if not employee.identification_id:
raise ValidationError(_('There is no identification no define for %s employee.' % (employee.name)))
context.update({'employee_id': emp_ids, 'date_start': start_date, 'date_stop': end_date})
company_data = self.env['res.users'].browse(uid).company_id
if data.get("export_report", False) == "pdf":
data.update({'currency': " " + tools.ustr(company_data.currency_id.symbol), 'company': company_data.name})
for employee in self.env['hr.employee'].browse(data.get('employee_ids')):
if not employee.identification_id:
raise Warning(_('There is no identification no define for %s employee.' % (employee.name)))
return self.env['report'].with_context(landscape=True).get_action(self, 'sg_hr_report.report_payment_advice')
else:
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('sheet 1', cell_overwrite_ok=True)
font = xlwt.Font()
font.bold = True
bold_style = xlwt.XFStyle()
bold_style.font = font
border = xlwt.easyxf('font: bold on, color black; align: wrap no; borders: left thin, right thin, top thin, bottom thin; pattern: pattern solid, fore_color white;')
borders = xlwt.easyxf('font: bold off, color black; align: wrap no; borders: left thin, right thin, top thin, bottom thin; pattern: pattern solid, fore_color white;')
style = xlwt.easyxf('align: wrap no')
style.num_format_str = '#,##0.00'
new_style = xlwt.easyxf('font: bold on; align: wrap no')
new_style.num_format_str = '#,##0.00'
# static data
sheet.write(0, 4, company_data.name)
sheet.write(1, 3, company_data.street or '')
sheet.write(2, 3, str(company_data and company_data.street2 or ' ') + ' ' + str(company_data and company_data.country_id and company_data.country_id.name or '') + ' ' + str(company_data.zip) or '')
sheet.write(3, 4, 'Tel : ' + str(company_data.phone or ' ') + ',' + 'Fax : ' + str(company_data.fax or ''))
sheet.write(4, 5, 'PAYMENT ADVICE')
sheet.write(6, 0, 'MANDATORY REF NO. : ' + str(company_data.company_code or ''))
sheet.write(7, 0, 'VOLUNTARY REF NO. : ')
sheet.write(8, 0, company_data.name)
sheet.write(9, 0, '6 Jalan Kilang #04-00')
sheet.write(6, 8, 'SUBM MODE')
sheet.write(7, 8, 'DATE')
sheet.write(6, 10, ':')
sheet.write(7, 10, ':')
sheet.write(6, 11, 'INTERNAL')
sheet.write(12, 0, 'PART 1 : Payment Details For ', bold_style)
sheet.write(13, 8, 'AMOUNT' , bold_style)
sheet.write(13, 10, 'NO. OF EMPLOYEE', bold_style)
sheet.write(15, 0, '1. CPF Contribution')
sheet.write(16, 1, 'Mandatory Contribution')
sheet.write(17, 1, 'Voluntary Contribution')
sheet.write(18, 0, '2. B/F CPF late Payment interest')
sheet.write(19, 0, 'Interest charged on last payment')
sheet.write(20, 0, '3. Late payment interest on CPF Contribution')
sheet.write(21, 0, '4. Late payment penalty for Foreign Worker Levy')
sheet.write(22, 0, '5. Foreign Worker Levy')
sheet.write(23, 0, '6. Skills Development Levy')
sheet.write(24, 0, '7. Donation to Community Chest')
sheet.write(25, 0, '8. Mosque Building & Mendaki Fund (MBMF)')
sheet.write(26, 0, '9. SINDA Fund')
sheet.write(27, 0, '10. CDAC Fund')
sheet.write(28, 0, '11. Eurasian Community Fund (EUCF)')
# total
sheet.write(30, 7, 'Total', bold_style)
# static data
sheet.write(31, 4, 'Please fill in cheque details if you are paying by cheque')
sheet.write(32, 4, 'BANK')
sheet.write(32, 5, ':')
sheet.write(33, 4, 'CHEQUE NO.')
sheet.write(33, 5, ':')
sheet.write(34, 4, 'THE EMPLOYER HEREBY GUARANTEES')
sheet.write(35, 4, 'THE ACCURACY')
sheet.write(36, 4, 'OF THE CPF RETURNS FOR')
sheet.write(37, 4, 'AS SHOWN ON THE SUBMITTED DISKETTE.')
sheet.write(39, 4, 'EMPLOYER\'S AUTHORIZED SIGNATORY')
sheet.write(42, 0, 'PART 2 : Contribution Details For', bold_style)
# data header
sheet.write(44, 0, 'Employee Name', border)
sheet.write(43, 3, 'CPF', border)
sheet.write(44, 3, 'Account No.', border)
sheet.write(43, 4, 'Mandatory CPF', border)
sheet.write(44, 4, 'Contribution', border)
sheet.write(43, 5, 'Voluntary CPF', border)
sheet.write(44, 5, 'Contribution', border)
sheet.write(43, 6, 'Last', border)
sheet.write(44, 6, 'Contribution', border)
# sheet.write(41, 8, 'CPF', bold_style)
# sheet.write(42, 8, 'Status', bold_style)
sheet.write(43, 8, 'MBMF', border)
sheet.write(44, 8, 'Fund', border)
sheet.write(43, 9, 'SINDA', border)
sheet.write(44, 9, 'Fund', border)
sheet.write(43, 10, 'CDAC', border)
sheet.write(44, 10, 'Fund', border)
sheet.write(43, 11, 'ECF', border)
sheet.write(44, 11, 'Fund', border)
sheet.write(43, 12, 'SDL', border)
sheet.write(44, 12, 'Fund', border)
sheet.write(43, 13, 'Ordinary', border)
sheet.write(44, 13, 'Wages', border)
sheet.write(43, 14, 'Additional', border)
sheet.write(44, 14, 'Wages', border)
sheet.write(43, 0, '', border)
sheet.write(43, 1, '', border)
sheet.write(43, 2, '', border)
sheet.write(43, 7, '', border)
sheet.write(44, 1, '', border)
sheet.write(44, 2, '', border)
sheet.write(44, 7, '', border)
emp_obj = self.env['hr.employee']
payslip_obj = self.env['hr.payslip']
hr_contract_obj = self.env['hr.contract']
category_ids = self.env['hr.employee.category'].search([])
start_row = raw_no = 45
# start_date = self.date_start or False
# stop_date = self.date_stop or False
# employee_ids_lst = context.get('employee_id',False)
# print"\n\nemployee_list_ids:\n\n", employee_ids_lst
month_dict = {'01':'January', '02':'February', '03': 'March', '04':'April', '05':'May', '06':'June', '07':'July', '08':'August', '09':'September', '10':'October', '11': 'November', '12': 'December'}
period = month_dict.get(start_date.split('-')[1]) + ', ' + start_date.split('-')[0]
sheet.write(36, 7, period)
sheet.write(7, 11, datetime.strptime(start_date, DEFAULT_SERVER_DATE_FORMAT).strftime('%d-%m-%Y'))
sheet.write(42, 2, period, bold_style)
sheet.write(12, 2, period, bold_style)
t_cpfsdl_amount = t_p_cpf_sdl_amount = t_p_fwl_amount = t_p_cpf_amount = t_gross_amount = t_ecf_amount = t_cdac_amount = t_sinda_amount = t_mbmf_amount = t_cpf_amount = 0.00
total_additional_amount = total_cpfsdl_amount = total_p_cpf_amount = total_gross_amount = total_ecf_amount = total_cdac_amount = total_sinda_amount = total_mbmf_amount = total_cpf_amount = 0.00
emp_cpfsdl_amount = emp_sdl_amount = emp_ecf_amount = emp_fwl_amount = emp_cdac_amount = emp_sinda_amount = emp_mbmf_amount = emp_cpf_amount = 0.00
# no category
join_date = start_date
emply_ids = emp_obj.search([('id', 'in', emp_ids),
('category_ids', '=', False)])
do_total = False
for emp_record in emply_ids:
payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id),
('date_from', '>=', start_date),
('date_from', '<=', stop_date),
('state', 'in', ['draft', 'done', 'verify'])])
previous_date = cpf_binary_obj._default_previous_date(start_date)
previous_payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id)], order='date_from ASC', limit=1)
if previous_payslip_ids:
join_date = previous_payslip_ids.date_from
while(join_date <= previous_date[0]):
previous_payslip_ids = payslip_obj.search([ ('employee_id', '=', emp_record.id),
('date_from', '>=', previous_date[0]),
('date_from', '<=', previous_date[1]),
('state', 'in', ['draft', 'done', 'verify'])])
if previous_payslip_ids:
break
else:
previous_date = cpf_binary_obj._default_previous_date(previous_date[0])
# if not payslip_ids:
# raise ValidationError(_('There is no payslip details between selected date %s and %s') %(previous_date[0], previous_date[1]))
additional_amount = cpfsdl_amount = p_cpf_amount = gross_amount = ecf_amount = fwl_amount = cdac_amount = sinda_amount = mbmf_amount = cpf_amount = 0.00
for payslip_rec in payslip_ids:
for line in payslip_rec.line_ids:
if line.register_id.name == 'CPF':
cpf_amount += line.amount
if line.register_id.name == 'CPF - MBMF':
mbmf_amount += line.amount
if line.register_id.name == 'CPF - SINDA':
sinda_amount += line.amount
if line.register_id.name == 'CPF - CDAC':
cdac_amount += line.amount
if line.register_id.name == 'CPF - ECF':
ecf_amount += line.amount
if line.register_id.name == 'CPF - FWL':
fwl_amount += line.amount
t_p_fwl_amount += line.amount
if line.register_id and line.register_id.name == 'BONUS':
gross_amount -= line.amount
if line.category_id.code == 'GROSS':
gross_amount += line.amount
if line.code == 'CPFSDL':
cpfsdl_amount += line.amount
t_p_cpf_sdl_amount += line.amount
if line.register_id and line.register_id.name == 'BONUS':
additional_amount += line.amount
if not gross_amount:
continue
if not cpf_amount and not mbmf_amount and not sinda_amount and not cdac_amount and not ecf_amount and not cpfsdl_amount:
continue
sheet.write(raw_no, 0, payslip_rec.employee_id and payslip_rec.employee_id.name or '', borders)
sheet.write(raw_no, 1, '', borders)
sheet.write(raw_no, 2, '', borders)
sheet.write(raw_no, 3, payslip_rec.employee_id and payslip_rec.employee_id.identification_id or '', borders)
# previous cpf
if previous_payslip_ids:
if payslip_rec.date_from != payslip_rec.date_from:
for previous_line in previous_payslip_ids.line_ids:
if previous_line.register_id.name == 'CPF':
p_cpf_amount += previous_line.amount
# Counts Employee
if fwl_amount:
emp_fwl_amount += 1
if cpf_amount != 0:
emp_cpf_amount += 1
if mbmf_amount != 0:
emp_mbmf_amount += 1
if sinda_amount != 0:
emp_sinda_amount += 1
if cdac_amount != 0:
emp_cdac_amount += 1
if ecf_amount != 0:
emp_ecf_amount += 1
if cpfsdl_amount != 0:
emp_sdl_amount += 1
# writes in xls file
do_total = True
sheet.write(raw_no, 4, "%.2f" % (cpf_amount or 0.00), borders)
t_cpf_amount += cpf_amount
total_cpf_amount += cpf_amount
sheet.write(raw_no, 5, "%.2f" % (0.00), borders)
# sheet.write(raw_no, 6, borders)
sheet.write(raw_no, 7, '', borders)
sheet.write(raw_no, 8, "%.2f" % (mbmf_amount or 0.00), borders)
t_mbmf_amount += mbmf_amount
total_mbmf_amount += mbmf_amount
sheet.write(raw_no, 9, "%.2f" % (sinda_amount or 0.00), borders)
t_sinda_amount += sinda_amount
total_sinda_amount += sinda_amount
sheet.write(raw_no, 10, "%.2f" % (cdac_amount or 0.00), borders)
t_cdac_amount += cdac_amount
total_cdac_amount += cdac_amount
sheet.write(raw_no, 11, "%.2f" % (ecf_amount or 0.00), borders)
t_ecf_amount += ecf_amount
total_ecf_amount += ecf_amount
sheet.write(raw_no, 12, "%.2f" % (cpfsdl_amount or 0.00), borders)
total_cpfsdl_amount += cpfsdl_amount
t_cpfsdl_amount += cpfsdl_amount
sheet.write(raw_no, 13, "%.2f" % (gross_amount or 0.00), borders)
sheet.write(raw_no, 14, "%.2f" % (additional_amount or 0.00), borders)
t_gross_amount += gross_amount
total_gross_amount += gross_amount
total_additional_amount += additional_amount
sheet.write(raw_no, 6, "%.2f" % (p_cpf_amount or 0.00), borders)
t_p_cpf_amount += p_cpf_amount
total_p_cpf_amount += p_cpf_amount
#sheet.write(raw_no, 6, 0.00, style)
contract_id = hr_contract_obj.search([('employee_id', '=', emp_record.id), '|',
('date_end','>=', payslip_rec.date_from),
('date_end','=',False)])
old_contract_id = hr_contract_obj.search([('employee_id', '=', emp_record.id),
('date_end','<=', payslip_rec.date_from)
])
for contract in contract_id:
if payslip_rec.employee_id.active == False:
sheet.write(raw_no, 7, 'Left', border)
elif contract.date_start >= payslip_rec.date_from and not old_contract_id.ids:
sheet.write(raw_no, 7, 'New Join', border)
else:
sheet.write(raw_no, 7, 'Existing', border)
raw_no += 1
if do_total:
raw_no = raw_no + 1
sheet.write(raw_no, 0, 'Total Employee:', borders)
sheet.write(raw_no, 1, '', borders)
sheet.write(raw_no, 2, '', borders)
sheet.write(raw_no, 3, '', borders)
start_row = start_row + 1
sheet.write(raw_no, 4, "%.2f" %(total_cpf_amount), borders) # cpf
sheet.write(raw_no, 5, "%.2f" % (0.00), borders) # v_cpf
sheet.write(raw_no, 6, "%.2f" % (total_p_cpf_amount or 0.00), borders) # p_cpf
sheet.write(raw_no, 7, '', borders)
sheet.write(raw_no, 8, "%.2f" % (total_mbmf_amount or 0.00), borders) # MBPF
sheet.write(raw_no, 9, "%.2f" % (total_sinda_amount or 0.00), borders) # SINDA
sheet.write(raw_no, 10, "%.2f" % (total_cdac_amount or 0.00), borders) # CDAC
sheet.write(raw_no, 11, "%.2f" % (total_ecf_amount or 0.00), borders) # ECF
sheet.write(raw_no, 12, "%.2f" % (total_cpfsdl_amount or 0.00), borders) # CPFSDL
sheet.write(raw_no, 13, "%.2f" % (total_gross_amount or 0.00), borders) # O_WAGE
sheet.write(raw_no, 14, "%.2f" % (total_additional_amount), borders)
# sheet.write(raw_no, 13, xlwt.Formula("sum(N" + str(start_row) + ":N" + str(raw_no - 1) + ")"), new_style) # AD_WAGE
# emp by category
start_row = raw_no = raw_no + 2
emp_rec = emp_obj.search([('id', 'in', emp_ids),
('category_ids', '!=', False)])
for category in category_ids:
emp_flag= False
total_additional_amount = total_cpfsdl_amount = total_p_cpf_amount = total_gross_amount = total_ecf_amount = total_cdac_amount = total_sinda_amount = total_mbmf_amount = total_cpf_amount = 0.00
for emp_record in emp_rec:
if (emp_record.category_ids and emp_record.category_ids[0].id != category.id) or not emp_record.category_ids:
continue
payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id),
('date_from', '>=', start_date),
('date_from', '<=', stop_date),
('state', 'in', ['draft', 'done', 'verify'])])
previous_date = cpf_binary_obj._default_previous_date(start_date)
previous_payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id)],
order='date_from ASC', limit=1)
if previous_payslip_ids:
join_date = previous_payslip_ids.date_from
while(join_date <= previous_date[0]):
previous_payslip_ids = payslip_obj.search([('employee_id', '=', emp_record.id),
('date_from', '>=', previous_date[0]),
('date_from', '<=', previous_date[1]),
('state', 'in', ['draft', 'done', 'verify'])])
if previous_payslip_ids:
break
else:
previous_date = cpf_binary_obj._default_previous_date(previous_date[0])
# if not payslip_ids:
# raise ValidationError(_('There is no payslip details between selected date %s and %s') %(previous_date[0], previous_date[1]))
additional_amount = cpfsdl_amount = p_cpf_amount = gross_amount = ecf_amount = fwl_amount = cdac_amount = sinda_amount = mbmf_amount = cpf_amount = 0.00
for payslip_rec in payslip_ids:
for line in payslip_rec.line_ids:
if line.register_id.name == 'CPF':
cpf_amount += line.amount
if line.register_id.name == 'CPF - MBMF':
mbmf_amount += line.amount
if line.register_id.name == 'CPF - SINDA':
sinda_amount += line.amount
if line.register_id.name == 'CPF - CDAC':
cdac_amount += line.amount
if line.register_id.name == 'CPF - ECF':
ecf_amount += line.amount
if line.register_id.name == 'CPF - FWL':
fwl_amount += line.amount
t_p_fwl_amount += line.amount
if line.register_id and line.register_id.name == 'BONUS':
gross_amount -= line.amount
if line.category_id.code == 'GROSS':
gross_amount += line.amount
if line.code == 'CPFSDL':
cpfsdl_amount += line.amount
t_p_cpf_sdl_amount += line.amount
if line.register_id and line.register_id.name == 'BONUS':
additional_amount += line.amount
if not gross_amount:
continue
if not cpf_amount and not mbmf_amount and not sinda_amount and not cdac_amount and not ecf_amount and not cpfsdl_amount:
t_p_fwl_amount -= fwl_amount
continue
sheet.write(raw_no, 0, payslip_rec.employee_id and payslip_rec.employee_id.name or '', borders)
sheet.write(raw_no, 1, '', borders)
sheet.write(raw_no, 2, '', borders)
sheet.write(raw_no, 3, payslip_rec.employee_id and payslip_rec.employee_id.identification_id or '', borders)
# previous cpf
if previous_payslip_ids:
if payslip_rec.date_from != payslip_rec.date_from:
for previous_line in previous_payslip_ids.line_ids:
if previous_line.register_id.name == 'CPF':
p_cpf_amount += previous_line.amount
# Counts Employee
if fwl_amount:
emp_fwl_amount += 1
if cpf_amount != 0:
emp_cpf_amount += 1
if mbmf_amount != 0:
emp_mbmf_amount += 1
if sinda_amount != 0:
emp_sinda_amount += 1
if cdac_amount != 0:
emp_cdac_amount += 1
if ecf_amount != 0:
emp_ecf_amount += 1
if cpfsdl_amount != 0:
emp_sdl_amount += 1
# writes in xls file
emp_flag = True
sheet.write(raw_no, 4, "%.2f" % (cpf_amount or 0.00), borders)
t_cpf_amount += cpf_amount
total_cpf_amount += cpf_amount
sheet.write(raw_no, 5, "%.2f" % (0.00), borders)
# sheet.write(raw_no, 6, borders)
sheet.write(raw_no, 7, '', borders)
sheet.write(raw_no, 8, "%.2f" % (mbmf_amount or 0.00), borders)
t_mbmf_amount += mbmf_amount
total_mbmf_amount += mbmf_amount
sheet.write(raw_no, 9, "%.2f" % (sinda_amount or 0.00), borders)
t_sinda_amount += sinda_amount
total_sinda_amount += sinda_amount
sheet.write(raw_no, 10, "%.2f" % (cdac_amount or 0.00), borders)
t_cdac_amount += cdac_amount
total_cdac_amount += cdac_amount
sheet.write(raw_no, 11, "%.2f" % (ecf_amount or 0.00), borders)
t_ecf_amount += ecf_amount
total_ecf_amount += ecf_amount
sheet.write(raw_no, 12, "%.2f" % (cpfsdl_amount or 0.00), borders)
t_cpfsdl_amount += cpfsdl_amount
total_cpfsdl_amount += cpfsdl_amount
sheet.write(raw_no, 13, "%.2f" % (gross_amount or 0.00), borders)
sheet.write(raw_no, 14, "%.2f" % (additional_amount or 0.00), borders)
t_gross_amount += gross_amount
total_gross_amount += gross_amount
sheet.write(raw_no, 6, "%.2f" % (p_cpf_amount or 0.00), borders)
t_p_cpf_amount += p_cpf_amount
total_p_cpf_amount += p_cpf_amount
total_additional_amount += additional_amount
#sheet.write(raw_no, 6, 0.00, style)
contract_id = hr_contract_obj.search([('employee_id', '=', emp_record.id), '|',
('date_end','>=', payslip_rec.date_from),
('date_end','=',False)])
old_contract_id = hr_contract_obj.search([('employee_id', '=', emp_record.id),
('date_end','<=', payslip_rec.date_from)])
for contract in contract_id:
if payslip_rec.employee_id.active == False:
sheet.write(raw_no, 7, 'Left', borders)
elif contract.date_start >= payslip_rec.date_from and not old_contract_id.ids:
sheet.write(raw_no, 7, 'New Join', borders)
else:
sheet.write(raw_no, 7, 'Existing', borders)
raw_no += 1
if emp_flag:
raw_no = raw_no + 1
sheet.write(raw_no, 0, 'Total %s :' % category.name , borders)
sheet.write(raw_no, 1, '', borders)
sheet.write(raw_no, 2, '', borders)
sheet.write(raw_no, 3, '', borders)
start_row = start_row + 1
sheet.write(raw_no, 4, "%.2f" % (total_cpf_amount or 0.00), borders) # cpf
sheet.write(raw_no, 5, "%.2f" % (0.00), borders) # v_cpf
sheet.write(raw_no, 6, "%.2f" % (total_p_cpf_amount or 0.00), borders) # p_cpf
sheet.write(raw_no, 7, '', borders)
sheet.write(raw_no, 8, "%.2f" % (total_mbmf_amount or 0.00), borders) # MBPF
sheet.write(raw_no, 9, "%.2f" % (total_sinda_amount or 0.00), borders) # SINDA
sheet.write(raw_no, 10, "%.2f" % (total_cdac_amount or 0.00), borders) # CDAC
sheet.write(raw_no, 11, "%.2f" % (total_ecf_amount or 0.00), borders) # ECF
sheet.write(raw_no, 12, "%.2f" % (total_cpfsdl_amount or 0.00), borders) # ECF
sheet.write(raw_no, 13, "%.2f" % (total_gross_amount or 0.00), borders) # O_WAGE
sheet.write(raw_no, 14, "%.2f" % (total_additional_amount), borders)
raw_no = raw_no + 2
start_row = start_row+ 3
# amount columns
sheet.write(16, 8,(t_cpf_amount or 0.00), style) # cpf
sheet.write(17, 8, 0.00, style)
sheet.write(18, 8, 0.00, style)
sheet.write(19, 8, 0.00, style)
sheet.write(20, 8, 0.00, style)
sheet.write(21, 8, 0.00, style)
sheet.write(22, 8, t_p_fwl_amount, style)
sheet.write(23, 8, t_p_cpf_sdl_amount, style)
sheet.write(24, 8, 0.00, style)
sheet.write(25, 8, (t_mbmf_amount or 0.00), style) # MBPF
sheet.write(26, 8,(t_sinda_amount or 0.00), style) # SINDA
sheet.write(27, 8,(t_cdac_amount or 0.00), style) # CDAC
sheet.write(28, 8,(t_ecf_amount or 0.00), style) # ECF
# no of employee
sheet.write(16, 10, emp_cpf_amount)
sheet.write(17, 10, 0)
sheet.write(18, 10, 0)
sheet.write(19, 10, 0)
sheet.write(20, 10, 0)
sheet.write(21, 10, 0)
sheet.write(22, 10, emp_fwl_amount)
sheet.write(23, 10, emp_sdl_amount)
sheet.write(24, 10, 0)
sheet.write(25, 10, emp_mbmf_amount)
sheet.write(26, 10, emp_sinda_amount)
sheet.write(27, 10, emp_cdac_amount)
sheet.write(28, 10, emp_ecf_amount)
sheet.write(30, 8, xlwt.Formula("sum(I17:I29)"), new_style) # Total
wbk.save(tempfile.gettempdir() + "/payslip.xls")
file = open(tempfile.gettempdir() + "/payslip.xls", "rb")
out = file.read()
file.close()
res = base64.b64encode(out)
if not start_date and stop_date:
return ''
end_date = datetime.strptime(stop_date, DEFAULT_SERVER_DATE_FORMAT)
monthyear = end_date.strftime('%m%Y')
file_name = "Payment Advice " + monthyear + '.xls'
module_rec = cpf_binary_obj.create({'name': file_name, 'xls_file' : res})
return {'name': _('Payment Advice Report'),
'res_id' : module_rec.id,
'view_type': 'form',
"view_mode": 'form',
'res_model': 'cpf.binary.wizard',
'type': 'ir.actions.act_window',
'target': 'new',
'context': context}
def get_result(self):
cr, uid, context = self.env.args
if context is None:
context = {}
context = dict(context)
data = self.read()[0]
start_date = data.get('date_start', False)
end_date = data.get('date_end', False)
if start_date and end_date and end_date < start_date:
raise Warning(_("End date should be greater than start date!"))
res_user = self.env["res.users"].browse(uid)
# Create Inventory Export report in Excel file.
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
font = xlwt.Font()
font.bold = True
header = xlwt.easyxf('font: bold 1, height 280')
# start_date = datetime.strptime(str(context.get("date_from")), DEFAULT_SERVER_DATE_FORMAT)
# start_date_formate = start_date.strftime('%d/%m/%Y')
# end_date = datetime.strptime(str(context.get("date_to")), DEFAULT_SERVER_DATE_FORMAT)
# end_date_formate = end_date.strftime('%d/%m/%Y')
# start_date_to_end_date = tools.ustr(start_date_formate) + ' To ' + tools.ustr(end_date_formate)
style = xlwt.easyxf('align: wrap yes')
worksheet.row(0).height = 500
worksheet.row(1).height = 500
for x in range(0, 41):
worksheet.col(x).width = 6000
borders = xlwt.Borders()
borders.top = xlwt.Borders.MEDIUM
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
border_style1 = xlwt.easyxf('font: bold 1')
border_style1.borders = borders
style = xlwt.easyxf('align: wrap yes', style)
ids_location = []
ids_categ = []
ids_product = []
where_end_date_awal = " sm.date is null "
where_start_date = " 1=1 "
if start_date:
where_start_date = " sm.date + interval '7 hour' >= '%s 00:00:00' " % start_date
where_end_date_awal = " sm.date + interval '7 hour' < '%s 00:00:00' " % start_date
where_end_date = " 1=1 "
if end_date:
where_end_date = " sm.date + interval '7 hour' <= '%s 23:59:59'" % end_date
where_location = " 1=1 "
if ids_location:
where_location = """(sm.location_id in %s
or sm.location_dest_id in %s)""" % (str(
tuple(ids_location)).replace(
',)', ')'), str(tuple(ids_location)).replace(',)', ')'))
where_categ = " 1=1 "
if ids_categ:
where_categ = "pt.categ_id in %s" % str(tuple(ids_categ)).replace(
',)', ')')
where_product = " 1=1 "
if ids_product:
where_product = "pp.id in %s" % str(tuple(ids_product)).replace(
',)', ')')
query = """
SELECT pp.default_code as kode_barang, pt.name as nama_barang, uu.name as satuan, sml.qty_done, sml.state, sm.name
FROM stock_move sm
LEFT JOIN product_product pp ON pp.id=sm.product_id
LEFT JOIN uom_uom uu ON uu.id=sm.product_uom
LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id
INNER JOIN stock_move_line sml ON sm.id=sml.move_id
where """ + where_start_date + """ and """ + where_end_date + """
-- AND sm.state = 'assigned' and qty_done = 0 and sm.raw_material_production_id is not null
AND sml.state = 'assigned' and sm.production_id is not null
AND sml.qty_done != 0
ORDER BY sm.id DESC
"""
list_data = []
company = self.env.user.company_id.name
start_date_format = start_date.strftime('%d/%m/%Y')
end_date_format = end_date.strftime('%d/%m/%Y')
self._cr.execute(query)
vals = self._cr.fetchall()
no = 1
for val in vals:
list_data.append({
'kode_barang': val[0],
'nama_barang': val[1],
'satuan': val[2],
'jumlah': val[3],
'keterangan': val[5]
})
no += 1
hasil = list_data
return hasil
def Report():
workbook = xlwt.Workbook()
staly_1 =xlwt.XFStyle()
font1 = xlwt.Font()
font1.name = u'宋体'
font1.bold = False
font1.colour_index = 0
staly_1.font = font1
sheet1=workbook.add_sheet(u'体检报告信息',cell_overwrite_ok=True)
sheet2=workbook.add_sheet(u'体检分科检查表',cell_overwrite_ok=True)
sheet3=workbook.add_sheet(u'体检检查项目表',cell_overwrite_ok=True)
sheet4=workbook.add_sheet(u'体检业务表',cell_overwrite_ok=True)
sheet5=workbook.add_sheet(u'体检结论表',cell_overwrite_ok=True)
sheet6=workbook.add_sheet(u'疾病诊断与阳性筛查',cell_overwrite_ok=True)
sheet1_data =[ u"序号",u"体检号",u"档案号",u"身份证",u"姓名",u"拼音首码",u"0男1女",u"出生日期",u"年龄"
,u"电子邮件",u"电话",u"登记日期",u"体检完成日期",u"综述",u"结论",u"建议",u"预约单号"] #体检报告信息
sheet1_loog = len(sheet1_data)
for a in range(len(sheet1_data)):
p = sheet1_data[a];
sheet1.write(0,a,sheet1_data[a],staly_1)
sheet2_data = [u"序号",u"体检号",u"科室编号",u"科室名称",u"科室类型",u"科室标准代码",u"科室小结",u"科室医生",
u"录入日期"]#体检分科检查表
sheet2_loog = len(sheet2_data)
for a in range(len(sheet2_data)):
p = sheet2_data[a];
sheet2.write(0,a,sheet2_data[a],staly_1)
sheet3_data = [u"序号",u"体检号",u"收费项目代码",u"科室编号",u"科室标准代码",u"检查项目编号",u"检查项目标准代码",
u"检查项目名称",u"检查项目单位",u"参考范围",u"检查结果描述",u"检验结果",u"数字结果",u"结果说明",u"排序"]#体检检查项目表
sheet3_loog = len(sheet3_data)
for a in range(len(sheet3_data)):
p = sheet3_data[a];
sheet3.write(0,a,sheet3_data[a],staly_1)
sheet4_data = [u"序号",u"体检号",u"收费项目代码",u"收费项目名称",u"科室标准代码",u"科室编码",u"检查医生名称",
u"检查日期"]#体检业务表
sheet4_loog = len(sheet4_data)
for a in range(len(sheet4_data)):
p = sheet4_data[a];
sheet4.write(0,a,sheet4_data[a],staly_1)
sheet5_data = [u"序号",u"体检号",u"科室编号",u"科室标准代码",u"结论词标准代码",u"结论编号",u"结论名称",]#体检结论表
sheet5_loog = len(sheet5_data)
for a in range(len(sheet5_data)):
p = sheet5_data[a];
sheet5.write(0,a,sheet5_data[a],staly_1)
sheet6_data = [u"序号",u"体检号",u"疾病诊断与阳性筛查",u"描述",u"阳性标准代码",u"阳性标准名称"]#疾病诊断与阳性筛查
sheet6_loog = len(sheet6_data)
for a in range(len(sheet6_data)):
p = sheet6_data[a];
sheet6.write(0,a,sheet6_data[a],staly_1)
# ————————————————————————————————内容——————————————————————————————
user = sql_user()
chickID =user[0] #体检报告号
user_name = user[1].decode('UTF-8')#体检报告姓名
print chickID,user_name
item_number = "00330001" #体检项目编号
item_name = user[1] #体检项目名
code = ident_generator()
print code
item_unit = "U/ml" #项目单位
result = '62' #项目值
sheet1_data =[ "1",chickID,chickID,code,user_name,"","1","19701216","24"
,"","18011111132","20170510","20170510",u"综述,综述综述","","","P121702050028"] #体检报告信息
sheet1_loog = len(sheet1_data)
for a in range(len(sheet1_data)):
p = sheet1_data[a];
sheet1.write(1,a,sheet1_data[a],staly_1)
sheet2_data = ["1",chickID,"0024",u"心电图","0","0024",u"******",u"刘大有",
"20170209"]#体检分科检查表
sheet2_loog = len(sheet2_data)
for a in range(len(sheet2_data)):
p = sheet2_data[a];
sheet2.write(1,a,sheet2_data[a],staly_1)
#------------------------------------------项目表-----------------------------------------
cur = sql_conn().cursor()
aa = cur.execute("select*from student")
for i in range(aa):
pp = cur.fetchone()
item_number = pp[1]
item_name = pp[2]
item_unit=pp[3]
result = '62' #项目值
code = ident_generator()
sheet3_data = ['1',chickID,u"A709","0024","0024",item_number,item_number,
item_name,'',"0-28","",item_unit,item_unit,"",""]#体检检查项目表
for a in range(len(sheet3_data)):
p = sheet3_data[a]
sheet3.write((i+1),a,sheet3_data[a],staly_1)
cur.close()
#------------------------------------------项目表-----------------------------------------
sheet4_data = ["1",chickID,u"A709",u"肝功1号",u"0024",u"0024",u"刘大有",
"20170210"]#体检业务表
sheet4_loog = len(sheet4_data)
for a in range(len(sheet4_data)):
p = sheet4_data[a];
sheet4.write(1,a,sheet4_data[a],staly_1)
sheet6_data = ["1",chickID,u"测试",u"建议：忌烟酒","G288"
,u""]#疾病诊断与阳性筛查
sheet6_loog = len(sheet6_data)
for a in range(len(sheet6_data)):
p = sheet6_data[a];
sheet6.write(1,a,sheet6_data[a],staly_1)
Room = "D:\\\\"+chickID+".xls"
workbook.save(Room)
# return Room,user_name,code
return Room
def savefile(outfilename, dataval):
wb = xlwt.Workbook()
ws = wb.add_sheet('SYN信息')
# title设置
titlestyle = xlwt.XFStyle()
# 设置字体
titlefont = xlwt.Font()
titlefont.name = 'SimSun'
titlefont.height = 20 * 11
titlestyle.font = titlefont
# 标题单元格对齐方式
titlealignment = xlwt.Alignment()
# 水平对齐方式和垂直对齐方式
titlealignment.horz = xlwt.Alignment.HORZ_CENTER
titlealignment.vert = xlwt.Alignment.VERT_CENTER
# 自动换行
titlealignment.wrap = 1
titlestyle.alignment = titlealignment
# 单元格背景设置
titlepattern = xlwt.Pattern()
titlepattern.pattern = xlwt.Pattern.SOLID_PATTERN
titlepattern.pattern_fore_colour = xlwt.Style.colour_map[
'sky_blue'] # 设置单元格背景颜色为蓝
titlestyle.pattern = titlepattern
# 单元格边框
titileborders = xlwt.Borders()
titileborders.left = 1
titileborders.right = 1
titileborders.top = 1
titileborders.bottom = 1
titileborders.left_colour = 0x40
titlestyle.borders = titileborders
# 设置标题
ws.write(0, 0, '序号', titlestyle)
ws.write(0, 1, '端口号', titlestyle)
ws.write(0, 2, 'IP地址', titlestyle)
ws.write(0, 3, '数量', titlestyle)
ws.write(0, 4, '动作', titlestyle)
ws.write(0, 5, '备注', titlestyle)
# 设置列高度
ws.row(0).height_mismatch = True
ws.row(0).height = int(20 * 40)
# 设置列宽度
ws.col(0).width = int(256 * 8)
ws.col(1).width = int(256 * 10)
ws.col(2).width = int(256 * 46)
ws.col(3).width = int(256 * 17)
ws.col(4).width = int(256 * 17)
ws.col(5).width = int(256 * 17)
contentstyle = xlwt.XFStyle()
contentalignment = xlwt.Alignment()
contentalignment.wrap = 1
# 水平对齐方式和垂直对齐方式
contentalignment.horz = xlwt.Alignment.HORZ_CENTER
contentalignment.vert = xlwt.Alignment.VERT_CENTER
contentstyle.alignment = contentalignment
# 写入数据
for i in range(len(result)):
serials = i + 1
ws.write(serials, 0, str(serials), contentstyle)
ws.write(serials, 1, dataval[i][0], contentstyle)
ws.write(serials, 2, dataval[i][1], contentstyle)
ws.write(serials, 3, dataval[i][2], contentstyle)
ws.write(serials, 4, '开启', contentstyle)
# 保存excel文件
wb.save(outfilename)
def print_report(self):
vals = {}
if self.non_moving_product_days <= 0:
raise Warning(_("Please Enter Days To Proceed."))
final_non_moving_data = {'days': self.non_moving_product_days}
warehouse_ids_list = self.warehouse_ids or self.env[
'stock.warehouse'].search([])
sale_category_ids_list = self.sale_category_ids or self.env[
'product.category'].search([])
if self.warehouse_ids:
warehouse_name = ", ".join(
[each.name for each in self.warehouse_ids])
final_non_moving_data.update({'warehouse': warehouse_name})
LocationObj = self.env['stock.location'].sudo()
customer_location_ids = LocationObj.search([('usage', '=', 'customer')
]).ids
for each in warehouse_ids_list:
location_ids = LocationObj.search([('usage', '=', 'internal'),
('location_id', 'child_of',
each.lot_stock_id.id)])
query = """
select product_id,location_id from stock_move
where location_id in %s and
state = 'done'
and date >= '%s 00:00:00' and date <= '%s 23:59:59'
and location_dest_id in %s
""" % ("(%s)" % ",".join(map(str, location_ids.ids)),
(date.today() - timedelta(days=self.non_moving_product_days)
), date.today(),
"(%s)" % ",".join(map(str, customer_location_ids)))
self._cr.execute(query)
stocK_move_data = self._cr.dictfetchall()
product_list = [each.get('product_id') for each in stocK_move_data]
stock_quant_query = '''
select sq.product_id,pt.default_code,sl.id,pt.name,pc.name as category,sq.quantity,sl.complete_name from stock_quant sq
LEFT JOIN stock_location sl on sl.id = sq.location_id
LEFT JOIN product_product pp on pp.id = sq.product_id
LEFT JOIN product_template pt on pt.id = pp.product_tmpl_id
LEFT JOIN product_category pc on pc.id = pt.categ_id
where pt.categ_id in %s
and sq.location_id in %s
and sq.product_id %s
''' % ("(%s)" %
",".join(map(str, sale_category_ids_list.ids)),
"(%s)" % ",".join(map(str, location_ids.ids)),
"not in (%s)" % ",".join(map(str, product_list))
if product_list else "is not null")
self._cr.execute(stock_quant_query)
stock_quant_data = self._cr.dictfetchall()
productobj = self.env['product.product']
if self.group_by_category:
for data in stock_quant_data:
product = productobj.browse(data.get('product_id'))
data.update({
'sell_price': product.list_price,
'unit_price': product.standard_price
})
if data.get('category') not in vals:
vals[data.get('category')] = [data]
else:
vals[data.get('category')].append(data)
else:
for data in stock_quant_data:
product = productobj.browse(data.get('product_id'))
data.update({
'sell_price': product.list_price,
'unit_price': product.standard_price
})
if each.id not in vals:
vals[each.id] = [data]
else:
vals[each.id].append(data)
vals = OrderedDict(sorted(vals.items(), key=lambda t: t[0]))
final_non_moving_data.update({
'data':
vals,
'group_by':
self.group_by_category,
'currency':
self.env.user.company_id.currency_id.symbol,
'current_date':
date.today().strftime('%d/%m/%Y')
})
if not vals:
raise Warning(_('NO Stock Found'))
if self.report_type == 'pdf':
if self.env.context.get('send_mail'):
final_non_moving_data.update({'break': False})
vals_new = {'products': final_non_moving_data}
template_id = self.env.ref(
'flexiretail_com_advance.non_moving_stock_report')
pdf = template_id.render_qweb_pdf(self, data=vals_new)
values = base64.b64encode(pdf[0])
attachment_id = self.env['ir.attachment'].sudo().create({
'datas':
values,
'name':
'Non Moving Stock Report',
'datas_fname':
'Non Moving Stock.pdf'
})
return attachment_id
else:
final_non_moving_data.update({'break': True})
vals_new = {'products': final_non_moving_data}
return self.env.ref(
'flexiretail_com_advance.non_moving_stock_report'
).report_action(self, data=vals_new)
elif self.report_type == 'xls':
workbook = xlwt.Workbook()
stylePC = xlwt.XFStyle()
worksheet = workbook.add_sheet('Non Moving Stock Report')
bold = xlwt.easyxf(
"font: bold on; pattern: pattern solid, fore_colour gray25;")
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
stylePC.alignment = alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment_num = xlwt.Alignment()
alignment_num.horz = xlwt.Alignment.HORZ_RIGHT
horz_style = xlwt.XFStyle()
horz_style.alignment = alignment_num
align_num = xlwt.Alignment()
align_num.horz = xlwt.Alignment.HORZ_RIGHT
horz_style_pc = xlwt.XFStyle()
horz_style_pc.alignment = alignment_num
style1 = horz_style
font = xlwt.Font()
font1 = xlwt.Font()
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
font.bold = True
font1.bold = True
font.height = 500
stylePC.font = font
style1.font = font1
stylePC.alignment = alignment
pattern = xlwt.Pattern()
pattern1 = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['gray25']
pattern1.pattern_fore_colour = xlwt.Style.colour_map['gray25']
stylePC.pattern = pattern
style1.pattern = pattern
worksheet.write_merge(0,
1,
0,
5,
'Non Moving Stock Report',
style=stylePC)
worksheet.col(2).width = 5600
worksheet.write_merge(
3, 3, 0, 2, 'Non Moving Product in Last ' +
str(self.non_moving_product_days) + ' Days', bold)
row = 5
if self.warehouse_ids:
worksheet.write(row, 0, 'Warehouses', bold)
worksheet.write_merge(row, row, 1, 2, warehouse_name)
row = row + 2
list1 = [
'Default Code', 'Product', 'Location', 'Quantity',
'Cost Price', 'Sale Price'
]
if self.group_by_category == True:
for j in vals:
worksheet.write(row, 0, 'Category', bold)
worksheet.write(row, 1, j)
row = row + 1
for i in range(len(list1)):
if list1[i] in ['Cost Price', 'Sale Price']:
worksheet.write(
row, i, list1[i] + "(" +
self.env.user.company_id.currency_id.symbol +
")", style1)
elif list1[i] == 'Quantity':
worksheet.write(row, i, list1[i], style1)
else:
worksheet.write(row, i, list1[i], bold)
row = row + 1
for dict in vals.get(j):
worksheet.col(0).width = 4000
worksheet.write(row, 0, dict.get('default_code'))
worksheet.col(1).width = 6000
worksheet.write(row, 1, dict.get('name'))
worksheet.col(2).width = 8000
worksheet.write(row, 2, dict.get('complete_name'))
worksheet.write(row, 3, dict.get('quantity'),
horz_style_pc)
worksheet.col(4).width = 4500
worksheet.write(
row, 4,
str('%.2f' % (dict.get('unit_price'))) +
str(self.env.user.company_id.currency_id.symbol),
horz_style_pc)
worksheet.col(5).width = 4500
worksheet.write(
row, 5,
str('%.2f' % (dict.get('sell_price'))) +
str(self.env.user.company_id.currency_id.symbol),
horz_style_pc)
row = row + 1
row = row + 1
else:
for i in range(len(list1)):
if list1[i] in ['Cost Price', 'Sale Price']:
worksheet.write(
row, i, list1[i] + "(" +
self.env.user.company_id.currency_id.symbol + ")",
style1)
elif list1[i] == 'Quantity':
worksheet.write(row, i, list1[i], style1)
else:
worksheet.write(row, i, list1[i], bold)
row = row + 1
for j in vals:
for dict in vals.get(j):
worksheet.col(0).width = 4000
worksheet.write(row, 0, dict.get('default_code'))
worksheet.col(1).width = 6000
worksheet.write(row, 1, dict.get('name'))
worksheet.col(2).width = 8000
worksheet.write(row, 2, dict.get('complete_name'))
worksheet.write(row, 3, dict.get('quantity'),
horz_style_pc)
worksheet.col(4).width = 4500
worksheet.write(
row, 4,
str('%.2f' % (dict.get('unit_price'))) +
str(self.env.user.company_id.currency_id.symbol),
horz_style_pc)
worksheet.col(5).width = 4500
worksheet.write(
row, 5,
str('%.2f' % (dict.get('sell_price'))) +
str(self.env.user.company_id.currency_id.symbol),
horz_style_pc)
row = row + 1
file_data = BytesIO()
workbook.save(file_data)
self.write({
'data':
base64.encodestring(file_data.getvalue()),
'file_name':
'Report - %s.xls' %
(datetime.now().strftime('%Y-%m-%d %H:%M:%S')),
'state':
'done'
})
attachment_id = self.env['ir.attachment'].sudo().create({
'datas':
self.data,
'name':
self.file_name,
'datas_fname':
'Non Moving Stock.xls'
})
if self.env.context.get('send_mail'):
return attachment_id
else:
return {
'name': ('Non Moving Stock'),
'view_type': 'form',
'view_mode': 'form',
'res_model': 'non.moving.stock',
'res_id': self.id,
'target': 'new',
'type': 'ir.actions.act_window',
}
def __init__(self):
font0 = xlwt.Font()
font0.name = 'Times New Roman'
font0.colour_index = 2
font0.height = 400
font0.bold = True
self.font0 = font0
style0 = xlwt.XFStyle()
style0.font = font0
self.style0 = style0
font1 = xlwt.Font()
font1.name = 'Times New Roman'
font1.height = 300
self.font1 = font1
style1 = xlwt.XFStyle()
style1.font = font1
self.style1 = style1
self.date = datetime.date.today().strftime("%d.%m.%Y")
# Если таблица была до этого
if os.path.exists(constants.dir):
self.users = []
self.tags = []
self.ids = []
rb = xlrd.open_workbook(constants.dir, formatting_info=True)
sheet = rb.sheet_by_index(0)
for tag in sheet.row_values(0):
self.tags.append(tag)
# Считываем данные из таблицы
lis = self.tags
for i in range(1, sheet.nrows):
self.ids.append(int(sheet.row_values(i)[1]))
j = 0
user = {}
for col in sheet.row_values(i):
l = lis[j]
user[l] = col
j += 1
self.users.append(user)
# Преобразование тегов
self.tags = self.tags[7:-1]
for tag in constants.key_words:
if not tag in self.tags:
self.tags.append(tag)
self.wb = xlwt.Workbook()
ws = self.wb.add_sheet('Users', cell_overwrite_ok=True)
ws.write(0, 0, "Дата", style0)
ws.write(0, 1, "ID", style0)
ws.write(0, 2, "Имя", style0)
ws.write(0, 3, "Фамилия", style0)
ws.write(0, 4, "Телефон", style0)
ws.write(0, 5, "Дата рождения", style0)
ws.write(0, 6, "Город", style0)
i = 7
if os.path.exists(constants.dir):
for tag in self.tags:
ws.write(0, i, tag, style0)
i += 1
else:
for tag in constants.key_words:
ws.write(0, i, tag, style0)
i += 1
ws.write(0, i, "Статус 1 или 0", style0)
self.ws = ws
#保存每个用户的最小值。
while (j > 0):
maxAttendanceDate.setdefault(table.cell(j, 3).value.encode("utf-8")+(str(datetime.datetime.fromtimestamp(((table.cell(j, 8).value)-70*365-19)*86400-8*3600))).split(" ")[0],datetime.datetime.fromtimestamp(((table.cell(j, 8).value)-70*365-19)*86400-8*3600));
j = j -1;
"""
设置Excel格式。
"""
newWorkbook = xlwt.Workbook();
newTable = newWorkbook.add_sheet('Attendance'.decode(),cell_overwrite_ok=True);
# 表头字体样式以及大小。
headStyle = xlwt.XFStyle();
headFont = xlwt.Font();
headFont.bold = True;
headFont.height = 360;
headFont.name = u'微软雅黑';
headStyle.font = headFont;
# 表格内容样式以及大小。
style = xlwt.XFStyle();
font = xlwt.Font();
font.height = 300;
style.font = font;
# 表格内时间数据格式。
style = xlwt.XFStyle();
style.num_format_str = 'yyyy/m/d h:mm AM/PM'
