def add_styles(xlsx_obj):
if "over_10k_style" not in xlsx_obj.style_names:
over_10k_favorite_color = BLUE
over_10k_favorite_bg_color = "F0E8DD"
over_10k_style = NamedStyle(name="over_10k_style")
over_10k_style.font = Font(color=over_10k_favorite_color)
over_10k_style.fill = PatternFill(
start_color=over_10k_favorite_bg_color,
end_color=over_10k_favorite_bg_color,
fill_type="solid")
xlsx_obj.add_named_style(over_10k_style)
if "over_20k_style" not in xlsx_obj.style_names:
over_20k_favorite_color = RED
over_20k_favorite_bg_color = "D1F5EC"
over_20k_style = NamedStyle(name="over_20k_style")
over_20k_style.font = Font(color=over_20k_favorite_color)
over_20k_style.fill = PatternFill(
start_color=over_20k_favorite_bg_color,
end_color=over_20k_favorite_bg_color,
fill_type="solid")
xlsx_obj.add_named_style(over_20k_style)
def build_styles():
global excel_file
from openpyxl.styles import NamedStyle, Font, PatternFill, Alignment, Border, Side
alignment = Alignment(
horizontal = 'center',
vertical = 'center',
wrap_text = True,
)
border = Border(
left = Side(style = 'thin'),
right = Side(style = 'thin'),
top = Side(style = 'thin'),
bottom = Side(style = 'thin'),
)
top_style = NamedStyle('top_style')
top_style.alignment = alignment
top_style.border = border
top_style.fill = PatternFill('solid', fgColor = "000000")
top_style.font = Font(
bold = True,
color = 'FFFFFF',
name = 'Calibri',
size = 10,
)
excel_file.add_named_style(top_style)
normal_style = NamedStyle('normal_style')
normal_style.alignment = alignment
normal_style.border = border
normal_style.fill = PatternFill('solid', fgColor = "FFFFFF")
normal_style.font = Font(
color = '000000',
name = 'Calibri',
size = 10,
)
excel_file.add_named_style(normal_style)
def paint_columns(ws, longHeader, lenData, num_col, row=11):
rowPar2 = NamedStyle(name="rowPar2")
rowPar2.fill = PatternFill("solid", fgColor="E0F8F1")
ini_col = num_col + 1
fin_col = ini_col + 13
while fin_col < longHeader:
for column in range(ini_col, fin_col):
column_letter = get_column_letter(column)
for rowD in range(row, lenData + row + 1):
if (rowD % 2 == 0):
ws[column_letter + str(rowD)].style = rowPar2
ini_col = fin_col + 13
fin_col = ini_col + 13
rowTotal = NamedStyle(name="rowTotal")
rowTotal.fill = PatternFill("solid", fgColor="E6E6E6")
ult_cols = longHeader - 13
for column in range(ult_cols + 1, longHeader + 1):
column_letter = get_column_letter(column)
for rowD in range(row, lenData + row + 1):
if (rowD % 2 == 0):
ws[column_letter + str(rowD)].style = rowTotal
return ws
def writeObjectValues(self, objName):
self.write_logger.info(self.wb.named_styles)
if ('style1' not in self.wb.named_styles):
style1 = NamedStyle(name="style1")
style1.font = Font(name='Calibri', size=8, color='FF000000')
style1.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
style1.fill = PatternFill(start_color='BDD7EE',
end_color='BDD7EE',
fill_type='solid')
else:
style1 = self.wb._named_styles['style1']
if ('style2' not in self.wb.named_styles):
style2 = NamedStyle(name="style2")
style2.font = Font(name='Calibri', size=8, color='FF000000')
style2.border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
style2.fill = PatternFill(start_color='00B050',
end_color='00B050',
fill_type='solid')
else:
style2 = self.wb._named_styles['style2']
username_json_path = config.project_path + '\\log\\' + config.customer + "\\" + objName + "\\" + objName + '_output.json'
with open(username_json_path, 'r') as username_file:
nokiaData = json.load(username_file)
startingRow = self.dataRowNumber
for nokiaRecord in nokiaData:
cell = self.ws.cell(column=1, row=startingRow)
try:
cell.style = style2
except:
self.write_logger.debug("style already exist ")
self.ws.cell(column=1, row=startingRow, value='ADD')
for nokiaAttr in nokiaRecord:
#self.write_logger.debug(self.validationRules[nokiaAttr])
self.applyValidationToCell(self.ws, startingRow, nokiaAttr)
#Create data validation object for this
cell = self.ws.cell(column=self.cols[nokiaAttr] + 1,
row=startingRow)
try:
cell.style = style1
except:
self.write_logger.debug("style already exist ")
self.ws.cell(column=self.cols[nokiaAttr] + 1,
row=startingRow,
value=nokiaRecord[nokiaAttr])
startingRow = startingRow + 1
#w_worksheet.write(startingRow, self.cols[nokiaAttr], nokiaRecord[nokiaAttr])
self.write_logger.info("Finished writing Excel")
def creationFichierResultat():
#nom = input("Nom du fichier:")
if p.ELECTRICITE_ET_AUTRES and p.INTRANTS_ET_FRET and p.EMBALLAGES_ET_SACHERIE and p.FRET_AVAL:
nom = "COMPLET "+str(int(100000*random.random()))
else:
nom = "test"+str(int(100000*random.random()))
nom = "Fichier Resultat - "+str(nom)+ " - "+str(p.ANNEE)+".xlsx"
fichierResultat = openpyxl.Workbook()
feuille_principale = fichierResultat.active
feuille_principale.title= "Résultats généraux"
#Tout ce qu'il y a ci après est esthétique
bd_dotted = Side(style="dotted", color="000000")
bd_thin = Side(style="thin", color="FF000000")
#Style du fond ffe2aa
styleFond = NamedStyle(name="Fond")
styleFond.fill = PatternFill("solid", fgColor= "ffe2aa")
fichierResultat.add_named_style(styleFond)
#Style du tete de tableau
styleTeteTab = NamedStyle(name="TeteTab")
styleTeteTab.border = Border(bottom = bd_dotted, top= bd_dotted)
styleTeteTab.alignment = Alignment(wrapText = "true",horizontal = "center")
styleTeteTab.fill = PatternFill("solid", fgColor= "FFFFFF")
fichierResultat.add_named_style(styleTeteTab)
#Style du valeurs de tableau
styleVal = NamedStyle(name="Valeurs")
styleVal.border = Border(left = bd_dotted, right = bd_dotted)
styleVal.fill = PatternFill("solid", fgColor= "FFF0E1")
fichierResultat.add_named_style(styleVal)
#Style des titres
styleTitre = NamedStyle(name="Titre")
styleTitre.border = Border(top=bd_dotted, bottom = bd_dotted)
styleTitre.fill = PatternFill("solid", fgColor= "EBEAF5")
styleTitre.alignment = Alignment(horizontal = "center", vertical="center")
styleTitre.font = Font(name = 'Calibri', size =36, italic = False, bold = True, color = 'FF000000')
fichierResultat.add_named_style(styleTitre)
#Style des entrees de tableau
styleEntree = NamedStyle(name="Entree")
styleEntree.border = Border(right = bd_thin)
styleEntree.fill = PatternFill("solid", fgColor= "FFFFFF")
fichierResultat.add_named_style(styleEntree)
#Style des tableaux en général
styleTableau = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)
#On applique le fond à toutes les cellules
for lin in range(1,200):
for col in [x for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]:
case = col+str(lin)
feuille_principale[case].style=styleFond
for col in [x+y for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ" for y in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]:
case = col+str(lin)
feuille_principale[case].style=styleFond
def _create_styles(self, workbook):
style = NamedStyle(name="title")
style.font = Font(bold=True)
style.fill = PatternFill(fill_type=FILL_SOLID, start_color='FFFF9900')
workbook.add_named_style(style)
style = NamedStyle(name="id")
style.font = Font(bold=True)
style.fill = PatternFill(fill_type=FILL_SOLID, start_color='FFFFFF00')
workbook.add_named_style(style)
style = NamedStyle(name="anonymous")
style.fill = PatternFill(fill_type=FILL_SOLID, start_color='FFFF9999')
workbook.add_named_style(style)
def wrapper(ws, number_of_row):
# 设置标题样式
ws.merge_cells('a1:f1')
color1 = Color(rgb=title_color)
font = Font(name="Microsoft YaHei UI",
size=34,
b=True,
color=color1)
a1 = ws['a1']
alignment = Alignment(horizontal='center', vertical='center')
a1.font = font
a1.alignment = alignment
# 设置表头样式
color2 = Color(rgb=header_color)
style_for_row2 = NamedStyle(name='header')
style_for_row2.font = Font(name='Calibri', size=16, color='FFFFFF')
style_for_row2.alignment = Alignment(horizontal='center',
vertical='center')
style_for_row2.fill = PatternFill('solid', fgColor=color2)
for each_cell in ws[2]:
each_cell.style = style_for_row2
# 设置表格样式
for i in range(1, number_of_row + 3):
ws.row_dimensions[i].height = 49.5
for i in range(1, 7):
ws.column_dimensions[chr(64 + i)].width = 15
color3 = Color(rgb=body_color)
style_for_body = NamedStyle(name='body')
style_for_body.font = Font(name='Calibri')
style_for_body.alignment = Alignment(horizontal='center',
vertical='center')
style_for_body.fill = PatternFill("solid", fgColor=color3)
style_for_body.border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'),
bottom=Side(border_style='thin',
color='FF000000'),
top=Side(border_style='thin',
color='FF000000'))
for i in range(3, number_of_row + 3):
for j in range(1, 7):
ws.cell(row=i, column=j).style = style_for_body
return function(ws, number_of_row)
def register_styles(wb):
header = NamedStyle(name='header')
header.font = Font(name="Verdana", size=12, bold=True, color="FFFFFF")
header.fill = PatternFill("solid", fgColor="666666")
wb.add_named_style(header)
divider = NamedStyle(name='divider')
divider.font = Font(name="Verdana", size=11, bold=True)
divider.fill = PatternFill("solid", fgColor="b7b7b7")
wb.add_named_style(divider)
general = NamedStyle(name='general')
general.font = Font(name="Verdana", size=11)
wb.add_named_style(general)
def style_thead():
style = NamedStyle(name='thead')
style.font = create_font(bold=True, color='00FFFFFF')
style.fill = PatternFill(patternType='solid', fgColor='00000000')
side = Side(style='thin', color='00FFFFFF')
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def make_style(wb): # создаем именованный стиль для серой заливки:
from openpyxl.styles import NamedStyle, Border, Side, PatternFill
ns = NamedStyle(name='Grey')
ns.fill = PatternFill("solid", fgColor="909090")
border = Side()
ns.border = Border(left=border, top=border, right=border, bottom=border)
wb.add_named_style(ns)
def get_style_from_dict(style_dict, style_name):
"""
Make NamedStyle instance from dictionary
:param style_dict: dictionary with style properties.
Example: {'fill': {'fill_type'='solid',
'start_color'='FFCCFFCC'},
'alignment': {'horizontal': 'center',
'vertical': 'center',
'wrapText': True,
'shrink_to_fit': True},
'border_side': {'border_style': 'thin',
'color': 'FF000000'},
'font': {'name': 'Arial',
'size': 14,
'bold': True,
'color': 'FF000000'}
}
:param style_name: name of created style
:return: openpyxl.styles.NamedStyle instance
"""
style = NamedStyle(name=style_name)
if not style_dict:
return style
for key, value in style_dict.items():
if key == "font":
style.font = Font(**value)
elif key == "fill":
style.fill = PatternFill(**value)
elif key == "alignment":
style.alignment = Alignment(**value)
elif key == "border_side":
side = Side(**value)
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def __init__(self, excel_name, name):
self.excel_name = "{}.xlsx".format(excel_name)
self.workbook = openpyxl.Workbook()
self.worksheet = self.workbook.active
self.worksheet.title = name
border_style = NamedStyle(name="border_style")
bian = Side(style="medium", color="000000")
border = Border(top=bian, bottom=bian, left=bian, right=bian)
border_style.border = border
alignment = Alignment(horizontal="center", vertical="center")
border_style.alignment = alignment
self.border_style = border_style
self.workbook.add_named_style(border_style)
title_style = NamedStyle(name="title_style")
ft = Font(name="Noto Sans CJK SC Regular",
color="FFFFFF",
size=11,
b=False)
fill = PatternFill("solid", fgColor="00A3FF")
title_style.font = ft
title_style.fill = fill
title_style.border = border
title_style.alignment = alignment
self.title_style = title_style
self.workbook.add_named_style(title_style)
def create_output_file(IP_list, output):
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = "Reputations"
highlight = NamedStyle(name="highlight")
highlight.font = Font(name='Verdana',
size=9,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FFFFFF')
highlight.fill = PatternFill(fill_type="solid", bgColor='FF000000')
workbook.add_named_style(highlight)
cell_obj = sheet.cell(row=1, column=1)
cell_obj.value = "IP"
sheet.merge_cells('A1:A2')
sheet['A1'].style = highlight
row = 3
col = 1
for IP in IP_list:
cell_obj = sheet.cell(row=row, column=col)
cell_obj.value = IP
row = row + 1
workbook.save(output + ".xlsx")
def style_thead():
style = NamedStyle(name='thead')
style.font = create_font(bold=True, color='00FFFFFF')
style.fill = PatternFill(patternType='solid', fgColor='00000000')
side = Side(style='thin', color='00FFFFFF')
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def total_summary(ws,
listTotal,
numberRow,
lonTableHeader,
font_color="FFFFFF",
fill_color="afbcd7",
formatPercent=[],
formatMoney=[],
formatNumberInteger=[],
formatNumberDecimal=[]):
ws.append(listTotal)
totalOpe = NamedStyle(name="totalOpe")
totalOpe.alignment = Alignment(horizontal='center')
totalOpe.fill = PatternFill("solid", fgColor=fill_color)
totalOpe.font = Font(color=font_color, size=12, bold=True)
for row in ws.iter_rows('A' + str(numberRow) + ':' +
get_column_letter(lonTableHeader) +
str(numberRow)):
for cell in row:
cell.style = totalOpe
for col_letter in formatPercent:
ws[col_letter + str(numberRow)].number_format = '#,##0.00 %'
for col_letter in formatMoney:
ws[col_letter + str(numberRow)].number_format = '#,##0.00 $'
for col_letter in formatNumberDecimal:
ws[col_letter + str(numberRow)].number_format = '#,##0.00'
for col_letter in formatNumberInteger:
ws[col_letter + str(numberRow)].number_format = '#0'
return ws
def paint_par(ws,
longHeader,
lenData,
num_col,
row=11,
col_money=[],
col_porc=[],
col_nro_dec=[],
col_nro_int=[]):
rowPar = NamedStyle(name="rowPar")
rowPar.fill = PatternFill("solid", fgColor="E0ECF8")
for column in range(1, longHeader + 1):
column_letter = get_column_letter(column)
for rowD in range(row, lenData + row + 1):
if (rowD % 2 == 0):
ws[column_letter + str(rowD)].style = rowPar
if (column > num_col):
ws[column_letter + str(rowD)].number_format = '#,##0.00'
if (column in col_nro_int):
ws[column_letter + str(rowD)].number_format = '#0'
if (column in col_nro_dec):
ws[column_letter + str(rowD)].number_format = '#,##0.00'
if (column in col_money):
ws[column_letter + str(rowD)].number_format = '#,##0.00 $'
if (column in col_porc):
ws[column_letter + str(rowD)].number_format = '#,##0.00 %'
return ws
def _build_styles(self):
# Builds new styles for the spreadsheet
title = NamedStyle("title")
title.font = self.WHITE_FONT
title.fill = self.BLACK_FILL
title.alignment = self.MID_ALIGN
self.workbook.add_named_style(title)
def generar_codigos_excel(self):
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
)
response[
'Content-Disposition'] = 'attachment; filename={date}-codigos.xlsx'.format(
date=datetime.now().strftime('%Y%m%d'), )
titulo = NamedStyle(name="titulo")
titulo.font = Font(bold=True, size=10, color='ffffff')
titulo.alignment = Alignment(
horizontal="center",
vertical="center",
wrap_text=True,
)
titulo.fill = PatternFill(
start_color='244062',
end_color='244062',
fill_type='solid',
)
wb = Workbook()
wb.add_named_style(titulo)
sheet = wb.active
column_dimensions = sheet.column_dimensions['A']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['B']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['C']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['D']
column_dimensions.width = 20
column_dimensions = sheet.column_dimensions['E']
column_dimensions.width = 20
sheet['A1'] = "CODIGO PRINCIPAL"
sheet['A1'].style = titulo
sheet['B1'] = "CODIGO EXTERNO"
sheet['B1'].style = titulo
sheet['C1'] = "CODIGO PROVEEDOR"
sheet['C1'].style = titulo
sheet['D1'] = "CODIGO TESTIGO"
sheet['D1'].style = titulo
sheet['E1'] = "CODIGO BOLSA"
sheet['E1'].style = titulo
for codigo in self.__codigos:
sheet.append(
(codigo.numero, codigo.cod_externo, codigo.cod_proveedor,
codigo.cod_testigo, codigo.cod_bolsa))
wb.save(response)
return response
def __init__(self):
__style = NamedStyle(name='simple')
__style.font = Font(bold=True, size=11, color=colors.BLACK)
__style.fill = PatternFill(patternType='solid', fgColor='FABF8F')
bd = Side(style='thin', color=colors.BLACK)
self.__border = Border(left=bd, top=bd, right=bd, bottom=bd)
__style.border = self.__border
self.__alignment = Alignment(horizontal='left', vertical='center')
__style.alignment = self.__alignment
self.__header_style = __style
def makeStyle (self, name, copyfromcell, sheet=None):
ws = self._getTable (sheet)
newstyle = NamedStyle (name=name)
newstylecell = ws [copyfromcell]
newstyle.font = copy (newstylecell.font)
newstyle.fill = copy (newstylecell.fill)
newstyle.border = copy (newstylecell.border)
newstyle.alignment = copy (newstylecell.alignment)
newstyle.number_format = newstylecell.number_format
newstyle.protection = copy (newstylecell.protection)
return newstyle
def adds_title_format_new(ws,
lonTableHeader,
font_color="FFFFFF",
fill_color="afbcd7",
rows=10):
headOpe = NamedStyle(name="headOpe")
headOpe.alignment = Alignment(horizontal='center')
headOpe.fill = PatternFill("solid", fgColor=fill_color)
headOpe.font = Font(color=font_color, size=12, bold=True)
for row in ws.iter_rows('A' + str(rows) + ':' +
get_column_letter(lonTableHeader) + str(rows)):
for cell in row:
cell.style = headOpe
return ws
def _generate_spreadsheet_data(cls, request, out, report, *args, **kwargs):
# Create a workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet(title=report.name)
# Create a named style for the header row
readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle")
readlonlyheaderstyle.fill = PatternFill(fill_type="solid",
fgColor="d0ebfb")
wb.add_named_style(readlonlyheaderstyle)
# Run the query
conn = None
try:
conn = create_connection(request.database)
comment = CellComment(force_text(_("Read only")),
"Author",
height=20,
width=80)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[request.database].get(
"SQL_ROLE", "report_role")
if sqlrole:
cursor.execute("set role %s" % (sqlrole, ))
cursor.execute(sql=cls.getSQL(report.sql))
if cursor.description:
# Write header row
header = []
for f in cursor.description:
cell = WriteOnlyCell(ws, value=f[0])
cell.style = "readlonlyheaderstyle"
cell.comment = comment
header.append(cell)
ws.append(header)
# Add an auto-filter to the table
ws.auto_filter.ref = "A1:%s1048576" % get_column_letter(
len(header))
# Write all output rows
for result in cursor.fetchall():
ws.append(
[_getCellValue(i, request=request) for i in result])
# Write the spreadsheet
wb.save(out)
finally:
if conn:
conn.close()
def add_col_style(wb,
column,
font_name='Calibri',
font_bold=False,
font_color='000000',
font_size=11,
top_border_type='thin',
top_border_color='000000',
bot_border_type='thin',
bot_border_color='000000',
left_border_type='thin',
left_border_color='000000',
right_border_type='thin',
right_border_color='000000',
align_horizn='left',
align_vertic='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0,
fill_start_color='FFFFFF',
fill_end_color='FFFFFF',
fill_type='solid'):
name = 'column_' + str(column) + "_" + str(tab_name)
st = NamedStyle(name=name)
st.font = Font(name=font_name,
bold=font_bold,
color=font_color,
size=font_size)
bd_top = Side(style=top_border_type, color=top_border_color)
bd_bot = Side(style=bot_border_type, color=bot_border_color)
bd_left = Side(style=left_border_type, color=left_border_color)
bd_right = Side(style=right_border_type, color=right_border_color)
st.border = Border(left=bd_left,
top=bd_top,
right=bd_right,
bottom=bd_bot)
st.alignment = Alignment(horizontal=align_horizn,
vertical=align_vertic,
text_rotation=text_rotation,
wrap_text=wrap_text,
shrink_to_fit=shrink_to_fit,
indent=indent)
st.fill = PatternFill(start_color=fill_start_color,
end_color=fill_end_color,
fill_type=fill_type)
wb.add_named_style(st)
return name
def add_body_style(wb):
name = 'body_' + str(tab_name)
st = NamedStyle(name=name)
st.font = Font(name='Calibri', bold=False, size=11)
bd = Side(style='thin', color="000000")
st.border = Border(left=bd, top=bd, right=bd, bottom=bd)
st.alignment = Alignment(horizontal='left',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
st.fill = PatternFill(start_color=body_color,
end_color=body_color,
fill_type='solid')
wb.add_named_style(st)
return name
def xl_create_named_style(wb):
''' create excel named style
wb : excel workbook
'''
zbx_title = NamedStyle(name="zbx title")
zbx_title.font = Font(bold=True, color="ffffff")
zbx_title.fill = PatternFill(fill_type="solid", start_color="538DD5", end_color="538DD5")
bd = Side(style="thin", color="538DD5")
zbx_title.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(zbx_title)
zbx_data = NamedStyle(name="zbx data")
zbx_data.font = Font(color="000000")
zbx_data.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(zbx_data)
return
def add_highlighter(wb):
name = 'highlight'
st = NamedStyle(name=name)
st.font = Font(name='Calibri', bold=True, size=11)
bdb = Side(style='medium', color="000000")
bdt = Side(style='thin', color="000000")
st.border = Border(left=bdt, top=bdt, right=bdt, bottom=bdt)
st.alignment=Alignment(horizontal='left',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
st.fill = PatternFill(start_color='ffcccc',
end_color='ffcccc',
fill_type='solid')
wb.add_named_style(st)
return name
def add_row_hlt_style(wb, highlight_vals, name_order, h_type=None):
'''
This styler is for highlighting cells and works as
for one style creation and for multiple as well due to
'name_order' argument which will distiguish each unique style.
This function is used for both index and row highlighters
Takes:
- wb - object - workbook;
- highlight_vals - dictionary from highlight_dict;
- name_order - int - id from highlight_dict
- h_type - string - highlighter type 'row' or 'index'
'''
if h_type == None:
print("Error: add_row_hlt_style(): Please"+\
"specify correct h_type variable.")
return None
# create unique style names depending on type of highlight
name = '{}_highlighter_'.format(h_type) + str(tab_name) + str(
name_order)
st = NamedStyle(name=name)
st.font = Font(name='Calibri',
bold=highlight_vals['hlt_font_bold'],
size=10,
color=highlight_vals['hlt_txt_color'])
if highlight_vals['hlt_border_bold']:
bord_side = 'thick'
else:
bord_side = "thin"
bdb = Side(style=bord_side, color="000000")
bdt = Side(style='thin', color="000000")
st.border = Border(left=bdt, top=bdb, right=bdt, bottom=bdb)
st.alignment = Alignment(horizontal=highlight_vals['hlt_align_horiz'],
vertical=highlight_vals['hlt_align_vert'],
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
st.fill = PatternFill(start_color=highlight_vals['hlt_color'],
end_color=highlight_vals['hlt_color'],
fill_type='solid')
wb.add_named_style(st)
return name
def add_named_style(wb,
name,
number_format=None,
font_name='Calibri',
font_size=10,
font_color='FF000000',
fill=None):
"""Add a named style to the open workbook.
Already named styles are ignored. See add_default_styles for example usage.
"""
if name in wb.named_styles:
return
sty = NamedStyle(name=name)
sty.font = Font(name=font_name, size=font_size, color=font_color)
if fill:
sty.fill = fill
if number_format:
sty.number_format = number_format
wb.add_named_style(sty)
def create_excel(product_list, product_name):
# Format of excel
# No. | Title of product | Price | URL link | E-Commerce
# Define file name
today = date.today().strftime("%d%m%Y")
excel_file_name = "ExcelReport/{}_{}.xlsx".format(today, product_name)
if os.path.exists(excel_file_name):
workbook = load_workbook(excel_file_name)
worksheet = workbook.active
else:
# Create Excel
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "Original Data"
# Header style
header_style = NamedStyle(name="hearder_style")
header_style.font = Font(bold=True)
header_style.fill = PatternFill(start_color="00FFFF00",
end_color="00FFFF00",
fill_type="solid")
# Write header
header_list = ("No.", "Product Title", "Price", "Url Link",
"E-Commerce")
for col in range(1, len(header_list) + 1):
header_cell = worksheet.cell(1, col, header_list[col - 1])
header_cell.style = header_style
# Write data into worksheet
product_num = worksheet.max_row
for data in product_list:
content = (product_num, data[0], data[1], data[2], data[3])
worksheet.append(content)
product_num += 1
workbook.save(excel_file_name)
print("Done create excel.")
def prettify(self):
b = load_workbook(self.fname)
header_style = NamedStyle(name='table_header')
header_style.font = Font(bold=True)
header_style.fill = PatternFill(fill_type='solid',
start_color='00CCCCCC',
end_color='00CCCCCC')
header_style.alignment = Alignment(horizontal='center')
thin = Side(border_style='thin', color='000000')
double = Side(border_style="double", color="ff0000")
header_style.border = Border(top=double,
left=thin,
right=thin,
bottom=double)
b.add_named_style(header_style)
for ws in b.worksheets:
if ws.title in self.sheets_with_header:
has_header = True
for cell in ws[1]:
cell.style = header_style
else:
has_header = False
for x, c in enumerate(ws.columns, start=1):
column_width = 5
for y, cell in enumerate(c, start=1):
if cell.value is not None:
w = len(str(cell.value))
if has_header and (y == 1):
w = int(1.25 * w)
if w > column_width:
column_width = w
if column_width > 50:
column_width = 50
ws.column_dimensions[get_column_letter(
x)].width = column_width + 2
b.save(self.fname)
if sys.version[0] == '2':
reload(sys)
sys.setdefaultencoding("utf-8")
#sys.path.insert(0, "/var/www/cgi-bin/bank_recon.py")
os.chdir(".") # bound path to the current directory
#satisfy openpyxl requirements for highlighting cells
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=12)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
highlight.fill = PatternFill(fill_type='lightUp',
start_color='fff000',
end_color='6efdfd')
#satisfy openpyxl requirements for highlighting cells2
highlight2 = NamedStyle(name="highlight2")
highlight2.font = Font(bold=True, size=12)
bd = Side(style='thick', color="000000")
highlight2.border = Border(left=bd, top=bd, right=bd, bottom=bd)
highlight2.fill = PatternFill(fill_type='lightUp',
start_color='fff000',
end_color='fff000')
print("\n")
print("arquivos excel disponiveis nesta pasta:")
def __init__(self, datetimenow=datetime.datetime.now()):
dirname = "Logs/%s" % datetimenow.strftime("%Y-%m-%d_%H_%M_%S")
if not os.path.exists(dirname):
os.mkdir(dirname)
self.wbname = "%s/Report.xlsx" % dirname
self.wb = Workbook()
ft = Font(name=u'Courier New',
size=16,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type="solid",
start_color='FF88FFFF',
end_color='FF008800')
cellfill = PatternFill(fill_type="solid",
start_color='FFFFFFFF',
end_color='FF000000')
bd = Border(left=Side(border_style="thin",
color='FF001000'),
right=Side(border_style="thin",
color='FF110000'),
top=Side(border_style="thin",
color='FF110000'),
bottom=Side(border_style="thin",
color='FF110000'),
diagonal=Side(border_style=None,
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None,
color='FF000000'),
vertical=Side(border_style=None,
color='FF000000'),
horizontal=Side(border_style=None,
color='FF110000')
)
alignment = Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
headerstyle = NamedStyle(name="header")
headerstyle.font = ft
headerstyle.fill = fill
headerstyle.border = bd
headerstyle.alignment = alignment
self.wb.add_named_style(headerstyle)
cellstyle = NamedStyle(name="cell")
cellstyle.font = ft
cellstyle.fill = cellfill
cellstyle.border = bd
cellstyle.alignment = alignment
self.wb.add_named_style(cellstyle)
self.current_row = 0
self.chart_row = 0
self.linkbd = bd
pass
def create_groups_xlsx(day):
activate("de")
day = GroupAssignment.objects.monday(day)
days = [day + timedelta(days=i) for i in range(5)]
wb = Workbook()
ws = wb.active
thin_border = Side(border_style="thin", color="00000000")
medium_border = Side(border_style="medium", color="00000000")
font = Font(name="Calibri", size=14)
centered = NamedStyle("centered")
centered.font = font
centered.alignment = Alignment(horizontal="center", vertical="center")
wb.add_named_style(centered)
dark = NamedStyle("dark")
dark.font = font
dark.fill = PatternFill("solid", "cccccc")
dark.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=thin_border
)
wb.add_named_style(dark)
darker = NamedStyle("darker")
darker.border = Border(top=thin_border, bottom=thin_border)
darker.font = Font(name="Calibri", size=14, bold=True)
darker.fill = PatternFill("solid", "aaaaaa")
wb.add_named_style(darker)
darker_border_left = NamedStyle("darkerBorderLeft")
darker_border_left.border = Border(
top=thin_border, bottom=thin_border, left=medium_border
)
darker_border_left.font = darker.font
darker_border_left.fill = darker.fill
wb.add_named_style(darker_border_left)
border = NamedStyle("borderThickLeft")
border.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=medium_border
)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThickBottom")
border.border = Border(bottom=medium_border)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThinLeft")
border.border = Border(left=thin_border)
border.font = font
wb.add_named_style(border)
border = NamedStyle("borderThinBottom")
border.border = Border(bottom=thin_border)
border.font = font
wb.add_named_style(border)
borderThin = NamedStyle("borderThin")
borderThin.border = Border(
top=thin_border, right=thin_border, bottom=thin_border, left=thin_border
)
borderThin.font = font
wb.add_named_style(borderThin)
vertical_text = Alignment(text_rotation=90)
def day_column(weekday):
return 2 + 9 * weekday
def style_row(row, style):
ws[c(0, row)].style = style
ws[c(1, row)].style = style
ws[c(day_column(5), row)].style = (
"darkerBorderLeft" if style == "darker" else style
)
for i in range(5):
for j in range(9):
ws[c(day_column(i) + j, row)].style = style
if style == "darker":
ws[c(day_column(i), row)].style = "darkerBorderLeft"
def column_width(column, width):
ws.column_dimensions[columns[column]].width = width
def row_height(row, height):
ws.row_dimensions[row + 1].height = height
ws[c(0, 1)].style = "borderThickBottom"
ws[c(1, 1)].style = "borderThickBottom"
ws[c(day_column(5), 1)].style = "borderThickBottom"
for i, cell in enumerate(
[
date_format(day, "F y"),
"Woche %s" % date_format(day, "W"),
"Auftragsnummer Arbeit",
"LEITUNG",
"ZIVIS",
]
):
ws[c(0, i + 1)] = cell
ws[c(day_column(5), i + 1)] = cell
ws[c(0, i + 1)].style = "borderThinBottom"
ws[c(1, i + 1)].style = "borderThinBottom"
if i > 0:
ws[c(day_column(5), i + 1)].style = "borderThickLeft"
if i < 2:
ws[c(0, i + 1)].style = centered
ws[c(day_column(5), i + 1)].style = centered
column_width(0, 35)
column_width(1, 15)
column_width(day_column(5), 35)
ws[c(0, 1)].style = "borderThickBottom"
ws[c(1, 1)].style = "borderThickBottom"
ws[c(0, 1)].alignment = centered.alignment
ws[c(1, 1)].alignment = centered.alignment
ws[c(0, 2)].style = "borderThinBottom"
ws[c(0, 2)].alignment = centered.alignment
ws[c(day_column(5), 1)].style = "borderThickBottom"
ws[c(day_column(5), 1)].alignment = centered.alignment
ws[c(day_column(5), 2)].style = "borderThickLeft"
ws[c(day_column(5), 2)].alignment = centered.alignment
for i, current in enumerate(days):
ws[c(day_column(i), 0)] = date_format(current, "l")
ws[c(day_column(i), 1)] = date_format(current, "d.m.y")
ws[c(day_column(i), 0)].style = centered
ws[c(day_column(i), 1)].style = "borderThickBottom"
ws[c(day_column(i), 1)].alignment = centered.alignment
ws.merge_cells("%s:%s" % (c(day_column(i), 0), c(day_column(i + 1) - 1, 0)))
ws.merge_cells("%s:%s" % (c(day_column(i), 1), c(day_column(i + 1) - 1, 1)))
ws[c(day_column(i), 2)] = "Absenz"
for k in range(2, 499):
ws[c(day_column(i), k)].style = "borderThickLeft"
ws[c(day_column(i) + 1, k)].style = "borderThin"
for j in range(1, 9):
ws[c(day_column(i) + j, 2)] = "%s)" % j
style = "borderThin" if j % 2 else "dark"
for k in range(2, 499):
ws[c(day_column(i) + j, k)].style = style
ws[c(day_column(i) + j, 2)].alignment = vertical_text
column_width(day_column(i) + j, 7)
ws[c(day_column(i), 2)].alignment = vertical_text
column_width(day_column(i), 7)
row_height(2, 250)
row_height(3, 60)
row_height(4, 60)
# ZIVIS line
style_row(5, "darker")
assignments = defaultdict(list)
seen_assignments = set()
for ga in GroupAssignment.objects.filter(week=day).select_related(
"assignment__drudge__user"
):
assignments[ga.group_id].append(ga.assignment)
seen_assignments.add(ga.assignment_id)
free_assignments = (
Assignment.objects.for_date(day)
.exclude(pk__in=seen_assignments)
.select_related("drudge__user")
)
absences = defaultdict(dict)
for absence in Absence.objects.filter(days__overlap=days):
for day in absence.days:
absences[absence.assignment_id][day] = absence
def add_group(row, group_name, assignments):
ws[c(0, row)] = group_name
ws[c(day_column(5), row)] = group_name
style_row(row, "darker")
# TODO courses (UNA/MSK)
for assignment in assignments:
row += 1
ws[c(0, row)] = assignment.drudge.user.get_full_name()
ws[c(day_column(5), row)] = assignment.drudge.user.get_full_name()
ws[c(0, row)].style = "borderThinBottom"
ws[c(1, row)].style = "borderThinBottom"
ws[c(day_column(5), row)].style = "borderThickLeft"
row_height(row, 35)
if assignment.date_from in days:
ws[c(1, row)] = "NEU"
elif assignment.determine_date_until() in days:
ws[c(1, row)] = "ENDE"
else:
ws[c(1, row)] = date_format(assignment.determine_date_until(), "d.m.y")
for i, current in enumerate(days):
if current < assignment.date_from:
ws[c(day_column(i), row)] = "Vor Beginn"
elif current > assignment.determine_date_until():
ws[c(day_column(i), row)] = "Nach Ende"
elif current in absences[assignment.id]:
ws[c(day_column(i), row)] = absences[assignment.id][
current
].pretty_reason()
# Skip some lines
for i in range(0, max(3, 6 - len(assignments))):
row += 1
row_height(row, 35)
ws[c(0, row)].style = "borderThinBottom"
ws[c(1, row)].style = "borderThinBottom"
ws[c(day_column(5), row)].style = "borderThickLeft"
row += 1
return row
row = 6
for group in Group.objects.active():
row = add_group(row, group.name, assignments[group.id])
row = add_group(row, "Nicht zugeteilt", free_assignments)
return wb