def addHeaderStyle(sheetTitle):
missingWB = openpyxl.load_workbook(missingWB_out)
unprocessedWB = openpyxl.load_workbook(unprocessedWB_out)
currentSheet1 = missingWB.get_sheet_by_name(sheetTitle)
currentSheet2 = unprocessedWB.get_sheet_by_name(sheetTitle)
if 'red_bold' not in missingWB._named_styles.names:
red_bold = NamedStyle(name="red_bold")
red_bold.font = Font(color='00FF0000', bold=True)
# red_bold.fill = PatternFill("solid", fgColor="FFCC00")
red_bold.alignment = Alignment(horizontal="center", vertical="center")
missingWB.add_named_style(red_bold)
if 'red_bold' not in unprocessedWB._named_styles.names:
red_bold = NamedStyle(name="red_bold")
red_bold.font = Font(color='00FF0000', bold=True)
# red_bold.fill = PatternFill("solid", fgColor="FFCC00")
red_bold.alignment = Alignment(horizontal="center", vertical="center")
unprocessedWB.add_named_style(red_bold)
for cell in currentSheet1["1:1"]:
cell.style = 'red_bold'
for cell in currentSheet2["1:1"]:
cell.style = 'red_bold'
missingWB.save(missingWB_out)
unprocessedWB.save(unprocessedWB_out)
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 write_excel(self):
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
title = NamedStyle(name="title")
title.font = Font(name=u'宋体', size=11, bold=True)
title.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
title.border = Border(left=left, right=right, top=top, bottom=bottom)
content = NamedStyle(name="content")
content.font = Font(name=u'宋体', size=11)
content.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
content.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long = NamedStyle(name="content_long")
content_long.font = Font(name=u'宋体', size=11)
content_long.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
self.ws.column_dimensions['A'].width = 15
self.ws.column_dimensions['B'].width = 15
self.ws.column_dimensions['C'].width = 80
self.ws.column_dimensions['D'].width = 55
self.ws.column_dimensions['E'].width = 55
self.ws.column_dimensions['F'].width = 10
self.ws_wrong.column_dimensions['A'].width = 20
self.ws_wrong.column_dimensions['B'].width = 15
self.ws_wrong.column_dimensions['C'].width = 15
self.ws_wrong.column_dimensions['D'].width = 80
self.ws_wrong.column_dimensions['E'].width = 80
for i in range(self.ws.max_row):
self.ws.row_dimensions[i + 1].height = 30
for i in range(self.ws_wrong.max_row):
self.ws_wrong.row_dimensions[i + 1].height = 50
for x in self.ws[1]:
x.style = title
for x in self.ws_wrong[1]:
x.style = title
for x in self.ws['A'][1:]:
x.style = content
for x in self.ws_wrong['A'][1:]:
x.style = content
for x in self.ws['B'][1:]:
x.style = content
for x in self.ws_wrong['B'][1:]:
x.style = content
for x in self.ws['C'][1:]:
x.style = content_long
for x in self.ws_wrong['C'][1:]:
x.style = content
for x in self.ws['D'][1:]:
x.style = content_long
for x in self.ws_wrong['D'][1:]:
x.style = content_long
for x in self.ws['E'][1:]:
x.style = content_long
for x in self.ws_wrong['E'][1:]:
x.style = content_long
for x in self.ws['F'][1:]:
x.style = content
print("格式正确个数 %d" % self.ws.max_row)
print("格式错误个数 %d" % self.ws_wrong.max_row)
self.wb.save(os.path.dirname(os.path.abspath(self.path)) + "\\" + os.path.basename(self.path).replace(".txt",
".xlsx"))
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 export_xlsx(self, filename):
um = self.units.description
data = [(i+1,v,um) for (i,v) in enumerate(self.values)]
wb = Workbook(write_only = True)
ws = wb.create_sheet()
# create some styles
cellstyle = NamedStyle(name="highlight")
headerstyle = NamedStyle(name='headercell')
wb.add_named_style(cellstyle)
wb.add_named_style(headerstyle)
cellstyle.font = Font(name='Calibri', size=11)
headerstyle.font = Font(name='Calibri', size=11, bold=True)
bd = Side(border_style='thin')
cellstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd)
headerstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd)
header_labels = ['#', 'Value', 'Units']
# write header
header = []
for el in header_labels:
cell = WriteOnlyCell(ws, value=el)
cell.style = 'headercell'
header.append(cell)
ws.append(header)
# write data
for t in data:
row = []
for el in t:
cell = WriteOnlyCell(ws, value=el)
cell.style = 'highlight'
row.append(cell)
ws.append(row)
wb.save(filename) # doctest: +SKIP
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 format_file(self):
self.ws_new.column_dimensions['A'].width = 20
self.ws_new.column_dimensions['B'].width = 14
self.ws_new.column_dimensions['C'].width = 14
self.ws_new.column_dimensions['D'].width = 70
self.ws_new.column_dimensions['E'].width = 10
self.ws_new.column_dimensions['F'].width = 14
self.ws_new.column_dimensions['G'].width = 14
self.ws_new.column_dimensions['H'].width = 14
self.ws_new.column_dimensions['I'].width = 14
self.ws_new.column_dimensions['J'].width = 14
self.ws_new.column_dimensions['K'].width = 14
self.ws_new.column_dimensions['L'].width = 25
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
title = NamedStyle(name="title")
title.font = Font(name=u'宋体', size=11, bold=True)
title.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
title.border = Border(left=left, right=right, top=top, bottom=bottom)
content = NamedStyle(name="content")
content.font = Font(name=u'宋体', size=11)
content.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
content.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long = NamedStyle(name="content_long")
content_long.font = Font(name=u'宋体', size=11)
content_long.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
for i in range(self.ws_new.max_row):
self.ws_new.row_dimensions[i + 1].height = 40
for x in self.ws_new['A:C']:
for y in x:
y.style = content
for x in self.ws_new['E:F']:
for y in x:
y.style = content
for x in self.ws_new['H:K']:
for y in x:
y.style = content
for x in self.ws_new[1]:
x.style = title
for x in self.ws_new['D'][1:]:
x.style = content_long
for x in self.ws_new['L'][1:]:
x.style = content_long
for x in self.ws_new['G'][1:]:
x.style = content_long
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 pg01_namedstyle():
''' NamedStyles set (mutable & used when need to apply formatting to different cells at once) '''
# columnEven = [2, 4, 6, 8]
# columnOdd = [3, 5, 7, 9]
#
headerrows = NamedStyle(name='headerrows')
headerrows.font = Font(bold=True, sz=12)
headerrows.alignment = center
#
rooms = NamedStyle(name='rooms')
rooms.font = Font(b=True, sz=12)
rooms.alignment = Alignment(horizontal='left', vertical='center')
#
subtitles = NamedStyle(name="subtitles")
subtitles.font = Font(i=True, size=9)
subtitles.alignment = left
#
rightAlign = NamedStyle(name='rightAlign')
rightAlign.font = Font(b=True, i=True, sz=10)
rightAlign.alignment = right
wb.save('Plymouth_Daily_Rounds.xlsx')
#
# Header Rows
sheet['A2'].style = rightAlign
sheet['A3'].style = 'rightAlign'
sheet['B3'].style = headerrows
sheet['C3'].style = 'headerrows'
sheet['D3'].style = 'headerrows'
sheet['E3'].style = 'headerrows'
# Room Divisions
sheet['A4'].style = rooms
sheet['A20'].style = 'rooms'
sheet['A28'].style = 'rooms'
sheet['A32'].style = 'rooms'
# Subtitles
sheet['A5'].style = subtitles
sheet['A8'].style = 'subtitles'
sheet['A21'].style = 'subtitles'
sheet['A33'].style = 'subtitles'
sheet['A40'].style = 'subtitles'
sheet['A44'].style = 'subtitles'
# Cell-specific adjustments
# A1
a1 = sheet['A1']
a1.font = Font(size=12, b=True, i=True, color='FF0000')
a1.alignment = center
a1.value = 'Note: When doing rounds be aware for unusual smells, sounds, sights, or anything not normal.'
sheet['A47'].alignment = leftTop
sheet['A47'].font = Font(b=True)
wb.save('Plymouth_Daily_Rounds.xlsx')
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 custom_research_base(ws1, d1, d2, result_query, research_title):
style_border = NamedStyle(name="style_border_ca")
bd = Side(style='thin', color="000000")
style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd)
style_border.font = Font(bold=True, size=11)
style_border.alignment = Alignment(wrap_text=True,
horizontal='center',
vertical='center')
ws1.cell(row=1, column=1).value = 'Услуга:'
ws1.cell(row=1, column=2).value = research_title
ws1.cell(row=2, column=1).value = 'Период:'
ws1.cell(row=3, column=1).value = f'c {d1} по {d2}'
columns = [
('Направление', 15),
('Пациент', 45),
('Пол', 10),
('Дата рождения', 26),
('Возраст', 10),
('Адрес', 40),
('Исполнитель', 35),
('Код врача', 15),
]
columns2 = [(i, 25) for i in result_query["custom_fields"]]
columns.extend(columns2)
row = 5
for idx, column in enumerate(columns, 1):
ws1.cell(row=row, column=idx).value = column[0]
ws1.column_dimensions[get_column_letter(idx)].width = column[1]
ws1.cell(row=row, column=idx).style = style_border
return ws1
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 write_to_spreadsheet(self):
'''Formats and writes data to spreadsheet.'''
wb = Workbook()
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=False, size=11)
bd = Side(style='thin', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight) # Register named style
sh1 = wb.active
sh1.title = 'Purchasing Exceptions'
sh1.append(['Part Number'])
sh1['A1'].font = Font(bold=True, size=11)
sh1['A1'].border = Border(left=bd, top=bd, right=bd, bottom=bd)
sh1['A1'].alignment = Alignment(horizontal='center')
rnum = 2
sh1.column_dimensions['A'].width = (30)
# col_width2 = 0
for item in self.part_list:
# sh1_tool_list_data = item
sh1.cell(row=rnum, column=1).value = item
sh1.cell(row=rnum, column=1).style = 'highlight'
# if len(str(sh1_description)) > col_width2:
# col_width2 = len(str(sh1_description))
rnum += 1
# sh1.column_dimensions['A'].width = (col_width2 * 1.125)
save_name = (('{}/Purchasing Exception Report.xlsx').format(self.idir))
self.saved_as_string = ('Results file: {}').format(save_name)
self.file_listbox.insert(tk.END, self.count_string)
self.file_listbox.insert(tk.END, self.saved_as_string)
self.file_listbox.see(tk.END)
wb.save(save_name)
os.startfile(save_name)
def create(data):
workbook = Workbook()
sheet = workbook.active
file_name = 'utils/tugs_scheduler.xlsx'
# setting
sheet.title = "scheduler"
sheet["A1"] = "ID"
sheet["B1"] = "REMOLCADOR"
sheet["C1"] = "ACTUAL"
sheet["D1"] = "PRÓXIMA"
sheet["E1"] = "TIPO"
# Let's create a style template for the header row
header = NamedStyle(name="header")
header.font = Font(bold=True)
header.border = Border(bottom=Side(border_style="thin"))
header.alignment = Alignment(horizontal="center", vertical="center")
header_row = sheet[1]
for cell in header_row:
cell.style = header
# put data into the file
for item in data:
sheet.append(item)
# validate date (coming date <= today date)
# whether validation is TRUE, add RED color to date
big_red_text = Font(color=colors.BLUE)
for i in sheet["D"][1:]:
if i.value <= datetime.date.today():
i.font = big_red_text
i.value = i.value.strftime("%d/%m/%Y")
# save data into the file
workbook.save(filename=file_name)
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 __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 add_default_styles(workbook):
""" plug in reusable styles """
border = Side(style="thin", color="000000")
thick_border = Side(style="thick", color="000000")
default_style = NamedStyle(name="default_style")
default_style.font = Font(size=15)
default_style.border = Border(top=border,
left=border,
bottom=border,
right=border)
workbook.add_named_style(default_style)
top_row = copy(default_style)
top_row.name = "top_row"
top_row.border = Border(top=thick_border,
left=border,
bottom=border,
right=border)
workbook.add_named_style(top_row)
centered = Alignment(horizontal="center", vertical="center")
for style in (default_style, top_row):
style = copy(style)
style.name = style.name + "_centered"
style.alignment = centered
workbook.add_named_style(style)
style = copy(style)
style.name = style.name + "_no_letter"
style.fill = PatternFill("solid", fgColor="b7b7b7")
workbook.add_named_style(style)
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 patologistology_buh_base(ws1):
style_border = NamedStyle(name="style_border")
bd = Side(style='thin', color="000000")
style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd)
style_border.font = Font(bold=True, size=11)
style_border.alignment = Alignment(wrap_text=True,
horizontal='center',
vertical='center')
ws1.cell(row=2, column=1).value = 'Период:'
columns = [
('Медицинская организация ', 36),
('ФИО пациента ', 25),
('Дата рождения ', 17),
('Номер полиса ОМС ', 30),
('СНИЛС ', 15),
('Дата регистрации', 17),
('Код МКБ 10 заключение ', 12),
('Источник ', 12),
('Код оплаты (категория)', 30),
('Цель ', 26),
('Код мед услуги и категория сложности', 35),
('Направление', 30),
]
for idx, column in enumerate(columns, 1):
ws1.cell(row=4, column=idx).value = column[0]
ws1.column_dimensions[get_column_letter(idx)].width = column[1]
ws1.cell(row=4, column=idx).style = style_border
return ws1
def create_font_style():
border_style = NamedStyle(name="BorderAndFont")
border_style.font = Font(name='Times New Roman', size=10, color='000000')
bd = Side(style='thin', color='000000')
border_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)
border_style.alignment.wrap_text = True
return border_style
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 _set_style(self):
my_style = NamedStyle(name="my_style")
my_style.font = Font(name='Calibri')
bd = Side(style='thin')
my_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)
self.wb.add_named_style(my_style)
return "my_style"
def join_Database():
xlsx_to_formated_xlsx = 'media/qbreports/uploads/xlsx_to_formated_xlsx.xlsx'
downlaod_file_name = 'media/qbreports/uploads/custom_qb_report.xlsx'
joined_table_file_name = pandas_df_join()
csv_to_xlsx, google_sheet = finial_file_creation(joined_table_file_name)
dfUpdated = pd.read_csv(csv_to_xlsx, encoding='latin-1')
dfgdoc = pd.read_csv(google_sheet, encoding='latin-1')
print("creating xlsx files")
wb = Workbook()
ws = wb.active
ws.title = 'Matching_Updated'
ws2 = wb.create_sheet()
ws2.title = 'Vendor Price Check Gdoc'
bold = NamedStyle(name='bold')
bold.font = Font(bold=True)
wb.add_named_style(bold)
cell = WriteOnlyCell(ws)
cell.style = 'bold'
greenFill = PatternFill(start_color='32CD32',
end_color='32CD32',
fill_type='solid')
for row in dataframe_to_rows(dfUpdated, index=False, header=True):
ws.append(row)
for row in dataframe_to_rows(dfgdoc, index=False, header=True):
ws2.append(row)
wb.save(xlsx_to_formated_xlsx)
wb = load_workbook(filename=xlsx_to_formated_xlsx)
ws = wb.active
for row in ws.iter_rows():
if row[1].value == "Vendor Name":
for cell in row:
ws[str(cell.coordinate)].font = Font(bold=True)
if row[1].value == None:
for cell in row:
if cell.value != None:
ws[str(cell.coordinate)].fill = greenFill
if row[1].value != None:
for cell in row:
ws[str(cell.coordinate)].font = Font(bold=True)
wb.save(downlaod_file_name)
print("created xlsx files")
qb_Import_File_delete_everything()
return downlaod_file_name
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 style_base(name):
style = NamedStyle(name=name)
style.font = create_font(size=9)
style.alignment = Alignment(vertical='top', wrap_text=True)
side = Side(style='thin')
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def save_price(a):
wb = openpyxl.Workbook()
wb.guess_types = True
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=15)
highlight.alignment = Alignment(horizontal='center', vertical='center')
wb.add_named_style(highlight)
for i in range(len(a)):
salename = a[i].get('sale')
url = a[i].get('url')
wb.create_sheet(index=i, title=salename)
wb[salename]['A1'] = "{}型号".format(salename)
wb[salename]['B1'] = "价格"
wb[salename]['A1'].style = highlight
wb[salename]['B1'].style = highlight
wb[salename].column_dimensions['A'].width = 50
c = 2
for each in find_url(url)[0]:
wb[salename].append(each)
ws = wb[salename]['A{}'.format(c)]
ws1 = wb[salename]['D{}'.format(c)]
ws.alignment = Alignment(horizontal='center', vertical='center')
ws1.alignment = Alignment(horizontal='center', vertical='center')
c += 1
wb[salename]['C1'] = find_url(url)[1]
wb[salename].merge_cells('C1:D1')
wb[salename]['C1'].style = highlight
time.sleep(0.5)
wb.save('报价.xlsx')
def style_base(name):
style = NamedStyle(name=name)
style.font = create_font(size=9)
style.alignment = Alignment(vertical='top', wrap_text=True)
side = Side(style='thin')
style.border = Border(left=side, right=side, top=side, bottom=side)
return style
def dispansery_plan_base(ws1, d1, d2):
style_border = NamedStyle(name="style_border_ca")
bd = Side(style='thin', color="000000")
style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd)
style_border.font = Font(bold=True, size=14)
style_border.alignment = Alignment(wrap_text=True,
horizontal='justify',
vertical='center')
ws1.cell(row=1, column=1).value = 'Д-учет план:'
ws1.cell(row=2, column=1).value = f'{d1} {d2}'
columns = [
('№ карты', 13),
('ФИО', 55),
('Дата рождения', 15),
('Диагноз', 65),
('Месяц план', 20),
]
for idx, column in enumerate(columns, 1):
ws1.cell(row=4, column=idx).value = column[0]
ws1.column_dimensions[get_column_letter(idx)].width = column[1]
ws1.cell(row=4, column=idx).style = style_border
return ws1
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 __cell_style():
#adding style
highlight = NamedStyle(name="highlight")
highlight.font = Font(name='Ebrima',size=8,)
highlight.alignment=Alignment(horizontal='center')
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
return highlight
def normal_style():
style2 = NamedStyle("normal")
style2.font = Font(size=12, color="000000")
# XSSFFont font = (XSSFFont) workbook.CreateFont()
# font.FontHeight = 400.0
# style2.SetFont(font)
style2.alignment = Alignment(horizontal='center', wrap_text=True)
return style2
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
# ws3 = wb.create_sheet(title="Data")
# # for row in range(10, 20):
# # for col in range(27, 54):
# # _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
# print(ws2['AA10'].value)
# wb.save(filename = '3sheet.xlsx')
# wb = load_workbook(filename = '3sheet.xlsx')
# sheet_ranges = wb['first']
# print(sheet_ranges['B1'].value)
# #Edit Page Setup
wb=Workbook()
ws=wb.active
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
#Creating a Named Style
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
#use st yle
wb.add_named_style(highlight)
ws['A1'].style = highlight
ws['D5'].style = 'highlight'
wb.save(filename = 'stylename1.xlsx')
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
def style_header2():
style = NamedStyle(name='header2')
style.font = create_font(size=16)
return style
def style_header():
style = NamedStyle(name='header')
style.font = create_font(size=20)
return style
def export_to_excel(self, bag, export_path=None):
def as_text(value):
if value is None:
return ""
return str(value)
if len(bag) == 0:
return None
try:
# create workbook
wb = Workbook()
# create header style
highlight = NamedStyle(name="highlight")
highlight.font = Font(name='DejaVu Sans', bold=True, size=10)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight)
# create data style
normal = NamedStyle(name="normal")
normal.font = Font(name='DejaVu Sans', bold=False, size=10)
wb.add_named_style(normal)
# Get worksheet
ws = wb.active
# build header
header = []
for field in HEADER_FIELDS:
header.append(str(HEADER[field]))
ws.append(header)
# Set data
data = []
for sid in bag:
row = []
metadata = self.srvdtb.get_sapnote_metadata(sid)
for field in HEADER_FIELDS:
if field == 'collections':
cols = ', '.join([self.srvclt.get_name_by_cid(col) for col in metadata[field]])
row.append(cols)
elif field == 'releasedon':
excel_date = self.srvutl.get_excel_date(metadata[field])
row.append(excel_date)
else:
row.append(str(metadata[field]))
ws.append(row)
data.append(row)
# assign style to header
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
cell = col + '1'
ws[cell].style = 'highlight'
# assign style to data
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
for i in range(len(bag)):
cell = col + '%d' % (i + 2)
ws[cell].style = 'normal'
# adjust columns width
try:
for column_cells in ws.columns:
length = max(len(as_text(cell.value)) for cell in column_cells)
ws.column_dimensions[COL[column_cells[0].column]].width = length
except Exception as error:
self.log.error(error)
self.log.error(self.get_traceback())
self.log.error("This piece of code isn't working on Windows...")
# header autofilter and sorting
ws.auto_filter.ref = "A1:J1"
ws.auto_filter.add_sort_condition("I2:I%d" % len(bag))
# save to export path
wb.save(export_path)
return True
except Exception as error:
self.log.error(error)
self.log.error(self.get_traceback())
return False
