Skipping empty cells when pasting a row of data

Discuss the spreadsheet application
Post Reply
htcmsg
Posts: 2
Joined: Sat Oct 01, 2022 7:31 pm

Skipping empty cells when pasting a row of data

Post by htcmsg »

Hi, is it possible for me to skip empty cells in a row like this.

Before : a  b   c  d    e   >>  Output : abcde

(no empty cells after output in the same row )

Thanks : )
OpenOffice 3.1 on Windows Vista
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Skipping empty cells when pasting a row of data

Post by Lupp »

There's no respective option to do this with an ordinary Copy/Paste.
But you can do it with the help of some lines of user code.
Just try the attached example.
aoo108597CompactingRowRange.ods
(11.99 KiB) Downloaded 40 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Skipping empty cells when pasting a row of data

Post by karolus »

why not simply inplace:

Code: Select all

def shrink_row_selection(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    row_range = doc.CurrentSelection
    sheet = row_range.Spreadsheet
    cursor = sheet.createCursorByRange(row_range)
    data = cursor.DataArray
    cursor.clearContents(63)
    out = [[entry for entry in data[0] if entry]]    
    cursor.collapseToSize(len(out[0]),1)
    cursor.DataArray = out
and copy & paste later

…or alternativly, select source_range and meanwhile holding <ctrl>key the leftmost target_cell:

Code: Select all

def shrink_source_row_paste2target(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    row_range , target = doc.CurrentSelection
    sheet = target.Spreadsheet
    cursor = sheet.createCursorByRange(target)
    data = row_range.DataArray
    out = tuple( filter( lambda x: f"{x}", data[0]) )     # dont skip zero!!
    cursor.collapseToSize( len(out), 1)
    cursor.DataArray = (out,)
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
htcmsg
Posts: 2
Joined: Sat Oct 01, 2022 7:31 pm

Re: Skipping empty cells when pasting a row of data

Post by htcmsg »

A little bit compliacted to me, but I will try.

Thanks : )
OpenOffice 3.1 on Windows Vista
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Skipping empty cells when pasting a row of data

Post by karolus »

the Code-snippets above are written for a recent Version of Python 3.5 or higher with a recent Version of LibreOffice 7.1 or higher
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Skipping empty cells when pasting a row of data

Post by Lupp »

Somebody may want to play with the alternative solution contained in the attached example document.
It is commented and should also work with very old versions of OOo, AOO, LibO.
A special feature is that it binds formats (cell and text attributes) to the data.

The code may need adaptions to special requirements. It should be moved to modules of the local standard library. (The calling hyperlink in the example will then also need editing, of course.)
AOOadjustCellContentsLeft.ods
(21.83 KiB) Downloaded 43 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply