def write_excel(all_infos):
print('开始合并数据......')
wbk = xlwt.Workbook(encoding='utf-8')
sheet = wbk.add_sheet('sheet 1')
font = xlwt.Font() # Create Font
font.bold = True # 加粗
style_title = xlwt.XFStyle() # 标题加粗居中宋体
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
style_title.borders = borders
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style_title.alignment = alignment
font = xlwt.Font()
font.name = '宋体'
font.bold = True
style_title.font = font # font属性添加进style，否则字体设置无效
style_content = xlwt.XFStyle() # 正文居中宋体
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style_content.alignment = alignment
font = xlwt.Font()
font.name = '宋体'
style_content.font = font
style_num_align = xlwt.XFStyle() # 数字居中Times New Roman
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style_num_align.alignment = alignment
font = xlwt.Font()
font.name = 'Times New Roman'
style_num_align.font = font
style_num = xlwt.XFStyle() # 数字不居中Times New Roman
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style_num.alignment = alignment
font = xlwt.Font()
font.name = 'Times New Roman'
style_num.font = font
sheet.write_merge(0, 0, 0, 10, '现金银行存款日记账', style_title)
sheet.write_merge(1, 2, 0, 2, '2017年度', style_title) #合并行行列列
sheet.write_merge(3, 4, 0, 0, '月', style_title)
sheet.write_merge(3, 4, 1, 1, '日', style_title)
sheet.write_merge(3, 4, 2, 2, '编号', style_title)
sheet.write_merge(1, 4, 3, 3, '摘要', style_title)
sheet.write_merge(1, 4, 4, 4, '银行名称', style_title)
sheet.write_merge(1, 4, 5, 5, '类型', style_title)
sheet.write_merge(1, 3, 6, 8, '合计', style_title)
sheet.write_merge(1, 3, 9, 11, '库存现金', style_title)
sheet.write(4, 6, '收', style_title)
sheet.write(4, 7, '付', style_title)
sheet.write(4, 8, '结存', style_title)
sheet.write(4, 9, '收', style_title)
sheet.write(4, 10, '付', style_title)
sheet.write(4, 11, '结存', style_title)
sheet.write_merge(1, 1, 12, 11 + 3 * len(all_infos), '银行存款',
style_title) #
info_count = 0 #初始化一个变量来保存已经写入的行数，因为不同银行所在列不同
all_money_ins = []
all_money_outs = []
all_money_nows = []
for i in range(len(all_infos)):
sheet.write_merge(2, 2, 12 + 3 * i, 14 + 3 * i, all_infos[i][0][0:4],
style_title)
sheet.write_merge(3, 3, 12 + 3 * i, 14 + 3 * i, all_infos[i][1],
style_title)
sheet.write(4, 12 + 3 * i, '收', style_title)
sheet.write(4, 13 + 3 * i, '付', style_title)
sheet.write(4, 14 + 3 * i, '结存', style_title)
# all_money_ins = all_money_ins + all_infos[i][-1]['money_in'] if all_infos[i][-1]['money_in'] != '' else 0
# all_money_outs = all_money_ins + all_infos[i][-1]['money_out'] if all_infos[i][-1]['money_in'] != '' else 0
# all_money_nows = all_money_ins + all_infos[i][-1]['money_now'] if all_infos[i][-1]['money_in'] != '' else 0
all_money_in = Decimal('0.00')
all_money_out = Decimal('0.00')
for j in range(len(all_infos[i]) - 2):
#下面需要通过正则匹配获取日期中正整数，如20170102，取出01时要输出1
sheet.write(
5 + j + info_count, 0,
re.findall(r"[1-9]\d*",
str(all_infos[i][j + 2]['date'])[4:6]),
style_num_align)
sheet.write(
5 + j + info_count, 1,
re.findall(r"[1-9]\d*",
str(all_infos[i][j + 2]['date'])[6:8]),
style_num_align)
sheet.write(5 + j + info_count, 2, j + info_count + 1,
style_num_align)
sheet.write(5 + j + info_count, 3, all_infos[i][j + 2]['beizhu'],
style_content)
sheet.write(5 + j + info_count, 4, all_infos[i][0][0:4],
style_content)
sheet.write(5 + j + info_count, 5, all_infos[i][0][4:],
style_content)
#第一个参数，行，j为其中一个银行流水线数据中第j行，第一个银行数据输入完后，要把行数存到info_count，下一个银行要从下面继续输入，
#因为不同银行的同一类型数据要写在不同行不同列，第二个参数也要自动确认列数
sheet.write(5 + j + info_count, 12 + 3 * i,
all_infos[i][j + 2]['money_in'], style_num)
sheet.write(5 + j + info_count, 13 + 3 * i,
all_infos[i][j + 2]['money_out'], style_num)
sheet.write(5 + j + info_count, 14 + 3 * i,
all_infos[i][j + 2]['money_now'], style_num)
sheet.write(5 + j + info_count, 6, all_infos[i][j + 2]['money_in'],
style_num)
sheet.write(5 + j + info_count, 7,
all_infos[i][j + 2]['money_out'], style_num)
sheet.write(5 + j + info_count, 8,
all_infos[i][j + 2]['money_now'], style_num)
all_money_in = all_money_in + Decimal(all_infos[i][
j +
2]['money_in'] if all_infos[i][j + 2]['money_in'] != '' else 0)
all_money_out = all_money_out + Decimal(
all_infos[i][j + 2]['money_out']
if all_infos[i][j + 2]['money_out'] != '' else 0)
all_money_now = Decimal(all_infos[i][j + 2]['money_now'] if
all_infos[i][j +
2]['money_now'] != '' else 0)
all_money_ins.append({
'all_money_in': all_money_in,
'all_money_out': all_money_out,
'all_money_now': all_money_now
})
info_count = info_count + len(all_infos[i]) - 2
money_ins = Decimal('0.00')
money_outs = Decimal('0.00')
money_nows = Decimal('0.00')
for k in range(len(all_money_ins)):
money_ins = money_ins + Decimal((all_money_ins[k]['all_money_in']))
money_outs = money_outs + Decimal((all_money_ins[k]['all_money_out']))
money_nows = money_nows + Decimal((all_money_ins[k]['all_money_now']))
sheet.write(5 + info_count, 12 + k * 3,
float(all_money_ins[k]['all_money_in']), style_num)
sheet.write(5 + info_count, 13 + k * 3,
float(all_money_ins[k]['all_money_out']), style_num)
sheet.write(5 + info_count, 14 + k * 3,
float(all_money_ins[k]['all_money_now']), style_num)
sheet.write(5 + info_count, 3, '合计', style_title)
sheet.write(5 + info_count, 6, float(money_ins), style_num)
sheet.write(5 + info_count, 7, float(money_outs), style_num)
sheet.write(5 + info_count, 8, float(money_nows), style_num)
name = '日记账' + time.strftime('%Y%m%d%H%M%S', time.localtime()) + '.xls'
wbk.save(name) #循环输入后保存，此时的文件名对应的文件可以存在，会被覆盖，但是不能是打开状态，会报错
print('数据合并成功！共', file_count, '个文件，成功', success_count, '个，失败', fail_cout,
'个，合并后的文件为：', name)
def write_excel(result):
myxls=xlwt.Workbook()
sheet1=myxls.add_sheet(u'sheet',cell_overwrite_ok=True)
style = xlwt.XFStyle() # 创建一个样式对象，初始化样式
al = xlwt.Alignment()
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
style.alignment = al
font = xlwt.Font() # 为样式创建字体
font.name = '微软雅黑'
font.bold = True # 黑体
style.font = font # 设定样式
# 边框
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style.borders = borders
# 设置每行宽度
for i in range(0,20):
sheet1.col(i).width=220*20
pass
# 设置每行宽度
for i in range(len(items)):
item = items[i]
sheet1.col(i).width=item['width']*20
# 将数据插入第一行
sheet1.write(0,i,item['name'],style)
# 设置每行高度
for x in range(0,5000):
sheet1.row(x).height_mismatch = True
sheet1.row(x).height=20*20
for i in range(len(items)):
item = items[i]
# 将数据插入第一行
sheet1.write(0,i,item['name'],style)
font = xlwt.Font() # 为样式创建字体
font.name = '微软雅黑'
font.bold = False
style.font = font
for i in range(0,len(result)):
item = result[i]
# 设置每行宽度
for m in range(len(items)):
set_item = items[m]
func = set_item.get('func')
if func:
# 带自定义函数的
content = func(item,set_item['key'])
sheet1.write(i+1,m,f'{content}',style)
else:
key = set_item['key']
index = key.find(':')
# 多级key
if index > -1:
keys = key.split(':')
# content最开始等于item
content = item
for j in range(len(keys)):
cur_key = keys[j]
if isinstance(content,list):
for n in range(len(content)):
sheet1.write(i+1,m+n,content[n][cur_key],style)
else:
content = content.get(cur_key)
if content is None:
content = ''
sheet1.write(i+1,m,f'{content}',style)
else:
content = item.get(set_item["key"]) if item.get(set_item["key"]) else ''
sheet1.write(i+1,m,f'{content}',style)
name = datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d-%H%M%S')
myxls.save(f'{os.path.expanduser("~")}/Desktop/{name}.xls')
def print_xls_product_report(self, vals):
stylePC = xlwt.XFStyle()
bold = xlwt.easyxf(
"font: bold on; pattern: pattern solid, fore_colour gray25;")
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
stylePC.alignment = alignment
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
font = xlwt.Font()
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
font.bold = True
font.height = 500
stylePC.font = font
stylePC.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['gray25']
stylePC.pattern = pattern
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Stock Expiry Report')
for j in range(0, 7):
worksheet.col(j).width = 5600
j += 1
worksheet.write_merge(1,
2,
0,
6,
'Product Expiry Report',
style=stylePC)
worksheet.write(4, 0, "Product Expiry In Next", bold)
worksheet.write(4, 1, str(vals.get('num_days')) + ' Days')
worksheet.write(4, 4, "Date", bold)
worksheet.write(4, 5, str(vals.get('today_date')))
i = 6
for key, value in vals.items():
if vals.get('group_by') and key not in [
'group_by', 'num_days', 'today_date'
]:
if vals.get('group_by') == 'location':
worksheet.write(i, 0, "Location", bold)
elif vals.get('group_by') == 'category':
worksheet.write(i, 0, "Category", bold)
worksheet.write(i, 1, key)
i += 2
if value not in [vals.get('num_day'), vals.get('today_date')]:
worksheet.write(i, 0, "Lot/Serial number", bold)
worksheet.write(i, 1, "Product", bold)
if vals.get('group_by') == 'location':
worksheet.write(i, 2, "Category", bold)
elif vals.get('group_by') == 'category':
worksheet.write(i, 2, "Location", bold)
worksheet.write(i, 3, "Internal Ref", bold)
worksheet.write(i, 4, "Expiry Date", bold)
worksheet.write(i, 5, "Remaining Days", bold)
worksheet.write(i, 6, "Available Quantity", bold)
i += 1
for each in value:
count = 0
for key, val in each.items():
worksheet.write(i, count, val)
count += 1
i += 1
i += 1
file_data = BytesIO()
workbook.save(file_data)
report_id = self.env['report.download.wizard'].create({
'data':
base64.encodestring(file_data.getvalue()),
'name':
'Product Expiry Report.xls'
})
return {
'name': 'Download Excel Report',
'view_type': 'form',
'view_mode': 'form',
'res_model': 'report.download.wizard',
'target': 'new',
'res_id': report_id.id,
'type': 'ir.actions.act_window'
}
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 _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) # 一定要记得保存
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
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
tem_excel = xlrd.open_workbook('D:/日统计.xls', formatting_info=True)
tem_sheet = tem_excel.sheet_by_index(0)
new_excel = copy(tem_excel)
new_sheet = new_excel.get_sheet(0)
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = '宋体'
font.bold = True
font.height = 360
style.font = font
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_LEFT
alignment.vert = xlwt.Alignment.VERT_TOP
style.alignment = alignment
style_red = xlwt.XFStyle()
font_red = xlwt.Font()
font_red.name = '宋体'
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 print_inventory_export_report(self):
data = self.read()[0]
start_date = data.get('date_start', False)
end_date = data.get('date_end', False)
if start_date and end_date and end_date < start_date:
raise Warning(_("End date should be greater than start date!"))
# Create Inventory Export report in Excel file.
workbook = xlwt.Workbook(style_compression=2)
worksheet = workbook.add_sheet('Sheet 1')
font = xlwt.Font()
font.bold = True
style = xlwt.easyxf('align: wrap yes')
worksheet.row(0).height = 500
worksheet.row(1).height = 500
for x in range(0, 41):
worksheet.col(x).width = 6000
borders = xlwt.Borders()
borders.top = xlwt.Borders.MEDIUM
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
border_style1 = xlwt.easyxf('font: bold 1')
border_style1.borders = borders
where_start_date = " 1=1 "
if start_date:
where_start_date = " sm.date + interval '7 hour' >= '%s 00:00:00' " % start_date
where_end_date = " 1=1 "
if end_date:
where_end_date = " sm.date + interval '7 hour' <= '%s 23:59:59'" % end_date
query = """
SELECT
sp.jenis_dokumen, sp.no_dokumen AS no_dokumen_pabean, sp.tanggal_dokumen AS tanggal_dokumen_pabean, sp.name AS no_dokumen,
sp.date_done AS tanggal_dokumen, rp.name AS nama_mitra, pp.default_code AS kode_barang, pt.name AS nama_barang, uu.name,
coalesce(sml.qty_done,0) AS qty_done, coalesce(sm.subtotal_price,0) AS nilai_barang, spt.code AS status_type, rc.symbol,
sp.no_aju, spd.name AS no_invoice, spd.date AS tanggal_invoice,
out.jenis_dok_out, out.no_dok_out, out.tgl_dok_out, coalesce(out.qty_out,0) AS qty_out, sm.sequence
FROM stock_move_line sml
LEFT JOIN stock_move sm ON sml.move_id = sm.id
LEFT JOIN stock_picking sp ON sml.picking_id=sp.id
LEFT JOIN res_partner rp ON sp.partner_id=rp.id
LEFT JOIN product_product pp ON pp.id=sml.product_id
LEFT JOIN uom_uom uu ON uu.id=sml.product_uom_id
LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id
LEFT JOIN stock_picking_type spt ON spt.id=sm.picking_type_id
LEFT JOIN res_currency rc ON rc.id = sp.currency_id
LEFT JOIN (
SELECT sml.lot_id, MIN(sp.jenis_dokumen) AS jenis_dok_out, MIN(sp.no_dokumen) AS no_dok_out,
MIN(sp.tanggal_dokumen) AS tgl_dok_out, SUM(qty_done) AS qty_out
FROM stock_move_line sml
LEFT JOIN stock_move sm ON sml.move_id = sm.id
LEFT JOIN stock_picking sp ON sml.picking_id = sp.id
WHERE sm.state = 'done'
AND (sm.location_id = '21' AND sm.location_dest_id != '21')
AND """ + where_start_date + """
AND """ + where_end_date + """
GROUP BY sml.lot_id
) out ON out.lot_id = sml.lot_id
LEFT JOIN (
SELECT picking_id, name, date
FROM stock_picking_document
WHERE doc_type = 'invoice'
) spd ON spd.picking_id = sp.id
WHERE sm.state = 'done'
AND (sm.location_id != '21' AND sm.location_dest_id = '21')
AND """ + where_start_date + """
AND """ + where_end_date + """
ORDER BY sp.tanggal_dokumen ASC, sp.no_dokumen ASC
"""
self._cr.execute(query)
vals = self._cr.fetchall()
company = self.env.user.company_id.name
start_date_format = start_date.strftime('%d/%m/%Y')
end_date_format = end_date.strftime('%d/%m/%Y')
worksheet.write_merge(2, 2, 0, 4, "" + str(company).upper(
), xls_format.font_style(position='left', bold=1, fontos='black', font_height=300))
worksheet.write_merge(3, 3, 0, 4, "LAPORAN POSISI BARANG PER DOKUMEN (Belawan)", xls_format.font_style(position='left', bold=1, fontos='black', font_height=300))
worksheet.write_merge(5, 5, 0, 1, "PERIODE : " + str(start_date_format) + " S.D " + str(
end_date_format), xls_format.font_style(position='left', bold=1, fontos='black', font_height=200))
row = 7
worksheet.write_merge(7, 8, 0, 0, "NO", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write_merge(7, 7, 1, 9, "DOKUMEN PEMASUKAN", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write_merge(7, 7, 10, 15, "DOKUMEN PENGELUARAN", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write_merge(7, 7, 16, 17, "SALDO BARANG", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 1, "Jenis", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 2, "Nomor", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 3, "Tanggal", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 4, "No. Invoice", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 5, "Tgl. Invoice", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 6, "Seri", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 7, "Kode Barang", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 8, "Nama Barang", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 9, "Satuan", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 10, "Jumlah", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 11, "Jenis", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 12, "Nomor", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 13, "Tanggal", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 14, "Satuan", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 15, "Jumlah", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 16, "Jumlah", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
worksheet.write(8, 17, "Satuan", xls_format.font_style(
position='center', bold=1, border=1, fontos='black', font_height=200, color='grey'))
row += 2
no = 1
for val in vals:
tgl_dok_out = ''
if (val[18]):
tgl_dok_out = str(val[18].strftime('%d/%m/%Y'))
jenis_dokumen = val[0]
nomor_pabean = val[1]
tanggal_pabean = val[2]
nomor_penerimaan_barang = val[3]
tanggal_penerimaan_barang = val[4]
pemasok_pengirim = val[5]
kode_barang = val[6]
nama_barang = val[7]
satuan = val[8]
jumlah = val[9]
nilai_barang = val[10]
currency = val[12]
no_aju = val[13]
no_invoice = val[14]
tgl_invoice = ''
if val[15]:
tgl_invoice = str(val[15].strftime('%d/%m/%Y'))
jenis_dok_out = val[16]
no_dok_out = val[17]
tgl_dok_out = tgl_dok_out
qty_out = val[19]
qty_saldo = val[9] - val[19]
seri = val[20]
worksheet.write(row, 0, no, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 1, jenis_dokumen, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 2, nomor_pabean, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 3, str(tanggal_pabean.strftime('%d/%m/%Y')), xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 4, no_invoice, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 5, tgl_invoice, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 6, seri, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 7, kode_barang, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 8, nama_barang, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 9, satuan, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 10, jumlah, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 11, jenis_dok_out, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 12, no_dok_out, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 13, tgl_dok_out, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 14, satuan, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 15, qty_out, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 16, qty_saldo, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
worksheet.write(row, 17, satuan, xls_format.font_style(
position='center', border=1, fontos='black', font_height=200, color='false'))
row += 1
no += 1
fp = BytesIO()
workbook.save(fp)
res = base64.encodestring(fp.getvalue())
res_id = self.env['posisi.barang.per.dokumen.belawan.export.summary'].create(
{'name': 'Laporan Posisi Barang PerDok (Belawan).xls', 'file': res})
return {
'type': 'ir.actions.act_url',
'url': '/web/binary/download_document?model=posisi.barang.per.dokumen.belawan.export.summary&field=file&id=%s&filename=Laporan Posisi Barang PerDok (Belawan).xls' % (res_id.id),
'target': 'new',
}
import xlwt import StringIO from datetime import date font_title = xlwt.Font() font_title.name = 'Times New Roman' font_title.bold = True font_title.height = 19 * 0x14 font_title.underline = xlwt.Font.UNDERLINE_DOUBLE font1 = xlwt.Font() font1.name = 'Verdana' 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()
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 make_to_excel(object_list, fields=None):
'''
object_list queryset.
fields is a list.eg: fields=['id', 'created', 'creator']
'''
if not object_list:
return
''' xlwt设置表格的一些样式 '''
body_style = xlwt.XFStyle()
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
font = xlwt.Font()
font.bold = True
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 22
title_style = xlwt.XFStyle()
title_style.borders = borders
title_style.font = font
title_style.pattern = pattern
body_style = xlwt.XFStyle()
body_style.borders = borders
''' 开始制作Excel表格 '''
verbose_name = object_list.model._meta.verbose_name
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('{0}列表'.format(verbose_name))
model = object_list.model
fields = fields_for_model(model, fields=fields)
# 上面 `fields` 获取某个 model fields 列表.
field_names = []
field_verboses = []
for attname, field in fields.items():
if attname not in ['password']:
field_names.append(attname)
field_verboses.append(label_for_field(attname, model))
for col in range(len(field_verboses)):
ws.write(0, col, force_text(field_verboses[col]), title_style)
row = 1
for obj in object_list:
for index, field_name in enumerate(field_names):
field = model._meta.get_field(field_name)
value = field.value_from_object(obj)
cell_value = display_for_field(value,
field,
html=False,
only_date=False)
ws.write(row, index, cell_value, body_style)
row += 1
output = BytesIO()
wb.save(output)
output.seek(0)
time = formats.localize(
timezone.template_localtime(timezone.datetime.now()))
filename = urlquote('{}{}'.format(verbose_name,
slugify(time, allow_unicode=True)))
# 上面 `filename` 解决导出中文文件名出错的问题
response = HttpResponse(output)
# response = StreamingHttpResponse(output)
# Stream在这里其实是不起作用了,可以直接HttpResponse
response['charset'] = 'utf-8'
response['content_type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment; filename="{}.xls"'.format(
filename)
return response
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,
}
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
print("查卡日期：", DayToday)
print('\n')
DayCheck = str(datetime.datetime.now() -
datetime.timedelta(days=1)).split(" ")[0]
# 定义保存Excel的位置
workbook = xlwt.Workbook() #定义workbook
sheet = workbook.add_sheet(DayToday) #添加sheet
# 设置居中
al = xlwt.Alignment()
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
# 设置边框
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
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
# 设置单元格背景颜色
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 47
# 创建样式
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
if 'Current messages:' in line: # 日志条目
log11 = line[-5:].rstrip()
print(log11)
if 'Routes' in line:
routingtable11 = line[-5:].rstrip() # 路由条目
print(routingtable11)
i += 1
workbook = xlwt.Workbook() # 创建表格
style = XFStyle() # 初始化样式，此样式包含了单元格背景颜色和单元格边框两个属性。
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = Style.colour_map['blue'] # 设置单元格背景色为蓝色
style.pattern = pattern
borders = xlwt.Borders() # 设置表格的边框，1是默认实线黑色。
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
style.borders = borders
style1 = XFStyle() # 只有边框
borders = xlwt.Borders()
borders.left = 1
#borders.left = xlwt.Borders.THIN
borders.right = 1
borders.top = 1
borders.bottom = 1
style1.borders = borders
def get_result(self):
cr, uid, context = self.env.args
if context is None:
context = {}
context = dict(context)
data = self.read()[0]
start_date = data.get('date_start', False)
end_date = data.get('date_end', False)
if start_date and end_date and end_date < start_date:
raise Warning(_("End date should be greater than start date!"))
res_user = self.env["res.users"].browse(uid)
export = data.get('export_report', False)
# Create Inventory Export report in Excel file.
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
font = xlwt.Font()
font.bold = True
header = xlwt.easyxf('font: bold 1, height 280')
# start_date = datetime.strptime(str(context.get("date_from")), DEFAULT_SERVER_DATE_FORMAT)
# start_date_formate = start_date.strftime('%d/%m/%Y')
# end_date = datetime.strptime(str(context.get("date_to")), DEFAULT_SERVER_DATE_FORMAT)
# end_date_formate = end_date.strftime('%d/%m/%Y')
# start_date_to_end_date = tools.ustr(start_date_formate) + ' To ' + tools.ustr(end_date_formate)
style = xlwt.easyxf('align: wrap yes')
worksheet.row(0).height = 500
worksheet.row(1).height = 500
for x in range(0, 41):
worksheet.col(x).width = 6000
borders = xlwt.Borders()
borders.top = xlwt.Borders.MEDIUM
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
border_style1 = xlwt.easyxf('font: bold 1')
border_style1.borders = borders
style = xlwt.easyxf('align: wrap yes', style)
ids_location = []
ids_categ = []
ids_product = []
where_end_date_awal = " sm.date is null "
where_start_date = " 1=1 "
if start_date:
where_start_date = " sm.date + interval '7 hour' >= '%s 00:00:00' " % start_date
where_end_date_awal = " sm.date + interval '7 hour' < '%s 00:00:00' " % start_date
where_end_date = " 1=1 "
if end_date:
where_end_date = " sm.date + interval '7 hour' <= '%s 23:59:59'" % end_date
where_location = " 1=1 "
if ids_location:
where_location = """(sm.location_id in %s
or sm.location_dest_id in %s)""" % (str(
tuple(ids_location)).replace(
',)', ')'), str(tuple(ids_location)).replace(',)', ')'))
where_categ = " 1=1 "
if ids_categ:
where_categ = "pt.categ_id in %s" % str(tuple(ids_categ)).replace(
',)', ')')
where_product = " 1=1 "
if ids_product:
where_product = "pp.id in %s" % str(tuple(ids_product)).replace(
',)', ')')
if export == "BC 2.5":
where_export = "BC 2.5"
elif export == "BC 2.6.1":
where_export = "BC 2.6.1"
elif export == "BC 2.7":
where_export = "BC 2.7"
elif export == "BC 3.0":
where_export = "BC 3.0"
elif export == "BC 3.3":
where_export = "BC 3.3"
elif export == "BC 4.1":
where_export = "BC 4.1"
query = """
SELECT
sp.jenis_dokumen, sp.no_dokumen AS no_dokumen_pabean, sp.tanggal_dokumen AS tanggal_dokumen_pabean, sp.name AS no_dokumen,
sp.date_done AS tanggal_dokumen, rp.name AS nama_mitra, pp.default_code AS kode_barang, pt.name AS nama_barang, uu.name,
sm.product_uom_qty, coalesce(sm.subtotal_price,0) AS nilai_barang, spt.code AS status_type, rc.symbol
FROM stock_move sm
INNER JOIN stock_picking sp ON sm.picking_id=sp.id
LEFT JOIN res_partner rp ON sp.partner_id=rp.id
LEFT JOIN product_product pp ON pp.id=sm.product_id
LEFT JOIN uom_uom uu ON uu.id=sm.product_uom
LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id
LEFT JOIN stock_picking_type spt ON spt.id=sm.picking_type_id
LEFT JOIN sale_order_line sol ON sol.id=sm.sale_line_id
LEFT JOIN res_currency rc ON rc.id = sp.currency_id
WHERE sm.state = 'done'
AND ((sm.location_id = '12' AND sm.location_dest_id != '12')
OR (sm.location_id = '26' AND sm.location_dest_id = '24'))
AND """ + where_start_date + """
AND """ + where_end_date + """
AND sp.jenis_dokumen = '""" + where_export + """'
ORDER BY sp.tanggal_dokumen ASC, sp.no_dokumen ASC
"""
list_data = []
company = self.env.user.company_id.name
start_date_format = start_date.strftime('%d/%m/%Y')
end_date_format = end_date.strftime('%d/%m/%Y')
self._cr.execute(query)
vals = self._cr.fetchall()
no = 1
for val in vals:
list_data.append({
'jenis_dokumen': val[0],
'nomor_pabean': val[1],
'tanggal_pabean': val[2],
'nomor_penerimaan_barang': val[3],
'tanggal_penerimaan_barang': val[4],
'pemasok_pengirim': val[5],
'kode_barang': val[6],
'nama_barang': val[7],
'satuan': val[8],
'jumlah': val[9],
'nilai_barang': val[10],
'currency': val[12]
})
no += 1
hasil = list_data
return hasil
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 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 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 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 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 process_block_as_xls(self, tempfile, block_text, user, **params):
try:
# get a connector to deal with column types
self.prepare_for_query(block_text, user, **params)
connector = dwwiki.connectors.get_connector(self.db)
# make an array for columns if we have totals
# at first it is filled with None's
need_totals = False
if len(self.totals_array) > 0:
self.totals_values = [None] * len(self.totals_array)
need_totals = True
# fetch at least one row to check for datatypes
row = self.cur.fetchone()
# workbook
wb = xlwt.Workbook()
ws = wb.add_sheet('test_sheet')
# init basic styles
header_font = xlwt.Font()
header_font.name = 'Arial'
# You set the font's height in "twips", which are 1/20 of a point
header_font.height = 10*20
header_font.bold = True
# borders
header_borders = xlwt.Borders()
header_borders.left = xlwt.Borders.THIN
header_borders.right = xlwt.Borders.THIN
header_borders.top = xlwt.Borders.THIN
header_borders.bottom = xlwt.Borders.THIN
# font and borders
header_style = xlwt.XFStyle()
header_style.font = header_font
header_style.borders = header_borders
# bg colour
header_pattern = xlwt.Pattern()
header_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# silver
header_pattern.pattern_fore_colour = 0x16
header_style.pattern = header_pattern
# body style
body_font = xlwt.Font()
body_font.name = header_font.name
# You set the font's height in "twips", which are 1/20 of a point
body_font.height = header_font.height
body_borders = header_borders
body_pattern = xlwt.Pattern()
body_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# white
body_pattern.pattern_fore_colour = 0x09
body_style = xlwt.XFStyle()
body_style.font = body_font
body_style.borders = body_borders
body_style.pattern = body_pattern
# footer style
footer_font = xlwt.Font()
footer_font.name = header_font.name
# You set the font's height in "twips", which are 1/20 of a point
footer_font.height = header_font.height
footer_font.bold = True
# borders
footer_borders = header_borders
#bg colour
footer_pattern = header_pattern
# now make up columns
columns = self.cur.description
# list of excel formats for all columns
col_formats = list()
header_list = list()
col_num = 0
for col in columns:
s = col[0]
if type(col[0]) is str:
s = col[0].decode('utf-8')
elif type(col[0]) is unicode:
s = col[0]
else:
print "type:" + str(type(col[0]))
header_list.append(s)
col_data_type = connector.get_col_type(col)
if col_data_type == 'date':
fmt_str = 'dd.mm.yyyy'
elif col_data_type == 'Decimal':
scale = connector.get_scale(col)
if scale > 0:
fmt_str = '#,##0.' + '0'*scale
else:
fmt_str = '#,##0'
else:
fmt_str = 'General'
col_formats.append(fmt_str)
# row col
# header
ws.write(0, col_num, s, header_style)
col_num += 1
#tempfile.write((sep.join(header_list) + nl).encode(enc))
#print sep.join(header_list) + "\n"
row_num = 1 # headers are done TODO what if no header?
num_rows = 0
while row is not None:
col_num = 0
for val in row:
if type(val) is str:
val = val.decode('utf-8')
#elif type(col[0]) is unicode:
#s = col[0]
# formats
body_style.num_format_str = col_formats[col_num]
ws.write(row_num, col_num, val, body_style)
col_num += 1
# totals
self.update_totals(row)
row = self.cur.fetchone()
row_num += 1
num_rows += 1
# deal with totals
number_of_totals = len(self.totals_array)
if need_totals and (num_rows > 0):
for col_num in range(len(columns)):
cell_style = xlwt.XFStyle()
cell_style.font = footer_font
cell_style.borders = footer_borders
cell_style.pattern = footer_pattern
if number_of_totals > col_num:
# don't lower it. yet.
total_heading = self.totals_array[col_num].strip()
lower_total_heading = total_heading.lower()
# check no heading
heading_rows = 1
if self.header == self.ATTR_HEADER_VAL_NO:
heading_rows = 0
start_cell_address = self.make_cell_address(heading_rows, col_num)
end_cell_address = self.make_cell_address(heading_rows+num_rows-1, col_num)
formula_range = "%s:%s" % (start_cell_address, end_cell_address)
if lower_total_heading == 'sum':
formula = xlwt.Formula("SUM(%s)" % formula_range)
cell_style.num_format_str = col_formats[col_num]
ws.write(row_num, col_num, formula, cell_style)
elif lower_total_heading == 'avg':
cell_style.num_format_str = col_formats[col_num]
formula = xlwt.Formula("AVERAGE(%s)" % formula_range)
ws.write(row_num, col_num, formula, cell_style)
elif lower_total_heading == 'count':
cell_style.num_format_str = 'General'
formula = xlwt.Formula("ROWS(%s)-COUNTBLANK(%s)" % (formula_range, formula_range))
ws.write(row_num, col_num, formula, cell_style)
elif lower_total_heading == 'min':
cell_style.num_format_str = col_formats[col_num]
formula = xlwt.Formula("MIN(%s)" % formula_range)
ws.write(row_num, col_num, formula, cell_style)
elif lower_total_heading == 'max':
cell_style.num_format_str = col_formats[col_num]
formula = xlwt.Formula("MAX(%s)" % formula_range)
ws.write(row_num, col_num, formula, cell_style)
else:
# it's just a string. Maybe empty
cell_style.num_format_str = 'General'
ws.write(row_num, col_num, total_heading, cell_style)
else:
# we've run out of totals. just make cells empty
total_heading = ''
cell_style.num_format_str = 'General'
ws.write(row_num, col_num, total_heading, cell_style)
wb.save(tempfile)
self.con.close()
self.con = None
except DwException, e:
print e
if self.con is not None:
self.con.close()
raise e
def print_xls(self,data):
styleP = xlwt.XFStyle()
stylePC = xlwt.XFStyle()
styleBorder = xlwt.XFStyle()
fontbold = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment_right = xlwt.Alignment()
alignment_right.horz = xlwt.Alignment.HORZ_LEFT
alignment_lft = xlwt.Alignment()
alignment_lft.horz = xlwt.Alignment.HORZ_RIGHT
# alignment_right.horz = xlwt.Alignment.HORZ_LEFT
font = xlwt.Font()
fontP = xlwt.Font()
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
font.bold = False
fontP.bold = True
styleP.font = font
# stylePC.font = fontP
stylePC.alignment = alignment_lft
styleBorder.font = fontP
fontbold.font = fontP
styleBorder.alignment = alignment
fontbold.alignment = alignment_lft
styleBorder.borders = borders
workbook = xlwt.Workbook(encoding="utf-8")
worksheet = workbook.add_sheet("General ledger")
worksheet.col(0).width = 6000
worksheet.col(3).width = 5000
worksheet.col(2).width = 5000
worksheet.col(4).width = 6000
worksheet.col(5).width = 6000
worksheet.col(4).width = 6000
display_acccount = False
if self.display_account == 'all':
display_acccount = 'All'
elif self.display_account == 'movement':
display_acccount = 'With Movements'
else:
display_acccount = 'With balance is not equal to 0'
worksheet.write_merge(0, 2, 0, 8,
self.company_id.name + '\n' + self.company_id.email + '\n' + self.company_id.phone,
style=styleBorder)
worksheet.write_merge(4, 4, 0, 3, 'Journals:', style=fontbold)
worksheet.write_merge(4, 4, 4, 5, 'Display Account', style=fontbold)
worksheet.write_merge(4, 4, 6, 8, 'Target Moves:', style=fontbold)
worksheet.write_merge(5, 5, 0, 3, ','.join(map(str, [each.code for each in self.journal_ids])) , style=fontbold)
worksheet.write_merge(5, 5, 4, 5, display_acccount, style=fontbold)
worksheet.write_merge(5, 5, 6, 8, 'All Posted Entries' if self.target_move == 'posted' else 'All Entries', style=fontbold)
worksheet.write_merge(6, 6, 0, 2, 'Sort by : ' + 'Date' if self.sortby == 'sort_date' else 'Sort by : ' + 'Journal & Partner')
worksheet.write_merge(6, 6, 3, 5, 'Date From : ' + str(self.date_from) if self.date_from else '')
worksheet.write_merge(6, 6, 6, 8, 'Date To : ' + str(self.date_to) if self.date_to else '')
worksheet.write_merge(7, 7, 0, 8)
worksheet.write_merge(8, 8, 0, 0, 'Date', fontbold)
worksheet.write_merge(8, 8, 1, 1, 'JRNL', fontbold)
worksheet.write_merge(8, 8, 2, 2, 'Partner', fontbold)
worksheet.write_merge(8, 8, 3, 3, 'Ref', fontbold)
worksheet.write_merge(8, 8, 4, 4, 'Move', fontbold)
worksheet.write_merge(8, 8, 5, 5, 'Entry Label', fontbold)
worksheet.write_merge(8, 8, 6, 6, 'Debit', fontbold)
worksheet.write_merge(8, 8, 7, 7, 'Credit', fontbold)
worksheet.write_merge(8, 8, 8, 8, 'Balance', fontbold)
report_obj = self.env['report.flexipharmacy.general_ledger_template']
data = {}
data['ids'] = self.env.context.get('active_ids', [])
data['model'] = self.env.context.get('active_model', 'ir.ui.menu')
data['form'] = self.read(
['date_from', 'date_to', 'journal_ids', 'display_account', 'target_move', 'include_init_balance',
'sortby'])[0]
data['form']['date_from'] = str(self.date_from) if self.date_from else ''
data['form']['date_to'] = str(self.date_to) if self.date_to else ''
datas = {
'ids': self._ids,
'docs': self._ids,
'model': 'account.general.ledger.wiz',
'form': data['form']
}
report_data = report_obj.with_context(active_model='account.general.ledger.wiz')._get_report_values(self, data=datas)
i = 9
for each in report_data['Accounts']:
worksheet.write_merge(i, i, 0, 5,each['code'] + ' ' + each['name'], fontbold)
worksheet.write_merge(i, i, 6, 6, formatLang(self.env,float(each['debit']),currency_obj=self.env.user.company_id.currency_id), fontbold)
worksheet.write_merge(i, i, 7, 7, formatLang(self.env,float(each['credit']),currency_obj=self.env.user.company_id.currency_id), fontbold)
worksheet.write_merge(i, i, 8, 8, formatLang(self.env,float(each['balance']),currency_obj=self.env.user.company_id.currency_id), fontbold)
if self.include_init_balance:
i = i + 1
worksheet.write_merge(i, i, 4,5 , 'Initial Balance')
worksheet.write_merge(i, i, 6, 6, formatLang(self.env,float(each['init_bal'][0]),currency_obj=self.env.user.company_id.currency_id))
worksheet.write_merge(i, i, 7, 7, formatLang(self.env,float(each['init_bal'][1]),currency_obj=self.env.user.company_id.currency_id))
worksheet.write_merge(i, i, 8, 8, formatLang(self.env,float(each['init_bal'][2]),currency_obj=self.env.user.company_id.currency_id))
i = i + 1
for each_line in each['move_lines']:
worksheet.write_merge(i, i, 0, 0, str(each_line['ldate']))
worksheet.write_merge(i, i, 1, 1, each_line['lcode'])
worksheet.write_merge(i, i, 2, 2, each_line['partner_name'])
worksheet.write_merge(i, i, 3, 3, each_line['lref'])
worksheet.write_merge(i, i, 4, 4, each_line['move_name'])
worksheet.write_merge(i, i, 5, 5, each_line['lname'])
worksheet.write_merge(i, i, 6, 6, formatLang(self.env,float(each_line['debit']),currency_obj=self.env.user.company_id.currency_id), stylePC)
worksheet.write_merge(i, i, 7, 7, formatLang(self.env,float(each_line['credit']),currency_obj=self.env.user.company_id.currency_id), stylePC)
worksheet.write_merge(i, i, 8, 8, formatLang(self.env,float(each_line['balance']),currency_obj=self.env.user.company_id.currency_id), stylePC)
i = i + 1
file_data = io.BytesIO()
workbook.save(file_data)
self.write({
'state': 'get',
'data': base64.encodestring(file_data.getvalue()),
'name': 'general_ledger.xls'
})
return {
'name': 'General ledger',
'type': 'ir.actions.act_window',
'res_model': 'account.general.ledger.wiz',
'view_mode': 'form',
'view_type': 'form',
'res_id': self.id,
'target': 'new'
}
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")
#
# # ############################Insert into Excel!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!######################################
# rowNumber以及新建excel放到第一重循环内
cdn_list = [
'CDN_A', 'CDN_B', 'CDN_C', 'CDN_D', 'CDN_E', 'CDN_F', 'CDN_G', 'CDN_H'
]
# cdn_list=['CDN_H']
# mu=0
for one_cdn_list in cdn_list:
rowNumber = 2
# mu+=1
w = xlwt.Workbook(encoding='utf-8', style_compression=2)
ws = w.add_sheet('date', cell_overwrite_ok=True)
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.col(0).width = 3000
ws.col(1).width = 3000
def query_to_workbook_title(ws, sql_cursor, row_start):
data_length = 0
global title_start
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern_fore_colour = 1
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
for i in xrange(0, 100):
for j in xrange(0, 20):
ws.write(i, j, '', style)
alignment4 = xlwt.Alignment()
alignment4.horz = xlwt.Alignment.HORZ_CENTER
font4 = xlwt.Font()
font4.name = 'Microsoft YaHei'
font4.bold = 'true'
font4.colour_index = 0x01 #1=White
font4.height = 280 #font=14
pattern4 = xlwt.Pattern() # Create the Pattern
pattern4.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern4.pattern_fore_colour = 158
style4 = xlwt.XFStyle() # Create the Pattern
style4.font = font4
style4.pattern = pattern4 # Add Pattern to Style
style4.alignment = alignment4
for colx, heading in enumerate(tuple[0] for tuple in sql_cursor[2]):
ws.write(row_start - 1, colx + 2, heading.decode('utf-8'), style4)
ws.write(row_start - 1, 2, u'指标', style4)
#ws.col(colx+2).width = 6000 # 3333 = 1" (one inch).
data_length = colx
borders1 = xlwt.Borders()
borders1.bottom = xlwt.Borders.MEDIUM
style.borders = borders1
for i in xrange(1, data_length + 4):
ws.write(title_start, i, '', style)
borders2 = xlwt.Borders()
borders2.left = xlwt.Borders.MEDIUM
style.borders = borders2
for i in xrange(1, 7):
ws.write(title_start + i, 1, '', style)
borders3 = xlwt.Borders()
borders3.right = xlwt.Borders.MEDIUM
style.borders = borders3
for i in xrange(1, 7):
ws.write(title_start + i, data_length + 3, '', style)
font1 = xlwt.Font()
font1.name = 'Microsoft YaHei'
font1.bold = 'true'
font1.colour_index = 0x9e #158
font1.height = 360 #font=18
style1 = xlwt.XFStyle() # Create the Pattern
style1.font = font1
style1.alignment = alignment4
#style1.borders = borders
style1.pattern = pattern
ws.write_merge(title_start + 2, title_start + 2, 2, data_length + 2,
u'东亚携程联名信用卡业务数据日报', style1)
font2 = xlwt.Font()
font2.name = 'Microsoft YaHei'
font2.colour_index = 0x9f #158
font2.height = 320 #font=16
style2 = xlwt.XFStyle() # Create the Pattern
style2 = xlwt.XFStyle() # Create the Pattern
style2.font = font2
style2.alignment = alignment4
#style2.borders = borders
style2.pattern = pattern
ws.write_merge(title_start + 3, title_start + 3, 2, data_length + 2,
u'信用卡事业部', style2)
font3 = xlwt.Font()
font3.name = 'Microsoft YaHei'
font3.bold = 'true'
font3.colour_index = 0x17 #23
font3.height = 240 #font=12
alignment3 = xlwt.Alignment()
alignment3.horz = xlwt.Alignment.HORZ_RIGHT
style3 = xlwt.XFStyle() # Create the Pattern
style3.font = font3
style3.alignment = alignment3
#style3.borders = borders
style3.pattern = pattern
time_string = datetime.datetime.now().strftime('%Y年%m月%d日').decode('utf-8')
ws.write_merge(title_start + 4, title_start + 4, 2, data_length + 2,
time_string, style3)
#ws.insert_bitmap('./bea.bmp', 1, 1,0,0,scale_x=1, scale_y=1)
return data_length
