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