129 lines
5.5 KiB
Python
129 lines
5.5 KiB
Python
from openpyxl import Workbook
|
||
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
||
from openpyxl.utils import get_column_letter
|
||
|
||
wb = Workbook()
|
||
ws = wb.active
|
||
ws.title = "Импорт объектов"
|
||
|
||
DARK_BLUE = "1F3864"
|
||
LIGHT_GRAY = "F2F2F2"
|
||
LIGHT_BLUE = "EBF3FB"
|
||
WHITE = "FFFFFF"
|
||
|
||
columns = [
|
||
("name *", 30),
|
||
("city *", 20),
|
||
("server_ip *", 18),
|
||
("client", 25),
|
||
("description", 35),
|
||
("notes", 35),
|
||
("ssh_user (root)", 15),
|
||
("ssh_password (123123)", 18),
|
||
("db_via_tunnel (0)", 20),
|
||
]
|
||
|
||
sample_row = [
|
||
"Парковка ТЦ Мега",
|
||
"Москва",
|
||
"192.168.1.10",
|
||
"ООО Рога и Копыта",
|
||
"Въезд с ул. Ленина",
|
||
"Контакт: +7 999 000-00-00",
|
||
"admin",
|
||
"qwerty123",
|
||
0,
|
||
]
|
||
|
||
header_font = Font(name="Arial", bold=True, color=WHITE, size=11)
|
||
header_fill = PatternFill("solid", start_color=DARK_BLUE, fgColor=DARK_BLUE)
|
||
header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
||
|
||
sample_fill = PatternFill("solid", start_color=LIGHT_GRAY, fgColor=LIGHT_GRAY)
|
||
blue_fill = PatternFill("solid", start_color=LIGHT_BLUE, fgColor=LIGHT_BLUE)
|
||
white_fill = PatternFill("solid", start_color=WHITE, fgColor=WHITE)
|
||
data_font = Font(name="Arial", size=10)
|
||
data_align = Alignment(vertical="center")
|
||
|
||
# Header row
|
||
for col_idx, (header, width) in enumerate(columns, start=1):
|
||
cell = ws.cell(row=1, column=col_idx, value=header)
|
||
cell.font = header_font
|
||
cell.fill = header_fill
|
||
cell.alignment = header_align
|
||
ws.column_dimensions[get_column_letter(col_idx)].width = width
|
||
|
||
ws.row_dimensions[1].height = 30
|
||
|
||
# Sample data row (row 2)
|
||
for col_idx, value in enumerate(sample_row, start=1):
|
||
cell = ws.cell(row=2, column=col_idx, value=value)
|
||
cell.font = data_font
|
||
cell.fill = sample_fill
|
||
cell.alignment = data_align
|
||
|
||
# Empty data rows 3-52, alternating white / light blue
|
||
for row in range(3, 53):
|
||
fill = white_fill if (row % 2 == 1) else blue_fill
|
||
for col_idx in range(1, len(columns) + 1):
|
||
cell = ws.cell(row=row, column=col_idx)
|
||
cell.font = data_font
|
||
cell.fill = fill
|
||
cell.alignment = data_align
|
||
|
||
ws.freeze_panes = "A2"
|
||
|
||
# --- Справка sheet ---
|
||
ws2 = wb.create_sheet("Справка")
|
||
|
||
ws2.column_dimensions["A"].width = 22
|
||
ws2.column_dimensions["B"].width = 40
|
||
ws2.column_dimensions["C"].width = 20
|
||
ws2.column_dimensions["D"].width = 20
|
||
|
||
title_cell = ws2.cell(row=1, column=1, value="Инструкция по заполнению")
|
||
title_cell.font = Font(name="Arial", bold=True, size=13)
|
||
ws2.row_dimensions[1].height = 24
|
||
|
||
# Sub-header
|
||
headers_ref = ["Колонка", "Описание", "Обязательность", "Значение по умолчанию"]
|
||
for col_idx, h in enumerate(headers_ref, start=1):
|
||
cell = ws2.cell(row=3, column=col_idx, value=h)
|
||
cell.font = Font(name="Arial", bold=True, color=WHITE, size=11)
|
||
cell.fill = PatternFill("solid", start_color=DARK_BLUE, fgColor=DARK_BLUE)
|
||
cell.alignment = Alignment(horizontal="center", vertical="center")
|
||
ws2.row_dimensions[3].height = 22
|
||
|
||
ref_rows = [
|
||
("name", "Название объекта (парковки, площадки)", "Обязательно", "—"),
|
||
("city", "Город расположения объекта", "Обязательно", "—"),
|
||
("server_ip", "IP-адрес основного сервера CAPS", "Обязательно", "—"),
|
||
("client", "Клиент / заказчик", "Необязательно","—"),
|
||
("description", "Описание объекта", "Необязательно","—"),
|
||
("notes", "Заметки (контакты, особенности и т.п.)", "Необязательно","—"),
|
||
("ssh_user", "Логин для SSH-подключения к серверу объекта", "Необязательно","root"),
|
||
("ssh_password", "Пароль для SSH-подключения к серверу объекта", "Необязательно","123123"),
|
||
("db_via_tunnel", "Подключаться к БД CAPS через SSH-туннель (0 — нет, 1 — да)","Необязательно","0"),
|
||
]
|
||
|
||
for i, (col_name, desc, required, default) in enumerate(ref_rows, start=4):
|
||
fill = white_fill if (i % 2 == 0) else blue_fill
|
||
values = [col_name, desc, required, default]
|
||
for col_idx, val in enumerate(values, start=1):
|
||
cell = ws2.cell(row=i, column=col_idx, value=val)
|
||
cell.font = Font(name="Arial", size=10)
|
||
cell.fill = fill
|
||
cell.alignment = Alignment(vertical="center", wrap_text=True)
|
||
ws2.row_dimensions[i].height = 18
|
||
|
||
note_row = len(ref_rows) + 5
|
||
note_cell = ws2.cell(row=note_row, column=1,
|
||
value="⚠ Дедупликация по полю server_ip: если объект с таким IP уже существует в системе, строка будет пропущена с предупреждением.")
|
||
note_cell.font = Font(name="Arial", size=10, bold=True, color="C00000")
|
||
note_cell.alignment = Alignment(wrap_text=True)
|
||
ws2.merge_cells(start_row=note_row, start_column=1, end_row=note_row, end_column=4)
|
||
ws2.row_dimensions[note_row].height = 32
|
||
|
||
out_path = r"C:\Users\Professional\Documents\VSCode\utp_service\objects_import_template.xlsx"
|
||
wb.save(out_path)
|
||
print(f"Saved: {out_path}")
|