Ejemplo n.º 1
0
 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)
Ejemplo n.º 2
0
    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)
Ejemplo n.º 3
0
    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)
Ejemplo n.º 4
0
    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
Ejemplo n.º 5
0
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])]])
Ejemplo n.º 6
0
    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")
Ejemplo n.º 7
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
Ejemplo n.º 8
0
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) #保存文件   
Ejemplo n.º 9
0
    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
Ejemplo n.º 10
0
    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',
        }
Ejemplo n.º 11
0
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)
Ejemplo n.º 12
0
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')
Ejemplo n.º 13
0
 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)
Ejemplo n.º 14
0
 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)
Ejemplo n.º 15
0
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
Ejemplo n.º 16
0
    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)
Ejemplo n.º 18
0
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')
Ejemplo n.º 19
0
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',
        }
Ejemplo n.º 21
0
 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
Ejemplo n.º 22
0
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
Ejemplo n.º 23
0
 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
Ejemplo n.º 24
0
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!")
Ejemplo n.º 25
0
 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)
Ejemplo n.º 26
0
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")
Ejemplo n.º 27
0
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)
Ejemplo n.º 28
0
    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
Ejemplo n.º 29
0
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")
Ejemplo n.º 30
0
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
Ejemplo n.º 31
0
    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
Ejemplo n.º 32
0
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))
Ejemplo n.º 33
0
    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)
Ejemplo n.º 34
0
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")
Ejemplo n.º 35
0
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
Ejemplo n.º 36
0
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)
Ejemplo n.º 37
0
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')
Ejemplo n.º 38
0
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
Ejemplo n.º 39
0
    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)
Ejemplo n.º 40
0
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')  # 保存文件
Ejemplo n.º 41
0
 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")
Ejemplo n.º 42
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)
Ejemplo n.º 43
0
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)
Ejemplo n.º 46
0
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))
Ejemplo n.º 47
0
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
Ejemplo n.º 48
0
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))
Ejemplo n.º 49
0

#=========================================================================================
# 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;'
Ejemplo n.º 50
0
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'))
Ejemplo n.º 51
0
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')
Ejemplo n.º 52
0
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 )
Ejemplo n.º 53
0
    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
Ejemplo n.º 54
0
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')
Ejemplo n.º 55
0
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(" ","\ ") +' &')