Detect and omit empty rows

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
bhh1988
Posts: 6
Joined: Wed Nov 20, 2013 5:53 am

Detect and omit empty rows

Post 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.
OpenOffice 3.5 on Linux
FJCC
Moderator
Posts: 9231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Detect and omit empty rows

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
bhh1988
Posts: 6
Joined: Wed Nov 20, 2013 5:53 am

Re: Detect and omit empty rows

Post by bhh1988 »

Here's an example excel sheet.

Thanks
Attachments
foobar-one-million-rows.xlsx
(28.63 KiB) Downloaded 473 times
OpenOffice 3.5 on Linux
FJCC
Moderator
Posts: 9231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Detect and omit empty rows

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
bhh1988
Posts: 6
Joined: Wed Nov 20, 2013 5:53 am

Re: Detect and omit empty rows

Post 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?
OpenOffice 3.5 on Linux
FJCC
Moderator
Posts: 9231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Detect and omit empty rows

Post 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 \.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
bhh1988
Posts: 6
Joined: Wed Nov 20, 2013 5:53 am

Re: Detect and omit empty rows

Post 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.
OpenOffice 3.5 on Linux
Post Reply