Is there any way to automate this in my spreadsheet? [closed]
Thank you for all your guidance! Learning new things everyday.
First time here? Check out the FAQ!
Thank you for all your guidance! Learning new things everyday.
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.
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:
#!python
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(
"com.sun.star.bridge.UnoUrlResolver", localContext )
# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",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()
cursor.gotoEndOfUsedArea(False)
cursor.gotoStartOfUsedArea(True)
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
else:
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)
foldRows(used_range,
fillNewCol,
(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:
soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"
calc:gen-new-col.py
file ...@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.
@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 :)
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…
Asked: 2019-07-18 21:16:11 +0100
Seen: 468 times
Last updated: Jul 23 '19
How do I export a chart in an image format from LibreOffice Calc? [closed]
Are there plans for a "papercut" project for libreoffice [closed]
Is it normal for Calc goal seek to take very long? [closed]
Please refine "Search" in Calc - implement functions in Gnumeric [closed]
LibreOffice Calc will not link to external data via internet [closed]
Is there a LibreOffice .odt, .ods viewer for Android? [closed]
Why is Calc so much slower at opening/saving files than MS-Office? (win7 x64) [closed]
Please don't close your questions that way. You made two mistakes:
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!