def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment #style.borders = borders #~ style.pattern = pattern return style
def __init__(self, ws, headers, header_line_no=0, use_borders=False, styleHeaders=None, styleDict={}, widths={}): self.headers = headers self.header_line_no = header_line_no self._currentRowIndex = self.header_line_no + 1 self.ws = ws self.rows = [] # columnn widths if isinstance(widths, dict): self._explicit_widths = True self.__colwidths = widths else: self._explicit_widths = False self.__colwidths = {i:len(h) for i,h in enumerate(self.headers)} #*************************************************************************** borders = Borders() if use_borders is True: borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN self.normStyle = XFStyle() self.normStyle.font = normFont if use_borders: self.normStyle.borders = borders # headers if not isinstance(styleHeaders, XFStyle): self.styleHeaders = XFStyle() self.styleHeaders.font = fntHeaders self.styleHeaders.borders = borders else: self.styleHeaders = styleHeaders # for date fields if no styleDict self.styleDate = XFStyle() self.styleDate.borders = borders self.styleDate.num_format_str = 'MM/DD/YYYY' self.styleDate.font = normFont #*************************************************************************** # # instance styles defined by user or defaults if all(map(lambda s: isinstance(s, XFStyle), styleDict.values())): self.styleDict = styleDict if DEFAULT_STYLE in styleDict: self._defaultStyle = self.styleDict[DEFAULT_STYLE] else: self.styleDict = {} self._defaultStyle = self.normStyle self.writeHeaders()
def get_style( font_specs=dict(), border_specs=dict(), pattern=None, num_format_str=None, ): style_key = (freeze(font_specs), freeze(border_specs), pattern, num_format_str) try: return styles[style_key] except KeyError: style = XFStyle() style.font = Font() for key, value in font_specs.items(): setattr(style.font, key, value) style.borders = Borders() for key, value in border_specs.items(): setattr(style.borders, key, value) if pattern: style.pattern = pattern if num_format_str: style.num_format_str = num_format_str styles[style_key] = style return style
class ReportStyles(): alignment = Alignment() font = Font() borders = Borders() # col = Column() def borders_light(self): self.borders.left = Borders.THIN self.borders.right = Borders.THIN self.borders.top = Borders.THIN self.borders.bottom = Borders.THIN return self.borders def align_hor_right(self): self.alignment.horz = Alignment.HORZ_RIGHT return self.alignment def align_hor_left(self): self.alignment.horz = Alignment.HORZ_LEFT return self.alignment def align_hor_center(self): self.alignment.horz = Alignment.HORZ_CENTER return self.alignment def text_bold(self): self.font.bold = True return self.font
def _getStyle(self, org_style, org_font): from xlwt import XFStyle, Font, Borders, Pattern # font font = Font() font.name = org_font.name font.height = org_font.height font.italic = org_font.italic font.struck_out = org_font.struck_out font.outline = org_font.outline font.shadow = org_font.shadow font.colour_index = org_font.colour_index font.bold = org_font.bold font._weight = org_font.weight font.escapement = org_font.escapement_type font.underline = org_font.underline_type font.family = org_font.family font.charset = org_font.character_set # border borders = Borders() borders.left = Borders.THIN #org_style.xf.border.left_line_style borders.right = Borders.THIN #org_style.xf.border.right_line_style borders.top = Borders.THIN #org_style.xf.border.top_line_style borders.bottom = Borders.THIN #org_style.xf.border.bottom_line_style #borders.diag = self.NO_LINE borders.left_colour = org_style.xf.border.left_colour_index borders.right_colour = org_style.xf.border.right_colour_index borders.top_colour = org_style.xf.border.top_colour_index borders.bottom_colour = org_style.xf.border.bottom_colour_index #borders.diag_colour = org_style.xf.border.left_colour_index #borders.need_diag1 = self.NO_NEED_DIAG1 #borders.need_diag2 = self.NO_NEED_DIAG2 # Pattern pattern = Pattern() pattern.pattern = org_style.xf.background.fill_pattern pattern.pattern_fore_colour = org_style.xf.background.pattern_colour_index pattern.pattern_back_colour = org_style.xf.background.background_colour_index # style = XFStyle() style.borders = borders style.font = font style.pattern = pattern return style
def setupFormat(self): headFont = Font() headFont.bold = True alignmentSetup = Alignment() alignmentSetup.wrap = True borders = Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 self.header_style = XFStyle() self.header_style.font = headFont self.table_style = XFStyle() self.table_style.borders = borders self.table_style.alignment = alignmentSetup
def estiloCeldasSeparadas(): fnt = Font() fnt.bold=True #fnt.colour_index=2 borders = Borders() borders.left = Borders.MEDIUM borders.right = Borders.MEDIUM borders.top = Borders.MEDIUM borders.bottom = Borders.MEDIUM pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x18#0x99 style = XFStyle() style.num_format_str='YYYY-MM-DD' style.font = fnt style.borders = borders style.pattern = pattern return style
def build_certificate_excel(queryset): workbook = xlwt.Workbook() sheet = workbook.add_sheet("Сертификаты") title_style = XFStyle() borders = Borders() borders.bottom = Borders.MEDIUM borders.right = Borders.MEDIUM borders.left = Borders.MEDIUM title_style.borders = borders title_style.font = easyfont(f"bold on, height {12*20};") font_style = XFStyle() font_style.font = easyfont(f"height {12*20};") sheet.write(0, 1, "ФИО", title_style) sheet.write(0, 2, "№ Договора", title_style) sheet.write(0, 3, "ИНН", title_style) sheet.write(0, 4, "День", title_style) sheet.write(0, 5, "Месяц", title_style) sheet.write(0, 6, "Год", title_style) sheet.write(0, 7, "Сертификат №", title_style) for r, obj in enumerate(queryset): n = r + 1 sheet.write(n, 0, str(n), font_style) sheet.write(n, 1, str(obj.full_name), font_style) sheet.write(n, 2, str(obj.contract_n), font_style) sheet.write(n, 3, str(obj.inn), font_style) sheet.write(n, 4, str(obj.date_received.year), font_style) sheet.write(n, 5, str(obj.date_received.month), font_style) sheet.write(n, 6, str(obj.date_received.day), font_style) sheet.write(n, 7, str(obj.certificate_n), font_style) sheet.col(0).width = 1400 sheet.col(1).width = 14000 sheet.col(2).width = 4500 sheet.col(3).width = 4000 sheet.col(7).width = 5500 file = BytesIO() workbook.save(file) return file
def __init__(self): self.style = XFStyle() base_font = Font() # 设置基本字体 base_font.height = FONT_SIZE_UNIT * 12 self.style.font = base_font alignment = Alignment() # 设置对齐 alignment.horz = Alignment.HORZ_CENTER alignment.vert = Alignment.VERT_CENTER self.style.alignment = alignment borders = Borders() # 设置边框 borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN borders.left_colour = COLOUR_BLACK borders.right_colour = COLOUR_BLACK borders.top_colour = COLOUR_BLACK borders.bottom_colour = COLOUR_BLACK self.style.borders = borders self.style.num_format_str = '#,##0' # 设置数字格式
def transCellData_summary(pos,val): tmp=[] tmp.append(transpos(pos)[0]) tmp.append(transpos(pos)[1]) style = getDefualtStyle() style.font.colour_index =0x17 style.alignment.horz = Alignment.HORZ_CENTER #style.borders.right = Borders.NO_LINE style.font.bold =True borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style.borders = borders try: tmp.append(val) except: tmp.append(0) return tmp,style
def create_sheet(self): """ this is general method for create work sheet and also create some of style which is used for give different effects on sheets. """ workbook = xlwt.Workbook() borders = Borders() header_border = Borders() header_border.left,header_border.right,header_border.top,header_border.bottom = Borders.THIN,Borders.THIN,Borders.THIN,Borders.THICK borders.left,borders.right,borders.top,borders.bottom = Borders.THIN,Borders.THIN,Borders.THIN,Borders.THIN header_bold = xlwt.easyxf("font: bold on, height 200; pattern: pattern solid, fore_colour gray25;alignment: horizontal center ,vertical center") header_bold.borders=header_border body_style = xlwt.easyxf("font: height 200; alignment: horizontal left") body_style.borders=borders ## style for different colors in columns xlwt.add_palette_colour("light_blue_21", 0x21) workbook.set_colour_RGB(0x21, 153, 255, 255) qty_cell_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour light_blue_21; borders: top thin,right thin,bottom thin,left thin") xlwt.add_palette_colour("custom_orange", 0x22) workbook.set_colour_RGB(0x22, 255, 204, 153) value_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_orange; borders: top thin,right thin,bottom thin,left thin") xlwt.add_palette_colour("custom_mandys_pink", 0x20) workbook.set_colour_RGB(0x20, 246, 228, 204) value_style2 = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_mandys_pink; borders: top thin,right thin,bottom thin,left thin") xlwt.add_palette_colour("custom_yellow", 0x25) workbook.set_colour_RGB(0x25, 255, 255, 179) blank_cell_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz center, vert center; pattern: pattern solid, fore_colour custom_yellow; borders: top thin,right thin,bottom thin,left thin") return workbook,header_bold,body_style,qty_cell_style,value_style,blank_cell_style,value_style2
def create_sheet(self): workbook = xlwt.Workbook() borders = Borders() header_border = Borders() header_border.left, header_border.right, header_border.top, header_border.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THICK borders.left, borders.right, borders.top, borders.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THIN header_bold = xlwt.easyxf( "font: bold on, height 200; pattern: pattern solid, fore_colour gray25;alignment: horizontal center ,vertical center" ) header_bold.borders = header_border body_style = xlwt.easyxf( "font: height 200; alignment: horizontal left") body_style.borders = borders # # style for different colors in columns xlwt.add_palette_colour("light_blue_21", 0x21) workbook.set_colour_RGB(0x21, 176, 216, 230) qty_cell_style = xlwt.easyxf( "font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour light_blue_21; borders: top thin,right thin,bottom thin,left thin" ) xlwt.add_palette_colour("custom_orange", 0x22) workbook.set_colour_RGB(0x22, 255, 204, 153) value_style = xlwt.easyxf( "font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_orange; borders: top thin,right thin,bottom thin,left thin" ) xlwt.add_palette_colour("custom_pink", 0x23) workbook.set_colour_RGB(0x23, 255, 204, 204) days_style = xlwt.easyxf( "font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_pink; borders: top thin,right thin,bottom thin,left thin" ) return workbook, header_bold, body_style, qty_cell_style, value_style, days_style
def css2excel(css): #custom_css = 'font: name "%s", %s on'%(current_value['font-family'].split(",")[0], current_value['font-weight'].split(",")[0]) #export_sheet.write(row_idx, col_idx, current_value['data'], xlwt.easyxf('font: italic on; pattern: pattern solid, fore-colour grey25')) xf_list = [] fnt = Font() borders = Borders() pattern = Pattern() align = Alignment() process_css = { 'font-family' : [fnt, "name" , lambda x : x.split(",")[0]], 'color' : [fnt, "colour_index", lambda x : excel_color_map.get(x,0)+8], 'font-weight' : [fnt, "bold", lambda x : x.upper() == 'BOLD'], #'font-weight' : [fnt, "bold", lambda x : x == '700'], 'text-align' : [align, "horz", lambda x : {'left':align.HORZ_LEFT, 'right':align.HORZ_RIGHT, 'center':align.HORZ_CENTER, 'justified': align.HORZ_JUSTIFIED}[x]], 'background-color' : [pattern,"pattern_fore_colour", lambda x: excel_color_map.get(x,16)+8], } #TODO process_css -> css for i in process_css.keys(): #print process_css[i][0] ,".",process_css[i][1], " => " , css[i] ," | ", process_css[i][2](css[i]) setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i])) style = XFStyle() style.font = fnt borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style.borders = borders style.pattern = pattern style.pattern.pattern = 1 style.alignment = align return style
def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment style.borders = borders #~ style.pattern = pattern return style
def RUNN(): # ghi file excel fnt = Font() fnt.name = "Times New Roman" fnt.height = 280 borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.font = fnt style.borders = borders wb = Workbook() sheet1 = wb.add_sheet("Điểm") col = 0 row = 0 sheet1.write(row, col, "STT", style) sheet1.write(row, col + 1, "TÊN BÀI LÀM", style) sheet1.write(row, col + 2, "ĐIỂM", style) row += 1 diem = [] st = 1 for i in t: if i == name_form: continue else: try: t1 = docx.Document(i) sheet1.write(row, col, st, style) sheet1.write(row, col + 1, i[:-5], style) sheet1.write(row, col + 2, round(10 * so_sanh(t1), 2), style) row += 1 st += 1 except Exception: pass wb.save("KetQua.xls")
def export_to_excel(self, **kwargs): work_book = Workbook(encoding="ascii") center_alignment = Alignment() center_alignment.horz = Alignment.HORZ_RIGHT center_alignment.vert = Alignment.VERT_CENTER border = Borders() border.top = Borders.THIN border.left = Borders.THIN border.bottom = Borders.THIN border.right = Borders.THIN head_style = XFStyle() head_pattern = Pattern() head_pattern.pattern = Pattern.SOLID_PATTERN head_pattern.pattern_fore_colour = Style.colour_map['gray25'] head_style.pattern = head_pattern head_font = Font() head_font.bold = True head_style.font = head_font head_style.alignment = center_alignment head_style.borders = border data_style = XFStyle() data_style.alignment = center_alignment data_style.borders = border work_sheet = work_book.add_sheet("Sheet") limit = 10000 count = self.get_data_scale(**kwargs)['count'] times = (count + limit - 1) // limit add_header = False for i in range(1): kwargs["startIndex"] = i * limit + 1 kwargs['endIndex'] = (i + 1) * limit + 1 records = self.get_data(**kwargs) column_names = [] y = 0 for data in records: if not add_header: x = 0 for column_name in data.keys(): work_sheet.write(y, x, column_name, head_style) column_names.append(column_name) x += 1 add_header = True y += 1 x = 0 for column_name in column_names: work_sheet.write(y, x, data[column_name], data_style) x += 1 y += 1 x = 0 work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
def excel_style(): borders_amount = Borders() borders_amount.right = Borders.THIN borders_amount.top = Borders.THIN borders_amount.bottom = Borders.THIN borders_name = Borders() borders_name.left = Borders.THIN borders_name.right = Borders.THIN borders_name.top = Borders.THIN borders_name.bottom = Borders.THIN style_amount = XFStyle() style_amount.borders = borders_amount style_name = XFStyle() style_name.borders = borders_name return style_amount, style_name
def __init__(self, text, left_border=False, right_border=False, use_pattern=False, width=0x0E00, func=lambda obj: ""): super(Column, self).__init__(text, self.title_fnt, self.title_aln, Borders(), self.title_ptn, width=width) self.obj_style = XFStyle() self.obj_style.alignment = self.title_aln self.obj_style.borders = Borders() self.obj_style.borders.left = Borders.HAIR self.obj_style.borders.right = Borders.HAIR self.obj_style.borders.top = Borders.HAIR self.obj_style.borders.bottom = Borders.HAIR self.border.left = Borders.HAIR self.border.right = Borders.HAIR self.border.top = Borders.THICK self.border.bottom = Borders.THICK if left_border: self.obj_style.borders.left = Borders.THICK self.border.left = Borders.THICK if right_border: self.obj_style.borders.right = Borders.THICK self.border.right = Borders.THICK self.obj_style.font = self.obj_fnt if use_pattern: self.obj_style.pattern = self.pattern self.func = func
def creaHojaDeTraduccion(excel, nombreHoja="traduccion"): hoja=excel.creaHoja(nombreHoja) hoja.col(0).width=90*256 hoja.col(1).width=20*256 fnt = Font() fnt.bold=True #fnt.colour_index=2 borders = Borders() borders.left = Borders.MEDIUM borders.right = Borders.MEDIUM borders.top = Borders.MEDIUM borders.bottom = Borders.MEDIUM pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x18#0x99 style = XFStyle() style.num_format_str='YYYY-MM-DD' style.font = fnt style.borders = borders style.pattern = pattern hoja.write(0,0,"Original Text",style) hoja.write(0,1,"Translation",style) return hoja
def write_header(self, ws, year): row = 0 for column in range(0, 11): ws.write(row, column, '', Style.black_on_grey) for month, column in enumerate(range(11, 83, 6), 1): for shift in range(0, 6): ws.write(row, column + shift, '{} {}'.format(calendar.month_abbr[month], year), Style.main) row = 1 for column, value in self.table_layout.iteritems(): h_style = copy.copy(value['header']) h_style.borders = Borders() h_style.borders.bottom = 1 ws.write(row, column, value['name'], h_style) ws.set_panes_frozen(True) # frozen headings instead of split panes return ws, row
def transCellData_summary(pos, val): tmp = [] tmp.append(transpos(pos)[0]) tmp.append(transpos(pos)[1]) style = getDefualtStyle() style.font.colour_index = 0x17 style.alignment.horz = Alignment.HORZ_CENTER #style.borders.right = Borders.NO_LINE style.font.bold = True borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style.borders = borders try: tmp.append(val) except: tmp.append(0) return tmp, style
def get_sheet_title_style(bg_color=0x39, font_color=0x0, font_size=300, has_pattern=True, horz_center=True): fnt = Font() fnt.name = 'Arial' fnt.colour_index = font_color fnt.bold = True fnt.height = font_size borders = Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 al = Alignment() if horz_center: al.horz = Alignment.HORZ_CENTER else: al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER pattern = None if has_pattern: pattern = Pattern() pattern.pattern = 1 pattern.pattern_fore_colour = bg_color pattern.pattern_back_colour = bg_color style = XFStyle() style.font = fnt style.borders = borders style.alignment = al if pattern: style.pattern = pattern return style
def css2excel(css): fnt = Font() borders = Borders() pattern = Pattern() align = Alignment() process_css = { 'font-family': [fnt, "name", lambda x: x.split(",")[0]], 'font-size': [fnt, "height", lambda x: get_font_height(x.upper())], 'color': [fnt, "colour_index", lambda x: match_color_index(x.upper())], 'font-weight': [fnt, "bold", lambda x: x == 'bold'], 'font-style': [fnt, "italic", lambda x: x == 'italic'], 'text-align': [align, "horz", lambda x: get_horizontal_align(x, align)], 'vertical-align': [align, "vert", lambda x: get_vertical_align(x, align)], 'background-color': [ pattern, "pattern_fore_colour", lambda x: match_color_index(x.upper()) ], } for i in css.keys(): if i in process_css.keys(): setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i].strip())) style = XFStyle() style.font = fnt borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style.borders = borders style.pattern = pattern style.pattern.pattern = 1 style.alignment = align style.num_format_str = '#,##0.00' return style
def in_ket_qua(): # tao file ketqua.xls de ghi ket qua fnt = Font() fnt.name = "Times New Roman" fnt.height = 280 borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.font = fnt style.borders = borders wb = Workbook() sheet1 = wb.add_sheet("Điểm") col = 0 row = 0 sheet1.write(row, col, "STT", style) sheet1.write(row, col + 1, "TÊN BÀI LÀM", style) sheet1.write(row, col + 2, "ĐIỂM", style) row += 1 st = 1 # Tinh ket qua cua tat ca cac file bai lam va in ket qua ra file KetQua.xls for name in list_file_word: sheet1.write(row, col, st, style) sheet1.write(row, col + 1, name[:-5], style) sheet1.write(row, col + 2, tinhKetQua(name), style) row += 1 st += 1 print(name, "___ĐÃ CHẤM XONG___") # Xoa bo cac file anh sau khi da xu li xong for name in glob.glob("*.png"):os.remove(name) # Luu ket qua ra file os.chdir(local) # shutil.rmtree('Temp') wb.save("KetQua.xls")
from datetime import date from xlwt import Workbook, XFStyle, Borders, Pattern, Font fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THICK borders.right = Borders.THICK borders.top = Borders.THICK borders.bottom = Borders.THICK pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x0A style = XFStyle() style.num_format_str='YYYY-MM-DD' style.font = fnt style.borders = borders style.pattern = pattern book = Workbook() sheet = book.add_sheet('A Date') sheet.write(1,1,date(2009,3,18),style) book.save('date.xls')
def archivo_excel(titulo, archivo_logo = 'unprg.bmp', label_resumen = [], datos_resumen = [], heads = [], color = 0x9ACD32, registros = [], nombre_archivo = 'descargar.xls'): book = Workbook() sheet1 = book.add_sheet('Hoja 01') #estilos de celda titulo fnt_titulo = Font() fnt_titulo.name = 'Arial' fnt_titulo.bold = True style_titulo = XFStyle() style_titulo.font = fnt_titulo #estilos de celda etiqueta resumen fnt_etiqueta_resumen = Font() fnt_etiqueta_resumen.name = 'Arial' fnt_etiqueta_resumen.bold = True style_etiqueta_resumen = XFStyle() style_etiqueta_resumen.font = fnt_etiqueta_resumen #estilos de celda datos resumen fnt_dato_resumen = Font() fnt_dato_resumen.name = 'Arial' style_dato_resumen = XFStyle() style_dato_resumen.font = fnt_dato_resumen #estilos de celda heads fnt_heads = Font() fnt_heads.name = 'Arial' fnt_heads.bold = True borders_heads = Borders() borders_heads.left = Borders.THIN borders_heads.right = Borders.THIN borders_heads.top = Borders.THIN borders_heads.bottom = Borders.THIN pattern_heads = Pattern() pattern_heads.pattern = Pattern.SOLID_PATTERN pattern_heads.pattern_fore_colour = color style_heads = XFStyle() style_heads.font = fnt_heads style_heads.borders = borders_heads style_heads.pattern = pattern_heads #estilos de celda registros fnt_registros = Font() fnt_registros.name = 'Arial' borders_registros = Borders() borders_registros.left = Borders.THIN borders_registros.right = Borders.THIN borders_registros.top = Borders.THIN borders_registros.bottom = Borders.THIN style_registros = XFStyle() style_registros.font = fnt_registros style_registros.borders = borders_registros sheet1.insert_bitmap(settings.MEDIA_ROOT + 'archivos_excel/%s' % archivo_logo, 1, 0) #escribir el titulo sheet1.write(10,0,titulo, style_titulo) row = 12 col = 0 #escribir las etiquetas del resumen for etiqueta in label_resumen: sheet1.write(row, col, etiqueta, style_etiqueta_resumen) row+=1 row = 12 col = 1 #escribir los datos del resumen for dato in datos_resumen: sheet1.write(row, col, dato, style_dato_resumen) row+=1 row+=1 col = 0 #escribimos los encabezados for head in heads: sheet1.write(row,col,head,style_heads) col+=1 row+=1 col=1 n = 1 #recorremos la lista y escribimos los datos for fila in registros: sheet1.write(row,0,n,style_registros) for dato in fila: sheet1.write(row,col,dato,style_registros) col+=1 col=1 row+=1 n+=1 #book.save(settings.MEDIA_ROOT + 'archivos_excel/%s' % nombre_archivo) book.save(TemporaryFile())
def write_sheet(self, data, sheet_name, print_to_screen=False): '''Write a very simple table to a new sheet in a spreadsheet, Optionally, print the table to the screen''' # most cells al = Alignment() al.horz = Alignment.HORZ_RIGHT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.height = 9 * 20 # 9 pt style = XFStyle() style.font = font style.alignment = al # tops cells al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.height = 9 * 20 # 9 pt style_top = XFStyle() style_top.font = font style_top.alignment = al # left cells al = Alignment() al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.italic = True font.height = 9 * 20 # 9 pt style_left = XFStyle() style_left.font = font style_left.alignment = al ws = self.add_sheet(sheet_name) for i, row in enumerate(data): for j, cell in enumerate(row): borders = Borders() if i == 0: borders.top = 1 borders.bottom = 2 if i == len(row) - 1: borders.bottom = 1 if j == 0: borders.left = 1 borders.right = 1 if j == len(row) - 1: borders.right = 1 if j == 0: _style = style_left elif i == 0: _style = style_top else: _style = style _style.borders = borders ws.write(i + 1, j + 1, cell, _style) if print_to_screen: print print_table(data, sheet_name, bold=True)
def generar_xsl(self, cr, uid, ids, context=None): header_style = XFStyle() borders = Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 header_style.borders = borders first_book = Workbook() ########################################################################################## # Estrutura principal del xsl (Encabezado) ########################################################################################## style0 = xlwt.easyxf('font: name Arial, colour black, bold on') #Estilo de fuente negrita style1 = xlwt.easyxf('align: horiz center') #Estilo alinear al centro centrado ws1 = first_book.add_sheet('first_sheet', cell_overwrite_ok=True) ws1.write_merge(0, 0, 0, 8,) #Numero de columnas que se expandera el encabezado ws1.row(0).height = 300 #Grosor de la fila del encabezado #Declaramos los encabezados de las columnas asi: #"FilaN, ColumnaN, 'Nombre de la columna', estilo_columna" ws1.write(0, 0, 'LISTADO DE SOLICITUDES', style0) ws1.write(1, 0, 'Codigo de Solicitud', style0) ws1.write(1, 1, 'Modelo', style0) ws1.write(1, 2, 'Serial', style0) ws1.write(1, 3, 'Fecha de Solicitud', style0) ws1.write(1, 4, 'Cedula', style0) ws1.write(1, 5, 'Nombre', style0) ws1.write(1, 6, 'Apellido', style0) ws1.write(1, 7, 'Status', style0) ws1.write(1, 8, 'Registrado', style0) get_gc = self.pool.get('solicitud.soporte') # Objeto search_repar = get_gc.search(cr, uid, [], context=None) # Se busca Todo r_canaimas = get_gc.read(cr,uid,search_repar,context=context) # Se refleja el resultado i = 0 for x in r_canaimas: # Bloque para la iteracion del objeto user_r = x['user_register'] #Como son camos many2one trae informacion que no necesitamos usuario = user_r[1] #por eso declaramos una variable que muestre el dato en la posicion que necesitamos. mod = x['modelo'] modelo = mod[1] nom = x['nombre_r'].upper() nombre = self.elimina_tildes(nom) ape = x['apellido_r'].upper() apellido = self.elimina_tildes(ape) #Asociamos cada columna a un campo de la base de datos ws1.write(i+2, 0, str(x['c_solicitud'])) ws1.write(i+2, 1, modelo) ws1.write(i+2, 2, str(x['serial'])) ws1.write(i+2, 3, str(x['f_solicitud'])) ws1.write(i+2, 4, str(x['cedula'])) ws1.write(i+2, 5, nombre) ws1.write(i+2, 6, apellido) ws1.write(i+2, 7, str(x['status'])) ws1.write(i+2, 8, usuario) i = i + 1 # Acumulador de la data # Variables de tiempo (dia, mes, año) para que cada vez que # se genere un reporte, al nombre se le adiera la fecha del día. dia = time.strftime('%d') mes = time.strftime('%m') year = time.strftime('%Y') fecha = dia+"-"+mes+"-"+year #Variable que concatena el dia la fecha y el año nom = 'Solicitudes Atendidas '+fecha+'.xls' #C first_book.save('/home/administrador/openerp70/modules/gestion_canaimas/reporte/'+nom) f = open('/home/administrador/openerp70/modules/gestion_canaimas/reporte/'+nom) r_archivo = self.pool.get('reportes.canaimas').create(cr, uid, { 'name' : nom, 'res_name' : nom, 'datas' : base64.encodestring(f.read()), 'datas_fname' : nom, 'res_model' : 'solicitud.soporte', 'gerencia' : 'Desarrollo Social', },context=context) return r_archivo
def write_sheet(self, data, sheet_name, print_to_screen=False): '''Write a very simple table to a new sheet in a spreadsheet, Optionally, print the table to the screen''' # most cells al = Alignment() al.horz = Alignment.HORZ_RIGHT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.height = 9 * 20 # 9 pt style = XFStyle() style.font = font style.alignment = al # tops cells al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.height = 9 * 20 # 9 pt style_top = XFStyle() style_top.font = font style_top.alignment = al # left cells al = Alignment() al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.italic = True font.height = 9 * 20 # 9 pt style_left = XFStyle() style_left.font = font style_left.alignment = al ws = self.add_sheet(sheet_name) for i, row in enumerate(data): for j, cell in enumerate(row): borders = Borders() if i == 0: borders.top = 1 borders.bottom = 2 if i == len(row) - 1: borders.bottom = 1 if j == 0: borders.left = 1 borders.right = 1 if j == len(row) - 1: borders.right = 1 if j == 0: _style = style_left elif i == 0: _style = style_top else: _style = style _style.borders = borders # one of the libraries can get confused with plain strings if isinstance(cell, str): cell = unicode(cell, 'utf-8') ws.write(i + 1, j + 1, cell, _style) if print_to_screen: print print_table(data, sheet_name, bold=True)
def generate_report(root, filename, attend_staff, absent_staff, meeting_direct, record_staff, reports, issues): book = Workbook(encoding='utf-8') sheet1 = book.add_sheet('Fota Security Knox Week Report') line_counter = 0 today = datetime.date.today().strftime('%Y/%m/%d') # header head_str = [ 'Meeting Name', 'SW 3 Group Part 5 Fota/ Security/Knox/Encription TG WeekReport', '', '', 'Meeting Date', today, 'Meeting Address', '8F Meeting Room', '', '', 'Attend Staff', attend_staff, 'Absent Staff', absent_staff, '', '', 'Meeting Direct', meeting_direct, 'Record Staff', record_staff, ] for i in range(len(head_str) / 2): sheet1.row(i).write(0, head_str[2 * i]) sheet1.row(i).write(1, head_str[2 * i + 1]) line_counter = len(head_str) / 2 + 1 # font bold font = Font() font.bold = True # pattern yellow pattern_yellow = Pattern() pattern_yellow.pattern = Pattern.SOLID_PATTERN pattern_yellow.pattern_fore_colour = 0x0D # yellow # pattern gray pattern_gray = Pattern() pattern_gray.pattern = Pattern.SOLID_PATTERN pattern_gray.pattern_fore_colour = 0x17 # gray # borders thin borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN # alignment horizontal center alig_hc = Alignment() # alig.horizontal = Alignment.HORZ_CENTER #no effect, why? f**k!!! alig_hc.horz = Alignment.HORZ_CENTER # title style style_title = XFStyle() style_title.font = font style_title.pattern = pattern_yellow style_title.alignment = alig_hc sheet1.write_merge( line_counter, line_counter, 0, 3, 'summary', style_title) line_counter += 1 # table header style sytle_tb_header = XFStyle() sytle_tb_header.font = font sytle_tb_header.pattern = pattern_gray sytle_tb_header.borders = borders sytle_tb_header.alignment = alig_hc sheet1.row(line_counter).write(0, 'Member', sytle_tb_header) sheet1.row(line_counter).write(1, 'Week Jobs', sytle_tb_header) sheet1.row(line_counter).write(2, 'Risk', sytle_tb_header) sheet1.row(line_counter).write(3, 'Next Week Plan', sytle_tb_header) line_counter += 1 # content # for i in range(line_counter,11+line_counter): i = line_counter for report in reports: # alignment alig = Alignment() alig.horz = Alignment.HORZ_CENTER alig.vert = Alignment.VERT_CENTER alig.wrap = 1 # alignment2 alig2 = Alignment() alig2.vert = Alignment.VERT_CENTER alig2.wrap = 1 # borders borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN # colors pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x2F style_content = XFStyle() style_content.alignment = alig style_content.borders = borders style_content2 = XFStyle() style_content2.alignment = alig2 style_content2.borders = borders style_content2.pattern = pattern style_content3 = XFStyle() style_content3.alignment = alig2 style_content3.borders = borders sheet1.row(i).write(0, report.who, style_content) sheet1.row(i).write(1, report.job, style_content2) sheet1.row(i).write(2, report.risk, style_content2) sheet1.row(i).write(3, report.plan, style_content3) sheet1.row(i).height_mismatch = True sheet1.row(i).height = 1500 i += 1 line_counter += len(reports) line_counter += 1 sheet1.write_merge( line_counter, line_counter, 0, 3, 'Main Issues List', style_title) line_counter += 1 sheet1.row(line_counter).write(0, 'Items', sytle_tb_header) sheet1.write_merge( line_counter, line_counter, 1, 2, 'Deatail', sytle_tb_header) sheet1.row(line_counter).write(3, 'Status', sytle_tb_header) line_counter += 1 sheet1.col(0).width = 256 * 16 sheet1.col(1).width = 256 * 50 sheet1.col(2).width = 256 * 31 sheet1.col(3).width = 256 * 46 book.save(root + '/' + filename)
def create_sheet(self): workbook = xlwt.Workbook() borders = Borders() header_border = Borders() header_title_border = Borders() ware_or_loc_border = Borders() header_border.left, header_border.right, header_border.top, header_border.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THICK header_title_border.left, header_title_border.right, header_title_border.top, header_title_border.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THICK ware_or_loc_border.left, ware_or_loc_border.right, ware_or_loc_border.top, ware_or_loc_border.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THICK borders.left, borders.right, borders.top, borders.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THIN header_bold = xlwt.easyxf("font: bold on, height 250; pattern: pattern solid, fore_colour gray25;alignment: horizontal center ,vertical center") header_bold.borders = header_border body_style = xlwt.easyxf("font: height 200; alignment: horizontal center") style = xlwt.easyxf("font: height 210, bold True; alignment: horizontal center,vertical center;borders: top medium,right medium,bottom medium,left medium") body_style.borders = borders header_title = xlwt.easyxf("font: bold on, height 315; pattern: pattern solid, fore_colour gray25;alignment: horizontal center ,vertical center") header_title.borders = header_title_border ware_or_loc_header_style = xlwt.easyxf("font: bold on, height 315; pattern: pattern solid, fore_colour gray25;alignment: horizontal center ,vertical center") ware_or_loc_header_style.borders = ware_or_loc_border red_font_style = xlwt.easyxf("font: bold on; pattern: pattern solid, fore_colour red;") xlwt.add_palette_colour("custom_indigo", 0x31) workbook.set_colour_RGB(0x31, 209, 196, 233) cost_cell_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_indigo; borders: top thin,right thin,bottom thin,left thin") cost_cell_style.string_num_format = '0.00' xlwt.add_palette_colour("light_blue_21", 0x25) workbook.set_colour_RGB(0x25, 179, 255, 240) worksheet_available_virtual_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour light_blue_21; borders: top thin,right thin,bottom thin,left thin") worksheet_available_virtual_style.string_num_format = '0.00' xlwt.add_palette_colour("custom_yellow", 0x21) workbook.set_colour_RGB(0x21, 255, 255, 179) blank_cell_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_yellow; borders: top thin,right thin,bottom thin,left thin") xlwt.add_palette_colour("custom_pink", 0x23) workbook.set_colour_RGB(0x23, 255, 204, 204) worksheet_incoming_outgoing_net_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_pink; borders: top thin,right thin,bottom thin,left thin") worksheet_incoming_outgoing_net_style.string_num_format = '0.00' xlwt.add_palette_colour("custom_green", 0x24) workbook.set_colour_RGB(0x24, 204, 255, 204) worksheet_totalsoldqty_totalpurchasedqty_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_green; borders: top thin,right thin,bottom thin,left thin") worksheet_totalsoldqty_totalpurchasedqty_style.string_num_format = '0.00' xlwt.add_palette_colour("custom_orange", 0x22) workbook.set_colour_RGB(0x22, 255, 204, 153) worksheet_valuation_style = xlwt.easyxf("font: height 200,bold on, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour custom_orange; borders: top thin,right thin,bottom thin,left thin") worksheet_valuation_style.string_num_format = '0.00' return workbook , header_bold, body_style, style, header_title, ware_or_loc_header_style, red_font_style, cost_cell_style, worksheet_available_virtual_style, blank_cell_style, worksheet_incoming_outgoing_net_style, worksheet_totalsoldqty_totalpurchasedqty_style, worksheet_valuation_style
def write_cursor_to_excel(curs, filename, sheetTitle): """write_cursor_to_excel curs: a cursor for an open connection to an oracle database filename: name of the XLS file to create sheetTitle: name of the sheet to create """ # create style for header row - bold font, thin border below fnt = Font() fnt.bold = True borders = Borders() borders.bottom = Borders.THIN hdrstyle = XFStyle() hdrstyle.font = fnt hdrstyle.borders = borders # create a date format style for any date columns, if any datestyle = XFStyle() datestyle.num_format_str = 'DD/MM/YYYY' # create the workbook. (compression: try to reduce the number of repeated styles) wb = Workbook(style_compression=2) # the workbook will have just one sheet sh = wb.add_sheet(sheetTitle) # write the header line, based on the cursor description c = 0 colWidth = [] for col in curs.description: #col[0] is the column name #col[1] is the column data type sh.write(0, c, col[0], hdrstyle) colWidth.append(1) # arbitrary min cell width if col[1] == cx_Oracle.DATETIME: colWidth[-1] = len(datestyle.num_format_str) if colWidth[-1] < len(col[0]): colWidth[-1] = len(col[0]) c += 1 # write the songs, one to each row r = 1 for song in curs: row = sh.row(r) for c in range(len(song)): if song[c]: if curs.description[c][1] == cx_Oracle.DATETIME: row.write(c, song[c], datestyle) else: if colWidth[c] < len(str(song[c])): colWidth[c] = len(str(song[c])) row.write(c, song[c]) r += 1 for c in range(len(colWidth)): sh.col(c).width = colWidth[c] * 350 # freeze the header row sh.panes_frozen = True sh.vert_split_pos = 0 sh.horz_split_pos = 1 wb.save(filename)
try: import erp.model as m import erp.model.testing as tst except ImportError: import portal.model as m import portal.model.testing as tst fnt = Font() fnt.name = 'Arial' fnt.bold = True style_bold = XFStyle() style_bold.font = fnt border_bottom = Borders() border_bottom.bottom = Borders.THIN style_bold_border_bottom = XFStyle() style_bold_border_bottom.font = fnt style_bold_border_bottom.borders = border_bottom border_right = Borders() border_right.right = Borders.THIN style_bold_border_right = XFStyle() style_bold_border_right.font = fnt style_bold_border_right.borders = border_right def version_items(version): """
def output(self): # from xlrd import open_workbook from xlwt import Workbook, XFStyle, Borders, Alignment, Font, Pattern, Style, easyxf # from xlutils.copy import copy """ rb = open_workbook(r"Templates\template.xls") wb = copy(rb) s = wb.get_sheet(0) s.write(0, 0, 'A1') wb.save(r"C:\Temp\nephro-planner\new.xls") """ book = Workbook(encoding="utf-8") sheet = book.add_sheet(r"Feuille1") style_title = XFStyle() font_title = Font() font_title.name = "Comic Sans MS" font_title.height = 280 style_title.font = font_title style_title.alignment.horz = Alignment.HORZ_CENTER style_title.alignment.vert = Alignment.VERT_CENTER style_cell_bottom = XFStyle() borders_cell_bottom = Borders() borders_cell_bottom.bottom = Borders.MEDIUM style_cell_bottom.borders = borders_cell_bottom style_header = XFStyle() font_header = Font() font_header.bold = 1 font_header.name = "Arial Narrow" font_header.height = 240 style_header.font = font_header style_header.alignment.horz = Alignment.HORZ_CENTER style_header.alignment.vert = Alignment.VERT_CENTER borders_header = Borders() borders_header.top = Borders.MEDIUM borders_header.left = Borders.MEDIUM borders_header.bottom = Borders.MEDIUM borders_header.right = Borders.MEDIUM style_header.borders = borders_header style_sub_header = XFStyle() font_sub_header = Font() font_sub_header.name = "Arial Narrow" font_sub_header.height = 240 style_sub_header.font = font_sub_header style_sub_header.alignment.horz = Alignment.HORZ_CENTER style_sub_header.alignment.vert = Alignment.VERT_CENTER borders_sub_header = Borders() borders_sub_header.top = Borders.MEDIUM borders_sub_header.left = Borders.MEDIUM borders_sub_header.bottom = Borders.MEDIUM borders_sub_header.right = Borders.MEDIUM style_sub_header.borders = borders_sub_header style_date = XFStyle() font_date = Font() font_date.name = "Arial Narrow" font_date.height = 240 style_date.font = font_date style_date.alignment.horz = Alignment.HORZ_RIGHT style_date.alignment.vert = Alignment.VERT_CENTER borders_date = Borders() borders_date.left = Borders.MEDIUM style_date.borders = borders_date style_date_status = XFStyle() font_date_status = Font() font_date_status.name = "Arial Narrow" font_date_status.height = 240 style_date_status.font = font_date_status style_date_status.alignment.horz = Alignment.HORZ_LEFT style_date_status.alignment.vert = Alignment.VERT_CENTER borders_date_status = Borders() borders_date_status.right = Borders.MEDIUM style_date_status.borders = borders_date_status style_cell_normal = XFStyle() font_cell_normal = Font() font_cell_normal.name = "Arial Narrow" font_cell_normal.height = 220 style_cell_normal.font = font_cell_normal style_cell_normal.alignment.horz = Alignment.HORZ_CENTER style_cell_normal.alignment.vert = Alignment.VERT_CENTER style_cell_right = XFStyle() font_cell_right = Font() font_cell_right.name = "Arial Narrow" font_cell_right.height = 220 style_cell_right.font = font_cell_right style_cell_right.alignment.horz = Alignment.HORZ_CENTER style_cell_right.alignment.vert = Alignment.VERT_CENTER borders_cell_right = Borders() borders_cell_right.right = Borders.MEDIUM style_cell_right.borders = borders_cell_right style_cell_top = XFStyle() borders_cell_top = Borders() borders_cell_top.top = Borders.MEDIUM style_cell_top.borders = borders_cell_top column_offset = 1 date_column_offset = 2 row_offset = 1 table_width = 1 + 3 * len(Database.team()) # build titles sheet.write_merge(row_offset, row_offset, column_offset, table_width + 1, "POLE MEDECINE INTERNE", style_title) sheet.write_merge( row_offset + 1, row_offset + 1, column_offset, table_width + 1, "Service NEPHROLOGIE – HEMODIALYSE", style_title, ) sheet.write_merge( row_offset + 2, row_offset + 2, column_offset, table_width + 1, "Planning de {0} {1}".format(self.human_readable_months[self.month - 1], self.year), style_title, ) # patch date columns top borders sheet.write(row_offset + 5, column_offset, "", style_cell_bottom) sheet.write(row_offset + 5, column_offset + 1, "", style_cell_bottom) # build header and sub header for x in Database.team(): sheet.write_merge( row_offset + 4, row_offset + 4, column_offset + date_column_offset + 3 * (x.id - 1), column_offset + date_column_offset + 3 * x.id - 1, x.name, style_header, ) sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1), "M", style_sub_header) sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 1, "AM", style_sub_header) sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 2, "N", style_sub_header) """ i = 40 for x in sorted(Style.colour_map): style = XFStyle() pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = Style.colour_map[x] style.pattern = pattern sheet.write(i, 1, x, style) i += 1 """ pattern_pale_blue = Pattern() pattern_pale_blue.pattern = Pattern.SOLID_PATTERN pattern_pale_blue.pattern_fore_colour = Style.colour_map["pale_blue"] pattern_light_yellow = Pattern() pattern_light_yellow.pattern = Pattern.SOLID_PATTERN pattern_light_yellow.pattern_fore_colour = Style.colour_map["light_yellow"] pattern_ice_blue = Pattern() pattern_ice_blue.pattern = Pattern.SOLID_PATTERN pattern_ice_blue.pattern_fore_colour = Style.colour_map["ice_blue"] pattern_light_green = Pattern() pattern_light_green.pattern = Pattern.SOLID_PATTERN pattern_light_green.pattern_fore_colour = Style.colour_map["light_green"] pattern_ivory = Pattern() pattern_ivory.pattern = Pattern.SOLID_PATTERN pattern_ivory.pattern_fore_colour = Style.colour_map["ivory"] pattern_tan = Pattern() pattern_tan.pattern = Pattern.SOLID_PATTERN pattern_tan.pattern_fore_colour = Style.colour_map["tan"] pattern_gold = Pattern() pattern_gold.pattern = Pattern.SOLID_PATTERN pattern_gold.pattern_fore_colour = Style.colour_map["gold"] def __cell_colouration__(style, current_activity): if current_activity is Activity.CONSULTATION: style.pattern = pattern_pale_blue elif current_activity is Activity.DIALYSIS: style.pattern = pattern_ice_blue elif current_activity is Activity.NEPHROLOGY: style.pattern = pattern_light_green elif current_activity is Activity.OTHERS: style.pattern = pattern_tan elif current_activity is Activity.OBLIGATION: style.pattern = pattern_ivory elif current_activity is Activity.OBLIGATION_WEEKEND: style.pattern = pattern_light_yellow elif current_activity is Activity.OBLIGATION_HOLIDAY: style.pattern = pattern_gold else: style.pattern = Pattern() return style last_day = calendar.monthrange(self.year, self.month)[1] for x in range(1, last_day + 1): current_date = date(self.year, self.month, x) current_daily_planning = self.daily_plannings[current_date] # build date and date status columns sheet.write( row_offset + 5 + x, column_offset, "{0}. {1}".format(self.human_readable_days[current_daily_planning.weekday][0:3].lower(), x), style_date, ) if current_daily_planning.weekday in [5, 6]: sheet.write(row_offset + 5 + x, column_offset + 1, "WK", style_date_status) elif not current_daily_planning.is_working_day: sheet.write(row_offset + 5 + x, column_offset + 1, "Férié", style_date_status) else: sheet.write(row_offset + 5 + x, column_offset + 1, "", style_date_status) """ easyxf( 'font: bold 1, name Tahoma, height 160;' 'align: vertical center, horizontal center, wrap on;' 'borders: left thin, right thin, top thin, bottom thin;' 'pattern: pattern solid, pattern_fore_colour green, pattern_back_colour green' ) """ # fill in month planning for y in Database.team(): current_activity = current_daily_planning.__get_activity__(TimeSlot.FIRST_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_normal, current_activity), ) current_activity = current_daily_planning.__get_activity__(TimeSlot.SECOND_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 1 + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_normal, current_activity), ) current_activity = current_daily_planning.__get_activity__(TimeSlot.THIRD_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 2 + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_right, current_activity), ) # patch table bottom border for x in range(0, table_width + 1): sheet.write(row_offset + 5 + (last_day + 1), column_offset + x, "", style_cell_top) book.save(r"C:\Temp\nephro-planner\new.xls")
def CreateExcelSpreadsheet(table, output_excel, use_alias=True): """Exports table to excel Required: table -- input table output_excel -- output excel table (.xlsx, .xls) Optional: use_alias -- use field alias name for column headers. Default is True """ # build field dict fieldNames = [(f.name, f.aliasName) for f in arcpy.ListFields(table) if f.type != 'Geometry'] fields = [f[1] if use_alias in ('true', True) else f[0] for f in fieldNames] widths = {i: arial10.fitwidth(f) + 1024 for i,f in enumerate(fields)} # get field values *Changed from type dict to list with arcpy.da.SearchCursor(table, [f[0] for f in fieldNames]) as rows: values = [r for r in rows] # Create spreadsheet wb = Workbook() ws = wb.add_sheet('Sheet 1') cols = len(fields) rows = len(values) + 1 # set styles #*************************************************************************** borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.borders = borders # headers fntHeaders = Font() fntHeaders.bold = True fntHeaders.height = 220 styleHeaders = XFStyle() styleHeaders.font = fntHeaders styleHeaders.borders = borders # for date fields styleDate = XFStyle() styleDate.borders = borders styleDate.num_format_str = 'MM/DD/YYYY' #*************************************************************************** # write headers and freeze panes for ci,field in enumerate(fields): ws.write(0, ci, field, styleHeaders) # freeze headers ws.set_panes_frozen(True) ws.set_horz_split_pos(1) # fill in values start = 1 for vals in values: for i, value in enumerate(vals): ws.write(start, i, value, styleDate if isinstance(value, datetime.datetime) else style) v_width = arial10.fitwidth(str(value).strip()) if v_width > widths[i]: widths[i] = v_width start += 1 if not start % 1000: ws.flush_row_data() # autofit column widths for ci,width in widths.iteritems(): ws.col(ci).width = int(width + 256) # just a little more padding # save workbook wb.save(output_excel) del wb out_url = '/'.join([PATH_URL, os.path.basename(out_file)]) arcpy.SetParameter(2, out_url) arcpy.AddMessage(out_url) return out_url
# 操作Excel2003 或者更低 from xlwt import Workbook, Alignment, Borders, XFStyle, Formula import xlrd book = Workbook() sheet1 = book.add_sheet('first sheet') a1 = Alignment() a1.horz = Alignment.HORZ_CENTER a1.vert = Alignment.HORZ_CENTER borders = Borders() borders.bottom = Borders.THICK style = XFStyle() style.alignment = a1 style.borders = borders row = sheet1.row(0) row.write(0, 'test', style=style) row = sheet1.row(1) for i in range(5): row.write(i, i, style=style) row.write(5, Formula('SUM(A2:E2)'), style=style) book.save('./test2003.xls') book = xlrd.open_workbook('./test2003.xls') sheet1 = book.sheet_by_name('first sheet') row = sheet1.row(0) print(row[0].value) # 读不到公式,无解 print(sheet1.row(1)[5].value) # print(sheet1.cell(1, 5).value)
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型 pattern.pattern_fore_colour = i # 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on... style.pattern = pattern # 将赋值好的模式参数导入Style sheet.write_merge(i, i, 0, 2, Line_data, style) #以合并单元格形式写入数据,即将数据写入以第1/2/3列合并德单元格内 for i in range(0x00,0xff): # 设置单元格内字体样式 fnt = Font() # 创建一个文本格式,包括字体、字号和颜色样式特性 fnt.name = '微软雅黑' # 设置其字体为微软雅黑, 'SimSun' # 指定“宋体” fnt.colour_index = i # 设置其字体颜色 fnt.bold = True style.font = fnt #将赋值好的模式参数导入Style sheet.write_merge(i,i,3,5,Line_data,style) #以合并单元格形式写入数据,即将数据写入以第4/5/6列合并德单元格内 for i in range(0, 0x53): # 设置单元格下框线样式 borders = Borders() borders.left = i borders.right = i borders.top = i borders.bottom = i style.borders = borders #将赋值好的模式参数导入Style sheet.write_merge(i,i,6,8,Line_data,style) #以合并单元格形式写入数据,即将数据写入以第4/5/6列合并德单元格内 for i in range(6, 80): # 设置单元格下列宽样式 sheet.write(0,i,Line_data,style) sheet.col(i).width = 0x0d00 + i*50 path_py = "/home/gswewf/jian1.bmp" #读取插入图片以.py运行时路径,images和.py在同一目录下 sheet.insert_bitmap(path_py, 2, 9) #插入一个图片
def performance_register_report(self, cr, uid, ids, data, context=None): obj = self.browse(cr, uid, ids) emp_obj = self.pool.get('hr.employee') f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('Payment Bonus') total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x00 borders1.right = 0x00 borders1.top = 0x00 borders1.bottom = 0x00 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x00 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt7 = Font() fnt7.name = 'Arial' fnt7.height = 275 content_fnt7 = Font() content_fnt7.name = 'Arial' content_fnt7.height = 150 align_content7 = Alignment() align_content7.horz = Alignment.HORZ_CENTER borders7 = Borders() borders7.left = 0x02 borders7.right = 0x02 borders7.top = 0x02 borders7.bottom = 0x02 align7 = Alignment() align7.horz = Alignment.HORZ_CENTER align7.vert = Alignment.VERT_CENTER pattern7 = Pattern() pattern7.pattern7 = Pattern.SOLID_PATTERN pattern7.pattern7_fore_colour = 0x1F style_header7 = XFStyle() style_header7.font = fnt style_header7.pattern = pattern style_header7.borders = borders style_header7.alignment = align fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 275 content_fnt2 = Font() content_fnt2.name = 'Arial' content_fnt2.height = 150 fnt2.bold = True align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x00 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x1F style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 300 fnt3.bold = True align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x00 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x1F style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt6 = Font() fnt6.name = 'Arial' fnt6.height = 275 content_fnt6 = Font() content_fnt6.name = 'Arial' content_fnt6.height = 150 align_content6 = Alignment() align_content6.horz = Alignment.HORZ_CENTER borders6 = Borders() borders6.left = 0x02 borders6.right = 0x02 # borders6.top = 0x00 # borders6.bottom = 0x00 align6 = Alignment() align6.horz = Alignment.HORZ_CENTER align6.vert = Alignment.VERT_CENTER pattern6 = Pattern() pattern6.pattern = Pattern.SOLID_PATTERN pattern6.pattern_fore_colour = 0x1F style_header6 = XFStyle() style_header6.font = fnt style_header6.pattern = pattern style_header6.borders = borders6 style_header6.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 200 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() align_content5.horz = Alignment.HORZ_CENTER borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.horz = Alignment.HORZ_CENTER align5.vert = Alignment.VERT_CENTER pattern5 = Pattern() # pattern5.pattern = Pattern.SOLID_PATTERN # pattern5.pattern_fore_colour = 0x1F style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 if obj.company_id: get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip else: get_name = obj.employee_id.resource_id.company_id.name + ' ' + obj.employee_id.resource_id.company_id.street + ' ' + ',' + obj.employee_id.resource_id.company_id.city + ' ' + '-' + obj.employee_id.resource_id.company_id.zip date1 = datetime.strptime(obj.from_date, "%Y-%m-%d").timetuple().tm_year date2 = datetime.strptime(obj.till_date, "%Y-%m-%d").timetuple().tm_year if date1 == date2: d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' - ' + str( date1) + ' ' + '[See Rule 4(b)]' else: d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' ' + str( date1) + ' ' + '-' + ' ' + str(date2) + ' ' + '[See Rule 4(b)]' ws.row(0).height = 500 ws.row(1).height = 500 ws.write_merge(0, 0, 0, 2, 'FORM C', style_header1) ws.write_merge(0, 0, 3, 10, d_name, style_header1) ws.write_merge(1, 1, 0, 2, 'Name of the Establishment', style_header1) ws.write_merge(1, 1, 3, 10, get_name, style_header1) ws.write_merge(1, 1, 11, 19, 'No. of Working days in the Year', style_header1) ws.col(0).width = 5000 ws.col(1).width = 7500 ws.col(2).width = 5000 ws.col(3).width = 7000 ws.col(4).width = 5000 ws.col(5).width = 8000 ws.col(6).width = 6000 ws.col(7).width = 8000 ws.col(8).width = 8000 ws.col(9).width = 9000 ws.col(10).width = 8000 ws.col(11).width = 9000 ws.col(12).width = 7000 ws.col(13).width = 7000 ws.col(14).width = 5000 ws.col(15).width = 5000 ws.col(16).width = 7000 ws.col(17).width = 3000 ws.col(18).width = 3000 ws.col(19).width = 3000 ws.col(20).width = 3000 ws.col(21).width = 3000 ws.col(22).width = 3000 ws.col(23).width = 3000 ws.col(24).width = 3000 ws.col(25).width = 3000 ws.col(26).width = 3000 ws.col(27).width = 3000 ws.col(28).width = 3000 ws.col(29).width = 4000 ws.row(2).height = 400 ws.write(2, 0, 'EMP. CODE', style_header) ws.write(2, 1, 'NAME', style_header) ws.write(2, 2, 'JOINING DATE', style_header) ws.write(2, 3, 'Father Name', style_header) ws.write(2, 4, 'Designation', style_header) ws.write(2, 5, 'Whether he has ', style_header) ws.write(2, 6, 'No.of days', style_header) ws.write(2, 7, 'Total Salary or wages', style_header) ws.write(2, 8, 'Account of bonus payable', style_header) ws.write_merge(2, 2, 9, 12, 'Deduction', style_header7) ws.write(2, 13, 'Net amount payable', style_header) ws.write(2, 14, 'Amount actualy', style_header) ws.write(2, 15, 'Date on which ', style_header) ws.write(2, 16, 'Signature/ Thumb ', style_header) ws.write(2, 17, 'Remarks', style_header) ws.write_merge(2, 2, 18, 19, 'APRIL', style_header7) ws.write_merge(2, 2, 20, 21, 'MAY', style_header7) ws.write_merge(2, 2, 22, 23, 'JUNE', style_header7) ws.write_merge(2, 2, 24, 25, 'JULY', style_header7) ws.write_merge(2, 2, 26, 27, 'AUGUST', style_header7) ws.write_merge(2, 2, 28, 29, 'SEPTEMBER', style_header7) ws.write_merge(2, 2, 30, 31, 'OCTOBER', style_header7) ws.write_merge(2, 2, 32, 33, 'NOVEMBER', style_header7) ws.write_merge(2, 2, 34, 35, 'DECEMBER', style_header7) ws.write_merge(2, 2, 36, 37, 'JANUARY', style_header7) ws.write_merge(2, 2, 38, 39, 'FEBRUARY', style_header7) ws.write_merge(2, 2, 40, 41, 'MARCH', style_header7) ws.write_merge(2, 2, 42, 43, 'TOTAL', style_header7) ws.write(2, 44, 'BONUS', style_header) ws.row(3).height = 400 ws.write(3, 0, '', style_header6) ws.write(3, 1, '', style_header6) ws.write(3, 2, '', style_header6) ws.write(3, 3, '', style_header6) ws.write(3, 4, '', style_header6) ws.write(3, 5, 'completed 15 year of', style_header6) ws.write(3, 6, 'worked in the', style_header6) ws.write(3, 7, 'in respect of', style_header6) ws.write(3, 8, 'under section 10', style_header6) ws.write(3, 9, 'Puja bonus or other customary', style_header7) ws.write(3, 10, 'Interim bonus', style_header7) ws.write(3, 11, 'Deduction on account of financial', style_header7) ws.write(3, 12, 'Total sum deducted', style_header7) ws.write(3, 13, '(Col.8 minus Col.12)', style_header6) ws.write(3, 14, 'paid', style_header6) ws.write(3, 15, 'paid', style_header6) ws.write(3, 16, 'impression', style_header6) ws.write(3, 17, '', style_header6) ws.write(3, 18, '', style_header7) ws.write(3, 19, '', style_header7) ws.write(3, 20, '', style_header7) ws.write(3, 21, '', style_header7) ws.write(3, 22, '', style_header7) ws.write(3, 23, '', style_header7) ws.write(3, 24, '', style_header7) ws.write(3, 25, '', style_header7) ws.write(3, 26, '', style_header7) ws.write(3, 27, '', style_header7) ws.write(3, 28, '', style_header7) ws.write(3, 29, '', style_header7) ws.write(3, 30, '', style_header7) ws.write(3, 31, '', style_header7) ws.write(3, 32, '', style_header7) ws.write(3, 33, '', style_header7) ws.write(3, 34, '', style_header7) ws.write(3, 35, '', style_header7) ws.write(3, 36, '', style_header7) ws.write(3, 37, '', style_header7) ws.write(3, 38, '', style_header7) ws.write(3, 39, '', style_header7) ws.write(3, 40, '', style_header7) ws.write(3, 41, '', style_header7) ws.write(3, 42, '', style_header7) ws.write(3, 43, '', style_header7) ws.write(3, 44, '', style_header7) ws.row(4).height = 400 ws.write(4, 0, '', style_header6) ws.write(4, 1, '', style_header6) ws.write(4, 2, '', style_header6) ws.write(4, 3, '', style_header6) ws.write(4, 4, '', style_header6) ws.write(4, 5, 'age at the beginning', style_header6) ws.write(4, 6, 'Establishment', style_header6) ws.write(4, 7, 'the accounting year', style_header6) ws.write(4, 8, 'or section 11', style_header6) ws.write(4, 9, 'bonus paid during', style_header6) ws.write(4, 10, 'or bonus paid in ', style_header6) ws.write(4, 11, 'loss if any caused by', style_header6) ws.write(4, 12, '(Col.9,10 and 11)', style_header6) ws.write(4, 13, '', style_header6) ws.write(4, 14, '', style_header6) ws.write(4, 15, '', style_header6) ws.write(4, 16, 'of the employee', style_header6) ws.write(4, 17, '', style_header6) ws.write(4, 18, '', style_header6) ws.write(4, 19, '', style_header6) ws.write(4, 20, '', style_header6) ws.write(4, 21, '', style_header6) ws.write(4, 22, '', style_header6) ws.write(4, 23, '', style_header6) ws.write(4, 24, '', style_header6) ws.write(4, 25, '', style_header6) ws.write(4, 26, '', style_header6) ws.write(4, 27, '', style_header6) ws.write(4, 28, '', style_header6) ws.write(4, 29, '', style_header6) ws.write(4, 30, '', style_header6) ws.write(4, 31, '', style_header6) ws.write(4, 32, '', style_header6) ws.write(4, 33, '', style_header6) ws.write(4, 34, '', style_header6) ws.write(4, 35, '', style_header6) ws.write(4, 36, '', style_header6) ws.write(4, 37, '', style_header6) ws.write(4, 38, '', style_header6) ws.write(4, 39, '', style_header6) ws.write(4, 40, '', style_header6) ws.write(4, 41, '', style_header6) ws.write(4, 42, '', style_header6) ws.write(4, 43, '', style_header6) ws.write(4, 44, '', style_header6) ws.row(5).height = 400 ws.write(5, 0, '', style_header6) ws.write(5, 1, '', style_header6) ws.write(5, 2, '', style_header6) ws.write(5, 3, '', style_header6) ws.write(5, 4, '', style_header6) ws.write(5, 5, 'of the accounting year', style_header6) ws.write(5, 6, '', style_header6) ws.write(5, 7, '', style_header6) ws.write(5, 8, 'as the case may be', style_header6) ws.write(5, 9, 'the accounting year', style_header6) ws.write(5, 10, 'advance', style_header6) ws.write(5, 11, 'misconduct of the employee', style_header6) ws.write(5, 12, '', style_header6) ws.write(5, 13, '', style_header6) ws.write(5, 14, '', style_header6) ws.write(5, 15, '', style_header6) ws.write(5, 16, '', style_header6) ws.write(5, 17, '', style_header6) ws.write(5, 18, 'DAYS', style_header6) ws.write(5, 19, 'SALARY', style_header6) ws.write(5, 20, 'DAYS', style_header6) ws.write(5, 21, 'SALARY', style_header6) ws.write(5, 22, 'DAYS', style_header6) ws.write(5, 23, 'SALARY', style_header6) ws.write(5, 24, 'DAYS', style_header6) ws.write(5, 25, 'SALARY', style_header6) ws.write(5, 26, 'DAYS', style_header6) ws.write(5, 27, 'SALARY', style_header6) ws.write(5, 28, 'DAYS', style_header6) ws.write(5, 29, 'SALARY', style_header6) ws.write(5, 30, 'DAYS', style_header6) ws.write(5, 31, 'SALARY', style_header6) ws.write(5, 32, 'DAYS', style_header6) ws.write(5, 33, 'SALARY', style_header6) ws.write(5, 34, 'DAYS', style_header6) ws.write(5, 35, 'SALARY', style_header6) ws.write(5, 36, 'DAYS', style_header6) ws.write(5, 37, 'SALARY', style_header6) ws.write(5, 38, 'DAYS', style_header6) ws.write(5, 39, 'SALARY', style_header6) ws.write(5, 40, 'DAYS', style_header6) ws.write(5, 41, 'SALARY', style_header6) ws.write(5, 42, 'DAYS', style_header6) ws.write(5, 43, 'SALARY', style_header6) ws.write(5, 44, '', style_header6) if obj.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', True), ('type', '=', 'Employee')]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', False), ('type', '=', 'Employee')]) list_ids = list_ids + list_ids1 elif obj.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', obj.employee_id.id), ('active', '=', True), ('type', '=', 'Employee')]) else: raise osv.except_osv( _('Warning !'), _("Please Select Atleast Company Or Employee.")) if len(list_ids) == 1: query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\ "where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\ "where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) columnno = 6 no = 6 for val in temp: father_name = '' if val[4]: father_qry = "select name from family where relation='Father' and employee_id='" + str( val[4]) + "' " cr.execute(father_qry) father_temp = cr.fetchall() if father_temp: father_name = father_temp[0][0] else: father_name = ' ' ws.row(no).height = 500 doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y") ws.write(columnno, 0, val[0], style_header5) ws.write(columnno, 1, val[1], style_header5) ws.write(columnno, 2, doj, style_header5) ws.write(columnno, 3, father_name, style_header5) ws.write(columnno, 4, val[3], style_header5) ws.write(columnno, 5, 'Yes', style_header5) ws.write(columnno, 6, '', style_header5) ws.write(columnno, 7, '', style_header5) ws.write(columnno, 8, '', style_header5) ws.write(columnno, 9, '', style_header5) ws.write(columnno, 10, '', style_header5) ws.write(columnno, 11, '', style_header5) ws.write(columnno, 12, '', style_header5) ws.write(columnno, 13, '', style_header5) ws.write(columnno, 14, '', style_header5) ws.write(columnno, 15, '', style_header5) ws.write(columnno, 16, '', style_header5) ws.write(columnno, 17, '', style_header5) ws.write(columnno, 18, val[5], style_header5) ws.write(columnno, 19, val[19], style_header5) ws.write(columnno, 20, val[6], style_header5) ws.write(columnno, 21, val[20], style_header5) ws.write(columnno, 22, val[7], style_header5) ws.write(columnno, 23, val[21], style_header5) ws.write(columnno, 24, val[8], style_header5) ws.write(columnno, 25, val[22], style_header5) ws.write(columnno, 26, val[9], style_header5) ws.write(columnno, 27, val[23], style_header5) ws.write(columnno, 28, val[10], style_header5) ws.write(columnno, 29, val[24], style_header5) ws.write(columnno, 30, val[11], style_header5) ws.write(columnno, 31, val[25], style_header5) ws.write(columnno, 32, val[12], style_header5) ws.write(columnno, 33, val[26], style_header5) ws.write(columnno, 34, val[13], style_header5) ws.write(columnno, 35, val[27], style_header5) ws.write(columnno, 36, val[14], style_header5) ws.write(columnno, 37, val[28], style_header5) ws.write(columnno, 38, val[15], style_header5) ws.write(columnno, 39, val[29], style_header5) ws.write(columnno, 40, val[16], style_header5) ws.write(columnno, 41, val[30], style_header5) ws.write(columnno, 42, val[17], style_header5) ws.write(columnno, 43, val[31], style_header5) ws.write(columnno, 44, val[18], style_header5) columnno += 1 no += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Payment Bonus.xls' }, context=context)
def report_get(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'Employee Name',style_header) ws.col(2).width = 8000 ws.write(0,3,'Salary Amount',style_header) ws.col(3).width = 5000 ws.write(0,4,month_name,style_header) ws.col(4).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource\ as res on (emp.resource_id=res.id) where emp.department_id = \ '"+str(this.dept_id.id)+"' and emp.department_id is not null and \ res.active=True order by emp.department_id") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource \ as res on (emp.resource_id=res.id) where res.active=True order by emp.department_id") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=0 dept_dict = {} grand = total = pay_total = pay_grand = budget = 0.0 pay_data = False flag = True for each in emp_obj.browse(cr, uid, emp_ids): pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id)]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) i+=1 if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: salary = 0.0 if flag: ws.write(i,0, 'Allocated Budget',style_header) ws.write(i,1, 0.0,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, total,style_header) if pay_total: ws.write(i,4,pay_total,style_header) else: ws.write(i,4, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,5, diff,style_header1) flag = False i += 2 total = pay_total = budget = 0.0 name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,0, 'X Department') ws.write(i,1,'X Reporting Officer') ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 else: dept_dict[str(each.department_id.id)] = '' if i != 1: ws.write(i,0, 'Allocated Budget',style_header) if budget: ws.write(i,1, budget,style_header) else: budget = 0.0 ws.write(i,1,budget,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, total,style_header) if pay_total: ws.write(i,4,pay_total,style_header) else: ws.write(i,4, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,5, diff,style_header1) i += 2 total = pay_total = budget = 0.0 if each.department_id: salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+1,2, 'Total',style_header) ws.write(i+1,3, total,style_header) ws.write(i+3,2, 'Grand Total',style_header) ws.write(i+3,3, grand,style_header) ws.write(i+1,4, pay_total,style_header) ws.write(i+3,4, pay_grand,style_header) diff_pay = pay_total - total diff_grand = pay_grand - grand ws.write(i+1,5, diff_pay,style_header1) ws.write(i+3,5, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def performance_register_report(self, cr, uid, ids, data, context=None): obj = self.browse(cr, uid, ids) emp_obj = self.pool.get('hr.employee') f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('Contractor Payment Bonus') total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x00 borders1.right = 0x00 borders1.top = 0x00 borders1.bottom = 0x00 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 300 fnt2.bold = True align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x00 borders2.right = 0x00 borders2.top = 0x00 borders2.bottom = 0x00 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x1F style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 300 fnt3.bold = True align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x00 borders3.right = 0x00 borders3.top = 0x00 borders3.bottom = 0x00 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x1F style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 200 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() align_content5.horz = Alignment.HORZ_CENTER borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.horz = Alignment.HORZ_CENTER align5.vert = Alignment.VERT_CENTER pattern5 = Pattern() # pattern5.pattern = Pattern.SOLID_PATTERN # pattern5.pattern_fore_colour = 0x1F style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 if obj.partner_id: get_name = obj.partner_id.name elif obj.company_id: get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip else: get_name = obj.employee_id.partner_id.name ws.row(0).height = 500 ws.write_merge(0, 0, 0, 19, get_name, style_header1) date1 = datetime.strptime(obj.from_date, "%Y-%m-%d").timetuple().tm_year date2 = datetime.strptime(obj.till_date, "%Y-%m-%d").timetuple().tm_year if date1 == date2: d_name = 'BONUS' + ' - ' + str(date1) else: d_name = 'BONUS' + ' ' + str(date1) + ' ' + '-' + ' ' + str(date2) ws.row(1).height = 500 ws.write_merge(1, 1, 0, 19, d_name, style_header2) ws.col(0).width = 5000 ws.col(1).width = 7500 ws.col(2).width = 5000 ws.col(3).width = 3000 ws.col(4).width = 3000 ws.col(5).width = 3000 ws.col(6).width = 3000 ws.col(7).width = 3000 ws.col(8).width = 3000 ws.col(9).width = 3000 ws.col(10).width = 3000 ws.col(11).width = 3000 ws.col(12).width = 3000 ws.col(13).width = 3000 ws.col(14).width = 3000 ws.col(15).width = 3000 ws.col(16).width = 3000 ws.col(17).width = 3000 ws.col(18).width = 3000 ws.col(19).width = 3000 ws.col(20).width = 3000 ws.col(21).width = 3000 ws.col(22).width = 3000 ws.col(23).width = 3000 ws.col(24).width = 3000 ws.col(25).width = 3000 ws.col(26).width = 3000 ws.col(27).width = 3000 ws.col(28).width = 3000 ws.col(29).width = 4000 ws.row(2).height = 400 ws.write(2, 0, 'EMP. CODE', style_header) ws.write(2, 1, 'NAME', style_header) ws.write(2, 2, 'JOINING DATE', style_header) ws.write_merge(2, 2, 3, 4, 'APRIL', style_header) ws.write_merge(2, 2, 5, 6, 'MAY', style_header) ws.write_merge(2, 2, 7, 8, 'JUNE', style_header) ws.write_merge(2, 2, 9, 10, 'JULY', style_header) ws.write_merge(2, 2, 11, 12, 'AUGUST', style_header) ws.write_merge(2, 2, 13, 14, 'SEPTEMBER', style_header) ws.write_merge(2, 2, 15, 16, 'OCTOBER', style_header) ws.write_merge(2, 2, 17, 18, 'NOVEMBER', style_header) ws.write_merge(2, 2, 19, 20, 'DECEMBER', style_header) ws.write_merge(2, 2, 21, 22, 'JANUARY', style_header) ws.write_merge(2, 2, 23, 24, 'FEBRUARY', style_header) ws.write_merge(2, 2, 25, 26, 'MARCH', style_header) ws.write_merge(2, 2, 27, 28, 'TOTAL', style_header) ws.write(2, 29, 'BONUS', style_header) ws.row(3).height = 400 ws.write(3, 0, '', style_header) ws.write(3, 1, '', style_header) ws.write(3, 2, '', style_header) ws.write(3, 3, 'DAYS', style_header) ws.write(3, 4, 'SALARY', style_header) ws.write(3, 5, 'DAYS', style_header) ws.write(3, 6, 'SALARY', style_header) ws.write(3, 7, 'DAYS', style_header) ws.write(3, 8, 'SALARY', style_header) ws.write(3, 9, 'DAYS', style_header) ws.write(3, 10, 'SALARY', style_header) ws.write(3, 11, 'DAYS', style_header) ws.write(3, 12, 'SALARY', style_header) ws.write(3, 13, 'DAYS', style_header) ws.write(3, 14, 'SALARY', style_header) ws.write(3, 15, 'DAYS', style_header) ws.write(3, 16, 'SALARY', style_header) ws.write(3, 17, 'DAYS', style_header) ws.write(3, 18, 'SALARY', style_header) ws.write(3, 19, 'DAYS', style_header) ws.write(3, 20, 'SALARY', style_header) ws.write(3, 21, 'DAYS', style_header) ws.write(3, 22, 'SALARY', style_header) ws.write(3, 23, 'DAYS', style_header) ws.write(3, 24, 'SALARY', style_header) ws.write(3, 25, 'DAYS', style_header) ws.write(3, 26, 'SALARY', style_header) ws.write(3, 27, 'DAYS', style_header) ws.write(3, 28, 'SALARY', style_header) ws.write(3, 29, '', style_header) if obj.partner_id: list_ids = emp_obj.search(cr, uid, [('partner_id', '=', obj.partner_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) list_ids1 = emp_obj.search(cr, uid, [('partner_id', '=', obj.partner_id.id), ('active', '=', False), ('type', '=', 'Contractor')]) list_ids = list_ids + list_ids1 elif obj.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', obj.employee_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) elif obj.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', False), ('type', '=', 'Contractor')]) list_ids = list_ids + list_ids1 else: raise osv.except_osv( _('Warning !'), _("Please Select Atleast Company Or Employee.")) if len(list_ids) == 1: query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\ "where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\ "where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) columnno = 4 for val in temp: doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y") ws.write(columnno, 0, val[0], style_header5) ws.write(columnno, 1, val[1], style_header5) ws.write(columnno, 2, doj, style_header5) ws.write(columnno, 3, val[3], style_header5) ws.write(columnno, 4, val[17], style_header5) ws.write(columnno, 5, val[4], style_header5) ws.write(columnno, 6, val[18], style_header5) ws.write(columnno, 7, val[5], style_header5) ws.write(columnno, 8, val[19], style_header5) ws.write(columnno, 9, val[6], style_header5) ws.write(columnno, 10, val[20], style_header5) ws.write(columnno, 11, val[7], style_header5) ws.write(columnno, 12, val[21], style_header5) ws.write(columnno, 13, val[8], style_header5) ws.write(columnno, 14, val[22], style_header5) ws.write(columnno, 15, val[9], style_header5) ws.write(columnno, 16, val[23], style_header5) ws.write(columnno, 17, val[10], style_header5) ws.write(columnno, 18, val[24], style_header5) ws.write(columnno, 19, val[11], style_header5) ws.write(columnno, 20, val[25], style_header5) ws.write(columnno, 21, val[12], style_header5) ws.write(columnno, 22, val[26], style_header5) ws.write(columnno, 23, val[13], style_header5) ws.write(columnno, 24, val[27], style_header5) ws.write(columnno, 25, val[14], style_header5) ws.write(columnno, 26, val[28], style_header5) ws.write(columnno, 27, val[15], style_header5) ws.write(columnno, 28, val[29], style_header5) ws.write(columnno, 29, val[16], style_header5) columnno += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Contractor Payment Bonus.xls' }, context=context)
def salary_payment_report(self,cr,uid,ids,context=None): #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.size=16 fnt.style= 'Regular' #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.size=16 content_fnt.style= 'Regular' align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x01 borders.right = 0x01 borders.top = 0x01 borders.bottom = 0x01 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt3 = Font() fnt3.name = 'Arial' fnt3.size=16 fnt3.style= 'Regular' #Define the font attributes for header content_fnt3 = Font() content_fnt3.name ='Arial' content_fnt3.size=16 content_fnt3.style= 'Regular' align_content3 = Alignment() align_content3.horz= Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x01 borders3.right = 0x01 borders3.top = 0x01 borders3.bottom = 0x01 #The text should be centrally aligned align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern3 = Pattern() #apply the above settings to the row(0) header style_header3= XFStyle() style_header3.font= fnt3 style_header3.pattern= pattern3 style_header3.borders = borders3 style_header3.alignment=align3 #Define the font attributes for header fnt4 = Font() fnt4.name = 'Arial' #Define the font attributes for header content_fnt4 = Font() content_fnt4.name ='Arial' align_content4 = Alignment() align_content4.horz= Alignment.HORZ_LEFT borders4 = Borders() borders4.left = 0x01 borders4.right = 0x01 borders4.top = 0x01 borders4.bottom = 0x01 #The text should be centrally aligned align4 = Alignment() align4.horz = Alignment.HORZ_LEFT align4.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern4 = Pattern() #apply the above settings to the row(0) header style_header4= XFStyle() style_header4.font= fnt4 style_header4.pattern= pattern4 style_header4.borders = borders4 style_header4.alignment=align4 #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.size=10 fnt1.Style= 'Regular' #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.size=10 content_fnt1.Style= 'Regular' align_content1 = Alignment() align_content1.horz= Alignment.HORZ_RIGHT borders1 = Borders() borders1.left = 0x01 borders1.right = 0x01 borders1.top = 0x01 borders1.bottom = 0x01 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_RIGHT align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 #Define the font attributes for header fnt2 = Font() fnt2.name = 'Arial' fnt2.size=16 fnt2.Style= 'Regular' #Define the font attributes for header content_fnt2 = Font() content_fnt2.name ='Arial' content_fnt2.size=16 content_fnt2.Style= 'Regular' align_content2 = Alignment() align_content2.horz= Alignment.HORZ_RIGHT borders2 = Borders() borders2.left = 0x01 borders2.right = 0x01 borders2.top = 0x01 borders2.bottom = 0x01 #The text should be centrally aligned align2 = Alignment() align2.horz = Alignment.HORZ_RIGHT align2.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern2 = Pattern() #apply the above settings to the row(0) header style_header2= XFStyle() style_header2.font= fnt2 style_header2.pattern= pattern style_header2.borders = borders2 style_header2.alignment=align2 wb = Workbook() ws = wb.add_sheet('Salary Report') this=self.browse(cr,uid,ids[0],context=context) ws.row(0).height=300 ws.row(1).height=300 ws.row(2).height=300 # ws.col(0).width = 5000 # ws.col(0).width = 5000 # ws.col(0).width = 5000 # ws.col(0).width = 5000 ws.write_merge(0,0,0,5, 'SALARY REPORT',style_header) ws.write_merge(1,1,0,5, ('DEPARTMENT : ',this.department_id.name),style_header) ws.write_merge(2,2,0,1, 'Month',style_header) ws.write_merge(2,2,2,3, 'Salary Amount',style_header) ws.write_merge(2,2,4,5, 'Remark',style_header) l=[] lst=[] for val in this.month_ids: i = 3 query="""select hy.name,spl.month, sum(spl.total_amount) from salary_payment_line as spl left join holiday_year as hy on spl.year_id = hy.id where month = '"""+str(val.month)+"""' and year_id = '"""+str(val.year_id.id)+"""' and curr_department = '"""+str(this.department_id.id)+"""' and salary_type='Salary' group by spl.month, hy.name""" cr.execute(query) temp = cr.fetchall() for year,month, total_amount in temp: t=() mon = int(month) t = (year,mon, total_amount) lst.append(t) total = 0.0 lst.sort() for year,month, total_amount in lst: if month == 1: month1 = 'January' elif month == 2: month1 = 'February' elif month == 3: month1 = 'March' elif month == 4: month1 = 'April' elif month == 5: month1 = 'May' elif month == 6: month1 = 'June' elif month == 7: month1 = 'July' elif month == 8: month1 = 'August' elif month == 9: month1 = 'September' elif month == 10: month1 = 'October' elif month == 11: month1 = 'November' else: month1 = 'December' total += total_amount ws.write_merge(i,i,0,1, (month1,' ',year),style_header4) ws.write_merge(i,i,2,3, round(total_amount,2),style_header1) ws.write_merge(i,i,4,5, ' ',style_header3) i += 1 ws.write_merge(i,i,0,1, 'Total Amount',style_header2) ws.write_merge(i,i,2,3, round(total,2),style_header2) ws.write_merge(i,i,4,5, ' ',style_header) if len(lst) < 1: raise osv.except_osv(_('Warning!'),_('No Record found!')) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) sal_report = self.write(cr, uid, ids, {'export_data':out, 'filename':'Salary Report.xls'}, context=context) return sal_report
def exportReport1ToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1'); sheet1.col(1).width = 256*20; sheet1.col(2).width = 256*80; sheet1.col(3).width = 256*10; sheet1.col(4).width = 256*10; sheet1.col(5).width = 256*20; sheet1.col(6).width = 256*20; borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 wrap = Alignment(); wrap.wrap = 1; wrap.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True styleWrap = XFStyle(); styleWrap.alignment = wrap; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; styleDate.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = wrap; if( objectProject): i=0; row1 = sheet1.row(i) ; row1.write(0, ('ลำดับที่').decode('UTF8') ,styleHead); #sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ); row1.write(1, ('เลขความเสี่ยง').decode('UTF8'),styleHead ); row1.write(2, ('อุบัติการณ์/ภาวะไม่พึงประสงค์').decode('UTF8'),styleHead); row1.write(3, ('วันที่รายงาน').decode('UTF8'),styleHead ); row1.write(4, ('ความรุนแรง').decode('UTF8'),styleHead ); row1.write(5, ('ด้าน/โปรแกรม').decode('UTF8') ,styleHead); row1.write(6, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead); i=i+1; for value in objectProject: row1 = sheet1.row(i) ; row1.write(0, value.get('row') ,styleRowDetail ); row1.write(1, str(value.get('risk_id')).decode('UTF8'),styleRowDetail ); row1.write(2, value.get('detail').decode('UTF8'),StyleRowDetailWrap ); row1.write(3, value.get('report_date') ,styleDate ); row1.write(4, value.get('level').decode('UTF8') ,styleRowDetail ); row1.write(5, value.get('pro').decode('UTF8') ,styleRowDetail ); row1.write(6, value.get('reporter').decode('UTF8') ,styleRowDetail ); i=i+1; row2 = sheet1.row(i) ; row2.write(2, ('รายละเอียด').decode('UTF8'),styleHead); row2.write(3, ('หน่วยที่ตอบ').decode('UTF8'),styleHead ); row2.write(4, ('ระยะเวลาตอบ').decode('UTF8'),styleHead ); i=i+1; for resp in value.get('responsible'): row2 = sheet1.row(i) ; row2.write(2, str(resp.get('detail')).decode('UTF8'),StyleRowDetailWrap); row2.write(3, str(resp.get('service_name')).decode('UTF8'),styleRowDetail ); row2.write(4, str(resp.get('report_date')).decode('UTF8'),styleRowDetail ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simpleReport1.xls'); book.save(dirTempFile); return dirTempFile;
def exportToXls(self): # opening file dialog fileName = QFileDialog.getSaveFileName(self, "Save as", RES, "Microsoft Excel Spreadsheet (*.xls)") if fileName.count() > 0: try: COLUMN_WIDTH = 3000 alignment = Alignment() alignment.horizontal = Alignment.HORZ_CENTER alignment.vertical = Alignment.VERT_CENTER borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.alignment = alignment style.borders = borders font = Font() font.bold = True headerStyle = XFStyle() headerStyle.font = font separate = Borders() separate.left = Borders.THIN separate.right = Borders.DOUBLE separate.top = Borders.THIN separate.bottom = Borders.THIN separateStyle = XFStyle() separateStyle.borders = separate book = Workbook(encoding="utf-8") # modelling data if self.exportStepByStep.isChecked(): dec_sheet = book.add_sheet("Data decomposition") # decomposition data if self.exportData.isChecked(): # initial data column = 0 row = 0 dec_sheet.write(row, column, "Time series", headerStyle) dec_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.parentWidget().currentDataSet[0]: dec_sheet.write(row, column, item, separateStyle) row += 1 # decomposition for lvl in self.parentWidget().wCoefficients: row = 0 column += 1 dec_sheet.write(row, column, "Level" + str(column - 1), headerStyle) dec_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in lvl: dec_sheet.write(row, column, item, style) row += 1 # decomposition graphs if self.exportGraph.isChecked(): pass levels_sheet = book.add_sheet("Multiscale forecast") # levels data if self.exportData.isChecked(): column = 0 for lvl in self.parentWidget().processedWCoeffs: row = 0 levels_sheet.write(row, column, "Level" + str(column), headerStyle) levels_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in lvl: levels_sheet.write(row, column, float(item), style) row += 1 column += 1 if self.exportForecast.isChecked(): result_sheet = book.add_sheet("Results") if self.exportData.isChecked(): # initial column = 0 row = 0 result_sheet.write(row, column, "Initial data", headerStyle) result_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.parentWidget().currentDataSet[0]: result_sheet.write(row, column, item, separateStyle) row += 1 # forecast row = 0 column += 1 result_sheet.write(row, column, "Forecast", headerStyle) result_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.parentWidget().resultingForecast: result_sheet.write(row, column, item, style) row += 1 if self.exportGraph.isChecked(): row = 0 column = 2 self.parentWidget().resultingGraph.saveFigure("forecast", format="bmp") result_sheet.insert_bitmap(RES + TEMP + "forecast.bmp", row, column) # saving xls try: book.save(unicode(fileName)) self.parentWidget().messageInfo.showInfo("Saved as " + unicode(fileName)) except Exception: self.parentWidget().messageInfo.showInfo("Could not save as " + unicode(fileName), True) except Exception, e: self.parentWidget().messageInfo.showInfo("Not enough data.", True)
def exportReport5ToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1'); sheet1.col(1).width = 256*80; sheet1.col(2).width = 256*10; sheet1.col(3).width = 256*20; borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 wrap = Alignment(); wrap.wrap = 1; wrap.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True styleWrap = XFStyle(); styleWrap.alignment = wrap; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; styleDate.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = wrap; if( objectProject): i=0; row1 = sheet1.row(i) ; row1.write(0, ('risk id').decode('UTF8'),styleHead ); #sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ); row1.write(1, ('รายละเอียด').decode('UTF8'),styleHead); row1.write(2, ('วันที่รายงาน').decode('UTF8'),styleHead ); row1.write(3, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead); i=i+1; for value in objectProject: row1 = sheet1.row(i) ; row1.write(0, value.get('risk_management_id') ,styleRowDetail ); row1.write(1, value.get('risk_detail').decode('UTF8'),StyleRowDetailWrap ); #sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap ); row1.write(2, value.get('report_date') ,styleDate ); row1.write(3, value.get('report').decode('UTF8') ,styleRowDetail ); i=i+1; for sub in value.get('response') : row1 = sheet1.row(i) ; row1.write(0," " ); text = "(" + sub.get('risk_team').decode('UTF8') + " ) " + sub.get('result').decode('UTF8'); row1.write(1, text ,StyleRowDetailWrap ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simpleReport5.xls'); book.save(dirTempFile); return dirTempFile;
cell_bold = settings.EXPORT_EXCEL_CELL_BOLD titleFont.bold = title_bold # Setting title font to bold headerFont.bold = header_bold # Setting column header font to bold cellFont.bold = cell_bold # Setting cell font to bold titlesize = 240 # 12*20 = 240 Font Size fontsize = 200# 10*20 = 200 Font Size if hasattr(settings, 'EXPORT_EXCEL_TITLE_SIZE'): titlesize = settings.EXPORT_EXCEL_TITLE_SIZE if hasattr(settings, 'EXPORT_EXCEL_FONT_SIZE'): fontsize = settings.EXPORT_EXCEL_FONT_SIZE titleFont.height = titlesize headerFont.height = fontsize cellFont.height = fontsize brdLeft = Borders() # Defining border which is around header brdLeft.left = 0x01 brdRight = Borders() # Defining border which is around header brdRight.right = 0x01 brdTop = Borders() # Defining border which is around header brdTop.top = 0x01 brdBottom = Borders() # Defining border which is around header brdBottom.bottom = 0x01 brdTopLeft = Borders() brdTopLeft.top = 0x01 brdTopLeft.left = 0x01
def exportToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1') sheet1.col(1).width = 256*20; sheet1.col(2).width = 256*80; sheet1.col(3).width = 256*10; sheet1.col(4).width = 256*20; default_book_style = book.default_style default_book_style.font.height = 20 * 36 # 36pt fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 algn1 = Alignment(); algn1.wrap = 1; #algn1.horz = Alignment.HORZ_CENTER #algn1.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP print "export"; if( objectProject): i=0; print "start" ; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; row1 = sheet1.row(i) ; row1.write(0, ('risk id').decode('UTF8'),styleHead ); sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead ); # row1.write(1, ('รายละเอียด').decode('UTF8')); row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead ); row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead ); i=i+1; style1 = XFStyle(); style1.alignment = algn1; #style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on') for value in objectProject: row1 = sheet1.row(i) ; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = algn1; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; row1.write(0, value.get('risk_management_id'),styleRowDetail ); #row1.write(1, value.get('risk_detail').decode('UTF8') , style1); sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap ); row1.write(3, value.get('report_date') ,styleDate); row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail ); i=i+1; row1 = sheet1.row(i) ; row1.write(0," " ); row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead ); sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead ); i=i+1; for sub in value.get('response') : row1 = sheet1.row(i) ; row1.write(0," " ); row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail ); sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simple.xls'); print dirTempFile; book.save(dirTempFile);
def account_budget(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if int(month) in [1,3,5,7,8,10,12]: join_date=year +'-'+month+'-'+'31' if int(month) in [4,6,9,11]: join_date=year +'-'+month+'-'+'30' if int(month) in [2]: if int(year) % 4 == 0: join_date=year +'-'+month+'-'+'29' else: join_date=year +'-'+month+'-'+'28' if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 #Define the font attributes for header fnt2 = Font() fnt2.name = 'Arial' fnt2.height= 275 #Define the font attributes for header content_fnt2 = Font() content_fnt2.name ='Arial' content_fnt2.height =220 align_content2 = Alignment() align_content2.horz= Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 #The text should be centrally aligned align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0x0A #apply the above settings to the row(0) header style_header2= XFStyle() style_header2.font= fnt2 style_header2.pattern= pattern2 style_header2.borders = borders2 style_header2.alignment=align2 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'RO of HOD',style_header) ws.col(2).width = 8000 ws.write(0,3,'Allocated Budget',style_header) ws.col(3).width = 8000 ws.write(0,4,'Salary Amount',style_header) ws.col(4).width = 5000 ws.write(0,5,month_name,style_header) ws.col(5).width = 8000 ws.write(0,6,'Difference',style_header) ws.col(6).width = 8000 ws.write(0,7,'No Of Emp',style_header) ws.col(7).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource " \ "as res on (emp.resource_id=res.id) left join hr_department as dept on " \ "(emp.department_id=dept.id) where emp.department_id = '"+str(this.dept_id.id)+"' and " \ "emp.department_id is not null and res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code, " \ "(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource " \ "as res on (emp.resource_id=res.id) left join hr_department as dept on (emp.department_id=dept.id) " \ "where res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code,(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=1 dept_dict = {} grand = total = pay_total = pay_grand = budget = total_budget = 0.0 pay_data = False flag = True count1=0.0 for each in emp_obj.browse(cr, uid, emp_ids): pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic total += salary grand += salary # if pay_data.employee_id.id == each.id: # print"ssssssssssssssssssss minssssssssss",count1 # count1=count1+1 if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 count1=count1+1 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: if flag: i -= 1 ws.write(i,3, total) ws.write(i,4, pay_total) if budget: diff = float(budget) - pay_total else: diff = pay_total if budget and diff > 0: ws.write(i,5, diff,style_header1) elif budget and diff < 0: ws.write(i,5, diff,style_header2) else: ws.write(i,5, diff,style_header2) ws.write(i,6, count1) count1=0.0 flag = False i += 1 # salary = 0.0 # name = '[' + str(each.sinid) +'] '+ str(each.name) # # ws.write(i,0, 'X Department') # ws.write(i,1,'X Reporting Officer') # ws.write(i,2, name) # # if each.daily: # salary = each.salary * working_day # else: # salary = each.salary # ws.write(i,3, salary) # if pay_data and pay_data.employee_id.id == each.id: # ws.write(i,4,pay_data.total_amount or 0.0) # else: # ws.write(i,4, 0.0) else: dept_dict[str(each.department_id.id)] = '' if i != 1: i -= 1 ws.write(i,4, total) ws.write(i,5, pay_total) if budget: diff = float(budget) - pay_total else: diff = pay_total if budget and diff > 0: ws.write(i,6, diff,style_header1) elif budget and diff < 0: ws.write(i,6, diff,style_header2) else: ws.write(i,6, diff,style_header2) ws.write(i,7, count1) count1=0.0 i += 1 if each.department_id: total = pay_total = budget = 0.0 salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') ws.write(i,3, budget) total_budget += budget if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic total += salary grand += salary # if pay_data.employee_id.id == each.id: # print"saaaaaaaaaaaaaallllllllllllllllllllllllllll",count1 # count1=count1+1 if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 count1=count1+1 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+3,1, 'Grand Total',style_header) ws.write(i+3,3, total_budget,style_header) ws.write(i+3,4, grand,style_header) ws.write(i+3,5, pay_grand,style_header) diff_grand = total_budget - pay_grand ws.write(i+3,6, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def produce_summary(self, cr, uid, travel, context=None): number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]') total_fnt = Font() total_fnt.name = 'Calibri' total_fnt.bold = True total_fnt.height = 16 * 20 # font size 12 total_cell_l_style = XFStyle() total_cell_l_style.alignment = Column.title_aln total_cell_l_style.borders = Borders() total_cell_l_style.borders.left = Borders.THICK total_cell_l_style.borders.right = Borders.HAIR total_cell_l_style.borders.top = Borders.THICK total_cell_l_style.borders.bottom = Borders.THICK total_cell_l_style.pattern = Column.title_ptn total_cell_l_style.num_format_str = number_format total_cell_l_style.font = Column.obj_fnt total_cell_r_style = XFStyle() total_cell_r_style.alignment = Column.title_aln total_cell_r_style.borders = Borders() total_cell_r_style.borders.left = Borders.HAIR total_cell_r_style.borders.right = Borders.THICK total_cell_r_style.borders.top = Borders.THICK total_cell_r_style.borders.bottom = Borders.THICK total_cell_r_style.pattern = Column.title_ptn total_cell_r_style.num_format_str = number_format total_cell_r_style.font = Column.obj_fnt sub_total_cell_label = Cell(_(u'SOUS-TOTAL'), Column.title_fnt, Column.title_aln, total_cell_l_style.borders, Column.title_ptn) total_cell_label = Cell(_(u'TOTAL'), total_fnt, Column.title_aln, total_cell_r_style.borders, Column.title_ptn, number_format) journeys = [i for i in travel.journey_ids] w = Workbook() ws = w.add_sheet(_('Travel Summary')) ws.row(2).height = 0x0280 ws.row(3 + len(journeys)).height = 0x0140 ws.row(4 + len(journeys)).height = 0x0180 row = 0 row += 2 # Write headers for i, col in enumerate(self._excel_columns): ws.col(i).width = col.width ws.write(row, i, col.text, col.style) row += 1 for i, obj in enumerate(journeys): ws.write(row + i, 0, i + 1, self._excel_columns[0].obj_style) for j in xrange(1, len(self._excel_columns)): ws.write(row + i, j, self._excel_columns[j].func(obj), self._excel_columns[j].obj_style) row += len(journeys) rate_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('TICKET RATE')) - 1 cost_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('COSTS')) - 1 total_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('TOTAL')) - 1 # Sub total label ws.write_merge(row, row, 0, rate_index, sub_total_cell_label.text, sub_total_cell_label.style) # Sub totals ws.write( row, rate_index + 1, Formula("SUM(%s%d:%s%d)" % (chr(66 + rate_index), 4, chr(66 + rate_index), row)), total_cell_l_style) ws.write( row, rate_index + 2, Formula("SUM(%s%d:%s%d)" % (chr(66 + cost_index), 4, chr(66 + cost_index), row)), total_cell_r_style) total_top_underline_style = XFStyle() total_top_underline_style.borders = Borders() total_top_underline_style.borders.top = Borders.THICK total_top_underline_style.font = Column.obj_fnt # Draw a line above total to close box ws.write(row, total_index + 1, "", total_top_underline_style) row += 1 # Total label ws.write_merge(row, row, 0, rate_index, total_cell_label.text, total_cell_label.style) # Total ws.write_merge( row, row, rate_index + 1, total_index, Formula("%s%d+%s%d" % (chr(66 + rate_index), row, chr(66 + cost_index), row)), total_cell_label.style) return w
def detail_budget(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) if int(month) in [1,3,5,7,8,10,12]: join_date=year +'-'+month+'-'+'31' if int(month) in [4,6,9,11]: join_date=year +'-'+month+'-'+'30' if int(month) in [2]: if int(year) % 4 == 0: join_date=year +'-'+month+'-'+'29' else: join_date=year +'-'+month+'-'+'28' #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'RO of HOD',style_header) ws.col(2).width = 8000 ws.write(0,3,'Employee Name',style_header) ws.col(3).width = 9000 ws.write(0,4,'Designation',style_header) ws.col(4).width = 8000 ws.write(0,5,'Working Days',style_header) ws.col(5).width = 5000 ws.write(0,6,'Working Hours',style_header) ws.col(6).width = 5000 ws.write(0,7,'Working Month',style_header) ws.col(7).width = 5000 ws.write(0,8,'Salary Amount',style_header) ws.col(8).width = 5000 ws.write(0,9,month_name,style_header) ws.col(9).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource "\ "as res on (emp.resource_id=res.id) where emp.department_id = "\ "'"+str(this.dept_id.id)+"' and emp.department_id is not null and "\ "res.active=True and emp.joining_date <'"+str(join_date)+"' order by emp.department_id, (substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource "\ "as res on (emp.resource_id=res.id) where res.active=True and emp.joining_date < '"+str(join_date)+"' order by emp.department_id, "\ "(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=0 dept_dict = {} grand = total = pay_total = pay_grand = budget = 0.0 pay_data = False flag = True for each in emp_obj.browse(cr, uid, emp_ids): tolal_months = 0 if not each.joining_date: continue dt1 = datetime.strptime(each.joining_date,'%Y-%m-%d') dt2 = datetime.strptime(time.strftime(DEFAULT_SERVER_DATE_FORMAT),'%Y-%m-%d') start_month=dt1.month end_months=(dt2.year-dt1.year)*12 + dt2.month+1 dates=[datetime(year=yr, month=mn, day=1) for (yr, mn) in ( ((m - 1) / 12 + dt1.year, (m - 1) % 12 + 1) for m in range(start_month, end_months) )] for val in dates: tolal_months += 1 pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) i+=1 if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days or '0.0') ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: salary = 0.0 if flag: ws.write(i,0, 'Allocated Budget',style_header) ws.write(i,1, 0.0,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, '',style_header) ws.write(i,4, '',style_header) ws.write(i,5, '',style_header) ws.write(i,6, '',style_header) ws.write(i,7, '',style_header) ws.write(i,8, total,style_header) if pay_total: ws.write(i,9,pay_total,style_header) else: ws.write(i,9, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,10, diff,style_header1) flag = False i += 2 total = pay_total = budget = 0.0 name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,0, 'X Department') ws.write(i,1,'X Reporting Officer') ws.write(i,2,'X Reporting Officer') ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days or '0.0') ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 else: dept_dict[str(each.department_id.id)] = '' if i != 1: ws.write(i,0, 'Allocated Budget',style_header) if budget: ws.write(i,1, budget,style_header) else: budget = 0.0 ws.write(i,1,budget,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, '',style_header) ws.write(i,4, '',style_header) ws.write(i,5, '',style_header) ws.write(i,6, '',style_header) ws.write(i,7, '',style_header) ws.write(i,8, total,style_header) if pay_total: ws.write(i,9,pay_total,style_header) else: ws.write(i,9, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,10, diff,style_header1) i += 2 total = pay_total = budget = 0.0 if each.department_id: salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days) ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+1,7, 'Total',style_header) ws.write(i+1,8, total,style_header) ws.write(i+3,7, 'Grand Total',style_header) ws.write(i+3,8, grand,style_header) ws.write(i+1,9, pay_total,style_header) ws.write(i+3,9, pay_grand,style_header) diff_pay = pay_total - total diff_grand = pay_grand - grand ws.write(i+1,10, diff_pay,style_header1) ws.write(i+3,10, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def create_excel(work_excel, withdraw_info=[]): title_fnt = Font() title_fnt.height = 0x0140 title_fnt.name = u'宋体' title_fnt.bold = True brd = Borders() brd.bottom = 1 brd.top = 1 brd.left = 1 brd.right = 1 title_location = Alignment() title_location.horz = Alignment.HORZ_CENTER title_location.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.font = title_fnt title_style.alignment = title_location style = XFStyle() style.font.height = 0x00E0 style.font.name = u'宋体' style.font.bold = False style.alignment.horz = Alignment.HORZ_CENTER style.alignment.vert = Alignment.VERT_CENTER content_title_style = deepcopy(style) content_title_style.alignment.horz = Alignment.HORZ_LEFT content_title_style.font.height = 0x00E0 content_style = deepcopy(style) content_style.alignment.horz = Alignment.HORZ_LEFT center_content_style = deepcopy(style) center_content_style.alignment.horz = Alignment.HORZ_CENTER center_content_style.borders = brd style.borders = brd content_style.borders = brd merge_up_style = deepcopy(style) merge_up_style.borders.bottom = 0 content_up_style = deepcopy(merge_up_style) content_up_style.alignment.horz = Alignment.HORZ_CENTER merge_down_style = deepcopy(style) merge_down_style.borders.top = 0 content_down_style = deepcopy(merge_down_style) content_down_style.alignment.horz = Alignment.HORZ_RIGHT content_down_style.font.height = 0x00CA for i, info in enumerate(withdraw_info): width = i * 25 work_excel.write_merge(2 + width, 2 + width, 0, 16, u'商户提现付款申请单', title_style) work_excel.write_merge(3 + width, 3 + width, 1, 5, info['supplier_type'], content_title_style) work_excel.write_merge(3 + width, 3 + width, 6, 16, info['supplier'], content_title_style) work_excel.write(4 + width, 0, u'收款单位名称', style) work_excel.write_merge(4 + width, 4 + width, 1, 5, info['company'], content_style) work_excel.write(4 + width, 6, u'申请提现帐号', style) work_excel.write_merge(4 + width, 4 + width, 7, 16, info['applier'], center_content_style) work_excel.write(5 + width, 0, u'开户银行', style) work_excel.write_merge(5 + width, 5 + width, 1, 5, info['bank'], content_style) work_excel.write(5 + width, 6, u'申请提现日期', style) work_excel.write_merge(5 + width, 5 + width, 7, 16, info['apply_time'], center_content_style) work_excel.write(6 + width, 0, u'银行帐号', style) work_excel.write_merge(6 + width, 6 + width, 1, 5, info['account'], content_style) work_excel.write(6 + width, 6, u'合同账期', style) work_excel.write_merge(6 + width, 6 + width, 7, 16, info['payment_day_type'], center_content_style) work_excel.write(7 + width, 0, u'付款用途', style) work_excel.write_merge(7 + width, 7 + width, 1, 5, u'商户提现', content_style) work_excel.write(7 + width, 6, u'付款日期', style) work_excel.write_merge(7 + width, 7 + width, 7, 16, info['date'], center_content_style) units = [u'千', u'百', u'十', u'万', u'千', u'百', u'十', u'元', u'角', u'分'] digit = list(str(info['money'])) digit.remove('.') digit.reverse() for j, unit in enumerate(units): work_excel.write(8 + width, 7 + j, unit, content_style) for index, value in enumerate(digit): work_excel.write(9 + width, 16 - index, value, content_style) work_excel.write(9 + width, 16 - index - 1, '¥', content_style) work_excel.write_merge(10 + width, 11 + width, 0, 0, u'备注事项', style) work_excel.write_merge(10 + width, 10 + width, 1, 6, '', content_style) work_excel.write_merge(10 + width, 10 + width, 7, 16, '', style) work_excel.write_merge(11 + width, 11 + width, 1, 16, info['print_info'], content_down_style) work_excel.write(8 + width, 0, u'付款金额', merge_up_style) work_excel.write(9 + width, 0, u'人民币(大写)', merge_down_style) work_excel.write_merge(8 + width, 9 + width, 1, 6, info['china_money'], content_style) work_excel.write_merge(13 + width, 13 + width, 0, 2, u'部门主管:', content_title_style) work_excel.write_merge(13 + width, 13 + width, 3, 5, u'财务经理:', content_title_style) work_excel.write_merge(13 + width, 13 + width, 6, 16, u'公司总经理:', content_title_style) for row in range(2, 12): work_excel.row(row + width).height_mismatch = 1 work_excel.row(row + width).height = 478 work_excel.row(2 + width).height_mismatch = 1 work_excel.row(2 + width).height = 1000 work_excel.col(0).width = 3871 work_excel.col(1).width = 1771 work_excel.col(2).width = 1509 work_excel.col(3).width = 2348 work_excel.col(4).width = 840 work_excel.col(5).width = 3241 work_excel.col(6).width = 3441 for col in range(7, 17): work_excel.col(col).width = 709
def exportToXls(self): # opening file dialog fileName = QFileDialog.getSaveFileName(self, 'Save as', RES, 'Microsoft Excel Spreadsheet (*.xls)') if fileName.count() > 0: try: COLUMN_WIDTH = 3000 alignment = Alignment() alignment.horizontal = Alignment.HORZ_CENTER alignment.vertical = Alignment.VERT_CENTER borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN style = XFStyle() style.alignment = alignment style.borders = borders font = Font() font.bold = True headerStyle = XFStyle() headerStyle.font = font separate = Borders() separate.left = Borders.THIN separate.right = Borders.DOUBLE separate.top = Borders.THIN separate.bottom = Borders.THIN separateStyle = XFStyle() separateStyle.borders = separate book = Workbook(encoding='utf-8') # modelling data dec_sheet = book.add_sheet('Data decomposition') # decomposition data # initial data column = 0 row = 0 dec_sheet.write(row, column, 'Time series', headerStyle) dec_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.data[0]: dec_sheet.write(row, column, item, separateStyle) row += 1 # decomposition for lvl in self.wCoefficients: row = 0 column += 1 dec_sheet.write(row, column, 'Level' + str(column - 1), headerStyle) dec_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in lvl: dec_sheet.write(row, column, item, style) row += 1 # decomposition graphs pass levels_sheet = book.add_sheet('Multiscale forecast') # levels data column = 0 for lvl in self.forecast: row = 0 levels_sheet.write(row, column, 'Level' + str(column), headerStyle) levels_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in lvl[1]: levels_sheet.write(row, column, float(item), style) row += 1 column += 1 result_sheet = book.add_sheet('Results') # initial column = 0 row = 0 result_sheet.write(row, column, 'Initial data', headerStyle) result_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.data[0]: result_sheet.write(row, column, item, separateStyle) row += 1 # forecast row = 0 column += 1 result_sheet.write(row, column, 'Forecast', headerStyle) result_sheet.col(column).width = COLUMN_WIDTH row += 1 for item in self.inverseWT(): result_sheet.write(row, column, item, style) row += 1 row = 0 column = 2 self.updateGraph() self.plotResult.saveFigure('forecast', format='bmp') result_sheet.insert_bitmap(RES + TEMP + 'forecast.bmp', row, column) # saving xls try: book.save(unicode(fileName)) except Exception: pass except Exception, e: pass
def create_sheet(self): """ create a sheet in sheet set color, border, font,etc... """ workbook = xlwt.Workbook() borders = Borders() header_border = Borders() header_title_border = Borders() ware_or_loc_border = Borders() header_border.left, header_border.right, header_border.top, header_border.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THICK header_title_border.left, header_title_border.right, header_title_border.top, header_title_border.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THICK ware_or_loc_border.left, ware_or_loc_border.right, ware_or_loc_border.top, ware_or_loc_border.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THICK borders.left, borders.right, borders.top, borders.bottom = Borders.THIN, Borders.THIN, Borders.THIN, Borders.THIN header_bold = xlwt.easyxf( "font: bold on, height 250; pattern: pattern solid, fore_colour gray25;alignment: horizontal center ,vertical center") header_bold.borders = header_border body_style = xlwt.easyxf("font: height 200; alignment: horizontal center") style = xlwt.easyxf( "font: height 210, bold True; alignment: horizontal center,vertical center;borders: top medium,right medium,bottom medium,left medium") body_style.borders = borders header_title = xlwt.easyxf( "font: bold on, height 315; pattern: pattern solid, fore_colour ice_blue;alignment: horizontal center ,vertical center") header_title.borders = header_title_border xlwt.add_palette_colour("light_blue_21", 0x25) workbook.set_colour_RGB(0x25, 179, 255, 240) cell_string_style = xlwt.easyxf( "font: height 200, name Arial; align: horiz left, vert center; pattern: pattern solid, fore_colour light_blue_21; borders: top thin,right thin,bottom thin,left thin") xlwt.add_palette_colour("light_blue_21", 0x25) workbook.set_colour_RGB(0x25, 179, 255, 240) cell_number_style = xlwt.easyxf( "font: height 200, name Arial; align: horiz right, vert center; pattern: pattern solid, fore_colour light_blue_21; borders: top thin,right thin,bottom thin,left thin") return workbook, header_bold, body_style, style, header_title, cell_string_style, cell_number_style
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on... style.pattern = pattern # 将赋值好的模式参数导入Style sheet.write_merge(i, i, 0, 2, Line_data, style) #以合并单元格形式写入数据,即将数据写入以第1/2/3列合并德单元格内 for i in range(0x00, 0xff): # 设置单元格内字体样式 fnt = Font() # 创建一个文本格式,包括字体、字号和颜色样式特性 fnt.name = '微软雅黑' # 设置其字体为微软雅黑, 'SimSun' # 指定“宋体” fnt.colour_index = i # 设置其字体颜色 fnt.bold = True style.font = fnt #将赋值好的模式参数导入Style sheet.write_merge(i, i, 3, 5, Line_data, style) #以合并单元格形式写入数据,即将数据写入以第4/5/6列合并德单元格内 for i in range(0, 0x53): # 设置单元格下框线样式 borders = Borders() borders.left = i borders.right = i borders.top = i borders.bottom = i style.borders = borders #将赋值好的模式参数导入Style sheet.write_merge(i, i, 6, 8, Line_data, style) #以合并单元格形式写入数据,即将数据写入以第4/5/6列合并德单元格内 for i in range(6, 80): # 设置单元格下列宽样式 sheet.write(0, i, Line_data, style) sheet.col(i).width = 0x0d00 + i * 50 path_py = "/home/gswyhq/jian1.bmp" #读取插入图片以.py运行时路径,images和.py在同一目录下 sheet.insert_bitmap(path_py, 2, 9) #插入一个图片