Find the last filled row in Excel using Revit Python Shell

[taispma...@gmail.com]

Hi!

I am trying to read until the last filled row in a column in Excel in Revit Python Shell.

I've read a lot of posts but I can't find a solution that works for me.

There is a picture of the code I'm working with. Line 15 shows how the last row would be in VBA, but I need a solution that works in Revit Python Shell.

Lines 18 and 20 show a range, but I want to adapt them to go until the last filled row.

I'm new in Python and Revit Python Shell.

I tried to import xlrd to try to find out if it could somehow help, but revit Python Shell shows an error: 

 Exception : IronPython.Runtime.Exceptions.ImportException: No module named xlrd
   at Microsoft.Scripting.Runtime.LightExceptions.ThrowException(LightException lightEx)
   at Microsoft.Scripting.Runtime.LightExceptions.CheckAndThrow(Object value)
   at Microsoft.Scripting.Interpreter.FuncCallInstruction`2.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.Interpreter.Run(InterpretedFrame frame)
   at Microsoft.Scripting.Interpreter.LightLambda.Run2[T0,T1,TRet](T0 arg0, T1 arg1)
   at IronPython.Compiler.PythonScriptCode.RunWorker(CodeContext ctx)
   at Microsoft.Scripting.Hosting.ScriptSource.Execute(ScriptScope scope)
   at Microsoft.Scripting.Hosting.ScriptSource.ExecuteAndWrap(ScriptScope scope, ObjectHandle& exception)

I don't know how to to solve this. And I don't really know if xrld would help me anyway.

Sorry if this question is stupid, I could not understand how to use Excel in Revit Python Shell...

Any idea?

[Ed Green]

Hi taispmagalhaes,

You might be able to use the worksheet property UsedRange.Rows.Count and UsedRange.Columns.Count to import data when you don't know how many rows / columns there are

See example below (not tested!) which should take in a range from the given worksheet and return a 2D list.

Hope that helps? (reading from Excel in IronPython is not much fun, I agree!)

Ed

def import_excel_data(workbook, sheet="Sheet1"):

ws = workbook.Sheets.Item[sheet]

row_count = ws.UsedRange.Rows.Count

column_count = ws.UsedRange.Columns.Count

imported_data = []

i=1

while i <= row_count:

imported_row = []

j=1

while j <= column_count:

imported_row.append(ws.Cells(i,j).Text)

j += 1

imported_data.append(imported_row)

i += 1

return imported_data

--
You received this message because you are subscribed to the Google Groups "RevitPythonShell" group.
To unsubscribe from this group and stop receiving emails from it, send an email to revitpythonshe...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/revitpythonshell/4e1a6f99-8665-4214-a11a-00d71e5247aeo%40googlegroups.com.

[taispma...@gmail.com]

Hi, Ed! Thank you so much for answering!

I've tried this, with some modifications (because I need the content of only one column at a time).

But I got this error:

Do you know what this is about or if I did something that I wasn't supposed to do? I need to get the active excel document so I can use the code for lots of files, so i tried activesheet.

Thank you! 

  

[Ed Green]

No problem, happy to help!

In my example, ws is the worksheet - my variable names were not very clear! 

I'm getting the sheet object from the workbook using its name as a string (eg "Sheet1")

In your case you can modify the function use the worksheet object you pass into it as an argument, eg the active sheet:

def import_excel_data( worksheet ):

row_count = worksheet.UsedRange.Rows.Count

column_count = worksheet.UsedRange.Columns.Count

last_row = row_count

last_column = column_count

imported_data = []

i=1

while i <= last_row:

imported_row = []

j=1

while j <= last_column:

imported_row.append(worksheet.Cells(i,j).Text) # or use .Value2 instead of .Text

j += 1

imported_data.append(imported_row)

i += 1

return imported_data

Note also: I think you might need to get the active document like this:

workbook = excel.ActiveWorkbook

and then get the active sheet like this:

worksheet = workbook.ActiveSheet

Let me know if that works :-)

--

You received this message because you are subscribed to the Google Groups "RevitPythonShell" group.
To unsubscribe from this group and stop receiving emails from it, send an email to revitpythonshe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/revitpythonshell/ded06109-bad8-44fb-ad76-b10844007d90o%40googlegroups.com.

[taispma...@gmail.com]

Thank you! It works!

The only problem is that now it gets 5 more empty rows:

 

[taispma...@gmail.com]

After I closed and reopened the file, it worked without the empty cells!

Thank you very much again! 

[Ed Green]

You're welcome, glad it helped!

About your phantom 5 extra rows - I think 'UsedRange' in excel can include cells with any kind of formatting applied, that may appear empty..  

On Tue, 30 Jun 2020 at 18:01, <taispma...@gmail.com> wrote:

After I closed and reopened the file, it worked without the empty cells!

Thank you very much again! 

--

You received this message because you are subscribed to the Google Groups "RevitPythonShell" group.
To unsubscribe from this group and stop receiving emails from it, send an email to revitpythonshe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/revitpythonshell/1c8c0665-dccf-4676-91bd-ec3e7dd38afeo%40googlegroups.com.

[taispma...@gmail.com]

Yes, the problem must be that...

Thanks!

[taispma...@gmail.com]

The UsedRange only works if all the columns in the worksheet have the same length, right?

[Ed Green]

I think (from memory) the UsedRange is the rectangular range defined by the longest column and longest row.

So if your columns are different lengths, the shorter columns will have empty values at the ends.

On Wed, 1 Jul 2020, 13:25 , <taispma...@gmail.com> wrote:

The UsedRange only works if all the columns in the worksheet have the same length, right?

--

You received this message because you are subscribed to the Google Groups "RevitPythonShell" group.
To unsubscribe from this group and stop receiving emails from it, send an email to revitpythonshe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/revitpythonshell/70b492ca-fc8c-4829-8597-48cc672793afo%40googlegroups.com.

[taispma...@gmail.com]

Thank you!

So I'll have to look for an alternative to overcome this problem 😅