def output(file_or_stream, sheetname, headers, values, encoding = 'utf8', footer_text = None, footer_link = None):
import xlwt
book = xlwt.Workbook(encoding)
sh = book.add_sheet(sheetname)
datestyle = XFStyle()
datestyle.num_format_str = 'DD/MM/YYYY'
timestyle = XFStyle()
timestyle.num_format_str = 'HH:MM:SS'
header_font = Font()
header_font.bold = True
al = Alignment()
al.horz = Alignment.HORZ_CENTER
header_style = XFStyle()
header_style.font = header_font
header_style.alignment = al
for i,header in enumerate(headers):
sh.write(0, i, header, header_style)
sh.set_panes_frozen(True) # frozen headings instead of split panes
sh.set_horz_split_pos(1) # in general, freeze after last heading row
sh.set_remove_splits(True) # if user does unfreeze, don't leave a split there
for j, row in enumerate(values):
for i, value in enumerate(row):
if value.__class__ == date:
sh.write(j+1, i, value, datestyle)
elif value.__class__ == time:
sh.write(j+1, i, value, timestyle)
else:
sh.write(j+1, i, value)
if footer_link and footer_text:
link_font = Font()
link_font.name = 'Verdana'
link_font.colour_index = 4
link_font.height = 20*8
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_BOTTOM
link_style = XFStyle()
link_style.font = link_font
link_style.alignment = al
row = len(values) + 1
sh.write_merge(row, row, 0, len(headers)-1, Formula('HYPERLINK("' + footer_link + '";"' + footer_text + '")'), link_style)
book.save(file_or_stream)
def get_style(
font_specs=dict(),
border_specs=dict(),
pattern=None,
num_format_str=None,
):
style_key = (freeze(font_specs), freeze(border_specs), pattern,
num_format_str)
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr(style.font, key, value)
style.borders = Borders()
for key, value in border_specs.items():
setattr(style.borders, key, value)
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[style_key] = style
return style
def RUNN():
# ghi file excel
fnt = Font()
fnt.name = "Times New Roman"
fnt.height = 280
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.font = fnt
style.borders = borders
wb = Workbook()
sheet1 = wb.add_sheet("Điểm")
col = 0
row = 0
sheet1.write(row, col, "STT", style)
sheet1.write(row, col + 1, "TÊN BÀI LÀM", style)
sheet1.write(row, col + 2, "ĐIỂM", style)
row += 1
diem = []
st = 1
for i in t:
if i == name_form: continue
else:
try:
t1 = docx.Document(i)
sheet1.write(row, col, st, style)
sheet1.write(row, col + 1, i[:-5], style)
sheet1.write(row, col + 2, round(10 * so_sanh(t1), 2), style)
row += 1
st += 1
except Exception: pass
wb.save("KetQua.xls")
def export_to_excel(self, **kwargs):
work_book = Workbook(encoding="ascii")
center_alignment = Alignment()
center_alignment.horz = Alignment.HORZ_RIGHT
center_alignment.vert = Alignment.VERT_CENTER
border = Borders()
border.top = Borders.THIN
border.left = Borders.THIN
border.bottom = Borders.THIN
border.right = Borders.THIN
head_style = XFStyle()
head_pattern = Pattern()
head_pattern.pattern = Pattern.SOLID_PATTERN
head_pattern.pattern_fore_colour = Style.colour_map['gray25']
head_style.pattern = head_pattern
head_font = Font()
head_font.bold = True
head_style.font = head_font
head_style.alignment = center_alignment
head_style.borders = border
data_style = XFStyle()
data_style.alignment = center_alignment
data_style.borders = border
work_sheet = work_book.add_sheet("Sheet")
limit = 10000
count = self.get_data_scale(**kwargs)['count']
times = (count + limit - 1) // limit
add_header = False
for i in range(1):
kwargs["startIndex"] = i * limit + 1
kwargs['endIndex'] = (i + 1) * limit + 1
records = self.get_data(**kwargs)
column_names = []
y = 0
for data in records:
if not add_header:
x = 0
for column_name in data.keys():
work_sheet.write(y, x, column_name, head_style)
column_names.append(column_name)
x += 1
add_header = True
y += 1
x = 0
for column_name in column_names:
work_sheet.write(y, x, data[column_name], data_style)
x += 1
y += 1
x = 0
work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
def css2excel(css):
#custom_css = 'font: name "%s", %s on'%(current_value['font-family'].split(",")[0], current_value['font-weight'].split(",")[0])
#export_sheet.write(row_idx, col_idx, current_value['data'], xlwt.easyxf('font: italic on; pattern: pattern solid, fore-colour grey25'))
xf_list = []
fnt = Font()
borders = Borders()
pattern = Pattern()
align = Alignment()
process_css = {
'font-family' : [fnt, "name" , lambda x : x.split(",")[0]],
'color' : [fnt, "colour_index", lambda x : excel_color_map.get(x,0)+8],
'font-weight' : [fnt, "bold", lambda x : x.upper() == 'BOLD'],
#'font-weight' : [fnt, "bold", lambda x : x == '700'],
'text-align' : [align, "horz", lambda x : {'left':align.HORZ_LEFT, 'right':align.HORZ_RIGHT, 'center':align.HORZ_CENTER, 'justified': align.HORZ_JUSTIFIED}[x]],
'background-color' : [pattern,"pattern_fore_colour", lambda x: excel_color_map.get(x,16)+8],
}
#TODO process_css -> css
for i in process_css.keys():
#print process_css[i][0] ,".",process_css[i][1], " => " , css[i] ," | ", process_css[i][2](css[i])
setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i]))
style = XFStyle()
style.font = fnt
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style.borders = borders
style.pattern = pattern
style.pattern.pattern = 1
style.alignment = align
return style
def set_style(self, isblod):
style = XFStyle()
fnt = Font() # 创建一个文本格式，包括字体、字号和颜色样式特性
fnt.name = u'微软雅黑' # 设置其字体为微软雅黑
fnt.bold = isblod
style.font = fnt
return style
def defineFontStyle(argFontName,argIsBold):
style = XFStyle()
font = Font()
font.name = argFontName
font.bold = argIsBold
style.font = font
return style
def get_style( font_specs=dict(),
border_specs = dict(),
pattern = None,
num_format_str = None, ):
style_key = ( freeze(font_specs),
freeze(border_specs),
pattern,
num_format_str )
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr( style.font, key, value )
style.borders = Borders()
for key, value in border_specs.items():
setattr( style.borders, key, value )
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[ style_key ] = style
return style
def write_result(self, row, actualresult, result,
casefile): # 文件不用写全路径，只写文件名只可
self.lock.acquire()
case_path = os.path.join(CASEPATH, casefile)
print(case_path)
book = xlrd.open_workbook(case_path) # 创建一个excel操作对象
book2 = copy(
book
) #复制book对象 #管道作用:利用xlutils.copy函数，将xlrd.Book转为xlwt.Workbook，再用xlwt模块进行存储
sheet = book2.get_sheet(0)
# 创建一个sheet操作实例,读取的是第一个excel ,#通过get_sheet()获取的sheet有write()方法
# 红色
# styleBlueBkg = xlwt.easyxf('pattern: pattern solid, fore_colour red;')
style = XFStyle() # 格式信息
font = xlwt.Font() # 字体基本设置
# 红色
font.colour_index = 0xff
style.font = font
sheet.write(row, 11, actualresult)
# 回写时，如果是失败，则颜色标成红色
# if result=='0':
# # sheet.write(row, 12, result,styleBlueBkg)
# sheet.write(row, 12, result,style)
# else:
# sheet.write(row, 12, result)
sheet.write(row, 12, result)
# sheet.write(row, 13, user)
book2.save(case_path)
print('结果写入保存成功！')
self.lock.release()
def get_report_info_perid(
self,
cmd,
diff_time,
thre_time,
max_number,
):
# 发送要执行的命令
pre_time_stamp = [0] * 4
self._channel.send(cmd + '\r')
# 回显很长的命令可能执行较久，通过循环分批次取回回显
index = [0] * 4
line_counter = 0
line_feed_byte = '\n'.encode(self.encoding)
while True:
buffer = self._channel.recv(65535)
if len(buffer) == 0:
logger.info('end______________')
break
lines = buffer.split(line_feed_byte)
for current_line in lines:
style = None
line = current_line.decode(self.encoding)
# logger.debug('shell显示：%s' % line)
col = self.check_type(line)
if not line.startswith(self.rq) or col == 10:
line_counter += 1
continue
time_stamp = int(
time.mktime(
time.strptime(' '.join([line[:8], line[9:17]]),
"%Y%m%d %H:%M:%S")))
time_stamp_dec = line[18:21] # 精确到毫秒
time_stamp = time_stamp * 1000 + int(time_stamp_dec)
logger.info('%s:%s' % (senior_name[col], time_stamp))
if pre_time_stamp[col] == 0:
pre_time_stamp[col] = time_stamp
else:
if abs((time_stamp - pre_time_stamp[col]) -
diff_time[col]) > thre_time[col]:
logger.error('两帧数据间隔为{}ms,时间戳分别为:({},{}),行号：{}'.format(
time_stamp - pre_time_stamp[col], time_stamp,
pre_time_stamp[col], index[col]))
style = XFStyle()
fnt = Font()
fnt.name = u'微软雅黑' # 设置其字体为微软雅黑
fnt.colour_index = 2 # 设置其字体颜色
fnt.bold = True
style.font = fnt
self.write_xl(index[col] + 1, col * 2, time_stamp)
self.write_xl(index[col] + 1,
col * 2 + 1,
time_stamp - pre_time_stamp[col],
style=style)
index[col] += 1
pre_time_stamp[col] = time_stamp
line_counter += 1
def build_certificate_excel(queryset):
workbook = xlwt.Workbook()
sheet = workbook.add_sheet("Сертификаты")
title_style = XFStyle()
borders = Borders()
borders.bottom = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.left = Borders.MEDIUM
title_style.borders = borders
title_style.font = easyfont(f"bold on, height {12*20};")
font_style = XFStyle()
font_style.font = easyfont(f"height {12*20};")
sheet.write(0, 1, "ФИО", title_style)
sheet.write(0, 2, "№ Договора", title_style)
sheet.write(0, 3, "ИНН", title_style)
sheet.write(0, 4, "День", title_style)
sheet.write(0, 5, "Месяц", title_style)
sheet.write(0, 6, "Год", title_style)
sheet.write(0, 7, "Сертификат №", title_style)
for r, obj in enumerate(queryset):
n = r + 1
sheet.write(n, 0, str(n), font_style)
sheet.write(n, 1, str(obj.full_name), font_style)
sheet.write(n, 2, str(obj.contract_n), font_style)
sheet.write(n, 3, str(obj.inn), font_style)
sheet.write(n, 4, str(obj.date_received.year), font_style)
sheet.write(n, 5, str(obj.date_received.month), font_style)
sheet.write(n, 6, str(obj.date_received.day), font_style)
sheet.write(n, 7, str(obj.certificate_n), font_style)
sheet.col(0).width = 1400
sheet.col(1).width = 14000
sheet.col(2).width = 4500
sheet.col(3).width = 4000
sheet.col(7).width = 5500
file = BytesIO()
workbook.save(file)
return file
def Style1():
style = XFStyle()
fnt = Font()
fnt.name = u'微软雅黑'
fnt.bold = True
style.font = fnt
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment # 给样式添加文字居中属性
style.font.height = 430 # 设置字体大小
return style
def excelWriter(self, filename):
workbook1 = xlwt.Workbook()
sheet1 = workbook1.add_sheet('表01')
sheet1.write(0, 0, 'Unformated value')
sheet1.write(0, 1, '大家好')
# 设置字体样式
font1 = Font()
font1.name = '华文琥珀'
style = XFStyle()
style.font = font1
sheet1.write(1, 0, 'Formated value', style)
workbook1.save(filename)
def writLabel(self):
"""向excel写入内容，内容自定义"""
style = XFStyle() # 初始化样式
font = Font() # 为样式创建字体
font.name = 'Times New Roman'
# font.bold = True # 黑体
# font.underline = True # 下划线
# font.italic = True # 斜体字
style.font = font # 设定样式
for i in range(10):
for j in range(5):
self.excel_w_sheet.write(i,j, label = 'admin%d%d'%(i,j))# 参数对应 行, 列, 值
self.excel_w.save('Excelw.xls')
def write_file(headers, data):
wb = Workbook()
font = Font()
style = XFStyle()
page = wb.add_sheet('All Data')
for i in range(len(data)):
font.bold = True
style.font = font
page.write(0, i, headers[i], style=style)
for j in range(len(data[i])):
page.write(j + 1, i, data[i][j])
wb.save('info/misc/rating_plot.xls')
def write_file(headers, data):
wb = Workbook()
font = Font()
style = XFStyle()
page = wb.add_sheet('All Data')
for i in range(len(data)):
font.bold = True
style.font = font
page.write(0, i, headers[i], style=style)
for j in range(len(data[0])):
page.write(j + 1, i, data[i][j])
wb.save(search_excel_file)
def def_style():
style = XFStyle()
font = Font() # 这部分设置字体
font.name = 'Times New Roman' # 或者换成外面传进来的参数，这样可以使一个函数定义所有style
# font.bold = 'True'
# font.height = 24
# font.size = 2000
# font.colour_index = 3
style.font = font
alignment = Alignment() # 这部分设置居中格式
alignment.horz = Alignment.HORZ_CENTER # 水平居中
alignment.vert = Alignment.VERT_CENTER # 垂直居中
style.alignment = alignment
return style
def get_style():
fnt = Font()
fnt.bold = True
bor = Borders()
bor.top = 1
bor.right = 1
bor.bottom = 1
bor.left = 1
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
style = XFStyle()
style.font = fnt
style.borders = bor
style.alignment = al
return style
def merge_styles(row_style, col_style, default_style=easyxf('')):
"""Merges row and column style.
Method tries to get "strongest" style feauters from col (row) style
and replicate it to row (col) style.
Alas, it doesn't stable yet.
"""
if (row_style, col_style) in styles_cache:
new_style = styles_cache[row_style, col_style]
else:
new_style = XFStyle()
# Merge borders
new_style.borders.top = row_style.borders.top if row_style.borders.top > col_style.borders.top \
else col_style.borders.top
new_style.borders.left = row_style.borders.left if row_style.borders.left > col_style.borders.left \
else col_style.borders.left
new_style.borders.right = row_style.borders.right if row_style.borders.right > col_style.borders.right \
else col_style.borders.right
new_style.borders.bottom = row_style.borders.bottom \
if row_style.borders.bottom > col_style.borders.bottom else col_style.borders.bottom
# Merge pattern
if default_style.pattern.pattern == row_style.pattern.pattern:
new_style.pattern.pattern = col_style.pattern.pattern
else:
new_style.pattern.pattern = row_style.pattern.pattern
if default_style.pattern.pattern_fore_colour == row_style.pattern.pattern_fore_colour:
new_style.pattern.pattern_fore_colour = col_style.pattern.pattern_fore_colour
else:
new_style.pattern.pattern_fore_colour = row_style.pattern.pattern_fore_colour
if default_style.pattern.pattern_back_colour == row_style.pattern.pattern_back_colour:
new_style.pattern.pattern_back_colour = col_style.pattern.pattern_back_colour
else:
new_style.pattern.pattern_back_colour = row_style.pattern.pattern_back_colour
# Merge font
new_style.font = merge_fonts(row_style.font, col_style.font, default_style.font)
styles_cache[row_style, col_style] = new_style
return new_style
def _getStyle(self, org_style, org_font):
from xlwt import XFStyle, Font, Borders, Pattern
# font
font = Font()
font.name = org_font.name
font.height = org_font.height
font.italic = org_font.italic
font.struck_out = org_font.struck_out
font.outline = org_font.outline
font.shadow = org_font.shadow
font.colour_index = org_font.colour_index
font.bold = org_font.bold
font._weight = org_font.weight
font.escapement = org_font.escapement_type
font.underline = org_font.underline_type
font.family = org_font.family
font.charset = org_font.character_set
# border
borders = Borders()
borders.left = Borders.THIN #org_style.xf.border.left_line_style
borders.right = Borders.THIN #org_style.xf.border.right_line_style
borders.top = Borders.THIN #org_style.xf.border.top_line_style
borders.bottom = Borders.THIN #org_style.xf.border.bottom_line_style
#borders.diag = self.NO_LINE
borders.left_colour = org_style.xf.border.left_colour_index
borders.right_colour = org_style.xf.border.right_colour_index
borders.top_colour = org_style.xf.border.top_colour_index
borders.bottom_colour = org_style.xf.border.bottom_colour_index
#borders.diag_colour = org_style.xf.border.left_colour_index
#borders.need_diag1 = self.NO_NEED_DIAG1
#borders.need_diag2 = self.NO_NEED_DIAG2
# Pattern
pattern = Pattern()
pattern.pattern = org_style.xf.background.fill_pattern
pattern.pattern_fore_colour = org_style.xf.background.pattern_colour_index
pattern.pattern_back_colour = org_style.xf.background.background_colour_index
#
style = XFStyle()
style.borders = borders
style.font = font
style.pattern = pattern
return style
def estiloCeldasSeparadas():
fnt = Font()
fnt.bold=True
#fnt.colour_index=2
borders = Borders()
borders.left = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.top = Borders.MEDIUM
borders.bottom = Borders.MEDIUM
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x18#0x99
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
return style
def create_act(self):
template = open_workbook(self.template, formatting_info=1)
new_file = copy(template)
sheet = new_file.get_sheet(0)
for cell in cells_conf:
print('Coздаем ячейку {}'.format(cell['name']))
style = XFStyle()
font = Font()
style.alignment.horz = cell['align_horz']
font.name = cell['fontname']
font.bold = cell['fontbold']
font.height = cell['fontsize']
style.font = font
name = cell['name']
sheet.write(cell['row'], cell['col'], self._get_str(name), style)
new_file.save(self.path)
def css2excel(css):
fnt = Font()
borders = Borders()
pattern = Pattern()
align = Alignment()
process_css = {
'font-family': [fnt, "name", lambda x: x.split(",")[0]],
'font-size': [fnt, "height", lambda x: get_font_height(x.upper())],
'color': [fnt, "colour_index", lambda x: match_color_index(x.upper())],
'font-weight': [fnt, "bold", lambda x: x == 'bold'],
'font-style': [fnt, "italic", lambda x: x == 'italic'],
'text-align':
[align, "horz", lambda x: get_horizontal_align(x, align)],
'vertical-align':
[align, "vert", lambda x: get_vertical_align(x, align)],
'background-color': [
pattern, "pattern_fore_colour",
lambda x: match_color_index(x.upper())
],
}
for i in css.keys():
if i in process_css.keys():
setattr(process_css[i][0], process_css[i][1],
process_css[i][2](css[i].strip()))
style = XFStyle()
style.font = fnt
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style.borders = borders
style.pattern = pattern
style.pattern.pattern = 1
style.alignment = align
style.num_format_str = '#,##0.00'
return style
def get_sheet_title_style(bg_color=0x39,
font_color=0x0,
font_size=300,
has_pattern=True,
horz_center=True):
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = font_color
fnt.bold = True
fnt.height = font_size
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
al = Alignment()
if horz_center:
al.horz = Alignment.HORZ_CENTER
else:
al.horz = Alignment.HORZ_LEFT
al.vert = Alignment.VERT_CENTER
pattern = None
if has_pattern:
pattern = Pattern()
pattern.pattern = 1
pattern.pattern_fore_colour = bg_color
pattern.pattern_back_colour = bg_color
style = XFStyle()
style.font = fnt
style.borders = borders
style.alignment = al
if pattern:
style.pattern = pattern
return style
def creaHojaDeTraduccion(excel, nombreHoja="traduccion"):
hoja=excel.creaHoja(nombreHoja)
hoja.col(0).width=90*256
hoja.col(1).width=20*256
fnt = Font()
fnt.bold=True
#fnt.colour_index=2
borders = Borders()
borders.left = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.top = Borders.MEDIUM
borders.bottom = Borders.MEDIUM
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x18#0x99
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
hoja.write(0,0,"Original Text",style)
hoja.write(0,1,"Translation",style)
return hoja
def export(match_list, file_path):
wb = xlwt.Workbook()
ws = wb.add_sheet('Ranking')
# writing header
header = ['Ranking',
'Name',
'Rating',
'Reviews',
'Price',
'Cuisines',
'Edenred Name',
'Match',
'Edenred Address']
font = Font()
font.name = 'Arial'
font.bold = True
style = XFStyle()
style.font = font
for index in range(len(header)):
ws.write(0, index, header[index], style)
# writing restaurants
for index in range(len(match_list)):
match = match_list[index]
ws.write(index+1, 0, int(match.restaurant1.ranking))
ws.write(index+1, 1, xlwt.Formula('HYPERLINK("%s"; "%s")' % (match.restaurant1.url, match.restaurant1.name)))
ws.write(index+1, 2, float(match.restaurant1.rating.replace(',', '.')))
ws.write(index+1, 3, int(match.restaurant1.review_count))
ws.write(index+1, 4, match.restaurant1.price)
ws.write(index+1, 5, ', '.join(match.restaurant1.cuisines))
ws.write(index+1, 6, match.restaurant2.name)
ws.write(index+1, 7, int(100.0 * match.score))
address = match.restaurant2.address + ', ' + match.restaurant2.postcode
map_link = 'https://www.google.com/maps?f=q&source=s_q&hl=es&q=%s' % address
ws.write(index+1, 8, xlwt.Formula('HYPERLINK("%s"; "%s")' % (map_link, address)))
wb.save(file_path)
def main():
lst = []
path = r'./Homework/03/'
with open(path + 'test.txt', 'r') as fpin:
title = fpin.readline().strip().replace('\n', '').split(',')
while True:
s = fpin.readline().strip().replace('\n', '').split(',')
if s == ['']:
break
lst.append(s)
fpin.close()
book = Workbook()
sheet1 = book.add_sheet('data')
style = XFStyle()
font = Font()
font.name = '宋体'
font.height = 280
style.font = font
alignment = Alignment()
alignment.horz = Alignment.HORZ_CENTER
alignment.vert = Alignment.VERT_CENTER
style.alignment = alignment
row = sheet1.row(0)
for i, j in zip(range(len(title)), title):
row.write(i, j, style=style)
for i, j in zip(range(1, len(lst) + 1), lst):
row = sheet1.row(i)
for x, y in zip(range(len(j)), j):
row.write(x, y, style=style)
book.save(path + 'data.xls')
def in_ket_qua():
# tao file ketqua.xls de ghi ket qua
fnt = Font()
fnt.name = "Times New Roman"
fnt.height = 280
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.font = fnt
style.borders = borders
wb = Workbook()
sheet1 = wb.add_sheet("Điểm")
col = 0
row = 0
sheet1.write(row, col, "STT", style)
sheet1.write(row, col + 1, "TÊN BÀI LÀM", style)
sheet1.write(row, col + 2, "ĐIỂM", style)
row += 1
st = 1
# Tinh ket qua cua tat ca cac file bai lam va in ket qua ra file KetQua.xls
for name in list_file_word:
sheet1.write(row, col, st, style)
sheet1.write(row, col + 1, name[:-5], style)
sheet1.write(row, col + 2, tinhKetQua(name), style)
row += 1
st += 1
print(name, "___ĐÃ CHẤM XONG___")
# Xoa bo cac file anh sau khi da xu li xong
for name in glob.glob("*.png"):os.remove(name)
# Luu ket qua ra file
os.chdir(local)
# shutil.rmtree('Temp')
wb.save("KetQua.xls")
def output(self):
# from xlrd import open_workbook
from xlwt import Workbook, XFStyle, Borders, Alignment, Font, Pattern, Style, easyxf
# from xlutils.copy import copy
"""
rb = open_workbook(r"Templates\template.xls")
wb = copy(rb)
s = wb.get_sheet(0)
s.write(0, 0, 'A1')
wb.save(r"C:\Temp\nephro-planner\new.xls")
"""
book = Workbook(encoding="utf-8")
sheet = book.add_sheet(r"Feuille1")
style_title = XFStyle()
font_title = Font()
font_title.name = "Comic Sans MS"
font_title.height = 280
style_title.font = font_title
style_title.alignment.horz = Alignment.HORZ_CENTER
style_title.alignment.vert = Alignment.VERT_CENTER
style_cell_bottom = XFStyle()
borders_cell_bottom = Borders()
borders_cell_bottom.bottom = Borders.MEDIUM
style_cell_bottom.borders = borders_cell_bottom
style_header = XFStyle()
font_header = Font()
font_header.bold = 1
font_header.name = "Arial Narrow"
font_header.height = 240
style_header.font = font_header
style_header.alignment.horz = Alignment.HORZ_CENTER
style_header.alignment.vert = Alignment.VERT_CENTER
borders_header = Borders()
borders_header.top = Borders.MEDIUM
borders_header.left = Borders.MEDIUM
borders_header.bottom = Borders.MEDIUM
borders_header.right = Borders.MEDIUM
style_header.borders = borders_header
style_sub_header = XFStyle()
font_sub_header = Font()
font_sub_header.name = "Arial Narrow"
font_sub_header.height = 240
style_sub_header.font = font_sub_header
style_sub_header.alignment.horz = Alignment.HORZ_CENTER
style_sub_header.alignment.vert = Alignment.VERT_CENTER
borders_sub_header = Borders()
borders_sub_header.top = Borders.MEDIUM
borders_sub_header.left = Borders.MEDIUM
borders_sub_header.bottom = Borders.MEDIUM
borders_sub_header.right = Borders.MEDIUM
style_sub_header.borders = borders_sub_header
style_date = XFStyle()
font_date = Font()
font_date.name = "Arial Narrow"
font_date.height = 240
style_date.font = font_date
style_date.alignment.horz = Alignment.HORZ_RIGHT
style_date.alignment.vert = Alignment.VERT_CENTER
borders_date = Borders()
borders_date.left = Borders.MEDIUM
style_date.borders = borders_date
style_date_status = XFStyle()
font_date_status = Font()
font_date_status.name = "Arial Narrow"
font_date_status.height = 240
style_date_status.font = font_date_status
style_date_status.alignment.horz = Alignment.HORZ_LEFT
style_date_status.alignment.vert = Alignment.VERT_CENTER
borders_date_status = Borders()
borders_date_status.right = Borders.MEDIUM
style_date_status.borders = borders_date_status
style_cell_normal = XFStyle()
font_cell_normal = Font()
font_cell_normal.name = "Arial Narrow"
font_cell_normal.height = 220
style_cell_normal.font = font_cell_normal
style_cell_normal.alignment.horz = Alignment.HORZ_CENTER
style_cell_normal.alignment.vert = Alignment.VERT_CENTER
style_cell_right = XFStyle()
font_cell_right = Font()
font_cell_right.name = "Arial Narrow"
font_cell_right.height = 220
style_cell_right.font = font_cell_right
style_cell_right.alignment.horz = Alignment.HORZ_CENTER
style_cell_right.alignment.vert = Alignment.VERT_CENTER
borders_cell_right = Borders()
borders_cell_right.right = Borders.MEDIUM
style_cell_right.borders = borders_cell_right
style_cell_top = XFStyle()
borders_cell_top = Borders()
borders_cell_top.top = Borders.MEDIUM
style_cell_top.borders = borders_cell_top
column_offset = 1
date_column_offset = 2
row_offset = 1
table_width = 1 + 3 * len(Database.team())
# build titles
sheet.write_merge(row_offset, row_offset, column_offset, table_width + 1, "POLE MEDECINE INTERNE", style_title)
sheet.write_merge(
row_offset + 1,
row_offset + 1,
column_offset,
table_width + 1,
"Service NEPHROLOGIE – HEMODIALYSE",
style_title,
)
sheet.write_merge(
row_offset + 2,
row_offset + 2,
column_offset,
table_width + 1,
"Planning de {0} {1}".format(self.human_readable_months[self.month - 1], self.year),
style_title,
)
# patch date columns top borders
sheet.write(row_offset + 5, column_offset, "", style_cell_bottom)
sheet.write(row_offset + 5, column_offset + 1, "", style_cell_bottom)
# build header and sub header
for x in Database.team():
sheet.write_merge(
row_offset + 4,
row_offset + 4,
column_offset + date_column_offset + 3 * (x.id - 1),
column_offset + date_column_offset + 3 * x.id - 1,
x.name,
style_header,
)
sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1), "M", style_sub_header)
sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 1, "AM", style_sub_header)
sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 2, "N", style_sub_header)
"""
i = 40
for x in sorted(Style.colour_map):
style = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = Style.colour_map[x]
style.pattern = pattern
sheet.write(i, 1, x, style)
i += 1
"""
pattern_pale_blue = Pattern()
pattern_pale_blue.pattern = Pattern.SOLID_PATTERN
pattern_pale_blue.pattern_fore_colour = Style.colour_map["pale_blue"]
pattern_light_yellow = Pattern()
pattern_light_yellow.pattern = Pattern.SOLID_PATTERN
pattern_light_yellow.pattern_fore_colour = Style.colour_map["light_yellow"]
pattern_ice_blue = Pattern()
pattern_ice_blue.pattern = Pattern.SOLID_PATTERN
pattern_ice_blue.pattern_fore_colour = Style.colour_map["ice_blue"]
pattern_light_green = Pattern()
pattern_light_green.pattern = Pattern.SOLID_PATTERN
pattern_light_green.pattern_fore_colour = Style.colour_map["light_green"]
pattern_ivory = Pattern()
pattern_ivory.pattern = Pattern.SOLID_PATTERN
pattern_ivory.pattern_fore_colour = Style.colour_map["ivory"]
pattern_tan = Pattern()
pattern_tan.pattern = Pattern.SOLID_PATTERN
pattern_tan.pattern_fore_colour = Style.colour_map["tan"]
pattern_gold = Pattern()
pattern_gold.pattern = Pattern.SOLID_PATTERN
pattern_gold.pattern_fore_colour = Style.colour_map["gold"]
def __cell_colouration__(style, current_activity):
if current_activity is Activity.CONSULTATION:
style.pattern = pattern_pale_blue
elif current_activity is Activity.DIALYSIS:
style.pattern = pattern_ice_blue
elif current_activity is Activity.NEPHROLOGY:
style.pattern = pattern_light_green
elif current_activity is Activity.OTHERS:
style.pattern = pattern_tan
elif current_activity is Activity.OBLIGATION:
style.pattern = pattern_ivory
elif current_activity is Activity.OBLIGATION_WEEKEND:
style.pattern = pattern_light_yellow
elif current_activity is Activity.OBLIGATION_HOLIDAY:
style.pattern = pattern_gold
else:
style.pattern = Pattern()
return style
last_day = calendar.monthrange(self.year, self.month)[1]
for x in range(1, last_day + 1):
current_date = date(self.year, self.month, x)
current_daily_planning = self.daily_plannings[current_date]
# build date and date status columns
sheet.write(
row_offset + 5 + x,
column_offset,
"{0}. {1}".format(self.human_readable_days[current_daily_planning.weekday][0:3].lower(), x),
style_date,
)
if current_daily_planning.weekday in [5, 6]:
sheet.write(row_offset + 5 + x, column_offset + 1, "WK", style_date_status)
elif not current_daily_planning.is_working_day:
sheet.write(row_offset + 5 + x, column_offset + 1, "Férié", style_date_status)
else:
sheet.write(row_offset + 5 + x, column_offset + 1, "", style_date_status)
"""
easyxf(
'font: bold 1, name Tahoma, height 160;'
'align: vertical center, horizontal center, wrap on;'
'borders: left thin, right thin, top thin, bottom thin;'
'pattern: pattern solid, pattern_fore_colour green, pattern_back_colour green'
)
"""
# fill in month planning
for y in Database.team():
current_activity = current_daily_planning.__get_activity__(TimeSlot.FIRST_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 3 * (y.id - 1),
self.human_readable_activities[current_activity] if current_activity else "",
__cell_colouration__(style_cell_normal, current_activity),
)
current_activity = current_daily_planning.__get_activity__(TimeSlot.SECOND_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 1 + 3 * (y.id - 1),
self.human_readable_activities[current_activity] if current_activity else "",
__cell_colouration__(style_cell_normal, current_activity),
)
current_activity = current_daily_planning.__get_activity__(TimeSlot.THIRD_SHIFT, y)
sheet.write(
row_offset + 5 + x,
column_offset + date_column_offset + 2 + 3 * (y.id - 1),
self.human_readable_activities[current_activity] if current_activity else "",
__cell_colouration__(style_cell_right, current_activity),
)
# patch table bottom border
for x in range(0, table_width + 1):
sheet.write(row_offset + 5 + (last_day + 1), column_offset + x, "", style_cell_top)
book.save(r"C:\Temp\nephro-planner\new.xls")
def detail_budget(self,cr,uid,ids,context=None):
this=self.browse(cr,uid,ids[0])
year = this.year_id.name
year_id = this.year_id.id
month = this.month
if month == '1':
month_name = 'January'
elif month == '2':
month_name = 'February'
elif month == '3':
month_name = 'March'
elif month == '4':
month_name = 'April'
elif month == '5':
month_name = 'May'
elif month == '6':
month_name = 'June'
elif month == '7':
month_name = 'July'
elif month == '8':
month_name = 'August'
elif month == '9':
month_name = 'September'
elif month == '10':
month_name = 'October'
elif month == '11':
month_name = 'November'
elif month == '12':
month_name = 'December'
else:
raise osv.except_osv(_('Warning !'),_("Specify month correctly. "))
if int(month) in [1,3,5,7,8,10,12]:
join_date=year +'-'+month+'-'+'31'
if int(month) in [4,6,9,11]:
join_date=year +'-'+month+'-'+'30'
if int(month) in [2]:
if int(year) % 4 == 0:
join_date=year +'-'+month+'-'+'29'
else:
join_date=year +'-'+month+'-'+'28'
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.height= 275
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.height =220
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height= 275
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.height =220
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x02
borders1.right = 0x02
borders1.top = 0x02
borders1.bottom = 0x02
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x32
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
style_content= XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
month_name = 'Payment ('+str(month_name)+')'
wb = Workbook()
ws = wb.add_sheet('Budget')
ws.row(0).height=500
ws.write(0,0,'Department Name',style_header)
ws.col(0).width = 8000
ws.write(0,1,'Department HoD',style_header)
ws.col(1).width = 8000
ws.write(0,2,'RO of HOD',style_header)
ws.col(2).width = 8000
ws.write(0,3,'Employee Name',style_header)
ws.col(3).width = 9000
ws.write(0,4,'Designation',style_header)
ws.col(4).width = 8000
ws.write(0,5,'Working Days',style_header)
ws.col(5).width = 5000
ws.write(0,6,'Working Hours',style_header)
ws.col(6).width = 5000
ws.write(0,7,'Working Month',style_header)
ws.col(7).width = 5000
ws.write(0,8,'Salary Amount',style_header)
ws.col(8).width = 5000
ws.write(0,9,month_name,style_header)
ws.col(9).width = 8000
# ws.write(0,5,'O.T. Amount',style_header)
# ws.col(5).width = 4400
# ws.write(0,6,'Total Amount',style_header)
# ws.col(6).width = 4400
# ws.write(0,7,'Insentive Amount',style_header)
# ws.col(7).width = 4400
# ws.write(0,8,'Deduction Amount',style_header)
# ws.col(8).width = 5000
# ws.write(0,9,'Percentage',style_header)
# ws.col(9).width = 4400
emp_ids = []
emp_obj=self.pool.get('hr.employee')
pay_obj=self.pool.get('salary.payment.line')
if this.dept_id:
cr.execute("select emp.id from hr_employee as emp left join resource_resource "\
"as res on (emp.resource_id=res.id) where emp.department_id = "\
"'"+str(this.dept_id.id)+"' and emp.department_id is not null and "\
"res.active=True and emp.joining_date <'"+str(join_date)+"' order by emp.department_id, (substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
else:
cr.execute("select emp.id from hr_employee as emp left join resource_resource "\
"as res on (emp.resource_id=res.id) where res.active=True and emp.joining_date < '"+str(join_date)+"' order by emp.department_id, "\
"(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
holiday_obj = self.pool.get('holiday.list')
if int(month) in [1,3,5,7,8,10,12]:
month = 31
if int(month) in [4,6,9,11]:
month = 30
if int(month) in [2]:
if int(year) % 4 == 0:
month = 29
else:
month = 28
off_day = working_day = 0
holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)])
for line in holiday_obj.browse(cr, uid, holiday_ids):
off_day = line.holiday
working_day = month - off_day
i=0
dept_dict = {}
grand = total = pay_total = pay_grand = budget = 0.0
pay_data = False
flag = True
for each in emp_obj.browse(cr, uid, emp_ids):
tolal_months = 0
if not each.joining_date:
continue
dt1 = datetime.strptime(each.joining_date,'%Y-%m-%d')
dt2 = datetime.strptime(time.strftime(DEFAULT_SERVER_DATE_FORMAT),'%Y-%m-%d')
start_month=dt1.month
end_months=(dt2.year-dt1.year)*12 + dt2.month+1
dates=[datetime(year=yr, month=mn, day=1) for (yr, mn) in (
((m - 1) / 12 + dt1.year, (m - 1) % 12 + 1) for m in range(start_month, end_months)
)]
for val in dates:
tolal_months += 1
pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')])
if pay_ids:
pay_data = pay_obj.browse(cr, uid, pay_ids[0])
i+=1
if dept_dict.has_key(str(each.department_id.id)):
if each.department_id:
salary = 0.0
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '')
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,3, name)
ws.write(i,4, each.designation_id and each.designation_id.name or '')
ws.write(i,5, pay_data.days or '0.0')
ws.write(i,6, pay_data.over_time)
ws.write(i,7, str(tolal_months) + ' Month')
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
ws.write(i,8, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,9,pay_data.total_amount or 0.0)
else:
ws.write(i,9, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
elif not each.department_id:
salary = 0.0
if flag:
ws.write(i,0, 'Allocated Budget',style_header)
ws.write(i,1, 0.0,style_header)
ws.write(i,2, 'Total',style_header)
ws.write(i,3, '',style_header)
ws.write(i,4, '',style_header)
ws.write(i,5, '',style_header)
ws.write(i,6, '',style_header)
ws.write(i,7, '',style_header)
ws.write(i,8, total,style_header)
if pay_total:
ws.write(i,9,pay_total,style_header)
else:
ws.write(i,9, 0.0,style_header)
if budget:
diff = pay_total - float(budget)
else:
diff = pay_total
ws.write(i,10, diff,style_header1)
flag = False
i += 2
total = pay_total = budget = 0.0
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,0, 'X Department')
ws.write(i,1,'X Reporting Officer')
ws.write(i,2,'X Reporting Officer')
ws.write(i,3, name)
ws.write(i,4, each.designation_id and each.designation_id.name or '')
ws.write(i,5, pay_data.days or '0.0')
ws.write(i,6, pay_data.over_time)
ws.write(i,7, str(tolal_months) + ' Month')
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
ws.write(i,8, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,9,pay_data.total_amount or 0.0)
else:
ws.write(i,9, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
else:
dept_dict[str(each.department_id.id)] = ''
if i != 1:
ws.write(i,0, 'Allocated Budget',style_header)
if budget:
ws.write(i,1, budget,style_header)
else:
budget = 0.0
ws.write(i,1,budget,style_header)
ws.write(i,2, 'Total',style_header)
ws.write(i,3, '',style_header)
ws.write(i,4, '',style_header)
ws.write(i,5, '',style_header)
ws.write(i,6, '',style_header)
ws.write(i,7, '',style_header)
ws.write(i,8, total,style_header)
if pay_total:
ws.write(i,9,pay_total,style_header)
else:
ws.write(i,9, 0.0,style_header)
if budget:
diff = pay_total - float(budget)
else:
diff = pay_total
ws.write(i,10, diff,style_header1)
i += 2
total = pay_total = budget = 0.0
if each.department_id:
salary = 0.0
budget = each.department_id.dept_budget
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '')
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,3, name)
ws.write(i,4, each.designation_id and each.designation_id.name or '')
ws.write(i,5, pay_data.days)
ws.write(i,6, pay_data.over_time)
ws.write(i,7, str(tolal_months) + ' Month')
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
ws.write(i,8, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,9,pay_data.total_amount or 0.0)
else:
ws.write(i,9, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
i += 1
ws.write(i+1,7, 'Total',style_header)
ws.write(i+1,8, total,style_header)
ws.write(i+3,7, 'Grand Total',style_header)
ws.write(i+3,8, grand,style_header)
ws.write(i+1,9, pay_total,style_header)
ws.write(i+3,9, pay_grand,style_header)
diff_pay = pay_total - total
diff_grand = pay_grand - grand
ws.write(i+1,10, diff_pay,style_header1)
ws.write(i+3,10, diff_grand,style_header1)
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def write_sheet(self, data, sheet_name, print_to_screen=False):
'''Write a very simple table to a new sheet in a spreadsheet,
Optionally, print the table to the screen'''
# most cells
al = Alignment()
al.horz = Alignment.HORZ_RIGHT
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.height = 9 * 20 # 9 pt
style = XFStyle()
style.font = font
style.alignment = al
# tops cells
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.bold = True
font.height = 9 * 20 # 9 pt
style_top = XFStyle()
style_top.font = font
style_top.alignment = al
# left cells
al = Alignment()
al.horz = Alignment.HORZ_LEFT
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.bold = True
font.italic = True
font.height = 9 * 20 # 9 pt
style_left = XFStyle()
style_left.font = font
style_left.alignment = al
ws = self.add_sheet(sheet_name)
for i, row in enumerate(data):
for j, cell in enumerate(row):
borders = Borders()
if i == 0:
borders.top = 1
borders.bottom = 2
if i == len(row) - 1:
borders.bottom = 1
if j == 0:
borders.left = 1
borders.right = 1
if j == len(row) - 1:
borders.right = 1
if j == 0:
_style = style_left
elif i == 0:
_style = style_top
else:
_style = style
_style.borders = borders
# one of the libraries can get confused with plain strings
if isinstance(cell, str):
cell = unicode(cell, 'utf-8')
ws.write(i + 1, j + 1, cell, _style)
if print_to_screen:
print print_table(data, sheet_name, bold=True)
def exportToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
sheet1.col(1).width = 256*20;
sheet1.col(2).width = 256*80;
sheet1.col(3).width = 256*10;
sheet1.col(4).width = 256*20;
default_book_style = book.default_style
default_book_style.font.height = 20 * 36 # 36pt
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
algn1 = Alignment();
algn1.wrap = 1;
#algn1.horz = Alignment.HORZ_CENTER
#algn1.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
print "export";
if( objectProject):
i=0;
print "start" ;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead );
# row1.write(1, ('รายละเอียด').decode('UTF8'));
row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead );
row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead );
i=i+1;
style1 = XFStyle();
style1.alignment = algn1;
#style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on')
for value in objectProject:
row1 = sheet1.row(i) ;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = algn1;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
row1.write(0, value.get('risk_management_id'),styleRowDetail );
#row1.write(1, value.get('risk_detail').decode('UTF8') , style1);
sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(3, value.get('report_date') ,styleDate);
row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead );
sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail );
sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simple.xls');
print dirTempFile;
book.save(dirTempFile);
def account_budget(self,cr,uid,ids,context=None):
this=self.browse(cr,uid,ids[0])
year = this.year_id.name
year_id = this.year_id.id
month = this.month
if int(month) in [1,3,5,7,8,10,12]:
join_date=year +'-'+month+'-'+'31'
if int(month) in [4,6,9,11]:
join_date=year +'-'+month+'-'+'30'
if int(month) in [2]:
if int(year) % 4 == 0:
join_date=year +'-'+month+'-'+'29'
else:
join_date=year +'-'+month+'-'+'28'
if month == '1':
month_name = 'January'
elif month == '2':
month_name = 'February'
elif month == '3':
month_name = 'March'
elif month == '4':
month_name = 'April'
elif month == '5':
month_name = 'May'
elif month == '6':
month_name = 'June'
elif month == '7':
month_name = 'July'
elif month == '8':
month_name = 'August'
elif month == '9':
month_name = 'September'
elif month == '10':
month_name = 'October'
elif month == '11':
month_name = 'November'
elif month == '12':
month_name = 'December'
else:
raise osv.except_osv(_('Warning !'),_("Specify month correctly. "))
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.height= 275
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.height =220
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height= 275
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.height =220
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x02
borders1.right = 0x02
borders1.top = 0x02
borders1.bottom = 0x02
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x32
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
#Define the font attributes for header
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.height= 275
#Define the font attributes for header
content_fnt2 = Font()
content_fnt2.name ='Arial'
content_fnt2.height =220
align_content2 = Alignment()
align_content2.horz= Alignment.HORZ_CENTER
borders2 = Borders()
borders2.left = 0x02
borders2.right = 0x02
borders2.top = 0x02
borders2.bottom = 0x02
#The text should be centrally aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern2 = Pattern()
pattern2.pattern = Pattern.SOLID_PATTERN
pattern2.pattern_fore_colour = 0x0A
#apply the above settings to the row(0) header
style_header2= XFStyle()
style_header2.font= fnt2
style_header2.pattern= pattern2
style_header2.borders = borders2
style_header2.alignment=align2
style_content= XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
month_name = 'Payment ('+str(month_name)+')'
wb = Workbook()
ws = wb.add_sheet('Budget')
ws.row(0).height=500
ws.write(0,0,'Department Name',style_header)
ws.col(0).width = 8000
ws.write(0,1,'Department HoD',style_header)
ws.col(1).width = 8000
ws.write(0,2,'RO of HOD',style_header)
ws.col(2).width = 8000
ws.write(0,3,'Allocated Budget',style_header)
ws.col(3).width = 8000
ws.write(0,4,'Salary Amount',style_header)
ws.col(4).width = 5000
ws.write(0,5,month_name,style_header)
ws.col(5).width = 8000
ws.write(0,6,'Difference',style_header)
ws.col(6).width = 8000
ws.write(0,7,'No Of Emp',style_header)
ws.col(7).width = 8000
# ws.write(0,5,'O.T. Amount',style_header)
# ws.col(5).width = 4400
# ws.write(0,6,'Total Amount',style_header)
# ws.col(6).width = 4400
# ws.write(0,7,'Insentive Amount',style_header)
# ws.col(7).width = 4400
# ws.write(0,8,'Deduction Amount',style_header)
# ws.col(8).width = 5000
# ws.write(0,9,'Percentage',style_header)
# ws.col(9).width = 4400
emp_ids = []
emp_obj=self.pool.get('hr.employee')
pay_obj=self.pool.get('salary.payment.line')
if this.dept_id:
cr.execute("select emp.id from hr_employee as emp left join resource_resource " \
"as res on (emp.resource_id=res.id) left join hr_department as dept on " \
"(emp.department_id=dept.id) where emp.department_id = '"+str(this.dept_id.id)+"' and " \
"emp.department_id is not null and res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code, " \
"(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
else:
cr.execute("select emp.id from hr_employee as emp left join resource_resource " \
"as res on (emp.resource_id=res.id) left join hr_department as dept on (emp.department_id=dept.id) " \
"where res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code,(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
holiday_obj = self.pool.get('holiday.list')
if int(month) in [1,3,5,7,8,10,12]:
month = 31
if int(month) in [4,6,9,11]:
month = 30
if int(month) in [2]:
if int(year) % 4 == 0:
month = 29
else:
month = 28
off_day = working_day = 0
holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)])
for line in holiday_obj.browse(cr, uid, holiday_ids):
off_day = line.holiday
working_day = month - off_day
i=1
dept_dict = {}
grand = total = pay_total = pay_grand = budget = total_budget = 0.0
pay_data = False
flag = True
count1=0.0
for each in emp_obj.browse(cr, uid, emp_ids):
pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')])
if pay_ids:
pay_data = pay_obj.browse(cr, uid, pay_ids[0])
if dept_dict.has_key(str(each.department_id.id)):
if each.department_id:
salary = 0.0
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
total += salary
grand += salary
# if pay_data.employee_id.id == each.id:
# print"ssssssssssssssssssss minssssssssss",count1
# count1=count1+1
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
count1=count1+1
else:
pay_total += 0.0
pay_grand += 0.0
elif not each.department_id:
if flag:
i -= 1
ws.write(i,3, total)
ws.write(i,4, pay_total)
if budget:
diff = float(budget) - pay_total
else:
diff = pay_total
if budget and diff > 0:
ws.write(i,5, diff,style_header1)
elif budget and diff < 0:
ws.write(i,5, diff,style_header2)
else:
ws.write(i,5, diff,style_header2)
ws.write(i,6, count1)
count1=0.0
flag = False
i += 1
# salary = 0.0
# name = '[' + str(each.sinid) +'] '+ str(each.name)
#
# ws.write(i,0, 'X Department')
# ws.write(i,1,'X Reporting Officer')
# ws.write(i,2, name)
#
# if each.daily:
# salary = each.salary * working_day
# else:
# salary = each.salary
# ws.write(i,3, salary)
# if pay_data and pay_data.employee_id.id == each.id:
# ws.write(i,4,pay_data.total_amount or 0.0)
# else:
# ws.write(i,4, 0.0)
else:
dept_dict[str(each.department_id.id)] = ''
if i != 1:
i -= 1
ws.write(i,4, total)
ws.write(i,5, pay_total)
if budget:
diff = float(budget) - pay_total
else:
diff = pay_total
if budget and diff > 0:
ws.write(i,6, diff,style_header1)
elif budget and diff < 0:
ws.write(i,6, diff,style_header2)
else:
ws.write(i,6, diff,style_header2)
ws.write(i,7, count1)
count1=0.0
i += 1
if each.department_id:
total = pay_total = budget = 0.0
salary = 0.0
budget = each.department_id.dept_budget
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '')
ws.write(i,3, budget)
total_budget += budget
if pay_data.employee_id.daily:
salary = pay_data.basic * working_day
else:
salary = pay_data.basic
total += salary
grand += salary
# if pay_data.employee_id.id == each.id:
# print"saaaaaaaaaaaaaallllllllllllllllllllllllllll",count1
# count1=count1+1
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
count1=count1+1
else:
pay_total += 0.0
pay_grand += 0.0
i += 1
ws.write(i+3,1, 'Grand Total',style_header)
ws.write(i+3,3, total_budget,style_header)
ws.write(i+3,4, grand,style_header)
ws.write(i+3,5, pay_grand,style_header)
diff_grand = total_budget - pay_grand
ws.write(i+3,6, diff_grand,style_header1)
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def exportToXls(self):
# opening file dialog
fileName = QFileDialog.getSaveFileName(self, "Save as", RES, "Microsoft Excel Spreadsheet (*.xls)")
if fileName.count() > 0:
try:
COLUMN_WIDTH = 3000
alignment = Alignment()
alignment.horizontal = Alignment.HORZ_CENTER
alignment.vertical = Alignment.VERT_CENTER
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.alignment = alignment
style.borders = borders
font = Font()
font.bold = True
headerStyle = XFStyle()
headerStyle.font = font
separate = Borders()
separate.left = Borders.THIN
separate.right = Borders.DOUBLE
separate.top = Borders.THIN
separate.bottom = Borders.THIN
separateStyle = XFStyle()
separateStyle.borders = separate
book = Workbook(encoding="utf-8")
# modelling data
if self.exportStepByStep.isChecked():
dec_sheet = book.add_sheet("Data decomposition")
# decomposition data
if self.exportData.isChecked():
# initial data
column = 0
row = 0
dec_sheet.write(row, column, "Time series", headerStyle)
dec_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.parentWidget().currentDataSet[0]:
dec_sheet.write(row, column, item, separateStyle)
row += 1
# decomposition
for lvl in self.parentWidget().wCoefficients:
row = 0
column += 1
dec_sheet.write(row, column, "Level" + str(column - 1), headerStyle)
dec_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in lvl:
dec_sheet.write(row, column, item, style)
row += 1
# decomposition graphs
if self.exportGraph.isChecked():
pass
levels_sheet = book.add_sheet("Multiscale forecast")
# levels data
if self.exportData.isChecked():
column = 0
for lvl in self.parentWidget().processedWCoeffs:
row = 0
levels_sheet.write(row, column, "Level" + str(column), headerStyle)
levels_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in lvl:
levels_sheet.write(row, column, float(item), style)
row += 1
column += 1
if self.exportForecast.isChecked():
result_sheet = book.add_sheet("Results")
if self.exportData.isChecked():
# initial
column = 0
row = 0
result_sheet.write(row, column, "Initial data", headerStyle)
result_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.parentWidget().currentDataSet[0]:
result_sheet.write(row, column, item, separateStyle)
row += 1
# forecast
row = 0
column += 1
result_sheet.write(row, column, "Forecast", headerStyle)
result_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.parentWidget().resultingForecast:
result_sheet.write(row, column, item, style)
row += 1
if self.exportGraph.isChecked():
row = 0
column = 2
self.parentWidget().resultingGraph.saveFigure("forecast", format="bmp")
result_sheet.insert_bitmap(RES + TEMP + "forecast.bmp", row, column)
# saving xls
try:
book.save(unicode(fileName))
self.parentWidget().messageInfo.showInfo("Saved as " + unicode(fileName))
except Exception:
self.parentWidget().messageInfo.showInfo("Could not save as " + unicode(fileName), True)
except Exception, e:
self.parentWidget().messageInfo.showInfo("Not enough data.", True)
def produce_summary(self, cr, uid, travel, context=None):
number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]')
total_fnt = Font()
total_fnt.name = 'Calibri'
total_fnt.bold = True
total_fnt.height = 16 * 20 # font size 12
total_cell_l_style = XFStyle()
total_cell_l_style.alignment = Column.title_aln
total_cell_l_style.borders = Borders()
total_cell_l_style.borders.left = Borders.THICK
total_cell_l_style.borders.right = Borders.HAIR
total_cell_l_style.borders.top = Borders.THICK
total_cell_l_style.borders.bottom = Borders.THICK
total_cell_l_style.pattern = Column.title_ptn
total_cell_l_style.num_format_str = number_format
total_cell_l_style.font = Column.obj_fnt
total_cell_r_style = XFStyle()
total_cell_r_style.alignment = Column.title_aln
total_cell_r_style.borders = Borders()
total_cell_r_style.borders.left = Borders.HAIR
total_cell_r_style.borders.right = Borders.THICK
total_cell_r_style.borders.top = Borders.THICK
total_cell_r_style.borders.bottom = Borders.THICK
total_cell_r_style.pattern = Column.title_ptn
total_cell_r_style.num_format_str = number_format
total_cell_r_style.font = Column.obj_fnt
sub_total_cell_label = Cell(
_(u'SOUS-TOTAL'), Column.title_fnt, Column.title_aln,
total_cell_l_style.borders, Column.title_ptn)
total_cell_label = Cell(
_(u'TOTAL'), total_fnt, Column.title_aln,
total_cell_r_style.borders, Column.title_ptn, number_format)
journeys = [j for i in travel.passenger_ids for j in i.journey_ids]
w = Workbook()
ws = w.add_sheet(_('Travel Summary'))
ws.row(2).height = 0x0280
ws.row(3 + len(journeys)).height = 0x0140
ws.row(4 + len(journeys)).height = 0x0180
row = 0
row += 2
_excel_columns = self.get_excel_columns(context)
# Write headers
for i, col in enumerate(_excel_columns):
ws.col(i).width = col.width
ws.write(row, i, col.text, col.style)
row += 1
for i, obj in enumerate(journeys):
ws.write(row + i, 0, i + 1, _excel_columns[0].obj_style)
for j in xrange(1, len(_excel_columns)):
ws.write(row + i, j,
_excel_columns[j].func(obj),
_excel_columns[j].obj_style)
row += len(journeys)
rate_index = [i for i, x in enumerate(_excel_columns)
if x.text == _('TICKET RATE')][0] - 1
cost_index = [i for i, x in enumerate(_excel_columns)
if x.text == _('COSTS')][0] - 1
total_index = [i for i, x in enumerate(_excel_columns)
if x.text == _('TOTAL')][0] - 1
# Sub total label
ws.write_merge(row, row, 0, rate_index,
sub_total_cell_label.text,
sub_total_cell_label.style)
# Sub totals
ws.write(row, rate_index + 1,
Formula("SUM(%s%d:%s%d)" % (chr(66 + rate_index), 4,
chr(66 + rate_index), row)),
total_cell_l_style)
ws.write(row, rate_index + 2,
Formula("SUM(%s%d:%s%d)" % (chr(66 + cost_index), 4,
chr(66 + cost_index), row)),
total_cell_r_style)
total_top_underline_style = XFStyle()
total_top_underline_style.borders = Borders()
total_top_underline_style.borders.top = Borders.THICK
total_top_underline_style.font = Column.obj_fnt
# Draw a line above total to close box
ws.write(row, total_index + 1, "", total_top_underline_style)
row += 1
# Total label
ws.write_merge(row, row, 0, rate_index,
total_cell_label.text,
total_cell_label.style)
# Total
ws.write_merge(row, row, rate_index + 1, total_index,
Formula("%s%d+%s%d" % (chr(66 + rate_index), row,
chr(66 + cost_index), row)),
total_cell_label.style)
return w
def report_get(self,cr,uid,ids,context=None):
this=self.browse(cr,uid,ids[0])
year = this.year_id.name
year_id = this.year_id.id
month = this.month
if month == '1':
month_name = 'January'
elif month == '2':
month_name = 'February'
elif month == '3':
month_name = 'March'
elif month == '4':
month_name = 'April'
elif month == '5':
month_name = 'May'
elif month == '6':
month_name = 'June'
elif month == '7':
month_name = 'July'
elif month == '8':
month_name = 'August'
elif month == '9':
month_name = 'September'
elif month == '10':
month_name = 'October'
elif month == '11':
month_name = 'November'
elif month == '12':
month_name = 'December'
else:
raise osv.except_osv(_('Warning !'),_("Specify month correctly. "))
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.height= 275
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.height =220
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height= 275
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.height =220
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x02
borders1.right = 0x02
borders1.top = 0x02
borders1.bottom = 0x02
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
pattern1.pattern = Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 0x32
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
style_content= XFStyle()
style_content.alignment = align_content
style_content.font = content_fnt
month_name = 'Payment ('+str(month_name)+')'
wb = Workbook()
ws = wb.add_sheet('Budget')
ws.row(0).height=500
ws.write(0,0,'Department Name',style_header)
ws.col(0).width = 8000
ws.write(0,1,'Department HoD',style_header)
ws.col(1).width = 8000
ws.write(0,2,'Employee Name',style_header)
ws.col(2).width = 8000
ws.write(0,3,'Salary Amount',style_header)
ws.col(3).width = 5000
ws.write(0,4,month_name,style_header)
ws.col(4).width = 8000
# ws.write(0,5,'O.T. Amount',style_header)
# ws.col(5).width = 4400
# ws.write(0,6,'Total Amount',style_header)
# ws.col(6).width = 4400
# ws.write(0,7,'Insentive Amount',style_header)
# ws.col(7).width = 4400
# ws.write(0,8,'Deduction Amount',style_header)
# ws.col(8).width = 5000
# ws.write(0,9,'Percentage',style_header)
# ws.col(9).width = 4400
emp_ids = []
emp_obj=self.pool.get('hr.employee')
pay_obj=self.pool.get('salary.payment.line')
if this.dept_id:
cr.execute("select emp.id from hr_employee as emp left join resource_resource\
as res on (emp.resource_id=res.id) where emp.department_id = \
'"+str(this.dept_id.id)+"' and emp.department_id is not null and \
res.active=True order by emp.department_id")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
else:
cr.execute("select emp.id from hr_employee as emp left join resource_resource \
as res on (emp.resource_id=res.id) where res.active=True order by emp.department_id")
temp = cr.fetchall()
for data in temp:
if len(data)>0 and data[0] != None:
emp_ids.append(data[0])
holiday_obj = self.pool.get('holiday.list')
if int(month) in [1,3,5,7,8,10,12]:
month = 31
if int(month) in [4,6,9,11]:
month = 30
if int(month) in [2]:
if int(year) % 4 == 0:
month = 29
else:
month = 28
off_day = working_day = 0
holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)])
for line in holiday_obj.browse(cr, uid, holiday_ids):
off_day = line.holiday
working_day = month - off_day
i=0
dept_dict = {}
grand = total = pay_total = pay_grand = budget = 0.0
pay_data = False
flag = True
for each in emp_obj.browse(cr, uid, emp_ids):
pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id)])
if pay_ids:
pay_data = pay_obj.browse(cr, uid, pay_ids[0])
i+=1
if dept_dict.has_key(str(each.department_id.id)):
if each.department_id:
salary = 0.0
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,2, name)
if each.daily:
salary = each.salary * working_day
else:
salary = each.salary
ws.write(i,3, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,4,pay_data.total_amount or 0.0)
else:
ws.write(i,4, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
elif not each.department_id:
salary = 0.0
if flag:
ws.write(i,0, 'Allocated Budget',style_header)
ws.write(i,1, 0.0,style_header)
ws.write(i,2, 'Total',style_header)
ws.write(i,3, total,style_header)
if pay_total:
ws.write(i,4,pay_total,style_header)
else:
ws.write(i,4, 0.0,style_header)
if budget:
diff = pay_total - float(budget)
else:
diff = pay_total
ws.write(i,5, diff,style_header1)
flag = False
i += 2
total = pay_total = budget = 0.0
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,0, 'X Department')
ws.write(i,1,'X Reporting Officer')
ws.write(i,2, name)
if each.daily:
salary = each.salary * working_day
else:
salary = each.salary
ws.write(i,3, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,4,pay_data.total_amount or 0.0)
else:
ws.write(i,4, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
else:
dept_dict[str(each.department_id.id)] = ''
if i != 1:
ws.write(i,0, 'Allocated Budget',style_header)
if budget:
ws.write(i,1, budget,style_header)
else:
budget = 0.0
ws.write(i,1,budget,style_header)
ws.write(i,2, 'Total',style_header)
ws.write(i,3, total,style_header)
if pay_total:
ws.write(i,4,pay_total,style_header)
else:
ws.write(i,4, 0.0,style_header)
if budget:
diff = pay_total - float(budget)
else:
diff = pay_total
ws.write(i,5, diff,style_header1)
i += 2
total = pay_total = budget = 0.0
if each.department_id:
salary = 0.0
budget = each.department_id.dept_budget
dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name)
ws.write(i,0, dept)
ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '')
name = '[' + str(each.sinid) +'] '+ str(each.name)
ws.write(i,2, name)
if each.daily:
salary = each.salary * working_day
else:
salary = each.salary
ws.write(i,3, salary)
if pay_data and pay_data.employee_id.id == each.id:
ws.write(i,4,pay_data.total_amount or 0.0)
else:
ws.write(i,4, 0.0)
total += salary
grand += salary
if pay_data and pay_data.employee_id.id == each.id:
pay_total += pay_data.total_amount or 0.0
pay_grand += pay_data.total_amount or 0.0
else:
pay_total += 0.0
pay_grand += 0.0
i += 1
ws.write(i+1,2, 'Total',style_header)
ws.write(i+1,3, total,style_header)
ws.write(i+3,2, 'Grand Total',style_header)
ws.write(i+3,3, grand,style_header)
ws.write(i+1,4, pay_total,style_header)
ws.write(i+3,4, pay_grand,style_header)
diff_pay = pay_total - total
diff_grand = pay_grand - grand
ws.write(i+1,5, diff_pay,style_header1)
ws.write(i+3,5, diff_grand,style_header1)
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
for i in range(len(datelist)):
test = datelist[i].strftime("%d%m%y")
sheet1.append(wkbook.add_sheet('Collect-' + test))
# Sytling the date
style = XFStyle()
style.num_format_str = 'DD/MM/YYYY'
# set to 2 decimal point
style2 = XFStyle()
style2.num_format_str = "#,##0.00"
style3 = XFStyle()
font = xlwt.Font()
font.height = 180
style.font = font
style2.font = font
style3.font = font
h = -1
for key in ccard_cif:
h += 1
# print(key)
for i in range(len(ccard_cif[key])):
for j in range(1, 7):
if j == 1: # type
sheet1[h].write(i, j, ccard_cif[key][i][0], style3)
if j == 2: # date
sheet1[h].write(i, j, datetime(*ccard_cif[key][i][1]),
style)
elif j == 3: # room
def performance_register_report(self, cr, uid, ids, data, context=None):
obj = self.browse(cr, uid, ids)
emp_obj = self.pool.get('hr.employee')
f_name = ''
d_name = ''
wb = Workbook()
ws = wb.add_sheet('Contractor Payment Bonus')
total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height = 300
fnt1.bold = True
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x00
borders1.right = 0x00
borders1.top = 0x00
borders1.bottom = 0x00
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern1_fore_colour = 0x1F
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.height = 300
fnt2.bold = True
align_content2 = Alignment()
align_content2.horz = Alignment.HORZ_CENTER
borders2 = Borders()
borders2.left = 0x00
borders2.right = 0x00
borders2.top = 0x00
borders2.bottom = 0x00
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_CENTER
pattern2 = Pattern()
pattern2.pattern2 = Pattern.SOLID_PATTERN
pattern2.pattern2_fore_colour = 0x1F
style_header2 = XFStyle()
style_header2.font = fnt2
style_header2.pattern = pattern2
style_header2.borders = borders2
style_header2.alignment = align2
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.height = 300
fnt3.bold = True
align_content3 = Alignment()
align_content3.horz = Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x00
borders3.right = 0x00
borders3.top = 0x00
borders3.bottom = 0x00
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
pattern3 = Pattern()
pattern3.pattern3 = Pattern.SOLID_PATTERN
pattern3.pattern3_fore_colour = 0x1F
style_header3 = XFStyle()
style_header3.font = fnt3
style_header3.pattern = pattern3
style_header3.borders = borders3
style_header3.alignment = align3
fnt = Font()
fnt.name = 'Arial'
fnt.height = 275
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 150
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x02
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
fnt5 = Font()
fnt5.name = 'Arial'
fnt5.height = 200
content_fnt5 = Font()
content_fnt5.name = 'Arial'
content_fnt5.height = 150
align_content5 = Alignment()
align_content5.horz = Alignment.HORZ_CENTER
borders5 = Borders()
borders5.left = 0x02
borders5.right = 0x02
borders5.top = 0x02
borders5.bottom = 0x02
align5 = Alignment()
align5.horz = Alignment.HORZ_CENTER
align5.vert = Alignment.VERT_CENTER
pattern5 = Pattern()
# pattern5.pattern = Pattern.SOLID_PATTERN
# pattern5.pattern_fore_colour = 0x1F
style_header5 = XFStyle()
style_header5.font = fnt5
style_header5.pattern = pattern5
style_header5.borders = borders5
style_header5.alignment = align5
if obj.partner_id:
get_name = obj.partner_id.name
elif obj.company_id:
get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip
else:
get_name = obj.employee_id.partner_id.name
ws.row(0).height = 500
ws.write_merge(0, 0, 0, 19, get_name, style_header1)
date1 = datetime.strptime(obj.from_date,
"%Y-%m-%d").timetuple().tm_year
date2 = datetime.strptime(obj.till_date,
"%Y-%m-%d").timetuple().tm_year
if date1 == date2:
d_name = 'BONUS' + ' - ' + str(date1)
else:
d_name = 'BONUS' + ' ' + str(date1) + ' ' + '-' + ' ' + str(date2)
ws.row(1).height = 500
ws.write_merge(1, 1, 0, 19, d_name, style_header2)
ws.col(0).width = 5000
ws.col(1).width = 7500
ws.col(2).width = 5000
ws.col(3).width = 3000
ws.col(4).width = 3000
ws.col(5).width = 3000
ws.col(6).width = 3000
ws.col(7).width = 3000
ws.col(8).width = 3000
ws.col(9).width = 3000
ws.col(10).width = 3000
ws.col(11).width = 3000
ws.col(12).width = 3000
ws.col(13).width = 3000
ws.col(14).width = 3000
ws.col(15).width = 3000
ws.col(16).width = 3000
ws.col(17).width = 3000
ws.col(18).width = 3000
ws.col(19).width = 3000
ws.col(20).width = 3000
ws.col(21).width = 3000
ws.col(22).width = 3000
ws.col(23).width = 3000
ws.col(24).width = 3000
ws.col(25).width = 3000
ws.col(26).width = 3000
ws.col(27).width = 3000
ws.col(28).width = 3000
ws.col(29).width = 4000
ws.row(2).height = 400
ws.write(2, 0, 'EMP. CODE', style_header)
ws.write(2, 1, 'NAME', style_header)
ws.write(2, 2, 'JOINING DATE', style_header)
ws.write_merge(2, 2, 3, 4, 'APRIL', style_header)
ws.write_merge(2, 2, 5, 6, 'MAY', style_header)
ws.write_merge(2, 2, 7, 8, 'JUNE', style_header)
ws.write_merge(2, 2, 9, 10, 'JULY', style_header)
ws.write_merge(2, 2, 11, 12, 'AUGUST', style_header)
ws.write_merge(2, 2, 13, 14, 'SEPTEMBER', style_header)
ws.write_merge(2, 2, 15, 16, 'OCTOBER', style_header)
ws.write_merge(2, 2, 17, 18, 'NOVEMBER', style_header)
ws.write_merge(2, 2, 19, 20, 'DECEMBER', style_header)
ws.write_merge(2, 2, 21, 22, 'JANUARY', style_header)
ws.write_merge(2, 2, 23, 24, 'FEBRUARY', style_header)
ws.write_merge(2, 2, 25, 26, 'MARCH', style_header)
ws.write_merge(2, 2, 27, 28, 'TOTAL', style_header)
ws.write(2, 29, 'BONUS', style_header)
ws.row(3).height = 400
ws.write(3, 0, '', style_header)
ws.write(3, 1, '', style_header)
ws.write(3, 2, '', style_header)
ws.write(3, 3, 'DAYS', style_header)
ws.write(3, 4, 'SALARY', style_header)
ws.write(3, 5, 'DAYS', style_header)
ws.write(3, 6, 'SALARY', style_header)
ws.write(3, 7, 'DAYS', style_header)
ws.write(3, 8, 'SALARY', style_header)
ws.write(3, 9, 'DAYS', style_header)
ws.write(3, 10, 'SALARY', style_header)
ws.write(3, 11, 'DAYS', style_header)
ws.write(3, 12, 'SALARY', style_header)
ws.write(3, 13, 'DAYS', style_header)
ws.write(3, 14, 'SALARY', style_header)
ws.write(3, 15, 'DAYS', style_header)
ws.write(3, 16, 'SALARY', style_header)
ws.write(3, 17, 'DAYS', style_header)
ws.write(3, 18, 'SALARY', style_header)
ws.write(3, 19, 'DAYS', style_header)
ws.write(3, 20, 'SALARY', style_header)
ws.write(3, 21, 'DAYS', style_header)
ws.write(3, 22, 'SALARY', style_header)
ws.write(3, 23, 'DAYS', style_header)
ws.write(3, 24, 'SALARY', style_header)
ws.write(3, 25, 'DAYS', style_header)
ws.write(3, 26, 'SALARY', style_header)
ws.write(3, 27, 'DAYS', style_header)
ws.write(3, 28, 'SALARY', style_header)
ws.write(3, 29, '', style_header)
if obj.partner_id:
list_ids = emp_obj.search(cr, uid,
[('partner_id', '=', obj.partner_id.id),
('active', '=', True),
('type', '=', 'Contractor')])
list_ids1 = emp_obj.search(cr, uid,
[('partner_id', '=', obj.partner_id.id),
('active', '=', False),
('type', '=', 'Contractor')])
list_ids = list_ids + list_ids1
elif obj.employee_id:
list_ids = emp_obj.search(cr, uid,
[('id', '=', obj.employee_id.id),
('active', '=', True),
('type', '=', 'Contractor')])
elif obj.company_id:
list_ids = emp_obj.search(cr, uid,
[('company_id', '=', obj.company_id.id),
('active', '=', True),
('type', '=', 'Contractor')])
list_ids1 = emp_obj.search(cr, uid,
[('company_id', '=', obj.company_id.id),
('active', '=', False),
('type', '=', 'Contractor')])
list_ids = list_ids + list_ids1
else:
raise osv.except_osv(
_('Warning !'),
_("Please Select Atleast Company Or Employee."))
if len(list_ids) == 1:
query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\
"sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\
"sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\
"sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\
"from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\
"where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid "
cr.execute(query)
temp = cr.fetchall()
else:
query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\
"sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\
"sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\
"sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\
"from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\
"where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid"
cr.execute(query)
temp = cr.fetchall()
if not temp:
raise osv.except_osv(_('Warning !'), _("Record Not Found !!!"))
columnno = 4
for val in temp:
doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y")
ws.write(columnno, 0, val[0], style_header5)
ws.write(columnno, 1, val[1], style_header5)
ws.write(columnno, 2, doj, style_header5)
ws.write(columnno, 3, val[3], style_header5)
ws.write(columnno, 4, val[17], style_header5)
ws.write(columnno, 5, val[4], style_header5)
ws.write(columnno, 6, val[18], style_header5)
ws.write(columnno, 7, val[5], style_header5)
ws.write(columnno, 8, val[19], style_header5)
ws.write(columnno, 9, val[6], style_header5)
ws.write(columnno, 10, val[20], style_header5)
ws.write(columnno, 11, val[7], style_header5)
ws.write(columnno, 12, val[21], style_header5)
ws.write(columnno, 13, val[8], style_header5)
ws.write(columnno, 14, val[22], style_header5)
ws.write(columnno, 15, val[9], style_header5)
ws.write(columnno, 16, val[23], style_header5)
ws.write(columnno, 17, val[10], style_header5)
ws.write(columnno, 18, val[24], style_header5)
ws.write(columnno, 19, val[11], style_header5)
ws.write(columnno, 20, val[25], style_header5)
ws.write(columnno, 21, val[12], style_header5)
ws.write(columnno, 22, val[26], style_header5)
ws.write(columnno, 23, val[13], style_header5)
ws.write(columnno, 24, val[27], style_header5)
ws.write(columnno, 25, val[14], style_header5)
ws.write(columnno, 26, val[28], style_header5)
ws.write(columnno, 27, val[15], style_header5)
ws.write(columnno, 28, val[29], style_header5)
ws.write(columnno, 29, val[16], style_header5)
columnno += 1
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
return self.write(cr,
uid,
ids, {
'export_data': out,
'filename': 'Contractor Payment Bonus.xls'
},
context=context)
def performance_register_report(self, cr, uid, ids, data, context=None):
obj = self.browse(cr, uid, ids)
emp_obj = self.pool.get('hr.employee')
f_name = ''
d_name = ''
wb = Workbook()
ws = wb.add_sheet('Payment Bonus')
total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.height = 300
fnt1.bold = True
align_content1 = Alignment()
align_content1.horz = Alignment.HORZ_CENTER
borders1 = Borders()
borders1.left = 0x00
borders1.right = 0x00
borders1.top = 0x00
borders1.bottom = 0x00
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER
pattern1 = Pattern()
pattern1.pattern1 = Pattern.SOLID_PATTERN
pattern1.pattern1_fore_colour = 0x1F
style_header1 = XFStyle()
style_header1.font = fnt1
style_header1.pattern = pattern1
style_header1.borders = borders1
style_header1.alignment = align1
fnt = Font()
fnt.name = 'Arial'
fnt.height = 275
content_fnt = Font()
content_fnt.name = 'Arial'
content_fnt.height = 150
align_content = Alignment()
align_content.horz = Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x02
borders.right = 0x02
borders.top = 0x02
borders.bottom = 0x00
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
style_header = XFStyle()
style_header.font = fnt
style_header.pattern = pattern
style_header.borders = borders
style_header.alignment = align
fnt7 = Font()
fnt7.name = 'Arial'
fnt7.height = 275
content_fnt7 = Font()
content_fnt7.name = 'Arial'
content_fnt7.height = 150
align_content7 = Alignment()
align_content7.horz = Alignment.HORZ_CENTER
borders7 = Borders()
borders7.left = 0x02
borders7.right = 0x02
borders7.top = 0x02
borders7.bottom = 0x02
align7 = Alignment()
align7.horz = Alignment.HORZ_CENTER
align7.vert = Alignment.VERT_CENTER
pattern7 = Pattern()
pattern7.pattern7 = Pattern.SOLID_PATTERN
pattern7.pattern7_fore_colour = 0x1F
style_header7 = XFStyle()
style_header7.font = fnt
style_header7.pattern = pattern
style_header7.borders = borders
style_header7.alignment = align
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.height = 275
content_fnt2 = Font()
content_fnt2.name = 'Arial'
content_fnt2.height = 150
fnt2.bold = True
align_content2 = Alignment()
align_content2.horz = Alignment.HORZ_CENTER
borders2 = Borders()
borders2.left = 0x00
borders2.right = 0x02
borders2.top = 0x02
borders2.bottom = 0x02
align2 = Alignment()
align2.horz = Alignment.HORZ_CENTER
align2.vert = Alignment.VERT_CENTER
pattern2 = Pattern()
pattern2.pattern2 = Pattern.SOLID_PATTERN
pattern2.pattern2_fore_colour = 0x1F
style_header2 = XFStyle()
style_header2.font = fnt2
style_header2.pattern = pattern2
style_header2.borders = borders2
style_header2.alignment = align2
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.height = 300
fnt3.bold = True
align_content3 = Alignment()
align_content3.horz = Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x00
borders3.right = 0x02
borders3.top = 0x02
borders3.bottom = 0x02
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
pattern3 = Pattern()
pattern3.pattern3 = Pattern.SOLID_PATTERN
pattern3.pattern3_fore_colour = 0x1F
style_header3 = XFStyle()
style_header3.font = fnt3
style_header3.pattern = pattern3
style_header3.borders = borders3
style_header3.alignment = align3
fnt6 = Font()
fnt6.name = 'Arial'
fnt6.height = 275
content_fnt6 = Font()
content_fnt6.name = 'Arial'
content_fnt6.height = 150
align_content6 = Alignment()
align_content6.horz = Alignment.HORZ_CENTER
borders6 = Borders()
borders6.left = 0x02
borders6.right = 0x02
# borders6.top = 0x00
# borders6.bottom = 0x00
align6 = Alignment()
align6.horz = Alignment.HORZ_CENTER
align6.vert = Alignment.VERT_CENTER
pattern6 = Pattern()
pattern6.pattern = Pattern.SOLID_PATTERN
pattern6.pattern_fore_colour = 0x1F
style_header6 = XFStyle()
style_header6.font = fnt
style_header6.pattern = pattern
style_header6.borders = borders6
style_header6.alignment = align
fnt5 = Font()
fnt5.name = 'Arial'
fnt5.height = 200
content_fnt5 = Font()
content_fnt5.name = 'Arial'
content_fnt5.height = 150
align_content5 = Alignment()
align_content5.horz = Alignment.HORZ_CENTER
borders5 = Borders()
borders5.left = 0x02
borders5.right = 0x02
borders5.top = 0x02
borders5.bottom = 0x02
align5 = Alignment()
align5.horz = Alignment.HORZ_CENTER
align5.vert = Alignment.VERT_CENTER
pattern5 = Pattern()
# pattern5.pattern = Pattern.SOLID_PATTERN
# pattern5.pattern_fore_colour = 0x1F
style_header5 = XFStyle()
style_header5.font = fnt5
style_header5.pattern = pattern5
style_header5.borders = borders5
style_header5.alignment = align5
if obj.company_id:
get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip
else:
get_name = obj.employee_id.resource_id.company_id.name + ' ' + obj.employee_id.resource_id.company_id.street + ' ' + ',' + obj.employee_id.resource_id.company_id.city + ' ' + '-' + obj.employee_id.resource_id.company_id.zip
date1 = datetime.strptime(obj.from_date,
"%Y-%m-%d").timetuple().tm_year
date2 = datetime.strptime(obj.till_date,
"%Y-%m-%d").timetuple().tm_year
if date1 == date2:
d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' - ' + str(
date1) + ' ' + '[See Rule 4(b)]'
else:
d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' ' + str(
date1) + ' ' + '-' + ' ' + str(date2) + ' ' + '[See Rule 4(b)]'
ws.row(0).height = 500
ws.row(1).height = 500
ws.write_merge(0, 0, 0, 2, 'FORM C', style_header1)
ws.write_merge(0, 0, 3, 10, d_name, style_header1)
ws.write_merge(1, 1, 0, 2, 'Name of the Establishment', style_header1)
ws.write_merge(1, 1, 3, 10, get_name, style_header1)
ws.write_merge(1, 1, 11, 19, 'No. of Working days in the Year',
style_header1)
ws.col(0).width = 5000
ws.col(1).width = 7500
ws.col(2).width = 5000
ws.col(3).width = 7000
ws.col(4).width = 5000
ws.col(5).width = 8000
ws.col(6).width = 6000
ws.col(7).width = 8000
ws.col(8).width = 8000
ws.col(9).width = 9000
ws.col(10).width = 8000
ws.col(11).width = 9000
ws.col(12).width = 7000
ws.col(13).width = 7000
ws.col(14).width = 5000
ws.col(15).width = 5000
ws.col(16).width = 7000
ws.col(17).width = 3000
ws.col(18).width = 3000
ws.col(19).width = 3000
ws.col(20).width = 3000
ws.col(21).width = 3000
ws.col(22).width = 3000
ws.col(23).width = 3000
ws.col(24).width = 3000
ws.col(25).width = 3000
ws.col(26).width = 3000
ws.col(27).width = 3000
ws.col(28).width = 3000
ws.col(29).width = 4000
ws.row(2).height = 400
ws.write(2, 0, 'EMP. CODE', style_header)
ws.write(2, 1, 'NAME', style_header)
ws.write(2, 2, 'JOINING DATE', style_header)
ws.write(2, 3, 'Father Name', style_header)
ws.write(2, 4, 'Designation', style_header)
ws.write(2, 5, 'Whether he has ', style_header)
ws.write(2, 6, 'No.of days', style_header)
ws.write(2, 7, 'Total Salary or wages', style_header)
ws.write(2, 8, 'Account of bonus payable', style_header)
ws.write_merge(2, 2, 9, 12, 'Deduction', style_header7)
ws.write(2, 13, 'Net amount payable', style_header)
ws.write(2, 14, 'Amount actualy', style_header)
ws.write(2, 15, 'Date on which ', style_header)
ws.write(2, 16, 'Signature/ Thumb ', style_header)
ws.write(2, 17, 'Remarks', style_header)
ws.write_merge(2, 2, 18, 19, 'APRIL', style_header7)
ws.write_merge(2, 2, 20, 21, 'MAY', style_header7)
ws.write_merge(2, 2, 22, 23, 'JUNE', style_header7)
ws.write_merge(2, 2, 24, 25, 'JULY', style_header7)
ws.write_merge(2, 2, 26, 27, 'AUGUST', style_header7)
ws.write_merge(2, 2, 28, 29, 'SEPTEMBER', style_header7)
ws.write_merge(2, 2, 30, 31, 'OCTOBER', style_header7)
ws.write_merge(2, 2, 32, 33, 'NOVEMBER', style_header7)
ws.write_merge(2, 2, 34, 35, 'DECEMBER', style_header7)
ws.write_merge(2, 2, 36, 37, 'JANUARY', style_header7)
ws.write_merge(2, 2, 38, 39, 'FEBRUARY', style_header7)
ws.write_merge(2, 2, 40, 41, 'MARCH', style_header7)
ws.write_merge(2, 2, 42, 43, 'TOTAL', style_header7)
ws.write(2, 44, 'BONUS', style_header)
ws.row(3).height = 400
ws.write(3, 0, '', style_header6)
ws.write(3, 1, '', style_header6)
ws.write(3, 2, '', style_header6)
ws.write(3, 3, '', style_header6)
ws.write(3, 4, '', style_header6)
ws.write(3, 5, 'completed 15 year of', style_header6)
ws.write(3, 6, 'worked in the', style_header6)
ws.write(3, 7, 'in respect of', style_header6)
ws.write(3, 8, 'under section 10', style_header6)
ws.write(3, 9, 'Puja bonus or other customary', style_header7)
ws.write(3, 10, 'Interim bonus', style_header7)
ws.write(3, 11, 'Deduction on account of financial', style_header7)
ws.write(3, 12, 'Total sum deducted', style_header7)
ws.write(3, 13, '(Col.8 minus Col.12)', style_header6)
ws.write(3, 14, 'paid', style_header6)
ws.write(3, 15, 'paid', style_header6)
ws.write(3, 16, 'impression', style_header6)
ws.write(3, 17, '', style_header6)
ws.write(3, 18, '', style_header7)
ws.write(3, 19, '', style_header7)
ws.write(3, 20, '', style_header7)
ws.write(3, 21, '', style_header7)
ws.write(3, 22, '', style_header7)
ws.write(3, 23, '', style_header7)
ws.write(3, 24, '', style_header7)
ws.write(3, 25, '', style_header7)
ws.write(3, 26, '', style_header7)
ws.write(3, 27, '', style_header7)
ws.write(3, 28, '', style_header7)
ws.write(3, 29, '', style_header7)
ws.write(3, 30, '', style_header7)
ws.write(3, 31, '', style_header7)
ws.write(3, 32, '', style_header7)
ws.write(3, 33, '', style_header7)
ws.write(3, 34, '', style_header7)
ws.write(3, 35, '', style_header7)
ws.write(3, 36, '', style_header7)
ws.write(3, 37, '', style_header7)
ws.write(3, 38, '', style_header7)
ws.write(3, 39, '', style_header7)
ws.write(3, 40, '', style_header7)
ws.write(3, 41, '', style_header7)
ws.write(3, 42, '', style_header7)
ws.write(3, 43, '', style_header7)
ws.write(3, 44, '', style_header7)
ws.row(4).height = 400
ws.write(4, 0, '', style_header6)
ws.write(4, 1, '', style_header6)
ws.write(4, 2, '', style_header6)
ws.write(4, 3, '', style_header6)
ws.write(4, 4, '', style_header6)
ws.write(4, 5, 'age at the beginning', style_header6)
ws.write(4, 6, 'Establishment', style_header6)
ws.write(4, 7, 'the accounting year', style_header6)
ws.write(4, 8, 'or section 11', style_header6)
ws.write(4, 9, 'bonus paid during', style_header6)
ws.write(4, 10, 'or bonus paid in ', style_header6)
ws.write(4, 11, 'loss if any caused by', style_header6)
ws.write(4, 12, '(Col.9,10 and 11)', style_header6)
ws.write(4, 13, '', style_header6)
ws.write(4, 14, '', style_header6)
ws.write(4, 15, '', style_header6)
ws.write(4, 16, 'of the employee', style_header6)
ws.write(4, 17, '', style_header6)
ws.write(4, 18, '', style_header6)
ws.write(4, 19, '', style_header6)
ws.write(4, 20, '', style_header6)
ws.write(4, 21, '', style_header6)
ws.write(4, 22, '', style_header6)
ws.write(4, 23, '', style_header6)
ws.write(4, 24, '', style_header6)
ws.write(4, 25, '', style_header6)
ws.write(4, 26, '', style_header6)
ws.write(4, 27, '', style_header6)
ws.write(4, 28, '', style_header6)
ws.write(4, 29, '', style_header6)
ws.write(4, 30, '', style_header6)
ws.write(4, 31, '', style_header6)
ws.write(4, 32, '', style_header6)
ws.write(4, 33, '', style_header6)
ws.write(4, 34, '', style_header6)
ws.write(4, 35, '', style_header6)
ws.write(4, 36, '', style_header6)
ws.write(4, 37, '', style_header6)
ws.write(4, 38, '', style_header6)
ws.write(4, 39, '', style_header6)
ws.write(4, 40, '', style_header6)
ws.write(4, 41, '', style_header6)
ws.write(4, 42, '', style_header6)
ws.write(4, 43, '', style_header6)
ws.write(4, 44, '', style_header6)
ws.row(5).height = 400
ws.write(5, 0, '', style_header6)
ws.write(5, 1, '', style_header6)
ws.write(5, 2, '', style_header6)
ws.write(5, 3, '', style_header6)
ws.write(5, 4, '', style_header6)
ws.write(5, 5, 'of the accounting year', style_header6)
ws.write(5, 6, '', style_header6)
ws.write(5, 7, '', style_header6)
ws.write(5, 8, 'as the case may be', style_header6)
ws.write(5, 9, 'the accounting year', style_header6)
ws.write(5, 10, 'advance', style_header6)
ws.write(5, 11, 'misconduct of the employee', style_header6)
ws.write(5, 12, '', style_header6)
ws.write(5, 13, '', style_header6)
ws.write(5, 14, '', style_header6)
ws.write(5, 15, '', style_header6)
ws.write(5, 16, '', style_header6)
ws.write(5, 17, '', style_header6)
ws.write(5, 18, 'DAYS', style_header6)
ws.write(5, 19, 'SALARY', style_header6)
ws.write(5, 20, 'DAYS', style_header6)
ws.write(5, 21, 'SALARY', style_header6)
ws.write(5, 22, 'DAYS', style_header6)
ws.write(5, 23, 'SALARY', style_header6)
ws.write(5, 24, 'DAYS', style_header6)
ws.write(5, 25, 'SALARY', style_header6)
ws.write(5, 26, 'DAYS', style_header6)
ws.write(5, 27, 'SALARY', style_header6)
ws.write(5, 28, 'DAYS', style_header6)
ws.write(5, 29, 'SALARY', style_header6)
ws.write(5, 30, 'DAYS', style_header6)
ws.write(5, 31, 'SALARY', style_header6)
ws.write(5, 32, 'DAYS', style_header6)
ws.write(5, 33, 'SALARY', style_header6)
ws.write(5, 34, 'DAYS', style_header6)
ws.write(5, 35, 'SALARY', style_header6)
ws.write(5, 36, 'DAYS', style_header6)
ws.write(5, 37, 'SALARY', style_header6)
ws.write(5, 38, 'DAYS', style_header6)
ws.write(5, 39, 'SALARY', style_header6)
ws.write(5, 40, 'DAYS', style_header6)
ws.write(5, 41, 'SALARY', style_header6)
ws.write(5, 42, 'DAYS', style_header6)
ws.write(5, 43, 'SALARY', style_header6)
ws.write(5, 44, '', style_header6)
if obj.company_id:
list_ids = emp_obj.search(cr, uid,
[('company_id', '=', obj.company_id.id),
('active', '=', True),
('type', '=', 'Employee')])
list_ids1 = emp_obj.search(cr, uid,
[('company_id', '=', obj.company_id.id),
('active', '=', False),
('type', '=', 'Employee')])
list_ids = list_ids + list_ids1
elif obj.employee_id:
list_ids = emp_obj.search(cr, uid,
[('id', '=', obj.employee_id.id),
('active', '=', True),
('type', '=', 'Employee')])
else:
raise osv.except_osv(
_('Warning !'),
_("Please Select Atleast Company Or Employee."))
if len(list_ids) == 1:
query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\
"sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\
"sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\
"sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\
"from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\
"where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid "
cr.execute(query)
temp = cr.fetchall()
else:
query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\
"sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\
"sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\
"sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\
"from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\
"where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid"
cr.execute(query)
temp = cr.fetchall()
if not temp:
raise osv.except_osv(_('Warning !'), _("Record Not Found !!!"))
columnno = 6
no = 6
for val in temp:
father_name = ''
if val[4]:
father_qry = "select name from family where relation='Father' and employee_id='" + str(
val[4]) + "' "
cr.execute(father_qry)
father_temp = cr.fetchall()
if father_temp:
father_name = father_temp[0][0]
else:
father_name = ' '
ws.row(no).height = 500
doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y")
ws.write(columnno, 0, val[0], style_header5)
ws.write(columnno, 1, val[1], style_header5)
ws.write(columnno, 2, doj, style_header5)
ws.write(columnno, 3, father_name, style_header5)
ws.write(columnno, 4, val[3], style_header5)
ws.write(columnno, 5, 'Yes', style_header5)
ws.write(columnno, 6, '', style_header5)
ws.write(columnno, 7, '', style_header5)
ws.write(columnno, 8, '', style_header5)
ws.write(columnno, 9, '', style_header5)
ws.write(columnno, 10, '', style_header5)
ws.write(columnno, 11, '', style_header5)
ws.write(columnno, 12, '', style_header5)
ws.write(columnno, 13, '', style_header5)
ws.write(columnno, 14, '', style_header5)
ws.write(columnno, 15, '', style_header5)
ws.write(columnno, 16, '', style_header5)
ws.write(columnno, 17, '', style_header5)
ws.write(columnno, 18, val[5], style_header5)
ws.write(columnno, 19, val[19], style_header5)
ws.write(columnno, 20, val[6], style_header5)
ws.write(columnno, 21, val[20], style_header5)
ws.write(columnno, 22, val[7], style_header5)
ws.write(columnno, 23, val[21], style_header5)
ws.write(columnno, 24, val[8], style_header5)
ws.write(columnno, 25, val[22], style_header5)
ws.write(columnno, 26, val[9], style_header5)
ws.write(columnno, 27, val[23], style_header5)
ws.write(columnno, 28, val[10], style_header5)
ws.write(columnno, 29, val[24], style_header5)
ws.write(columnno, 30, val[11], style_header5)
ws.write(columnno, 31, val[25], style_header5)
ws.write(columnno, 32, val[12], style_header5)
ws.write(columnno, 33, val[26], style_header5)
ws.write(columnno, 34, val[13], style_header5)
ws.write(columnno, 35, val[27], style_header5)
ws.write(columnno, 36, val[14], style_header5)
ws.write(columnno, 37, val[28], style_header5)
ws.write(columnno, 38, val[15], style_header5)
ws.write(columnno, 39, val[29], style_header5)
ws.write(columnno, 40, val[16], style_header5)
ws.write(columnno, 41, val[30], style_header5)
ws.write(columnno, 42, val[17], style_header5)
ws.write(columnno, 43, val[31], style_header5)
ws.write(columnno, 44, val[18], style_header5)
columnno += 1
no += 1
f = cStringIO.StringIO()
wb.save(f)
out = base64.encodestring(f.getvalue())
return self.write(cr,
uid,
ids, {
'export_data': out,
'filename': 'Payment Bonus.xls'
},
context=context)
def write_cursor_to_excel(curs, filename, sheetTitle):
"""write_cursor_to_excel
curs: a cursor for an open connection to an oracle database
filename: name of the XLS file to create
sheetTitle: name of the sheet to create
"""
# create style for header row - bold font, thin border below
fnt = Font()
fnt.bold = True
borders = Borders()
borders.bottom = Borders.THIN
hdrstyle = XFStyle()
hdrstyle.font = fnt
hdrstyle.borders = borders
# create a date format style for any date columns, if any
datestyle = XFStyle()
datestyle.num_format_str = 'DD/MM/YYYY'
# create the workbook. (compression: try to reduce the number of repeated styles)
wb = Workbook(style_compression=2)
# the workbook will have just one sheet
sh = wb.add_sheet(sheetTitle)
# write the header line, based on the cursor description
c = 0
colWidth = []
for col in curs.description:
#col[0] is the column name
#col[1] is the column data type
sh.write(0, c, col[0], hdrstyle)
colWidth.append(1) # arbitrary min cell width
if col[1] == cx_Oracle.DATETIME:
colWidth[-1] = len(datestyle.num_format_str)
if colWidth[-1] < len(col[0]):
colWidth[-1] = len(col[0])
c += 1
# write the songs, one to each row
r = 1
for song in curs:
row = sh.row(r)
for c in range(len(song)):
if song[c]:
if curs.description[c][1] == cx_Oracle.DATETIME:
row.write(c, song[c], datestyle)
else:
if colWidth[c] < len(str(song[c])):
colWidth[c] = len(str(song[c]))
row.write(c, song[c])
r += 1
for c in range(len(colWidth)):
sh.col(c).width = colWidth[c] * 350
# freeze the header row
sh.panes_frozen = True
sh.vert_split_pos = 0
sh.horz_split_pos = 1
wb.save(filename)
def CreateExcelSpreadsheet(table, output_excel, use_alias=True):
"""Exports table to excel
Required:
table -- input table
output_excel -- output excel table (.xlsx, .xls)
Optional:
use_alias -- use field alias name for column headers. Default is True
"""
# build field dict
fieldNames = [(f.name, f.aliasName) for f in arcpy.ListFields(table) if f.type != 'Geometry']
fields = [f[1] if use_alias in ('true', True) else f[0] for f in fieldNames]
widths = {i: arial10.fitwidth(f) + 1024 for i,f in enumerate(fields)}
# get field values *Changed from type dict to list
with arcpy.da.SearchCursor(table, [f[0] for f in fieldNames]) as rows:
values = [r for r in rows]
# Create spreadsheet
wb = Workbook()
ws = wb.add_sheet('Sheet 1')
cols = len(fields)
rows = len(values) + 1
# set styles
#***************************************************************************
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.borders = borders
# headers
fntHeaders = Font()
fntHeaders.bold = True
fntHeaders.height = 220
styleHeaders = XFStyle()
styleHeaders.font = fntHeaders
styleHeaders.borders = borders
# for date fields
styleDate = XFStyle()
styleDate.borders = borders
styleDate.num_format_str = 'MM/DD/YYYY'
#***************************************************************************
# write headers and freeze panes
for ci,field in enumerate(fields):
ws.write(0, ci, field, styleHeaders)
# freeze headers
ws.set_panes_frozen(True)
ws.set_horz_split_pos(1)
# fill in values
start = 1
for vals in values:
for i, value in enumerate(vals):
ws.write(start, i, value, styleDate if isinstance(value, datetime.datetime) else style)
v_width = arial10.fitwidth(str(value).strip())
if v_width > widths[i]:
widths[i] = v_width
start += 1
if not start % 1000:
ws.flush_row_data()
# autofit column widths
for ci,width in widths.iteritems():
ws.col(ci).width = int(width + 256) # just a little more padding
# save workbook
wb.save(output_excel)
del wb
out_url = '/'.join([PATH_URL, os.path.basename(out_file)])
arcpy.SetParameter(2, out_url)
arcpy.AddMessage(out_url)
return out_url
def exportToXls(self):
# opening file dialog
fileName = QFileDialog.getSaveFileName(self,
'Save as', RES, 'Microsoft Excel Spreadsheet (*.xls)')
if fileName.count() > 0:
try:
COLUMN_WIDTH = 3000
alignment = Alignment()
alignment.horizontal = Alignment.HORZ_CENTER
alignment.vertical = Alignment.VERT_CENTER
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.alignment = alignment
style.borders = borders
font = Font()
font.bold = True
headerStyle = XFStyle()
headerStyle.font = font
separate = Borders()
separate.left = Borders.THIN
separate.right = Borders.DOUBLE
separate.top = Borders.THIN
separate.bottom = Borders.THIN
separateStyle = XFStyle()
separateStyle.borders = separate
book = Workbook(encoding='utf-8')
# modelling data
dec_sheet = book.add_sheet('Data decomposition')
# decomposition data
# initial data
column = 0
row = 0
dec_sheet.write(row, column, 'Time series', headerStyle)
dec_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.data[0]:
dec_sheet.write(row, column, item, separateStyle)
row += 1
# decomposition
for lvl in self.wCoefficients:
row = 0
column += 1
dec_sheet.write(row, column, 'Level' + str(column - 1), headerStyle)
dec_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in lvl:
dec_sheet.write(row, column, item, style)
row += 1
# decomposition graphs
pass
levels_sheet = book.add_sheet('Multiscale forecast')
# levels data
column = 0
for lvl in self.forecast:
row = 0
levels_sheet.write(row, column, 'Level' + str(column), headerStyle)
levels_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in lvl[1]:
levels_sheet.write(row, column, float(item), style)
row += 1
column += 1
result_sheet = book.add_sheet('Results')
# initial
column = 0
row = 0
result_sheet.write(row, column, 'Initial data', headerStyle)
result_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.data[0]:
result_sheet.write(row, column, item, separateStyle)
row += 1
# forecast
row = 0
column += 1
result_sheet.write(row, column, 'Forecast', headerStyle)
result_sheet.col(column).width = COLUMN_WIDTH
row += 1
for item in self.inverseWT():
result_sheet.write(row, column, item, style)
row += 1
row = 0
column = 2
self.updateGraph()
self.plotResult.saveFigure('forecast', format='bmp')
result_sheet.insert_bitmap(RES + TEMP + 'forecast.bmp', row, column)
# saving xls
try:
book.save(unicode(fileName))
except Exception:
pass
except Exception, e:
pass
################################### # configuration du fichier excel de sortie book = Workbook(encoding = 'utf8') feuy1 = book.add_sheet(u'Dossiers', cell_overwrite_ok=True) feuy2 = book.add_sheet(u'Shapes', cell_overwrite_ok=True) feuy3 = book.add_sheet(u'Champs', cell_overwrite_ok=True) feuy4 = book.add_sheet(u'MXD', cell_overwrite_ok=True) feuy5 = book.add_sheet(u'Metadatos', cell_overwrite_ok=True) # personnalisation du fichier excel font1 = Font() # cr?ation police 1 font1.name = 'Times New Roman' font1.bold = True entete = XFStyle() # cr?ation style pour les en-t?tes entete.font = font1 # application de la police 1 au style entete hyperlien = easyxf(u'font: underline single') erreur = easyxf(u'font: name Arial, bold 1, colour red') # colonnes feuille dossiers feuy1.write(0, 0, u'Nom actuel', entete) feuy1.write(0, 1, u'Nom chang?', entete) feuy1.write(0, 2, u'Type g?om?trie', entete) feuy1.write(0, 3, u'Emprise', entete) feuy1.write(0, 4, u'Projection', entete) feuy1.write(0, 5, u'EPSG', entete) feuy1.write(0, 6, u'Nombre de champs', entete) feuy1.write(0, 7, u'Nombre d\'objets', entete) feuy1.write(0, 8, u'Ann?e de l\'information', entete) feuy1.write(0, 9, u'Date derni?re actualisation', entete) feuy1.write(0, 10, u'Liste des champs', entete)
def makeXLS(self, id_relatorio, id_promocao=None):
"""
cria o arquivo xls
a var index é utilizada para
controlar em qual linha entrara o conteudo
"""
timename = str(util.dtnow('%d/%m/%Y %H:%M%S')).replace("/", "")
timename = timename.replace(":", "")
timename = "_" + timename.replace(" ", "")
site = getDadosSite(id_site=self.id_site,
request=self.request)
base = site["base_app"]
if not base.endswith("/"):
base = base + "/"
if int(id_relatorio) == 1:
titulo = 'Relacao_usuario_participacao'
elif int(id_relatorio) == 2:
titulo = 'Relacao_participantes_contemplados'
elif int(id_relatorio) == 4:
titulo = 'Dados_usuarios_por_promocao'
else:
titulo = 'Relacao_participantes_promocao'
saveplace = ("{0}/ns{1}/arquivos/tmp/"
"{2}{3}.xls").format(str(settings.PATH_FILES),
str(self.id_site),
titulo,
timename)
saveurl = "{0}tmp/{1}{2}.xls".format(
base, titulo, timename)
book = Workbook()
font = Font()
font.bold = True
style = XFStyle()
style.font = font
style0 = XFStyle()
style0.font = font
alignment = Alignment()
alignment.horz = Alignment.HORZ_CENTER
style0.alignment = alignment
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 22
style0.pattern = pattern
index = 0
sheet = book.add_sheet('promocoes')
# sheet
if int(id_relatorio) == 1 or int(id_relatorio) == 3:
promocoes = self._getConteudo()
if int(id_relatorio) == 1:
campos_s1 = [{"nome": "Nome"},
{"email": "email"},
{"cpf": "CPF"},
{"quantidade de participacoes": u"Quantidade de participações"},
{"status":"status"},
{"bloqueio":"bloqueado"}]
sheet.write_merge(0, 0, 0, 5, u"Relação usuário participação por promoção", style0)
else:
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"data_hora":u"Hora participação"}]
sheet.write_merge(0, 0, 0, 5, u"Dados de usuários por promoção", style0)
total_geral = 0
count = 0
for i in promocoes:
count_participacoes = self._getCountParticipacoes(i['id_conteudo'])
count += count_participacoes
index += 1
titulo = i['titulo']
sheet.write_merge(index, index, 0, 5, titulo.decode("latin1"), style0)
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
cont = 0
if int(id_relatorio) == 1:
for y in self.execSql("select_usuarios_promocoes_all",
id_conteudo=int(i['id_conteudo'])):
sheet.write(index + 1,
0,
self.dec(y['nome']))
sheet.write(index + 1,
1,
self.dec(y['email']))
sheet.write(index + 1,
2,
self.dec(y['cpf']))
sheet.write(index + 1,
3,
self.dec(y['total']))
sheet.write(index + 1,
4,
self.dec(y['status']))
if y['bloqueio']:
bloqueio = 'Sim'
else:
bloqueio = 'Não'
sheet.write(index + 1,
5,
self.dec(bloqueio))
index += 1
cont += 1
if int(id_relatorio) == 3:
for y in self.execSql("select_usuarios_promocoes_all2",
id_conteudo=int(i['id_conteudo'])):
endereco = "{0}, {1}, {2}, {3}, {4}". format(y['endereco'],
y['numero'],
y['bairro'],
y['estado'],
y['cep'])
sheet.write(index + 1,
0,
self.dec(y['nome']))
sheet.write(index + 1,
1,
self.dec(y['email']))
sheet.write(index + 1,
2,
self.dec(y['cpf']))
sheet.write(index + 1,
3,
self.dec(y['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
y['dhora_participacao'])
index += 1
cont += 1
if cont:
index += 2
sheet.write(index, 0, 'Total', style)
sheet.write(index,
1,
cont)
total_geral += cont
index += 1
sheet.write(index, 0, u'Total Participações', style)
sheet.write(index,
1,
count_participacoes)
else:
index += 2
sheet.write(index, 0, 'Total', style)
sheet.write(index,
1,
cont)
index += 2
index += 6
sheet.write_merge(index, index, 0, 1, "Total Geral", style0)
index += 1
sheet.write(index, 0, "Total de Participantes", style)
sheet.write(index, 1, self.dec(total_geral), style)
index += 1
sheet.write(index, 0, u"Total de Participações Geral", style)
sheet.write(index, 1, self.dec(count), style)
else:
if int(id_relatorio) == 4:
cont = 0
titulo_promo = self._getConteudo(id_promocao)['titulo']
sheet.write_merge(0, 0, 0, 5, u"Relação de partcipantes da promoção " + titulo_promo.decode('latin1'), style0)
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"status":"Status"}
]
saveplace = ("{0}/ns{1}/arquivos/tmp/{2}{3}.xls").format(str(settings.PATH_FILES),
str(self.id_site),
unicode(titulo + '_' + titulo_promo, errors='ignore'),
timename)
saveurl = "{0}tmp/{1}{2}.xls".format(base, unicode(titulo + '_' + titulo_promo, errors='ignore'), timename)
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
for i in self.execSql("select_usuarios_promocoes_all2",
id_conteudo=int(id_promocao)):
endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'],
i['numero'],
i['bairro'],
i['estado'],
i['cep'])
sheet.write(index + 1,
0,
self.dec(i['nome']))
sheet.write(index + 1,
1,
self.dec(i['email']))
sheet.write(index + 1,
2,
self.dec(i['cpf']))
sheet.write(index + 1,
3,
self.dec(i['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
self.dec(i['status']))
index += 1
cont += 1
else:
cont = 0
sheet.write_merge(0, 0, 0, 5, u"Relação de usuários contemplados", style0)
campos_s1 = [{"nome":"Nome"},
{"email":"email"},
{"cpf":"CPF"},
{"telefone":"Telefone"},
{"endereco":u"Endereço"},
{"Contemplações":u"Contemplações"}]
index += 1
for j in range(len(campos_s1)):
sheet.write(index, j, campos_s1[j].values()[0], style)
sheet.col(j).width = 30 * 256
for i in self.execSql("select_contemplados"):
endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'],
i['numero'],
i['bairro'],
i['estado'],
i['cep'])
sheet.write(index + 1,
0,
self.dec(i['nome']))
sheet.write(index + 1,
1,
self.dec(i['email']))
sheet.write(index + 1,
2,
self.dec(i['cpf']))
sheet.write(index + 1,
3,
self.dec(i['telefone']))
sheet.write(index + 1,
4,
self.dec(endereco))
sheet.write(index + 1,
5,
self.dec(i['total']))
index += 1
cont += 1
index += 6
sheet.write(index, 0, u"Total de usuários", style)
sheet.write(index, 1, self.dec(cont), style)
book.save(saveplace)
return saveurl
def salary_payment_report(self,cr,uid,ids,context=None):
#Define the font attributes for header
fnt = Font()
fnt.name = 'Arial'
fnt.size=16
fnt.style= 'Regular'
#Define the font attributes for header
content_fnt = Font()
content_fnt.name ='Arial'
content_fnt.size=16
content_fnt.style= 'Regular'
align_content = Alignment()
align_content.horz= Alignment.HORZ_CENTER
borders = Borders()
borders.left = 0x01
borders.right = 0x01
borders.top = 0x01
borders.bottom = 0x01
#The text should be centrally aligned
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x1F
#apply the above settings to the row(0) header
style_header= XFStyle()
style_header.font= fnt
style_header.pattern= pattern
style_header.borders = borders
style_header.alignment=align
#Define the font attributes for header
fnt3 = Font()
fnt3.name = 'Arial'
fnt3.size=16
fnt3.style= 'Regular'
#Define the font attributes for header
content_fnt3 = Font()
content_fnt3.name ='Arial'
content_fnt3.size=16
content_fnt3.style= 'Regular'
align_content3 = Alignment()
align_content3.horz= Alignment.HORZ_CENTER
borders3 = Borders()
borders3.left = 0x01
borders3.right = 0x01
borders3.top = 0x01
borders3.bottom = 0x01
#The text should be centrally aligned
align3 = Alignment()
align3.horz = Alignment.HORZ_CENTER
align3.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern3 = Pattern()
#apply the above settings to the row(0) header
style_header3= XFStyle()
style_header3.font= fnt3
style_header3.pattern= pattern3
style_header3.borders = borders3
style_header3.alignment=align3
#Define the font attributes for header
fnt4 = Font()
fnt4.name = 'Arial'
#Define the font attributes for header
content_fnt4 = Font()
content_fnt4.name ='Arial'
align_content4 = Alignment()
align_content4.horz= Alignment.HORZ_LEFT
borders4 = Borders()
borders4.left = 0x01
borders4.right = 0x01
borders4.top = 0x01
borders4.bottom = 0x01
#The text should be centrally aligned
align4 = Alignment()
align4.horz = Alignment.HORZ_LEFT
align4.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern4 = Pattern()
#apply the above settings to the row(0) header
style_header4= XFStyle()
style_header4.font= fnt4
style_header4.pattern= pattern4
style_header4.borders = borders4
style_header4.alignment=align4
#Define the font attributes for header
fnt1 = Font()
fnt1.name = 'Arial'
fnt1.size=10
fnt1.Style= 'Regular'
#Define the font attributes for header
content_fnt1 = Font()
content_fnt1.name ='Arial'
content_fnt1.size=10
content_fnt1.Style= 'Regular'
align_content1 = Alignment()
align_content1.horz= Alignment.HORZ_RIGHT
borders1 = Borders()
borders1.left = 0x01
borders1.right = 0x01
borders1.top = 0x01
borders1.bottom = 0x01
#The text should be centrally aligned
align1 = Alignment()
align1.horz = Alignment.HORZ_RIGHT
align1.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern1 = Pattern()
#apply the above settings to the row(0) header
style_header1= XFStyle()
style_header1.font= fnt1
style_header1.pattern= pattern1
style_header1.borders = borders1
style_header1.alignment=align1
#Define the font attributes for header
fnt2 = Font()
fnt2.name = 'Arial'
fnt2.size=16
fnt2.Style= 'Regular'
#Define the font attributes for header
content_fnt2 = Font()
content_fnt2.name ='Arial'
content_fnt2.size=16
content_fnt2.Style= 'Regular'
align_content2 = Alignment()
align_content2.horz= Alignment.HORZ_RIGHT
borders2 = Borders()
borders2.left = 0x01
borders2.right = 0x01
borders2.top = 0x01
borders2.bottom = 0x01
#The text should be centrally aligned
align2 = Alignment()
align2.horz = Alignment.HORZ_RIGHT
align2.vert = Alignment.VERT_CENTER
#We set the backgroundcolour here
pattern2 = Pattern()
#apply the above settings to the row(0) header
style_header2= XFStyle()
style_header2.font= fnt2
style_header2.pattern= pattern
style_header2.borders = borders2
style_header2.alignment=align2
wb = Workbook()
ws = wb.add_sheet('Salary Report')
this=self.browse(cr,uid,ids[0],context=context)
ws.row(0).height=300
ws.row(1).height=300
ws.row(2).height=300
# ws.col(0).width = 5000
# ws.col(0).width = 5000
# ws.col(0).width = 5000
# ws.col(0).width = 5000
ws.write_merge(0,0,0,5, 'SALARY REPORT',style_header)
ws.write_merge(1,1,0,5, ('DEPARTMENT : ',this.department_id.name),style_header)
ws.write_merge(2,2,0,1, 'Month',style_header)
ws.write_merge(2,2,2,3, 'Salary Amount',style_header)
ws.write_merge(2,2,4,5, 'Remark',style_header)
l=[]
lst=[]
for val in this.month_ids:
i = 3
query="""select hy.name,spl.month, sum(spl.total_amount) from salary_payment_line as spl left join holiday_year as hy on spl.year_id = hy.id where month = '"""+str(val.month)+"""'
and year_id = '"""+str(val.year_id.id)+"""' and curr_department = '"""+str(this.department_id.id)+"""' and salary_type='Salary'
group by spl.month, hy.name"""
cr.execute(query)
temp = cr.fetchall()
for year,month, total_amount in temp:
t=()
mon = int(month)
t = (year,mon, total_amount)
lst.append(t)
total = 0.0
lst.sort()
for year,month, total_amount in lst:
if month == 1:
month1 = 'January'
elif month == 2:
month1 = 'February'
elif month == 3:
month1 = 'March'
elif month == 4:
month1 = 'April'
elif month == 5:
month1 = 'May'
elif month == 6:
month1 = 'June'
elif month == 7:
month1 = 'July'
elif month == 8:
month1 = 'August'
elif month == 9:
month1 = 'September'
elif month == 10:
month1 = 'October'
elif month == 11:
month1 = 'November'
else:
month1 = 'December'
total += total_amount
ws.write_merge(i,i,0,1, (month1,' ',year),style_header4)
ws.write_merge(i,i,2,3, round(total_amount,2),style_header1)
ws.write_merge(i,i,4,5, ' ',style_header3)
i += 1
ws.write_merge(i,i,0,1, 'Total Amount',style_header2)
ws.write_merge(i,i,2,3, round(total,2),style_header2)
ws.write_merge(i,i,4,5, ' ',style_header)
if len(lst) < 1:
raise osv.except_osv(_('Warning!'),_('No Record found!'))
f = cStringIO.StringIO()
wb.save(f)
out=base64.encodestring(f.getvalue())
sal_report = self.write(cr, uid, ids, {'export_data':out, 'filename':'Salary Report.xls'}, context=context)
return sal_report
def create_template(self):
try:
open_workbook(self.template_path)
except FileNotFoundError:
# 文件夹下无模板文件，直接创建然后导入数据
wb = Workbook(encoding='ascii')
ws = wb.add_sheet("1")
style1 = XFStyle()
font1 = Font()
font1.bold = True
style1.font = font1
ws.write(0, 0, OLD_FILE_NAME, style1)
ws.write(0, 1, NEW_FILE_NAME, style1)
style2 = XFStyle()
font2 = Font()
font2.bold = True
font2.colour_index = 2
style2.font = font2
ws.write(1, 3, TEMPLATE_HINT, style2)
new_row_id = 1
for i in range(len(self.table_name_list0)):
if i not in self.disable_pos_list:
ws.write(new_row_id, 0, self.table_name_list0[i])
new_row_id += 1
wb.save(self.template_path)
return
# 文件夹下已经存在模板文件
# 暂时不检查完整性
rb = open_workbook(self.template_path, formatting_info=True)
r_sheet = rb.sheet_by_index(0)
wb = copy(rb)
sheet = wb.get_sheet(0)
def update_old_name(old_name_list, disable_pos_list):
needed_add_name_list = []
old_name_index = 0 # 旧文件名列序号
name0_list = r_sheet.col_values(old_name_index,
start_rowx=1,
end_rowx=None)
for i in range(len(old_name_list)):
if i not in disable_pos_list:
if old_name_list[i] not in name0_list:
needed_add_name_list.append(old_name_list[i])
if len(needed_add_name_list) == 0:
print("没有文件名需要添加到模板文件中，或者所有文件名已经添加")
return
n_row = r_sheet.nrows
print("原模板文件中已经有" + str(n_row) + "行")
for name in needed_add_name_list:
print("将文件名" + name + "添加到模板文件中")
sheet.write(n_row, old_name_index, name)
n_row += 1
update_old_name(self.table_name_list0, self.disable_pos_list)
try:
wb.save(self.template_path)
except PermissionError:
messagebox.showwarning("模板文件异常", "请关闭文件夹下的模板文件后再重新导入")
return
messagebox.showinfo("一切正常", "文件导入成功，模板文件已生成。\n请打开对应文件夹的模板文件编辑新文件名")
def create_excel(work_excel, withdraw_info=[]):
title_fnt = Font()
title_fnt.height = 0x0140
title_fnt.name = u'宋体'
title_fnt.bold = True
brd = Borders()
brd.bottom = 1
brd.top = 1
brd.left = 1
brd.right = 1
title_location = Alignment()
title_location.horz = Alignment.HORZ_CENTER
title_location.vert = Alignment.VERT_CENTER
title_style = XFStyle()
title_style.font = title_fnt
title_style.alignment = title_location
style = XFStyle()
style.font.height = 0x00E0
style.font.name = u'宋体'
style.font.bold = False
style.alignment.horz = Alignment.HORZ_CENTER
style.alignment.vert = Alignment.VERT_CENTER
content_title_style = deepcopy(style)
content_title_style.alignment.horz = Alignment.HORZ_LEFT
content_title_style.font.height = 0x00E0
content_style = deepcopy(style)
content_style.alignment.horz = Alignment.HORZ_LEFT
center_content_style = deepcopy(style)
center_content_style.alignment.horz = Alignment.HORZ_CENTER
center_content_style.borders = brd
style.borders = brd
content_style.borders = brd
merge_up_style = deepcopy(style)
merge_up_style.borders.bottom = 0
content_up_style = deepcopy(merge_up_style)
content_up_style.alignment.horz = Alignment.HORZ_CENTER
merge_down_style = deepcopy(style)
merge_down_style.borders.top = 0
content_down_style = deepcopy(merge_down_style)
content_down_style.alignment.horz = Alignment.HORZ_RIGHT
content_down_style.font.height = 0x00CA
for i, info in enumerate(withdraw_info):
width = i * 25
work_excel.write_merge(2 + width, 2 + width, 0, 16, u'商户提现付款申请单', title_style)
work_excel.write_merge(3 + width, 3 + width, 1, 5, info['supplier_type'], content_title_style)
work_excel.write_merge(3 + width, 3 + width, 6, 16, info['supplier'], content_title_style)
work_excel.write(4 + width, 0, u'收款单位名称', style)
work_excel.write_merge(4 + width, 4 + width, 1, 5, info['company'], content_style)
work_excel.write(4 + width, 6, u'申请提现帐号', style)
work_excel.write_merge(4 + width, 4 + width, 7, 16, info['applier'], center_content_style)
work_excel.write(5 + width, 0, u'开户银行', style)
work_excel.write_merge(5 + width, 5 + width, 1, 5, info['bank'], content_style)
work_excel.write(5 + width, 6, u'申请提现日期', style)
work_excel.write_merge(5 + width, 5 + width, 7, 16, info['apply_time'], center_content_style)
work_excel.write(6 + width, 0, u'银行帐号', style)
work_excel.write_merge(6 + width, 6 + width, 1, 5, info['account'], content_style)
work_excel.write(6 + width, 6, u'合同账期', style)
work_excel.write_merge(6 + width, 6 + width, 7, 16, info['payment_day_type'], center_content_style)
work_excel.write(7 + width, 0, u'付款用途', style)
work_excel.write_merge(7 + width, 7 + width, 1, 5, u'商户提现', content_style)
work_excel.write(7 + width, 6, u'付款日期', style)
work_excel.write_merge(7 + width, 7 + width, 7, 16, info['date'], center_content_style)
units = [u'千', u'百', u'十', u'万', u'千', u'百', u'十', u'元', u'角', u'分']
digit = list(str(info['money']))
digit.remove('.')
digit.reverse()
for j, unit in enumerate(units):
work_excel.write(8 + width, 7 + j, unit, content_style)
for index, value in enumerate(digit):
work_excel.write(9 + width, 16 - index, value, content_style)
work_excel.write(9 + width, 16 - index - 1, '￥', content_style)
work_excel.write_merge(10 + width, 11 + width, 0, 0, u'备注事项', style)
work_excel.write_merge(10 + width, 10 + width, 1, 6, '', content_style)
work_excel.write_merge(10 + width, 10 + width, 7, 16, '', style)
work_excel.write_merge(11 + width, 11 + width, 1, 16, info['print_info'], content_down_style)
work_excel.write(8 + width, 0, u'付款金额', merge_up_style)
work_excel.write(9 + width, 0, u'人民币（大写）', merge_down_style)
work_excel.write_merge(8 + width, 9 + width, 1, 6, info['china_money'], content_style)
work_excel.write_merge(13 + width, 13 + width, 0, 2, u'部门主管:', content_title_style)
work_excel.write_merge(13 + width, 13 + width, 3, 5, u'财务经理:', content_title_style)
work_excel.write_merge(13 + width, 13 + width, 6, 16, u'公司总经理:', content_title_style)
for row in range(2, 12):
work_excel.row(row + width).height_mismatch = 1
work_excel.row(row + width).height = 478
work_excel.row(2 + width).height_mismatch = 1
work_excel.row(2 + width).height = 1000
work_excel.col(0).width = 3871
work_excel.col(1).width = 1771
work_excel.col(2).width = 1509
work_excel.col(3).width = 2348
work_excel.col(4).width = 840
work_excel.col(5).width = 3241
work_excel.col(6).width = 3441
for col in range(7, 17):
work_excel.col(col).width = 709
def generate_report(root, filename, attend_staff, absent_staff, meeting_direct, record_staff, reports, issues):
book = Workbook(encoding='utf-8')
sheet1 = book.add_sheet('Fota Security Knox Week Report')
line_counter = 0
today = datetime.date.today().strftime('%Y/%m/%d')
# header
head_str = [
'Meeting Name', 'SW 3 Group Part 5 Fota/ Security/Knox/Encription TG WeekReport',
'', '',
'Meeting Date', today,
'Meeting Address', '8F Meeting Room',
'', '',
'Attend Staff', attend_staff,
'Absent Staff', absent_staff,
'', '',
'Meeting Direct', meeting_direct,
'Record Staff', record_staff,
]
for i in range(len(head_str) / 2):
sheet1.row(i).write(0, head_str[2 * i])
sheet1.row(i).write(1, head_str[2 * i + 1])
line_counter = len(head_str) / 2 + 1
# font bold
font = Font()
font.bold = True
# pattern yellow
pattern_yellow = Pattern()
pattern_yellow.pattern = Pattern.SOLID_PATTERN
pattern_yellow.pattern_fore_colour = 0x0D # yellow
# pattern gray
pattern_gray = Pattern()
pattern_gray.pattern = Pattern.SOLID_PATTERN
pattern_gray.pattern_fore_colour = 0x17 # gray
# borders thin
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
# alignment horizontal center
alig_hc = Alignment()
# alig.horizontal = Alignment.HORZ_CENTER #no effect, why? f**k!!!
alig_hc.horz = Alignment.HORZ_CENTER
# title style
style_title = XFStyle()
style_title.font = font
style_title.pattern = pattern_yellow
style_title.alignment = alig_hc
sheet1.write_merge(
line_counter, line_counter, 0, 3, 'summary', style_title)
line_counter += 1
# table header style
sytle_tb_header = XFStyle()
sytle_tb_header.font = font
sytle_tb_header.pattern = pattern_gray
sytle_tb_header.borders = borders
sytle_tb_header.alignment = alig_hc
sheet1.row(line_counter).write(0, 'Member', sytle_tb_header)
sheet1.row(line_counter).write(1, 'Week Jobs', sytle_tb_header)
sheet1.row(line_counter).write(2, 'Risk', sytle_tb_header)
sheet1.row(line_counter).write(3, 'Next Week Plan', sytle_tb_header)
line_counter += 1
# content
# for i in range(line_counter,11+line_counter):
i = line_counter
for report in reports:
# alignment
alig = Alignment()
alig.horz = Alignment.HORZ_CENTER
alig.vert = Alignment.VERT_CENTER
alig.wrap = 1
# alignment2
alig2 = Alignment()
alig2.vert = Alignment.VERT_CENTER
alig2.wrap = 1
# borders
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
# colors
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x2F
style_content = XFStyle()
style_content.alignment = alig
style_content.borders = borders
style_content2 = XFStyle()
style_content2.alignment = alig2
style_content2.borders = borders
style_content2.pattern = pattern
style_content3 = XFStyle()
style_content3.alignment = alig2
style_content3.borders = borders
sheet1.row(i).write(0, report.who, style_content)
sheet1.row(i).write(1, report.job, style_content2)
sheet1.row(i).write(2, report.risk, style_content2)
sheet1.row(i).write(3, report.plan, style_content3)
sheet1.row(i).height_mismatch = True
sheet1.row(i).height = 1500
i += 1
line_counter += len(reports)
line_counter += 1
sheet1.write_merge(
line_counter, line_counter, 0, 3, 'Main Issues List', style_title)
line_counter += 1
sheet1.row(line_counter).write(0, 'Items', sytle_tb_header)
sheet1.write_merge(
line_counter, line_counter, 1, 2, 'Deatail', sytle_tb_header)
sheet1.row(line_counter).write(3, 'Status', sytle_tb_header)
line_counter += 1
sheet1.col(0).width = 256 * 16
sheet1.col(1).width = 256 * 50
sheet1.col(2).width = 256 * 31
sheet1.col(3).width = 256 * 46
book.save(root + '/' + filename)
from datetime import date
from xlwt import Workbook, XFStyle, Borders, Pattern, Font
fnt = Font()
fnt.name = 'Arial'
borders = Borders()
borders.left = Borders.THICK
borders.right = Borders.THICK
borders.top = Borders.THICK
borders.bottom = Borders.THICK
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x0A
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
book = Workbook()
sheet = book.add_sheet('A Date')
sheet.write(1,1,date(2009,3,18),style)
book.save('date.xls')
def exportReport5ToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1');
sheet1.col(1).width = 256*80;
sheet1.col(2).width = 256*10;
sheet1.col(3).width = 256*20;
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
wrap = Alignment();
wrap.wrap = 1;
wrap.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
styleWrap = XFStyle();
styleWrap.alignment = wrap;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
styleDate.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = wrap;
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
#sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') );
row1.write(1, ('รายละเอียด').decode('UTF8'),styleHead);
row1.write(2, ('วันที่รายงาน').decode('UTF8'),styleHead );
row1.write(3, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead);
i=i+1;
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('risk_management_id') ,styleRowDetail );
row1.write(1, value.get('risk_detail').decode('UTF8'),StyleRowDetailWrap );
#sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(2, value.get('report_date') ,styleDate );
row1.write(3, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
text = "(" + sub.get('risk_team').decode('UTF8') + " ) " + sub.get('result').decode('UTF8');
row1.write(1, text ,StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simpleReport5.xls');
book.save(dirTempFile);
return dirTempFile;
def write_sheet(self, data, sheet_name, print_to_screen=False):
'''Write a very simple table to a new sheet in a spreadsheet,
Optionally, print the table to the screen'''
# most cells
al = Alignment()
al.horz = Alignment.HORZ_RIGHT
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.height = 9 * 20 # 9 pt
style = XFStyle()
style.font = font
style.alignment = al
# tops cells
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.bold = True
font.height = 9 * 20 # 9 pt
style_top = XFStyle()
style_top.font = font
style_top.alignment = al
# left cells
al = Alignment()
al.horz = Alignment.HORZ_LEFT
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.bold = True
font.italic = True
font.height = 9 * 20 # 9 pt
style_left = XFStyle()
style_left.font = font
style_left.alignment = al
ws = self.add_sheet(sheet_name)
for i, row in enumerate(data):
for j, cell in enumerate(row):
borders = Borders()
if i == 0:
borders.top = 1
borders.bottom = 2
if i == len(row) - 1:
borders.bottom = 1
if j == 0:
borders.left = 1
borders.right = 1
if j == len(row) - 1:
borders.right = 1
if j == 0:
_style = style_left
elif i == 0:
_style = style_top
else:
_style = style
_style.borders = borders
ws.write(i + 1, j + 1, cell, _style)
if print_to_screen:
print print_table(data, sheet_name, bold=True)
Line_data = ('测试表') #创建一个Line_data列表，并将其值赋为测试表
for i in range(0x00,0xff): # 设置单元格背景颜色
pattern = Pattern() # 创建一个模式
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型
pattern.pattern_fore_colour = i
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on...
style.pattern = pattern # 将赋值好的模式参数导入Style
sheet.write_merge(i, i, 0, 2, Line_data, style) #以合并单元格形式写入数据，即将数据写入以第1/2/3列合并德单元格内
for i in range(0x00,0xff): # 设置单元格内字体样式
fnt = Font() # 创建一个文本格式，包括字体、字号和颜色样式特性
fnt.name = '微软雅黑' # 设置其字体为微软雅黑, 'SimSun' # 指定“宋体”
fnt.colour_index = i # 设置其字体颜色
fnt.bold = True
style.font = fnt #将赋值好的模式参数导入Style
sheet.write_merge(i,i,3,5,Line_data,style) #以合并单元格形式写入数据，即将数据写入以第4/5/6列合并德单元格内
for i in range(0, 0x53): # 设置单元格下框线样式
borders = Borders()
borders.left = i
borders.right = i
borders.top = i
borders.bottom = i
style.borders = borders #将赋值好的模式参数导入Style
sheet.write_merge(i,i,6,8,Line_data,style) #以合并单元格形式写入数据，即将数据写入以第4/5/6列合并德单元格内
for i in range(6, 80): # 设置单元格下列宽样式
sheet.write(0,i,Line_data,style)
sheet.col(i).width = 0x0d00 + i*50
def produce_summary(self, cr, uid, travel, context=None):
number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]')
total_fnt = Font()
total_fnt.name = 'Calibri'
total_fnt.bold = True
total_fnt.height = 16 * 20 # font size 12
total_cell_l_style = XFStyle()
total_cell_l_style.alignment = Column.title_aln
total_cell_l_style.borders = Borders()
total_cell_l_style.borders.left = Borders.THICK
total_cell_l_style.borders.right = Borders.HAIR
total_cell_l_style.borders.top = Borders.THICK
total_cell_l_style.borders.bottom = Borders.THICK
total_cell_l_style.pattern = Column.title_ptn
total_cell_l_style.num_format_str = number_format
total_cell_l_style.font = Column.obj_fnt
total_cell_r_style = XFStyle()
total_cell_r_style.alignment = Column.title_aln
total_cell_r_style.borders = Borders()
total_cell_r_style.borders.left = Borders.HAIR
total_cell_r_style.borders.right = Borders.THICK
total_cell_r_style.borders.top = Borders.THICK
total_cell_r_style.borders.bottom = Borders.THICK
total_cell_r_style.pattern = Column.title_ptn
total_cell_r_style.num_format_str = number_format
total_cell_r_style.font = Column.obj_fnt
sub_total_cell_label = Cell(_(u'SOUS-TOTAL'), Column.title_fnt,
Column.title_aln,
total_cell_l_style.borders,
Column.title_ptn)
total_cell_label = Cell(_(u'TOTAL'), total_fnt, Column.title_aln,
total_cell_r_style.borders, Column.title_ptn,
number_format)
journeys = [i for i in travel.journey_ids]
w = Workbook()
ws = w.add_sheet(_('Travel Summary'))
ws.row(2).height = 0x0280
ws.row(3 + len(journeys)).height = 0x0140
ws.row(4 + len(journeys)).height = 0x0180
row = 0
row += 2
# Write headers
for i, col in enumerate(self._excel_columns):
ws.col(i).width = col.width
ws.write(row, i, col.text, col.style)
row += 1
for i, obj in enumerate(journeys):
ws.write(row + i, 0, i + 1, self._excel_columns[0].obj_style)
for j in xrange(1, len(self._excel_columns)):
ws.write(row + i, j, self._excel_columns[j].func(obj),
self._excel_columns[j].obj_style)
row += len(journeys)
rate_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('TICKET RATE')) - 1
cost_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('COSTS')) - 1
total_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('TOTAL')) - 1
# Sub total label
ws.write_merge(row, row, 0, rate_index, sub_total_cell_label.text,
sub_total_cell_label.style)
# Sub totals
ws.write(
row, rate_index + 1,
Formula("SUM(%s%d:%s%d)" %
(chr(66 + rate_index), 4, chr(66 + rate_index), row)),
total_cell_l_style)
ws.write(
row, rate_index + 2,
Formula("SUM(%s%d:%s%d)" %
(chr(66 + cost_index), 4, chr(66 + cost_index), row)),
total_cell_r_style)
total_top_underline_style = XFStyle()
total_top_underline_style.borders = Borders()
total_top_underline_style.borders.top = Borders.THICK
total_top_underline_style.font = Column.obj_fnt
# Draw a line above total to close box
ws.write(row, total_index + 1, "", total_top_underline_style)
row += 1
# Total label
ws.write_merge(row, row, 0, rate_index, total_cell_label.text,
total_cell_label.style)
# Total
ws.write_merge(
row, row, rate_index + 1, total_index,
Formula("%s%d+%s%d" %
(chr(66 + rate_index), row, chr(66 + cost_index), row)),
total_cell_label.style)
return w
cd_sheet = wkbook.add_sheet("Test")
# Sytling the date
style = XFStyle()
style.num_format_str = 'DD/MM/YYYY'
# set to 2 decimal point
style2 = XFStyle()
style2.num_format_str = "0.00"
style3 = XFStyle()
font = xlwt.Font()
font.height = 180
style.font = font
style2.font = font
style3.font = font
for i in range(len(cashDepo)):
for j in range(len(cashDepo[i])):
if j == 0 or j == 6: # Date
cd_sheet.write(i, j, datetime(*cashDepo[i][j]), style)
elif j == 5: # Amount
cd_sheet.write(i, j, cashDepo[i][j], style2)
else: # Others
cd_sheet.write(i, j,cashDepo[i][j], style3)
# Create excel file for the cash deposits
print "Exporting to Excel...."
ext = ".xls"
from sqlalchemy import and_, or_, distinct, asc
from sqlalchemy.orm import eagerload, eagerload_all
try:
import erp.model as m
import erp.model.testing as tst
except ImportError:
import portal.model as m
import portal.model.testing as tst
fnt = Font()
fnt.name = 'Arial'
fnt.bold = True
style_bold = XFStyle()
style_bold.font = fnt
border_bottom = Borders()
border_bottom.bottom = Borders.THIN
style_bold_border_bottom = XFStyle()
style_bold_border_bottom.font = fnt
style_bold_border_bottom.borders = border_bottom
border_right = Borders()
border_right.right = Borders.THIN
style_bold_border_right = XFStyle()
style_bold_border_right.font = fnt
style_bold_border_right.borders = border_right
def exportReport1ToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1');
sheet1.col(1).width = 256*20;
sheet1.col(2).width = 256*80;
sheet1.col(3).width = 256*10;
sheet1.col(4).width = 256*10;
sheet1.col(5).width = 256*20;
sheet1.col(6).width = 256*20;
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
wrap = Alignment();
wrap.wrap = 1;
wrap.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
styleWrap = XFStyle();
styleWrap.alignment = wrap;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
styleDate.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = wrap;
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('ลำดับที่').decode('UTF8') ,styleHead);
#sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') );
row1.write(1, ('เลขความเสี่ยง').decode('UTF8'),styleHead );
row1.write(2, ('อุบัติการณ์/ภาวะไม่พึงประสงค์').decode('UTF8'),styleHead);
row1.write(3, ('วันที่รายงาน').decode('UTF8'),styleHead );
row1.write(4, ('ความรุนแรง').decode('UTF8'),styleHead );
row1.write(5, ('ด้าน/โปรแกรม').decode('UTF8') ,styleHead);
row1.write(6, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead);
i=i+1;
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('row') ,styleRowDetail );
row1.write(1, str(value.get('risk_id')).decode('UTF8'),styleRowDetail );
row1.write(2, value.get('detail').decode('UTF8'),StyleRowDetailWrap );
row1.write(3, value.get('report_date') ,styleDate );
row1.write(4, value.get('level').decode('UTF8') ,styleRowDetail );
row1.write(5, value.get('pro').decode('UTF8') ,styleRowDetail );
row1.write(6, value.get('reporter').decode('UTF8') ,styleRowDetail );
i=i+1;
row2 = sheet1.row(i) ;
row2.write(2, ('รายละเอียด').decode('UTF8'),styleHead);
row2.write(3, ('หน่วยที่ตอบ').decode('UTF8'),styleHead );
row2.write(4, ('ระยะเวลาตอบ').decode('UTF8'),styleHead );
i=i+1;
for resp in value.get('responsible'):
row2 = sheet1.row(i) ;
row2.write(2, str(resp.get('detail')).decode('UTF8'),StyleRowDetailWrap);
row2.write(3, str(resp.get('service_name')).decode('UTF8'),styleRowDetail );
row2.write(4, str(resp.get('report_date')).decode('UTF8'),styleRowDetail );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simpleReport1.xls');
book.save(dirTempFile);
return dirTempFile;