def setupDefaultStrings():
print("Setup sheet for default strings ...")
global SHEET
SHEET.cell(ROW_HEADING, COL_SLNO).value = "Sl.No"
SHEET.cell(ROW_HEADING, COL_KEY).value = "String Key"
SHEET.cell(ROW_HEADING, COL_DEFAULT).value = "Default"
SHEET.cell(ROW_HEADING, COL_SLNO).font = Font(bold=True, size="13")
SHEET.cell(ROW_HEADING, COL_KEY).font = Font(bold=True, size="13")
SHEET.cell(ROW_HEADING, COL_DEFAULT).font = Font(bold=True, size="13")
SHEET.column_dimensions["A"].width = 6
SHEET.column_dimensions["B"].width = 25
SHEET.column_dimensions["C"].width = 75
def add_worksheet_header(worksheet: openpyxl.worksheet.worksheet.Worksheet):
"""
Adds a PowerPlan header for any tab on the PowerPlan DCW. This only adds
the PowerPlan header and not the links to the different tabs
"""
bg_fill = GradientFill(type='linear',
degree=90,
stop=[
Stop(position=0, color=Color(rgb='FF2A92D0')),
Stop(position=1, color=Color(rgb='FF0F6EB4'))
])
bg_font = Font(name='Segoe UI Light',
sz=20,
family=2.0,
b=True,
color=Color(rgb='FFFFFFFF'))
for col in range(1, 17):
active_cell = worksheet.cell(row=1, column=col)
active_cell.font = bg_font
active_cell.fill = bg_fill
# PowerPlan name is already denoted on the Table of Contents tab, so
# refer to that tab for the name
worksheet['A1'].value = '=\'Table of Contents\'!B2'
return worksheet
def add_links_on_toc_tab(workbook: openpyxl.Workbook, output_file: str = None):
"""
Adds Excel links to appropriate cells on the Table of Contents tab
"""
if output_file is None:
output_file = "test_output.xlsx"
sheet_name = "Table of Contents"
util.check_toc_tab_exists(workbook, sheet_name=sheet_name)
worksheet = workbook["Table of Contents"]
linked_cell_values = [
"Components", "Treatment Schedules", "Plan Attributes"
]
for row in range(9, 12):
active_cell = worksheet.cell(row=row, column=1)
if active_cell.value in linked_cell_values:
active_cell.hyperlink = "{}#'{}'!A1".format(
output_file, active_cell.value)
# TODO: Hyperlink is dependent on output file name
active_cell.style = "Hyperlink"
active_cell.font = Font(
name="Calibri",
family=2,
sz=14,
u="single",
color=Color(theme=10, type="theme"),
)
return workbook
def __write(self,
ws,
key: str,
value,
fill: PatternFill = PatternFill(),
border=Border(top=Side(style='thin', color='000000'),
bottom=Side(style='thin', color='000000'),
left=Side(style='thin', color='000000'),
right=Side(style='thin', color='000000')),
font_size: int = 9):
"""ワークシートの指定したキーにデータを書き込む
Arguments:
ws {} -- 対象のワークシート
key {str} -- キー
value {} -- 書き込む内容
Keyword Arguments:
fill {PatternFill} -- 色の指定 (default: {PatternFill()})
border {Border} -- 罫線情報(default: {Border(top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'), left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000'))})
font_size {int} -- フォントサイズ (default: {9})
"""
ws[key] = value
ws[key].fill = fill
ws[key].border = border
ws[key].font = Font(size=font_size)
def resize_image_to_cell(self, img, cell):
ratio = 1
width = img.width
height = img.height
font = Font(
name='Arial',
sz=11,
)
cell.font = font
#self.wb._named_styles["標準"].font = font
cellHeight = self.get_cell_height_px(cell)
cellWidth = self.get_cell_width_px(cell)
self.logger.info("target cell width, height: {}, {}".format(
cellWidth, cellHeight))
if width > cellWidth:
ratio = cellWidth / width
width *= ratio
height *= ratio
if height > cellHeight:
ratio = cellHeight / height
height *= ratio
width *= ratio
self.logger.info("needs resize cell width, height: {}, {}".format(
width, height))
return self.resize_image(img, round(width), round(height))
def test_style():
from openpyxl.styles import Font, Style
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
new_style = Style(font=Font(bold=True))
ws.append([{'value': 'hello', 'style': new_style}])
assert new_style in wb.shared_styles
def add_phase_label(phase_name: str, row: int,
worksheet: openpyxl.worksheet.worksheet.Worksheet):
phase_bg_fill = PatternFill(
patternType="solid",
fgColor=Color(type="rgb", rgb="FF305496"),
bgColor=Color(type="indexed", indexed=64),
)
phase_font = Font(
name="Calibri",
family=2,
sz=11,
scheme="minor",
vertAlign=None,
color=Color(theme=0, type="theme"),
)
worksheet.cell(row=row, column=1).fill = phase_bg_fill
worksheet.cell(row=row, column=1).font = phase_font
worksheet.cell(row=row, column=1).value = phase_name
for x in range(1, 17):
active_cell = worksheet.cell(row=row, column=x)
active_cell.fill = phase_bg_fill
return worksheet
def format_components_tab_row_3(workbook: openpyxl.Workbook):
'''
Formats row 2 in the Components tab of the DCW
'''
sheet_name = 'Components'
util.check_toc_tab_exists(workbook=workbook, sheet_name=sheet_name)
worksheet = workbook[sheet_name]
field_bg_fill = PatternFill(patternType='solid',
fgColor=Color(rgb='FF48545D', type='rgb'),
bgColor=Color(indexed=64, type='indexed', theme=0))
field_font = Font(name='Calibri', family=2, sz=11, b=True,
scheme='minor', vertAlign=None, color=Color(theme=0, type='theme'))
for col in range(1, 17):
active_cell = worksheet.cell(row=3, column=col)
active_cell.fill = field_bg_fill
active_cell.font = field_font
active_cell.alignment = Alignment(wrapText=True)
util_borders.set_outside_borders(active_cell)
return workbook
def changeCellFrontColorMulti(self, range, color):
startRange = range.split(':')[0]
endRange = range.split(':')[1]
font = Font(color=color)
for rows in self.sheet[startRange:endRange]:
for cell in rows:
self.sheet[cell.coordinate].font = font
def add_phase_properties(
phase_dict: dict, worksheet: openpyxl.worksheet.worksheet.Worksheet
):
attribute_label_dict = OrderedDict(
{
"Phase Description": phase_dict.get("description"),
"Reference Text": phase_dict.get(""),
"Evidence Link": phase_dict.get(""),
"Related Results": phase_dict.get(""),
"Check Alerts on Planning": util.bool_to_str(
phase_dict.get("check_alerts_on_planning_ind")
),
"Check Alerts on Plan Updates": util.bool_to_str(
phase_dict.get("check_alerts_on_plan_updt_ind")
),
"Treatment Schedule": "Yes" if phase_dict.get("treatment_sched") else "No",
"Route for Review": phase_dict.get("route_for_review"),
# TODO: Add fields here to the query,
"Classification": "Oncology",
"Document Reschedule Reason": phase_dict.get(""),
"Build Linked Components Group": phase_dict.get(""),
}
)
row = util.get_next_empty_row_in_col(col=1, worksheet=worksheet)
for k, v in attribute_label_dict.items():
active_cell = worksheet.cell(row=row, column=1)
active_cell.value = k
active_cell.font = Font(bold=True)
active_cell.offset(column=1).value = v
row += 1
return worksheet
def add_phase_row(phase_name: str, worksheet: openpyxl.worksheet.worksheet.Worksheet):
row = util.get_next_empty_row_in_col(col=1, worksheet=worksheet)
worksheet.row_dimensions[row].height = 21.75
bg_fill = GradientFill(
type="linear",
degree=90,
stop=[
Stop(position=0, color=openpyxl.styles.colors.Color(rgb="FF344756")),
Stop(position=1, color=openpyxl.styles.colors.Color(rgb="FF1A2D3B")),
],
)
bg_font = Font(
name="Calibri", sz=16, family=2.0, b=True, color=Color(rgb="FFFFFFFF")
)
phase_row_value = "Phase - {}".format(phase_name)
for col in range(1, 17):
# Non-hyperlinked cells
active_cell = worksheet.cell(row=row, column=col)
active_cell.font = bg_font
active_cell.fill = bg_fill
if col == 1:
util_borders.set_top_bot_l_borders(active_cell)
active_cell.value = phase_row_value
elif col == 16:
util_borders.set_top_bot_r_borders(active_cell)
else:
util_borders.set_top_bot_borders(active_cell)
return worksheet
def store_xml(data,
first_row,
sheet_name,
strenth_col=None,
strenth_color='#ff0000'):
wb = Workbook()
ws = wb.create_sheet(sheet_name, 0)
ws.append(first_row)
for onr in data:
ws.append(onr)
# 关键标红
if strenth_col:
col = chr(64 + strenth_col)
if col < 'A' or col > 'Z':
raise Exception("不支持标红列数多于26")
colB = ws[col]
for cell in colB:
# cell.value = 'sb'
cell.font = Font(color="ff0000")
name = uuid.uuid4()
file_name = str(name) + ".xlsx"
date_dir = get_cur_date()
file_dir = os.path.join(STATIC_ROOT, date_dir)
if not os.path.exists(file_dir):
os.makedirs(file_dir, exist_ok=True)
path = os.path.join(file_dir, file_name)
wb.save(path)
url = PROTOCOL_IP_OR_DOMAIN + STATIC_URL + "/" + date_dir + "/" + file_name
return url
def add_parent_plan_properties_rows(
plan_dict: dict, worksheet: openpyxl.worksheet.worksheet.Worksheet
):
for phase_dict in plan_dict.get("phases").values():
if phase_dict.get("phase_offset_qty"):
phase_offset_dict = phase_dict
break
if "phase_offset_dict" not in locals():
phase_offset_dict = {}
attribute_label_dict = OrderedDict(
{
"Plan Name/Display Description": plan_dict.get("display_description"),
"Description": plan_dict.get("description"),
"Plan Type": plan_dict.get("plan_type"),
"Plan Display Method": plan_dict.get("display_method"),
"Active": "Yes",
"Version": 1,
"Begin Effective Date": "",
"End Effective Date": "",
"PowerPlan Status": "Production",
"Cross Encounter": util.bool_to_str(plan_dict.get("cross_encounter_ind")),
"Evidence Link": plan_dict.get("evidence_link"),
"Allow Diagnosis Propagation": util.bool_to_str(
plan_dict.get("allow_diagnosis_propagation_ind")
),
"Hide Flexed Components": util.bool_to_str(
plan_dict.get("hide_flexed_components")
),
"Use Cycle Numbers": plan_dict.get("use_cycle_numbers_ind"),
"Standard": "Yes" if plan_dict.get("cycle_std_nbr") else "No",
"Standard number of cycles": plan_dict.get("cycle_std_nbr"),
"Range": plan_dict.get(""),
"Range Begin Value": plan_dict.get(""),
"Range End Value": plan_dict.get(""),
"Cycle Increment": plan_dict.get(""),
"Display Standard Number/End Value": plan_dict.get(""),
"Restrict ability to modify Standard/End Value": plan_dict.get(""),
"Cycle Value Display": plan_dict.get("cycle_disp_val"),
"Prompt for Ordering Physician": util.bool_to_str(
plan_dict.get("prompt_for_ordering_physician_ind")
),
"Copy Forward": util.bool_to_str(plan_dict.get("copy_forward_ind")),
"Phase Offsets": plan_dict.get(""),
"Lab Phase": plan_dict.get(""),
"Offset": "{} {}".format(phase_offset_dict.get("phase_offset_qty"), phase_offset_dict.get("phase_offset_unit")),
"Anchor": phase_offset_dict.get("anchor_phase"),
"Plan Ordering Defaults": plan_dict.get(""),
}
)
row = util.get_next_empty_row_in_col(col=1, worksheet=worksheet)
for k, v in attribute_label_dict.items():
active_cell = worksheet.cell(row=row, column=1)
active_cell.value = k
active_cell.font = Font(b=True)
active_cell.offset(column=1).value = v
row += 1
return worksheet
def format_toc_tab(workbook: openpyxl.Workbook):
"""
Formats the table of contents tab for PowerPlan DCWs
"""
sheet_name = "Table of Contents"
util.check_toc_tab_exists(workbook, sheet_name=sheet_name)
worksheet = workbook[sheet_name]
# Column/row dimensions
worksheet.column_dimensions["A"].width = 45
worksheet.column_dimensions["B"].width = 101.42578125
worksheet.row_dimensions[1].height = 28.5
# Background color
bg_fill = GradientFill(
type="linear",
degree=90,
stop=[
Stop(position=0,
color=openpyxl.styles.colors.Color(rgb="FF344756")),
Stop(position=1,
color=openpyxl.styles.colors.Color(rgb="FF1A2D3B")),
],
)
worksheet["A1"].fill = bg_fill
worksheet["B1"].fill = bg_fill
# Font style
worksheet["A1"].font = Font(
name="Calibri",
family=2.0,
b=True,
sz=22,
color=openpyxl.styles.colors.Color(rgb="FFFFFFFF"),
)
worksheet["A1"] = sheet_name
worksheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2)
for row in range(2, 9):
active_cell = worksheet.cell(row=row, column=1)
active_cell.font = Font(name="Calibri", sz=11, b=True)
return workbook
def write_data(sheet, row, data_list):
font = Font(name='Times New Roman', size=10)
coordinates = 'K{}:CF{}'.format(row, row)
cells = sheet[coordinates][0]
for i, cell in enumerate(cells):
cell.value = data_list[i]
cell.font = font
def test_dump_with_font():
from openpyxl.writer.dump_worksheet import WriteOnlyCell
test_filename = _get_test_filename()
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
user_style = Style(font=Font(name='Courrier', size=36))
cell = WriteOnlyCell(ws, value='hello')
cell.style = Style(font=Font(name='Courrier', size=36))
ws.append([cell, 3.14, None])
assert user_style in wb.shared_styles
wb.save(test_filename)
wb2 = load_workbook(test_filename)
ws2 = wb2[ws.title]
assert ws2['A1'].style == user_style
def applyFmt(tblStyle, trStyle, tdStyle, cell, ws):
# resolve all the styles
finalStyle = deepcopy(tblStyle)
if finalStyle == None:
finalStyle = {}
for s in [trStyle, tdStyle]:
if s == None:
continue
for k, v in s.iteritems():
if v == False:
continue
finalStyle[k] = v
font = Font()
for k, v in finalStyle.iteritems():
if k == "italic" and v != False:
font.i = True
if k == "underline" and v != False:
font.u = Font.UNDERLINE_SINGLE
if k == "line-through" and v != False:
font.strikethrough = True
if k == "font_name" and v != False:
font.name = v
if k == "bold" and v == True:
font.bold = True
if k == 'width' and v != "" and v != False:
c, r = coordinate_from_string(cell.coordinate)
m = re.match("([\d\.]+)(\D+)", v)
if m != None:
w = m.group(1)
units = m.group(2)
if units == "in":
w = float(w) * 12
ws.column_dimensions[c].width = w
if k == "color" and v != False:
if v[1] == "#":
font.color = v[1:]
else:
try:
hxcol = webcolors.name_to_hex(v)
font.color = hxcol[1:]
except:
pass
if k == "background-color" and v != False:
c = Color(v[1:])
fill = PatternFill(patternType=fills.FILL_SOLID, fgColor=c)
cell.fill = fill
cell.font = font
def format_components_tab(workbook: openpyxl.Workbook,
output_file: str=None):
'''
Formats the Components tab of the DCW
'''
sheet_name = 'Components'
util.check_toc_tab_exists(workbook=workbook, sheet_name=sheet_name)
worksheet = workbook[sheet_name]
# Column dimensions:
worksheet.column_dimensions['A'].width = 15.85546875
worksheet.column_dimensions['B'].width = 44.0
worksheet.column_dimensions['C'].width = 21.28515625
worksheet.column_dimensions['D'].width = 40
worksheet.column_dimensions['E'].width = 40
worksheet.column_dimensions['F'].width = 9.140625
worksheet.column_dimensions['G'].width = 8.7109375
worksheet.column_dimensions['H'].width = 8.7109375
worksheet.column_dimensions['I'].width = 9.42578125
worksheet.column_dimensions['J'].width = 15.85546875
worksheet.column_dimensions['K'].width = 15.0
worksheet.column_dimensions['L'].width = 11.28515625
worksheet.column_dimensions['M'].width = 9.42578125
# Row dimensions
worksheet.row_dimensions[1].height = 33.75
worksheet.row_dimensions[2].height = 21.75
worksheet.row_dimensions[3].height = 45
# Row 1 formatting
bg_fill = GradientFill(type='linear', degree=90,
stop=[
Stop(
position=0, color=openpyxl.styles.colors.Color(rgb='FF2A92D0')
),
Stop(
position=1, color=openpyxl.styles.colors.Color(rgb='FF0F6EB4')
)
])
bg_font = Font(name='Segoe UI Light', sz=20,
family=2.0, b=True, color=Color(rgb='FFFFFFFF'))
for col in range(1, 17):
# Non-hyperlinked cells
active_cell = worksheet.cell(row=1, column=col)
active_cell.font = bg_font
active_cell.fill = bg_fill
worksheet = add_links_header_to_components_tab(worksheet,
output_file=output_file)
return workbook
def __export_to_xlsx_steps(work_book, features):
"""stylborder son los bordes de la tablita"""
styleborder = Style(font=Font(bold=False), border=Border(top=Side(border_style='thin', color=colors.BLACK),
left=Side(border_style='thin', color=colors.BLACK),
bottom=Side(border_style='thin', color=colors.BLACK),
right=Side(border_style='thin', color=colors.BLACK)))
#tomamos los steps del .feature y demas
steps = _gather_steps(features)
#nos paramos en la pestana activa
work_sheet = Workbook.get_active_sheet(work_book)
# y le ponemos el titulo
work_sheet.title = 'Execution Steps'
#nos paramos en la primer celda
row_index = 1
work_sheet['A1'].style = styleborder
work_sheet['A1'].value = 'Step'
work_sheet['B1'].style = styleborder
work_sheet['B1'].value = 'Ocurrencias'
work_sheet['C1'].style = styleborder
work_sheet['C1'].value = 'Ejecuciones'
work_sheet['D1'].style = styleborder
work_sheet['D1'].value = 'Tiempo promedio'
work_sheet['E1'].style = styleborder
work_sheet['E1'].value = 'Tiempo total'
work_sheet['F1'].style = styleborder
work_sheet['F1'].value = 'Scenarios'
#por cada columna le agrega los datos y steps, etc
for step in sorted(steps):
cell = work_sheet.cell(row=row_index + 1, column=1)
cell.value = step
cell.style = styleborder
cell.offset(column=1).value = steps[step]['appearances']
cell.offset(column=1).style = styleborder
cell.offset(column=2).value = steps[step]['quantity']
cell.offset(column=2).style = styleborder
cell.offset(column=3).value = '%.2fs' % \
(steps[step]['total_duration'] /
(steps[step]['quantity'] or 1))
cell.offset(column=3).style = styleborder
cell.offset(column=4).value = '%.2fs' % steps[step]['total_duration']
# cell.offset(column=5).value = steps[step]['scenario']
# cell.offset(column=5).style = styleborder
cell.offset(column=4).style = styleborder
if len(step) > work_sheet.column_dimensions['A'].width:
work_sheet.column_dimensions['A'].width = len(step)
work_sheet.column_dimensions['B'].width = 10
work_sheet.column_dimensions['C'].width = 10
work_sheet.column_dimensions['D'].width = 10
work_sheet.column_dimensions['E'].width = 10
# work_sheet.column_dimensions['F'].width = 20
row_index += 1
def test_ctor(self):
f = Font()
assert f.name == 'Calibri'
assert f.size == 11
assert f.bold is False
assert f.italic is False
assert f.underline == 'none'
assert f.strikethrough is False
assert f.color.value == '00000000'
assert f.color.type == 'rgb'
assert f.vertAlign is None
assert f.charset is None
def test_dump_with_font():
test_filename = _get_test_filename()
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
user_style = Style(font=Font(name='Courrier', size=36))
complex_cell = {'value': 'hello', 'style': user_style}
ws.append([complex_cell, 3.14, None])
wb.save(test_filename)
wb2 = load_workbook(test_filename)
ws2 = wb2[ws.title]
assert ws2['A1'].style == user_style
def applyFmt(tblStyle, trStyle,tdStyle, cell,ws):
# resolve all the styles
finalStyle=deepcopy(tblStyle)
if finalStyle == None:
finalStyle ={}
for s in [trStyle,tdStyle]:
if s==None:
continue
for k,v in s.iteritems():
if v == False:
continue
finalStyle[k]=v
font=Font()
for k,v in finalStyle.iteritems():
if k == "italic" and v!=False:
font.i=True
if k == "underline" and v!=False:
font.u=Font.UNDERLINE_SINGLE
if k == "line-through" and v!=False:
font.strikethrough=True
if k == "font_name" and v!=False:
font.name=v
if k=="bold" and v==True:
font.bold=True
if k=='width' and v != "" and v != False:
c,r=coordinate_from_string(cell.coordinate)
m=re.match("([\d\.]+)(\D+)",v)
if m != None:
w=m.group(1)
units=m.group(2)
if units == "in":
w=float(w)*12
ws.column_dimensions[c].width=w
if k == "color" and v != False:
if v[1]=="#":
font.color = v[1:]
else:
try:
hxcol=webcolors.name_to_hex(v)
font.color=hxcol[1:]
except:
pass
if k == "background-color" and v != False:
c=Color(v[1:])
fill=PatternFill(patternType=fills.FILL_SOLID,fgColor=c)
cell.fill = fill
cell.font=font
def make_excel():
# 1) 기본데이터
serial_data = []
num = []
for i in range(0, 20):
serial_number = e[i]
serial_data.append(serial_number.get())
num.append(i)
selected_comapny = company_name_cmb.get()
title = selected_comapny + " 재셋팅 제품" # 제목
resetting_data = pd.DataFrame({'재셋팅 필요한 제품 S/N': serial_data},
index=num) # 시리얼정보 데이터
today = str(date.today())
tday = today[2:4] + today[5:7] + today[8:] # 시트명 및 파일명 만들기 : ex)220105
file_dir = './' + tday + '_' + selected_comapny + '.xlsx'
# 2) 엑셀파일 만들기
resetting_data.to_excel(file_dir,
sheet_name=tday,
startrow=6,
startcol=1)
wb = openpyxl.load_workbook(file_dir)
sheet = wb[tday]
sheet.cell(2, 2).value = title
sheet['B2'].font = Font(size=16, bold=True, name='HY엽서M')
sheet.cell(4, 2).value = '업체명'
sheet['B4'].font = Font(size=11, bold=True, name='HY엽서L')
sheet.cell(4, 3).value = selected_comapny
sheet.cell(5, 2).value = '발주번호'
sheet['B5'].font = Font(size=11, bold=True, name='HY엽서L')
sheet.cell(5, 3).value = psno_input_txt.get()
sheet.cell(7, 2).value = 'No'
sheet['B7'].font = Font(size=11, bold=True)
sheet['C7'].font = Font(size=11, bold=True)
wb.save(file_dir)
msgbox.showinfo('완료!', '입력완료!')
print('파일생성 / 데이터입력 완료')
def add_links_header(link: str,
col_start: int,
col_end: int,
worksheet: openpyxl.worksheet.worksheet.Worksheet,
output_file: str = None):
"""
Adds linked cells to the header for any tab on the PowerPlan DCW. The
output_file is needed so that the links will point to the different tabs
of the same file, and it needs the file name for some reason
"""
if output_file is None:
output_file = 'test_output.xlsx'
link_bg_fill = PatternFill(patternType='solid',
fgColor=Color(tint=-0.25, type='theme',
theme=0),
bgColor=Color(indexed=64,
type='indexed',
theme=0))
link_font = Font(name='Calibri',
family=2,
sz=11,
u='single',
scheme='minor',
vertAlign=None,
color=Color(theme=10, type='theme'))
link_alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
initial_cell = worksheet.cell(row=1, column=col_start)
initial_cell.value = link
initial_cell.hyperlink = '{}#\'{}\'!A1'.format(output_file, link)
initial_cell.style = 'Hyperlink'
for x in range(col_start, col_end + 1):
active_cell = worksheet.cell(row=1, column=x)
active_cell.font = link_font
active_cell.alignment = link_alignment
active_cell.fill = link_bg_fill
active_cell = util_borders.set_outside_borders(active_cell)
worksheet.merge_cells(start_row=1,
start_column=col_start,
end_row=1,
end_column=col_end)
return worksheet
def edit_font(self,
name='ＭＳ Ｐゴシック',
size=11,
bold=False,
italic=False,
underline='none',
strike=False,
color='FF000000'):
self.cell.font = Font(name=name,
size=size,
bold=bold,
italic=italic,
underline=underline,
strike=strike,
color=color)
def export_file(self, groups, file, file_type):
if file_type == "Comma Seperated Values (*.csv)":
with open(file, 'w', newline='') as f:
writer = csv.writer(f, delimiter=',')
for group in groups:
people = ""
for person in group[1]:
people += person + ", "
row = str(group[0]) + ", " + people
row = row.split(",")
writer.writerow(row)
elif file_type == "Microsoft Excel Spreadsheets (*.xls *.xlsx)":
# Setup the workbook and sheet
wb = openpyxl.Workbook()
sheet = wb['Sheet']
sheet.title = "Generated Groups"
# Setup the header
# TODO: Centre the headers
header_font = Font(bold=True)
people_per_group = len(groups[0][1]) + 1
sheet.cell(1, 1).font = header_font
sheet.cell(1, 1).value = "#"
sheet.merge_cells(start_row=1,
start_column=2,
end_row=1,
end_column=people_per_group)
sheet.cell(1, 2).font = header_font
sheet.cell(1, 2).value = "Groups"
# Put the data in the cells below the header
for group in groups:
people = ""
for person in group[1]:
people += person + ", "
row = str(group[0]) + ", " + people
row = row.split(",")
row[0] = int(row[0])
for column in row:
sheet.cell(group[0] + 1,
row.index(column) + 1).value = column
wb.save(file)
else:
pass
def a():
wb = openpyxl.Workbook()
ws = wb.active
ws['A1'] = 'You should see three logos below'
ws['A2'] = '中国'
ws['B1'] = 'You'
ws['B2'] = '伤害'
ws.sheet_properties.tabColor = "1072BA"
# ws._fonts.add(Font(size=8))
# ws.add_named_style(NamedStyle(font=Font(size=8), builtinId=0))
for row in ws.rows:
for cell in row:
print(cell)
# Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
cell.font = Font(size=8)
wb.save('group.xlsx')
def createFile(filePath):
'''
create an excel file in the filePath
'''
wb = Workbook()
ws = wb.create_sheet('Report', 0)
header = ['P/N', 'Description', 'Storage-Qty', 'Quickbook-Qty', 'Real-Qty']
j = 1
for i in header:
ws.cell(row=1, column=j, value=i).font = Font(color=colors.BLACK,
sz=13,
bold=True)
j = j + 1
wb.save(filePath)
return wb
def write_operations_to_cell(excelfilename,
sheettitle,
cell_num,
operation,
weapTextopt=True,
fill_line_color=None,
fix_size=13,
bold=False):
wb = load_workbook(excelfilename)
ws = wb.active
ws.title = sheettitle
ws[cell_num].alignment = Alignment(wrapText=weapTextopt, vertical='center')
ws[cell_num] = operation
if fill_line_color:
for i in range(12):
color_fill_cellnum = "".join([chr(65 + i), cell_num[1:]])
ws[color_fill_cellnum].fill = PatternFill(fill_type='solid',
fgColor=fill_line_color)
ws[cell_num].font = Font(size=fix_size, bold=bold)
wb.save(filename=excelfilename)
def write_day(day, xl_parser):
"""Setup day to table cells"""
sheet = xl_parser.work_book.get_active_sheet()
# Count summary rows in the day
sum_rows = 0
for it in day.notes + day.kvants:
sum_rows += len(it.teachers)
# Shift the pointer to setup the table without splitting days
while xl_parser.cur_row % SHIFT_ROW == 0 or (
xl_parser.cur_row % SHIFT_ROW) + sum_rows > SHIFT_ROW:
xl_parser.cur_row += 1
# Merge cells to setup the date
sheet.merge_cells(start_column=COLUMNS['title'],
end_column=COLUMNS['class'],
start_row=xl_parser.cur_row,
end_row=xl_parser.cur_row)
# Setup the date of the day at the center and bold it
cell = sheet.cell(row=xl_parser.cur_row, column=COLUMNS['title'])
cell.value = day.date
cell.alignment = Alignment(horizontal='center')
cell.font = Font(bold=True)
# Setup title's borders
for column in range(COLUMNS['title'], COLUMNS['class'] + 1):
sheet.cell(row=xl_parser.cur_row,
column=column).border = border('medium')
# Iterate rows of the table
xl_parser.cur_row += 1
for kvant in day.kvants:
write_note(kvant, xl_parser)
for note in day.notes:
write_note(note, xl_parser)
def create_pattern_xlsx():
"""This function to create template xlsx file with style. This file is called Match_Statistic.xlsx."""
bold_side: Side = Side('hair', color=Color())
bold_font: Font = Font('Roboto', bold=True)
bold_border: Border = Border(left=bold_side,
right=bold_side,
top=bold_side,
bottom=bold_side)
column_number: int = 1
match_statistic_xlsx: Workbook = openpyxl.Workbook()
match_statistic_xlsx.remove(match_statistic_xlsx['Sheet'])
match_statistic: Worksheet = match_statistic_xlsx.create_sheet(
'Match statistic')
match_statistic.merge_cells('H1:J2')
match_statistic.merge_cells('L1:AF2')
match_statistic['H1'].font = bold_font
match_statistic['L1'].font = bold_font
match_statistic['H1'].alignment = Alignment('center')
match_statistic['L1'].alignment = Alignment('center')
match_statistic['H1'].border = bold_border
match_statistic['L1'].border = bold_border
match_statistic['H1'].value = 'Average odds'
match_statistic[
'L1'].value = 'Названия букмекерских контор и коэффициенты на победу фаворита'
for col_name in COLUMNS:
match_statistic[
f'{get_column_letter(column_number)}3'].font = bold_font
match_statistic[
f'{get_column_letter(column_number)}3'].border = bold_border
match_statistic[f'{get_column_letter(column_number)}3'] = col_name
column_number += 1
match_statistic_xlsx.save('Match_Statistic.xlsx')
match_statistic_xlsx.close()