win32com: how to tell if an excel cell is empty

[Alex Martelli]

"Harold Howe" <hhowe at gowebway.com> wrote in message
news:39FE48C9.B4F00766 at gowebway.com...
> I am writing a python script to parse an excel file and move the data
> into a database. In one section of the script, I want to test a
> particular cell to see if it is empty. How do I do this?

By testing the cell's Value for equality with None.


> I have tried these combinations, but none work. I thought the comparison
> to None was the way to go <shrug>:

The cell object itself (a Range, actually, in the Excel object model)
cannot be None, of course -- else, you could not operate on it, right?
It *Value*, though, can be (and is, if it's empty) None.


> import win32com.client
> excel = win32com.client.Dispatch("Excel.Application")
> wkbook = excel.Workbooks.Open('foo.xls')
> sheet = wkbook.Sheets("Sheet1")
> cell = sheet.Cells(1,1)
>
> if(cell == None):
>   print "empty"

if cell.Value is None:
    print "empty"

is, I believe, the most-Pythonical idiom here.  You can use ==
instead (matter of individual style) and even the redundant
parentheses, if you wish.

> How do I test the return from Cells for an empty value? Is there a
> constant that I compare it with (win32com.Empty) or some such thing? I
> saw a reference to a VarType function in a VB group, but I don't think
> that VarType is applicable in python.

You could use the type function, but that would only tell you
it's 'an instance' -- and definitely not how its current Value
is typed.

The confusion between an object and its (default) property Value
is probably due to the VisualBasic-oriented documentation that is
typically found accompanying various object models (particularly,
but _not_ exclusively, the ones of Office applications).  In VB,
when an object is named in an expression, the object itself is
typically NOT meant, except in certain contexts -- rather, the
"default property" of the object (often named 'Value', but not
always) is meant.  This is why, for example, VB needs two separate
assignment statements -- to provide the context...:
    let foo = cell ' copies cell.Value, actually!
    set foo = cell ' copies a reference to cell
(the 'let' keyword can be omitted, but 'set' cannot be -- a not
infrequent source of errors for VB and VBscript users).

In Python, there is no such 'implicitness' -- having a variable
name mean the variable itself, or one of its properties, depending.
The variable name, whenever it's used in an expression, always
means whatever the variable is referring to right now, neither
more nor less.  "explicit is better than implicit" (Python mantra).

Therefore, if you want to access a COM object's "default property",
you have to indicate that fact explicitly.  I normally find it
preferable to use the default property name, as in
    if cell.Value is None:

However, the Python/COM interface does provide an alternative --
*call* the object, and it will return its default property.  I.e.,
any COM object is a Python callable (if it has a default property,
that is; not all COM objects need have such a property...), and
calling it (i.e., following its name with open and square parens)
returns that default property.  I.e.:
    if cell() is None:

This style may appeal to some VB users, I guess.  It sure IS
handy when the default property is an *indexed* ones, i.e, it
takes one or more arguments to indicate what exactly we are
referring to -- typical of container-objects, whose default
property is normally named Item.  This is what you're using
in the expression:
    wkbook.Sheets("Sheet1")
it's actually shorthand for:
    wkbook.Sheets.Item("Sheet1")


> Thanks in advance. If there is a better group for posting win32 specific
> python questions, please let me know.

AFAIK, this group is just fine for any Python question,
including platform-specific ones.  You may want to look
at the ActiveState site -- it may be that they have set
up specific discussion groups for their distribution,
and they're the ones releasing win32all too now (since
Mark Hammond, the Father and Guru of win32all and Pythonwin,
now works for them).  But I don't know any details.


Alex