[Solved] Calc: Pasting from Clipboard (JAVA or OOBasic)

[twilli3141]

Hello:

I would like to paste from Excel to a cell in CALC using the system clipboard (in code). I want to use the HTML Format DataFlavor so that cell attributes are preserved.

I have the necessarry components to get the data from the clipboard:

Code: Select all

			

     // query for the interface XClipboard from the Clipboard service
     XClipboard xClipboard = (XClipboard) UnoRuntime.queryInterface(XClipboard.class, oClipboard);
     
     //Get the transferable	  	  
     XTransferable xTransferable = xClipboard.getContents();
			
     //Get the DataFlavor I want: "HTML Format"
     DataFlavor[] dataFlavors = xTransferable.getTransferDataFlavors();
	  	  	
     DataFlavor selectedFlavor=null;
     for (DataFlavor dataFlavor: dataFlavors) {
	  if (dataFlavor.HumanPresentableName.equals("HTML Format")) {
	  	  selectedFlavor=dataFlavor;
	  	  break;
	  }
     }
	
     //Get the data matching my selected DataFlavor- can print this out, so I know it is correct  	  	
     Object data = xTransferable.getTransferData(selectedFlavor)
	  	  	
     //Where do I go from here ???

I can't find any documentation on this - plenty of stuff about getting data onto the Clipboard - even reading it from the Clipboard.

Nothing about actually pasting it into cells - while preserving attributes.

Any help appreciated - thanks.

[Zizi64]

I would like to paste from Excel to a cell in CALC using the system clipboard (in code).

Maybe I do not understand, what you want to do exactly?
From Excel PROGRAM or from an Excel .xls document (opened by OOoCalc) you want copy the data?
Want you to control the Excel program or only an Excel document by OOoCalc macro ?

[twilli3141]

Tibor:

I have an OOoCalc document.

I have some information on the Windows clipboard which has been copied from Excel.

When you do a "copy" (ctrl-c) from Excel, the information is placed on the clipboard in several formats - one of these is HTML Format. The HTML Format includes cell attributes in the form of an inline <style> sheet. OO can parse this HTML and thus copy the cell attributes (such as background color) as well as the values.

My challenge is to paste the data into OOoCalc programatically - using the HTML format so that the cell attributes are retained.

The code above gets the "HTML Format" data off the clipboard - I just don't know how to programatically trigger a "paste" without parsing the HTML myself???

Thanks.

[Zizi64]

I recorded the example SUBs in macro with macro recorder.
see attached file:
(Copy some formatted cells in Excel, then click in a cell in OOo Calc and push a button.)

Paste.ods

(10.87 KiB) Downloaded 648 times

This is what you need?

[twilli3141]

Tibor:

Thanks!! It looks like the DispatchHelper service I found inside your Macros is probably exactly what I need.

Now, if I can just figure out the details of making it work in JAVA - the JAVA side is really poorly documented.

For example, looking at the API entry for XDispatchHelper:

http://api.openoffice.org/docs/common/r ... elper.html

No clue is given as to what values are valid for the URL parameter.

Can you point out somewhere in the documentation where you got the syntax for executing the PasteSpecial service. It appears in your MACRO as:

".uno:PasteSpecial"

the 2nd parameter to the dispatcher.executeDispatch() call:

dispatcher.executeDispatch(document, ".uno:PasteSpecial", "", 0, args3())

?????????

Regards.

[twilli3141]

Tibor:

I found the documentation for Command URL's and made it work based on your Macros - many thanks.

I still cannot find any documentation for the "Format" property: From your macro:

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Format"
args1(0).Value = 52

I used it in JAVA and it works great, but I would really like to know where this is documented.

Regardless, thanks for the lead.

In case anyone is interested, the JAVA equivalent to Tibor's macro's l;ooks like this:

Code: Select all

//Get the DispatchHelper service
Object oDispatchHelper = xServiceManager.createInstanceWithContext(
	  	          "com.sun.star.frame.DispatchHelper", xRemoteContext);

//Get the DispatchHelper interface
XDispatchHelper xDispatchHelper = (XDispatchHelper)
	          UnoRuntime.queryInterface(XDispatchHelper.class, oDispatchHelper);

//Get the Frame for the current document
XFrame xFrame=xDesktop_.getCurrentFrame();

//Query for the frame's DispatchProvider
XDispatchProvider xProvider = (XDispatchProvider)UnoRuntime.queryInterface ( 
	  	       XDispatchProvider .class, xFrame );

//Set the PropertyValue array - don't know where "Format" is documented 
  PropertyValue [] propertyValues = new PropertyValue[1];
  PropertyValue propertyValue=new PropertyValue();
  propertyValue.Name="Format";
  propertyValue.Value=121;
  propertyValues[0]=propertyValue;

//Execute the PasteSpecial
xDispatchHelper.executeDispatch(xProvider, ".uno:PasteSpecial", xFrame.getName(), 0, propertyValues);

[Zizi64]

From PM:

Tibor:

Thanks for helping me on the forum. I really need to know one more thing.
Where did you find out about the "Format" property and what the value "52"
means???

I can't find documentation on this anywhere. Here is the excerpt from
your Macro that I am talking about:

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Format"
args1(0).Value = 52

Regards;
Terry

I not found such documentation.
Just I recorded the macro using the Macro Recorder:
I copied some formatted Excel cells to the clipboard, then started the Macro Recorder in OOo and I manually inserted the content of clipboard onto target area. I used the 'pastes pecial' in the three cases, with different options. The format code created automatically by Macro Recorder. I do not know the other codes.

I think:
If you need to collect the format code list and you need identify all of codes: You must try all of options of "paste special" by use the Macro Recorder.

[twilli3141]

Understood - thanks again.