def print_exl_report(self):
filename = 'Non Moving Products Report.xls'
get_warehouse = self.get_warehouse()
get_warehouse_name = self._get_warehouse_name()
l1 = []
workbook = xlwt.Workbook()
stylePC = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
fontP = xlwt.Font()
date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'
fontP.bold = True
fontP.height = 200
stylePC.font = fontP
stylePC.num_format_str = '@'
stylePC.alignment = alignment
style_title = xlwt.easyxf(
"font:height 300; font: name Liberation Sans, bold on,color black; align: horiz center"
)
style_table_header = xlwt.easyxf(
"font:height 200; font: name Liberation Sans, bold on,color black; align: horiz center"
)
style = xlwt.easyxf(
"font:height 200; font: name Liberation Sans,color black;")
worksheet = workbook.add_sheet('Sheet 1')
worksheet.write_merge(3, 3, 1, 2, 'Start Date:', style_table_header)
worksheet.write_merge(4, 4, 1, 2, self.start_date, date_format)
worksheet.write_merge(3, 3, 3, 4, 'End Date', style_table_header)
worksheet.write_merge(4, 4, 3, 4, self.end_date, date_format)
worksheet.write_merge(3, 3, 5, 6, 'Warehouse(s)', style_table_header)
w_col_no = 7
w_col_no1 = 8
if get_warehouse_name:
worksheet.write_merge(4, 4, 5, 6, get_warehouse_name, stylePC)
worksheet.write_merge(0,
1,
1,
5,
"Non Moving Products Report",
style=style_title)
worksheet.write_merge(6, 6, 0, 1, 'Product ID', style_table_header)
worksheet.write_merge(6, 6, 2, 3, 'Default Code', style_table_header)
worksheet.write_merge(6, 6, 4, 5, 'Product Name', style_table_header)
worksheet.write_merge(6, 6, 6, 7, 'Available Qty', style_table_header)
worksheet.write(6, 8, 'Last Sale Time', style_table_header)
worksheet.write_merge(6, 6, 9, 10, 'Duration From Last Sale In Days',
style_table_header)
prod_row = 7
prod_col = 0
for i in get_warehouse:
get_line = self.get_lines(i)
for each in get_line:
worksheet.write_merge(prod_row, prod_row, prod_col,
prod_col + 1, each['id'], style)
worksheet.write_merge(prod_row, prod_row, prod_col + 2,
prod_col + 3, each['code'], style)
worksheet.write_merge(prod_row, prod_row, prod_col + 4,
prod_col + 5, each['name'], style)
worksheet.write_merge(prod_row, prod_row, prod_col + 6,
prod_col + 7, each['qty_available'],
style)
if each['last_sale']:
worksheet.write(prod_row, prod_col + 8, each['last_sale']
or '', date_format)
if each['duration']:
worksheet.write_merge(prod_row, prod_row, prod_col + 9,
prod_col + 10, each['duration']
or '', style)
prod_row = prod_row + 1
break
prod_row = 6
prod_col = 7
fp = io.BytesIO()
workbook.save(fp)
export_id = self.env['non.moving.report.excel'].create({
'excel_file':
base64.encodestring(fp.getvalue()),
'file_name':
filename
})
res = {
'view_mode': 'form',
'res_id': export_id.id,
'res_model': 'non.moving.report.excel',
'view_type': 'form',
'type': 'ir.actions.act_window',
'target': 'new'
}
return res
def _writeExcel(self, excelName, data):
"""
将表列表及表结构写入excel
:param excelName: excel名字
:param data: 表列表数据
:return:
"""
writebook = xlwt.Workbook() # 打开一个excel
sheet = writebook.add_sheet('表列表') # 在打开的excel中添加一个sheet
# 调整样式
style = xlwt.XFStyle()
# 边框
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style.borders = borders
# 对其方式
alignment1 = xlwt.Alignment()
alignment2 = xlwt.Alignment()
# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment1.horz = 0x02
# 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment1.vert = 0x01
# 字体
fontT = xlwt.Font()
fontF = xlwt.Font()
fontT.bold = True # 字体加粗
fontT.height = 20 * 13 # 字体大小，13为字号，20为衡量单位
fontF.bold = False
# 表列表
for i, lin in enumerate(data):
self.sheetName = f'tbl{i}'
# 第一列填充序号
if i < len(data) - 1:
sheet.write(i + 1, 0, i + 1, style) # 序号
for j, cell in enumerate(lin):
# 处理时间格式
if isinstance(cell, datetime):
style.num_format_str = 'YYYY/M/D h:mm:ss'
elif i == 0:
# 处理表头
style.font = fontT
style.alignment = alignment1
sheet.write(i, j + 1, cell, style)
# 清除格式
style.num_format_str = ''
style.font = fontF
style.alignment = alignment2
# 添加表结构超链接
if i > 0:
link = f'HYPERLINK("#{self.sheetName}!B1";"查看")'
sheet.write(i, len(lin) + 1, xlwt.Formula(link), style)
# 表结构
for i, (self.tbl_en, self.tbl_cn) in enumerate(self.tbls.items()):
self.sheetName = f'tbl{i + 1}'
db_read = self.db.read(self._sqls(1), as_dict=False)
sheet = writebook.add_sheet(self.sheetName)
sheet.write_merge(0, 0, 0, 5, self.tbl_en)
sheet.write_merge(1, 1, 0, 5, self.tbl_cn) # 开始行,结束行, 开始列, 结束列
link = 'HYPERLINK("#表列表!B3";"返回")'
sheet.write_merge(2, 2, 0, 5, xlwt.Formula(link))
# 表头
for j, cell in enumerate(self.title_column):
style.font = fontT
style.alignment = alignment1
sheet.write(4, j, cell, style)
# 清除格式
style.font = fontF
style.alignment = alignment2
# 表数据
num_l = 5
for read in db_read.get_all():
for j, cell in enumerate(read):
sheet.write(num_l, j, cell, style)
num_l += 1
writebook.save(excelName) # 一定要记得保存
today = datetime.date.today()
oneday = datetime.timedelta(days=1)
yesterday = today - oneday
print yesterday
ws = w.add_sheet(str(yesterday).decode('utf-8'), cell_overwrite_ok=True)
font = xlwt.Font() # Create the Font
font.name = 'Times New Roman'
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour = 0x3A
style = xlwt.XFStyle()
style.borders = borders
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style = xlwt.XFStyle()
style.borders = borders
style.alignment = alignment
ws.write(0, 0, '日期'.decode('utf-8'), style)
ws.write(0, 1, 'NGID'.decode('utf-8'), style)
ws.write(0, 2, 'FREQ'.decode('utf-8'), style)
ws.write(0, 3, '错误数量'.decode('utf-8'), style)
ws.write(0, 4, '错误总和'.decode('utf-8'), style)
ws.panes_frozen = True
ws.horz_split_pos = 1
# for area in cdn_list:
# print 'CDN_A'[4:5]
def report(request):
DATA = []
spreads = Spread.objects.filter(prof__id__gt=0).order_by('prof__last_name')
groups = Group.objects.all()
profs = spreads.values('prof').distinct()
status = profs
status = []
for prof in profs:
cur = {}
pr = Professors.objects.get(id=int(prof['prof']))
cur['name'] = pr.last_name + ' ' + pr.first_name + ' ' + pr.middle_name + ', ' + pr.post.name + ', ' + pr.degree.name
spr = spreads.filter(prof__id=int(prof['prof']))
types = spr.values('loadUnit__typeLoad__name').order_by(
'-loadUnit__typeLoad__sort').distinct()
cur['spread'] = []
sum1 = 0
sum2 = 0
for tp in types:
sub = spr.filter(
loadUnit__typeLoad__name=tp['loadUnit__typeLoad__name'])
sub1 = []
sub2 = []
for spliter in sub:
if spliter.loadUnit.sem % 2 == 0:
sub2.append(spliter)
else:
sub1.append(spliter)
size = max(len(sub1), len(sub2))
lines = []
hourSum1 = 0
hourSum2 = 0
for i in range(0, size):
line = {}
if i < len(sub1):
line['sub1'] = sub1[i].loadUnit.subject.name
factor = 1
if sub1[i].loadUnit.typeLoad.typeTL == 'all':
gr1 = groups.filter(caf=sub1[i].loadUnit.caf,
sem=sub1[i].loadUnit.sem,
grade=sub1[i].loadUnit.grade)
else:
gr1 = groups.filter(id=sub1[i].group.id)
if sub1[i].loadUnit.typeLoad.typeTL == 'sub':
factor = Subgroup.objects.get(
group_id=gr1[0].id).amount
line['gr1'] = []
for g in gr1:
postfix = ''
if g.grade == 'b':
postfix += u'\u0411' # 'Б'
elif g.grade == 'm':
postfix += u'\u041c' # 'M'
if factor > 1:
postfix += '(' + str(factor) + ')'
line['gr1'].append(g.caf.name + '-' + str(g.sem) +
str(g.number) + postfix + ' [' +
str(g.amount) + '] ')
line['hour1'] = sub1[i].hours * factor
hourSum1 += line['hour1']
if i < len(sub2):
line['sub2'] = sub2[i].loadUnit.subject.name
factor = 1
if sub2[i].loadUnit.typeLoad.typeTL == 'all':
gr2 = groups.filter(caf=sub2[i].loadUnit.caf,
sem=sub2[i].loadUnit.sem,
grade=sub2[i].loadUnit.grade)
else:
gr2 = groups.filter(id=sub2[i].group.id)
if sub2[i].loadUnit.typeLoad.typeTL == 'sub':
factor = Subgroup.objects.get(
group_id=gr2[0].id).amount
line['gr2'] = []
for g in gr2:
postfix = ''
if g.grade == 'b':
postfix += u'\u0411' # 'Б'
elif g.grade == 'm':
postfix += u'\u041c' # 'M'
if factor > 1:
postfix += '(' + str(factor) + ')'
line['gr2'].append(g.caf.name + '-' + str(g.sem) +
str(g.number) + postfix + ' [' +
str(g.amount) + '] ')
line['hour2'] = sub2[i].hours * factor
hourSum2 += line['hour2']
lines.append(line)
cur['spread'].append({
'typeload': tp['loadUnit__typeLoad__name'],
'subs': lines,
'hourSum1': hourSum1,
'hourSum2': hourSum2
})
sum1 += hourSum1
sum2 += hourSum2
cur['sum1'] = sum1
cur['sum2'] = sum2
DATA.append(cur)
context = {
'spreads': spreads,
'data': DATA,
'status': status,
'menu': 'report'
}
# save into xls
style_full = style_antibottom = style_antitop = style_top = style_bottom = style_left = style_right = style_topleft = style_topright = style_bottomleft = style_bottomright = style_clear = xlwt.easyxf(
'font: name Times New Roman, color-index black, bold off',
num_format_str='#,##0')
style_red = xlwt.easyxf(
'font: name Times New Roman, color-index red, bold on',
num_format_str='#,##0')
style_bold = xlwt.easyxf(
'font: name Times New Roman, color-index black, bold on',
num_format_str='#,##0')
style_wrap = xlwt.easyxf(
'font: name Times New Roman, color-index black, bold off',
num_format_str='#,##0')
align = xlwt.Alignment()
align.wrap = 1
align.horz = xlwt.Alignment.HORZ_JUSTIFIED
align.vert = xlwt.Alignment.VERT_JUSTIFIED
style_wrap.alignment = align
borders_full = xlwt.Borders()
borders_full.bottom = borders_full.top = borders_full.left = borders_full.right = xlwt.Borders.THIN
style_full.borders = style_red.borders = style_bold.borders = style_wrap.borders = borders_full
borders_top = xlwt.Borders()
borders_top.top = xlwt.Borders.THIN
style_top.borders = borders_top
borders_bottom = xlwt.Borders()
borders_bottom.bottom = xlwt.Borders.THIN
style_bottom.borders = borders_bottom
borders_left = xlwt.Borders()
borders_left.left = xlwt.Borders.THIN
style_left.left = borders_left
borders_right = xlwt.Borders()
borders_right.top = xlwt.Borders.THIN
style_right.borders = borders_right
borders_topleft = xlwt.Borders()
borders_topleft.top = borders_topleft.left = xlwt.Borders.THIN
style_topleft.borders = borders_topleft
borders_topright = xlwt.Borders()
borders_topright.top = borders_topright.right = xlwt.Borders.THIN
style_topright.borders = borders_topright
borders_bottomleft = xlwt.Borders()
borders_bottomleft.top = borders_bottomleft.left = xlwt.Borders.THIN
style_bottomleft.borders = borders_bottomleft
borders_bottomright = xlwt.Borders()
borders_bottomright.top = borders_bottomright.right = xlwt.Borders.THIN
style_bottomright.borders = borders_bottomright
borders_antibottom = xlwt.Borders()
borders_antibottom.top = borders_antibottom.left = borders_antibottom.right = xlwt.Borders.THIN
style_antibottom.borders = borders_antibottom
borders_antitop = xlwt.Borders()
borders_antitop.bottom = borders_antitop.left = borders_antitop.right = xlwt.Borders.THIN
style_antitop.borders = borders_antitop
wb = xlwt.Workbook()
ws = wb.add_sheet('Load')
ws.col(0).width = 100 * 30
ws.col(1).width = 120 * 30
ws.col(2).width = 125 * 30
ws.col(3).width = 60 * 30
ws.col(4).width = 120 * 30
ws.col(5).width = 125 * 30
ws.col(6).width = 60 * 30
ws.col(7).width = 60 * 30
y = 0
for obj in DATA:
ws.write_merge(y, y, 0, 7, obj['name'], style=style_bold)
ws.write_merge(y + 1, y + 2, 0, 0, u'Нагрузка', style=style_bold)
ws.write_merge(y + 1,
y + 1,
1,
3,
u'Осенний семестр',
style=style_bold)
ws.write_merge(y + 1,
y + 1,
4,
6,
u'Весенний семестр',
style=style_bold)
ws.write_merge(y + 1, y + 2, 7, 7, u'Итого:', style=style_bold)
ws.write(y + 2, 1, u'Предмет', style=style_bold)
ws.write(y + 2, 2, u'Группы', style=style_bold)
ws.write(y + 2, 3, u'Часы', style=style_bold)
ws.write(y + 2, 4, u'Предмет', style=style_bold)
ws.write(y + 2, 5, u'Группы', style=style_bold)
ws.write(y + 2, 6, u'Часы', style=style_bold)
y += 3
for spr in obj['spread']:
ws.write_merge(y,
y + len(spr['subs']),
0,
0,
spr['typeload'],
style=style_full)
ws.write_merge(y,
y - 1 + len(spr['subs']),
7,
7,
'',
style=style_full)
for sub in spr['subs']:
if 'sub1' in sub:
ws.write(y, 1, sub['sub1'], style=style_topleft)
ws.write(y, 2, sub['gr1'], style=style_wrap)
ws.write(y, 3, sub['hour1'], style=style_topright)
else:
ws.write(y, 1, '', style=style_topleft)
ws.write(y, 2, '', style=style_top)
ws.write(y, 3, '', style=style_topright)
if 'sub2' in sub:
ws.write(y, 4, sub['sub2'], style=style_topleft)
ws.write(y, 5, sub['gr2'], style=style_wrap)
ws.write(y, 6, sub['hour2'], style=style_topright)
else:
ws.write(y, 4, '', style=style_topleft)
ws.write(y, 5, '', style=style_top)
ws.write(y, 6, '', style=style_topright)
y += 1
ws.write_merge(y, y, 1, 2, u'Итого:', style=style_bold)
ws.write(y, 3, spr['hourSum1'], style=style_bold)
ws.write_merge(y, y, 4, 5, u'Итого:', style=style_bold)
ws.write(y, 6, spr['hourSum2'], style=style_bold)
ws.write(y, 7, spr['hourSum2'] + spr['hourSum1'], style=style_bold)
y += 1
ws.write(y, 0, '', style=style_topleft)
ws.write_merge(y,
y,
1,
2,
u'Итого за осенний семестр:',
style=style_bold)
ws.write(y, 3, obj['sum1'], style=style_bold)
ws.write_merge(y,
y,
4,
5,
u'Итого за весенний семестр:',
style=style_bold)
ws.write(y, 6, obj['sum2'], style=style_bold)
ws.write(y, 7, obj['sum2'] + obj['sum1'], style=style_red)
y += 3
wb.save('static/report.xls')
return render(request, 'report.html', context)
def WriteExcel(LastBankInfo):
#加入写Excel
import xlwt
workbook = xlwt.Workbook() #注意Workbook的开头W要大写
sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok = True)
#cell_overwrite_ok参数用于确认同一个cell单元是否可以重设值。
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = 'SimSun' # 指定“宋体”
font.height = 230
style.font = font
#上设置字体
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour = 0x3A
style.borders = borders
#上设置框线
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER #水平居中
alignment.vert = xlwt.Alignment.VERT_CENTER #垂直居中
style.alignment = alignment
#设置字体居中
first_col = sheet1.col(0)
first_col.width = 350 * 20
second_col = sheet1.col(1)
second_col.width = 270 * 20
third_col = sheet1.col(2)
third_col.width = 270 * 20
fourth_col = sheet1.col(3)
fourth_col.width = 270 * 20
fifth_col = sheet1.col(4)
fifth_col.width = 270 * 20
sixth_col = sheet1.col(5)
sixth_col.width = 270 * 20
seventh_col = sheet1.col(6)
seventh_col.width = 270 * 20
seventh_col = sheet1.col(7)
seventh_col.width = 270 * 20
seventh_col = sheet1.col(8)
seventh_col.width = 270 * 20
#上设置横行的长度
################设置第一行字体的格式###############
geshi = xlwt.XFStyle()
font = xlwt.Font()
font.name = 'SimSun' # 指定“宋体”
font.height = 350
font.bold = 'on'
geshi.font = font
#上设置字体
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour = 0x3A
geshi.borders = borders
##############设置第一行字体的格式完成#############
sheet1.write(0, 0, '银行卡号', geshi)
sheet1.write(0, 1, '所属银行', geshi)
sheet1.write(0, 2, '所在城市', geshi)
sheet1.write(0, 3, '所在区县', geshi)
num = 1
for don in LastBankInfo:
sheet1.write(num, 0, don[0], style) # 银行卡号
sheet1.write(num, 1, don[1], style) # 所属银行
sheet1.write(num, 2, don[2], style) # 所在城市
sheet1.write(num, 3, don[3], style) # 所在区县
num = num + 1
now = time.strftime("%Y-%m-%d-%H_%M_%S",time.localtime(time.time()))
workbook.save(now + '生成银行卡结果.xls')
print ("===================生成银行卡完成===================")
def series_export_spreadsheet(matrix, matrix_answers, logo):
"""
Now take the matrix data type and generate a spreadsheet from it
"""
try:
import xlwt
except ImportError:
response.error = T(
"xlwt not installed, so cannot export as a Spreadsheet")
output = s3_rest_controller("survey",
"survey_series",
rheader=s3db.survey_series_rheader)
return output
import math
from io import BytesIO
# -------------------------------------------------------------------------
def wrap_text(sheet, cell, style):
row = cell.row
col = cell.col
try:
text = s3_str(cell.text)
except:
text = cell.text
width = 16
# Wrap text and calculate the row width and height
characters_in_cell = float(width - 2)
twips_per_row = 255 #default row height for 10 point font
if cell.merged():
try:
sheet.write_merge(
cell.row,
cell.row + cell.mergeV,
cell.col,
cell.col + cell.mergeH,
text,
style,
)
except Exception as msg:
log = current.log
log.error(msg)
log.debug("row: %s + vert: %s, col: %s + horiz %s" % \
(cell.row, cell.mergeV, cell.col, cell.mergeH))
posn = "%s,%s" % (cell.row, cell.col)
if matrix.matrix[posn]:
log.debug(matrix.matrix[posn])
rows = math.ceil(
(len(text) / characters_in_cell) / (1 + cell.mergeH))
else:
sheet.write(
cell.row,
cell.col,
text,
style,
)
rows = math.ceil(len(text) / characters_in_cell)
new_row_height = int(rows * twips_per_row)
new_col_width = width * COL_WIDTH_MULTIPLIER
if sheet.row(row).height < new_row_height:
sheet.row(row).height = new_row_height
if sheet.col(col).width < new_col_width:
sheet.col(col).width = new_col_width
# -------------------------------------------------------------------------
def merge_styles(list_template, style_list):
"""
Take a list of styles and return a single style object with
all the differences from a newly created object added to the
resultant style.
"""
if len(style_list) == 0:
final_style = xlwt.XFStyle()
elif len(style_list) == 1:
final_style = list_template[style_list[0]]
else:
zero_style = xlwt.XFStyle()
final_style = xlwt.XFStyle()
for i in range(0, len(style_list)):
final_style = merge_object_diff(final_style,
list_template[style_list[i]],
zero_style)
return final_style
# -------------------------------------------------------------------------
def merge_object_diff(base_obj, new_obj, zero_obj):
"""
Function to copy all the elements in new_obj that are different from
the zero_obj and place them in the base_obj
"""
element_list = new_obj.__dict__
for (element, value) in element_list.items():
try:
base_obj.__dict__[element] = merge_object_diff(
base_obj.__dict__[element], value,
zero_obj.__dict__[element])
except:
if zero_obj.__dict__[element] != value:
base_obj.__dict__[element] = value
return base_obj
COL_WIDTH_MULTIPLIER = 240
book = xlwt.Workbook(encoding="utf-8")
output = BytesIO()
protection = xlwt.Protection()
protection.cell_locked = 1
no_protection = xlwt.Protection()
no_protection.cell_locked = 0
borders = xlwt.Borders()
borders.left = xlwt.Borders.DOTTED
borders.right = xlwt.Borders.DOTTED
borders.top = xlwt.Borders.DOTTED
borders.bottom = xlwt.Borders.DOTTED
border_t1 = xlwt.Borders()
border_t1.top = xlwt.Borders.THIN
border_t2 = xlwt.Borders()
border_t2.top = xlwt.Borders.MEDIUM
border_l1 = xlwt.Borders()
border_l1.left = xlwt.Borders.THIN
border_l2 = xlwt.Borders()
border_l2.left = xlwt.Borders.MEDIUM
border_r1 = xlwt.Borders()
border_r1.right = xlwt.Borders.THIN
border_r2 = xlwt.Borders()
border_r2.right = xlwt.Borders.MEDIUM
border_b1 = xlwt.Borders()
border_b1.bottom = xlwt.Borders.THIN
border_b2 = xlwt.Borders()
border_b2.bottom = xlwt.Borders.MEDIUM
align_base = xlwt.Alignment()
align_base.horz = xlwt.Alignment.HORZ_LEFT
align_base.vert = xlwt.Alignment.VERT_TOP
align_wrap = xlwt.Alignment()
align_wrap.horz = xlwt.Alignment.HORZ_LEFT
align_wrap.vert = xlwt.Alignment.VERT_TOP
align_wrap.wrap = xlwt.Alignment.WRAP_AT_RIGHT
shaded_fill = xlwt.Pattern()
shaded_fill.pattern = xlwt.Pattern.SOLID_PATTERN
shaded_fill.pattern_fore_colour = 0x16 # 25% Grey
shaded_fill.pattern_back_colour = 0x08 # Black
heading_fill = xlwt.Pattern()
heading_fill.pattern = xlwt.Pattern.SOLID_PATTERN
heading_fill.pattern_fore_colour = 0x1F # ice_blue
heading_fill.pattern_back_colour = 0x08 # Black
style_title = xlwt.XFStyle()
style_title.font.height = 0x0140 # 320 twips, 16 points
style_title.font.bold = True
style_title.alignment = align_base
style_header = xlwt.XFStyle()
style_header.font.height = 0x00F0 # 240 twips, 12 points
style_header.font.bold = True
style_header.alignment = align_base
style_sub_header = xlwt.XFStyle()
style_sub_header.font.bold = True
style_sub_header.alignment = align_wrap
style_section_heading = xlwt.XFStyle()
style_section_heading.font.bold = True
style_section_heading.alignment = align_wrap
style_section_heading.pattern = heading_fill
style_hint = xlwt.XFStyle()
style_hint.protection = protection
style_hint.font.height = 160 # 160 twips, 8 points
style_hint.font.italic = True
style_hint.alignment = align_wrap
style_text = xlwt.XFStyle()
style_text.protection = protection
style_text.alignment = align_wrap
style_instructions = xlwt.XFStyle()
style_instructions.font.height = 0x00B4 # 180 twips, 9 points
style_instructions.font.italic = True
style_instructions.protection = protection
style_instructions.alignment = align_wrap
style_box = xlwt.XFStyle()
style_box.borders = borders
style_box.protection = no_protection
style_input = xlwt.XFStyle()
style_input.borders = borders
style_input.protection = no_protection
style_input.pattern = shaded_fill
box_l1 = xlwt.XFStyle()
box_l1.borders = border_l1
box_l2 = xlwt.XFStyle()
box_l2.borders = border_l2
box_t1 = xlwt.XFStyle()
box_t1.borders = border_t1
box_t2 = xlwt.XFStyle()
box_t2.borders = border_t2
box_r1 = xlwt.XFStyle()
box_r1.borders = border_r1
box_r2 = xlwt.XFStyle()
box_r2.borders = border_r2
box_b1 = xlwt.XFStyle()
box_b1.borders = border_b1
box_b2 = xlwt.XFStyle()
box_b2.borders = border_b2
style_list = {}
style_list["styleTitle"] = style_title
style_list["styleHeader"] = style_header
style_list["styleSubHeader"] = style_sub_header
style_list["styleSectionHeading"] = style_section_heading
style_list["styleHint"] = style_hint
style_list["styleText"] = style_text
style_list["styleInstructions"] = style_instructions
style_list["styleInput"] = style_input
style_list["boxL1"] = box_l1
style_list["boxL2"] = box_l2
style_list["boxT1"] = box_t1
style_list["boxT2"] = box_t2
style_list["boxR1"] = box_r1
style_list["boxR2"] = box_r2
style_list["boxB1"] = box_b1
style_list["boxB2"] = box_b2
sheet1 = book.add_sheet(T("Assessment"))
sheet2 = book.add_sheet(T("Metadata"))
max_col = 0
for cell in matrix.matrix.values():
if cell.col + cell.mergeH > 255:
current.log.warning("Cell (%s,%s) - (%s,%s) ignored" % \
(cell.col, cell.row, cell.col + cell.mergeH, cell.row + cell.mergeV))
continue
if cell.col + cell.mergeH > max_col:
max_col = cell.col + cell.mergeH
if cell.joined():
continue
style = merge_styles(style_list, cell.styleList)
if (style.alignment.wrap == style.alignment.WRAP_AT_RIGHT):
# get all the styles from the joined cells
# and merge these styles in.
joined_styles = matrix.joinedElementStyles(cell)
joined_style = merge_styles(style_list, joined_styles)
try:
wrap_text(sheet1, cell, joined_style)
except:
pass
else:
if cell.merged():
# get all the styles from the joined cells
# and merge these styles in.
joined_styles = matrix.joinedElementStyles(cell)
joined_style = merge_styles(style_list, joined_styles)
try:
sheet1.write_merge(
cell.row,
cell.row + cell.mergeV,
cell.col,
cell.col + cell.mergeH,
s3_str(cell.text),
joined_style,
)
except Exception as msg:
log = current.log
log.error(msg)
log.debug("row: %s + vert: %s, col: %s + horiz %s" % \
(cell.row, cell.mergeV, cell.col, cell.mergeH))
posn = "%s,%s" % (cell.row, cell.col)
if matrix.matrix[posn]:
log.debug(matrix.matrix[posn])
else:
sheet1.write(
cell.row,
cell.col,
s3_str(cell.text),
style,
)
CELL_WIDTH = 480 # approximately 2 characters
if max_col > 255:
max_col = 255
for col in range(max_col + 1):
sheet1.col(col).width = CELL_WIDTH
sheet2.write(0, 0, "Question Code")
sheet2.write(0, 1, "Response Count")
sheet2.write(0, 2, "Values")
sheet2.write(0, 3, "Cell Address")
for cell in matrix_answers.matrix.values():
style = merge_styles(style_list, cell.styleList)
sheet2.write(
cell.row,
cell.col,
s3_str(cell.text),
style,
)
if logo != None:
sheet1.insert_bitmap(logo, 0, 0)
sheet1.protect = True
sheet2.protect = True
for i in range(26):
sheet2.col(i).width = 0
sheet2.write(
0,
26,
s3_str(T("Please do not remove this sheet")),
style_header,
)
sheet2.col(26).width = 12000
book.save(output)
return output
def get_style(self, text_type):
style = xlwt.XFStyle()
al = xlwt.Alignment()
al.vert = xlwt.Alignment.VERT_CENTER
if text_type == 'discipline_name':
al.horz = xlwt.Alignment.HORZ_LEFT
style.font.height = 280
style.font.bold = True
if text_type == 'group_number':
al.horz = xlwt.Alignment.HORZ_CENTER
style.font.height = 280
style.font.bold = True
if text_type == 'department_name':
al.horz = xlwt.Alignment.HORZ_LEFT
style.font.height = 240
style.font.italic = True
if text_type == 'sem_number':
al.horz = xlwt.Alignment.HORZ_RIGHT
style.font.height = 240
style.font.bold = True
style.font.name = 'Times New Roman'
if text_type == 'student_info':
al.horz = xlwt.Alignment.HORZ_LEFT
style.font.height = 180
borders = xlwt.Borders()
borders.bottom = 1
borders.left = 1
borders.right = 1
borders.top = 1
style.borders = borders
if text_type == 'grades_info':
al.horz = xlwt.Alignment.HORZ_CENTER
style.font.height = 180
borders = xlwt.Borders()
borders.bottom = 1
borders.left = 1
borders.right = 1
borders.top = 1
style.borders = borders
if text_type == 'table_header':
al.horz = xlwt.Alignment.HORZ_CENTER
style.font.height = 200
borders = xlwt.Borders()
borders.bottom = 1
borders.left = 1
borders.right = 1
borders.top = 1
style.borders = borders
if text_type == 'teacher_fio':
al.wrap = xlwt.Alignment.WRAP_AT_RIGHT
al.horz = xlwt.Alignment.HORZ_RIGHT
al.vert = xlwt.Alignment.VERT_BOTTOM
style.font.height = 280
borders = xlwt.Borders()
borders.bottom = 1
style.borders = borders
if text_type == 'teacher_title':
al.horz = xlwt.Alignment.HORZ_RIGHT
style.font.height = 240
style.font.name = 'Times New Roman'
if text_type == 'sign_field':
borders = xlwt.Borders()
borders.bottom = 1
style.borders = borders
if text_type == 'sign_footer':
al.horz = xlwt.Alignment.HORZ_CENTER
style.font.italic = True
style.font.height = 200
style.font.name = 'Times New Roman'
style.alignment = al
return style
borders.left = 1
#borders.left = xlwt.Borders.THIN
borders.right = 1
borders.top = 1
borders.bottom = 1
style1.borders = borders
style3 = XFStyle() # 初始化样式，带边框和表格内容居中。
borders = xlwt.Borders()
borders.left = 1
#borders.left = xlwt.Borders.THIN
borders.right = 1
borders.top = 1
borders.bottom = 1
style3.borders = borders
al = xlwt.Alignment()
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
style3.alignment = al
F51FSwitch = workbook.add_sheet('F51FSwitch',
cell_overwrite_ok=True) # 创建表格的某一分页
first_col = F51FSwitch.col(0) # 设置0、1、2、3列的列宽
sec_col = F51FSwitch.col(1)
thr_col = F51FSwitch.col(2)
for_col = F51FSwitch.col(3)
first_col.width = 150 * 25
sec_col.width = 100 * 25
thr_col.width = 120 * 25
for_col.width = 320 * 25
def print_report(self, data):
"""
To get the date and print the report
@param self: The object pointer.
@param cr: A database cursor
@param uid: ID of the user currently logged in
@param context: A standard dictionary
@return: return report
"""
context = (self._context or {})
datas = {'ids': context.get('active_ids', [])}
res = self.read()
res = res and res[0] or {}
datas.update({'form': res})
if datas['form'].get('xls_output', False):
obj_pr = self.env['report.ng_hr_payroll.payroll_register_report']
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('Payroll Register')
sheet.row(0).height = 256*3
title_style = xlwt.easyxf('font: name Times New Roman,bold on, italic on, height 600')
title_style1 = xlwt.easyxf('font: name Times New Roman,bold on')
al = xlwt.Alignment()
al.horz = xlwt.Alignment.HORZ_CENTER
title_style.alignment = al
sheet.write_merge(0, 0, 5, 9, 'Payroll Register', title_style)
sheet.write(1, 6, datas['form']['name'], title_style1)
sheet.write(2, 4, 'From', title_style1)
sheet.write(2, 5, datas['form']['start_date'], title_style1)
sheet.write(2, 6, 'To', title_style1)
sheet.write(2, 7, datas['form']['end_date'], title_style1)
main_header = obj_pr.get_periods(datas['form'])
row = self.render_header(sheet, ['Name'] + main_header[0] + ['Total'], first_row=5)
emp_datas = obj_pr.get_employee(datas['form'], excel=True)
value_style = xlwt.easyxf('font: name Helvetica,bold on', num_format_str='#,##0.00')
cell_count = 0
for value in emp_datas:
for v in value:
sheet.write(row, cell_count, v, value_style)
cell_count += 1
row += 1
cell_count = 0
sheet.write(row+1, 0, 'Total', value_style)
total_datas = obj_pr.get_months_tol()
cell_count = 1
row += 1
for record in total_datas[-1][1:]:
sheet.write(row, cell_count, record, value_style)
cell_count += 1
# row += 1
# cell_count = 0
# sheet.write(row+1, cell_count, v, value_style)
# total_datas = obj_pr.get_months_tol()
# cell_count = 1
# row += 1
# for value in total_datas:
# for v in value[1:]:
# sheet.write(row, cell_count, v, value_style)
# print "sheet", sheet
# cell_count += 1
# row += 1
# cell_count = 0
stream = BytesIO()
workbook.save(stream)
ctx = {'default_xls_output': base64.encodebytes(stream.getvalue())}
ir_attachment = self.env['ir.attachment'].create({
'name': self.name + '.xls',
'datas': base64.encodebytes(stream.getvalue()),
'datas_fname': self.name + '.xls'}).id
# actid = self.env.get('ir.model.data').get_object_reference(cr, uid, 'base', 'action_attachment')[1]
actid = self.env.ref('base.action_attachment')[0]
myres = actid.read()[0]
myres['domain'] = "[('id','in',[" + ','.join(map(str, [ir_attachment])) + "])]"
return myres
return {
'context': ctx,
'view_type': 'form',
'view_mode': 'form',
'res_model': 'account.xls.output.wiz',
'type': 'ir.actions.act_window',
'target': 'new',
}
# return {
# 'type': 'ir.actions.report.xml',
# 'report_name': 'hr.payroll.register',
# 'datas': datas,
# }
return self.env['report'].get_action(self, 'ng_hr_payroll.payroll_register_report', data=datas)
def writeDailyOutput(date, quit_member_list):
cursor.execute('SELECT name,potato,presence FROM T_' + date)
daily_list = cursor.fetchall()
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet(date, cell_overwrite_ok=True)
# 设置单元格宽度
ws.col(0).width = 256 * 8
ws.col(1).width = 256 * 32
ws.col(2).width = 256 * 8
# 设置对其格式
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_LEFT # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
# 设置填充色
xlwt.add_palette_colour('_red1', 0x21)
wb.set_colour_RGB(0x21, 214, 88, 66) # 每次要用不同的x+num 8-63之间
xlwt.add_palette_colour('_red2', 0x22)
wb.set_colour_RGB(0x22, 226, 135, 120)
xlwt.add_palette_colour('_grey1', 0x23)
wb.set_colour_RGB(0x23, 188, 188, 188)
xlwt.add_palette_colour('_grey2', 0x24)
wb.set_colour_RGB(0x24, 234, 234, 234)
# 设置边框
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.THIN
# May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = xlwt.Style.colour_map['white']
borders.right_colour = xlwt.Style.colour_map['white']
borders.top_colour = xlwt.Style.colour_map['white']
borders.bottom_colour = xlwt.Style.colour_map['white']
# style1
style1 = xlwt.easyxf('pattern: pattern solid, fore_colour _grey1')
style1.alignment = alignment
font = xlwt.Font()
font.name = '微软雅黑'
font.height = 240
font.colour_index = 1
style1.font = font
style1.borders = borders
# style2
style2 = xlwt.easyxf('pattern: pattern solid, fore_colour _red1')
style2.alignment = alignment
font = xlwt.Font()
font.name = '微软雅黑'
font.height = 240
font.colour_index = 1
style2.font = font
style2.borders = borders
# style3
style3 = xlwt.easyxf('pattern: pattern solid, fore_colour _red2')
style3.alignment = alignment
font = xlwt.Font()
font.name = '微软雅黑'
font.height = 240
font.colour_index = xlwt.Style.colour_map['light_green']
font.bold = True
style3.font = font
style3.borders = borders
# style4
style4 = xlwt.easyxf('pattern: pattern solid, fore_colour _red2')
style4.alignment = alignment
font = xlwt.Font()
font.name = '微软雅黑'
font.height = 240
font.colour_index = xlwt.Style.colour_map['light_green']
style4.font = font
style4.borders = borders
# style5
style5 = xlwt.easyxf('pattern: pattern solid, fore_colour _grey1')
style5.alignment = alignment
font = xlwt.Font()
font.name = '微软雅黑'
font.height = 240
font.colour_index = 23
font.bold = True
style5.font = font
style5.borders = borders
# style6
style6 = xlwt.easyxf('pattern: pattern solid, fore_colour _grey2')
style6.alignment = alignment
font = xlwt.Font()
font.name = '微软雅黑'
font.height = 240
font.colour_index = 23
style6.font = font
style6.borders = borders
ws.write(0, 0, 'RANK', style1)
ws.write(0, 1, 'NAME', style1)
ws.write(0, 2, 'TODO', style1)
for i, x in enumerate(daily_list):
if i < 3:
ws.write(i + 1, 0, i + 1, style2)
ws.write(i + 1, 1, x[0], style3)
ws.write(i + 1, 2, x[1], style4)
else:
if x[1] == '0':
if x[2] == '0':
ws.write(i + 1, 0, i + 1, style1)
ws.write(i + 1, 1, x[0], style5)
ws.write(i + 1, 2, '缺勤', style6)
else:
ws.write(i + 1, 0, i + 1, style1)
ws.write(i + 1, 1, x[0], style5)
ws.write(i + 1, 2, '请假', style6)
else:
ws.write(i + 1, 0, i + 1, style1)
ws.write(i + 1, 1, x[0], style5)
ws.write(i + 1, 2, x[1], style6)
n = 0
for x in enumerate(quit_member_list):
ws.write(i + 2 + n + 0, 0, 'Quit', style1)
ws.write(i + 2 + n + 0, 1, x[0], style5)
ws.write(i + 2 + n + 0, 2, '', style6)
ws.write(i + 2 + n + 1, 0, '', style1)
ws.write(i + 2 + n + 1, 1, x[1], style5)
ws.write(i + 2 + n + 1, 2, '', style6)
ws.write(i + 2 + n + 2, 0, '', style1)
ws.write(i + 2 + n + 2, 1, x[2], style5)
ws.write(i + 2 + n + 2, 2, '', style6)
n += 3
wb.save('daily\\' + date + '.xls') #?
def ExportExcel(listview=None,
grid=None,
titre="Liste",
lstColonnes=None,
listeValeurs=None,
autoriseSelections=False):
# Export de la liste au format Excel
# Vérifie si données bien présentes
if (listview != None and len(listview.innerList) == 0) or (
grid != None and
(grid.GetNumberRows() == 0 or grid.GetNumberCols() == 0)):
dlg = wx.MessageDialog(None, "Il n'y a aucune donnée dans la liste !",
"Erreur", wx.OK | wx.ICON_ERROR)
dlg.ShowModal()
dlg.Destroy()
return wx.CANCEL
# Récupération des valeurs
if listview != None and lstColonnes == None and listeValeurs == None:
lstColonnes, listeValeurs = GetValeursListview(listview,
format="original")
if grid != None and lstColonnes == None and listeValeurs == None:
autoriseSelections = False
lstColonnes, listeValeurs = GetValeursGrid(grid)
# Selection des lignes
if autoriseSelections == True:
dlg = xchoixListe.DialogAffiche(
None,
lstColonnes=lstColonnes,
lstDonnees=listeValeurs,
)
if dlg.ShowModal() == wx.OK:
listeSelections = dlg.GetChoix()
dlg.Destroy()
else:
dlg.Destroy()
return wx.CANCEL
# Définit le nom et le chemin du fichier
nomFichier = "ExportExcel_%s.xls" % datetime.datetime.now().strftime(
"%Y%m%d%H%M%S")
wildcard = "Fichier Excel (*.xls)|*.xls|" \
"All files (*.*)|*.*"
cheminFichier = ChoixDestination(nomFichier, wildcard)
if not cheminFichier: return wx.CANCEL
# Export
import xlwt
# Création d'un classeur
wb = xlwt.Workbook()
# Création d'une feuille
title = xformat.NoPunctuation(titre)
ws1 = wb.add_sheet(title[:25])
# Remplissage de la feuille
al = xlwt.Alignment()
al.horz = xlwt.Alignment.HORZ_LEFT
al.vert = xlwt.Alignment.VERT_CENTER
ar = xlwt.Alignment()
ar.horz = xlwt.Alignment.HORZ_RIGHT
ar.vert = xlwt.Alignment.VERT_CENTER
styleEuros = xlwt.XFStyle()
styleEuros.num_format_str = '"$"#,##0.00_);("$"#,##'
styleEuros.alignment = ar
styleDate = xlwt.XFStyle()
styleDate.num_format_str = 'DD/MM/YYYY'
styleDate.alignment = ar
styleHeure = xlwt.XFStyle()
styleHeure.num_format_str = "[hh]:mm"
styleHeure.alignment = ar
# Création des labels de colonnes
x = 0
y = 0
for labelCol, alignement, largeur, nomChamp in lstColonnes:
try:
if "CheckState" in nomChamp:
nomChamp = "Coche"
except:
pass
ws1.write(x, y, labelCol)
if largeur <= 0: largeur = 1
ws1.col(y).width = largeur * 42
y += 1
# -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# Création des lignes
def RechercheFormatFromChaine(valeur):
""" Recherche le type de la chaîne """
if valeur.endswith(SYMBOLE):
# Si c'est un montant en euros
try:
if valeur.startswith("- "):
valeur = valeur.replace("- ", "-")
if valeur.startswith("+ "):
valeur = valeur.replace("+ ", "")
nbre = float(valeur[:-1])
return (nbre, styleEuros)
except:
pass
# Si c'est un nombre
try:
if valeur.startswith("- "):
valeur = valeur.replace("- ", "-")
nbre = float(valeur)
return (nbre, None)
except:
pass
# Si c'est une date
try:
if len(valeur) == 10:
if valeur[2] == "/" and valeur[5] == "/":
return (valeur, styleDate)
except:
pass
if type(valeur) == datetime.timedelta:
return (valeur, styleHeure)
# Si c'est une heure
try:
if len(valeur) > 3:
if ":" in valeur:
separateur = ":"
elif "h" in valeur:
separateur = "h"
else:
separateur = None
if separateur != None:
heures, minutes = valeur.split(separateur)
valeur = datetime.timedelta(minutes=int(heures) * 60 +
int(minutes))
# valeur = datetime.time(hour=int(valeur.split(separateur)[0]), minute=int(valeur.split(separateur)[1]))
return (valeur, styleHeure)
except:
pass
return str(valeur), None
# -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
def RechercheFormat(valeur):
""" Recherche le type de la donnée """
if type(valeur) == decimal.Decimal:
valeur = float(valeur)
return (valeur, styleEuros)
if type(valeur) == float:
return (valeur, None)
if type(valeur) == int:
return (valeur, None)
if type(valeur) == datetime.date:
valeur = xformat.DatetimeToStr(valeur)
return (valeur, styleDate)
if type(valeur) == datetime.timedelta:
return (valeur, styleHeure)
try:
if len(valeur) > 3:
if ":" in valeur:
separateur = ":"
elif "h" in valeur:
separateur = "h"
else:
separateur = None
if separateur != None:
donnees = valeur.split(separateur)
if len(donnees) == 2:
heures, minutes = donnees
if len(donnees) == 3:
heures, minutes, secondes = donnees
valeur = datetime.timedelta(minutes=int(heures) * 60 +
int(minutes))
# valeur = datetime.time(hour=int(valeur.split(separateur)[0]), minute=int(valeur.split(separateur)[1]))
return (valeur, styleHeure)
except:
pass
if type(valeur) in (str, six.text_type):
if len(valeur) == 10:
if valeur[2] == "/" and valeur[5] == "/":
return (valeur, styleDate)
if valeur[4] == "-" and valeur[7] == "-":
return (xformat.DateSqlToIso(valeur), styleDate)
return str(valeur), None
# -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x = 1
y = 0
for valeurs in listeValeurs:
if autoriseSelections == False or int(valeurs[0]) in listeSelections:
for valeur in valeurs:
if valeur == None:
valeur = u""
# Recherche s'il y a un format de nombre ou de montant
if isinstance(valeur, str):
valeur, format = RechercheFormatFromChaine(
valeur) # RechercheFormatFromChaine(valeur)
else:
valeur, format = RechercheFormat(valeur)
# Enregistre la valeur
if format != None:
ws1.write(x, y, valeur, format)
else:
ws1.write(x, y, valeur)
y += 1
x += 1
y = 0
# Finalisation du fichier xls
try:
wb.save(cheminFichier)
except:
dlg = wx.MessageDialog(
None,
"Il est impossible d'enregistrer le fichier Excel. Veuillez vérifier que ce fichier n'est pas déjà ouvert en arrière-plan.",
"Erreur", wx.OK | wx.ICON_ERROR)
dlg.ShowModal()
dlg.Destroy()
return wx.CANCEL
return Confirmation(cheminFichier)
style = xlwt.XFStyle() font = xlwt.Font() font.name = '宋体' font.bold = True font.height = 360 style.font = font borders = xlwt.Borders() borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN style.borders = borders alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_LEFT alignment.vert = xlwt.Alignment.VERT_TOP style.alignment = alignment style_red = xlwt.XFStyle() font_red = xlwt.Font() font_red.name = '宋体' font_red.bold = True font_red.height = 360 font_red.colour_index = 2 style_red.font = font_red borders_red = xlwt.Borders() borders_red.top = xlwt.Borders.THIN
font1.height = 10 * 0x14 font1.bold = True borders_title = xlwt.Borders() borders_title.left = 0 borders_title.right = 0 borders_title.top = 0 borders_title.bottom = 1 borders = xlwt.Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 al = xlwt.Alignment() al.horz = xlwt.Alignment.HORZ_CENTER al.vert = xlwt.Alignment.VERT_CENTER al1 = xlwt.Alignment() al1.horz = xlwt.Alignment.HORZ_RIGHT color = xlwt.Pattern() color.pattern = xlwt.Pattern.SOLID_PATTERN color.pattern_fore_colour = 22 style_title = xlwt.XFStyle() style_title.font = font_title style_title.alignment = al style_title.borders = borders_title style_t_table = xlwt.XFStyle()
def write_xls(datas):
titles = [u'设备标识', u'用户名', u'设备IP', u'设备端口', '设备接入时间']
f = xlwt.Workbook()
sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True)
row = 0
# 设置行高
sheet1.row(0).height_mismatch = True
sheet1.row(0).height = 100 * 20
# 合并
sheet1.write_merge(0, 0, 0, len(titles) - 1, 'First Merge')
tittlStyle = xlwt.XFStyle()
al = xlwt.Alignment()
# 居中
al.horz = 2
al.vert = 1
tittlStyle.alignment = al
# 字体格式
tittleFont = xlwt.Font()
tittleFont.name = '黑体'
tittleFont.height = 20 * 20
tittleFont.bold = True
tittlStyle.font = tittleFont
# 边框
borders = xlwt.Borders()
# 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
# 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
tittlStyle.borders = borders
sheet1.write(row, 0, "远程设备信息列表", tittlStyle)
row += 1
row0 = titles
contentStyle = xlwt.XFStyle()
contentStyle.borders = borders
#row0 = list(datas[0][0].keys())
for i in range(len(row0)):
sheet1.write(row, i, row0[i], contentStyle)
row += 1
for data in datas:
for d in data:
col = 0
sheet1.col(col).width = 13 * 256
sheet1.write(row, col, d['clientid'], contentStyle)
col += 1
sheet1.col(col).width = 10 * 256
sheet1.write(row, col, d['username'], contentStyle)
col += 1
sheet1.col(col).width = 15 * 256
sheet1.write(row, col, d['ip_address'], contentStyle)
col += 1
sheet1.col(col).width = 13 * 256
sheet1.write(row, col, d['port'], contentStyle)
col += 1
sheet1.col(col).width = 21 * 256
sheet1.write(row, col, d['created_at'], contentStyle)
col += 1
row += 1
f.save(path)
def set_style(name='Arial',
height=200,
colour_index=0x7FFF,
bold=False,
underline=False,
italic=False,
borders=True,
borders_colour_index=0x40,
backgroud_color=0x01,
alignment=None):
"""
设置单元格格式
:param name: 字体名
:param height: 字体高度
:param colour_index: 字体颜色
:param bold: 字体是否加粗
:param borders: 是否有边框
:param borders_colour_index:边框颜色
:param backgroud_color: 单元格背景颜色
:param alignment:单元格对齐方式
:return: 单元格格式
"""
# 初始化样式
__style = xlwt.XFStyle()
# 字体设置
__font = xlwt.Font() # 为样式创建字体
# 字体
__font.name = name
__font.height = height
# 颜色索引
__font.colour_index = colour_index
# 颜色也可以从定义好的map中去取
# __font.colour_index = xlwt.Style.colour_map['red']
# 加粗
__font.bold = bold
# 下划线
__font.underline = underline
# 斜体
__font.italic = italic
__style.font = __font
# 设置单元格背景颜色
pattern = xlwt.Pattern()
# 设置背景颜色的模式
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# 背景颜色
pattern.pattern_fore_colour = backgroud_color
__style.pattern = pattern
# 设置单元格的对齐方式，默认水平垂直居中
if alignment is None:
__alignment = xlwt.Alignment()
# 水平居中
__alignment.horz = xlwt.Alignment.HORZ_CENTER
# 垂直居中
__alignment.vert = xlwt.Alignment.VERT_CENTER
__style.alignment = __alignment
else:
__style.alignment = alignment
# 边框设置
if borders is True:
__borders = xlwt.Borders()
__borders.left = xlwt.Borders.MEDIUM
__borders.right = xlwt.Borders.MEDIUM
__borders.top = xlwt.Borders.MEDIUM
__borders.bottom = xlwt.Borders.MEDIUM
__borders.left_colour = borders_colour_index
__borders.right_colour = borders_colour_index
__borders.top_colour = borders_colour_index
__borders.bottom_colour = borders_colour_index
__style.borders = __borders
WriteExcel.__style = __style # 修改字体格式
return __style
def set_all_center(self):
alignment = xlwt.Alignment() # 设置居中
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
return alignment
def getcase(title, get_dict, count):
try:
xls = xlwt.Workbook()
logging.info('创建Excel')
# 设置单元格对齐方式
alignment = xlwt.Alignment()
# 自动换行
alignment.wrap = 1
# 设置样式
style = xlwt.XFStyle()
style.alignment = alignment
logging.info('已设置Excel为自动换行')
# 创建一个Sheet
sheet = xls.add_sheet("sheet")
# 设置行列的高宽
tall_style = xlwt.easyxf('font:height 450')
orther_style = xlwt.easyxf('font:height 1000')
# 设置第一行的宽度
first_row = sheet.row(0)
first_row.set_style(tall_style)
logging.info('已将第一行的宽度设置为450')
except Exception as ex:
logging.info("出现错误，如下：")
logging.error(ex)
try:
table_name = \
['需求名称', '所属模块', '一级模块', '二级模块', '用例名称', '优先级', '前置步骤', '操作步骤', '期望结果', '测试结果', '备注', '测试人']
# 设置列
for i in range(11):
sheet.write(0, i, table_name[i])
if i == 4 or i == 6:
sheet.col(i).width = 8888
logging.info('第五行或第七行设置为8888')
elif i == 7 or i == 8:
sheet.col(i).width = 12888
logging.info('第七行或第八行设置为12888')
else:
sheet.col(i).width = 4000
logging.info('已写入第一行数据')
except Exception as ex:
logging.info("出现错误，如下：")
logging.error(ex)
try:
print("需求名称：", title)
get_case = get_dict["topic"]
# 计算大模块数量
get_num_module = len(get_case["topics"])
logging.info('计算最大模块数量')
# 1.进入大模块的循环中
for i in range(0, get_num_module):
# 进来后计算一级模块数量
num_module_first = len(get_case["topics"][i]["topics"])
logging.info('计算一级模块数量')
# 获取大模块名称
get_module_big = get_case["topics"][i]["title"]
print("所属模块：", get_module_big)
logging.info('获取大模块名称')
# 2.进入一级模块
for j in range(0, num_module_first):
# 计算二级模块的数量
num_module_second = len(
get_case["topics"][i]["topics"][j]["topics"])
logging.info('计算二级模块数量')
# 获取一级模块的名称
get_module_first = get_case["topics"][i]["topics"][j]["title"]
print("一级模块：", get_module_first)
logging.info('获取一级模块名称')
# 3.进入二级模块
for a in range(0, num_module_second):
# 计算二级模块下的用例数
num_case = len(get_case["topics"][i]["topics"][j]["topics"]
[a]["topics"])
logging.info('计算用例数')
# 获取二级模块名称
get_module_second = get_case["topics"][i]["topics"][j][
"topics"][a]["title"]
print("二级模块：", get_module_second)
logging.info('获取二级模块名称')
# 4.进入每一条用例中
for b in range(0, num_case):
# 获取测试用例名字
case_name = get_case["topics"][i]["topics"][j][
"topics"][a]["topics"][b]["title"]
print("用例名称：", case_name)
logging.info('获取用例名称')
# 获取测试用例的优先级
get_priority = get_case["topics"][i]["topics"][j][
"topics"][a]["topics"][b]["makers"][0]
if get_priority == "priority-1":
priority = "S"
elif get_priority == "priority-2":
priority = "A"
elif get_priority == "priority-3":
priority = "B"
elif get_priority == "priority-4":
priority = "C"
else:
priority = "D"
print("优先级：", priority)
logging.info('获取用例优先级')
# 获取前置步骤
step_first = get_case["topics"][i]["topics"][j][
"topics"][a]["topics"][b]["topics"][0]["title"]
print("前置步骤：", step_first)
logging.info('获取前置步骤')
# 获取操作步骤
step = get_case["topics"][i]["topics"][j]["topics"][a][
"topics"][b]["topics"][1]["title"]
print("操作步骤：", step)
logging.info('获取操作步骤')
# 获取期望结果
hope_result = \
get_case["topics"][i]["topics"][j]["topics"][a]["topics"][b]["topics"][1]["topics"][0][
"title"]
print("期望结果", hope_result)
logging.info('获取期望结果')
# 收集用例信息
case_info = [
title, get_module_big, get_module_first,
get_module_second, case_name, priority, step_first,
step, hope_result
]
# 写入Excel
for s in range(9):
sheet.write(count, s, case_info[s], style)
orther_row = sheet.row(count)
orther_row.set_style(orther_style)
count += 1
logging.info('本条用例写入Excel成功')
xls.save('./case.xls')
except Exception as ex:
logging.info("出现错误，如下：")
logging.error(ex)
logging.info('用例已保存')
return 1
def auto_line(self):
alignment = xlwt.Alignment()
alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
return alignment
def action_excel(self, cr, uid, ids, context=None):
if not ids:
return {'type_pv': 'ir.actions.act_window_close'}
if context is None:
context = {}
form = self.read(cr, uid, ids)[0]
period = form.get('period_id')
print "PERIOD", period[0]
per_ids = self.pool.get('account.period').browse(cr, uid, [period[0]])[0]
print "OBJ PERIOD", per_ids.id
#path = form.get('path')
#Formato de la Hoja de Excel
wb = pycel.Workbook(encoding='utf-8')
style_cabecera = pycel.easyxf('font: colour black, bold True;'
'align: vertical center, horizontal center;'
)
style_cabecerader = pycel.easyxf('font: bold True;'
'align: vertical center, horizontal right;'
)
style_cabeceraizq = pycel.easyxf('font: bold True;'
'align: vertical center, horizontal left;'
)
style_header = pycel.easyxf('font: bold True;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea = pycel.easyxf('borders:bottom 1;')
linea_center = pycel.easyxf('font: colour black, height 140;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_center_red = pycel.easyxf('font: bold True, colour blue, height 140;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_izq = pycel.easyxf('font: colour black, height 140;'
'align: vertical center, horizontal left, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_izq_red = pycel.easyxf('font: bold True, colour blue, height 140;'
'align: vertical center, horizontal left, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_izq_n = pycel.easyxf('font: bold True, colour black, height 140;'
'align: vertical center, horizontal left, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_der = pycel.easyxf('font: colour black, height 140;'
'align: vertical center, horizontal right;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_der_red = pycel.easyxf('font: colour blue, height 140;'
'align: vertical center, horizontal right;'
'borders: left 1, right 1, top 1, bottom 1;')
if period:
title = 'FORMULARIO-104'
title1 = 'FORMULARIO-104'
ws = wb.add_sheet(title)
ws.show_grid = False
ws.header_str = u"&LFecha de Impresion: &D Hora: &T&RPagina &P de &N"
ws.footer_str = u""
compania = self.pool.get('res.users').browse(cr, uid, uid).company_id
print "compania", compania.name
print "direccion", compania.partner_id
calle1 = compania.partner_id.street
calle2 = compania.partner_id.street2
ruc = compania.partner_id.part_number
if calle1 and calle2:
direccion = str(calle1.encode('UTF-8')) +" "+ str(calle2.encode('UTF-8'))
elif calle1 and not calle2:
direccion = str(calle1.encode('UTF-8'))
elif calle2 and not calle1:
direccion = str(calle2.encode('UTF-8'))
else:
direccion = ''
if ruc:
ruc_part = ruc
else:
ruc_part = ''
x0 = 6
ws.write_merge(1, 1, 1, x0, compania.name, style_cabecera)
# ws.write_merge(2, 2, 1, x0, 'Direccion: ' + direccion, style_cabecera)
ws.write_merge(3, 3, 1, x0, title , style_cabecera)
ws.write_merge(5, 5, 1, x0, title1 +" "+ time.strftime('%d/%m/%Y', time.strptime(per_ids.date_start, '%Y-%m-%d')).upper() +" AL "+ time.strftime('%d/%m/%Y', time.strptime(per_ids.date_stop, '%Y-%m-%d')).upper(), style_cabecera)
ws.fit_num_pages = 1
ws.fit_height_to_pages = 0
ws.fit_width_to_pages = 1
ws.portrait = 1
align = pycel.Alignment()
align.horz = pycel.Alignment.HORZ_RIGHT
align.vert = pycel.Alignment.VERT_CENTER
font1 = pycel.Font()
font1.colour_index = 0x0
font1.height = 140
linea_izq_n.width = 150
#Formato de Numero
style = pycel.XFStyle()
style.num_format_str = '#,##0.00'
style.alignment = align
style.font = font1
#Formato de Numero Saldo
font = pycel.Font()
font.bold = True
font.colour_index = 0x27
style1 = pycel.XFStyle()
style1.num_format_str = '#,##0.00'
style1.alignment = align
style1.font = font
font2 = pycel.Font()
font2.bold = True
font2.colour_index = 0x0
style2 = pycel.XFStyle()
style2.num_format_str = '#,##0.00'
style2.alignment = align
style2.font = font2
style3 = pycel.XFStyle()
style3.num_format_str = '0'
style3.alignment = align
style3.font = font1
#info = self.get_payroll(cr, uid, form)
xi = 8 # Cabecera de Cliente
xe = 9
sec = 1
if period:
ws.write(xi, 1, 'SECUENCIAL', style_header)
ws.write(xi, 2, 'PROVEEDOR / CLIENTE', style_header)
ws.write(xi, 3, 'NOMBRE', style_header)
ws.write(xi, 4, 'CODIGO BASE', style_header)
ws.write(xi, 5, 'BASE IMPONIBLE', style_header)
ws.write(xi, 6, 'CODIGO IMPUESTO', style_header)
ws.write(xi, 7, 'VALOR RETENIDO', style_header)
totales = self.get_lines_report_wage(cr, uid, form)
# for tot in totales:
# print "Total", tot
# t_inv = tot.get('t_invr', '')
# t_pun = tot.get('t_puni', '')
# t_tot = tot.get('t_tot', '')
# t_fis = tot.get('t_fis', '')
# t_pend = tot.get('t_pend', '')
# ws.write(xe, 1, '', style_header)
# ws.write(xe, 2, '', style_header)
# ws.write(xe, 3, 'TOTAL', style_header)
# ws.write(xe, 4, t_fis, linea_der)
# ws.write(xe, 5, t_inv, linea_der)
# ws.write(xe, 6, t_pend, linea_der)
# ws.write(xe, 7, t_pun, linea_der)
# ws.write(xe, 8, t_tot, linea_der)
xi = xe + 1
rf = rr = ri = 0
amount_base = amount_calculate = 0.00
if period:
lineas = self.get_lines_report_wage(cr, uid, form)
#print "***lineas: ", lineas
for linea in lineas:
print "OJO ES IMP", linea.get('impuesto', '')
if not linea.get('impuesto', ''):
ws.write(xi, 1, linea.get('sec', ''), linea_center)
ws.write(xi, 2, linea.get('partner', ''), linea_center)
ws.write(xi, 3, linea.get('name', ''), linea_izq)
ws.write(xi, 4, linea.get('code', ''), linea_center)
ws.write(xi, 5, linea.get('base_imp', ''), linea_der)
ws.write(xi, 6, linea.get('code_imp', ''), linea_center)
ws.write(xi, 7, linea.get('valor_ret', ''), linea_der)
xi += 1
elif linea.get('impuesto', ''):
ws.write(xi, 1, linea.get('sec', ''), linea_center_red)
ws.write(xi, 2, linea.get('', ''), linea_center)
ws.write(xi, 3, linea.get('name', ''), linea_izq_red)
ws.write(xi, 4, linea.get('code', ''), linea_center_red)
ws.write(xi, 5, linea.get('base_imp', ''), linea_der_red)
ws.write(xi, 6, linea.get('code_imp', ''), linea_center_red)
ws.write(xi, 7, linea.get('valor_ret', ''), linea_der_red)
xi += 1
#totales
# ws.write(10, 4, linea.get('t_invr', ''), linea_der)
# ws.write(10, 5, linea.get('t_puni', ''), linea_der)
# ws.write(10, 6, linea.get('t_tot', ''), linea_der)
ws.col(0).width = 1000
ws.col(1).width = 3250
ws.col(2).width = 11100
ws.col(3).width = 13900
ws.col(4).width = 3250
ws.col(5).width = 3250
ws.col(6).width = 3250
ws.col(7).width = 3250
ws.col(8).width = 3250
ws.row(8).height = 750
# buf = cStringIO.StringIO()
# name = "%s%s%s.xls" % (path, "Reporte_detallado_", datetime.datetime.now())
# try:
# wb.save(name)
# raise Warning('Archivo salvado correctamente')
# except ValueError:
# raise Warning('Error a la hora de salvar el archivo')
# buf = StringIO.StringIO()
# name = "%s%s.xls" % ("Reporte_detallado", datetime.datetime.now())
# name1 = wb.save(name)
# out = base64.encodestring(buf.getvalue())
# # buf.close()
# return self.write(cr, uid, ids, {'data':name1, 'name':name})
buf = cStringIO.StringIO()
wb.save(buf)
out = base64.encodestring(buf.getvalue())
buf.close()
name = "%s.xls" % ("Reporte_104")
archivo = '/opt/temp/' + name
res_model = 'report.104'
id = ids and type(ids) == type([]) and ids[0] or ids
self.load_doc(cr, uid, out, id, name, archivo, res_model)
return {
'type': 'ir.actions.client',
'tag': 'reload'
}
def print_report(self):
"""
To get the date and print the report
@param self: The object pointer.
@param cr: A database cursor
@param uid: ID of the user currently logged in
@param context: A standard dictionary
@return: return report
"""
# if context is None:
# context = {}
datas = {'ids': self._context.get('active_ids', [])}
res = self.read()
res = res and res[0] or {}
datas.update({'form': res})
if datas['form'].get('xls_output', False):
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('Payroll Register')
sheet.row(0).height = 256 * 3
title_style = xlwt.easyxf(
'font: name Times New Roman,bold on, italic on, height 600')
title_style1 = xlwt.easyxf('font: name Times New Roman,bold on')
al = xlwt.Alignment()
al.horz = xlwt.Alignment.HORZ_CENTER
title_style.alignment = al
sheet.write_merge(0, 0, 5, 9, 'Payroll Register', title_style)
sheet.write(1, 6, datas['form']['name'], title_style1)
sheet.write(2, 4, 'From', title_style1)
sheet.write(2, 5, datas['form']['start_date'], title_style1)
sheet.write(2, 6, 'To', title_style1)
sheet.write(2, 7, datas['form']['end_date'], title_style1)
main_header = self.get_periods(datas['form'])
# Add the PFA LIST
pfa_list = []
zip_pfa_list = []
pfa_dict = {}
pfa_obj = self.env['pen.type']
pfa_ids = pfa_obj.search([])
pfas = pfa_ids
pfa_list = ["Pension - " + pfa.name for pfa in pfas]
zip_pfa_list = [0.0 for pfa in pfas
] # Creates an empty list filled with zeros
pfa_dict = dict(zip(pfa_list, zip_pfa_list))
bf_pfa_list = [
'Name', 'Start Date', 'Employee Position',
'Pension Institution', 'Pension Account Number',
'Annual Salary'
] + main_header[0]
count_bf_pfa_list = len(bf_pfa_list)
af_pfa_list = ['Total']
comp_list = bf_pfa_list + af_pfa_list + pfa_list
row = self.render_header(sheet, comp_list, first_row=5)
emp_datas = self.get_employee(datas['form'], excel=True)
emp_ids = datas['form']['employee_ids']
employee_obj = self.env['hr.employee']
employees = employee_obj.browse(emp_ids)
# Search for the Pension Field
inds = []
for i, ele in enumerate(comp_list):
_logger.info('Pension: %s: %s' % (i, ele))
if "Employee's Pension Contribution" == ele:
inds.append(i)
if inds:
for emp_data in emp_datas:
emp_ids = employee_obj.search([('name', '=', emp_data[0])])
pen_comp = emp_ids[0].pf_id.name
_logger.info('Emp data %s: %s' % (emp_data[0], pen_comp))
thevalue = emp_data[inds[0]]
_logger.info('Value: %s' % thevalue)
if not thevalue:
thevalue = 0.0
data_len = len(emp_data)
#Pad the list with empty spaces
for i in range(0, len(pfa_list)):
emp_data.insert(data_len, '')
if pen_comp:
# Search for the index of the column
ind = []
for i, ele in enumerate(comp_list):
_logger.info('Pen comp: %s:%s' % (pen_comp, ele))
if "Pension - " + pen_comp == ele:
ind.append(i)
try:
emp_data[ind[0]] = float(thevalue)
except Exception as e:
pass
# emp_data[0] = 0
# emp_data[ind[0]] = float(thevalue)
pfa_total = pfa_dict.get("Pension - " +
pen_comp) or 0.0
pfa_dict["Pension - " +
pen_comp] = pfa_total + thevalue
_logger.info('Pension: %s' % pfa_total)
value_style = xlwt.easyxf('font: name Helvetica',
num_format_str='#,##0.00')
cell_count = 0
for value in emp_datas:
for v in value:
sheet.write(row, cell_count, v, value_style)
cell_count += 1
row += 1
cell_count = 0
sheet.write(row + 1, 0, 'Total', value_style)
total_datas = self.get_months_tol()
cell_count = 1
for value in [total_datas]:
row += 1
for v in value[1:]:
sheet.write(row, cell_count, v, value_style)
cell_count += 1
# cell_count = 0
total = self.get_total()
sheet.write(row, cell_count, total, value_style)
cell_count = 0
col = 0
ind = []
for pfa in pfa_list:
for i, ele in enumerate(comp_list):
if pfa == ele:
pfa_total = pfa_dict.get(pfa)
col = i
sheet.write(row, col, pfa_total, value_style)
row += 1
stream = BytesIO()
workbook.save(stream)
stream.seek(0)
result = base64.b64encode(stream.read())
base_url = self.env['ir.config_parameter'].get_param(
'web.base.url')
attachment_obj = self.env['ir.attachment']
attachment_id = attachment_obj.create({
'name': self.name + '.xls',
'datas_fname': self.name + '.xls',
'datas': result
})
download_url = '/web/content/' + str(
attachment_id.id) + '?download=true'
return {
"type": "ir.actions.act_url",
"url": str(base_url) + str(download_url),
"target": "self",
}
data = {'data': datas}
return self.env.ref(
'sunray_hr_payroll.action_report_payroll_register').report_action(
self, data=datas, config=False)
def export_xls(request, id):
response = HttpResponse(content_type='application/ms-excel')
response[
'Content-Disposition'] = 'attachment; filename="Report_' + id + '_MAStucco.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Work Order')
work_order = WorkOrder.getByID(id)
# Sheet header, first row
col_num = 0
row_num = 0
# BordersLeft
bordersContent = xlwt.Borders()
bordersContent.bottom = xlwt.Borders.THIN
bordersContent.top = xlwt.Borders.THIN
bordersContent.right = xlwt.Borders.MEDIUM
bordersContent.left = xlwt.Borders.THIN
# BordersLeft
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.right = xlwt.Borders.MEDIUM
borders.left = xlwt.Borders.MEDIUM
#BordersTitle
bordersTitle = xlwt.Borders()
bordersTitle.bottom = xlwt.Borders.MEDIUM
bordersTitle.top = xlwt.Borders.MEDIUM
bordersTitle.right = xlwt.Borders.MEDIUM
bordersTitle.left = xlwt.Borders.MEDIUM
#ColorTitle
patternTitle = xlwt.Pattern()
patternTitle.pattern = xlwt.Pattern.SOLID_PATTERN
patternTitle.pattern_fore_colour = xlwt.Style.colour_map['orange']
# ColorSubTitle
patternSubTitle = xlwt.Pattern()
patternSubTitle.pattern = xlwt.Pattern.SOLID_PATTERN
patternSubTitle.pattern_fore_colour = xlwt.Style.colour_map['light_orange']
# ColorLeft
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['gray25']
#AlignmentContent
alignWrap = xlwt.Alignment()
alignWrap.horz = xlwt.Alignment.HORZ_RIGHT
alignWrap.vert = xlwt.Alignment.VERT_TOP
alignWrap.wrap = xlwt.Alignment.WRAP_AT_RIGHT
# StyleLeft
styleLeft = xlwt.XFStyle()
styleLeft.borders = all
styleLeft.font.bold = True
styleLeft.font.italic = True
styleLeft.borders = borders
styleLeft.pattern = pattern
# StyleTitle
styleTitle = xlwt.XFStyle()
styleTitle.borders = all
styleTitle.font.bold = True
styleTitle.font.italic = True
styleTitle.borders = bordersTitle
styleTitle.pattern = patternTitle
# StyleSubTitle
styleSubTitle = xlwt.XFStyle()
styleSubTitle.borders = all
styleSubTitle.font.bold = True
styleSubTitle.font.italic = True
styleSubTitle.borders = bordersTitle
styleSubTitle.pattern = patternSubTitle
# StyleContent
styleContent = xlwt.XFStyle()
styleContent.borders = bordersContent
styleContent.alignment = alignWrap
ws.write(row_num, col_num, 'Work Order', styleTitle)
ws.write(row_num, col_num + 1, '', styleTitle)
rows = [
'Customer', 'Phase', 'Worker', 'Date', 'Order By', 'Model', 'Notes'
]
for row in range(len(rows)):
ws.write(row_num + row + 1, col_num, rows[row], styleLeft)
ws.col(col_num).width = 256 * 15
# Sheet body, remaining rows
#work_order = WorkOrder.getByID(49)
col_num += 1
ws.write(row_num + 1, col_num, work_order.customer, styleContent)
ws.write(row_num + 2, col_num, work_order.work_phase, styleContent)
ws.write(
row_num + 3, col_num, work_order.assigned_worker.first_name + ' ' +
work_order.assigned_worker.last_name, styleContent)
ws.write(row_num + 4, col_num, work_order.date.strftime('%d, %b %Y'),
styleContent)
ws.write(row_num + 5, col_num, work_order.order_by, styleContent)
ws.write(row_num + 6, col_num, work_order.model, styleContent)
ws.write(row_num + 7, col_num, work_order.notes, styleContent)
ws.col(col_num).width = 256 * 40
col_num -= 1
row_num += 10
ws.write(row_num, col_num, 'Part Orders', styleTitle)
ws.write(row_num, col_num + 1, '', styleTitle)
part_orders = PartOrder.objects.all().filter(work_order=work_order)
rows = ['Quantity', 'Part', 'Measure']
cont = 0
for part in part_orders:
cont += 1
ws.write(row_num + 1, col_num, '', styleSubTitle)
ws.write(row_num + 1, col_num + 1, '', styleSubTitle)
row_num += 1
for row in range(len(rows)):
ws.write(row_num + row + 1, col_num, rows[row], styleLeft)
ws.col(col_num).width = 256 * 15
col_num += 1
ws.write(row_num + 1, col_num, part.quantity, styleContent)
ws.write(row_num + 2, col_num, part.part, styleContent)
ws.write(row_num + 3, col_num, part.measure, styleContent)
col_num -= 1
row_num += 3
row_num += 3
ws.write(row_num, col_num, 'Job Information', styleTitle)
ws.write(row_num, col_num + 1, '', styleTitle)
rows = ['Lot', 'Address', 'Subdivision']
for row in range(len(rows)):
ws.write(row_num + row + 1, col_num, rows[row], styleLeft)
ws.col(col_num).width = 256 * 15
col_num += 1
ws.write(row_num + 1, col_num, work_order.job.lot, styleContent)
ws.write(row_num + 2, col_num, work_order.job.address, styleContent)
ws.write(row_num + 3, col_num, work_order.job.subdivision, styleContent)
wb.save(response)
return response
def txtToXls(TxtFilename, XlsName):
print("Starting converting xls......")
#打开一个txt文件,该txt文件为utf-8编码
f = open(TxtFilename, encoding='utf-8')
#创建一个excel
#x为列
#y为行
x = 0
y = 0
xlsTemp = xlwt.Workbook()
#初始化表的风格
styleHead = xlwt.XFStyle()
styleBody = xlwt.XFStyle()
#设置字体
fontSet = xlwt.Font()
fontSet.bold = True
fontSet.underline = True
fontSet.height = 12 * 20
#居中设置
align = xlwt.Alignment()
align.horz = xlwt.Alignment.HORZ_CENTER
align.wrap = xlwt.Alignment.WRAP_AT_RIGHT
#设置单元格宽度
border = xlwt.Borders()
border.left = xlwt.Borders.THIN
border.right = xlwt.Borders.THIN
border.top = xlwt.Borders.THIN
border.bottom = xlwt.Borders.THIN
#应用字体到第一行标题栏风格
styleHead.font = fontSet
styleHead.alignment = align
styleHead.borders = border
#应用到表格数据部分的风格
styleBody.borders = border
styleBody.alignment.wrap = 1
styleBody.font.height = 11 * 20
#创建第一个表,名为"Banzou"
sheetTemp = xlsTemp.add_sheet("BanZou", cell_overwrite_ok=True)
sheetTemp.write(0, 0, 'UID', styleHead)
sheetTemp.write(0, 1, 'KID', styleHead)
sheetTemp.write(0, 2, 'Auther', styleHead)
#设置单元格宽度
for i in range(0, 3):
if i == 2:
sheetTemp.col(i).width = 10000
else:
sheetTemp.col(i).width = 3333
#用while循环来一行一行写入txt文本到excel表中
while True:
a = 1
b = 0
#readline函数表示从txt文本中一行行的读取
LineTemp = f.readline()
#LineTemp为空,则终止读取
if not LineTemp:
break
#按照空格分隔,只分割前两个空格
for i in LineTemp.split(' ', 2):
item = i.strip()
sheetTemp.write(x + 1, y, item, styleBody)
y = y + 1
x = x + 1
y = 0
f.close()
xlsTemp.save(XlsName + '.xls')
def save(mLineNumber, mOpenFullPath, mTemplateFullPath, mSaveFullPath):
# 开始时间
startTime = datetime.datetime.now()
# 如果 XLS 存在就删除
if os.path.exists(mSaveFullPath):
os.remove(mSaveFullPath)
# 打开 XLS && 读取 XLS
xlsx = xlrd.open_workbook(mOpenFullPath)
table = xlsx.sheet_by_index(0)
old_excel = xlrd.open_workbook(mTemplateFullPath, formatting_info = True)
new_excel = copy(old_excel)
ws = new_excel.get_sheet(0)
# 边框
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
# 字体
font = xlwt.Font()
# 字体类型
font.name = 'Arial'
# 字体颜色
font.colour_index = 0
# 字体大小，11为字号，20为衡量单位
font.height = 20 * 11
# 字体加粗
font.bold = False
# 下划线
font.underline = False
# 斜体字
font.italic = False
# 写入 XLS 的主题，默认设置为：自动换行，水平居中，垂直居中，边框，字体....
style = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
style.borders = borders
style.font = font
count = 0
data1 = 5 # [5, 9, 13, 17, 21]
data2 = 0 # [0, 2, 4]
for i in range(1, 31, 2):
count += 1
ws.write(data1, data2, table.cell(mLineNumber - 1, i).value, style)
if count >= 3:
data1 += 4
count = 0
if data2 == 4:
data2 = 0
else:
data2 += 2
ws.write(0, 0, table.cell(0, 0).value, style)
new_excel.save(mSaveFullPath)
# 结束时间
endTime = datetime.datetime.now()
print('保存成功，用时：', (endTime - startTime).seconds)
def createExcel(self):
if self.BOM:
# print(self.BOM)
f, t = QFileDialog.getSaveFileName(self, 'Save', '/', 'Excel(*.xls)')
if f:
wb = xlwt.Workbook(encoding='utf-8')
sheet = wb.add_sheet('Location BOM')
style = xlwt.XFStyle()
align1 = xlwt.Alignment()
# Horizontal center
align1.horz = xlwt.Alignment.HORZ_CENTER
align1.wrap = xlwt.Alignment.WRAP_AT_RIGHT
# Vertical center
align1.vert = xlwt.Alignment.VERT_CENTER
style.alignment = align1
border = xlwt.Borders()
border.left = xlwt.Borders.THIN
border.right = xlwt.Borders.THIN
border.top = xlwt.Borders.THIN
border.bottom = xlwt.Borders.THIN
style.borders = border
font = xlwt.Font()
font.name = 'Microsoft YaHei'
font.bold = True
style.font = font
# Location BOM title
sheet.write_merge(0, 1, 0, 4, 'Location BOM', style)
sheet.write_merge(2, 2, 0, 1, 'PCBA part number:', style)
sheet.write(2, 2, self.b_name.split(' ')[0], style)
sheet.write_merge(3, 3, 0, 1, 'PCBA Description:', style)
sheet.write(3, 2, 'PCBA ASSY. of ' + self.b_name.split(' ')[0], style)
sheet.write_merge(2, 3, 3, 4, 'Rev' + self.b_name.split(' ')[-1], style)
sheet.write_merge(4, 4, 0, 4, '', style)
# Location BOM header
sheet.write(5, 0, 'Index', style)
sheet.col(1).width = 256 * 15
sheet.write(5, 1, 'Part number', style)
sheet.col(2).width = 256 * 60
sheet.write(5, 2, 'Description', style)
sheet.col(3).width = 256 * 30
sheet.write(5, 3, 'Location', style)
sheet.col(4).width = 256 * 6
sheet.write(5, 4, 'Qty', style)
# setup cell style
align2 = xlwt.Alignment()
align2.horz = xlwt.Alignment.HORZ_LEFT
align2.wrap = xlwt.Alignment.WRAP_AT_RIGHT
# Vertical center
align2.vert = xlwt.Alignment.VERT_CENTER
style.alignment = align2
c_font = xlwt.Font()
c_font.name = 'Microsoft YaHei'
c_font.bold = False
style.font = c_font
# fill with insertion parts
i = 6
sheet.write_merge(i, i, 0, 4, 'Insertion Parts', style)
# i = i + 1
k = 1
for p in self.insertions:
try:
sheet.write(i + k, 0, k, style)
sheet.write(i + k, 1, p['PN'], style)
sheet.write(i + k, 2, p['Desc'], style)
sheet.write(i + k, 4, p['Qty'], style)
if 'Location' in p:
sheet.write(i + k, 3, p['Location'], style)
else:
sheet.write(i + k, 3, '', style)
except Exception as e:
print('Exception in writing to Excel: ', e)
k = k + 1
# fill with SMT parts
sheet.write_merge(i + k, i + k, 0, 4, 'SMT Parts', style)
i = i + k
k = 1
for p in self.SMTs:
try:
sheet.write(i + k, 0, k, style)
sheet.write(i + k, 1, p['PN'], style)
sheet.write(i + k, 2, p['Desc'], style)
if 'Location' in p:
sheet.write(i + k, 3, p['Location'], style)
else:
sheet.write(i + k, 3, '', style)
sheet.write(i + k, 4, p['Qty'], style)
except Exception as e:
print('Exception in writing to Excel: ', e)
k = k + 1
wb.save(f)
def horz_left(x, y, data):
algnt = xlwt.Alignment()
algnt.horz = xlwt.Alignment.HORZ_LEFT
style = xlwt.XFStyle()
style.alignment = algnt
table.write(x, y, data, style)
def write_xls(title_list, filed_list, data_list, from_admin):
'''
将数据写入 excel 表格
'''
i = 0
work_book = xlwt.Workbook(encoding='utf-8')
work_sheet = work_book.add_sheet("订单明细")
# font_title 为标题的字体格式
font_title = xlwt.Font() # 创建字体样式
font_title.name = '华文宋体'
font_title.bold = True
# 字体颜色
font_title.colour_index = i
# 字体大小，18为字号，20为衡量单位
font_title.height = 20 * 18
# font_body 为内容的字体央视
font_body = xlwt.Font()
font_body.name = '华文宋体'
font_title.colour_index = i
font_title.height = 20 * 12
# 设置单元格对齐方式
alignment = xlwt.Alignment()
# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.horz = 0x02
# 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment.vert = 0x01
# 设置自动换行
alignment.wrap = 1
# 设置边框
borders = xlwt.Borders()
# 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7
# 大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
# 初始化样式 (标题样式)
style_title = xlwt.XFStyle()
style_title.font = font_title
style_title.alignment = alignment
style_title.borders = borders
# 初始化样式 (内容样式)
style_body = xlwt.XFStyle()
style_body.font = font_body
style_body.alignment = alignment
style_body.borders = borders
# 写入标题
for index, item in enumerate(title_list):
work_sheet.write(0, index, item, style_title)
# 写入内容
total_price = 0
for index, item in enumerate(data_list, start=1):
total_price += float(item.get("total_price"))
for num, val in enumerate(filed_list):
work_sheet.write(index, num, item.get(val), style_body)
data_count = len(data_list) + 1
work_sheet.write(data_count, 2, "累计金额", style_title)
work_sheet.write(data_count, 3, total_price, style_title)
curr_date = str(datetime.datetime.now()).split(" ")[0] + "-" + str(
from_admin) + "-order.xls"
work_book.save(curr_date)
return curr_date
formatting_info=True) # 同时保存格式信息
oldSheet = oldExcel.sheet_by_index(0)
newExcel = copy(oldExcel)
newSheet = newExcel.get_sheet(0)
# newSheet.write(0, 3, 'test') # 默认格式写入
# newExcel.save('C:/Users/aby/Desktop/newExcel.xls')
newStyle = xlwt.XFStyle() # 新建空样式（后文代码命名可以不用new）
newFont = xlwt.Font() # 新建空字体样式
newFont.name = '等线'
newFont.bold = True # 加粗
newFont.height = 280 # 280 = 14pt * 20
newStyle.font = newFont # 添加至新建的空样式
newBorders = xlwt.Borders() # 新建空边框样式
newBorders.top = xlwt.Borders.THIN # 细上边框
newBorders.bottom = xlwt.Borders.THIN
newBorders.left = xlwt.Borders.THIN
newBorders.right = xlwt.Borders.THIN
newStyle.borders = newBorders # 添加至新建的空样式
newAlignment = xlwt.Alignment()
newAlignment.horz = xlwt.Alignment.HORZ_CENTER # 水平居中
newAlignment.vert = xlwt.Alignment.VERT_CENTER # 垂直居中
newStyle.alignment = newAlignment
newSheet.write(0, 3, 'test', newStyle)
newExcel.save('C:/Users/aby/Desktop/newExcel.xls')
def export_excel_to_print(self,no,send_date,d_path,files):
w = xlwt.Workbook(encoding='utf-8')
single_dict = files.get(u"单独邮寄",[])
if single_dict:
files.remove(u"单独邮寄")
#12号字,水平居中,垂直居中
title_style = xlwt.XFStyle()
title_style.font = xlwt.Font()
title_style.font.name=u"宋体"
title_style.font.height = 240
title_style.alignment = xlwt.Alignment()
title_style.alignment.horz = xlwt.Alignment.HORZ_CENTER
title_style.alignment.vert = xlwt.Alignment.VERT_CENTER
#11号字,水平居中,垂直居中
style = xlwt.XFStyle()
style.font = xlwt.Font()
style.font.name=u"宋体"
style.font.height = 220
style.alignment = xlwt.Alignment()
style.alignment.horz = xlwt.Alignment.HORZ_CENTER
style.alignment.vert = xlwt.Alignment.VERT_CENTER
#18号字,水平居中,垂直居中,画线
title_style1 = xlwt.XFStyle()
title_style1.font = xlwt.Font()
title_style1.font.name=u"宋体"
title_style1.font.height = 360
title_style1.alignment = xlwt.Alignment()
title_style1.alignment.horz = xlwt.Alignment.HORZ_CENTER
title_style1.alignment.vert = xlwt.Alignment.VERT_CENTER
title_style1.borders = xlwt.Borders() # Add Borders to Style
title_style1.borders.left = xlwt.Borders.MEDIUM # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
title_style1.borders.right = xlwt.Borders.MEDIUM
title_style1.borders.top = xlwt.Borders.MEDIUM
title_style1.borders.bottom = xlwt.Borders.MEDIUM
title_style1.borders.left_colour = 0x40
title_style1.borders.right_colour = 0x40
title_style1.borders.top_colour = 0x40
title_style1.borders.bottom_colour = 0x40
#12号字,水平居中,垂直居中,画线
style2 = xlwt.XFStyle()
style2.font = xlwt.Font()
style2.font.name=u"宋体"
style2.font.height = 240
style2.alignment = xlwt.Alignment()
style2.alignment.horz = xlwt.Alignment.HORZ_CENTER
style2.alignment.vert = xlwt.Alignment.VERT_CENTER
style2.borders = xlwt.Borders() # Add Borders to Style
style2.borders.left = xlwt.Borders.MEDIUM # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
style2.borders.right = xlwt.Borders.MEDIUM
style2.borders.top = xlwt.Borders.MEDIUM
style2.borders.bottom = xlwt.Borders.MEDIUM
style2.borders.left_colour = 0x40
style2.borders.right_colour = 0x40
style2.borders.top_colour = 0x40
style2.borders.bottom_colour = 0x40
#发货单
ws0 = w.add_sheet(u"发货单")
ws0.col(1).width = 4300
ws0.col(2).width = 10750
ws0.col(4).width = 5000
ws0.write_merge(1,1,0,3,u"耳聋检测报告送货清单(日期:%s)" %(send_date,),style=title_style1)
ws0.write(1,4,no,style=title_style1)
ws0.write(2,0,u"序号",style=style2)
ws0.write(2,1,u"货品名称",style=style2)
ws0.write(2,2,u"送检机构",style=style2)
ws0.write(2,3,u"包裹数量",style=style2)
ws0.write(2,4,u"报告数量（份）",style=style2)
row=3
total_pack=0
total_report=0
for k,v in files.items():
ws0.write(row,0,row-2,style=style2)
ws0.write(row,1,u"检测报告",style=style2)
ws0.write(row,2,k,style=style2)
ws0.write(row,3,1,style=style2)
ws0.write(row,4,len(v),style=style2)
row +=1
total_pack +=1
total_report += len(v)
if single_dict:
ws0.write(row,0,row-2)
ws0.write(row,1,u"检测报告",style=style2)
ws0.write(row,2,u"单独邮寄",style=style2)
ws0.write(row,3,len(single_dict),style=style2)
ws0.write(row,4,len(single_dict),style=style2)
total_pack +=len(single_dict)
total_report += len(single_dict)
row +=1
ws0.write_merge(row,row,0,2,u"合计件数",style=style2)
ws0.write(row,3,total_pack,style=style2)
ws0.write(row,4,total_report,style=style2)
for k,v in files.items():
ws = w.add_sheet(k)
ws.col(0).width = 3200 #1000 = 3.715(Excel)
ws.col(3).width = 3200
ws.write_merge(0,0,0,3,k+u"耳聋报告清单",style=title_style)
row=1
ws.write(row,0,u"样本编号",style=style)
ws.write(row,1,u"姓名",style=style)
ws.write(row,2,u"性别",style=style)
ws.write(row,3,u"送检日期",style=style)
row +=1
for i in v:
ws.write(row,0,i[1],style=style)
ws.write(row,1,i[2],style=style)
ws.write(row,2,u"男" if i[3]=="M" else u"女" ,style=style)
ws.write(row,3,i[7],style=style)
row +=1
if single_dict:
ws2 = w.add_sheet(u"单独邮寄")
ws2.col(0).width = 6200
ws2.col(1).width = 3200
ws2.col(4).width = 3200
ws2.col(5).width = 4200
ws2.col(6).width = 8200
ws2.write_merge(0,0,0,6,u"单独邮寄耳聋报告清单",style=title_style)
row=1
ws2.write(row,0,u"送检机构",style=style)
ws2.write(row,1,u"样本编号",style=style)
ws2.write(row,2,u"姓名",style=style)
ws2.write(row,3,u"性别",style=style)
ws2.write(row,4,u"送检日期",style=style)
ws2.write(row,5,u"电话",style=style)
ws2.write(row,6,u"邮寄地址",style=style)
row +=1
for i in single_dict:
ws2.write(row,0,i[6],style=style)
ws2.write(row,1,i[1],style=style)
ws2.write(row,2,i[2],style=style)
ws2.write(row,3,u"男" if i[3]=="M" else u"女" ,style=style)
ws2.write(row,4,i[7] ,style=style)
ws2.write(row,5,i[4] ,style=style)
ws2.write(row,6,i[5] ,style=style)
row +=1
w.save(os.path.join(d_path,no+u"耳聋发货单")+".xls")
def Excel(caseResult):
# 获取信息配置表
arr = ["手机品牌", "手机型号", "系统版本", "CPU核心数", "运行内存大小", "手机分辨率", "测试期间耗电"]
# 品牌、型号、系统版本、CPU核心数、运行内存、手机分辨率
arr2 = [readConfig.getConfig("phoneConf", "brand"), readConfig.getConfig("phoneConf", "model"),
readConfig.getConfig("phoneConf", "systemVersion"), readConfig.getConfig("phoneConf", "cpu"),
readConfig.getConfig("phoneConf", "men"), readConfig.getConfig("phoneConf", "appPix")]
startBc = int(readConfig.getConfig("phoneConf", "startPower"))
def batteryCapacity():
get_cmd = os.popen("adb shell dumpsys battery").readlines()
for i in range(0, len(get_cmd)):
a = str(get_cmd[i])
b = 'level'
p = a.find(b)
try:
if p != -1:
s = get_cmd[i].split('level')
Battery = "".join(s).strip('\n').strip("'").strip(' : ')
return int(Battery)
except:
return '获取电量失败'
# 获取配置信息
applicationName = readConfig.getConfig("baseconf", "applicationName") # 获取应用名
applicationVersion = readConfig.getConfig("baseconf", "applicationVersion") # 获取应用版本信息
fileSize = readConfig.getConfig("baseconf", "fileSize") # 获取app文件大小
# excel样式
def set_style(name, height, bold):
u'字体，高度，背景色，加粗，字体色'
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.bold = bold
font.color_index = 4
font.height = height
style.font = font
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
return style
def s_style(name, height, lei, bold, s):
u'字体，高度，背景色'
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.colour_index = s # 设置其字体颜色
font.bold = bold
font.color_index = 4
font.height = height
style.font = font
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = lei
style.pattern = pattern # Add Pattern to Style创建模式
return style
w = xlwt.Workbook() # 创建一个工作簿
ws = w.add_sheet('Hey, Hades') # 创建一个工作表
# 合并单元格
ws.write_merge(0, 0, 0, 4, u'测试报告总概况', set_style(u'宋体', 360, True)) # 合并行单元格
ws.write_merge(1, 1, 0, 4, u'测试概况', s_style(u'宋体', 270, 4, False, 0x01)) # 合并行单元格
ws.write_merge(8, 8, 0, 6, u'测试手机详情', s_style(u'宋体', 270, 4, False, 0x01)) # 合并行单元格
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style
end = datetime.datetime.now()
print '计时结束时间：%s' % end
timeConsuming = str(end - start)[:-7] # 用测试结束时间-开始时间得到测试耗时，再把时间转成字符串并去掉小数部分
print '测试耗时：%s' % timeConsuming
endBc = batteryCapacity()
bC = str(startBc - endBc)+'%'
arr2.append(bC)
dk = u'%s + Appium:%s + Python:%s' % (platform.platform(),
os.popen('appium -v').readlines()[0].split('\n')[0],
platform.python_version())
app1 = ["APP名称", applicationName, "用例总数", caseResult[0], "测试环境"]
app2 = ["APP大小", fileSize, "通过总数", caseResult[1]]
app3 = ["APP版本", applicationVersion, "失败总数", str(int(caseResult[2]) + int(caseResult[3]))]
app4 = ["测试日期", time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())), "测试耗时", timeConsuming + "秒"]
ws.write_merge(3, 5, 4, 4, dk, set_style(u'宋体', 270, False)) # 合并行单元格
for i in range(0, len(app1)):
# print app1[i]
ws.write(2, i, app1[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(app2)):
ws.write(3, i, app2[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(app3)):
ws.write(4, i, app3[i].decode('utf-8'), set_style(u'宋体',270,False))
for i in range(0, len(app4)):
ws.write(5, i, app4[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(arr)): # 写入第一行arr的内容
ws.write(9, i, arr[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(arr2)): # 写入第二行arr2的内容
ws.write(10, i, arr2[i].decode('utf-8'), set_style(u'宋体', 270, False))
createFolder('..\\report\\', '\\log') # 判断文件夹是否存在，不存在则创建
timestr = time.strftime('%Y-%m-%d-%H', time.localtime(time.time()))
path = '..\\report\\%s\\%s\\log\\%s_%s.xls' \
% (timestr[:10], timestr[11:], arr2[0].strip("\r"), arr2[1].strip("\r").replace(' ', '_'))
w.save(path.decode('utf-8')) # 以“品牌_机型”命名保存
print '导出结束'
def export_asset_xls(self, asset_catg_id):
workbook = xlwt.Workbook()
for asset_categ_id in self.assest_categ_ids:
asset_catg_ids = self.env['account.asset.asset'].search([
('category_id', '=', asset_categ_id.id),
('date', '>=', self.xlsx_date_from),
('date', '<=', self.xlsx_date_to)
])
fl = BytesIO()
style0 = xlwt.easyxf(
'font: name Times New Roman, color-index red, bold on',
num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
worksheet = workbook.add_sheet(asset_categ_id.name)
font = xlwt.Font()
font.bold = True
for_date = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz left,vertical center; borders: top thin, bottom thin, left thin, right thin ; pattern: pattern solid, fore_color %s;"
% '100')
for_work_location = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz left,vertical center; borders: top thin, bottom thin, left thin, right medium ; pattern: pattern solid, fore_color %s;"
% '100')
for_bottom_left = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz left,vertical center; borders: top thin, bottom medium, left thin, right medium ; pattern: pattern solid, fore_color %s;"
% '100')
for_center = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz center,vertical center; borders: top thin, bottom thin, left thin, right thin; pattern: pattern solid, fore_color %s;"
% '100')
for_center_right = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz center,vertical center; borders: top thin, bottom thin, left thin, right medium; pattern: pattern solid, fore_color %s;"
% '100')
for_center_left = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz center,vertical center; borders: top thin, bottom thin, left medium, right thin; pattern: pattern solid, fore_color %s;"
% '100')
for_string = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz left,vertical center; borders: top thin, bottom thin, left thin, right thin; pattern: pattern solid, fore_color %s;"
% '100')
for_last_col = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz center,vertical center; borders: top thin, bottom thin, left thin, right medium; pattern: pattern solid, fore_color %s;"
% '100')
for_last_row = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz center,vertical center; borders: top thin, bottom medium, left thin, right thin; pattern: pattern solid, fore_color %s;"
% '100')
for_last_row_col = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz center,vertical center; borders: top thin, bottom medium, left thin, right medium; pattern: pattern solid, fore_color %s;"
% '100')
for_center_heading = xlwt.easyxf(
"font:bold 1, name Verdana, color black, height 200; align: horiz center,vertical center; borders: top medium, bottom medium, left medium, right medium "
)
for_normal_border = xlwt.easyxf(
"font:bold 1, name Verdana, color black, height 200; align: horiz center, vertical center; borders: top medium, bottom medium, left medium, right medium; pattern: pattern solid, fore_color %s;"
% '100')
for_no_border = xlwt.easyxf(
"font: name Verdana, color black, height 200; align: horiz center, vertical center; borders: top thin, bottom thin, left thin, right thin; pattern: pattern solid, fore_color %s;"
% '100')
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_RIGHT
style = xlwt.easyxf(
'align: wrap yes; borders: top thin, bottom thin, left thin, right thin;'
)
style.num_format_str = '#,##0.00'
style_net_sal = xlwt.easyxf(
'font:bold 1; align: wrap yes; borders: top medium, bottom medium, left medium, right medium;'
)
style_net_sal.num_format_str = '#,##0.00'
for limit in range(1, 65536):
worksheet.row(limit).height = 400
worksheet.row(0).height = 300
worksheet.col(0).width = 2000
worksheet.col(1).width = 6000
worksheet.col(2).width = 3000
worksheet.col(3).width = 6000
worksheet.col(4).width = 7000
worksheet.col(5).width = 4000
worksheet.col(6).width = 5000
worksheet.col(7).width = 4000
worksheet.col(8).width = 4500
worksheet.col(9).width = 4000
worksheet.col(10).width = 4000
worksheet.col(11).width = 4000
worksheet.col(12).width = 4000
worksheet.col(13).width = 4000
worksheet.col(14).width = 4000
worksheet.col(15).width = 4000
worksheet.col(16).width = 4000
worksheet.col(17).width = 4000
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
inv_name_row = 6
worksheet.write(0, 0, 'Dost Steels Limited', style0)
worksheet.write(1, 0, 'Fixed Assets Register', style0)
worksheet.write(2, 0, asset_categ_id.name, style0)
worksheet.write(
3, 0, 'For the Period (' + datetime.strptime(
self.xlsx_date_from, '%Y-%m-%d').strftime('%m/%d/%y') +
'--' + datetime.strptime(
self.xlsx_date_to, '%Y-%m-%d').strftime('%m/%d/%y') + ')',
for_date)
# worksheet.write(inv_name_row, 0, 'Purchase',for_center)
worksheet.write_merge(inv_name_row, 7, 0, 0, 'Purchase',
for_center)
worksheet.write_merge(inv_name_row, 7, 1, 1, 'Supplier',
for_center)
worksheet.write_merge(inv_name_row, 7, 2, 2, 'Description',
for_center)
worksheet.write_merge(inv_name_row, 7, 3, 3, 'Location',
for_center_left)
worksheet.write_merge(
inv_name_row, 7, 4, 4,
datetime.strptime(self.xlsx_date_from,
'%Y-%m-%d').strftime('%m/%d/%y'), for_date)
worksheet.write(inv_name_row, 5, 'C', for_center)
worksheet.write(inv_name_row, 6, 'O', for_center)
worksheet.write(inv_name_row, 7, 'S', for_center)
worksheet.write(inv_name_row, 8, 'T', for_center)
worksheet.write(inv_name_row, 9, ' ', for_center)
worksheet.write(inv_name_row, 10, 'Rate', for_center)
worksheet.write(inv_name_row, 11, 'Accummulated Depreciation',
for_center)
worksheet.write_merge(inv_name_row, inv_name_row, 12, 15,
"Depreciation: ", for_center)
worksheet.write(inv_name_row, 16, 'Number Of Month', for_center)
worksheet.write(inv_name_row, 17, 'Total', for_center)
worksheet.write(inv_name_row, 18, 'Accumulated', for_center)
worksheet.write(inv_name_row, 19, 'Accumulated Depreciation',
for_center)
worksheet.write(inv_name_row, 20, 'WDV', for_center)
inv_name_row2 = 7
# worksheet.write(inv_name_row2, 4, datetime.strptime(self.xlsx_date_from, '%Y-%m-%d').strftime('%m/%d/%y') ,for_date)
worksheet.write(inv_name_row2, 5, 'Addition', for_string)
worksheet.write(inv_name_row2, 6, 'Deletion', for_string)
worksheet.write(inv_name_row2, 7, 'Revaluation', for_string)
worksheet.write(inv_name_row2, 8, 'impairment', for_string)
worksheet.write(
inv_name_row2, 9,
datetime.strptime(self.xlsx_date_to,
'%Y-%m-%d').strftime('%m/%d/%y'), for_date)
worksheet.write(inv_name_row2, 10, '%', for_center_right)
worksheet.write(
inv_name_row2, 11,
datetime.strptime(self.xlsx_date_from,
'%Y-%m-%d').strftime('%m/%d/%y'), for_date)
worksheet.write(inv_name_row2, 12, '1st QTR', for_center)
worksheet.write(inv_name_row2, 13, '2nd QTR', for_center)
worksheet.write(inv_name_row2, 14, '3rd QTR', for_center)
worksheet.write(inv_name_row2, 15, '4th QTR', for_center)
worksheet.write(inv_name_row2, 16, 'Used During Year', for_center)
worksheet.write(inv_name_row2, 17, 'Total Of Filtered dates',
for_center)
worksheet.write(inv_name_row2, 18, 'Depreciation Adj', for_center)
worksheet.write(
inv_name_row2, 19,
datetime.strptime(self.xlsx_date_to,
'%Y-%m-%d').strftime('%m/%d/%y'), for_date)
worksheet.write(
inv_name_row2, 20,
datetime.strptime(self.xlsx_date_to,
'%Y-%m-%d').strftime('%m/%d/%y'), for_date)
#adding information on sheet
inv_name_row3 = 9
for record in asset_catg_ids:
if record:
purchase = record.x_studio_field_GPSJG or ' '
supplier = record.partner_id.name or ''
description = record.name
location = record.x_studio_field_EZFjg
asset_cost = record.cost_value #cost value
rate = (record.method_progress_factor * 100) or ''
accumulated_depr_from = record.x_studio_field_6a8eF or 0.0
qtr_calculation = self.get_atr(record)
qtr_1st = qtr_calculation.get('qtr_1st') or 0.0
qtr_2nd = qtr_calculation.get('qtr_2nd') or 0.0
qtr_3rd = qtr_calculation.get('qtr_3rd') or 0.0
qtr_4th = qtr_calculation.get('qtr_4th') or 0.0
year_month = 12
total_of_yr = qtr_1st + qtr_2nd + qtr_3rd + qtr_4th
accumulated_depr_to = asset_cost + total_of_yr
WDV = accumulated_depr_to - asset_cost
worksheet.write(inv_name_row3, 0, purchase, for_center)
worksheet.write(inv_name_row3, 1, supplier, for_string)
worksheet.write(inv_name_row3, 2, description, for_string)
worksheet.write(inv_name_row3, 3, location, for_center)
worksheet.write(inv_name_row3, 4, asset_cost, for_center)
worksheet.write(inv_name_row3, 5, ' ', for_center)
worksheet.write(inv_name_row3, 6, ' ', for_center)
worksheet.write(inv_name_row3, 7, ' ', for_center)
worksheet.write(inv_name_row3, 8, ' ', for_center)
worksheet.write(inv_name_row3, 9, ' ', for_center)
worksheet.write(
inv_name_row3,
10,
rate,
for_center_left,
)
worksheet.write(inv_name_row3, 11, accumulated_depr_from,
style)
#This Quarters is set if finacial year is july to Jun (qtr_3rd,,qtr_1st,qtr_2nd)
#if finacial year is Jun to Dec then just below field change according to (qtr_1st,qtr_2nd,qtr_3rd,qtr_th)
worksheet.write(inv_name_row3, 12, qtr_3rd, style)
worksheet.write(inv_name_row3, 13, qtr_4th, style)
worksheet.write(inv_name_row3, 14, qtr_1st, style)
worksheet.write(inv_name_row3, 15, qtr_2nd, style)
worksheet.write(inv_name_row3, 16, year_month, for_center)
worksheet.write(inv_name_row3, 17, total_of_yr, style)
worksheet.write(inv_name_row3, 18, ' ', style)
worksheet.write(inv_name_row3, 19, accumulated_depr_to,
style)
worksheet.write(inv_name_row3, 20, WDV, style)
inv_name_row3 += 1
workbook.save(fl)
fl.seek(0)
self.write({
'report_file': base64.encodestring(fl.getvalue()),
'name': 'Assets.xls'
})
self.visible = False
return {
'type': 'ir.actions.act_window',
'view_type': 'form',
'view_mode': 'form',
'res_model': 'wizard.asset.asset.history',
'target': 'new',
'res_id': self.id,
}
