class Column(Cell):
title_fnt = Font()
title_fnt.name = 'Calibri'
title_fnt.bold = True
title_fnt.height = 12 * 20 # font size 12
obj_fnt = Font()
obj_fnt.name = 'Calibri'
obj_fnt.height = 12 * 20 # font size 12
title_aln = Alignment()
title_aln.horz = Alignment.HORZ_CENTER
title_aln.vert = Alignment.VERT_CENTER
title_aln.wrap = Alignment.WRAP_AT_RIGHT
title_ptn = Pattern()
title_ptn.pattern = Pattern.SOLID_PATTERN
title_ptn.pattern_fore_colour = 0x2C
def __init__(self,
text,
left_border=False,
right_border=False,
use_pattern=False,
width=0x0E00,
func=lambda obj: ""):
super(Column, self).__init__(text,
self.title_fnt,
self.title_aln,
Borders(),
self.title_ptn,
width=width)
self.obj_style = XFStyle()
self.obj_style.alignment = self.title_aln
self.obj_style.borders = Borders()
self.obj_style.borders.left = Borders.HAIR
self.obj_style.borders.right = Borders.HAIR
self.obj_style.borders.top = Borders.HAIR
self.obj_style.borders.bottom = Borders.HAIR
self.border.left = Borders.HAIR
self.border.right = Borders.HAIR
self.border.top = Borders.THICK
self.border.bottom = Borders.THICK
if left_border:
self.obj_style.borders.left = Borders.THICK
self.border.left = Borders.THICK
if right_border:
self.obj_style.borders.right = Borders.THICK
self.border.right = Borders.THICK
self.obj_style.font = self.obj_fnt
if use_pattern:
self.obj_style.pattern = self.pattern
self.func = func
def RUNN():
# ghi file excel
fnt = Font()
fnt.name = "Times New Roman"
fnt.height = 280
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.font = fnt
style.borders = borders
wb = Workbook()
sheet1 = wb.add_sheet("Điểm")
col = 0
row = 0
sheet1.write(row, col, "STT", style)
sheet1.write(row, col + 1, "TÊN BÀI LÀM", style)
sheet1.write(row, col + 2, "ĐIỂM", style)
row += 1
diem = []
st = 1
for i in t:
if i == name_form: continue
else:
try:
t1 = docx.Document(i)
sheet1.write(row, col, st, style)
sheet1.write(row, col + 1, i[:-5], style)
sheet1.write(row, col + 2, round(10 * so_sanh(t1), 2), style)
row += 1
st += 1
except Exception: pass
wb.save("KetQua.xls")
def export_to_excel(self, **kwargs):
work_book = Workbook(encoding="ascii")
center_alignment = Alignment()
center_alignment.horz = Alignment.HORZ_RIGHT
center_alignment.vert = Alignment.VERT_CENTER
border = Borders()
border.top = Borders.THIN
border.left = Borders.THIN
border.bottom = Borders.THIN
border.right = Borders.THIN
head_style = XFStyle()
head_pattern = Pattern()
head_pattern.pattern = Pattern.SOLID_PATTERN
head_pattern.pattern_fore_colour = Style.colour_map['gray25']
head_style.pattern = head_pattern
head_font = Font()
head_font.bold = True
head_style.font = head_font
head_style.alignment = center_alignment
head_style.borders = border
data_style = XFStyle()
data_style.alignment = center_alignment
data_style.borders = border
work_sheet = work_book.add_sheet("Sheet")
limit = 10000
count = self.get_data_scale(**kwargs)['count']
times = (count + limit - 1) // limit
add_header = False
for i in range(1):
kwargs["startIndex"] = i * limit + 1
kwargs['endIndex'] = (i + 1) * limit + 1
records = self.get_data(**kwargs)
column_names = []
y = 0
for data in records:
if not add_header:
x = 0
for column_name in data.keys():
work_sheet.write(y, x, column_name, head_style)
column_names.append(column_name)
x += 1
add_header = True
y += 1
x = 0
for column_name in column_names:
work_sheet.write(y, x, data[column_name], data_style)
x += 1
y += 1
x = 0
work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
class ReportStyles():
alignment = Alignment()
font = Font()
borders = Borders()
# col = Column()
def borders_light(self):
self.borders.left = Borders.THIN
self.borders.right = Borders.THIN
self.borders.top = Borders.THIN
self.borders.bottom = Borders.THIN
return self.borders
def align_hor_right(self):
self.alignment.horz = Alignment.HORZ_RIGHT
return self.alignment
def align_hor_left(self):
self.alignment.horz = Alignment.HORZ_LEFT
return self.alignment
def align_hor_center(self):
self.alignment.horz = Alignment.HORZ_CENTER
return self.alignment
def text_bold(self):
self.font.bold = True
return self.font
def set_style(self, isblod):
style = XFStyle()
fnt = Font() # 创建一个文本格式，包括字体、字号和颜色样式特性
fnt.name = u'微软雅黑' # 设置其字体为微软雅黑
fnt.bold = isblod
style.font = fnt
return style
def css2excel(css):
#custom_css = 'font: name "%s", %s on'%(current_value['font-family'].split(",")[0], current_value['font-weight'].split(",")[0])
#export_sheet.write(row_idx, col_idx, current_value['data'], xlwt.easyxf('font: italic on; pattern: pattern solid, fore-colour grey25'))
xf_list = []
fnt = Font()
borders = Borders()
pattern = Pattern()
align = Alignment()
process_css = {
'font-family' : [fnt, "name" , lambda x : x.split(",")[0]],
'color' : [fnt, "colour_index", lambda x : excel_color_map.get(x,0)+8],
'font-weight' : [fnt, "bold", lambda x : x.upper() == 'BOLD'],
#'font-weight' : [fnt, "bold", lambda x : x == '700'],
'text-align' : [align, "horz", lambda x : {'left':align.HORZ_LEFT, 'right':align.HORZ_RIGHT, 'center':align.HORZ_CENTER, 'justified': align.HORZ_JUSTIFIED}[x]],
'background-color' : [pattern,"pattern_fore_colour", lambda x: excel_color_map.get(x,16)+8],
}
#TODO process_css -> css
for i in process_css.keys():
#print process_css[i][0] ,".",process_css[i][1], " => " , css[i] ," | ", process_css[i][2](css[i])
setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i]))
style = XFStyle()
style.font = fnt
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style.borders = borders
style.pattern = pattern
style.pattern.pattern = 1
style.alignment = align
return style
def get_style(
font_specs=dict(),
border_specs=dict(),
pattern=None,
num_format_str=None,
):
style_key = (freeze(font_specs), freeze(border_specs), pattern,
num_format_str)
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr(style.font, key, value)
style.borders = Borders()
for key, value in border_specs.items():
setattr(style.borders, key, value)
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[style_key] = style
return style
def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment style.borders = borders #~ style.pattern = pattern return style
def get_report_info_perid(
self,
cmd,
diff_time,
thre_time,
max_number,
):
# 发送要执行的命令
pre_time_stamp = [0] * 4
self._channel.send(cmd + '\r')
# 回显很长的命令可能执行较久，通过循环分批次取回回显
index = [0] * 4
line_counter = 0
line_feed_byte = '\n'.encode(self.encoding)
while True:
buffer = self._channel.recv(65535)
if len(buffer) == 0:
logger.info('end______________')
break
lines = buffer.split(line_feed_byte)
for current_line in lines:
style = None
line = current_line.decode(self.encoding)
# logger.debug('shell显示：%s' % line)
col = self.check_type(line)
if not line.startswith(self.rq) or col == 10:
line_counter += 1
continue
time_stamp = int(
time.mktime(
time.strptime(' '.join([line[:8], line[9:17]]),
"%Y%m%d %H:%M:%S")))
time_stamp_dec = line[18:21] # 精确到毫秒
time_stamp = time_stamp * 1000 + int(time_stamp_dec)
logger.info('%s:%s' % (senior_name[col], time_stamp))
if pre_time_stamp[col] == 0:
pre_time_stamp[col] = time_stamp
else:
if abs((time_stamp - pre_time_stamp[col]) -
diff_time[col]) > thre_time[col]:
logger.error('两帧数据间隔为{}ms,时间戳分别为:({},{}),行号：{}'.format(
time_stamp - pre_time_stamp[col], time_stamp,
pre_time_stamp[col], index[col]))
style = XFStyle()
fnt = Font()
fnt.name = u'微软雅黑' # 设置其字体为微软雅黑
fnt.colour_index = 2 # 设置其字体颜色
fnt.bold = True
style.font = fnt
self.write_xl(index[col] + 1, col * 2, time_stamp)
self.write_xl(index[col] + 1,
col * 2 + 1,
time_stamp - pre_time_stamp[col],
style=style)
index[col] += 1
pre_time_stamp[col] = time_stamp
line_counter += 1
def excelWriter(self, filename):
workbook1 = xlwt.Workbook()
sheet1 = workbook1.add_sheet('表01')
sheet1.write(0, 0, 'Unformated value')
sheet1.write(0, 1, '大家好')
# 设置字体样式
font1 = Font()
font1.name = '华文琥珀'
style = XFStyle()
style.font = font1
sheet1.write(1, 0, 'Formated value', style)
workbook1.save(filename)
def Style1():
style = XFStyle()
fnt = Font()
fnt.name = u'微软雅黑'
fnt.bold = True
style.font = fnt
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment # 给样式添加文字居中属性
style.font.height = 430 # 设置字体大小
return style
def __init__(self, timesheets, year=datetime.date.today().year):
self.timesheets = timesheets.order_by('activity__person', 'project',
'year', 'month')
self.book = Workbook()
self.year = int(year)
font_header = Font()
font_header.bold = True
font_title = Font()
font_title.bold = True
grey_pattern = Pattern()
grey_pattern.pattern = Pattern.SOLID_PATTERN
grey_pattern.pattern_fore_colour = Style.colour_map['gray25']
self.header_style = XFStyle()
self.header_style.pattern = grey_pattern
self.header_style.font = font_header
self.header_title = XFStyle()
self.header_title.font = font_title
self.date_style = XFStyle()
self.date_style.num_format_str = 'M/D/YY'
self.title_style = XFStyle()
self.title_style.pattern = grey_pattern
self.title_style.font = font_title
self.title_style.alignment.horz = self.title_style.alignment.HORZ_CENTER
self.txt_centered_al = Alignment()
self.txt_centered_al.horz = Alignment.HORZ_CENTER
self.txt_centered = XFStyle()
self.txt_centered.alignment = self.txt_centered_al
self.header_date_style = XFStyle()
self.header_date_style.pattern = grey_pattern
self.header_date_style.font = font_header
self.header_date_style.alignment = self.txt_centered_al
def writLabel(self):
"""向excel写入内容，内容自定义"""
style = XFStyle() # 初始化样式
font = Font() # 为样式创建字体
font.name = 'Times New Roman'
# font.bold = True # 黑体
# font.underline = True # 下划线
# font.italic = True # 斜体字
style.font = font # 设定样式
for i in range(10):
for j in range(5):
self.excel_w_sheet.write(i,j, label = 'admin%d%d'%(i,j))# 参数对应 行, 列, 值
self.excel_w.save('Excelw.xls')
def write_file(headers, data):
wb = Workbook()
font = Font()
style = XFStyle()
page = wb.add_sheet('All Data')
for i in range(len(data)):
font.bold = True
style.font = font
page.write(0, i, headers[i], style=style)
for j in range(len(data[0])):
page.write(j + 1, i, data[i][j])
wb.save(search_excel_file)
def write_file(headers, data):
wb = Workbook()
font = Font()
style = XFStyle()
page = wb.add_sheet('All Data')
for i in range(len(data)):
font.bold = True
style.font = font
page.write(0, i, headers[i], style=style)
for j in range(len(data[i])):
page.write(j + 1, i, data[i][j])
wb.save('info/misc/rating_plot.xls')
def def_style():
style = XFStyle()
font = Font() # 这部分设置字体
font.name = 'Times New Roman' # 或者换成外面传进来的参数，这样可以使一个函数定义所有style
# font.bold = 'True'
# font.height = 24
# font.size = 2000
# font.colour_index = 3
style.font = font
alignment = Alignment() # 这部分设置居中格式
alignment.horz = Alignment.HORZ_CENTER # 水平居中
alignment.vert = Alignment.VERT_CENTER # 垂直居中
style.alignment = alignment
return style
def _getStyle(self, org_style, org_font):
from xlwt import XFStyle, Font, Borders, Pattern
# font
font = Font()
font.name = org_font.name
font.height = org_font.height
font.italic = org_font.italic
font.struck_out = org_font.struck_out
font.outline = org_font.outline
font.shadow = org_font.shadow
font.colour_index = org_font.colour_index
font.bold = org_font.bold
font._weight = org_font.weight
font.escapement = org_font.escapement_type
font.underline = org_font.underline_type
font.family = org_font.family
font.charset = org_font.character_set
# border
borders = Borders()
borders.left = Borders.THIN #org_style.xf.border.left_line_style
borders.right = Borders.THIN #org_style.xf.border.right_line_style
borders.top = Borders.THIN #org_style.xf.border.top_line_style
borders.bottom = Borders.THIN #org_style.xf.border.bottom_line_style
#borders.diag = self.NO_LINE
borders.left_colour = org_style.xf.border.left_colour_index
borders.right_colour = org_style.xf.border.right_colour_index
borders.top_colour = org_style.xf.border.top_colour_index
borders.bottom_colour = org_style.xf.border.bottom_colour_index
#borders.diag_colour = org_style.xf.border.left_colour_index
#borders.need_diag1 = self.NO_NEED_DIAG1
#borders.need_diag2 = self.NO_NEED_DIAG2
# Pattern
pattern = Pattern()
pattern.pattern = org_style.xf.background.fill_pattern
pattern.pattern_fore_colour = org_style.xf.background.pattern_colour_index
pattern.pattern_back_colour = org_style.xf.background.background_colour_index
#
style = XFStyle()
style.borders = borders
style.font = font
style.pattern = pattern
return style
def setupFormat(self):
headFont = Font()
headFont.bold = True
alignmentSetup = Alignment()
alignmentSetup.wrap = True
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
self.header_style = XFStyle()
self.header_style.font = headFont
self.table_style = XFStyle()
self.table_style.borders = borders
self.table_style.alignment = alignmentSetup
def __init__(self):
self.style = XFStyle()
base_font = Font() # 设置基本字体
base_font.height = FONT_SIZE_UNIT * 12
self.style.font = base_font
alignment = Alignment() # 设置对齐
alignment.horz = Alignment.HORZ_CENTER
alignment.vert = Alignment.VERT_CENTER
self.style.alignment = alignment
borders = Borders() # 设置边框
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
borders.left_colour = COLOUR_BLACK
borders.right_colour = COLOUR_BLACK
borders.top_colour = COLOUR_BLACK
borders.bottom_colour = COLOUR_BLACK
self.style.borders = borders
self.style.num_format_str = '#,##0' # 设置数字格式
def create_act(self):
template = open_workbook(self.template, formatting_info=1)
new_file = copy(template)
sheet = new_file.get_sheet(0)
for cell in cells_conf:
print('Coздаем ячейку {}'.format(cell['name']))
style = XFStyle()
font = Font()
style.alignment.horz = cell['align_horz']
font.name = cell['fontname']
font.bold = cell['fontbold']
font.height = cell['fontsize']
style.font = font
name = cell['name']
sheet.write(cell['row'], cell['col'], self._get_str(name), style)
new_file.save(self.path)
def downloadTrue(request):
response = HttpResponse(mimetype=u'application/ms-excel')
filename = u'错误实名制列表.xls'
response['Content-Disposition'] = (u'attachment;filename=%s' %
filename).encode('utf-8')
import xlwt
from xlwt import Font, Alignment
style1 = xlwt.XFStyle()
font1 = Font()
font1.height = 250
font1.name = u'仿宋'
style1.font = font1
algn = Alignment()
algn.horz = Alignment.HORZ_LEFT
style1.alignment = algn
style1.font = font1
wb = xlwt.Workbook()
ws = wb.add_sheet(u"错误实名制列表", cell_overwrite_ok=True)
rownum = 0
ws.write_merge(rownum, rownum, 0, 0, u'手机号', style1)
ws.write_merge(rownum, rownum, 1, 1, u'姓名', style1)
ws.write_merge(rownum, rownum, 2, 2, u'身份证号', style1)
ws.write_merge(rownum, rownum, 3, 3, u'地址', style1)
ws.write_merge(rownum, rownum, 4, 4, u'错误原因', style1)
rownum += 1
for o in Truename.objects.filter(status=2).order_by('datetime'):
ws.write_merge(rownum, rownum, 0, 0, o.tel, style1)
ws.write_merge(rownum, rownum, 1, 1, o.name, style1)
ws.write_merge(rownum, rownum, 2, 2, o.number, style1)
ws.write_merge(rownum, rownum, 3, 3, o.address, style1)
ws.write_merge(rownum, rownum, 4, 4, o.help, style1)
rownum += 1
for i in range(5):
ws.col(i).width = 256 * 20
wb.save(response)
return response
def css2excel(css):
fnt = Font()
borders = Borders()
pattern = Pattern()
align = Alignment()
process_css = {
'font-family': [fnt, "name", lambda x: x.split(",")[0]],
'font-size': [fnt, "height", lambda x: get_font_height(x.upper())],
'color': [fnt, "colour_index", lambda x: match_color_index(x.upper())],
'font-weight': [fnt, "bold", lambda x: x == 'bold'],
'font-style': [fnt, "italic", lambda x: x == 'italic'],
'text-align':
[align, "horz", lambda x: get_horizontal_align(x, align)],
'vertical-align':
[align, "vert", lambda x: get_vertical_align(x, align)],
'background-color': [
pattern, "pattern_fore_colour",
lambda x: match_color_index(x.upper())
],
}
for i in css.keys():
if i in process_css.keys():
setattr(process_css[i][0], process_css[i][1],
process_css[i][2](css[i].strip()))
style = XFStyle()
style.font = fnt
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style.borders = borders
style.pattern = pattern
style.pattern.pattern = 1
style.alignment = align
style.num_format_str = '#,##0.00'
return style
def get_sheet_title_style(bg_color=0x39,
font_color=0x0,
font_size=300,
has_pattern=True,
horz_center=True):
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = font_color
fnt.bold = True
fnt.height = font_size
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
al = Alignment()
if horz_center:
al.horz = Alignment.HORZ_CENTER
else:
al.horz = Alignment.HORZ_LEFT
al.vert = Alignment.VERT_CENTER
pattern = None
if has_pattern:
pattern = Pattern()
pattern.pattern = 1
pattern.pattern_fore_colour = bg_color
pattern.pattern_back_colour = bg_color
style = XFStyle()
style.font = fnt
style.borders = borders
style.alignment = al
if pattern:
style.pattern = pattern
return style
def main():
lst = []
path = r'./Homework/03/'
with open(path + 'test.txt', 'r') as fpin:
title = fpin.readline().strip().replace('\n', '').split(',')
while True:
s = fpin.readline().strip().replace('\n', '').split(',')
if s == ['']:
break
lst.append(s)
fpin.close()
book = Workbook()
sheet1 = book.add_sheet('data')
style = XFStyle()
font = Font()
font.name = '宋体'
font.height = 280
style.font = font
alignment = Alignment()
alignment.horz = Alignment.HORZ_CENTER
alignment.vert = Alignment.VERT_CENTER
style.alignment = alignment
row = sheet1.row(0)
for i, j in zip(range(len(title)), title):
row.write(i, j, style=style)
for i, j in zip(range(1, len(lst) + 1), lst):
row = sheet1.row(i)
for x, y in zip(range(len(j)), j):
row.write(x, y, style=style)
book.save(path + 'data.xls')
def in_ket_qua():
# tao file ketqua.xls de ghi ket qua
fnt = Font()
fnt.name = "Times New Roman"
fnt.height = 280
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.font = fnt
style.borders = borders
wb = Workbook()
sheet1 = wb.add_sheet("Điểm")
col = 0
row = 0
sheet1.write(row, col, "STT", style)
sheet1.write(row, col + 1, "TÊN BÀI LÀM", style)
sheet1.write(row, col + 2, "ĐIỂM", style)
row += 1
st = 1
# Tinh ket qua cua tat ca cac file bai lam va in ket qua ra file KetQua.xls
for name in list_file_word:
sheet1.write(row, col, st, style)
sheet1.write(row, col + 1, name[:-5], style)
sheet1.write(row, col + 2, tinhKetQua(name), style)
row += 1
st += 1
print(name, "___ĐÃ CHẤM XONG___")
# Xoa bo cac file anh sau khi da xu li xong
for name in glob.glob("*.png"):os.remove(name)
# Luu ket qua ra file
os.chdir(local)
# shutil.rmtree('Temp')
wb.save("KetQua.xls")
def exportReport5ToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1');
sheet1.col(1).width = 256*80;
sheet1.col(2).width = 256*10;
sheet1.col(3).width = 256*20;
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
wrap = Alignment();
wrap.wrap = 1;
wrap.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
styleWrap = XFStyle();
styleWrap.alignment = wrap;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
styleDate.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = wrap;
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
#sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') );
row1.write(1, ('รายละเอียด').decode('UTF8'),styleHead);
row1.write(2, ('วันที่รายงาน').decode('UTF8'),styleHead );
row1.write(3, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead);
i=i+1;
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('risk_management_id') ,styleRowDetail );
row1.write(1, value.get('risk_detail').decode('UTF8'),StyleRowDetailWrap );
#sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(2, value.get('report_date') ,styleDate );
row1.write(3, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
text = "(" + sub.get('risk_team').decode('UTF8') + " ) " + sub.get('result').decode('UTF8');
row1.write(1, text ,StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simpleReport5.xls');
book.save(dirTempFile);
return dirTempFile;
def produce_summary(self, cr, uid, travel, context=None):
number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]')
total_fnt = Font()
total_fnt.name = 'Calibri'
total_fnt.bold = True
total_fnt.height = 16 * 20 # font size 12
total_cell_l_style = XFStyle()
total_cell_l_style.alignment = Column.title_aln
total_cell_l_style.borders = Borders()
total_cell_l_style.borders.left = Borders.THICK
total_cell_l_style.borders.right = Borders.HAIR
total_cell_l_style.borders.top = Borders.THICK
total_cell_l_style.borders.bottom = Borders.THICK
total_cell_l_style.pattern = Column.title_ptn
total_cell_l_style.num_format_str = number_format
total_cell_l_style.font = Column.obj_fnt
total_cell_r_style = XFStyle()
total_cell_r_style.alignment = Column.title_aln
total_cell_r_style.borders = Borders()
total_cell_r_style.borders.left = Borders.HAIR
total_cell_r_style.borders.right = Borders.THICK
total_cell_r_style.borders.top = Borders.THICK
total_cell_r_style.borders.bottom = Borders.THICK
total_cell_r_style.pattern = Column.title_ptn
total_cell_r_style.num_format_str = number_format
total_cell_r_style.font = Column.obj_fnt
sub_total_cell_label = Cell(_(u'SOUS-TOTAL'), Column.title_fnt,
Column.title_aln,
total_cell_l_style.borders,
Column.title_ptn)
total_cell_label = Cell(_(u'TOTAL'), total_fnt, Column.title_aln,
total_cell_r_style.borders, Column.title_ptn,
number_format)
journeys = [i for i in travel.journey_ids]
w = Workbook()
ws = w.add_sheet(_('Travel Summary'))
ws.row(2).height = 0x0280
ws.row(3 + len(journeys)).height = 0x0140
ws.row(4 + len(journeys)).height = 0x0180
row = 0
row += 2
# Write headers
for i, col in enumerate(self._excel_columns):
ws.col(i).width = col.width
ws.write(row, i, col.text, col.style)
row += 1
for i, obj in enumerate(journeys):
ws.write(row + i, 0, i + 1, self._excel_columns[0].obj_style)
for j in xrange(1, len(self._excel_columns)):
ws.write(row + i, j, self._excel_columns[j].func(obj),
self._excel_columns[j].obj_style)
row += len(journeys)
rate_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('TICKET RATE')) - 1
cost_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('COSTS')) - 1
total_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('TOTAL')) - 1
# Sub total label
ws.write_merge(row, row, 0, rate_index, sub_total_cell_label.text,
sub_total_cell_label.style)
# Sub totals
ws.write(
row, rate_index + 1,
Formula("SUM(%s%d:%s%d)" %
(chr(66 + rate_index), 4, chr(66 + rate_index), row)),
total_cell_l_style)
ws.write(
row, rate_index + 2,
Formula("SUM(%s%d:%s%d)" %
(chr(66 + cost_index), 4, chr(66 + cost_index), row)),
total_cell_r_style)
total_top_underline_style = XFStyle()
total_top_underline_style.borders = Borders()
total_top_underline_style.borders.top = Borders.THICK
total_top_underline_style.font = Column.obj_fnt
# Draw a line above total to close box
ws.write(row, total_index + 1, "", total_top_underline_style)
row += 1
# Total label
ws.write_merge(row, row, 0, rate_index, total_cell_label.text,
total_cell_label.style)
# Total
ws.write_merge(
row, row, rate_index + 1, total_index,
Formula("%s%d+%s%d" %
(chr(66 + rate_index), row, chr(66 + cost_index), row)),
total_cell_label.style)
return w
def write_sheet(self, data, sheet_name, print_to_screen=False):
'''Write a very simple table to a new sheet in a spreadsheet,
Optionally, print the table to the screen'''
# most cells
al = Alignment()
al.horz = Alignment.HORZ_RIGHT
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.height = 9 * 20 # 9 pt
style = XFStyle()
style.font = font
style.alignment = al
# tops cells
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.bold = True
font.height = 9 * 20 # 9 pt
style_top = XFStyle()
style_top.font = font
style_top.alignment = al
# left cells
al = Alignment()
al.horz = Alignment.HORZ_LEFT
al.vert = Alignment.VERT_CENTER
font = Font()
font.name = 'Arial'
font.bold = True
font.italic = True
font.height = 9 * 20 # 9 pt
style_left = XFStyle()
style_left.font = font
style_left.alignment = al
ws = self.add_sheet(sheet_name)
for i, row in enumerate(data):
for j, cell in enumerate(row):
borders = Borders()
if i == 0:
borders.top = 1
borders.bottom = 2
if i == len(row) - 1:
borders.bottom = 1
if j == 0:
borders.left = 1
borders.right = 1
if j == len(row) - 1:
borders.right = 1
if j == 0:
_style = style_left
elif i == 0:
_style = style_top
else:
_style = style
_style.borders = borders
ws.write(i + 1, j + 1, cell, _style)
if print_to_screen:
print print_table(data, sheet_name, bold=True)
def create_template(self):
try:
open_workbook(self.template_path)
except FileNotFoundError:
# 文件夹下无模板文件，直接创建然后导入数据
wb = Workbook(encoding='ascii')
ws = wb.add_sheet("1")
style1 = XFStyle()
font1 = Font()
font1.bold = True
style1.font = font1
ws.write(0, 0, OLD_FILE_NAME, style1)
ws.write(0, 1, NEW_FILE_NAME, style1)
style2 = XFStyle()
font2 = Font()
font2.bold = True
font2.colour_index = 2
style2.font = font2
ws.write(1, 3, TEMPLATE_HINT, style2)
new_row_id = 1
for i in range(len(self.table_name_list0)):
if i not in self.disable_pos_list:
ws.write(new_row_id, 0, self.table_name_list0[i])
new_row_id += 1
wb.save(self.template_path)
return
# 文件夹下已经存在模板文件
# 暂时不检查完整性
rb = open_workbook(self.template_path, formatting_info=True)
r_sheet = rb.sheet_by_index(0)
wb = copy(rb)
sheet = wb.get_sheet(0)
def update_old_name(old_name_list, disable_pos_list):
needed_add_name_list = []
old_name_index = 0 # 旧文件名列序号
name0_list = r_sheet.col_values(old_name_index,
start_rowx=1,
end_rowx=None)
for i in range(len(old_name_list)):
if i not in disable_pos_list:
if old_name_list[i] not in name0_list:
needed_add_name_list.append(old_name_list[i])
if len(needed_add_name_list) == 0:
print("没有文件名需要添加到模板文件中，或者所有文件名已经添加")
return
n_row = r_sheet.nrows
print("原模板文件中已经有" + str(n_row) + "行")
for name in needed_add_name_list:
print("将文件名" + name + "添加到模板文件中")
sheet.write(n_row, old_name_index, name)
n_row += 1
update_old_name(self.table_name_list0, self.disable_pos_list)
try:
wb.save(self.template_path)
except PermissionError:
messagebox.showwarning("模板文件异常", "请关闭文件夹下的模板文件后再重新导入")
return
messagebox.showinfo("一切正常", "文件导入成功，模板文件已生成。\n请打开对应文件夹的模板文件编辑新文件名")
def exportToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
sheet1.col(1).width = 256*20;
sheet1.col(2).width = 256*80;
sheet1.col(3).width = 256*10;
sheet1.col(4).width = 256*20;
default_book_style = book.default_style
default_book_style.font.height = 20 * 36 # 36pt
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
algn1 = Alignment();
algn1.wrap = 1;
#algn1.horz = Alignment.HORZ_CENTER
#algn1.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
print "export";
if( objectProject):
i=0;
print "start" ;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead );
# row1.write(1, ('รายละเอียด').decode('UTF8'));
row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead );
row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead );
i=i+1;
style1 = XFStyle();
style1.alignment = algn1;
#style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on')
for value in objectProject:
row1 = sheet1.row(i) ;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = algn1;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
row1.write(0, value.get('risk_management_id'),styleRowDetail );
#row1.write(1, value.get('risk_detail').decode('UTF8') , style1);
sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(3, value.get('report_date') ,styleDate);
row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead );
sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail );
sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simple.xls');
print dirTempFile;
book.save(dirTempFile);
