excel - 使用 openpyxl 从多个工作表中创建一个主重复的名称列表

Question

我在 Python 中没有任何想象力，据说我有一个脚本，它将获取一个特定的选项卡并将其从 4 个单独的电子表格中复制过来，并将它们全部放入一个工作簿中。每个工作表都有一个包含计算机名称的列，我想要完成的是从所有工作表中创建一个唯一的名称列表并将它们放入 Sheet1 的 A 列。下面是我的完整代码。请帮助我使用 openpyxl，我知道它是以科学怪人的方式放在一起的，我可能会从它身上剪掉一些脂肪，所以请温柔一点。

import openpyxl
from openpyxl.styles import NamedStyle
from openpyxl.workbook import Workbook
from openpyxl.utils import get_column_letter
from openpyxl import load_workbook

#Creating new workbook 
wb = Workbook()
ws1 = wb.create_sheet('Sheet_A')
ws1.title = 'Response'

ws2 = wb.create_sheet('Sheet_B')
ws2.title = 'Defense'

ws3 = wb.create_sheet('Sheet_C')
ws3.title = 'Protection'

ws4 = wb.create_sheet('Sheet_D')
ws4.title = 'AD Computer List'

wb.save('test.xlsx')
#End Creating new workbook

# importing openpyxl module
import openpyxl as xl;
  
# opening the source excel file
filename ="Protection.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]
  
# opening the destination excel file 
filename1 ="test.xlsx"
wb2 = xl.load_workbook(filename1)
ws2 = wb2.worksheets[3]
  
# calculate total number of rows and 
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column
  
# copying the cell values from source 
# excel file to destination excel file
for i in range (1, mr + 1):
    for j in range (1, mc + 1):
        # reading cell value from source excel file
        c = ws1.cell(row = i, column = j)
  
        # writing the read value to destination excel file
        ws2.cell(row = i, column = j).value = c.value
  
# saving the destination excel file
wb2.save(str(filename1))

# opening the source excel file
filename ="AD.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]
  
# opening the destination excel file 
filename1 ="test.xlsx"
wb2 = xl.load_workbook(filename1)
ws2 = wb2.worksheets[4]
  
# calculate total number of rows and 
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column
  
# copying the cell values from source 
# excel file to destination excel file
for i in range (1, mr + 1):
    for j in range (1, mc + 1):
        # reading cell value from source excel file
        c = ws1.cell(row = i, column = j)
  
        # writing the read value to destination excel file
        ws2.cell(row = i, column = j).value = c.value
  
# saving the destination excel file
wb2.save(str(filename1))

# opening the source excel file
filename ="Defense.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]
  
# opening the destination excel file 
filename1 ="test.xlsx"
wb2 = xl.load_workbook(filename1)
ws2 = wb2.worksheets[2]
  
# calculate total number of rows and 
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column
  
# copying the cell values from source 
# excel file to destination excel file
for i in range (1, mr + 1):
    for j in range (1, mc + 1):
        # reading cell value from source excel file
        c = ws1.cell(row = i, column = j)
  
        # writing the read value to destination excel file
        ws2.cell(row = i, column = j).value = c.value
  
# saving the destination excel file
wb2.save(str(filename1))

# opening the source excel file
filename ="Response.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]
  
# opening the destination excel file 
filename1 ="test.xlsx"
wb2 = xl.load_workbook(filename1)
ws2 = wb2.worksheets[1]
  
# calculate total number of rows and 
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column
  
# copying the cell values from source 
# excel file to destination excel file
for i in range (1, mr + 1):
    for j in range (1, mc + 1):
        # reading cell value from source excel file
        c = ws1.cell(row = i, column = j)
  
        # writing the read value to destination excel file
        ws2.cell(row = i, column = j).value = c.value
  
# saving the destination excel file
wb2.save(str(filename1))

book = openpyxl.load_workbook('test.xlsx')
sheet = book['AD Computer List']
sheet.delete_rows(1)
book.save('test.xlsx')

标签： excelopenpyxl