python - 将Excel工作簿中的数据复制到另一个工作簿，需要选择特定的行和列

Question

我已经能够打开工作簿并保存它，但我似乎无法复制和粘贴特定的行和列。我希望能够将其用于多个工作表，并随着行的增长将数据附加到数据中。

最终产品我想选择多个 Excel 文件并复制特定的行和列，然后将每个文件附加到一个 Excel 工作簿。因为我现在必须浏览 20 个工作簿并将其全部复制并粘贴到一个工作簿中。

我尝试了几种不同的方法并在论坛上进行了搜索。我只能复制和粘贴表格。

import openpyxl

#Prepare the spreadsheets to copy from and paste too.

#File to load
wb = openpyxl.load_workbook("Test_Book.xlsx")
# Get a sheet by name 
sheet = wb['Sheet1']

#File to be pasted into
template = openpyxl.load_workbook("Copy of Test_Book.xlsx") #Add file 
name
temp_sheet = template['Sheet1'] #Add Sheet name

#Copy range of cells as a nested list
#Takes: start cell, end cell, and sheet you want to copy from.
def copyRange(startCol, startRow, endCol, endRow, sheet):
    rangeSelected = []
    #Loops through selected Rows
    #A 8 to BC 27
    for i in range(startRow,endRow + 1,1):
        #Appends the row to a RowSelected list
        rowSelected = []
        for j in range(startCol,endCol+ 1,1):
            rowSelected.append(sheet.cell(row = i, column = j).value)
        #Adds the RowSelected List and nests inside the rangeSelected
        rangeSelected.append(rowSelected)

    return rangeSelected
#Paste range
#Paste data from copyRange into template sheet
def pasteRange(startCol, startRow, endCol, endRow, 
sheetReceiving,copiedData):
    countRow = 0
    for i in range(startRow,endRow+1,1):
        countCol = 0
        for j in range(startCol,endCol+1,1):

            sheetReceiving.cell(row = i, column = j).value = 
copiedData[countRow][countCol]
            countCol += 1
        countRow += 1

def createData():
    print("Processing...")
    selectedRange = copyRange(1,2,4,14,sheet)
    pasteRange(1,2,4,14,temp_sheet,selectedRange)
    template.save("Copy of Test_Book.xlsx")
    print("Range copied and pasted!")

Answer 1

您可以指定要在工作表对象中循环的行或列。

import openpyxl

wb = openpyxl.load_workbook("your_excel_file")
ws = wb.active

some_column = [cell.value for cell in ws["A"]] # Change A to whichever column you want
some_row = [cell.value for cell in ws["1"]] # Change 1 to whichever row you want

然后，您可以将整个列/行附加到新工作表中。