Ask Your Question
0

How do I detect a blank/unfilled (ie non-zero, non-null) cell in calc?

asked 2017-07-11 00:13:02 +0100

avei gravatar image

Please excuse what should be a simple question, but alas, I'm finding very little simple about writing python macros.

I have a spreadsheet with a column with a series of rows, most, but not all containing a datum, usually a 3 character code. My plan is to use this 3 character code to access (way down the road) a postgresql db and retrieve data and insert it into an adjacent (nearby) column. There will be occasional blank cells in this column which I need to do nothing with. My frustration with trying to detect a simple empty cell has spawned a new question. uno claims to have an enumerated value for cell types (ref: Pytoniak) com.sun.star.table.CellContentType.EMPTY com.sun.star.table.CellContentType.VALUE com.sun.star.table.CellContentType.STRING etc.

Python tells me that it can't find com.sun.star.table.CellContentType.EMPTY or any of the variants.

So, my question is twofold: What is the correct way to detect a cell property? Where (or what) are the enumberated values associated with that that I can put in in if/else statement?

import sys
import uno
import unohelper
import time
import urllib.request

from datetime import date

and

for rows in range(0, rowcount):
                oCell1 = oSheet.getCellByPosition(0,rows)
                oCell2 = oSheet.getCellByPosition(1,rows)
                #oCell2.String = oCell1.String
                if oCell1.Type == com.sun.star.table.CellContentType.EMPTY :
                        oCell2.String = 'EmptyNULL'
                else:
                        oCell2.String = 'Full'

This fails with a com.sun.star.table.CellContentType.EMPTY not valid error.

The oo/loBASIC code examples use the above format. I can find none for python. Should I give up on python/lo? These seem to me to be very fundamental tasks that I can't seem to figure out on my own with uno. Is there a good source for this kind of information that I am overlooking? Thanks folks.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-07-11 01:05:11 +0100

librebel gravatar image

Hello @avei,

please try if inserting this line helps:

from com.sun.star.table.CellContentType import EMPTY, VALUE, TEXT, FORMULA
edit flag offensive delete link more

Comments

Excellent suggestion. It worked. I am saved. at least for now.... I am starting to compile a python/uno/libreoffice(calc) list of how to do practical things techniques, as I go to may be make some clues for other newbies bumbling around in the libreoffice/python forest in the dark. Thank you for the light in the darkness!

avei gravatar imageavei ( 2017-07-11 03:48:23 +0100 )edit

You're welcome @avei,

please see this wiki page for more examples on importing uno enums in Python:

Transfer from Basic to Python

librebel gravatar imagelibrebel ( 2017-07-11 04:11:12 +0100 )edit
0

answered 2017-07-11 00:57:20 +0100

Lupp gravatar image

updated 2017-07-11 01:00:49 +0100

(I don't use Python.)

The long "mnemonic names" can be disposed in this case. The actually used Cell.Type values are 0 for blank, 1 for number (value), 2 for text, and 3 for formula. I don't think this will be changed within the next century.

If your goal actually is to test for any (large) number of cells one by one this will consume time. If you only need to find the ranges of empty cells as part of a given range use the QueryEmptyCells method available for any SheetCellRange object which returns a SheetCellRanges (plural!) object. This method is much faster than scripts sifting through.

edit flag offensive delete link more

Comments

Thanks to ratslinger, I don't need to search huge numbers. ratslinger taught me how to limit the searches. His help is very much appreciated. I will only need to check for these blank cells in a limited range of known data which may have a few blank cells in the data set. These are the ones I need to trap. I'll play around some more with your data. I appreciate your help. Thanks. I'm finding that finding this data on my own pretty frustrating. I'm going to work on consolidating this.

avei gravatar imageavei ( 2017-07-11 03:28:28 +0100 )edit

Using number for Cell.Type didn't work. I tried a second approach, from the python shell I loaded the com.sun.star.table.CellContentType as above. The constants supplied are indeed enumerated constants: f>>rom sun.star.table.CellContentType import EMPTY

EMPTY <enum instance="" com.sun.star.table.cellcontenttype="" ('empty')&gt;="" so,="" i="" guess="" using="" the="" numbers="" in="" python="" won't="" work.<="" p="">

avei gravatar imageavei ( 2017-07-11 06:27:53 +0100 )edit

The type of the cell property SheetCelll.Type is 'Long'. I don't understand for what reason Python should not work out a comparison like sheetCell.Type=0 if sheetCell got assigned a cell and the correct syntax is used.

Also for a known range the range.QueryEmptyCells should be more efficient than sifting through the cells. And it offers the SheetCellRanges service for the result. After all you need the result in an evaluable form.

Lupp gravatar imageLupp ( 2017-07-11 10:17:07 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-11 00:13:02 +0100

Seen: 632 times

Last updated: Jul 11 '17