def _getColor(self, color): colour_mark = None if type(color) == str and len(color) == 6: colour_mark = 'COR_' + color.upper() color = [ int('0x' + color[i:i + 2].upper(), 16) for i in range(0, len(color), 2) ] elif type(color) == list and len(color) == 3: tempList = list(filter(lambda x: type(x) == int, color)) if len(tempList) == 3: colour_mark = 'COR_' + "".join( [str(hex(r)).replace('0x', '').upper() for r in color]) else: self._getException(color) else: self._getException(color) if colour_mark: if colour_mark not in xlwt.Style.colour_map.keys(): for i in range(8, 63): if i not in xlwt.Style.colour_map.values(): xlwt.add_palette_colour(colour_mark, i) self.__wt.set_colour_RGB(i, 251, 228, 228) break return colour_mark else: self._getException(color)
def create_count_per_item(self, workbook, sheet_name, columns, queryset): ws = workbook.add_sheet(sheet_name) ws.col(0).width = 13 * 350 xlwt.add_palette_colour( "custom_blue_color", 0x21) # the second argument must be a number between 8 and 64 workbook.set_colour_RGB(0x21, 79, 129, 189) # Red — 79, Green — 129, Blue — 189 header_style = xlwt.easyxf( "pattern: pattern solid, fore_colour custom_blue_color; align: vert centre, horiz centre;" ) row_style = xlwt.easyxf("align: vert centre, horiz centre;") # set the headers on first row row_num = 0 for col_num in range(len(columns)): ws.write(row_num, col_num, columns[col_num], header_style) # Sheet body, remaining rows for row in queryset: row_num += 1 for col_num in range(len(row)): ws.write(row_num, col_num, row[col_num], row_style)
def add_style_lookup(x_pos, y_pos): """Add a new style to lookup table for one pixel if needed.""" palcolnum = pal_pixels[x_pos, y_pos] if palcolnum in already_used_colors: return # We need to define a style for every possible style-combination # In our case: 1) cell without border 2) cell with top border # 3) cell with left border 4) cell with top & left border already_used_colors.add(palcolnum) col_name = "custom_colour_" + str(palcolnum) xlwt.add_palette_colour(col_name, palcolnum) book.set_colour_RGB(palcolnum, *img_pixels[x_pos, y_pos]) def add_new_style(top, left): """Add a new style to lookup table with potential cell border.""" style = xlwt.easyxf('pattern: pattern solid, fore_colour ' + col_name) style.pattern.pattern_fore_colour = palcolnum style.borders.top = top style.borders.left = left offset_dict = {(0, 0): STYLE_OFFSET_BORDER_NONE, (1, 0): STYLE_OFFSET_BORDER_TOP, (0, 1): STYLE_OFFSET_BORDER_LEFT, (1, 1): STYLE_OFFSET_BORDER_TOPLEFT} style_lookup[palcolnum * STYLE_CNT + offset_dict[(top, left)]] = style add_new_style(0, 0) add_new_style(1, 0) add_new_style(0, 1) add_new_style(1, 1)
def __init__(self, sheetname=None, **kwds): if not sheetname: sheetname = "Sheet1" sheetname = utf8_encode(sheetname) self.book = xlwt.Workbook(encoding='utf-8') self.sheet = FitSheetWrapper(self.book.add_sheet(sheetname)) # добавить новый цвет в палитре и установить RGB xlwt.add_palette_colour("custom_colour", 0x21) self.book.set_colour_RGB(0x21, 244, 236, 197) # FFF4ECC5 # border FFCCC085 # Создаем новые стили # self.general_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour') # По умолчанию self.general_style = xlwt.XFStyle() self.general_style.num_format_str = 'general' # self.general_style.alignment.wrap = 1 alignment = xlwt.Alignment() # Create Alignment alignment.horz = xlwt.Alignment.HORZ_LEFT alignment.vert = xlwt.Alignment.VERT_TOP alignment.wrap = 1 self.general_style.alignment = alignment # Стиль шапки self.head_style = self.general_style
def ContactDeps(TheDep, TheDep1, TheDep2, PhoneNum, QQ, Name, Assignment, AssignmentDict, Iron, DepNum): xlwt.add_palette_colour('yellow', 0x22) color = xlwt.easyxf('pattern: pattern solid, fore_colour yellow') TheDep.write(0, 0, '手机') TheDep.col(0).width = 256 * 12 TheDep.write(0, 1, 'QQ') TheDep.col(1).width = 256 * 12 TheDep.write(0, 2, '姓名') TheDep.col(2).width = 256 * 10 TheDep.write(0, 3, '第几志愿') TheDep.write(0, 4, '服从调剂?') TheDep.col(4).width = 256 * 10 for i in range(0, len(TheDep1)): TheDep.write(i + 1, 0, PhoneNum[int(TheDep1[i])]) TheDep.write(i + 1, 1, QQ[int(TheDep1[i])]) if TheDep1[i] in Iron: TheDep.write(i + 1, 2, Name[int(TheDep1[i])], color) else: TheDep.write(i + 1, 2, Name[int(TheDep1[i])]) TheDep.write(i + 1, 3, 1) TheDep.write(i + 1, 4, AssignmentDict[Assignment[int(TheDep1[i])]]) for i in range(0, len(TheDep2)): TheDep.write(i + 1 + len(TheDep1), 0, PhoneNum[int(TheDep2[i])]) TheDep.write(i + 1 + len(TheDep1), 1, QQ[int(TheDep2[i])]) TheDep.write(i + 1 + len(TheDep1), 2, Name[int(TheDep2[i])]) TheDep.write(i + 1 + len(TheDep1), 3, 2) TheDep.write(i + 1 + len(TheDep1), 4, AssignmentDict[Assignment[int(TheDep2[i])]])
def init_grade_styles(cls): """ Adds the grade styles to cls.styles and as a xlwt identifier. This also notes all registered colors in cls.COLOR_MAPPINGS for the instances. This method should only be called once, right after the class definition. Instances need the styles, but they should only be registered once for xlwt. """ if len(cls.COLOR_MAPPINGS) > 0: # Method has already been called (probably in another import of this file). warnings.warn( "ResultsExporter.init_grade_styles has been called, " "although the styles have already been initialized. " "This can happen, if the file is imported / run multiple " "times in one application run.", ImportWarning) return grade_base_style = 'pattern: pattern solid, fore_colour {}; alignment: horiz centre; font: bold on; borders: left medium, right medium' for i in range(0, cls.NUM_GRADE_COLORS): grade = 1 + i * cls.STEP color = get_grade_color(grade) palette_index = cls.CUSTOM_COLOR_START + i style_name = cls.grade_to_style(grade) color_name = style_name + "_color" xlwt.add_palette_colour(color_name, palette_index) cls.COLOR_MAPPINGS[palette_index] = color cls.styles[style_name] = xlwt.easyxf( grade_base_style.format(color_name), num_format_str="0.0")
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
def write_tables_to_excel(filename,tables): book=xlwt.Workbook() sheet1 = book.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet style = xlwt.XFStyle() # 初始化样式 aligm=xlwt.Alignment() aligm.wrap=True style.alignment=aligm xlwt.add_palette_colour("custom_colour", 0x21) book.set_colour_RGB(0x21, 220, 215, 223) total_rows=0 for data in tables: for j in range(0,len(data)): row=data[j] if j%2==0: cell_style = xlwt.easyxf('pattern: pattern solid,fore_colour custom_colour; alignment: wrap 1;') else: cell_style = xlwt.easyxf('pattern: pattern solid,fore_colour white; alignment: wrap 1;') # style.pattern = pattern for k in range(0,len(row)): text=row[k] sheet1.write(j+total_rows,k,text,cell_style) sheet1.col(k).width=5000 # sheet1.col(k).width=500 # Tools.output_file('adjust_table.txt',o_text,new_line=(k==len(row)-1)) # print('%s' % str(cell_width),end=' '*4) total_rows+=len(data) book.save(filename) #保存文件
def write_to_excel(self, username=""): xlwt.add_palette_colour("custom_colour", 0x21) self.workbook.set_colour_RGB(0x21, 66, 236, 245) style = xlwt.easyxf( 'font: bold 1; font: height 600; pattern: pattern solid, fore_color custom_colour;' 'align: horiz center; borders: top thick, bottom thick, left thick, right thick;' ) self.housie_sheet.write_merge(self.ticket_counter, self.ticket_counter, 0, 8, 'Ticket - {}'.format(username), style) style = xlwt.easyxf( 'font: bold 1; font: height 800; pattern: pattern solid, fore_color custom_colour;' 'align: horiz center; borders: top thick, bottom thick, left thick, right thick;' ) for index, num in enumerate(self.first_row): self.housie_sheet.write(self.ticket_counter + 1, index, num, style) for index, num in enumerate(self.second_row): self.housie_sheet.write(self.ticket_counter + 2, index, num, style) for index, num in enumerate(self.third_row): self.housie_sheet.write(self.ticket_counter + 3, index, num, style) self.ticket_counter += 6
def reporte_excel(self): for w in self: libro = xlwt.Workbook() hoja = libro.add_sheet('reporte') xlwt.add_palette_colour("custom_colour", 0x21) libro.set_colour_RGB(0x21, 200, 200, 200) estilo = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour') hoja.write(0, 0, 'COSTO DE INVENTARIO EN METROS') y = 2 for producto in w.producto_ids: hoja.write(y, 0, producto.name) y += 1 hoja.write(y, 0, 'Lote') hoja.write(y, 1, 'Cantidad') hoja.write(y, 2, 'Cantidad en metros') hoja.write(y, 3, 'Costo por metro') hoja.write(y, 4, 'Costo total en metros') y += 1 quants = self.env['stock.quant'].search([('product_id', '=', producto.id)]) lineas = {} for q in quants: if q.lot_id: lote = q.lot_id.name else: lote = 'Indefinido' if lote not in lineas: lineas[lote] = {'cantidad': 0, 'cantidad_en_metros': 0, 'costo_por_metro': q.product_id.costo_por_metro, 'costo_total_en_metros': 0} lineas[lote]['cantidad'] += q.qty lineas[lote]['cantidad_en_metros'] += q.cantidad_en_metros lineas[lote]['costo_total_en_metros'] += q.costo_total_en_metros for lote in lineas: hoja.write(y, 0, lote) hoja.write(y, 1, lineas[lote]['cantidad']) hoja.write(y, 2, lineas[lote]['cantidad_en_metros']) hoja.write(y, 3, lineas[lote]['costo_por_metro']) hoja.write(y, 4, lineas[lote]['costo_total_en_metros']) y += 1 y += 3 f = StringIO.StringIO() libro.save(f) datos = base64.b64encode(f.getvalue()) self.write({'archivo':datos, 'name':'costo_inventario_metros.xls'}) return { 'view_type': 'form', 'view_mode': 'form', 'res_model': 'domex.asistente_reporte_costo_metros', 'res_id': self.id, 'view_id': False, 'type': 'ir.actions.act_window', 'target': 'new', }
def generaExcelSheet(partida,wb): #Llamamos a funcion para crear las listas y se lo asignamos a una list que tendrá 12 posiciones, 2 de equipo, 10 de jugadores listaDatos = listasExcel(partida) #Creamos el excel sheet1 = wb.add_sheet(partida.nombreMatch, cell_overwrite_ok=True) #estilos####### #color azul xlwt.add_palette_colour("custom_blue_color", 0x21) wb.set_colour_RGB(0x21, 192, 223, 255) #color rojo xlwt.add_palette_colour("custom_red_color", 0x22) wb.set_colour_RGB(0x22, 251, 204, 150) styleBold = xlwt.easyxf('font: bold 1') styleNormalBlue = xlwt.easyxf('align: vert centre, horiz center; pattern: pattern solid, fore_colour custom_blue_color') styleNormalRed = xlwt.easyxf('align: vert centre, horiz center; pattern: pattern solid, fore_colour custom_red_color') #/estilos###### try: sheet1.col(0).width = 256 *12 sheet1.col(1).width = 256 * 12 sheet1.col(2).width = 256 * 12 sheet1.col(3).width = 256 * 8 sheet1.col(4).width = 256 * 10 sheet1.col(5).width = 256 * 40 sheet1.col(6).width = 256 * 40 sheet1.col(7).width = 256 * 12 sheet1.col(8).width = 256 * 12 sheet1.col(9).width = 256 * 14 sheet1.col(10).width = 256 * 15 sheet1.col(11).width = 256 * 15 sheet1.col(12).width = 256 * 15 sheet1.col(13).width = 256 * 15 sheet1.col(14).width = 256 * 15 sheet1.col(15).width = 256 * 15 sheet1.col(16).width = 256 * 15 sheet1.col(17).width = 256 * 15 sheet1.col(18).width = 256 * 15 sheet1.col(19).width = 256 * 15 sheet1.col(20).width = 256 * 17 except ValueError: pass #ancho de columnas escribeLinea(sheet1, vars.titulosPartidaStats, 0, styleBold) escribeLinea(sheet1,listaDatos[0],1,styleNormalBlue) escribeLinea(sheet1, listaDatos[1], 2,styleNormalRed) escribeLinea(sheet1, vars.titulosPlayerStats, 4,styleBold) escribeLinea(sheet1, listaDatos[2], 5,styleNormalBlue) escribeLinea(sheet1, listaDatos[3], 6,styleNormalBlue) escribeLinea(sheet1, listaDatos[4], 7,styleNormalBlue) escribeLinea(sheet1, listaDatos[5], 8,styleNormalBlue) escribeLinea(sheet1, listaDatos[6], 9,styleNormalBlue) escribeLinea(sheet1, listaDatos[7], 11,styleNormalRed) escribeLinea(sheet1, listaDatos[8], 12,styleNormalRed) escribeLinea(sheet1, listaDatos[9], 13,styleNormalRed) escribeLinea(sheet1, listaDatos[10], 14,styleNormalRed) escribeLinea(sheet1, listaDatos[11], 15,styleNormalRed)
def setColor(sheetName="Accounts", rows=rows): book = xlwt.Workbook() sheet1 = book.add_sheet(sheetName) xlwt.add_palette_colour("custom_colour", 0x21) book.set_colour_RGB(0x21, 251, 228, 228) style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour') sheet1.write(0, 0, 'Some text', style) book.save('out.xls')
def apply(self): # assign name/key pair to xlwt, globally (may re-apply): for key, info in self.colors.items(): name, rgb = info xlwt.add_palette_colour(name, key) if self.context is not None: # assign RGB to color local to workbook context: self.context.book.set_colour_RGB(key, *rgb)
def write_to_xls(order_list): workbook = xlwt.Workbook(encoding="utf-8") # utf-8 인코딩 방식의 workbook 생성 workbook.default_style.font.height = 20 * 11 # (11pt) 기본폰트설정 다양한건 찾아보길 xlwt.add_palette_colour("header_background_color", 0x21) workbook.set_colour_RGB(0x21, 207, 238, 209) xlwt.add_palette_colour("header_font_color", 0x22) workbook.set_colour_RGB(0x22, 43, 95, 24) worksheet = workbook.add_sheet("Sheet1") # 시트 생성 header_style = xlwt.easyxf('font:name Malgun Gothic, color header_font_color, height 220; ' 'align: vert center; alignment: wrap True;' 'pattern:pattern solid, fore_colour header_background_color') # 폰트 스타일 생성 font_style = xlwt.easyxf('font:name Malgun Gothic, height 220; align: vert center; alignment: ' 'wrap True;') # 폰트 스타일 생성 print("새 파일 쓰는 중") worksheet.row(0).height_mismatch = True worksheet.row(0).height = 54*20 worksheet.write(0, 0, "수령인", header_style) worksheet.write(0, 1, "우편번호(생략가능)", header_style) worksheet.write(0, 2, "전체 주소", header_style) worksheet.write(0, 3, "연락처1\n(예) 0505-780-0505", header_style) worksheet.write(0, 4, "연락처2\n(예) 010-2879-6266", header_style) worksheet.write(0, 5, "상품명 (alt+enter 꼭 줄바꿈으로 구분할것, 독립된 셀에 적지마세요)\n(예) 김미진_무광하드_아이폰7_1개\n(예) " "이진영_무광터프_아이폰7_3개", header_style) worksheet.write(0, 6, "배송메세지", header_style) for row_val in range(len(order_list)): order = order_list[row_val] worksheet.row(row_val+1).height_mismatch = True worksheet.row(row_val+1).height = 90*20 worksheet.write(row_val+1, 0, order.recipient_name, font_style) worksheet.write(row_val+1, 2, order.address, font_style) worksheet.write(row_val+1, 3, order.recipient_phone, font_style) worksheet.write(row_val+1, 5, "[위탁][패키지]" + order.recipient_name + "_" + order.product_option + "_1개", font_style) worksheet.write(row_val+1, 6, order.caution, font_style) worksheet.write(row_val+1, 7, order.product_name, font_style) # worksheet.col(0).width = 256* 10 #(key byte) 칸 너비 설정 worksheet.col(0).width = 256 * 7 # 휴대폰 칸 너비 설정 worksheet.col(1).width = 256 * 18 # 휴대폰 칸 너비 설정 worksheet.col(2).width = 256 * 69 # 휴대폰 칸 너비 설정 worksheet.col(3).width = 256 * 18 # 주소 칸 너비 설정 worksheet.col(4).width = 256 * 18 # 운송잔 칸 너비 설정 worksheet.col(5).width = 256 * 61 # 주문번호 칸 너비 설정 worksheet.col(6).width = 256 * 11 # 상품명 칸 너비 설정 worksheet.col(7).width = 256 * 61 # 참고용 칸 print("새 파일 쓰기 완료, 저장 중") from io import BytesIO f = BytesIO() workbook.save(f) # 엑셀 파일 저장 및 생성 f.seek(0) print("저장 완료") return f
def add_write(self, row, column, content, style=''): if len(style) > 0: xlwt.add_palette_colour("custom_colour", 0x21) self.wb.set_colour_RGB(0x21, 200, 200, 100) l_style = xlwt.easyxf(style) self.sheet.write(row, column, content, l_style) else: self.sheet.write(row, column, content)
def mxd_to_excel(inFolder, outWorkbook, folderLvl): # Create spreadsheet with file name as sheet name workbook = xlwt.Workbook() sheet1 = workbook.add_sheet( validate_sheet_name(os.path.splitext(os.path.basename(outWorkbook))[0])) # Set cell format/style for header xlwt.add_palette_colour("hdcolour", 0x21) workbook.set_colour_RGB(0x21, 7, 97, 183) headerStyle = 'font: bold on, color white; align: horiz center; pattern: pattern solid, fore_color hdcolour' headerCols = ['MXD File Name','Project Title','Figure Number','Figure Title','Author','Approver','Export Date'] timeNow = datetime.datetime.now() sheetCreateInfo = "Generated: " + timeNow.strftime('%d/%m/%Y, %I:%M%p') sheet1.row(0).write(0, sheetCreateInfo, xlwt.Style.easyxf('font: bold on, color red, italic on, height 160')) # Font height 8pt (20*8) naStyle = 'font: height 160' # Font height 8 * 20 for 8pt hdColIndex = 0 for i in headerCols: sheet1.row(1).write(hdColIndex, headerCols[hdColIndex], xlwt.Style.easyxf(headerStyle)) hdColIndex += 1 sheet1.col(0).width = 256 * 25 sheet1.col(1).width = 256 * 40 sheet1.col(2).width = 256 * 22 sheet1.col(3).width = 256 * 40 sheet1.col(4).width = 256 * 22 sheet1.col(5).width = 256 * 22 sheet1.col(6).width = 256 * 22 # If file is an mxd, add required information to excel cells row = 2 # Set row to start below header col = 1 info = 0 for root, dirs, files in walk_level(inFolder, folderLvl): for f in files: if os.path.splitext(f)[1] == '.mxd': mxd = os.path.join(root, f) mxdinfo = mxd_details(mxd) link = 'HYPERLINK("' + mxd + '"; "' + f + '")' sheet1.write(row, 0, xlwt.Formula(link), xlwt.Style.easyxf('font: color blue')) sheet1.write(row, 1, mxdinfo[0]) sheet1.write(row, 2, mxdinfo[1]) sheet1.write(row, 3, mxdinfo[2]) sheet1.write(row, 4, mxdinfo[3]) sheet1.write(row, 5, mxdinfo[4]) sheet1.write(row, 6, mxdinfo[5]) row += 1 else: continue workbook.save(outWorkbook) arcpy.AddMessage(str(row) + ' rows added to Excel') os.startfile(outWorkbook)
def build_excel_shit(Stock1): #Workbook is created wb = Workbook() # add new colour to palette and set RGB colour value xlwt.add_palette_colour("custom_colour", 0x21) wb.set_colour_RGB(0x21, 1, 183, 2) # now you can use the colour in styles sheet1 = wb.add_sheet('Sheet 1') style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour') sheet1.write(1, 0, 'Sales') sheet1.write(2, 0, 'Current Ratio') sheet1.write(3, 0, 'P/E') sheet1.write(4, 0, 'Graham Multiplier ') sheet1.write(5, 0, 'Eps Growth') sheet1.write(7, 0, 'ROE') sheet1.write(8, 0, 'ROI') if (Stock1.total_revenue[4] > 350000000): sheet1.write(1, 1, Stock1.total_revenue[4], style) else: sheet1.write(1, 1, Stock1.total_revenue[4]) if (Stock1.current_ratio) > 2: sheet1.write(2, 1, Stock1.current_ratio, style) else: sheet1.write(2, 1, Stock1.current_ratio) if (Stock1.p_e) > 5 and (Stock1.p_e) < 15: sheet1.write(3, 1, Stock1.p_e, style) else: sheet1.write(3, 1, Stock1.p_e) if (Stock1.graham_multiplier) < 22: sheet1.write(4, 1, Stock1.graham_multiplier, style) else: sheet1.write(4, 1, Stock1.graham_multiplier) if (Stock1.eps_growth[4]) > 0.3: sheet1.write(5, 1, Stock1.eps_growth[4], style) else: sheet1.write(5, 1, Stock1.eps_growth[4]) if (Stock1.return_on_equity) > 15: sheet1.write(7, 1, Stock1.return_on_equity, style) else: sheet1.write(7, 1, Stock1.return_on_equity) if (Stock1.return_on_invested_capital) > 15: sheet1.write(8, 1, Stock1.return_on_invested_capital, style) else: sheet1.write(8, 1, Stock1.return_on_invested_capital) wb.save('xlwt example.xls')
def generaExcelResumen(lista, wb): pass sheet = wb.add_sheet("Resumen Scrims", cell_overwrite_ok=True) # estilos####### # color azul xlwt.add_palette_colour("custom_blue_color", 0x21) wb.set_colour_RGB(0x21, 192, 223, 255) # color rojo xlwt.add_palette_colour("custom_red_color", 0x22) wb.set_colour_RGB(0x22, 251, 204, 150) styleBold = xlwt.easyxf('font: bold 1') styleNormalBlue = xlwt.easyxf( 'align: vert centre, horiz center; pattern: pattern solid, fore_colour custom_blue_color') styleNormalRed = xlwt.easyxf( 'align: vert centre, horiz center; pattern: pattern solid, fore_colour custom_red_color') # /estilos###### # ancho de columnas try: sheet.col(0).width = 256 * 12 sheet.col(1).width = 256 * 12 sheet.col(2).width = 256 * 12 sheet.col(3).width = 256 * 8 sheet.col(4).width = 256 * 10 sheet.col(5).width = 256 * 40 sheet.col(6).width = 256 * 40 sheet.col(7).width = 256 * 12 sheet.col(8).width = 256 * 12 sheet.col(9).width = 256 * 14 sheet.col(10).width = 256 * 15 sheet.col(11).width = 256 * 15 sheet.col(12).width = 256 * 15 sheet.col(13).width = 256 * 15 sheet.col(14).width = 256 * 15 sheet.col(15).width = 256 * 15 sheet.col(16).width = 256 * 15 sheet.col(17).width = 256 * 15 sheet.col(18).width = 256 * 15 sheet.col(19).width = 256 * 15 sheet.col(20).width = 256 * 17 except ValueError: pass # /ancho de columnas escribeLinea(sheet, vars.titulosPartidaStats, 0, styleBold) i = 1 for linea in lista: if (linea[1] == "Red Team"): escribeLinea(sheet, linea, i, styleNormalRed) elif (linea[1] == "Blue Team"): escribeLinea(sheet, linea, i, styleNormalBlue) i = i +1 if (i%3==0): i = i +1
def print_report_excel(self): for w in self: dict = {} dict['fecha_hasta'] = w['fecha_hasta'] dict['fecha_desde'] = w['fecha_desde'] dict['proveedor'] = [w.proveedor_id.name] res = self.env['report.libreria.reporte_libreria_excel'].lineas( dict) lineas = res['lineas'] # registros = res['registros'] libro = xlwt.Workbook() hoja = libro.add_sheet('reporte') xlwt.add_palette_colour("custom_colour", 0x21) libro.set_colour_RGB(0x21, 200, 200, 200) hoja.write(0, 0, 'Reporte de registros de libros') hoja.write(2, 0, 'Nombre de proveedor') hoja.write(2, 1, w.proveedor_id[0].name) hoja.write(3, 0, str(w.fecha_desde) + 'al' + str(w.fecha_hasta)) y = 5 hoja.write(y, 0, 'Correlativo') hoja.write(y, 1, 'Nombre libro') hoja.write(y, 2, 'ISBN') hoja.write(y, 3, '#Paginas') hoja.write(y, 4, 'Fecha') hoja.write(y, 5, 'Precio') hoja.write(y, 6, 'Inventario') for linea in lineas: y += 1 hoja.write(y, 0, linea['correlativo']) hoja.write(y, 1, linea['nombre']) hoja.write(y, 2, linea['isbn']) hoja.write(y, 3, linea['paginas']) hoja.write(y, 4, linea['fecha_ingreso']) hoja.write(y, 5, linea['precio']) hoja.write(y, 6, linea['inventario']) f = io.BytesIO() libro.save(f) datos = base64.b64encode(f.getvalue()) self.write({'archivo': datos, 'name': 'libro_de_registros.xls'}) return { 'view_type': 'form', 'view_mode': 'form', 'res_model': 'libreria.asistente_reporte_libreria_excel', 'res_id': self.id, 'view_id': False, 'type': 'ir.actions.act_window', 'target': 'new', }
def add_style_lookup(x_pos, y_pos): """Add a new style to lookup table for one pixel if needed.""" palcolnum = pal_pixels[x_pos, y_pos] if palcolnum in already_used_colors: return already_used_colors.add(palcolnum) col_name = "custom_colour_" + str(palcolnum) xlwt.add_palette_colour(col_name, palcolnum) book.set_colour_RGB(palcolnum, *img_pixels[x_pos, y_pos]) style = xlwt.easyxf('pattern: pattern solid, fore_colour ' + col_name) style.pattern.pattern_fore_colour = palcolnum style_lookup[palcolnum] = style
def generaExcelJugadorIndividual(lista, wb, sheetName): pass sheet = wb.add_sheet(sheetName, cell_overwrite_ok=True) # estilos####### # color azul xlwt.add_palette_colour("custom_blue_color", 0x21) wb.set_colour_RGB(0x21, 192, 223, 255) # color rojo xlwt.add_palette_colour("custom_red_color", 0x22) wb.set_colour_RGB(0x22, 251, 204, 150) styleBold = xlwt.easyxf('font: bold 1') styleNormalBlue = xlwt.easyxf( 'align: vert centre, horiz center; pattern: pattern solid, fore_colour custom_blue_color') styleNormalRed = xlwt.easyxf( 'align: vert centre, horiz center') # /estilos###### # ancho de columnas try: sheet.col(0).width = 256 * 12 sheet.col(1).width = 256 * 12 sheet.col(2).width = 256 * 12 sheet.col(3).width = 256 * 8 sheet.col(4).width = 256 * 10 sheet.col(5).width = 256 * 40 sheet.col(6).width = 256 * 40 sheet.col(7).width = 256 * 12 sheet.col(8).width = 256 * 12 sheet.col(9).width = 256 * 14 sheet.col(10).width = 256 * 15 sheet.col(11).width = 256 * 15 sheet.col(12).width = 256 * 15 sheet.col(13).width = 256 * 15 sheet.col(14).width = 256 * 15 sheet.col(15).width = 256 * 15 sheet.col(16).width = 256 * 15 sheet.col(17).width = 256 * 15 sheet.col(18).width = 256 * 15 sheet.col(19).width = 256 * 15 sheet.col(20).width = 256 * 17 except ValueError: pass # /ancho de columnas escribeLinea(sheet, vars.titulosPlayerStats, 0, styleBold) i = 1 for statsPartida in lista: escribeLinea(sheet, statsPartida, i, styleNormalRed) i = i+2
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 summarySpreadsheet(rows): wb = xlwt.Workbook() wordFormat = xlwt.easyxf( 'align: wrap yes, horiz centre; font: name Calibri, height 220; border: left thin, top thin, right thin' ) sheet1 = wb.add_sheet('Sheet 1') i = 1 r = 0 xlwt.add_palette_colour("custom_colour", 0x21) wb.set_colour_RGB(0x21, 237, 125, 49) xlwt.add_palette_colour("pale_orange", 0x23) wb.set_colour_RGB(0x23, 252, 228, 214) #setting up formats for the cells sidestyle = xlwt.easyxf( 'pattern: pattern solid, fore_colour custom_colour; align: horiz centre; font: name Calibri, height 220, bold on; border: left medium, top medium, right medium' ) summStyle = xlwt.easyxf( 'pattern: pattern solid, fore_colour custom_colour; align: vert centre; font: name Calibri, height 220, bold on;border: left medium, top medium, right medium ' ) emptyLine = xlwt.easyxf('pattern: pattern solid, fore_colour pale_orange;') #function to generate header cells summaryHeaders(r, sheet1, sidestyle, summStyle, emptyLine) #going through each summary for row in rows: #setting up the dimentions of the cells sheet1.col(i).width = 12800 sheet1.row(r).height_mismatch = True sheet1.row(r).height = 240 * 2 #populating the cells with data, format: row, col, data, format sheet1.write(r, i, row[0], sidestyle) sheet1.write(r + 1, i, row[1], wordFormat) sheet1.write(r + 2, i, row[2], wordFormat) sheet1.write(r + 3, i, row[5], wordFormat) sheet1.write(r + 4, i, row[4], wordFormat) sheet1.write(r + 5, i, " ", emptyLine) sheet1.write(r + 6, i, " ", emptyLine) i += 1 #splitting the summaries to 5 per level if i % 5 == 1 and i != 46: r += 7 i = 1 summaryHeaders(r, sheet1, sidestyle, summStyle, emptyLine) wb.save('./%s/summaries.xls' % (path)) print("Spreadsheet ready!")
def set_Path_configure_all(self, path): self.path = str(os.getcwd()) + "/files/" + path if path != None else None # O comando os.getcwd pega o diretório atual de onde o arquivo python está. try: self.file = xlrd.open_workbook(self.path, formatting_info=True) # Abre o arquivo com o nome enviado no parâmetro diretorio except: self.file = xlrd.open_workbook(self.path) # Abre o arquivo com o nome enviado no parâmetro diretorio self.write_file = copy(self.file) xlwt.add_palette_colour("custom_colour", 0x21) self.sheet_list = self.file.sheet_names() # Pega o nome das páginas do arquivo self.sheet = self.file.sheet_by_index(0) # Pega a página inicial (começa por 0) self.formated_sheet = self.write_file.get_sheet(0) # Aqui já vão ser atribuídas no decorrer do processamento. self.columns_total = self.sheet.ncols self.row_total = self.sheet.nrows self.coordinate = Coordinate(self.sheet) self.data_treatment = Data_Treatment(self.sheet) self.locality = Locality(self.sheet)
def writePixelsIntoExcel(pixels): workbook = xlwt.Workbook(style_compression=2) sheet = workbook.add_sheet('PixelImage') workbook.save("./Output" + imageName + ".xls") for row in range(0, int(math.ceil(len(pixels) / mergeSize))): for column in range(0, int(math.ceil(len(pixels[0]) / mergeSize))): (r, g, b) = pixels[row * mergeSize][column * mergeSize] print("draw on cell:(" + str(row * mergeSize) + "," + str(column * mergeSize) + ") RGB:(" + str(r) + "," + str(g) + "," + str(b) + ")") (colorNum, color) = matchSimilarColor((r, g, b)) xlwt.add_palette_colour(str(colorNum), colorNum) workbook.set_colour_RGB(colorNum, color[0], color[1], color[2]) style = xlwt.easyxf('pattern: pattern solid, fore_colour ' + str(colorNum)) for mergedRow in range(row * mergeSize, row * mergeSize + mergeSize): for mergedColumn in range(column * mergeSize, column * mergeSize + mergeSize): sheet.write(mergedRow, mergedColumn, "", style) workbook.save("Output/" + imageName + "." + str(mergeSize) + ".xls")
def writeSheet(fileName="sample.xls", sheetName="Accounts", rows=rows): book = xlwt.Workbook() newSheet_1 = book.add_sheet(sheetName) for row_i, row in enumerate(rows): if 'error' in row: xlwt.add_palette_colour("custom_colour", 0x21) book.set_colour_RGB(0x21, 251, 228, 228) style = xlwt.easyxf( 'pattern: pattern solid, fore_colour custom_colour') else: style = False for i, cell in enumerate(row): if style == False: newSheet_1.write(row_i, i, cell) else: newSheet_1.write(row_i, i, cell, style) book.save(fileName)
def excel(self): book = xlwt.Workbook() # add new colour to palette and set RGB colour value xlwt.add_palette_colour("custom_colour", 0x21) book.set_colour_RGB(0x21, 255, 0, 0) # now you can use the colour in styles sheet1 = book.add_sheet('error') style = xlwt.easyxf( 'pattern: pattern solid, fore_colour custom_colour') error_file = True header = get_header(self.json) for row in range(0, 1): for col in range(len(header)): sheet1.write(row, col, header[col]) for i, item in enumerate(self.json): for col in range(len(header)): if header[col] == 'province': if Checker(item[header[col]]).province(): sheet1.write(i + 1, col, item[header[col]]) else: sheet1.write(i + 1, col, item[header[col]], style) elif header[col] == 'district': if Checker(item[header[col]]).district(): sheet1.write(i + 1, col, item[header[col]]) else: sheet1.write(i + 1, col, item[header[col]], style) elif header[col] == 'sector': if Checker(item[header[col]]).sector(): sheet1.write(i + 1, col, item[header[col]]) else: sheet1.write(i + 1, col, item[header[col]], style) else: sheet1.write(i + 1, col, item[header[col]]) filename = uniqid() + "_" + self.name + ".xls" save = "/Users/muhireremy/cartix/test/data_2015/error/" + filename book.save(save) return save
def update(): book = xlwt.Workbook(encoding="utf-8") xlwt.add_palette_colour("custom_colour", 0x21) book.set_colour_RGB(0x21, 251, 228, 228) style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour') sheet = book.add_sheet("User Activation Statistics", cell_overwrite_ok=True) sheet.write(0, 0, "Email", style) sheet.write(0, 1, "Activation Dates", style) row_number = 1 users = requests.get("http://127.0.0.1:8000/db/users/").json() max_length_1 = 10 max_length_2 = 10 for user in users: email = user['email'] + "@innopolis.ru" if len(email) > max_length_1: max_length_1 = len(email) try: dates = json.loads(user['activationDate']) except Exception: dates = [] sheet.write(row_number, 0, email) for date in dates: sheet.write(row_number, 1, date) row_number += 1 if len(date) > max_length_2: max_length_2 = len(date) if not dates: row_number += 1 sheet.col(0).width = get_width(max_length_1) sheet.col(1).width = get_width(max_length_2) if not os.path.exists(FOLDER_NAME): os.makedirs(FOLDER_NAME) if os.path.exists(FOLDER_NAME + "/" + FILE_NAME + ".xls"): os.remove(FOLDER_NAME + "/" + FILE_NAME + ".xls") book.save(FOLDER_NAME + "/" + FILE_NAME + ".xls")
def get_data_comparative(request): if request.method == 'GET': response = HttpResponse(content_type='application/ms-excel') response[ 'Content-Disposition'] = 'attachment; filename="sravnitelny.xls"' data = export_data_to_exel.data_from_db( export_data_to_exel.COMPARATIVE) wb = xlwt.Workbook(encoding='utf-8') ws = wb.add_sheet(export_data_to_exel.COMPARATIVE) xlwt.add_palette_colour("custom_colour", 0x21) wb.set_colour_RGB(0x21, 44, 176, 116) style = xlwt.easyxf( 'pattern: pattern solid, fore_colour custom_colour;' 'font: colour white, bold True;') date_format = xlwt.XFStyle() date_format.num_format_str = 'dd/mm/yyyy' name_of_cols = [ 'id', 'name', 'year', 'mileage', 'offer_price', 'par1_name', 'par1_val', 'par2_name', 'par2_val', 'created_at' ] for index_col in range(len(data[0])): # заполняем названия столбцов ws.write(0, index_col, name_of_cols[index_col], style) for row, index_row in zip(data, range(1, len(data) + 1)): for el, index_col in zip(row, range(len(row))): if index_col == 9: tmp = el.replace(tzinfo=None) ws.write(index_row, index_col, tmp, date_format) else: ws.write(index_row, index_col, el) wb.save(response) return response
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 save_2xlsx_with_colors(df, path2save, file_name='stats_general.xlsx', cols2color_sig=[], sig_thresh=0.05, nr_digits=6): if not chk_if_module_ready('xlwt'): print( 'xlwt not installed, cannot continue saving to an xlsx file with colors' ) pass else: print('creating file xlsx with colors') import xlwt w = xlwt.Workbook() xlwt.add_palette_colour("custom_colour", 0x21) w.set_colour_RGB(0x21, 251, 228, 228) style = xlwt.easyxf( 'pattern: pattern solid, fore_colour custom_colour') style_bold = xlwt.easyxf('font: bold on; font: color blue') style_significant = xlwt.easyxf('font: bold on; font: color red') ws = w.add_sheet('stats_general', cell_overwrite_ok=True) icol = 0 for col in df.columns.tolist(): ws.write(0, icol + 1, col, style_bold) if not cols2color_sig: if ', p' in col.lower() or col.lower().endswith('p'): cols2color_sig.append(col) icol += 1 irow = 0 while irow < len(df.index): ws.write(irow + 1, 0, df.index[irow], style_bold) for icol in range(0, len(df.columns.tolist())): col = df.columns.tolist()[icol] val = str(df.iloc[irow, icol])[:nr_digits] ws.write(irow + 1, icol + 1, val) if col in cols2color_sig and float(val) < sig_thresh: ws.write(irow + 1, icol + 1, val, style_significant) irow += 1 w.save(os.path.join(path2save, file_name))
def add_color(self, name, red, green, blue): """ Adds a custom color in the pallette within the scope of the report. After adding the color, it also stores its name and hex code in the `custom_color_palette` attribute for reference purposes. NOTE: The xlwt library takes into account the binary significance of the values assigned to colors, hence the usage of hexadecimal values. For references, see: https://stackoverflow.com/questions/7746837/python-xlwt-set-custom-background-colour-of-a-cell Sample Usage: ``` self.add_color('color_name', red=255, green=255, blue=255) style = xlwt.XFStyle() pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['color_name'] style.pattern = pattern return style ``` """ hex_value = self.available_palette_hex # xlwt only allows hexes equivalent to 8-63 as colors. # This is to protect the integrity of the library usage. if not (0x8 <= hex_value <= 0x3f): raise ValueError('For integrity purposes, a color may only ' 'have a hex value of 8-63.') xlwt.add_palette_colour(name, hex_value) self.workbook.set_colour_RGB(hex_value, red, green, blue) self.available_palette_hex += 0x1 cached_value = {name: hex_value} self.custom_color_palette.append(cached_value)
def new_xlwt_colored_workbook(): """ Get an Xlwt Workbook with the custom colors. :return: xlwt Woorkbook """ new_workbook = XWorkbook(encoding="UTF-8") new_workbook.set_colour_RGB(0x21, 139, 31, 63) new_workbook.set_colour_RGB(0x22, 255, 255, 255) new_workbook.set_colour_RGB(0x23, 31, 85, 111) add_palette_colour("custom_colour", 0x21) add_palette_colour("white", 0x22) add_palette_colour("blue", 0x23) return new_workbook
def putDataXL(offRow, offCol, TProblem, TUtility, TAvailability): clusterNames = readClusterNames() cNumbers = readClusterNumbers() book = xlwt.Workbook() # add new colour to palette and set RGB colour value xlwt.add_palette_colour("sky_blue_10", 0x21) book.set_colour_RGB(0x21, 153,204,255) xlwt.add_palette_colour("blue_classic", 0x22) book.set_colour_RGB(0x22, 207,231,245) xlwt.add_palette_colour("hijau_tua", 0x23) book.set_colour_RGB(0x23, 0,204,0) xlwt.add_palette_colour("hijau_muda", 0x24) book.set_colour_RGB(0x24, 153,255,153) xlwt.add_palette_colour("kuning", 0x25) book.set_colour_RGB(0x25, 255,255,0) xlwt.add_palette_colour("merah", 0x26) book.set_colour_RGB(0x26, 255,51,51) sheet1 = book.add_sheet(clusterType, cell_overwrite_ok = True) sheet1.row(0).height_mismatch = True sheet1.row(0).height = 360 styleTitle = 'pattern: pattern solid, fore_colour white;' styleTitle += 'align: vertical center, horizontal center, wrap on;' styleTitle += 'font: name Tahoma, height 280, bold 1;' sheet1.write_merge(offRow, offRow, offCol, offCol+20, 'ATM PRO ' + RegionName + ' PER '+clusterType , xlwt.easyxf(styleTitle)) shiftDown = 1 sheet1.row(1).height_mismatch = True sheet1.row(1).height = 360 sheet1.write_merge(offRow+shiftDown, offRow+shiftDown, offCol, offCol+20, 'posisi tanggal ' +time.strftime("%d/%m/%Y-%H:%M") , xlwt.easyxf(styleTitle)) contentAlignmentHorz = ["center", "right", "center", "center", "center", "center", "center", "center", "center"] def styler(strColor, fontHeight): styleHeader = 'pattern: pattern solid, fore_colour '+strColor+';' styleHeader += 'align: vertical center, horizontal center, wrap on;' styleHeader += 'borders: top thin, bottom thin;' styleHeader += 'font: name Tahoma, height '+str(fontHeight)+', bold 1;' return styleHeader def makeHeader(xRow, yCol, jenisTabel): arrJudul = ["", "", "NOP", "RSK", "OPS", "OOS", "OFF", "OF6", "JML"] sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+len(arrJudul)-1, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) # TABULASI RANKING PROBLEM ---------------------------------------------------- makeHeader(offRow, offCol, 'BY PROBLEM') sheet1.col(offCol+0).width = 5*315 sheet1.col(offCol+1).width = 20*315 sheet1.col(offCol+2).width = 4*315 sheet1.col(offCol+3).width = 4*315 sheet1.col(offCol+4).width = 4*315 sheet1.col(offCol+5).width = 4*315 sheet1.col(offCol+6).width = 4*315 sheet1.col(offCol+7).width = 4*315 sheet1.col(offCol+8).width = 4*315 sheet1.col(offCol+9).width = 6*315 shiftDownSeparator = 0 headRow = [] for i in range (0, len(TProblem)): if TProblem[i-1][9] != TProblem[i][9]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz center' shiftDownSeparator +=1 headRow.append(i+shiftDownSeparator+5) sepStyle = xlwt.easyxf(separatorStyle) r = i+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol sheet1.write(r, c, "", sepStyle) print TProblem[i][9] sheet1.write(r, c+1, TProblem[i][9].upper(), sepStyle) for j in range(0,len(TProblem[i])-1): strColor = colorProblem(TProblem[i][8]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[j] style = xlwt.easyxf(contentStyle) cellContent = TProblem[i][j] if j == len(TProblem[i])-1: cellContent = Formula("SUM(C"+str(i+shiftDownSeparator+5)+":H"+str(i+shiftDownSeparator+5)+")") if cellContent == 0: cellContent = '-' sheet1.write(i+offRow+4*shiftDown+shiftDownSeparator, j+offCol, cellContent, style) lastRow = i+offRow+4*shiftDown+shiftDownSeparator+3 headRow.append(lastRow) print headRow # PROBLEM ---------------------------------------------------- for k in range (0, len(headRow)-1): print k, headRow[k], len(headRow) sheet1.write(headRow[k]-2, c+1, clusterNames[k].upper(), xlwt.easyxf(separatorStyle)) sheet1.write(headRow[k]-2, c+2, Formula("SUM(C"+str(headRow[k])+":C"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+3, Formula("SUM(D"+str(headRow[k])+":D"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+4, Formula("SUM(E"+str(headRow[k])+":E"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+5, Formula("SUM(F"+str(headRow[k])+":F"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+6, Formula("SUM(G"+str(headRow[k])+":G"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+7, Formula("SUM(H"+str(headRow[k])+":H"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+8, Formula("SUM(I"+str(headRow[k])+":I"+str(headRow[k+1]-2)+")"), sepStyle) shiftLeft = 10 def makeHeader2(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+5, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "UP", "TUNAI", "NON", "%"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader2(offRow, offCol+shiftLeft, "BY UTILITY") # TABULASI RANKING UTILITY ---------------------------------------------------- sheet1.col(offCol+shiftLeft-1).width = 2*315 sheet1.col(offCol+shiftLeft+0).width = 5*315 sheet1.col(offCol+shiftLeft+1).width = 20*315 sheet1.col(offCol+shiftLeft+2).width = 6*315 sheet1.col(offCol+shiftLeft+3).width = 7*315 sheet1.col(offCol+shiftLeft+4).width = 5*315 sheet1.col(offCol+shiftLeft+5).width = 7*315 shiftDownSeparator = 0 headRow = [] for k in range (0, len(TUtility)): if TUtility[k-1][6] != TUtility[k][6]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz center' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = k+offRow+4*shiftDown+shiftDownSeparator-1 headRow.append(r+2) c = offCol+shiftLeft sheet1.write(r, shiftLeft, "", sepStyle) sheet1.write(r, shiftLeft+1, TUtility[k][6].upper(), sepStyle) sheet1.write(r, shiftLeft, "", sepStyle) sheet1.write(r, shiftLeft, "", sepStyle) for l in range(0,len(TUtility[k])-1): strColor = colorUtility(TUtility[k][5]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[l] cellContent = TUtility[k][l] if cellContent == 0: cellContent = '-' style = xlwt.easyxf(contentStyle) sheet1.write(k+offRow+4*shiftDown+shiftDownSeparator, l+offCol+shiftLeft, cellContent, style) lastRow = k+offRow+4*shiftDown+shiftDownSeparator+3 headRow.append(lastRow) # MATHEMATICAL CALCULATION ---------------------------------------------------- print "clusterNames", clusterNames REGUP, REGTUNAI, REGUTIL = 0, 0, 0 for j in range(0, len(cNumbers)): UP, TUNAI, NONTUNAI, PERCENT = 0, 0, 0, 0 for i in range(0,len(TUtility)): CNumber = TUtility[i][-1].upper() if CNumber == cNumbers[j]: UP += TUtility[i][2] TUNAI += TUtility[i][3] NONTUNAI += TUtility[i][4] REGUP += UP REGTUNAI += TUNAI try: PERCENT = float(TUNAI*100)/float(UP) except: PERCENT = 0.0 print clusterNames[j].upper(), UP, TUNAI, NONTUNAI, "{0:.4}".format(PERCENT) sheet1.write(headRow[j]-2, shiftLeft+1, clusterNames[j].upper(), xlwt.easyxf(separatorStyle)) sheet1.write(headRow[j]-2, shiftLeft+2, UP, xlwt.easyxf(separatorStyle)) sheet1.write(headRow[j]-2, shiftLeft+3, TUNAI, xlwt.easyxf(separatorStyle)) sheet1.write(headRow[j]-2, shiftLeft+4, NONTUNAI, xlwt.easyxf(separatorStyle)) sheet1.write(headRow[j]-2, shiftLeft+5, "{0:.4}".format(PERCENT), xlwt.easyxf(separatorStyle.replace('white',colorUtility(PERCENT)),num_format_str= '0.00')) try: REGUTIL = float(REGTUNAI*100)/float(REGUP) except: REGUTIL = 0.0 print "REG UTIL", REGUTIL, REGTUNAI # UTILITY ---------------------------------------------------- #for k in range (0, len(headRow)-1): # sheet1.write(headRow[k]-2, shiftLeft+2, Formula("SUM(L"+str(headRow[k])+":L"+str(headRow[k+1]-2)+")"), sepStyle) # sheet1.write(headRow[k]-2, shiftLeft+3, Formula("SUM(M"+str(headRow[k])+":M"+str(headRow[k+1]-2)+")"), sepStyle) # sheet1.write(headRow[k]-2, shiftLeft+4, Formula("SUM(N"+str(headRow[k])+":N"+str(headRow[k+1]-2)+")"), sepStyle) # sheet1.write(headRow[k]-2, shiftLeft+5, Formula("(M"+str(headRow[k]-1)+"/L"+str(headRow[k]-1)+")*100"), xlwt.easyxf(separatorStyle,num_format_str= '0.00')) # AVAILABILITY ---------------------------------------------------- shiftLeft = 17 def makeHeader3(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+3, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["", "", "ATM", "%"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader3(offRow, offCol+shiftLeft, "BY AVAILABILITY") #print TAvailability sheet1.col(offCol+shiftLeft-1).width = 2*315 sheet1.col(offCol+shiftLeft+0).width = 5*315 sheet1.col(offCol+shiftLeft+1).width = 20*315 sheet1.col(offCol+shiftLeft+2).width = 6*315 sheet1.col(offCol+shiftLeft+3).width = 7*315 shiftDownSeparator = 0 headRow = [] for m in range (0, len(TAvailability)): if TAvailability[m-1][4] != TAvailability[m][4]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz center' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = m+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol+shiftLeft headRow.append(r+2) sheet1.write(r, c, "", sepStyle) sheet1.write(r, c+1, TAvailability[m][4].upper(), sepStyle) tempSum = 0 for n in range(0,len(TAvailability[m])-1): strColor = colorAvail(TAvailability[m][3]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[n] style = xlwt.easyxf(contentStyle) sheet1.write(m+offRow+4*shiftDown+shiftDownSeparator, n+offCol+shiftLeft, TAvailability[m][n], style) strFormula = "PRODUCT(S" + str(m+offCol+shiftLeft+shiftDownSeparator-shiftLeft+5)+ ":T" +str(m+offCol+shiftLeft+shiftDownSeparator-shiftLeft+5)+")" sheet1.write(m+offRow+4*shiftDown+shiftDownSeparator, shiftLeft+4, Formula(strFormula), xlwt.easyxf('font: color white;')) lastRow = m+offRow+4*shiftDown+shiftDownSeparator+3 headRow.append(lastRow) # MATHEMATICAL CALCULATION ---------------------------------------------------- REGATM, REGATMAVAIL, REGAVAIL = 0, 0, 0 for j in range(0, len(clusterNames)): ATM, ATMAVAIL, PERCENT = 0, 0, 0 for i in range(0,len(TAvailability)): CNumber = TAvailability[i][-1].upper() if CNumber == cNumbers[j]: ATM += TAvailability[i][2] ATMAVAIL += TAvailability[i][2]*TAvailability[i][3] REGATM += ATM REGATMAVAIL += ATMAVAIL try: PERCENT = float(ATMAVAIL)/float(ATM) except: PERCENT = 0.0 print clusterNames[j].upper(), ATM, "{0:.4}".format(PERCENT) #TProblem.append((kodeCabang, namaCabang, NOP, RSK, PROBOPS, OOS, OFF, PROB, CName)) #TUtility.append((kodeCabang, namaCabang, UP, TUNAI, NONTUNAI, float("{0:.2}".format(PERCENT)),CName)) #TAvailability.append((kodeCabang, namaCabang, ATM, AVAIL, CName)) sheet1.write(headRow[j]-2, shiftLeft+1, clusterNames[j].upper(), xlwt.easyxf(separatorStyle)) sheet1.write(headRow[j]-2, shiftLeft+2, ATM, sepStyle) sheet1.write(headRow[j]-2, shiftLeft+3, PERCENT, xlwt.easyxf(separatorStyle.replace('white',colorUtility(PERCENT)),num_format_str= '0.00')) try: REGAVAIL = float(REGATMAVAIL)/float(REGATM) except: REGAVAIL = 0.0 print "REG AVAIL", REGAVAIL, REGATM # AVAILABILITY ---------------------------------------------------- #for k in range (0, len(headRow)-1): # sheet1.write(headRow[k]-2, shiftLeft+2, Formula("SUM(S"+str(headRow[k])+":S"+str(headRow[k+1]-2)+")"), sepStyle) # sheet1.write(headRow[k]-2, shiftLeft+3, Formula("U"+str(headRow[k]-1)+"/S"+str(headRow[k]-1)), xlwt.easyxf(separatorStyle,num_format_str= '0.00')) # sheet1.write(headRow[k]-2, shiftLeft+4, Formula("SUM(U"+str(headRow[k])+":U"+str(headRow[k+1]-2)+")"), xlwt.easyxf('font: color white;') ) lastRow = headRow[-1]-1 style = xlwt.easyxf(styler('sky_blue_10', 240)) #TOTAL 1 arrJudul = ["", "", "NOP", "RSK", "OPS", "OOS", "OFF", "OF6","JML"] for i in range (0, len(arrJudul)): sheet1.write(lastRow-1 , i, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) shiftLeft = len(arrJudul) #TOTAL 2 arrJudul = ["", "", "UP", "TUNAI", "NON", "%"] for i in range (0, len(arrJudul)): sheet1.write(lastRow-1 , i+shiftLeft+1, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) shiftLeft += len(arrJudul)+1 #TOTAL 3 arrJudul = ["", "", "ATM", "%"] for i in range (0, len(arrJudul)): sheet1.write(lastRow-1 , i+shiftLeft+1, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) sheet1.write(lastRow, 0, '', style) sheet1.write(lastRow, 1, 'PROBLEM', style) sheet1.write(lastRow, 2, Formula("SUM(C"+str(headRow[0]-1)+"+C"+str(headRow[1]-1)+"+C"+str(headRow[2]-1)+"+C"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 3, Formula("SUM(D"+str(headRow[0]-1)+"+D"+str(headRow[1]-1)+"+D"+str(headRow[2]-1)+"+D"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 4, Formula("SUM(E"+str(headRow[0]-1)+"+E"+str(headRow[1]-1)+"+E"+str(headRow[2]-1)+"+E"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 5, Formula("SUM(F"+str(headRow[0]-1)+"+F"+str(headRow[1]-1)+"+F"+str(headRow[2]-1)+"+F"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 6, Formula("SUM(G"+str(headRow[0]-1)+"+G"+str(headRow[1]-1)+"+G"+str(headRow[2]-1)+"+G"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 7, Formula("SUM(H"+str(headRow[0]-1)+"+H"+str(headRow[1]-1)+"+H"+str(headRow[2]-1)+"+H"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 8, Formula("SUM(I"+str(headRow[0]-1)+"+I"+str(headRow[1]-1)+"+I"+str(headRow[2]-1)+"+I"+str(headRow[3]-1)+")"), style) #TOTAL 2 sheet1.write(lastRow, 10, '', style) sheet1.write(lastRow, 11, 'UTILITY', style) sheet1.write(lastRow, 12, Formula("SUM(M"+str(headRow[0]-1)+"+M"+str(headRow[1]-1)+"+M"+str(headRow[2]-1)+"+M"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 13, Formula("SUM(N"+str(headRow[0]-1)+"+N"+str(headRow[1]-1)+"+N"+str(headRow[2]-1)+"+N"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 14, Formula("SUM(O"+str(headRow[0]-1)+"+O"+str(headRow[1]-1)+"+O"+str(headRow[2]-1)+"+O"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 15, REGUTIL, xlwt.easyxf(styler(colorAvail(REGAVAIL), 240), num_format_str= '0.00')) #TOTAL 2 sheet1.write(lastRow, 17, '', style) sheet1.write(lastRow, 18, 'AVAILABILITY', style) sheet1.write(lastRow, 19, Formula("SUM(T"+str(headRow[0]-1)+"+T"+str(headRow[1]-1)+"+T"+str(headRow[2]-1)+"+T"+str(headRow[3]-1)+")"), style) sheet1.write(lastRow, 20, REGAVAIL, xlwt.easyxf(styler(colorAvail(REGAVAIL), 240), num_format_str= '0.00')) sheet1.write_merge(lastRow+1, lastRow+1, offCol, offCol+20, 'TARGET UTILITY & AVAILABILITY = 99%' , xlwt.easyxf(styleTitle + 'borders: top thin, bottom thin;')) namaFileXLS = prepareDirectory("OUTPUT") + "ATM "+clusterType+"-" + RegionName +time.strftime("-%Y%m%d-%H")+'.xls' book.save(namaFileXLS)
outFileName="pagespeed-"+str(time.time())+".xls" # make the default output file name unique, by appending the unix timestamp inFileName=""#no input file by default tmpJson="tmpJson"#temporary json files returned by google api are saved as 'tmpJson+"Desktop/Mobile"+str(i+1)+".json"' in case it's needed for troubleshooting #google latest api path and optimized asset location apiCall="https://www.googleapis.com/pagespeedonline/v2/runPagespeed?" googleStore="https://developers.google.com/speed/pagespeed/insights/optimizeContents?url=" #create overall Workbook wb = xlwt.Workbook() #spreadsheet styles # add new colours to palette and set RGB colour values #see styles at https://secure.simplistix.co.uk/svn/xlwt/tags/0.7.2/xlwt/Style.py xlwt.add_palette_colour("custom_green", 0x21) wb.set_colour_RGB(0x21, 200, 255, 200) xlwt.add_palette_colour("custom_orange", 0x22) wb.set_colour_RGB(0x22, 255, 250, 180) xlwt.add_palette_colour("custom_red", 0x23) wb.set_colour_RGB(0x23, 255, 200, 200) xlwt.add_palette_colour("bright_red", 0x24) wb.set_colour_RGB(0x24, 255, 0, 0) #define cell styles boldStyle=xlwt.Style.easyxf("font: bold on") greenStyle=xlwt.easyxf('pattern: pattern solid,fore_colour custom_green;align: vert top') orangeStyle=xlwt.easyxf('pattern: pattern solid,fore_colour custom_orange;align: vert top') redStyle=xlwt.easyxf('pattern: pattern solid,fore_colour custom_red;align: vert top') brightRedStyle=xlwt.easyxf('pattern: pattern solid,fore_colour bright_red;align: vert top') defaultStyle=xlwt.easyxf('align: vert top')
def export_xls(request, week, start_date): import xlwt data = get_statement_data(week, start_date) file_name = 'statement({} {}).xls'.format(data['start_week'], data['end_week']) response = HttpResponse(content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename=' + file_name wb = xlwt.Workbook(encoding='utf-8') # кастомные цвета xlwt.add_palette_colour("custom_blue", 0x21) wb.set_colour_RGB(0x21, 0, 176, 240) blue_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_blue') xlwt.add_palette_colour("custom_light_blue", 0x22) wb.set_colour_RGB(0x22, 146, 205, 220) light_blue_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_light_blue') xlwt.add_palette_colour("custom_orange", 0x23) wb.set_colour_RGB(0x23, 255, 192, 0) orange_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_orange') xlwt.add_palette_colour("custom_light_green", 0x24) wb.set_colour_RGB(0x24, 146, 208, 80) light_green_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_light_green') xlwt.add_palette_colour("custom_yellow", 0x25) wb.set_colour_RGB(0x25, 255, 255, 0) yellow_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_yellow') xlwt.add_palette_colour("custom_pink", 0x26) wb.set_colour_RGB(0x26, 230, 184, 183) pink_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_pink') xlwt.add_palette_colour("custom_dark_pink", 0x27) wb.set_colour_RGB(0x27, 218, 150, 148) dark_pink_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_dark_pink') xlwt.add_palette_colour("custom_penalty", 0x28) wb.set_colour_RGB(0x28, 218, 150, 148) penalty_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_penalty') xlwt.add_palette_colour("custom_purple", 0x29) wb.set_colour_RGB(0x29, 177, 160, 199) purple_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_purple') xlwt.add_palette_colour("custom_red", 0x30) wb.set_colour_RGB(0x30, 255, 71, 71) red_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_red') font_style_bold = xlwt.XFStyle() font_style_bold.font.bold = True writer = Writer(wb.add_sheet('statement')) # ws = wb.add_sheet('statement')#, cell_overwrite_ok=True) # ПЕРВАЯ СТРОКА font_style = xlwt.XFStyle() for i in xrange(len(data['drinks_table_header']) + 2): writer.write('', font_style) writer.write('Tequilla Girl', font_style_bold) # имена сотрудников for idx, employee in enumerate(data['employees_table_header']): writer.write('{}. {}'.format(idx + 1, employee.get_full_name()), font_style) # ВТОРАЯ СТРОКА writer.new_row() writer.write('', font_style) writer.write('', font_style) # названия напитков for drink_data in data['drinks_table_header']: if drink_data['used']: writer.write(drink_data['name'], font_style) writer.write('Менеджер Tequilla girl', font_style_bold) # координаторы for employee in data['employees_table_header']: text = employee.coordinator.get_full_name() if employee.coordinator else '' writer.write(text, font_style) # ТРЕТЬЯ СТРОКА writer.new_row() writer.write('Клуб', font_style_bold) writer.write('Менеджер клуба', font_style_bold) for drink_data in data['drinks_table_header']: if drink_data['used']: writer.write('Цена', font_style) writer.write('Напитки', font_style_bold) # названия напитков for drinks in data['header_drinks_for_employee'].values(): names = [drink for drink in drinks] writer.write('{}, {}, {}'.format(', '.join(names), 'Всего клуб', 'Коорд-р'), font_style) # ВЫВОД ТАБЛИЦЫ for idx, row in enumerate(data['grid']): writer.new_row() writer.write( '{}. {} {}, {}'.format( idx + 1, row['club'].name, row['club'].get_address(), row['club'].city if row['club'].city else '' ), yellow_style if idx % 2 == 0 else orange_style ) writer.write(row['club'].coordinator.surname if row['club'].coordinator else '', light_green_style) for ndx, price in enumerate(row['drinks_for_club']): if price['used']: writer.write( price['sum'], blue_style if ndx % 2 == 0 else light_blue_style ) writer.write('', font_style) for info in row['employees_info']: count = [str(drink['count']) for drink in info['drinks_list']] writer.write( '{}, {:.2f}р., {:.2f}р.'.format(' '.join(count), info['sum_for_club'], info['sum_for_coordinator']), yellow_style ) # ФУТЕР writer.new_row() writer.write('ВСЕГО (Перевод Tequilla girl)', pink_style) for i in xrange(len(data['drinks_table_header']) + 2): writer.write('', pink_style) for info in data['bottom_prices_for_employee'].values(): writer.write('{:.2f}'.format(info['all']), pink_style) writer.new_row() writer.write('Залог Tequilla girl', dark_pink_style) for i in xrange(len(data['drinks_table_header']) + 2): writer.write('', dark_pink_style) for info in data['bottom_prices_for_employee'].values(): writer.write(info['pledge'], dark_pink_style) writer.new_row() writer.write('Штраф', penalty_style) for i in xrange(len(data['drinks_table_header']) + 2): writer.write('', penalty_style) for info in data['bottom_prices_for_employee'].values(): writer.write('{:.2f}'.format(info['penalty']), penalty_style) writer.new_row() writer.write('Причина штрафа', penalty_style) for i in xrange(len(data['drinks_table_header']) + 2): writer.write('', penalty_style) for info in data['bottom_prices_for_employee'].values(): description = [description for description in info['penalty_description']] writer.write('; '.join(description), penalty_style) writer.new_row() writer.write('Координатор (всего за клубы по каждой Tequilla Girl)', purple_style) for i in xrange(len(data['drinks_table_header']) + 2): writer.write('', purple_style) for info in data['bottom_prices_for_employee'].values(): writer.write('{:.2f}'.format(info['coordinator']), purple_style) writer.new_row() writer.write('Ермакова (всего за клубы по каждой Tequilla Girl)', light_blue_style) for i in xrange(len(data['drinks_table_header']) + 2): writer.write('', light_blue_style) for info in data['bottom_prices_for_employee'].values(): writer.write('{:.2f}'.format(info['director']), light_blue_style) writer.new_row() writer.new_row() writer.write( 'Координатор(за все клубы и за всех tequila girls): {:.2f}'.format(data['admins_salary']['coordinator']), pink_style ) writer.new_row() writer.write( 'Ермакова(за все клубы и за всех tequila girls): {:.2f}'.format(data['admins_salary']['director']), red_style ) wb.save(response) return response
def __init__(self, minimum, maximum, interval, book): self.levels = {} self.minimum = minimum self.maximum = maximum dataRange = maximum - minimum steps = int(dataRange / interval) + 1 if (steps >= 4): steps_4 = steps / 4 else: steps_4 = 1 for i in range(steps): if (i <= steps_4): red = 255 elif (i > steps_4 and i <= steps_4 * 2): red = 255 - (255 / steps_4) * (i - steps_4) elif (i > steps_4 * 2 and i <= steps_4 * 3): red = (255 / 2 / steps_4) * (i - steps_4 * 2) elif i < steps: red = (255 / 2) - (255 / 2 / steps_4) * (i - steps_4 * 3) else: red = 0 if (i <= steps_4): green = (255 / steps_4) * i elif (i > steps_4 and i <= steps_4 * 2): green = 255 - (255 / steps_4) * (i - steps_4) elif (i > steps_4 * 2 and i <= steps_4 * 3): green = (255 / steps_4) * (i - steps_4 * 2) else: green = 255 if (i <= steps_4): blue = 0 elif (i > steps_4 and i <= steps_4 * 2): blue = 0 + (255 / steps_4) * (i - steps_4) elif i < steps: blue = 255 - (255 / steps_4 / 2) * (i - steps_4 * 2) else: blue = 0 red = abs(red) green = abs(green) blue = abs(blue) if (red > 255): red = 255 if (green > 255): green = 255 if (blue > 255): blue = 255 value = self.roundValue(minimum + i * interval) excelIndex = 8 + i colourName = "custom_colour_%d" % excelIndex xlwt.add_palette_colour(colourName, excelIndex) book.set_colour_RGB(excelIndex, red, green, blue) style = xlwt.easyxf('pattern: pattern solid, fore_colour %s' % colourName, num_format_str='0%') self.levels[value] = (red, green, blue, value, excelIndex, colourName, style)
def write_duty_summary_data(): """ 考勤汇总总表 :return: """ year = '2016' month = '01' month_days = calendar.monthrange(int(year), int(month))[1] original_data_excel = xlrd.open_workbook('/Users/cai/Documents/考勤系统需求说明书及附件/test_excel2222.xls') table0 = original_data_excel.sheet_by_index(0) # 通过索引顺序获取 work_book = xlwt.Workbook() # 创建工作簿 # set custom color xlwt.add_palette_colour('custom_green', 0x21) work_book.set_colour_RGB(0x21, 203, 255, 200) green_style = xlwt.XFStyle() pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['custom_green'] green_style.pattern = pattern sheet1 = work_book.add_sheet(u'合同制', cell_overwrite_ok=True) # 创建sheet sheet1.write(0, 0, '研究院%s年%s月员工考勤汇总总表' % (year, month)) sheet1.write(1, 0, '序号') sheet1.write(1, 1, '部门序号') sheet1.write(1, 2, '组织单位') sheet1.write(1, 3, '人员编号') sheet1.write(1, 4, '人员姓名') duty_list = ['全勤', '出差、会议、培训等出勤情况', '迟到', '早退', '缺勤', '法定年休假', '企业年休假', '病假', '事假', '产假', '其他假期(如:婚假、丧假等)'] for i in xrange(2, 13): sheet1.write(1, 2*i+1, duty_list[i-2]) sheet1.write(1, 2*i+2, duty_list[i-2]) if i != 11: sheet1.write(2, 2*i+1, '天数', green_style) else: sheet1.write(2, 2*i+1, '天数日历日', green_style) sheet1.write(2, 2*i+2, '说明') for i in xrange(2, table0.nrows, 2): # 步进2 staff_name = table0.cell(i, 2).value staff_no = table0.cell(i, 1).value staff_dept = table0.cell(i, 0).value if staff_dept != table0.cell(i-2, 0).value: dept_count = 1 else: dept_count += 1 present_days = 0 # 全勤天数 out_days = 0 # 外出天数 legal_leave_days = 0 # 法定年假天数 legal_leave_string = '' # 法定年假说明 company_leave_days = 0 # 企业年假天数 company_leave_string = '' # 企业年假说明 sick_leave_days = 0 # 病假天数 sick_leave_string = '' # 病假天数说明 personal_leave_days = 0 # 事假天数 personal_leave_string = '' # 事假天数说明 pregnant_leave_days = 0 # 产假 other_leave_days = 0 # 其他假期 late_count = 0 # 迟到次数 late_days_string = '' # 迟到天数说明 leave_early_count = 0 # 早退次数 leave_early_string = '' # 早退天数说明 absence_count = 0 # 缺勤次数 absence_string = '' # 缺勤天数说明 for j in xrange(0, month_days): am_duty = table0.cell(i, 3+j).value # 上午考勤情况 pm_duty = table0.cell(i+1, 3+j).value # 下午考勤情况 # 判断上午情况 if am_duty == '8': present_days += 0.5 elif '请假' in am_duty: if '法定年假' in am_duty: legal_leave_days += 0.5 legal_leave_string += '%s-%s-%s上;' % (year, month, j) elif '企业年假' in am_duty: company_leave_days += 0.5 company_leave_string += '%s-%s-%s上;' % (year, month, j) elif '病假' in am_duty: sick_leave_days += 0.5 sick_leave_string += '%s-%s-%s上;' % (year, month, j) elif '事假' in am_duty: personal_leave_days += 0.5 personal_leave_string += '%s-%s-%s上;' % (year, month, j) elif '产假' in am_duty: pregnant_leave_days += 0.5 elif '其他' in am_duty: other_leave_days += 0.5 elif '外出' in am_duty: out_days += 0.5 elif '晚签到' in am_duty: present_days += 0.5 elif '迟到' in am_duty: late_count += 1 late_days_string += '%s-%s-%s;' % (year, month, j) elif '缺勤' in am_duty: absence_count += 0.5 absence_string += '%s-%s-%s上;' % (year, month, j) # 判断下午情况 if pm_duty == '8': present_days += 0.5 elif '请假' in pm_duty: if '法定年假' in pm_duty: legal_leave_days += 0.5 legal_leave_string += '%s-%s-%s下;' % (year, month, j) elif '企业年假' in pm_duty: company_leave_days += 0.5 company_leave_string += '%s-%s-%s下;' % (year, month, j) elif '病假' in pm_duty: sick_leave_days += 0.5 sick_leave_string += '%s-%s-%s下;' % (year, month, j) elif '事假' in pm_duty: personal_leave_days += 0.5 personal_leave_string += '%s-%s-%s下;' % (year, month, j) elif '产假' in pm_duty: pregnant_leave_days += 0.5 elif '其他' in pm_duty: other_leave_days += 0.5 elif '外出' in pm_duty: out_days += 0.5 elif '晚签到' in pm_duty: present_days += 0.5 elif '早退' in pm_duty: leave_early_count += 1 leave_early_string += '%s-%s-%s;' % (year, month, j) elif '缺勤' in pm_duty: absence_count += 0.5 absence_string += '%s-%s-%s下;' % (year, month, j) sheet1.write(i/2+2, 0, i/2) # index sheet1.write(i/2+2, 1, dept_count) sheet1.write(i/2+2, 4, staff_name) sheet1.write(i/2+2, 3, staff_no) sheet1.write(i/2+2, 2, staff_dept) sheet1.write(i/2+2, 5, present_days, green_style) sheet1.write(i/2+2, 7, out_days, green_style) sheet1.write(i/2+2, 9, late_count, green_style) sheet1.write(i/2+2, 10, late_days_string) sheet1.write(i/2+2, 11, leave_early_count, green_style) sheet1.write(i/2+2, 12, leave_early_string) sheet1.write(i/2+2, 13, absence_count, green_style) sheet1.write(i/2+2, 14, absence_string, green_style) sheet1.write(i/2+2, 15, legal_leave_days, green_style) sheet1.write(i/2+2, 16, legal_leave_string) sheet1.write(i/2+2, 17, company_leave_days, green_style) sheet1.write(i/2+2, 18, company_leave_string) sheet1.write(i/2+2, 19, sick_leave_days, green_style) sheet1.write(i/2+2, 20, sick_leave_string) sheet1.write(i/2+2, 21, personal_leave_days, green_style) sheet1.write(i/2+2, 22, personal_leave_string) sheet1.write(i/2+2, 23, pregnant_leave_days, green_style) sheet1.write(i/2+2, 25, other_leave_days, green_style) work_book.save('/Users/cai/Documents/考勤系统需求说明书及附件/test_excel3333.xls') # 保存文件
def create_style(self, workbook, base_style, style_name, palette_index, color): color_name = style_name + "_color" xlwt.add_palette_colour(color_name, palette_index) workbook.set_colour_RGB(palette_index, *color) self.styles[style_name] = xlwt.easyxf(base_style.format(color_name), num_format_str="0.0")
def create_color(self, workbook, color_name, palette_index, color): xlwt.add_palette_colour(color_name, palette_index) workbook.set_colour_RGB(palette_index, *color)
def Write_ToExcel(StatsObject,FileName): workbook = xlwt.Workbook() sheet = FitSheetWrapper(workbook.add_sheet("Sheet 1")) Row0 = 0 Row1 = 1 Row2 = 2 Row3 = 3 Row4 = 4 Row5 = 5 Row6 = 6 Row7 = 7 Row8 = 8 Row9 = 9 Row10 = 10 Row11 = 11 Row12 = 12 Row13 = 13 Row14 = 14 Row15 = 15 Row16 = 16 Row17 = 17 Row18 = 18 Row19 = 19 Row20 = 20 Row21 = 21 Row22 = 22 #Setting Colors xlwt.add_palette_colour("salmon", 0x21) workbook.set_colour_RGB(0x21, 255, 153, 153) xlwt.add_palette_colour("light_blue", 0x22) workbook.set_colour_RGB(0x22, 173, 216, 230) bold = xlwt.easyxf('font: bold 1') lightred = xlwt.easyxf('pattern: pattern solid, fore_colour salmon; border: top thin, bottom thin, left thin, right thin') lightblue = xlwt.easyxf('pattern: pattern solid, fore_colour light_blue; border: top thin, bottom thin, left thin, right thin') GameStartRow = 1 for Day in range(len(StatsObject)): for Game in range(len(StatsObject[Day])): #Set up totals. BTeamDeaths = int((StatsObject[Day][Game]['Blue Team Char Info']['CharOne'][5]+ StatsObject[Day][Game]['Blue Team Char Info']['CharTwo'][5]+ StatsObject[Day][Game]['Blue Team Char Info']['CharThree'][5]+ StatsObject[Day][Game]['Blue Team Char Info']['CharFour'][5]+ StatsObject[Day][Game]['Blue Team Char Info']['CharFive'][5])) RTeamDeaths = int((StatsObject[Day][Game]['Red Team Char Info']['CharOne'][5]+ StatsObject[Day][Game]['Red Team Char Info']['CharTwo'][5]+ StatsObject[Day][Game]['Red Team Char Info']['CharThree'][5]+ StatsObject[Day][Game]['Red Team Char Info']['CharFour'][5]+ StatsObject[Day][Game]['Red Team Char Info']['CharFive'][5])) #TITLES sheet.write(Row0,0,"Game Length in Seconds",bold) sheet.write(Row1,0,"VOD",bold) sheet.write(Row2,0,"Match History",bold) #Team Info Titles sheet.write(Row3,0,"Team Name",bold) sheet.write(Row4,0,"Team Win",bold) sheet.write(Row5,0,"Ban One",bold) sheet.write(Row6,0,"Ban Two",bold) sheet.write(Row7,0,"Ban Three",bold) sheet.write(Row8,0,"Towers Destoryed",bold) sheet.write(Row9,0,"Dragons Killed",bold) sheet.write(Row10,0,"Barons Killed",bold) sheet.write(Row11,0,"Kills",bold) sheet.write(Row12,0,"Deaths",bold) sheet.write(Row13,0,"Gold",bold) #Player Char Info Titles sheet.write(Row0,4,"Player 1",bold) sheet.write(Row0,5,"Player 2",bold) sheet.write(Row0,6,"Player 3",bold) sheet.write(Row0,7,"Player 4",bold) sheet.write(Row0,8,"Player 5",bold) sheet.write(Row0,9,"Player 6",bold) sheet.write(Row0,10,"Player 7",bold) sheet.write(Row0,11,"Player 8",bold) sheet.write(Row0,12,"Player 9",bold) sheet.write(Row0,13,"Player 10",bold) sheet.write(Row1,3,"Name",bold) sheet.write(Row2,3,"Champ",bold) sheet.write(Row3,3,"SS One",bold) sheet.write(Row4,3,"SS Two",bold) sheet.write(Row5,3,"Kills",bold) sheet.write(Row6,3,"Kills %",bold) sheet.write(Row7,3,"Deaths",bold) sheet.write(Row8,3,"Deaths %",bold) sheet.write(Row9,3,"Assists",bold) sheet.write(Row10,3,"Assists %",bold) sheet.write(Row11,3,"CS",bold) sheet.write(Row12,3,"CS %",bold) sheet.write(Row13,3,"Gold",bold) sheet.write(Row14,3,"Gold %",bold) sheet.write(Row15,3,"Item One",bold) sheet.write(Row16,3,"Item Two",bold) sheet.write(Row17,3,"Item Three",bold) sheet.write(Row18,3,"Item Four",bold) sheet.write(Row19,3,"Item Five",bold) sheet.write(Row20,3,"Item Six",bold) sheet.write(Row21,3,"Trinket",bold) sheet.write(Row22,3,"Mastery",bold) #Pick Info sheet.write(Row1,14,"Pick 1",bold) sheet.write(Row2,14,"Pick 3",bold) sheet.write(Row3,14,"Pick 3",bold) sheet.write(Row4,14,"Pick 5",bold) sheet.write(Row5,14,"Pick 5",bold) sheet.write(Row0,14,"Pick #",bold) sheet.write(Row0,15,"Character",bold) sheet.write(Row0,16,"Position",bold) sheet.write(Row0,17,"Position",bold) sheet.write(Row0,18,"Character",bold) sheet.write(Row0,19,"Pick #",bold) sheet.write(Row1,19,"Pick 2",bold) sheet.write(Row2,19,"Pick 2",bold) sheet.write(Row3,19,"Pick 4",bold) sheet.write(Row4,19,"Pick 4",bold) sheet.write(Row5,19,"Pick 6",bold) #Tower Place Holder sheet.write(Row7,15,"Time",bold) sheet.write(Row8,14,"BotTow1",bold) sheet.write(Row9,14,"BotTow2",bold) sheet.write(Row10,14,"BotTow3",bold) sheet.write(Row11,14,"Botinhib",bold) sheet.write(Row12,14,"MidTow1",bold) sheet.write(Row13,14,"MidTow2",bold) sheet.write(Row14,14,"MidTow3",bold) sheet.write(Row15,14,"Midinhib",bold) sheet.write(Row16,14,"TopTow1",bold) sheet.write(Row17,14,"TopTow2",bold) sheet.write(Row18,14,"TopTow3",bold) sheet.write(Row19,14,"Topinhib",bold) sheet.write(Row20,14,"FTowTop",bold) sheet.write(Row21,14,"FTowBot",bold) sheet.write(Row7,18,"Time",bold) sheet.write(Row8,19,"BotTow1",bold) sheet.write(Row9,19,"BotTow2",bold) sheet.write(Row10,19,"BotTow3",bold) sheet.write(Row11,19,"Botinhib",bold) sheet.write(Row12,19,"MidTow1",bold) sheet.write(Row13,19,"MidTow2",bold) sheet.write(Row14,19,"MidTow3",bold) sheet.write(Row15,19,"Midinhib",bold) sheet.write(Row16,19,"TopTow1",bold) sheet.write(Row17,19,"TopTow2",bold) sheet.write(Row18,19,"TopTow3",bold) sheet.write(Row19,19,"Topinhib",bold) sheet.write(Row20,19,"FTowTop",bold) sheet.write(Row21,19,"FTowBot",bold) ######### ulstyle = xlwt.easyxf('font: underline single') sheet.write(Row0,1,StatsObject[Day][Game]['Game Length'])#Game X link = 'HYPERLINK("%s","VOD")\n' % (StatsObject[Day][Game]['Links']['Video']) sheet.write(Row1,1,xlwt.Formula(link),ulstyle)#VOD link = 'HYPERLINK("%s","STATS")\n' % (StatsObject[Day][Game]['Links']['Match History']) sheet.write(Row2,1,xlwt.Formula(link),ulstyle)#Stats #sheet.col(1).width = 100 #Blue Team General Info sheet.write(Row3,1,str(StatsObject[Day][Game]['Game Title'].split(" vs ")[0]),lightblue)#Team A Name sheet.write(Row4,1,StatsObject[Day][Game]['Blue Team Info']['Win'],lightblue) sheet.write(Row5,1,StatsObject[Day][Game]['Blue Team Info']['Bans'][0],lightblue)#Team A Ban 1 sheet.write(Row6,1,StatsObject[Day][Game]['Blue Team Info']['Bans'][1],lightblue)#Team A Ban 2 sheet.write(Row7,1,StatsObject[Day][Game]['Blue Team Info']['Bans'][2],lightblue)#Team A Ban 3 sheet.write(Row8,1,StatsObject[Day][Game]['Blue Team Info']['Towers'],lightblue)#Team A Towers sheet.write(Row9,1,StatsObject[Day][Game]['Blue Team Info']['Dragons'],lightblue)#Team A Dragons sheet.write(Row10,1,StatsObject[Day][Game]['Blue Team Info']['Barons'],lightblue)#Team A Barons sheet.write(Row11,1,StatsObject[Day][Game]['Blue Team Info']['Kills'],lightblue)#Team A Kills sheet.write(Row12,1,StatsObject[Day][Game]['Blue Team Info']['Deaths'],lightblue)#Team A Deaths sheet.write(Row13,1,StatsObject[Day][Game]['Blue Team Info']['Gold'],lightblue) #Red Team General Info sheet.write(Row3,2,str(StatsObject[Day][Game]['Game Title'].split(" vs ")[1]),lightred)#Team B Name sheet.write(Row4,2,StatsObject[Day][Game]['Red Team Info']['Win'],lightred) sheet.write(Row5,2,StatsObject[Day][Game]['Red Team Info']['Bans'][0],lightred)#Team B Ban 1 sheet.write(Row6,2,StatsObject[Day][Game]['Red Team Info']['Bans'][1],lightred)#Team B Ban 2 sheet.write(Row7,2,StatsObject[Day][Game]['Red Team Info']['Bans'][2],lightred)#Team B Ban 3 sheet.write(Row8,2,StatsObject[Day][Game]['Red Team Info']['Towers'],lightred)#Team B Towers sheet.write(Row9,2,StatsObject[Day][Game]['Red Team Info']['Dragons'],lightred)#Team B Dragons sheet.write(Row10,2,StatsObject[Day][Game]['Red Team Info']['Barons'],lightred)#Team B Barons sheet.write(Row11,2,StatsObject[Day][Game]['Red Team Info']['Kills'],lightred)#Team B Kills sheet.write(Row12,2,StatsObject[Day][Game]['Red Team Info']['Deaths'],lightred)#Team B Deaths sheet.write(Row13,2,StatsObject[Day][Game]['Red Team Info']['Gold'],lightred) #####Blue Team Char Info column = 4 row = GameStartRow for character in (StatsObject[Day][Game]['Blue Team Char Info']): for attribute in (StatsObject[Day][Game]['Blue Team Char Info'][character]): if isinstance(attribute,list): for a in attribute: sheet.write(row,column,a,lightblue) row +=1 else: sheet.write(row,column,str(attribute),lightblue) row +=1 row = GameStartRow column +=1 for character in (StatsObject[Day][Game]['Red Team Char Info']): for attribute in (StatsObject[Day][Game]['Red Team Char Info'][character]): if isinstance(attribute,list): for a in attribute: sheet.write(row,column,a,lightred) row +=1 else: sheet.write(row,column,str(attribute),lightred) row +=1 row = GameStartRow column +=1 #Blue Char Names sheet.write(Row1,15,StatsObject[Day][Game]['Blue Team Info']['Picks'][0],lightblue) sheet.write(Row2,15,StatsObject[Day][Game]['Blue Team Info']['Picks'][1],lightblue) sheet.write(Row3,15,StatsObject[Day][Game]['Blue Team Info']['Picks'][2],lightblue) sheet.write(Row4,15,StatsObject[Day][Game]['Blue Team Info']['Picks'][3],lightblue) sheet.write(Row5,15,StatsObject[Day][Game]['Blue Team Info']['Picks'][4],lightblue) #Blue Char Pos sheet.write(Row1,16,StatsObject[Day][Game]['Blue Team Info']['Picks Pos'][0],lightblue) sheet.write(Row2,16,StatsObject[Day][Game]['Blue Team Info']['Picks Pos'][1],lightblue) sheet.write(Row3,16,StatsObject[Day][Game]['Blue Team Info']['Picks Pos'][2],lightblue) sheet.write(Row4,16,StatsObject[Day][Game]['Blue Team Info']['Picks Pos'][3],lightblue) sheet.write(Row5,16,StatsObject[Day][Game]['Blue Team Info']['Picks Pos'][4],lightblue) #Red Char Pos sheet.write(Row1,17,StatsObject[Day][Game]['Red Team Info']['Picks Pos'][0],lightred) sheet.write(Row2,17,StatsObject[Day][Game]['Red Team Info']['Picks Pos'][1],lightred) sheet.write(Row3,17,StatsObject[Day][Game]['Red Team Info']['Picks Pos'][2],lightred) sheet.write(Row4,17,StatsObject[Day][Game]['Red Team Info']['Picks Pos'][3],lightred) sheet.write(Row5,17,StatsObject[Day][Game]['Red Team Info']['Picks Pos'][4],lightred) #Red Char Name sheet.write(Row1,18,StatsObject[Day][Game]['Red Team Info']['Picks'][0],lightred) sheet.write(Row2,18,StatsObject[Day][Game]['Red Team Info']['Picks'][1],lightred) sheet.write(Row3,18,StatsObject[Day][Game]['Red Team Info']['Picks'][2],lightred) sheet.write(Row4,18,StatsObject[Day][Game]['Red Team Info']['Picks'][3],lightred) sheet.write(Row5,18,StatsObject[Day][Game]['Red Team Info']['Picks'][4],lightred) #Spacing Between Games RowGap = 25 GameStartRow += RowGap Row0 += RowGap Row1 += RowGap Row2 += RowGap Row3 += RowGap Row4 += RowGap Row5 += RowGap Row6 += RowGap Row7 += RowGap Row8 += RowGap Row9 += RowGap Row10 += RowGap Row11 += RowGap Row12 += RowGap Row13 += RowGap Row14 += RowGap Row15 += RowGap Row16 += RowGap Row17 += RowGap Row18 += RowGap Row19 += RowGap Row20 += RowGap Row21 += RowGap Row22 += RowGap workbook.save(FileName)
# Prepare Excel file book = xlwt.Workbook() # Sheet 1 sheet_name_1 = unicode(u'Κουπόνι ' + date_kouponi).replace('/', '.') sheet1 = book.add_sheet(sheet_name_1) print 'Created sheet:', sheet_name_1 sheet1.set_panes_frozen(True) sheet1.set_horz_split_pos(7) sheet1.set_vert_split_pos(13) sheet1.set_remove_splits(True) # Add custom colours xlwt.add_palette_colour("custom_colour_1", 0x28) book.set_colour_RGB(0x28, 0, 112, 192) xlwt.add_palette_colour("custom_colour_2", 0x21) book.set_colour_RGB(0x21, 0, 146, 0) xlwt.add_palette_colour("custom_colour_3", 0x22) book.set_colour_RGB(0x22, 234, 244, 233) xlwt.add_palette_colour("custom_colour_4", 0x23) book.set_colour_RGB(0x23, 255, 255, 255) xlwt.add_palette_colour("custom_colour_5", 0x24) book.set_colour_RGB(0x24, 209, 231, 208) xlwt.add_palette_colour("custom_colour_6", 0x25)
def putDataXL(offRow, offCol, TProblem, TUtility, TAvailability): book = xlwt.Workbook() # add new colour to palette and set RGB colour value xlwt.add_palette_colour("sky_blue_10", 0x21) book.set_colour_RGB(0x21, 153,204,255) xlwt.add_palette_colour("blue_classic", 0x22) book.set_colour_RGB(0x22, 207,231,245) xlwt.add_palette_colour("hijau_tua", 0x23) book.set_colour_RGB(0x23, 0,204,0) xlwt.add_palette_colour("hijau_muda", 0x24) book.set_colour_RGB(0x24, 153,255,153) xlwt.add_palette_colour("kuning", 0x25) book.set_colour_RGB(0x25, 255,255,0) xlwt.add_palette_colour("merah", 0x26) book.set_colour_RGB(0x26, 255,51,51) sheet1 = book.add_sheet('PIC', cell_overwrite_ok = True) sheet1.row(0).height_mismatch = True sheet1.row(0).height = 360 styleTitle = 'pattern: pattern solid, fore_colour white;' styleTitle += 'align: vertical center, horizontal center, wrap on;' styleTitle += 'font: name Tahoma, height 280, bold 1;' sheet1.write_merge(offRow, offRow, offCol, offCol+19, 'ATM PRO ' + RegionName + ' PER CLUSTER' , xlwt.easyxf(styleTitle)) shiftDown = 1 sheet1.row(1).height_mismatch = True sheet1.row(1).height = 360 sheet1.write_merge(offRow+shiftDown, offRow+shiftDown, offCol, offCol+19, 'posisi tanggal ' +time.strftime("%d/%m/%Y-%H:%M") , xlwt.easyxf(styleTitle)) contentAlignmentHorz = ["center", "right", "center", "center", "center", "center", "center", "center", "center"] def styler(strColor, fontHeight): styleHeader = 'pattern: pattern solid, fore_colour '+strColor+';' styleHeader += 'align: vertical center, horizontal center, wrap on;' styleHeader += 'borders: top thin, bottom thin;' styleHeader += 'font: name Tahoma, height '+str(fontHeight)+', bold 1;' return styleHeader def makeHeader(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+7, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "NOP", "RSK", "OPS", "OOS", "OFF", "JML"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) # TABULASI RANKING PROBLEM ---------------------------------------------------- makeHeader(offRow, offCol, 'BY PROBLEM') sheet1.col(offCol+0).width = 5*315 sheet1.col(offCol+1).width = 22*315 sheet1.col(offCol+2).width = 4*315 sheet1.col(offCol+3).width = 4*315 sheet1.col(offCol+4).width = 4*315 sheet1.col(offCol+5).width = 4*315 sheet1.col(offCol+6).width = 4*315 sheet1.col(offCol+7).width = 4*315 sheet1.col(offCol+8).width = 8*315 shiftDownSeparator = 0 headRow = [] for i in range (0, len(TProblem)): if TProblem[i-1][8] != TProblem[i][8]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz center' shiftDownSeparator +=1 headRow.append(i+shiftDownSeparator+5) sepStyle = xlwt.easyxf(separatorStyle) r = i+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol sheet1.write(r, c, "", sepStyle) sheet1.write(r, c+1, TProblem[i][8].upper(), sepStyle) for j in range(0,len(TProblem[i])-1): strColor = colorProblem(TProblem[i][7]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[j] style = xlwt.easyxf(contentStyle) cellContent = TProblem[i][j] if j == len(TProblem[i])-1: cellContent = Formula("SUM(C"+str(i+shiftDownSeparator+5)+":H"+str(i+shiftDownSeparator+5)+")") if cellContent == 0: cellContent = '-' sheet1.write(i+offRow+4*shiftDown+shiftDownSeparator, j+offCol, cellContent, style) lastRow = i+offRow+4*shiftDown+shiftDownSeparator+3 headRow.append(lastRow) # PROBLEM ---------------------------------------------------- for k in range (0, len(headRow)-1): sheet1.write(headRow[k]-2, c+2, Formula("SUM(C"+str(headRow[k])+":C"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+3, Formula("SUM(D"+str(headRow[k])+":D"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+4, Formula("SUM(E"+str(headRow[k])+":E"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+5, Formula("SUM(F"+str(headRow[k])+":F"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+6, Formula("SUM(G"+str(headRow[k])+":G"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, c+7, Formula("SUM(H"+str(headRow[k])+":H"+str(headRow[k+1]-2)+")"), sepStyle) # PROBLEM ---------------------------------------------------- shiftLeft = 9 def makeHeader2(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+5, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "UP", "TUNAI", "NON", "%"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader2(offRow, offCol+shiftLeft, "BY UTILITY") sheet1.col(offCol+shiftLeft-1).width = 2*315 sheet1.col(offCol+shiftLeft+0).width = 5*315 sheet1.col(offCol+shiftLeft+1).width = 22*315 sheet1.col(offCol+shiftLeft+2).width = 5*315 sheet1.col(offCol+shiftLeft+3).width = 6*315 sheet1.col(offCol+shiftLeft+4).width = 4*315 sheet1.col(offCol+shiftLeft+5).width = 5*315 shiftDownSeparator = 0 headRow = [] for k in range (0, len(TUtility)): if TUtility[k-1][6] != TUtility[k][6]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz center' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = k+offRow+4*shiftDown+shiftDownSeparator-1 headRow.append(r+2) c = offCol+shiftLeft sheet1.write(r, shiftLeft, "", sepStyle) sheet1.write(r, shiftLeft+1, TUtility[k][6].upper(), sepStyle) sheet1.write(r, shiftLeft, "", sepStyle) sheet1.write(r, shiftLeft, "", sepStyle) for l in range(0,len(TUtility[k])-1): strColor = colorUtility(TUtility[k][5]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[l] cellContent = TUtility[k][l] if cellContent == 0: cellContent = '-' style = xlwt.easyxf(contentStyle) sheet1.write(k+offRow+4*shiftDown+shiftDownSeparator, l+offCol+shiftLeft, cellContent, style) lastRow = k+offRow+4*shiftDown+shiftDownSeparator+3 headRow.append(lastRow) #print headRow, len(headRow) # UTILITY ---------------------------------------------------- for k in range (0, len(headRow)-1): sheet1.write(headRow[k]-2, shiftLeft+2, Formula("SUM(L"+str(headRow[k])+":L"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, shiftLeft+3, Formula("SUM(M"+str(headRow[k])+":M"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, shiftLeft+4, Formula("SUM(N"+str(headRow[k])+":N"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, shiftLeft+5, Formula("(M"+str(headRow[k]-1)+"/L"+str(headRow[k]-1)+")*100"), xlwt.easyxf(separatorStyle,num_format_str= '0.00')) # AVAILABILITY ---------------------------------------------------- shiftLeft = 16 def makeHeader3(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+3, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "ATM", "%"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader3(offRow, offCol+shiftLeft, "BY AVAILABILITY") #print TAvailability sheet1.col(offCol+shiftLeft-1).width = 2*315 sheet1.col(offCol+shiftLeft+0).width = 5*315 sheet1.col(offCol+shiftLeft+1).width = 22*315 sheet1.col(offCol+shiftLeft+2).width = 5*315 sheet1.col(offCol+shiftLeft+3).width = 7*315 sheet1.col(offCol+shiftLeft+4).width = 8*315 shiftDownSeparator = 0 headRow = [] for m in range (0, len(TAvailability)): if TAvailability[m-1][4] != TAvailability[m][4]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz center' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = m+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol+shiftLeft headRow.append(r+2) sheet1.write(r, c, "", sepStyle) sheet1.write(r, c+1, TAvailability[m][4].upper(), sepStyle) tempSum = 0 for n in range(0,len(TAvailability[m])-1): strColor = colorAvail(TAvailability[m][3]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[n] style = xlwt.easyxf(contentStyle) sheet1.write(m+offRow+4*shiftDown+shiftDownSeparator, n+offCol+shiftLeft, TAvailability[m][n], style) strFormula = "PRODUCT(S" + str(m+offCol+shiftLeft+shiftDownSeparator-shiftLeft+5)+ ":T" +str(m+offCol+shiftLeft+shiftDownSeparator-shiftLeft+5)+")" sheet1.write(m+offRow+4*shiftDown+shiftDownSeparator, shiftLeft+4, Formula(strFormula), xlwt.easyxf('font: color white;')) lastRow = m+offRow+4*shiftDown+shiftDownSeparator+3 headRow.append(lastRow) # AVAILABILITY ---------------------------------------------------- for k in range (0, len(headRow)-1): sheet1.write(headRow[k]-2, shiftLeft+2, Formula("SUM(S"+str(headRow[k])+":S"+str(headRow[k+1]-2)+")"), sepStyle) sheet1.write(headRow[k]-2, shiftLeft+3, Formula("U"+str(headRow[k]-1)+"/S"+str(headRow[k]-1)), xlwt.easyxf(separatorStyle,num_format_str= '0.00')) sheet1.write(headRow[k]-2, shiftLeft+4, Formula("SUM(U"+str(headRow[k])+":U"+str(headRow[k+1]-2)+")"), xlwt.easyxf('font: color white;') ) headRow.append(lastRow) namaFileXLS = prepareDirectory("OUTPUT") + RegionName + "-ATM PRO PER CLUSTER-" +time.strftime("%Y%m%d-%H")+'.xls' book.save(namaFileXLS) # MATHEMATICAL CALCULATION ---------------------------------------------------- UP, TUNAI, NONTUNAI, PERCENT = 0, 0, 0, 0 for i in range(0,len(TUtility)): clusterName = TUtility[i][-1].upper() if clusterName == "Jakbar".upper(): UP += TUtility[i][2] TUNAI += TUtility[i][3] NONTUNAI += TUtility[i][4] PERCENT = float(float(TUNAI)/float(UP)*100) print "NOP=", UP, TUNAI, NONTUNAI, "{0:.2}".format(PERCENT)
import requests from bs4 import BeautifulSoup as BS import xlwt, os.path, subprocess path = r"http://systems.confluence.igt.com/display/SCEP/Issue+Tracking+List+for+RR+4.0+Player+Progress?focusedCommentId=34021577#comment-34021577" content = requests.get(path).content.decode() workbook = xlwt.Workbook() sheet = workbook.add_sheet("Random Riches Tracking List") soup = BS(content, "lxml") # setup the column width colWidth = [8, 44, 128, 12, 12, 44, 12] # define a custom colour xlwt.add_palette_colour("custom_colour", 0x21) workbook.set_colour_RGB(0x21, 251, 228, 228) safe_tags = ["closed", "do not fix",'do not fixenhancement', "invaild defect", "invalid defect"] danger_tags = ["release to pa", "released to pa"] # will hide the row only_showContent = True for j, row in enumerate(soup.find_all('table')[0].tbody.findAll('tr')): isContent = (row.find_all('td')[3].get_text()).strip().lower() == "content" isContent = True # \xa0 is actually non-breaking space in Latin1 (ISO 8859-1), also chr(160). You should replace it with a space. string = string.replace(u'\xa0', u' ') tag = row.find_all('td')[4].get_text().strip().lower().replace("\xa0"," ") shouldHighlight = (j>0 and isContent and not (tag in safe_tags))
def export_excel(story_map, file_name, title=None): colors = Story.objects.filter( theme__story_map=story_map ).order_by('color').values("color") # we could use distinct here but as we want to support # sqlite, we use list(set(x)) instead. color_list = [c['color'] for c in colors] color_list = list(set(color_list)) file_name = u"{0}.xls".format(slugify(file_name)) response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = \ 'attachment; filename="{0}"'.format(file_name) book = Workbook() i = 0 ix = 0x21 for c in color_list: add_palette_colour("board_color_{0}".format(i), ix) color = Color(c) book.set_colour_RGB(ix, int(color.r * 255), int(color.g * 255), int(color.b * 255)) ix += 1 i += 1 header_style = easyxf( 'font: name Arial, bold True, height 250;' 'borders: bottom thin;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', ) sheet1 = book.add_sheet('Board') row = 0 row_head = sheet1.row(row) themes = story_map.themes.all() index = 1 for theme in themes: row_head.write(index, theme.name, header_style) sheet1.col(index).width = 5000 index += 1 phase_style = easyxf( 'alignment: horizontal center, vertical center;' 'font: bold True;' 'border: right thin, top thin;' 'pattern: pattern solid, fore_colour gray25;' ) row += 1 for phase in story_map.phases.all(): max_stories = 1 index = 1 for theme in themes: stories = phase.stories.filter(theme=theme).all() row_index = row for story in stories: color = "board_color_{0}".format(color_list.index(story.color)) style = easyxf( 'alignment: wrap True, horizontal center, vertical top;' 'border: left thin, top thin, right thin, bottom thin;' 'pattern: pattern solid, fore_colour {0};'.format(color) ) sheet1.write(row_index, index, story.title, style) row_index += 1 index += 1 max_stories = max(max_stories, len(stories)) index = 0 # write theme name sheet1.write_merge(row, (row + max_stories - 1), index, index, phase.name, phase_style) row += max_stories book.save(response) return response
def informesFacturacion(request): now = datetime.datetime.now() mes=now.month template = 'discriminacion_iva.html' if request.method == "GET": if request.GET.get('fecha'): mes = request.GET.get('fecha')[-2:] # anio = int(request.POST.get('anio')) #now = datetime.datetime(anio,mes,1) if request.method == "POST": if request.POST.get('fecha'): mes = request.GET.get('fecha')[-2:] facturas_recibidas = Registro_factura.objects.filter(fecha_registro__month = mes) xlwt.add_palette_colour("color_neto", 0x21) xlwt.add_palette_colour("color_iva", 0x22) xlwt.add_palette_colour("color_percepciones", 0x23) xlwt.add_palette_colour("color_total", 0x24) estilo_general = xlwt.easyxf('font: name Arial, colour black, bold off; align: wrap on, vert centre, horiz center') estilo_empresa = xlwt.easyxf('font: name Arial, colour black, bold off; align: wrap on, horiz left') estilo_fecha = xlwt.easyxf('',num_format_str='DD-MMM-YY') estilo_moneda = xlwt.easyxf('font: name Arial, colour black, bold off; align: wrap on, vert centre, horiz center',num_format_str='$#,##0.00') borders = xlwt.Borders() borders.left = 2 borders.right = 2 borders.top = 1 borders.bottom = 1 estilo_general.borders = borders estilo_empresa.borders = borders estilo_fecha.borders = borders estilo_moneda.borders = borders # wb = xlwt.Workbook() # ws = wb.add_sheet('FACTURAS',cell_overwrite_ok=True) # i = 3 # for fr in facturas_recibidas: # ws.write(i, 2, fr.fecha, style1) # # ws.write(i, 3, fr.emisor, style1) # ws.write(i, 4, fr.nro_factura, style0) # ws.write(i, 5, fr.total(), style0) # i = i + 1 # ws.write(0, 0, 'Test', style0) # ws.write(1, 0, datetime.datetime.now(), style1) # ws.write(2, 0, 4) # ws.write(2, 1, 1) # ws.write(2, 2, xlwt.Formula("A3+B3")) rb = open_workbook("sistema_inges/"+settings.STATIC_URL+'/xls/plantilla/plantilla.xls',formatting_info=True) wb = copy(rb) wb.set_colour_RGB(0x22, 41, 117, 217) wb.set_colour_RGB(0x21, 242, 221, 64) wb.set_colour_RGB(0x23, 115, 76, 65) wb.set_colour_RGB(0x24, 56, 166, 62) estilo_neto = xlwt.easyxf('font: name Arial, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour color_neto') # 11 * 20, for 11 point estilo_iva = xlwt.easyxf('font: name Arial, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour color_iva') estilo_percepciones = xlwt.easyxf('font: name Arial, bold on, height 180; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour color_percepciones') estilo_total = xlwt.easyxf('font: name Arial, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour color_total') bordersTit = xlwt.Borders() bordersTit.left = 2 bordersTit.right = 2 bordersTit.top = 2 bordersTit.bottom = 2 estilo_neto.borders = bordersTit estilo_iva.borders = bordersTit estilo_percepciones.borders = bordersTit estilo_total.borders = bordersTit ws = wb.get_sheet(0) ws.write(2, 4, "SUBTOTAL (NETO)", estilo_neto) ws.write(2, 5, "IVA", estilo_iva) ws.write(2, 6, "PERCEPCIONES OTROS", estilo_percepciones) ws.write(2, 7, "TOTAL", estilo_total) ws.write(0, 3, ARRAY_MESES[mes]+' '+now.strftime("%Y"), estilo_general) i = 3 for fr in facturas_recibidas: ws.write(i, 1, fr.fecha, estilo_fecha) ws.write(i, 2, fr.emisor.nombre, estilo_empresa) ws.write(i, 3, fr.nro_factura, estilo_general) ws.write(i, 4, fr.subtotal, estilo_moneda) ws.write(i, 5, fr.impuesto(), estilo_moneda) ws.write(i, 6, fr.percepciones_otros, estilo_moneda) ws.write(i, 7, fr.total(), estilo_moneda) i = i + 1 estilo_neto = xlwt.easyxf('font: name Arial, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour color_neto',num_format_str='$#,##0.00') # 11 * 20, for 11 point estilo_iva = xlwt.easyxf('font: name Arial, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour color_iva',num_format_str='$#,##0.00') estilo_percepciones = xlwt.easyxf('font: name Arial, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour color_percepciones',num_format_str='$#,##0.00') estilo_total = xlwt.easyxf('font: name Arial, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour color_total',num_format_str='$#,##0.00') ws.write(106, 4, xlwt.Formula("SUM(E4:E105)"), estilo_neto) ws.write(106, 5, xlwt.Formula("SUM(F4:F105)"), estilo_iva) ws.write(106, 6, xlwt.Formula("SUM(G4:G105)"), estilo_percepciones) ws.write(106, 7, xlwt.Formula("SUM(H4:H105)"), estilo_total) fechaXLS = "InformeSI_"+str(now.day) +"-"+ str(now.month) +"-"+ str(now.year) + ".xls" wb.save("sistema_inges/"+settings.STATIC_URL+'/xls/' + fechaXLS) return HttpResponseRedirect(settings.STATIC_URL+'/xls/' + fechaXLS) facturas_emitidas = Emision_factura.objects.filter(fecha_registro__month = mes) facturas_recibidas = Registro_factura.objects.filter(fecha_registro__month = mes) # albaranes_recibidos = Recibo.objects.filter(fecha_registro__month = mes) facturacion_iva = 0 descuento_iva = 0 total_ingresos = 0 subtotal_ingresos =0 f_r_civa = 0 f_r_siva = 0 percep_otros = 0 totalAlb = 0 for f in facturas_emitidas: descuento_iva = descuento_iva + f.impuesto() total_ingresos = total_ingresos + f.total() # subtotal_ingresos = subtotal_ingresos + f.subtotal subtotal_ingresos = subtotal_ingresos + (total_ingresos - descuento_iva) for fr in facturas_recibidas: facturacion_iva = facturacion_iva + fr.impuesto() f_r_civa = f_r_civa + fr.total() f_r_siva = f_r_siva + fr.subtotal() # percep_otros = percep_otros + fr.percepciones_otros # for ar in albaranes_recibidos: # totalAlb = totalAlb + ar.total gasto_c_iva = f_r_civa + totalAlb gasto_s_iva = f_r_siva + totalAlb return render(request, template,{'request': request, 'fact_iva': facturacion_iva, 'title': 'Informes', 'desc_iva': descuento_iva, 'gastoCIva': gasto_c_iva, 'gastoSIva': gasto_s_iva, 'mespy': mes, 'detalleFacturasR': facturas_recibidas, 'detalleFacturasE': facturas_emitidas, #'detalleAlbaranesR': albaranes_recibidos, 'percep_otros': percep_otros, 'totalAlb': totalAlb, 'total_ingresos': total_ingresos, 'subtotal_ingresos': subtotal_ingresos, 'f_r_civa': f_r_civa, 'f_r_siva': f_r_siva}, context_instance=RequestContext(request))
#========================================================================================= # add this location to PYTHONPATH variable sys.path.append("//00.00.00.000/shotgunpro/regular") modOffice = imp.load_source('office365','//00.00.00.000/shotgunpro/regular/office365.py') reload(modOffice) off = modOffice.office365() modFunc = imp.load_source('getModules','//00.00.00.000/shotgunpro/regular/getModules.py') reload(modFunc) #========================================================================================= xlwt.add_palette_colour("blue_x30", 0x30) # replaces "cool blue" xlwt.add_palette_colour("periwinkle_x18", 0x18) # replaces "periwinkle" xlwt.add_palette_colour("white_x09", 0x09) # replaces "white" xlwt.add_palette_colour("blueish", 0x2c) # replaces "" xlwt.add_palette_colour("green_x39", 0x32) # replaces "quiet green" xlwt.add_palette_colour("aqua_x31", 0x31) # replaces "aqua" HEADER_CELL = xlwt.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 blue_x30, pattern_back_colour blue_x30' ) TASK_CELL = xlwt.easyxf( 'font: bold 0, name Tahoma, height 160;'
def generate_comparison(list_experiments): row = easyxf('pattern: pattern solid, fore_colour blue') col = easyxf('pattern: pattern solid, fore_colour green') header = easyxf('font: bold True; pattern: pattern solid, fore_colour dark_red') book = Workbook() xlwt.add_palette_colour("dark_red", 0x21) book.set_colour_RGB(0x21, 150, 0, 0) attributes = ['AVG_DEV','MIN_DEV','MAX_DEV','VAR_DEV','FITNESS'] overview_sheets = [] for attr in attributes: sheet1 = book.add_sheet("Overview %s" % attr,cell_overwrite_ok=True) overview_sheets.append(sheet1) #from random import randint #for x in xrange(1, num_uavs+1): #for y in xrange(1, num_samples+1): #style = None #val = x*y #if val < 25: #style = easyxf('pattern: pattern solid, fore_colour green') #elif val < 50: #style = easyxf('pattern: pattern solid, fore_colour white') #elif val < 75: #style = easyxf('pattern: pattern solid, fore_colour orange') #else: #style = easyxf('pattern: pattern solid, fore_colour red') #sheet1.write(x,y,str(val),style) num_uavs = 0 num_steps = 0 for experiment in list_experiments: exp_num_uavs = experiment.config.getint(GLOBAL_SECTION,"num_receivers") exp_num_steps = experiment.config.getint(GENOTYPE_SECTION,"receiver_step_count") if num_uavs < exp_num_uavs: num_uavs = exp_num_uavs if num_steps < exp_num_steps: num_steps = exp_num_steps best_phenome = experiment.get_phenome(100,0,"FITNESS",True) if best_phenome is None: continue for attr, sheet in zip(attributes, overview_sheets): value = best_phenome[attr] value = round(value,2) cell_value = None image_path = os.path.join(experiment.experiment_folder, "figure_best_predictions", "generation_100.jpg") image_path_abs = os.path.join(os.path.dirname(os.path.abspath(__file__)), image_path) cell_value = Formula('HYPERLINK("file://' + image_path_abs + '";' + str(value) + ')') sheet.write(exp_num_steps,exp_num_uavs,cell_value) for sheet in overview_sheets: sheet.write(0,0,"# samples/UAVs",header) for x in xrange(1,num_steps+1): for sheet in overview_sheets: sheet.write(x,0,str(x),header) for y in xrange(1,num_uavs+1): for sheet in overview_sheets: sheet.write(0,y,str(y),header) #c = 0 #for experiment in list_experiments: #sheet = book.add_sheet(str(c),cell_overwrite_ok=True) #for gen in xrange(11): #image_path = os.path.join(experiment.experiment_folder, "figure_best_predictions", "generation_" + str(max(gen*10,1)) + ".jpg") #import Image #try: #im = Image.open("./" + image_path) ##open_image(image_path) #im.save("temp.bmp", "BMP") #except: #continue #sheet.insert_bitmap("temp.bmp", gen*17+1, 0, scale_x = 0.5, scale_y= 0.5) #c += 1 book.save(os.path.join(experiment_base_folder,'overview.xls'))
def putDataXL(offRow, offCol, TProblem, TUtility, TAvailability): book = xlwt.Workbook() # add new colour to palette and set RGB colour value xlwt.add_palette_colour("sky_blue_10", 0x21) book.set_colour_RGB(0x21, 153,204,255) xlwt.add_palette_colour("blue_classic", 0x22) book.set_colour_RGB(0x22, 207,231,245) xlwt.add_palette_colour("hijau_tua", 0x23) book.set_colour_RGB(0x23, 0,204,0) xlwt.add_palette_colour("hijau_muda", 0x24) book.set_colour_RGB(0x24, 153,255,153) xlwt.add_palette_colour("kuning", 0x25) book.set_colour_RGB(0x25, 255,255,0) xlwt.add_palette_colour("merah", 0x26) book.set_colour_RGB(0x26, 255,51,51) sheet1 = book.add_sheet('PERINGKAT ATM', cell_overwrite_ok = True) sheet1.row(0).height_mismatch = True sheet1.row(0).height = 360 styleTitle = 'pattern: pattern solid, fore_colour white;' styleTitle += 'align: vertical center, horizontal center, wrap on;' styleTitle += 'font: name Tahoma, height 280, bold 1;' sheet1.write_merge(offRow, offRow, offCol, offCol+22, 'ATM PRO ' + RegionName + ' PER PIC' , xlwt.easyxf(styleTitle)) shiftDown = 1 sheet1.row(1).height_mismatch = True sheet1.row(1).height = 360 sheet1.write_merge(offRow+shiftDown, offRow+shiftDown, offCol, offCol+22, 'posisi tanggal ' +time.strftime("%d/%m/%Y-%H:%M") , xlwt.easyxf(styleTitle)) contentAlignmentHorz = ["center", "right", "center", "center", "center", "center", "center", "center", "center"] def styler(strColor, fontHeight): styleHeader = 'pattern: pattern solid, fore_colour '+strColor+';' styleHeader += 'align: vertical center, horizontal center, wrap on;' styleHeader += 'borders: top thin, bottom thin;' styleHeader += 'font: name Tahoma, height '+str(fontHeight)+', bold 1;' return styleHeader def makeHeader(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+8, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "NOP", "RSK", "OPS", "OOS", "OFF", "PRO", "PIC"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) # TABULASI RANKING PROBLEM ---------------------------------------------------- makeHeader(offRow, offCol, 'BY PROBLEM') sheet1.col(offCol+0).width = 5*315 sheet1.col(offCol+1).width = 22*315 sheet1.col(offCol+2).width = 4*315 sheet1.col(offCol+3).width = 4*315 sheet1.col(offCol+4).width = 4*315 sheet1.col(offCol+5).width = 4*315 sheet1.col(offCol+6).width = 4*315 sheet1.col(offCol+7).width = 4*315 sheet1.col(offCol+8).width = 8*315 shiftDownSeparator = 0 for i in range (0, len(TProblem)): if TProblem[i-1][8] != TProblem[i][8]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz left' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = i+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol sheet1.write(r, c, i, sepStyle) sheet1.write_merge(r, r, c, c+8, TProblem[i][8].upper(), sepStyle) for j in range(0,len(TProblem[i])): strColor = colorProblem(TProblem[i][7]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[j] style = xlwt.easyxf(contentStyle) cellContent = TProblem[i][j] if cellContent == 0: cellContent = '-' sheet1.write(i+offRow+4*shiftDown+shiftDownSeparator, j+offCol, cellContent, style) # UTILITY ---------------------------------------------------- shiftLeft = 10 def makeHeader2(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+6, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "UP", "TUNAI", "NON", "%", "PIC"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader2(offRow, offCol+shiftLeft, "BY UTILITY") sheet1.col(offCol+shiftLeft-1).width = 2*315 sheet1.col(offCol+shiftLeft+0).width = 5*315 sheet1.col(offCol+shiftLeft+1).width = 22*315 sheet1.col(offCol+shiftLeft+2).width = 4*315 sheet1.col(offCol+shiftLeft+3).width = 6*315 sheet1.col(offCol+shiftLeft+4).width = 4*315 sheet1.col(offCol+shiftLeft+5).width = 4*315 sheet1.col(offCol+shiftLeft+6).width = 8*315 shiftDownSeparator = 0 for k in range (0, len(TUtility)): if TUtility[k-1][6] != TUtility[k][6]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz left' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = k+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol+shiftLeft sheet1.write(r, c, k, sepStyle) sheet1.write_merge(r, r, c, c+6, TUtility[k][6].upper(), sepStyle) for l in range(0,len(TUtility[k])): strColor = colorUtility(TUtility[k][5]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[l] cellContent = TUtility[k][l] if cellContent == 0: cellContent = '-' style = xlwt.easyxf(contentStyle) sheet1.write(k+offRow+4*shiftDown+shiftDownSeparator, l+offCol+shiftLeft, cellContent, style) # AVAILABILITY ---------------------------------------------------- shiftLeft = 18 def makeHeader3(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+4, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "ATM", "%", "PIC"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader3(offRow, offCol+shiftLeft, "BY AVAILABILITY") print TAvailability sheet1.col(offCol+shiftLeft-1).width = 2*315 sheet1.col(offCol+shiftLeft+0).width = 5*315 sheet1.col(offCol+shiftLeft+1).width = 22*315 sheet1.col(offCol+shiftLeft+2).width = 4*315 sheet1.col(offCol+shiftLeft+3).width = 7*315 sheet1.col(offCol+shiftLeft+4).width = 8*315 shiftDownSeparator = 0 for m in range (0, len(TAvailability)): if TAvailability[m-1][4] != TAvailability[m][4]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz left' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = m+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol+shiftLeft sheet1.write(r, c, m, sepStyle) sheet1.write_merge(r, r, c, c+4, TAvailability[m][4].upper(), sepStyle) for n in range(0,len(TAvailability[m])): strColor = colorAvail(TAvailability[m][3]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[n] style = xlwt.easyxf(contentStyle) sheet1.write(m+offRow+4*shiftDown+shiftDownSeparator, n+offCol+shiftLeft, TAvailability[m][n], style) book.save(RegionName + "-ATM PRO PER PIC-" +time.strftime("%Y%m%d-%H")+'.xls')
def img2xls(img_path, xls_path): # Load image. im = Image.open(img_path) im = im.convert('RGB') width, height = im.size # Create Table. book = xlwt.Workbook() # Create new sheet with valid name. sheet1 = book.add_sheet(re.sub('[^\.0-9a-zA-Z]+', '', os.path.basename(img_path))) # Scale image down if needed. if width > 256 or height > 256: factor = 256.0 / max(width, height) im = im.resize((int(factor * width), int(factor * height)), Image.BILINEAR) width, height = im.size # Reduce image colors. colCnt = 63-8 # Excel does not allow more custom colors. palImg = im.convert('P', palette=Image.ADAPTIVE, colors=colCnt) # Pixel access to image. pix = im.load() palPix = palImg.load() # Gather color values. palVals = set() for y in range(height): for x in range(width): palVals.add(palPix[x,y]) # Generate an index for every color. pallookup = {} for idx, val in enumerate(palVals, start=8): pallookup[val] = idx # Generate custom palette for Excel def gen_col_name(idx): return "custom_colour_" + str(idx) alreadyHadCol = set() for y in range(height): for x in range(width): palcolnum = pallookup[palPix[x,y]] if palcolnum in alreadyHadCol: continue alreadyHadCol.add(palcolnum) r, g, b = pix[x,y] xlwt.add_palette_colour(gen_col_name(palcolnum), palcolnum) book.set_colour_RGB(palcolnum, r, g, b) # Generate cell styles with custom colors. style_lookup = {} for key, val in pallookup.items(): style = xlwt.easyxf( 'pattern: pattern solid, fore_colour ' + gen_col_name(val)) style.pattern.pattern_fore_colour = val style_lookup[val] = style # Scale table cells. maxEdge = max(width, height) colWidth = int(25000 / maxEdge) rowHeight = int(10000 / maxEdge) for x in range(width): col = sheet1.col(x).width = colWidth for y in range(height): row = sheet1.row(y).height = rowHeight # Set cell colors. for y in range(height): for x in range(width): palcolnum = pallookup[palPix[x,y]] style = style_lookup[palcolnum] sheet1.write(y, x, ' ', style) # Save finished work of art. book.save(xls_path) print( 'saved', xls_path )
def generateExcelFile(self): model_hr_attendance_detail_company = self.env['hr.attendance.detail.company'].search([('attendance_company_id','=', self.id)]) str_dir = constants.GenXUtils.get_data_dir_excel_template int_rowindex = 6 workbook_xlrd = xlrd.open_workbook(str_dir + 'Attendance 2.xls', formatting_info=True) workbook = copy(workbook_xlrd) sheet_xlrd = workbook_xlrd.sheet_by_index(0) sheet = workbook.get_sheet(0) xlwt.add_palette_colour("custom_colour", 0x21) workbook.set_colour_RGB(0x21, 196, 215, 155) alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_LEFT alignment.vert = xlwt.Alignment.VERT_CENTER alignment.Wrap = 1 job_background_color = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour') job_background_color.alignment = alignment job_background_color.alignment.wrap = 1 #border 1 border = xlwt.Borders() border.bottom = xlwt.Borders.THIN border.top = xlwt.Borders.THIN border.left = xlwt.Borders.THIN border.right = xlwt.Borders.THIN job_background_color.borders = border job_background_color.font.bold =True for hr_attendance_detail_main in model_hr_attendance_detail_company: #Title sheet.write(1,2,self.company_id.name) sheet.write(2,2,str(hr_attendance_detail_main.hr_payroll_attendance_id.schedule_datefrom) + ' - ' + str(hr_attendance_detail_main.hr_payroll_attendance_id.schedule_dateto)) model_attendances = self.env['hr.payroll.attendance'].search([('employee_attendance_child_id','=',hr_attendance_detail_main.hr_payroll_attendance_id.id)]) model_attendance = model_attendances.sorted(key=lambda r: r.employee_sequence) sheet.write_merge(int_rowindex,int_rowindex,0,15,hr_attendance_detail_main.assign_projects.name,job_background_color) int_rowindex +=1 if len(model_attendance) > 0: styleColumns =xlwt.XFStyle() border = xlwt.Borders() border.bottom = xlwt.Borders.THIN border.top = xlwt.Borders.THIN border.left = xlwt.Borders.THIN border.right = xlwt.Borders.THIN styleColumns.borders = border for employee in model_attendance: fontData = xlwt.Font() if employee.has_a_reliever == True: fontData.colour_index = 0x0A styleColumns.font =fontData sheet.write_merge(int_rowindex,int_rowindex,0,2,employee.employee_id.last_name + ',' + employee.employee_id.first_name,styleColumns) elif employee.is_reliever == True: fontData.colour_index = 0x0C styleColumns.font =fontData sheet.write_merge(int_rowindex,int_rowindex,0,2, '**' + employee.employee_reliever.last_name + ',' + employee.employee_reliever.first_name,styleColumns) else: fontData.colour_index = 0x08 styleColumns.font =fontData sheet.write_merge(int_rowindex,int_rowindex,0,2,employee.employee_id.last_name + ',' + employee.employee_id.first_name,styleColumns) sheet.write(int_rowindex,3,employee.regular_days_work,styleColumns) sheet.write(int_rowindex,4,employee.absent,styleColumns) sheet.write(int_rowindex,5,employee.tardiness,styleColumns) sheet.write(int_rowindex,6,employee.straight_duty,styleColumns) sheet.write(int_rowindex,7,employee.night_differential,styleColumns) sheet.write(int_rowindex,8,employee.regular_overtime,styleColumns ) sheet.write(int_rowindex,9,employee.rest_day_work,styleColumns) sheet.write(int_rowindex,10,employee.restday_overtime,styleColumns) sheet.write(int_rowindex,11,employee.special_day_work,styleColumns) sheet.write(int_rowindex,12,employee.special_overtime,styleColumns) sheet.write(int_rowindex,13,employee.legal_holiday_day,styleColumns) sheet.write(int_rowindex,14,employee.holiday_day_work,styleColumns) sheet.write(int_rowindex,15,employee.holiday_overtime,styleColumns) int_rowindex +=1 fp = StringIO() workbook.save(fp) fp.seek(0) data_read = fp.read() fp.close() byte_arr = base64.b64encode(data_read) self.attendance_file = byte_arr
def putDataXL(offRow, offCol, merchant, brilinks, jenisPersen): book = xlwt.Workbook() # add new colour to palette and set RGB colour value xlwt.add_palette_colour("sky_blue_10", 0x21) book.set_colour_RGB(0x21, 153,204,255) xlwt.add_palette_colour("blue_classic", 0x22) book.set_colour_RGB(0x22, 207,231,245) xlwt.add_palette_colour("hijau_tua", 0x23) book.set_colour_RGB(0x23, 0,204,0) xlwt.add_palette_colour("hijau_muda", 0x24) book.set_colour_RGB(0x24, 153,255,153) xlwt.add_palette_colour("kuning", 0x25) book.set_colour_RGB(0x25, 255,255,0) xlwt.add_palette_colour("merah", 0x26) book.set_colour_RGB(0x26, 255,51,51) sheet1 = book.add_sheet(jenisPersen, cell_overwrite_ok = True) #sheet1 = book.add_sheet(jenisPersen) sheet1.row(0).height_mismatch = True sheet1.row(0).height = 360 styleTitle = 'pattern: pattern solid, fore_colour white;' styleTitle += 'align: vertical center, horizontal center, wrap on;' styleTitle += 'font: name Tahoma, height 280, bold 1;' sheet1.write_merge(offRow, offRow, offCol, offCol+12, jenisPersen + ' EDC PRO PER PIC ' + RegionName , xlwt.easyxf(styleTitle)) shiftDown = 1 sheet1.row(1).height_mismatch = True sheet1.row(1).height = 360 sheet1.write_merge(offRow+shiftDown, offRow+shiftDown, offCol, offCol+12, 'posisi tanggal ' +time.strftime("%d/%m/%Y-%H:%M") , xlwt.easyxf(styleTitle)) contentAlignmentHorz = ["center", "right", "center", "center", "left", "center"] def styler(strColor, fontHeight): styleHeader = 'pattern: pattern solid, fore_colour '+strColor+';' styleHeader += 'align: vertical center, horizontal center, wrap on;' styleHeader += 'borders: top thin, bottom thin;' styleHeader += 'font: name Tahoma, height '+str(fontHeight)+', bold 1;' return styleHeader # MERCHANT ---------------------------------------------------- def makeHeader(xRow, yCol, jenisEDC, jenisPersen): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+5, jenisEDC + ' '+jenisPersen, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "NOP", "EDC", "%", "PIC"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader(offRow, offCol, 'MERCHANT', jenisPersen) shiftDownSeparator = 0 for i in range (0, len(merchant)): if merchant[i-1][5] != brilinks[i][5]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz left' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = i+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol sheet1.write(r, c, i, sepStyle) sheet1.write_merge(r, r, c, c+5, merchant[i][5].upper(), sepStyle) for j in range(0,len(merchant[i])): strColor = colorPercent(merchant[i][4]) jumlahEDC = merchant[i][3] if jumlahEDC == 0: strColor = 'white' contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[j] sheet1.col(offCol+0).width = 6*256 sheet1.col(offCol+1).width = 27*256 sheet1.col(offCol+2).width = 6*256 sheet1.col(offCol+3).width = 6*256 sheet1.col(offCol+4).width = 7*256 style = xlwt.easyxf(contentStyle) sheet1.write(i+offRow+4*shiftDown+shiftDownSeparator, j+offCol, merchant[i][j], style) # BRILINKS ---------------------------------------------------- shiftLeft = 7 makeHeader(offRow, offCol+shiftLeft, 'BRILINK', jenisPersen) shiftDownSeparator = 0 for k in range (0, len(brilinks)): if brilinks[k-1][5] != brilinks[k][5]: separatorStyle = 'borders: top thin, bottom thin;' separatorStyle += 'font: name Tahoma, height 180, bold 1;' separatorStyle += 'pattern: pattern solid, fore_colour white;' separatorStyle += 'align: horiz left' shiftDownSeparator +=1 sepStyle = xlwt.easyxf(separatorStyle) r = k+offRow+4*shiftDown+shiftDownSeparator-1 c = offCol+shiftLeft sheet1.write(r, c, k, sepStyle) sheet1.write_merge(r, r, c, c+5, brilinks[k][5].upper(), sepStyle) for l in range(0,len(brilinks[k])): strColor = colorPercent(brilinks[k][4]) jumlahEDC = brilinks[k][3] contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[l] sheet1.col(offCol+shiftLeft+0).width = 6*256 sheet1.col(offCol+shiftLeft+1).width = 27*256 sheet1.col(offCol+shiftLeft+2).width = 6*256 sheet1.col(offCol+shiftLeft+3).width = 6*256 sheet1.col(offCol+shiftLeft+4).width = 7*256 style = xlwt.easyxf(contentStyle) sheet1.write(k+offRow+4*shiftDown+shiftDownSeparator, l+offCol+shiftLeft, brilinks[k][l], style) book.save(RegionName + "-EDC " +jenisPersen + "-PIC-" +time.strftime("%Y%m%d-%H")+'.xls')
def putDataXL(offRow, offCol, TReliability, TUtility, TAvailability): book = xlwt.Workbook() # add new colour to palette and set RGB colour value xlwt.add_palette_colour("sky_blue_10", 0x21) book.set_colour_RGB(0x21, 153,204,255) xlwt.add_palette_colour("blue_classic", 0x22) book.set_colour_RGB(0x22, 207,231,245) xlwt.add_palette_colour("hijau_tua", 0x23) book.set_colour_RGB(0x23, 0,204,0) xlwt.add_palette_colour("hijau_muda", 0x24) book.set_colour_RGB(0x24, 153,255,153) xlwt.add_palette_colour("kuning", 0x25) book.set_colour_RGB(0x25, 255,255,0) xlwt.add_palette_colour("merah", 0x26) book.set_colour_RGB(0x26, 255,51,51) sheet1 = book.add_sheet('PERINGKAT ATM', cell_overwrite_ok = True) sheet1.set_portrait(False) sheet1.fit_num_pages=1 sheet1.paper_size_code = 1 sheet1.row(0).height_mismatch = True sheet1.row(0).height = 360 styleTitle = 'pattern: pattern solid, fore_colour white;' styleTitle += 'align: vertical center, horizontal center, wrap on;' styleTitle += 'font: name Tahoma, height 280, bold 1;' lastCol = 15 sheet1.write_merge(offRow, offRow, offCol, lastCol, 'ATM PRO ' + regionName , xlwt.easyxf(styleTitle)) shiftDown = 1 sheet1.row(1).height_mismatch = True sheet1.row(1).height = 360 sheet1.write_merge(offRow+shiftDown, offRow+shiftDown, offCol, lastCol, 'posisi tanggal ' +time.strftime("%d/%m/%Y-%H:%M") , xlwt.easyxf(styleTitle)) contentAlignmentHorz = ["center", "right", "center", "center", "center", "center", "center", "center", "center" , "center"] def styler(strColor, fontHeight): styleHeader = 'pattern: pattern solid, fore_colour '+strColor+';' styleHeader += 'align: vertical center, horizontal center, wrap on;' styleHeader += 'borders: top thin, bottom thin;' styleHeader += 'font: name Tahoma, height '+str(fontHeight)+', bold 1;' return styleHeader def makeHeader(xRow, yCol, jenisTabel): arrJudul = ["CODE", "BRANCH", "ATM", "%"] sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+len(arrJudul)-1, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) # TABULASI RANKING RELIABILITY ---------------------------------------------------- makeHeader(offRow, offCol, 'BY RELIABILITY') sheet1.col(offCol+0).width = 5*315 sheet1.col(offCol+1).width = 22*315 sheet1.col(offCol+2).width = 6*315 sheet1.col(offCol+3).width = 8*315 for i in range (0, len(TReliability)): #print len(TReliability[i]) for j in range(0,len(TReliability[i])): strColor = "" strColor = colorRelia(float(TReliability[i][3])) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[j] style = xlwt.easyxf(contentStyle) cellContent = TReliability[i][j] if cellContent == 0: cellContent = '-' sheet1.write(i+offRow+4*shiftDown, j+offCol, cellContent, style) # UTILITY ---------------------------------------------------- shiftLeft = (4) + 1 def makeHeader2(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+5, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "UP", "TUNAI", "NON", "%"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader2(offRow, offCol+shiftLeft, "BY UTILITY") sheet1.col(offCol+shiftLeft-1).width = 2*315 sheet1.col(offCol+shiftLeft+0).width = 5*315 sheet1.col(offCol+shiftLeft+1).width = 22*315 sheet1.col(offCol+shiftLeft+2).width = 6*315 sheet1.col(offCol+shiftLeft+3).width = 6*315 sheet1.col(offCol+shiftLeft+4).width = 6*315 sheet1.col(offCol+shiftLeft+5).width = 7*315 for k in range (0, len(TUtility)): for l in range(0,len(TUtility[k])): strColor = "" strColor = colorUtility(TUtility[k][5]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[l] cellContent = TUtility[k][l] if cellContent == 0: cellContent = '-' style = xlwt.easyxf(contentStyle) sheet1.write(k+offRow+4*shiftDown, l+offCol+shiftLeft, cellContent, style) # AVAILABILITY ---------------------------------------------------- shiftLeft = (4) + 1 + (6) + 1 def makeHeader3(xRow, yCol, jenisTabel): sheet1.write_merge(xRow+2*shiftDown, xRow+2*shiftDown, yCol, yCol+3, 'RANKING ' + jenisTabel, xlwt.easyxf(styler('sky_blue_10', 240))) arrJudul = ["CODE", "BRANCH", "ATM", "%"] for i in range (0, len(arrJudul)): sheet1.write(xRow+3*shiftDown , i+yCol, arrJudul[i], xlwt.easyxf(styler('blue_classic', 180))) makeHeader3(offRow, offCol+shiftLeft, "BY AVAILABILITY") #print TAvailability sheet1.col(offCol+shiftLeft-1).width = 2*315 sheet1.col(offCol+shiftLeft+0).width = 5*315 sheet1.col(offCol+shiftLeft+1).width = 22*315 sheet1.col(offCol+shiftLeft+2).width = 6*315 sheet1.col(offCol+shiftLeft+3).width = 9*315 for m in range (0, len(TAvailability)): for n in range(0,len(TAvailability[m])): strColor = "" strColor = colorAvail(TAvailability[m][3]) contentStyle = 'font: name Tahoma, height 180;' contentStyle += 'pattern: pattern solid, fore_colour '+strColor+';' contentStyle += 'align: horiz '+contentAlignmentHorz[n] style = xlwt.easyxf(contentStyle) sheet1.write(m+offRow+4*shiftDown, n+offCol+shiftLeft, TAvailability[m][n], style) style = xlwt.easyxf(styler('sky_blue_10', 240)) #TOTAL 1 sheet1.write(jumlahKanca+4, 0, '', style) sheet1.write(jumlahKanca+4, 1, 'RELIABILITY', style) sheet1.write(jumlahKanca+4, 2, Formula("SUM(C5:C"+str(jumlahKanca+4)+")"), style) sheet1.write(jumlahKanca+4, 3, Formula("SUM(D5:D"+str(jumlahKanca+4)+")"), style) #TOTAL 2 sheet1.write(jumlahKanca+4, 5, '', style) sheet1.write(jumlahKanca+4, 6, 'UTILITY', style) sheet1.write(jumlahKanca+4, 7, Formula("SUM(H5:H"+str(jumlahKanca+4)+")"), style) sheet1.write(jumlahKanca+4, 8, Formula("SUM(I5:I"+str(jumlahKanca+4)+")"), style) sheet1.write(jumlahKanca+4, 9, Formula("SUM(J5:J"+str(jumlahKanca+4)+")"), style) sheet1.write(jumlahKanca+4, 10, Formula("I"+str(jumlahKanca+5)+"/H"+str(jumlahKanca+5)+"*100"), style) #TOTAL 2 sheet1.write(jumlahKanca+4, 12, '', style) sheet1.write(jumlahKanca+4, 13, 'AVAILABILITY', style) sheet1.write(jumlahKanca+4, 14, Formula("SUM(O5:O"+str(jumlahKanca+4)+")"), style) lastRow = jumlahKanca+5 for i in range(5, lastRow): strFormula = "PRODUCT(C" + str(i)+ ":D"+str(i)+")" sheet1.write(i-1, 4, Formula(strFormula), xlwt.easyxf('font: color white;')) strFormula = "PRODUCT(O" + str(i)+ ":P"+str(i)+")" sheet1.write(i-1, 16, Formula(strFormula), xlwt.easyxf('font: color white;')) style = xlwt.easyxf(styler('sky_blue_10', 240), num_format_str= '0.00') sheet1.write(jumlahKanca+4, 3, Formula("SUM(E5:E"+str(jumlahKanca+4)+")/C"+str(jumlahKanca+5)+""), style ) sheet1.write(jumlahKanca+4, 15, Formula("SUM(Q5:Q"+str(jumlahKanca+4)+")/O"+str(jumlahKanca+5)+""), style ) sheet1.write_merge(lastRow, lastRow, offCol, offCol+15, 'TARGET UTILITY & AVAILABILITY = 99%' , xlwt.easyxf(styleTitle + 'borders: top thin, bottom thin;')) namaFileXLS = prepareDirectory("OUTPUT") + "ATM ALL-" + regionName +time.strftime("-%Y%m%d-%H")+'.xls' book.save(namaFileXLS) os.system('libreoffice '+namaFileXLS.replace(" ","\ ") +' &')