CODEWITHSUNDEEP
pythonpython-2.7xlwings
user2242044
user2242044

Reputation: 9213

xlwings function to find the last row with data

I am trying to find the last row in a column with data. to replace the vba function: LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

I am trying this, but this pulls in all rows in Excel. How can I just get the last row.

from xlwings import Workbook, Range
wb = Workbook()
print len(Range('A:A'))

Upvotes: 18

Views: 39862

Answers (12)

Devan Conley
Devan Conley

Reputation: 11

You can make use of the usual range expansion to read in a range of dynamic size:

with xw.Book("myfile.xlsx", mode="r") as book:
sheet1 = book.sheets[0]
data = sheet1["A1"].expand().value

Upvotes: 1

Mattos
Mattos

Reputation: 1

columnr = 1 # any value just to define the variable
countrow = 7 # Number of rows before the first data row.
selectcolumn = 18 # Column used to find the first empty data cell


while columnr is not None :
    countrow = countrow + 1
    columnr = wsreportsheet.cells(countrow,selectcolumn).value

print (countrow)

Upvotes: 0

zongxin
zongxin

Reputation: 21

@Cody's answer will help under normal circumstances, but if your sheet have hidden rows at bottom like links: example, it will give the wrong row number.

Lets say, if your row counts of data is 10, and row[5:11] are hidden, i.e. actually last_row will be 10.

[code a] below will give you answer 5, [code b] below will give you answer 10.

code a:

ws = wb.sheets[your_sheet_name]
last_row = ws.range('A' + str(ws.cells.last_cell.row)).end('up').row  # return 5

code b:

ws = wb.sheets[your_sheet_name]
last_row_1 = ws.used_range.last_cell.row  # return 10

Upvotes: 0

stansy
stansy

Reputation: 308

Interesting solutions. But maybe like this:

print(sheet.used_range.last_cell.row)

Upvotes: 2

Sgdva
Sgdva

Reputation: 2800

Update:
After a while none of the solutions were really intuitive to me, so I decided to compile the following: Code:

import xlwings as Objxlwings
import xlwings.constants
def Return_RangeLastCell(ObjWS):
    return ObjWS.api.Cells.SpecialCells(xlwings.constants.CellType.xlCellTypeLastCell)

I tried to keep consistency with the way to call it from Excel to keep it simple
Then on my main code, I just call it like so:

ObjWS=Objxlwings.Book('Book1.xlsx').sheets["Sheet1"]
print(Return_RangeLastCell(ObjWS).Column)

Upvotes: 0

Steven
Steven

Reputation: 41

python 3.6, xlwings 0.11

Solutoin 1

To find last row with data, you should do some work both horizontally and vertically. You have to go through every column to determine which row is the last row.

import xlwings
workbook_all = xlwings.Book(r'path.xlsx')
objectiveSheet = workbook_all .sheets['some_sheet']
# lastCellContainData(), inspired of Stefan's answer.
def lastCellContainData(objectiveSheet,lastRow=None,lastColumn=None):
    lastRow = objectiveSheet.cells.last_cell.row if lastRow==None else lastRow
    lastColumn = objectiveSheet.cells.last_cell.column if lastColumn==None else lastColumn
    lastRows,lastColumns = [],[]
    for col in range(1,lastColumn):
        lastRows.append(objectiveSheet.range((lastRow, col)).end('up').row)
        # extract last row of every column, then max(). Or you can compare the next 
        # column's last row number to the last column's last row number. Here you get
        # the last row with data, you can also go further get the last column with data:
    for row in range(1,lastRow):
        lastColumns.append(objectiveSheet.range((row, lastColumn)).end('left').column)
    return max(lastRows),max(lastColumns)
    
lastCellContainData(objectiveSheet,lastRow=5000,lastColumn=300)

I added lastRow and lastColumn. To make the program more effective, you can set these parameters according to the approximate shape of the data you're dealing with.

Solution 2

xlwings is honored for being wrapper of pywin32. I don't know if your situation allows for keyboard or mouse. If so, first you ctrl+tab switch to the workbook, then ctrl+a to select the region containing data, then you call workbook_all.selection.rows.count.

another way: When you know where right bottom cell of your data locates faintly, say AAA10000, just call objectiveSheet.range('A1:'+'AAA10000').current_region.rows.count

Upvotes: 1

Sung Eun Jo
Sung Eun Jo

Reputation: 111

We can use Range object to find the last row and/or the last column:

import xlwings as xw

# open raw data file
filename_read = 'data_raw.csv'
wb = xw.Book(filename_read)
sht = wb.sheets[0]

# find the numbers of columns and rows in the sheet
num_col = sht.range('A1').end('right').column
num_row = sht.range('A1').end('down').row

# collect data
content_list = sht.range((1,1),(num_row,num_col)).value
print(content_list)

Upvotes: 10

crazymachu
crazymachu

Reputation: 21

You could try using Direction by starting at the very bottom and then moving up:

import xlwings
from xlwings.constants import Direction
wb = xlwings.Workbook(r'data.xlsx')
print(wb.active_sheet.xl_sheet.Cells(65536, 1).End(Direction.xlUp).Row)

Upvotes: 2

Elijas Dapšauskas
Elijas Dapšauskas

Reputation: 1237

One could use the VBA Find function that is exposed through api property (use it to find anything with a star, and begin your search from the first cell).

Example:

row_cell = s.api.Cells.Find(What="*",
                   After=s.api.Cells(1, 1),
                   LookAt=xlwings.constants.LookAt.xlPart,
                   LookIn=xlwings.constants.FindLookIn.xlFormulas,
                   SearchOrder=xlwings.constants.SearchOrder.xlByRows,
                SearchDirection=xlwings.constants.SearchDirection.xlPrevious,
                       MatchCase=False)

column_cell = s.api.Cells.Find(What="*",
                      After=s.api.Cells(1, 1),
                      LookAt=xlwings.constants.LookAt.xlPart,
                      LookIn=xlwings.constants.FindLookIn.xlFormulas,
                      SearchOrder=xlwings.constants.SearchOrder.xlByColumns,
                      SearchDirection=xlwings.constants.SearchDirection.xlPrevious,
                      MatchCase=False)

print((row_cell.Row, column_cell.Column))

Other methods outlined here seems to require no empty rows/columns between data.

source: https://gist.github.com/Elijas/2430813d3ad71aebcc0c83dd1f130e33

Upvotes: 2

Cody
Cody

Reputation: 194

Consolidating the answers above, you can do it in one line:

wb.sheet.range(column + last cell value).Get End of section going up[non blank assuming the last cell is blank].row

Example code:

import xlwings as xw
from xlwings import Range, constants

wb = xw.Book(r'path.xlsx')
wb.sheets[0].range('A' + str(wb.sheets[0].cells.last_cell.row)).end('up').row

Upvotes: 17

Stefan
Stefan

Reputation: 313

This is very much the same as crazymachu's answer, just wrapped up in a function. Since version 0.9.0 of xlwings you can do this:

import xlwings as xw

def lastRow(idx, workbook, col=1):
    """ Find the last row in the worksheet that contains data.

    idx: Specifies the worksheet to select. Starts counting from zero.

    workbook: Specifies the workbook

    col: The column in which to look for the last cell containing data.
    """

    ws = workbook.sheets[idx]

    lwr_r_cell = ws.cells.last_cell      # lower right cell
    lwr_row = lwr_r_cell.row             # row of the lower right cell
    lwr_cell = ws.range((lwr_row, col))  # change to your specified column

    if lwr_cell.value is None:
        lwr_cell = lwr_cell.end('up')    # go up untill you hit a non-empty cell

    return lwr_cell.row

Intuitively, the function starts off by finding the most extreme lower-right cell in the workbook. It then moves across to your selected column and then up until it hits the first non-empty cell.

Upvotes: 4

user1579506
user1579506

Reputation: 9

Try this:

import xlwings as xw

cellsDown = xw.Range('A1').vertical.value

cellsRight = xw.Range('A1').horizontal.value

print len(cellsDown)

print len(cellsRight)

Upvotes: 0

Related Questions