I have an Excel file and I use python Win32com to operate on it.
how do I check if the last row has value in a specific column ?
when I try , the nrows is 28, not 23 (the last row have value not empty)
used = sht1.UsedRange
nrows = used.Row + used.Rows.Count - 1
UsedRange is not reliable always when you want to get last row of any specific column. You should use EndXlUp or EndXlDown function.
Check this below line:
LastRow = Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row
Where, A is the column to get last row.
Also, check this URL: Error in finding last used cell in VBA
As your are using python win32 com, Endxlup will not work. There's one basic thing you can do. Check the below code:
ws = wb.Worksheets('Sheet1')
rw = 2
While ws.cells(rw, 1) <> ""
rw +=1
Where, rw is the starting row from where you want to start row count. 1 in (rw, 1) represents column. Column A represents A.
Logic behind this is while loop will run till it does not get blank cell in column A and you will get row count in variable rw
I'm currently writing an Excel wrapper, and part of it is this function returning all data from a certain offset to the last row of that column. For example, if I want everything from the 3rd row up till including the last line. This should work for your problem as well. Note where the constant is retrieved from and how all methods are stacked together to get the data and the last row.
Key functions:
def get_column_after(self, column, offset):
for item in self.ws.Range("{0}{1}:{0}{2}".format(column, offset, self.get_last_row_from_column(column))).Value:
print(item[0])
def get_last_row_from_column(self, column):
return self.ws.Range("{0}{1}".format(column, self.ws.Rows.Count)).End(win32com.client.constants.xlUp).Row
NOTE: This code is a work in progress and at the moment only supports one worksheet, one workbook per instance. I'm sure you can figure out a way to get this to work in your project though.
import string
import win32com.client
SOURCE_PATH = "C:\ExternData\somefile.xlsx"
WORKSHEET_NAME = "WS_1"
class ExcelInstance():
def __init__(self, wb=None):
self.source_path = SOURCE_PATH
try:
self.app = win32com.client.gencache.EnsureDispatch('Excel.Application')
except:
print("Application could not be opened.")
return
try:
self.open_workbook()
except:
print("Workbook could not be opened.")
return
try:
self.ws = self.wb.Worksheets(WORKSHEET_NAME)
except:
print("Worksheet not found.")
return
self.app.Visible = True
self.app.WindowState = win32com.client.constants.xlMaximized
def open_workbook(self):
"""
If it doesn't open one way, try another.
"""
try:
self.wb = self.app.Workbooks(self.source_path)
except Exception as e:
try:
self.wb = self.app.Workbooks.Open(self.source_path)
except Exception as e:
print(e)
self.wb = None
def get_column_after(self, column, offset):
for item in self.ws.Range("{0}{1}:{0}{2}".format(column, offset, self.get_last_row_from_column(column))).Value:
print(item[0])
def get_last_row_from_column(self, column):
return self.ws.Range("{0}{1}".format(column, self.ws.Rows.Count)).End(win32com.client.constants.xlUp).Row
def main():
f = ExcelInstance()
f.get_column_after("A", 3)
if __name__ == "__main__":
main()
xlUp is nothing short of a pre-defined constant.
So the most simple way is to type the code in the direct window of the VBE
? xlUp
you will see the following result.
-4162
add the line to your python code
xlUp = -4162
Done!
i use this stupid way to do . not sure have problem or not. so far work for myself
def find_lastrow_oncolumn(worksht, colno):
used = worksht.UsedRange
nrows = used.Row + used.Rows.Count - 1
lastrow_havevalue = 0
for k in range(nrows):
if worksht.Cells(nrows-k, colno).Value is not None:
print worksht.Cells(nrows-k, colno).Value, nrows-k , k
lastrow_havevalue = nrows-k
break
return lastrow_havevalue