wb = Workbook()
ws = wb.active
for i in range(1, 20):
ws.append(range(300))
ws.merge_cells("A1:B5")
ws.unmerge_cells("A1:B5")
ws.merge_cells(start_row=2, start_column=2, end_row=5, end_column=5)
cell = ws['B2']
cell.font = Font(color=colors.RED, size=20, italic=True)
cell.value = 'Merged Cell'
cell.alignment = Alignment(horizontal='right', vertical='bottom')
cell.fill = GradientFill(stop=("000000", "FFFFFF"))
wb.save('text.xlsx')
highlight = NamedStyle(name='highlight')
highlight.font = Font(bold=True)
bd = Side(style='thick', color='000000')
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
highlight.fill = PatternFill('solid', fgColor='FFFF00')
count = 0
for col in ws.iter_cols(min_col=8, min_row=1, max_col=30, max_row=30):
col[count].style = highlight
count = count + 1
wb.save('highlight.xlsx')
def _convert_to_fill(cls, fill_dict):
"""
Convert ``fill_dict`` to an openpyxl v2 Fill object
Parameters
----------
fill_dict : dict
A dict with one or more of the following keys (or their synonyms),
'fill_type' ('patternType', 'patterntype')
'start_color' ('fgColor', 'fgcolor')
'end_color' ('bgColor', 'bgcolor')
or one or more of the following keys (or their synonyms).
'type' ('fill_type')
'degree'
'left'
'right'
'top'
'bottom'
'stop'
Returns
-------
fill : openpyxl.styles.Fill
"""
from openpyxl.styles import PatternFill, GradientFill
_pattern_fill_key_map = {
'patternType': 'fill_type',
'patterntype': 'fill_type',
'fgColor': 'start_color',
'fgcolor': 'start_color',
'bgColor': 'end_color',
'bgcolor': 'end_color',
}
_gradient_fill_key_map = {
'fill_type': 'type',
}
pfill_kwargs = {}
gfill_kwargs = {}
for k, v in fill_dict.items():
pk = gk = None
if k in _pattern_fill_key_map:
pk = _pattern_fill_key_map[k]
if k in _gradient_fill_key_map:
gk = _gradient_fill_key_map[k]
if pk in ['start_color', 'end_color']:
v = cls._convert_to_color(v)
if gk == 'stop':
v = cls._convert_to_stop(v)
if pk:
pfill_kwargs[pk] = v
elif gk:
gfill_kwargs[gk] = v
else:
pfill_kwargs[k] = v
gfill_kwargs[k] = v
try:
return PatternFill(**pfill_kwargs)
except TypeError:
return GradientFill(**gfill_kwargs)
ft2 = Font(color="00ff00", italic=True)
for sheet in wb:
print(sheet.title + " working")
if sheet.title == "sheet_03":
# wooksheet = wb.get_sheet_by_name(sheet.title)
wooksheet = wb[sheet.title]
wooksheet['A1'] = 4
wooksheet['A1'].font = ft1
wooksheet['A1'].fill = PatternFill("solid", fgColor="0000ff")
x = 3
y = 3
wooksheet.cell(row=y, column=x).value = 23
wooksheet.cell(row=y, column=x).font = ft2
wooksheet.cell(row=y,
column=x).fill = GradientFill(stop=("ffffff",
"0000ff"))
if sheet.title == "sheet_01":
# wooksheet = wb.get_sheet_by_name(sheet.title)
wooksheet = wb[sheet.title]
wooksheet['A4'] = 4
print(sheet.title + " done")
# wb_name_list[0]['A4']=
# ws0['A4'] = 4
# ws2['A1'] = 1
# x=3
# y=3
# ws2.cell(row=y, column=x).value=23
# ft1 = Font(color=colors.RED, italic=True)
b2 = ws['B2']
b2.value = 'FishC'
#创建字体为 加粗 颜色红色
bold_red_font = Font(bold=True, color='FF0000')
b2.font = bold_red_font
b3 = ws['B3']
b3.value = 'FishC'
#创建字体为 字号16 斜线 斜体,颜色蓝色
italic_strike_16font = Font(size=16, italic=True, strike=True, color='0000FF')
b3.font = italic_strike_16font
#创建填充为 纯色 颜色黄色
yellow_fill = PatternFill(fill_type='solid', fgColor="FFFF00")
b2.fill = yellow_fill
#创建渐变填充 从红到绿
red_to_green_fill = GradientFill(type='linear', stop=('FF0000', "00FF00"))
b3.fill = red_to_green_fill
#创建边框对象
thin_side = Side(border_style='thin', color='000000')
double_side = Side(border_style='double', color='FF0000')
#绘制对角线,两条都为真
b2.border = Border(diagonal=thin_side, diagonalUp=True, diagonalDown=True)
b3.border = Border(left=double_side,
top=double_side,
right=double_side,
bottom=double_side)
#合并单元格
ws.merge_cells(('A4:C4'))
ws['A4'].value = 'I love FishC.com'
#设置对齐方式为 居中
worksheet.title = 'My Awesome Sheet'
worksheet['A1'] = 'Hello Openpyxl'
workbook.save('excel/awesomeworkbook.xlsx')
# How To Format Workbooks
from openpyxl.styles import Font, Alignment, GradientFill
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
sheet.insert_rows(1, 2)
sheet.merge_cells('A1:O2')
cell = sheet['A1']
cell.font = Font(color='007742', size=20, italic=True)
cell.value = 'Super Cool And Stylish Spreadsheet'
cell.alignment = Alignment(horizontal='right', vertical='center')
cell.fill = GradientFill(stop=('000000', 'ffffff'))
workbook.save('excel/stylish.xlsx')
# Named Styles In Openpyxl
from openpyxl.styles import Font, Alignment, GradientFill, NamedStyle, Side, Border, PatternFill
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
sheet.insert_rows(1, 2)
sheet.merge_cells('A1:O2')
cell = sheet['A1']
cell.font = Font(color='007742', size=20, italic=True)
cell.value = 'Super Cool And Stylish Spreadsheet'
cell.alignment = Alignment(horizontal='right', vertical='center')
cell.fill = GradientFill(stop=('000000', 'ffffff'))
def create_report():
#список недель для формирования необходимых листов
report = load_workbook(filename=os.getcwd() + '\Reports\\Client\\' +
'Template.xlsm',
read_only=False,
keep_vba=True)
postclick_week_set = set()
plan_week_set = set()
for jsonplan in os.listdir(os.getcwd() + '\\MP\\JSON\\'):
if jsonplan.endswith('.json'):
with open(str(os.getcwd()) + '\\MP\\JSON\\' + jsonplan,
'r') as infile:
placement_dict = json.load(infile)
plan_week_set.update(placement_dict.get('plan_weeks'))
for value in placement_dict['postclick']:
postclick_week_set.add(int(value['weeknumber']))
for week in sorted(postclick_week_set):
#начало переменных форматирования
medium = Side(border_style="medium", color="000000")
borders = Border(top=medium, left=medium, right=medium, bottom=medium)
yellow_fill = GradientFill(stop=("ffff99", "ffff99"))
text_rotation = Alignment(textRotation=90,
horizontal='center',
vertical='center')
#конец переменных форматирования
source = report.active
target = report.copy_worksheet(source)
fields_row = get_fields_row(target)
first_stage_row = target.max_row + 1
for stage in [
'Awareness', 'Consideration', 'Preference', 'Action', 'Loyalty'
]:
if first_stage_row != target.max_row + 1:
target.cell(row=first_stage_row,
column=2).alignment = text_rotation
target.merge_cells(start_row=first_stage_row,
end_row=target.max_row,
start_column=2,
end_column=2)
style_merged_cells(target,
first_stage_row,
target.max_row,
2,
2,
border=borders,
fill=yellow_fill)
first_stage_row = target.max_row + 1
for category in ['OLV', 'Programmatic', 'Social Media', 'SEA']:
category_flag = True
for jsonplan in os.listdir(os.getcwd() + '\\MP\\JSON\\'):
if jsonplan.endswith('.json'):
with open(
str(os.getcwd()) + '\\MP\\JSON\\' + jsonplan,
'r') as infile:
placement_dict = json.load(infile)
if placement_dict.get(
'stage') == stage and placement_dict.get(
'category') == category:
last_column = 1
last_row = target.max_row + 1
if category_flag:
target.cell(row=last_row,
column=last_column + 1,
value=stage)
target.merge_cells(
start_row=last_row,
start_column=last_column + 2,
end_row=last_row,
end_column=target.max_column - 1)
target.cell(row=last_row,
column=last_column + 2,
value=category)
style_merged_cells(target,
last_row,
last_row,
last_column + 2,
target.max_column - 1,
border=borders,
fill=yellow_fill)
category_flag = False
last_row += 1
while target.cell(
row=fields_row,
column=last_column).value != "end":
cell = get_value_from_placement_dict(
target.cell(row=fields_row,
column=last_column).value,
placement_dict, week)
target.cell(row=last_row,
column=last_column,
value=cell.value)
target.cell(
row=last_row, column=last_column
).number_format = copy(
target.cell(
row=fields_row,
column=last_column).number_format)
target.cell(
row=last_row,
column=last_column).fill = copy(
cell.fill)
target.cell(
row=last_row,
column=last_column).alignment = copy(
target.cell(
row=fields_row,
column=last_column).alignment)
target.cell(
row=last_row,
column=last_column).border = copy(
target.cell(
row=fields_row,
column=last_column).border)
last_column += 1
last_column = 1
while target.cell(row=fields_row, column=last_column).value != "end":
if re.search(
'\d{1,2}',
str(target.cell(row=fields_row, column=last_column).value)
) and (int(target.cell(row=fields_row, column=last_column).value)
in plan_week_set or int(
target.cell(row=fields_row, column=last_column).value)
in postclick_week_set):
target.cell(row=fields_row,
column=last_column).font = Font(bold=True)
total_formula = get_total(
target.cell(row=fields_row, column=last_column), target)
target.cell(row=last_row + 1,
column=last_column,
value=total_formula)
if total_formula != "":
target.cell(row=last_row + 1,
column=last_column).border = Border(
top=Side(style='medium'),
bottom=Side(style='medium'),
left=Side(style='thin'),
right=Side(style='thin'))
else:
target.cell(row=last_row + 1,
column=last_column).border = Border(
top=Side(style='medium'),
bottom=Side(style='medium'))
target.cell(row=last_row + 1,
column=last_column).font = Font(bold=True)
target.cell(row=last_row + 1,
column=last_column).number_format = copy(
target.cell(row=fields_row,
column=last_column).number_format)
target.cell(row=last_row + 1, column=last_column).alignment = copy(
target.cell(row=fields_row, column=last_column).alignment)
last_column += 1
d = str(datetime.datetime.now().year) + '-W' + str(week)
target.title = datetime.datetime.strftime(
datetime.datetime.strptime(d + '-1', "%Y-W%W-%w"),
"%d-%b-%Y") + " -- " + datetime.datetime.strftime(
datetime.datetime.strptime(d + '-1', "%Y-W%W-%w") +
datetime.timedelta(days=6), "%d-%b-%Y")
report.save(os.getcwd() + '\Reports\\Client\\' + 'Report_week' +
str(datetime.datetime.today().isocalendar()[1]) + '.xlsm')
return
wb = Workbook()
ws = wb.active
b2 = ws["B2"]
b2.value = "FishC"
bold_red_font = Font(bold=True, color="FF0000")
b2.font = bold_red_font
b3 = ws["B3"]
b3.value = "Fish"
Ita_strike_font = Font(size=16, italic=True, strike=True, color="0000FF")
b3.font = Ita_strike_font
yellow_fill = PatternFill(fill_type="solid", fgColor="FFFF00")
b2.fill = yellow_fill
red2gre = GradientFill(fill_type="linear", stop=("FF0000", "00FF00"))
b3.fill = red2gre
thin_side = Side(border_style="thin", color="000000")
double_side = Side(border_style="double", color="FF0000")
b2.border = Border(diagonal=thin_side, diagonalUp=True, diagonalDown=True)
ws.merge_cells("A1:C2")
ws["A1"].value = "FishC"
center_align = Alignment(horizontal="center", vertical="center")
ws["A1"].alighment = center_align
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
highlight.alignment = Alignment(horizontal="center", vertical="center")
wb.add_named_range(highlight)