openpyxl:将数据追加到第一个空列单元格 [英] openpyxl: Append data to first empty column cell

问题描述

背景:

我有一个excel工作簿，其中包含分散在各个工作表中的元数据.我需要从各个工作表中获取数据的相关列，并将它们组合成一个工作表.使用以下代码，我已经能够创建一个新的工作表并向其中添加数据.

I have an excel workbook containing metadata which spread across various worksheets. I need to take the relevant columns of data from the various worksheets and combine them into a single worksheet. With the following code I have been able to create a new worksheet and add data to it.

        # Open workbook and assign worksheet
        try:
            wb = openpyxl.load_workbook(metadata)
            shtEditionLNM = wb.worksheets[0]  # Edition date & latest NM
            shtChartsTitles = wb.worksheets[1]  # Charts & Titles
            shtDepthHeight = wb.worksheets[4]  # Depth & heights
            shtChartProj = wb.worksheets[7]  # Chart Projection
        except:
            raise SystemExit(0)

        new = wb.create_sheet()
        new.title = "MT_CHARTS INFO"
        new.sheet_properties.tabColor = "1072BA"
        shtMeta = wb.get_sheet_by_name("MT_CHARTS INFO")

        for row in shtChartsTitles.rows:
            shtMeta.append([row[0].value, row[1].value, row[2].value, row[4].value])
        for row in shtEditionLNM.rows:
            shtMeta.append([row[3].value, row[4].value])


        wb.save('OW - Quarterly Extract of Metadata for Raster Charts Dec 2015.xlsx')

这有效，没有任何错误，我可以看到保存到新工作簿中的数据.但是，当我运行第二个循环并附加值时，它们将附加到单元格A3169中，而我实际上希望它们从E1填充.

This works without any errors and I can see the data saved to my new workbook. However when I run a second loop and append values they are appended to cell A3169 whereas I actually want them to populate from E1.

我的问题归结为有没有办法我可以追加到新列而不是新行?"

My question boils down to 'is there a way I can append to a new column instead of a new row?'

提前谢谢！

推荐答案

不直接:ws.append()用于行，因为这是数据存储的方式，因此最容易针对只读和只写进行优化模式.

Not directly: ws.append() works with rows because this is the way the data is stored and thus the easiest to optimise for the read-only and write-only modes.

但是，ws.cell(row=x, column=y, value=z)将允许您执行想要的操作. 2.4版(从签出安装)还将允许您通过为您管理单元格的分配来直接使用列:ws['E']将返回列中单元格的元组，直到当前的ws.max_row； ws.iter_cols(min_col, min_row, max_col, max_row)将返回您需要的列生成器.

However, ws.cell(row=x, column=y, value=z) will allow you to do want you want. Version 2.4 (install from a checkout) will also let you work directly with columns by managing the assignment to cells for you: ws['E'] will return a tuple of the cells in the column up to the current ws.max_row; ws.iter_cols(min_col, min_row, max_col, max_row) will return a generator of columns as big as you need it.

这篇关于openpyxl:将数据追加到第一个空列单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！