# Is there any way to copy Calc cell content and paste... something else?

Somewhat absurd title I know :-). But sane question really.

Let's say I see abbreviation «MS» in a cell. I copy it and have «Microsoft» instead of «MS» in the clipboard? Can it be done in Calc without using any third party tools (like advanced clipboard manager, AHK and stuff like that)?

If it can not, than maybe it is possible to have part of the cell content invisible? In my example «MS» is what I see, but there is some hidden text in the cell, for example, « (Microsoft)», so it is «MS (Microsoft)» that I really copy.

edit retag close merge delete

Sort by » oldest newest most voted

Here is a complete macro. It is written in Python, so use APSO to add the code.

Then for convenience, go to Tools -> Customize and set a hotkey to run it.

import re

import uno
import unohelper
from com.sun.star.datatransfer import XTransferable, DataFlavor

def copy_and_replace():
oDoc = XSCRIPTCONTEXT.getDocument()
oRange = oDoc.getCurrentSelection()
oCellTopLeft = oRange.getCellByPosition(0, 0)
sText = oCellTopLeft.getString()
dReplace = {
"MS" : "Microsoft",
}
pattern = re.compile('|'.join(dReplace.keys()))
sResult = pattern.sub(lambda x: dReplace[x.group()], sText)
transferable = Transferable(sResult)
ctx = XSCRIPTCONTEXT.getComponentContext()
oClip = ctx.getServiceManager().createInstanceWithContext(
"com.sun.star.datatransfer.clipboard.SystemClipboard", ctx)
oClip.setContents(transferable, None)

class Transferable(unohelper.Base, XTransferable):
"""Keep clipboard data and provide them."""

def __init__(self, text):
df = DataFlavor()
df.MimeType = "text/plain;charset=utf-16"
df.HumanPresentableName = "encoded text utf-16"
self.flavors = [df]
self.data = [text] #[text.encode('ascii')]

def getTransferData(self, flavor):
if not flavor: return
mtype = flavor.MimeType
for i,f in enumerate(self.flavors):
if mtype == f.MimeType:
return self.data[i]

def getTransferDataFlavors(self):
return tuple(self.flavors)

def isDataFlavorSupported(self, flavor):
if not flavor: return False
mtype = flavor.MimeType
for f in self.flavors:
if mtype == f.MimeType:
return True
return False

# Functions that can be called from Tools -> Macros -> Run Macro.
g_exportedScripts = copy_and_replace,


For example, if a cell contains "The MS product," then run the macro and paste into Notepad to produce "The Microsoft product."

For more string changes, simply add entries to the dReplace dictionary.

Sources:

more

1

Thank you! It looks very promising and I am willing to give it try, but... I am at loss how to get it working.

Okay, I installed APSO, opened its dialogue box, added a new module. Now I guess I should open the module for editing and add your code, but when I click «Edit» some command prompt window opens and closes so quickly, that I am barely able to see its flashing, let alone reading what it says (not sure whether it is relevant, but I have Python 3.4 installed).

( 2018-04-02 10:31:03 +0100 )edit

Yes, I was confused about that the first time as well. You need to go to Tools -> Extension Manager, select APSO and press Options. Choose the path to your favorite editor; for example, mine is set to C:\Program Files (x86)\Vim\vim80\gvim.exe. Many people on Windows prefer Notepad++ instead.

( 2018-04-02 15:20:48 +0100 )edit

Your script works perfectly. Thank you very much.

( 2018-04-04 17:01:08 +0100 )edit

Glad it helped. Please mark the answer correct and upvote, as explained in guidelines for asking.

( 2018-04-05 01:39:06 +0100 )edit

You're right. Just did it. Should have done earlier.

( 2018-04-05 12:04:48 +0100 )edit

There is no ready-made tool for this, I'm afraid, and I don't remember any similar question posted in the past.

-1- Relying on user code a lot of very special behaviour can be implemented. More details needed.
-2- You may think of tricky workarounds depending on what side-effects / needs you are ready to accept.
One example:
Assume cells in a row set to a constant height don't allow for more than one line of text diplayed in the preset font size. Enter Microsoft (Ctrl+Enter) MS (Enter) and you will only see the "MS". Copy/Paste the content into a cell of a row set to optimal height ...
Anotherone? ...

Edit1 (related to the comments below):
See the attached example. Copy the contained "macro" to your 'Standard' library (or leave it in the document?) and "customise" your LibO to call it on Alt+C e.g. Select a subrange of the abbreviations in column A and use the Alt+C or whatever shortcut you chose. You won't see much of an action, but you can paste the 'Full info' elsewhere subsequently. Of course the preparatory service by the formulae in column B can also be done by user code. I would advise to do it as demonstrated if there aren't preemptory reasons against.
(Sorry! I did not thouroughly study the code suggested by @Jim K . My suggestions may be obsolete.)

more

Thank you for the idea. We have already tried this trick before, but it is not a very convenient way (you have to cut the visible part manually). It is not to say such cutting can't be automated with, for examle, AHK. However, solution based on Libre office tools would be preferable.

( 2018-04-02 10:41:01 +0100 )edit

The "idea" was only posted to exemplify my statement -2-. I wouldn't recommend it earnestly. The only appropriate way to assign full info to abbreviations is a lookup table. And in spreadsheets what can be done by formulae with sufficient efficiency should be done this way.
However, there cannot be a ready-made tool for every highly specific task. In this case, if urgently needed, the action can be supported by a few lines of BASIC code. See "Edit1" to my answer.

( 2018-04-02 15:20:35 +0100 )edit

Your code works, but not exactly reliably and robustly. I have been playing with your example for some time. In approximately one fifth of cases clipboard is left empty. Still not sure which combination of factors leads to this, looks like it happens at random (of course, it is not, but this is how it looks). So for now I am going to stick with @Jim K.'s solution.

That being said I appreciate your helping as much as that of Jim.

( 2018-04-04 17:14:33 +0100 )edit

Would you mind to attach an example you got the issue with?

( 2018-04-04 19:26:35 +0100 )edit

There is nothing to upload really. It is that same example you uploaded earlier. I select a few cells from the «A» column and hit Alt-C (used your suggestion on the shortcut). In most cases it works as expected, but sometimes nothing ist pasted.

( 2018-04-05 11:58:40 +0100 )edit

You surely use Shift+Ctrl+V where you want to paste in addition? If only Ctrl+V is used a formula will be pasted which is expected to return an empty result in many cases.
In my example document only the grey range is aligned with the cells in the hidden column containing the looked-up full text.
I tested a lot again and did not get any malkfunction.

( 2018-04-05 13:43:10 +0100 )edit

I should admit to have been wrong. I tested your script on two more computers (one running Xubuntu at work and my wife's laptop with Windows 10) and it worked without any problems. It looks like the clipboard mechanism on my own computer fails from time to time (it is copy of Windows 7 that is about five years old, so maybe it is not that surprising after all). Anyway, as I see it now, your solution is as good as Jim's. Sorry for confusion and thank you for your helping!

( 2018-04-09 11:57:51 +0100 )edit

## Stats

Asked: 2018-03-30 13:58:21 +0100

Seen: 111 times

Last updated: Apr 02 '18