Detect and omit empty rows

The Application Programming Interface and the OASIS Open Document Format

Detect and omit empty rows

Postby bhh1988 » Thu Nov 21, 2013 12:35 pm

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
bhh1988
 
Posts: 6
Joined: Wed Nov 20, 2013 5:53 am

Re: Detect and omit empty rows

Postby FJCC » Thu Nov 21, 2013 3:53 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7492
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Detect and omit empty rows

Postby bhh1988 » Thu Nov 21, 2013 8:11 pm

Here's an example excel sheet.

Thanks
Attachments
foobar-one-million-rows.xlsx
(28.63 KiB) Downloaded 113 times
OpenOffice 3.5 on Linux
bhh1988
 
Posts: 6
Joined: Wed Nov 20, 2013 5:53 am

Re: Detect and omit empty rows

Postby FJCC » Thu Nov 21, 2013 9:15 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7492
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Detect and omit empty rows

Postby bhh1988 » Fri Nov 22, 2013 2:44 am

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
bhh1988
 
Posts: 6
Joined: Wed Nov 20, 2013 5:53 am

Re: Detect and omit empty rows

Postby FJCC » Fri Nov 22, 2013 5:46 am

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 \.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7492
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Detect and omit empty rows

Postby bhh1988 » Fri Nov 22, 2013 10:55 am

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
bhh1988
 
Posts: 6
Joined: Wed Nov 20, 2013 5:53 am


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest