Ask Your Question

Is there any way to automate this in my spreadsheet? [closed]

asked 2019-07-18 21:16:11 +0100

nihle45 gravatar image

updated 2020-08-02 03:01:20 +0100

Alex Kemp gravatar image

Thank you for all your guidance! Learning new things everyday.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by nihle45
close date 2019-07-23 03:17:43.478786


Please don't close your questions that way. You made two mistakes:

  1. You didn't mark an answer that you chose as a solution using the checkmark;
  2. You have removed your question and replaced it with some meaningless "thank you".

These things disallow others with similar questions to learn from the question on this Q&A site, which goal is to collect questions and their resolutions for everyone. So in the end, this turned out (inadvertently, I'm sure) to be very selfish, and not giving back to the community that tried to help you.

Please use your question's update history to recover the original question, and mark the correct solution, for everyone's benefit. Thanks!

Mike Kaganski gravatar imageMike Kaganski ( 2019-07-23 04:14:42 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2019-07-19 12:19:51 +0100

robleyd gravatar image

A slightly less complex solution might be to select the range you want to work with and use a standard filter - Data | More Filters | Standard Filter

You might want to use a named range if there is a lot of rows to work with. You mention ten million items; of course you won't be able to add that many rows to a Calc sheet as the maximum number of rows is 1048576.

If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. That will help other people with the same question.

edit flag offensive delete link more

answered 2019-07-19 04:12:04 +0100

Hi-Angel gravatar image

updated 2019-07-20 15:53:05 +0100

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
    # get the uno component context from the PyUNO runtime
    localContext = uno.getComponentContext()
    resolver = localContext.ServiceManager.createInstanceWithContext(
        "", localContext )
    # connect to the running office
    ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
    smgr = ctx.ServiceManager
    desktop = smgr.createInstanceWithContext( "",ctx)
    return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
    cursor = sheet.createCursor()
    return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
    for row in rectangle.Rows:
        accum = f(row, accum)

def fillNewCol(row, col_write_to):
    (dst_col, row_index) = col_write_to
    if row.getCellByPosition(I_COL_MARKS, 0).String == MARK:
        dst_cell = dst_col.getCellByPosition(0, row_index)
        # see comments under the post: a cell has different state when a number is
        # assigned compared to a string. So here I test for whether we're dealing
        # with strings or numbers. But you may want to remove overhead of this test
        # if you know what you deal with right away
        src_cell = row.getCellByPosition(I_COL_TO_READ_FROM, 0)
        if src_cell.String.isdigit():
            dst_cell.Value  = src_cell.Value
            dst_cell.String = src_cell.String
        return (dst_col, row_index+1)
    return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
        (focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))

The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

  1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"
  2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
  3. Save the python code as file ...
edit flag offensive delete link more


Caveat: The numbers look to be text now (see left alignment of 2,4).

Opaque gravatar imageOpaque ( 2019-07-19 12:32:42 +0100 )edit

@Opaque oh, yeah, I mean, I am not sure I fully understood you (e.g. I don't see how you figured that from the screenshot), but I just remember that for whatever reason cells has two ways to assign to them: one way is cell.String = some_text and the other one is cell.Value = some_number. I'll probably replace String with Value in the code in a sec, and add a comment about it.

Hi-Angel gravatar imageHi-Angel ( 2019-07-20 14:17:32 +0100 )edit

I figured we don't know whether OP is using a text or numbers (or do you?), and hence can't just blindly replace String with Value, it may break script for the OP. So for now I just inserted a comment in the code.

Hi-Angel gravatar imageHi-Angel ( 2019-07-20 14:24:58 +0100 )edit

@Opaque okay, to make sure script does not break if OP has strings instead of numbers, I figured I can just do cell.String.isdigit(), and assign either to Value or to String based on that. Done. Huh, can't delete my prev. comment :/ I just figured btw what you meant by "left alignment", I see now how you figured that from the shot :)

Hi-Angel gravatar imageHi-Angel ( 2019-07-20 14:45:17 +0100 )edit

answered 2019-07-20 23:39:59 +0100

Here is a sample of what I would do, just put "x" next to number and bush button. For Button to work, set macro’s at Medium….In Tools/Option, LibreOffice/Security..Macro Security.. Medium…

C:\fakepath\Just push the button.ods

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2019-07-18 21:16:11 +0100

Seen: 468 times

Last updated: Jul 23 '19