Page 1 of 1

Detect and omit empty rows

Posted: Thu Nov 21, 2013 12:35 pm
by bhh1988
Some excel sheets for some reason end with a ton of empty rows. I would like an automated way of figuring out the used area (rows with content in them) and omitting the rest.

Using cursor.gotoEndOfUsedArea(True) seems to actually count the empty rows as part of the used area, so that doesn't work. Also, is there any way to do this without killing performance, as checking too many cells/rows for emptiness can be really slow as it's inter-process communication.

Re: Detect and omit empty rows

Posted: Thu Nov 21, 2013 3:53 pm
by FJCC
It is hard to suggest a solution without knowing what is in these cells you want to eliminate. They are not actually empty, or at least one of them isn't, because the gotoEndOfUsedArea() method is detecting something. There is a queryContentCells() method that allows you to find cells with only certain types of content. That might be useful in this case. Can you post an example of a file with this problem? There is an Upload Attachment tab just below the box where you type a response.

Re: Detect and omit empty rows

Posted: Thu Nov 21, 2013 8:11 pm
by bhh1988
Here's an example excel sheet.

Thanks

Re: Detect and omit empty rows

Posted: Thu Nov 21, 2013 9:15 pm
by FJCC
The root of the problem with this file is that cell H1041137 contains a \ character. Any idea why that is there? If that were eliminated, gotoEndOfUsedArea() would return the expected result, unless I've missed something else.

Re: Detect and omit empty rows

Posted: Fri Nov 22, 2013 2:44 am
by bhh1988
Interesting...I can't find column H in the spreadsheet. It skips from column F to I. Are you able to find this in the GUI? If so, what OS and software? Or did you find this character using the api?

Re: Detect and omit empty rows

Posted: Fri Nov 22, 2013 5:46 am
by FJCC
I found it while playing with the queryContentCells() method but there is an easier way. To see column H, left click and hold the mouse button down on the letter F at the top of that column and drag the cursor over to column I, the apparently adjacent column. You should end up with both columns selected. Then go to the menu Format -> Column and select Show. With all of the columns now visible, you can now use the key combination CTRL END to go to the bottom right corner of the used area. Scroll left along that row and you will find the \.

Re: Detect and omit empty rows

Posted: Fri Nov 22, 2013 10:55 am
by bhh1988
Yea I see it now, thanks! So it seems to me that Excel treats those columns as hidden and therefore doesn't print them to pdf. Libreoffice doesn't seem to respect the notion of hidden cells or sheets, at least in version 3.5.