# -*- coding: utf-8 -*-
import openpyxl,psycopg2
from openpyxl.styles import Font, PatternFill, Border, Side
class xls:
def get_data1(self):
conn = psycopg2.connect(host="192.168.137.129", port="5432", database="postgres", user="postgres",
password="postgresql@123", )
conn.autocommit = True
cur = conn.cursor()
cur.execute("SELECT category,name,setting FROM pg_settings order by 1,2")
lsts = cur.fetchall()
cur.close()
conn.close()
return lsts
def get_data2(self):
conn = psycopg2.connect(host="192.168.137.129", port="1921", database="postgres", user="pg10",
password="postgresql@123", )
conn.autocommit = True
cur = conn.cursor()
cur.execute("SELECT category,name,setting FROM pg_settings order by 1,2")
lsts = cur.fetchall()
cur.close()
conn.close()
return lsts
def execute_xls(self):
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = '差集1'
wb.create_sheet(title='差集2')
wb.create_sheet(title='交集')
bold_24_Font = Font(size=12, bold=True)
bold_red_Font = Font(bold=True, color="FF0000")
fill = PatternFill(
patternType = "solid",
fgColor = "8CC7B5"
)
side = Side(
style = "thin",
color = "000000"
)
border = Border(
top = side,
bottom = side,
left = side,
right= side,
)
lsts1 = self.get_data1()
lsts2 = self.get_data2()
name_list1 = [i[1] for i in lsts1]
name_list2 = [i[1] for i in lsts2]
set1 = set(name_list1).difference(name_list2)
lsts = [i for i in lsts1 if i[1] in set1]
# 差集1
sheet = wb['差集1']
sheet['A1'] = '分类'
sheet['B1'] = '参数'
sheet['C1'] = '值'
sheet['A1'].font = bold_24_Font
sheet['B1'].font = bold_24_Font
sheet['C1'].font = bold_24_Font
sheet.column_dimensions['A'].width = 80
sheet.column_dimensions['B'].width = 40
sheet.column_dimensions['C'].width = 20
sheet['A1'].fill = fill
sheet['B1'].fill = fill
sheet['C1'].fill = fill
sheet['A1'].border = border
sheet['B1'].border = border
sheet['C1'].border = border
sheet.freeze_panes = 'A2'
for i, key in enumerate(lsts):
sheet.cell(row=i + 2, column=1).value = lsts[i][0]
sheet.cell(row=i + 2, column=2).value = lsts[i][1]
sheet.cell(row=i + 2, column=3).value = lsts[i][2]
sheet.cell(row=i + 2, column=1).border = border
sheet.cell(row=i + 2, column=2).border = border
sheet.cell(row=i + 2, column=3).border = border
# 差集2
sheet = wb['差集2']
sheet['A1'] = '分类'
sheet['B1'] = '参数'
sheet['C1'] = '值'
sheet['A1'].font = bold_24_Font
sheet['B1'].font = bold_24_Font
sheet['C1'].font = bold_24_Font
sheet.column_dimensions['A'].width = 80
sheet.column_dimensions['B'].width = 40
sheet.column_dimensions['C'].width = 20
sheet['A1'].fill = fill
sheet['B1'].fill = fill
sheet['C1'].fill = fill
sheet['A1'].border = border
sheet['B1'].border = border
sheet['C1'].border = border
sheet.freeze_panes = 'A2'
set2 = set(name_list2).difference(name_list1)
lsts = [i for i in lsts2 if i[1] in set2]
for i, key in enumerate(lsts):
sheet.cell(row=i + 2, column=1).value = lsts[i][0]
sheet.cell(row=i + 2, column=2).value = lsts[i][1]
sheet.cell(row=i + 2, column=3).value = lsts[i][2]
sheet.cell(row=i + 2, column=1).border = border
sheet.cell(row=i + 2, column=2).border = border
sheet.cell(row=i + 2, column=3).border = border
# 交集
set3 = set(name_list2).intersection(name_list1)
lsts1 = [i for i in lsts1 if i[1] in set3]
lsts2 = [i for i in lsts2 if i[1] in set3]
sheet = wb['交集']
sheet['A1'] = '分类'
sheet['B1'] = '参数1'
sheet['C1'] = '值1'
sheet['D1'] = '参数2'
sheet['E1'] = '值2'
sheet['A1'].font = bold_24_Font
sheet['B1'].font = bold_24_Font
sheet['C1'].font = bold_24_Font
sheet['D1'].font = bold_24_Font
sheet['E1'].font = bold_24_Font
sheet.column_dimensions['A'].width = 80
sheet.column_dimensions['B'].width = 40
sheet.column_dimensions['C'].width = 20
sheet.column_dimensions['D'].width = 40
sheet.column_dimensions['E'].width = 20
sheet['A1'].fill = fill
sheet['B1'].fill = fill
sheet['C1'].fill = fill
sheet['D1'].fill = fill
sheet['E1'].fill = fill
sheet['A1'].border = border
sheet['B1'].border = border
sheet['C1'].border = border
sheet['D1'].border = border
sheet['E1'].border = border
sheet.freeze_panes = 'A2'
for i, key in enumerate(lsts):
sheet.cell(row=i + 2, column=1).value = lsts1[i][0]
sheet.cell(row=i + 2, column=2).value = lsts1[i][1]
sheet.cell(row=i + 2, column=3).value = lsts1[i][2]
sheet.cell(row=i + 2, column=4).value = lsts2[i][1]
sheet.cell(row=i + 2, column=5).value = lsts2[i][2]
sheet.cell(row=i + 2, column=1).border = border
sheet.cell(row=i + 2, column=2).border = border
sheet.cell(row=i + 2, column=3).border = border
sheet.cell(row=i + 2, column=4).border = border
sheet.cell(row=i + 2, column=5).border = border
if lsts1[i][2] != lsts2[i][2]:
sheet.cell(row=i + 2, column=1).font = bold_red_Font
sheet.cell(row=i + 2, column=2).font = bold_red_Font
sheet.cell(row=i + 2, column=3).font = bold_red_Font
sheet.cell(row=i + 2, column=4).font = bold_red_Font
sheet.cell(row=i + 2, column=5).font = bold_red_Font
wb.save('copy.xlsx')
if __name__ == '__main__':
xls().execute_xls()