xls生成对比

# -*- 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)
        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
        wb.save('copy.xlsx')

if __name__ == '__main__':
    xls().execute_xls()