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