import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Border, Side, colors, Font, Alignment, GradientFill, PatternFill
# refer https://openpyxl.readthedocs.io/en/stable/index.html
def set_column_by_list(work_sheet, in_list, column_name, column_width,
start_raw, fill_color, font_color):
s_start = start_raw
for s in in_list:
cell_name = column_name + str(s_start)
cell = work_sheet[cell_name]
cell.value = s
cell.font = Font(b=True, color=font_color)
work_sheet.column_dimensions[column_name].width = column_width
s_start += 1
# first raw is different
cell = column_name + str(start_raw)
# 182, 215, 241 B6D7F1
work_sheet[cell].fill = PatternFill("solid", fgColor=fill_color)
def create_corner(work_sheet, corner_location, row_end, column_end, fill_color, cell_value=None):
col = corner_location[0:1]
num = corner_location[1:3]
work_sheet.merge_cells(corner_location + ":" + column_end + num)
column_cell = work_sheet[corner_location]
column_cell.fill = PatternFill("solid", fgColor=fill_color) # 0, 112, 216
column_cell.value = cell_value
column_cell.font = Font(b=True, color="0070D8")
column_end_str = corner_location[0:1] + str(row_end)
raw_start = col + str(int(num) + 1)
work_sheet.merge_cells(raw_start + ":" + column_end_str)
raw_cell = work_sheet[raw_start]
raw_cell.fill = PatternFill("solid", fgColor=fill_color) # 216, 239, 218
raw_cell.alignment = Alignment(horizontal="center", vertical="center")
E_column_vaule = ["デバイス名[1]", "マウントポイント[1]", "ファイルシステム[1]", "dump[1]",
"fsck[1]", "デバイス名[1]", "マウントポイント[2]", "ファイルシステム[2]", "dump[2]",
"fsck[2]"]
class excelTemplate:
def __init__(self):
self.file_path = "general_template.xlsx"
self.wb = Workbook()
self.work_sheet = self.wb.active
# Sheet title
self.work_sheet.title = "log"
self.font_color = "FFFFFF" #"000000"
self.left_header_column = [‘B‘, ‘C‘, ‘D‘, ‘E‘, ‘F‘, ‘G‘, ‘H‘, ‘I‘, ‘J‘, ‘K‘, ‘L‘, ‘M‘, ‘N‘, ‘O‘, ‘P‘, ‘Q‘, ‘R‘]
def set_title(self):
self.work_sheet.merge_cells(‘B2:F2‘)
top_left_cell = self.work_sheet[‘B2‘]
top_left_cell.value = ‘项目‘
double = Side(border_style="double", color="FFFFFF")
index = 0
for column in self.left_header_column:
cell = self.work_sheet[column + "2"]
if index == 0:
cell.border = Border(top=double, left=double, bottom=double)
if index == len(self.left_header_column) - 1:
cell.border = Border(top=double, right=double, bottom=double)
cell.border = Border(top=double, bottom=double)
index += 1
# 198, 224, 180
top_left_cell.fill = PatternFill("solid", fgColor="C6E0B4")
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
self.work_sheet.merge_cells(‘G2:Q2‘)
second_left_cell = self.work_sheet[‘G2‘]
second_left_cell.value = "RHEL7"
second_left_cell.alignment = Alignment(horizontal="center", vertical="center")
u_cell = self.work_sheet[‘U2‘]
v_cell = self.work_sheet[‘V2‘]
u_cell.value = "设定值共有几列不确定"
v_cell.value = "可以依次读取写入"
self.work_sheet.column_dimensions["V"].width = 40
self.work_sheet.column_dimensions["U"].width = 40
def set_header(self):
self.work_sheet.merge_cells(start_row=4, start_column=2,
end_row=8, end_column=1 + len(self.left_header_column))
top_left_cell = self.work_sheet["B4"]
top_left_cell.value = "パラメータ名"
for column in self.left_header_column:
self.work_sheet.column_dimensions[column].width = 3
top_left_cell.fill = PatternFill("solid", fgColor="C6E0B4")
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
# column S
s_list = [‘种别‘, ‘cloud名‘, ‘环境名‘, ‘系统名‘, ‘a名‘, ‘b名‘]
set_column_by_list(self.work_sheet, in_list=s_list, column_name=‘S‘, column_width=8,
start_raw=4, fill_color="B6D7F1", font_color="FFFFFF")
# column T3
self.work_sheet.merge_cells(‘T4:T9‘)
second_left_cell = self.work_sheet[‘T4‘]
second_left_cell.value = "默认值"
second_left_cell.alignment = Alignment(horizontal="center", vertical="center")
# 216, 239, 218
second_left_cell.fill = PatternFill("solid", fgColor="D8EFDA")
self.work_sheet.column_dimensions["T"].width = 40
# column V3
u_list = [‘设定值‘, ‘sys‘, ‘env‘, ‘base‘, ‘ip-x-x-x‘, ‘-‘]
set_column_by_list(self.work_sheet, in_list=u_list, column_name=‘U‘, column_width=20,
start_raw=4, fill_color="D8EFDA", font_color=None)
# column U3
v_list = [‘设定值‘, ‘sys‘, ‘env‘, ‘base‘, ‘ip-x-x-x‘, ‘-‘]
set_column_by_list(self.work_sheet, in_list=v_list, column_name=‘V‘, column_width=20,
start_raw=4, fill_color="D8EFDA", font_color=None)
# column W
self.work_sheet.merge_cells(‘W4:W9‘)
second_left_cell = self.work_sheet[‘W4‘]
second_left_cell.value = "数据类型"
second_left_cell.alignment = Alignment(horizontal="center", vertical="center")
# 216, 239, 218
second_left_cell.fill = PatternFill("solid", fgColor="D8EFDA")
self.work_sheet.column_dimensions["W"].width = 10
def set_content(self):
self.work_sheet["B9"].value = "No."
for j in range(0, 17):
if j != 0:
cell_name = self.left_header_column[j]+str(9)
self.work_sheet[cell_name].value = j
# 9 Raw
for i in range(0, 18):
# start at B10
self.work_sheet["B" + str(10+i)].value = i+1
# C10
create_corner(work_sheet=self.work_sheet, corner_location="C10", column_end="R", row_end="21",fill_color="D8EFDA",
cell_value="disks（logical)")
# D11
create_corner(work_sheet=self.work_sheet, corner_location="D11", column_end="R", row_end="21",
fill_color="D8EFDA", cell_value="properties")
# C22
create_corner(work_sheet=self.work_sheet, corner_location="C22", column_end="V", row_end="24",
fill_color="D8EFDA", cell_value="properties")
# D23
create_corner(work_sheet=self.work_sheet, corner_location="D23", column_end="V", row_end="24",
fill_color="D8EFDA", cell_value="properties")
# C25
create_corner(work_sheet=self.work_sheet, corner_location="C25", column_end="V", row_end="27",
fill_color="D8EFDA", cell_value="timedateclt")
# D25
create_corner(work_sheet=self.work_sheet, corner_location="D26", column_end="V", row_end="27",
fill_color="D8EFDA", cell_value="properties")
# C28
create_corner(work_sheet=self.work_sheet, corner_location="C28", column_end="R", row_end="30",
fill_color="D8EFDA", cell_value="yum (yum.repos)")
# D29
create_corner(work_sheet=self.work_sheet, corner_location="D29", column_end="R", row_end="30",
fill_color="D8EFDA", cell_value="Metadata")
# C31 C32 , C35 are wider
create_corner(work_sheet=self.work_sheet, corner_location="C31", column_end="V", row_end="35",
fill_color="D8EFDA", cell_value="properties")
# C52
create_corner(work_sheet=self.work_sheet, corner_location="D32", column_end="V", row_end="35",
fill_color="D8EFDA", cell_value="properties")
# C36
create_corner(work_sheet=self.work_sheet, corner_location="C36", column_end="V", row_end="42",
fill_color="D8EFDA", cell_value="logrotate(logrotate.conf)")
# D37
create_corner(work_sheet=self.work_sheet, corner_location="D37", column_end="V", row_end="42",
fill_color="D8EFDA", cell_value="properties")
# C43
create_corner(work_sheet=self.work_sheet, corner_location="C43", column_end="R", row_end="48",
fill_color="D8EFDA", cell_value="logrotate(logrotate.conf)")
# D37
create_corner(work_sheet=self.work_sheet, corner_location="D44", column_end="R", row_end="48",
fill_color="D8EFDA", cell_value="properties")
# C49
create_corner(work_sheet=self.work_sheet, corner_location="C49", column_end="V", row_end="51",
fill_color="D8EFDA", cell_value="cron.daily(man-db.cron)")
# D50
create_corner(work_sheet=self.work_sheet, corner_location="D50", column_end="V", row_end="51",
fill_color="D8EFDA", cell_value="properties")
# C52
create_corner(work_sheet=self.work_sheet, corner_location="C52", column_end="V", row_end="54",
fill_color="D8EFDA", cell_value="cron.d_(0hourly)")
# C53
create_corner(work_sheet=self.work_sheet, corner_location="D53", column_end="V", row_end="54",
fill_color="D8EFDA", cell_value="properties")
# C55
create_corner(work_sheet=self.work_sheet, corner_location="C55", column_end="V", row_end="58",
fill_color="D8EFDA", cell_value="cron.d_(0hourly)")
# D56
create_corner(work_sheet=self.work_sheet, corner_location="D56", column_end="V", row_end="58",
fill_color="D8EFDA", cell_value="properties")
# C59
create_corner(work_sheet=self.work_sheet, corner_location="C59", column_end="V", row_end="61",
fill_color="D8EFDA", cell_value="journald(journald.conf)")
# D60
create_corner(work_sheet=self.work_sheet, corner_location="D60", column_end="V", row_end="61",
fill_color="D8EFDA", cell_value="properties")
# C59
create_corner(work_sheet=self.work_sheet, corner_location="C62", column_end="V", row_end="66",
fill_color="D8EFDA", cell_value="kme(sysctl.conf)")
# D60
create_corner(work_sheet=self.work_sheet, corner_location="D63", column_end="V", row_end="66",
fill_color="D8EFDA", cell_value="Metadata")
# E Column word adding
index = 0
for col in E_column_vaule:
self.work_sheet["E" + str(12+index)].value = col
index += 1
self.work_sheet["E24"].value = "LANG"
self.work_sheet["E27"].value = "TimeZone"
self.work_sheet["E30"].value = "rehat"
self.work_sheet["E33"].value = "Files"
self.work_sheet["E34"].value = "missingok |#missingok "
self.work_sheet["E35"].value = "postrotate-endscript"
self.work_sheet["E38"].value = "daily | weekly | monthly | yearly"
self.work_sheet["E39"].value = "rotate"
self.work_sheet["E40"].value = "creat | mode | ow…"
self.work_sheet["E41"].value = "/var/log/wtmp"
self.work_sheet["E42"].value = "/var/log/btmp"
self.work_sheet["E45"].value = "デバイスの指定"
self.work_sheet["E46"].value = "path"
self.work_sheet["E47"].value = "core_collector"
self.work_sheet["E48"].value = "dracut_args --omit-drivers"
self.work_sheet["E51"].value = "Code"
self.work_sheet["E54"].value = "MALITO"
self.work_sheet["E57"].value = "proxy"
self.work_sheet["E58"].value = "keepache"
self.work_sheet["E61"].value = "#Storage"
self.work_sheet["E64"].value = "fullpath"
self.work_sheet["E65"].value = "owner"
self.work_sheet["E66"].value = "group"
# T column default value
self.work_sheet["T24"].value = "LANG=en_US.UTF-8"
self.work_sheet["T27"].value = "UTC"
self.work_sheet["T30"].value = "[Yes]"
self.work_sheet["T33"].value = "/var/log/cron\n var/log/maillog"
self.work_sheet["T34"].value = "missingok"
# W
self.work_sheet["W10"].value = "String"
def gen_excel(self, file_path):
self.work_sheet.column_dimensions["A"].width = 1
self.file_path = file_path
self.set_title()
self.set_header()
self.set_content()
self.wb.save(file_path)
if __name__ == ‘__main__‘:
excelTemplate = excelTemplate()
excelTemplate.gen_excel("test.xlsx")
