复制工作簿中的列，粘贴到第二个工作簿的第二张表openPyXL中 [英] Copy columns from workbook, paste in second sheet of second workbook, openPyXL

问题描述

我是openpyxl的新手，正在开发一种需要复制&的工具.粘贴列.

I'm new to openpyxl and am developing a tool that requires copying & pasting columns.

我有一个包含两组excel文件的文件夹.我需要脚本来遍历文件，找到名为"GenLU_xx"的文件(xx代表诸如卡尔加里的地方的名称)，然后复制列C&E(3和5).然后，它需要找到名为"LU_Summary_xx"的对应文件(xx再次代表地点名称，例如Calgary)，然后将复制的列粘贴到该工作簿的第二张纸上.它需要将GenLU_Calgary与LUZ_Summary_Calgary匹配，以此类推，以用于所有文件.到目前为止，我还无法弄清楚用于复制和粘贴列的代码，而且看似双重的迭代使我感到困惑.我的python技能是初学者，尽管我通常可以通过查看示例来弄清楚代码.在这种情况下，我在查找示例代码时遇到了一些麻烦.刚开始使用openpyxl.除了与excel有关的部分，我已经完成了脚本.希望有人可以帮忙.任何帮助将非常感激！

I have a folder containing two sets of excel files. I need the script to iterate through the files, find the ones that are named "GenLU_xx" (xx represents name of a place such as Calgary) and copy Columns C & E (3 & 5). It then needs to find the corresponding file which is named as "LU_Summary_xx" (xx again represents name of place such as Calgary) and paste the copied columns to the second sheet of that workbook. It needs to match GenLU_Calgary with LUZ_Summary_Calgary and so forth for all the files. So far I have not been able to figure out code for copying and pasting columns and the seemingly double iteration is confusing me. My python skills are beginner although I'm usually able to figure out code by looking at examples. In this case I'm having some trouble locating sample code. Just started using openpyxl. I have completed the script except for the parts pertaining to excel. Hopefully someone can help out. Any help would be much appreciated!

也是StackOverflow的新手，所以不确定为什么我得到-2.也许是由于缺少任何代码?

New to StackOverflow as well so not sure why I got -2. Maybe due to lack of any code?

这是我到目前为止所拥有的:

Here is what I have so far:

    import os, openpyxl, glob
from openpyxl import Workbook
Tables = r"path"
os.chdir(Tables)
for file in glob.glob ("LUZ*"):
    wb = openpyxl.load_workbook(file)
    ws = wb.active
    ws ["G1"] = "GEN_LU_ZN"
    wb.create_sheet(title="Sheet2")
    wb.save(file)

这只是将每个文件的G1值添加到LUZ开头，并创建第二张表.

This just adds a value to G1 of every file starting with LUZ and creates a second sheet.

正如我之前提到的，我还没有弄清楚用于复制整个列的值的代码.

As I mentioned previously, I have yet to even figure out the code for copying the values of an entire column.

我想我可以使用glob遍历所有以"GenLU *"开头的文件，然后存储第3列和第3列的值.5，但是我仍然很难弄清楚如何访问列的值.我没有行的范围，因为每个工作簿的两列都有不同数量的行.

I am thinking I could iterate through all files starting with "GenLU*" using glob and then store the values of Columns 3 & 5 but I'm still having trouble figuring out how to access values for columns. I do not have a range of rows as each workbook will have a different number of rows for the two columns.

我可以使用以下代码访问特定列的单元格值:

EDIT 2: I am able to access cell values for a particular column using this code:

for file in glob.glob ("GenLU_Airdrie*"):
    wb = openpyxl.load_workbook(file, use_iterators=True)
    ws = wb.active
    for row in ws.iter_rows ('C1:C200'):
        for cell in row:
            values = cell.value
            print values

但是我不确定如何将这些值粘贴"到另一张纸的A列中.

However I'm not sure how I would go about 'pasting' these values in column A of the other sheet.

推荐答案

如果您确实想使用列，则可以在读取文件时使用 .columns 属性.

If you really do want to work with columns then you can use the .columns property when reading files.

要将值从一张纸复制到另一张纸，只需分配它们即可.下面将把A1的值从一个工作表复制到另一个工作表.

To copy the values from one sheet to another you just assign them. The following will copy the value of A1 from one worksheet to another.

ws1 = wb1.active
ws2 = wb2.active
ws2['A1'] = ws1['A1'].value

复制D列代码可能看起来像这样

To copy column D code could look something like this

col_d = ws1.columns[3] # 0-indexing
for idx, cell in enumerate(col_d, 1):
    ws.cell(row=idx, col=4).value = cell.value #1-indexing

这篇关于复制工作簿中的列，粘贴到第二个工作簿的第二张表openPyXL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！