OpenPyXL:需要在Excel中具有数据的列中的最大行数 [英] Openpyxl : need the max number of rows in a column that has data in Excel

问题描述

我需要包含Excel中数据的特定列中的最后一行.在openpyxl sheet.max_row或max_column中，我们获得整个工作表中的最大行数或列数.但是我想要的是特定的列.

I need the last row in a particular column that contains data in Excel. In openpyxl sheet.max_row or max_column gets us the maximum row or column in the whole sheet. But what I want is for a particular column.

我的情况是必须从数据库中获取一些值，并将其附加到Excel工作表中特定列的末尾.

My scenario is where I have to get some values from database and append it to the end of a particular column in Excel sheet.

在此屏幕截图中，如果我希望max_column在"C"列中包含数据，则应返回10:

In this screenshot, if I want max_column containing data in column 'C', it should return 10:

在上图中，如果我想要最后一个包含列"C"的数据的单元格，则应返回10

In the above image if I want last cell containing data of column 'C', it should return 10

-------------解决方案1 ​​--------------------

------------- Solution 1 --------------------

import pandas as pd

# lt is the dataframe containing the data to be loaded to excel file

for index,i in enumerate(lt):
   panda_xl_rd = pd.read_excel('file.xlsx',"sheet_Name") # Panda Dataframe
   max = len(panda_xl_rd.iloc[:,(col-1)].dropna())+2     ''' getting the 
                                                             row_num of 
                                                             last record in 
                                                             column 
                                                             dropna removes 
                                                             the Nan 
                                                             values else we 
                                                             will get 
                                                             the entire 
                                                             sheets max 
                                                             column length . 
                                                             +2 gets 
                                                             the next column 
                                                             right after the 
                                                             last column to 
                                                             enter data '''
   cellref = sheet.cell(row = max+index, column=col)
   cellref.value = i
   del panda_xl_rd

------------------------解决方案2 --------------------- -

------------------------Solution 2 ----------------------

https://stackoverflow.com/a/52816289/10003981

------------------------解决方案3 --------------------- -

------------------------Solution 3 ----------------------

https://stackoverflow.com/a/52817637/10003981

也许解决方案3是更简洁的一种！

Maybe solution 3 is a more concise one !!

推荐答案

问题:我希望max_column列"C"中包含数据，它应返回10:

Question: i want max_column containing data in Column 'C' it should return 10:

简单计数cell.value not Empty
文档访问许多单元格

Simple count cell.value not Empty
Documentation Accessing many cells

PSEUDOCODE

for cell in Column('C'):
    if not cell.value is empty:
        count += 1

---

评论:如果我们之间有一个空白单元格该怎么办?

Comment: What if we have an empty cell in between?

计算与列范围"同步的行，并使用maxRowWithData变量.这也适用于没有之间的空白单元格.

Count the Rows in sync with the Column Range, and use a maxRowWithData variable. This will also work with no empty cell between.

PSEUDOCODE

for row index, cell in enumerate Column('C'):
    if not cell.value is empty:
        maxRowWithData = row index

注意:openpyxl的单元格索引是基于 1的！

Note: The cell index of openpyxl is 1-based!

文档:枚举(可迭代，开始= 0)

这篇关于OpenPyXL:需要在Excel中具有数据的列中的最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！