Apache OpenOffice Community Forum

User community support forum for Apache OpenOffice, LibreOffice and all the OpenOffice.org derivatives
https://forum.openoffice.org/en/forum/

[Solved] C# format copy problem: .uno:copy

https://forum.openoffice.org/en/forum/viewtopic.php?t=72229

Page 1 of 1

[Solved] C# format copy problem: .uno:copy

Posted: Tue Sep 09, 2014 11:22 am
by tbiggs
Summary
This problem relates to copying Open office(calc) cell formatting from one spreadsheet document to another document; using C sharp. More specifically using dispatch helper and .uno:paste .uno.copy. While the program is functional, it is very unreliable.

Solution
Using dispatcher would not be an acceptable solution in this situation.(please see follow up thread: viewtopic.php?f=20&t=72248&p=325011#p325011) *[see edit list]
What you experience with .uno:PasteSpecial is normal. Dispatcher commands are intended for user interface, not for programming.
by B Marcelly » Tue Sep 09, 2014 9:38 pm [see post below]
Thank you B Marcelly.

System Information
SharpDevelop Version : 4.4.1.9729-7196a277
.NET Version : 4.0.30319.18331 (using 2.0 for this project)
OS Version : Microsoft Windows NT 6.1.7601 Service Pack 1 (windows 7 ultimate)
Current culture : English (Australia) (en-AU)
Running under WOW6432, processor architecture: x86-64

What is this for? (An overview of my end goal)
My goal is to make a C# program (<-- I have reasons for this) that is capable of creating/changing the contents of an open office spreadsheet (calc) document; based on the contents of other (multiple) open office spreadsheet (calc) documents.

For example:
One spreadsheet will contain the formatting data, i.e. rows of cells that have the correct (size, font etc.), without values. Another spreadsheet would have values for certain cells, there could be lots of these spreadsheet documents. The program combines data from these separate documents into a single output spreadsheet document. This way I could change / update multiple documents by changing only one.

The structure of the spreadsheet that would be created would have a reasonable amount of formatting in terms of (size, font, cell position, cell formatting{decimal places, symbol}, bold etc.) and most of the time the documents follow a similar format. What I would like to do is copy cells from the format spreadsheet and paste them into my target spreadsheet (this is the problem). I can copy unformatted data in a different way from one document to another.

The following approach may be suggested however it can only copy within it’s own document (as far as I know):

Code: Select all

XCellRangeMovement xMovement;
xMovement = (XCellRangeMovement) oSheet;
xMovement.copyRange(celladdy,cellraddy);
//(celladdy – cell address, cellraddy – cell range address)
With that in mind I moved onto the following code (see program code) which works.

The problem
The code below(program code) is very unreliable; one out of about three times the cell will not be pasted.

I’ve replaced the line:

Code: Select all

Dispatcher.executeDispatch((XDispatchProvider)Frame, ".uno:paste", "", 0, null);
With this line:

Code: Select all

Dispatcher.executeDispatch((XDispatchProvider)Frame, ".uno:PasteSpecial", "", 0, null);
And received the following pop-up, indicating that perhaps the problem is with the coping not the pasting.
Image
I’m not sure where to start fault finding this issue; is this a C problem, a Windows (clipboard) problem or an office problem? Is there a simpler solution to (transferring formatting) / cut and pasting between OpenOffice documents? Any help / suggestions / links would be much appreciated.

Program code

Code: Select all

/*
 * Created by SharpDevelop.
 * User: ---
 * Date: 1/08/2014
 * Time: 7:21 PM
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;

// CLI declarations
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.bridge;
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.text;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.table;
using unoidl.com.sun.star.awt;
using unoidl.com.sun.star.style;

namespace copycell
{
    class Program
    {
        public static void Main(string[] args)
        {
            
            //Get a ComponentContext
            XComponentContext xLocalContext = uno.util.Bootstrap.bootstrap();
            
            //Get MultiServiceFactory
            unoidl.com.sun.star.lang.XMultiServiceFactory xRemoteFactory = (unoidl.com.sun.star.lang.XMultiServiceFactory)xLocalContext.getServiceManager();
            unoidl.com.sun.star.lang.XMultiServiceFactory xRemoteFactory2 = (unoidl.com.sun.star.lang.XMultiServiceFactory)xLocalContext.getServiceManager();
            
            //Get a ComponentLoader
            XComponentLoader aLoader = (XComponentLoader)xRemoteFactory.createInstance("com.sun.star.frame.Desktop");
            
            XDesktop desktop = (XDesktop)xRemoteFactory.createInstance("com.sun.star.frame.Desktop");
            unoidl.com.sun.star.beans.PropertyValue[] Args = new unoidl.com.sun.star.beans.PropertyValue[1];
            XComponent Document1 = aLoader.loadComponentFromURL(@"file:///C:/ooo_test/test.ods", "_blank", 0, Args);
            
                    //open the the correct sheet
                    XSpreadsheets oSheets = ((XSpreadsheetDocument)Document1).getSheets();
                    XIndexAccess oSheetsIA = (XIndexAccess)oSheets;
                    XSpreadsheet oSheet = (XSpreadsheet)oSheetsIA.getByIndex(0).Value; 
            
            unoidl.com.sun.star.frame.XFrame Frame = desktop.getCurrentFrame();
            
            //Create the Dispatcher
            unoidl.com.sun.star.frame.XDispatchHelper Dispatcher = (XDispatchHelper)xRemoteFactory.createInstance("com.sun.star.frame.DispatchHelper");
            
            unoidl.com.sun.star.beans.PropertyValue[] pvargs = new unoidl.com.sun.star.beans.PropertyValue[3];
            pvargs[0] = new unoidl.com.sun.star.beans.PropertyValue();
            pvargs[0].Name = "ToPoint";
            pvargs[0].Value = new uno.Any(typeof(string), "$A$1");
            
            unoidl.com.sun.star.beans.PropertyValue[] pvargs2 = new unoidl.com.sun.star.beans.PropertyValue[3];
            pvargs2[0] = new unoidl.com.sun.star.beans.PropertyValue();
            pvargs2[0].Name = "ToPoint";
            pvargs2[0].Value = new uno.Any(typeof(string), "$A$21");
            
            //pick the cursor postion
            Dispatcher.executeDispatch((XDispatchProvider)Frame, ".uno:GoToCell", "", 0, pvargs);
            //copy @ cursor postion
            Dispatcher.executeDispatch((XDispatchProvider)Frame, ".uno:copy", "", 0, null);
            
            Dispatcher.executeDispatch((XDispatchProvider)Frame, ".uno:GoToStart", "", 0, null);
            Dispatcher.executeDispatch((XDispatchProvider)Frame, ".uno:GoToCell", "", 0, pvargs2);
            //Dispatcher.executeDispatch((XDispatchProvider)Frame, ".uno:PasteSpecial", "", 0, null);
            Dispatcher.executeDispatch((XDispatchProvider)Frame, ".uno:paste", "", 0, null);

            Console.Write("Press any key to continue . . . ");
            Console.ReadKey(true);
            

        }
    }
}
Useful links
“Calc/API/Programming - Apache OpenOffice Wiki.” Accessed September 9, 2014. https://wiki.openoffice.org/wiki/Calc/API/Programming.
“Calc Macro: Paste Function (View Topic) • Apache OpenOffice Community Forum.” Accessed September 9, 2014. viewtopic.php?f=20&t=10706.
“Cells and Ranges - Apache OpenOffice Wiki.” Accessed September 9, 2014. https://wiki.openoffice.org/wiki/Docume ... and_Ranges.
“Compare 2 Documents Using C# Code and OOo Writer API (View Topic) • Apache OpenOffice Community Forum.” Accessed September 9, 2014. viewtopic.php?f=44&t=2795.
“Creating an OpenOffice Calc Document with C#.” Accessed September 9, 2014. https://suite.io/mark-alexander-bain/1w222aa.
“Creating an OpenOffice Writer Document with C#.” Accessed September 9, 2014. https://suite.io/mark-alexander-bain/1vvr2aa.
“Cut/Paste Loop Macro Problem - Ask LibreOffice.” Accessed September 9, 2014. http://ask.libreoffice.org/en/question/ ... o-problem/.
“How to Format Calc Spreadsheet Cells with C#.” Accessed September 9, 2014. https://suite.io/mark-alexander-bain/1x0j2aa.
“How to Use a Macro to Format Calc Cells.” Accessed September 9, 2014. https://suite.io/mark-alexander-bain/14pc2aa.
“Interface XComponent.” Accessed September 9, 2014. http://www.openoffice.org/api/docs/comm ... onent.html.
“.net - OpenOffice and C# - Stack Overflow.” Accessed September 9, 2014. http://stackoverflow.com/questions/4384 ... nd-c-sharp.
“[Solved] Copy and Paste Syntax (View Topic) • Apache OpenOffice Community Forum.” Accessed September 9, 2014. viewtopic.php?f=25&t=16413.
“The StarDesktop - Apache OpenOffice Wiki.” Accessed September 9, 2014. https://wiki.openoffice.org/wiki/Docume ... tarDesktop.

Edit list
09/09/14 - Original post
10/09/14 - ((Edit P3A1)) - Strike out text. Reason: moved to another topic and have marked this thread as solved. Question outside of this thread's scope.
10/09/14 - Edited "Solution" to incorporate material pertaining to the question and added link to the follow up question.

Re: C# format copy problem: .uno:copy

Posted: Tue Sep 09, 2014 2:38 pm
by B Marcelly
tbiggs wrote:This problem relates to copying Open office(calc) cell formatting from one spreadsheet document to another document; using C sharp.
Your Program code does a copy from cell A1 of Document1, followed by a paste to cell A21 of the same sheet in the same document.
You should have opened two documents, and used the current frame from the first for the selection of cell A1 and Copy, and the current frame from the second for the selection of cell A21 and Paste.

What you experience with .uno:PasteSpecial is normal. Dispatcher commands are intended for user interface, not for programming.

Re: C# format copy problem: .uno:copy

Posted: Wed Sep 10, 2014 10:23 am
by tbiggs
Thank you very much for the reply B Marcelly.

What you experience with .uno:PasteSpecial is normal. Dispatcher commands are intended for user interface, not for programming.

So using dispatcher would not be an acceptable solution in this situation.

I did find XSheetLinkable http://www.openoffice.org/api/docs/comm ... kable.html of interest. Using that(if I understand correctly) I could link a sheet from another document to another. Then use XCellRangeMovement http://www.openoffice.org/api/docs/comm ... ement.html to copy from one document to another.

For example assuming I have two documents; "source document" containing "Ssheet" and "traget document" containing "Tsheet". I could open (source document[Ssheet]), then link the (target document[Tsheet]) with XSheetLinkable. The XCellRangeMovement could then be used to copy data from [Ssheet] in the source document to [Tsheet] in the target document (through the link). When Open-office closes I could then open the target document to the "Tsheet" sheet and observe the changed cells.

Unfortunately XSheetLinkable is deprecated, I assume by this I will run into nothing but trouble if I attempt to implement it. Am I correct in that assumption?
Has XSheetLinkable this been superseded by something else?
Or is there a far simpler (super obvious) solution I have missed?

(Edit P3A1) I have moved further discussion to another post viewtopic.php?f=20&t=72248&p=325011#p325011 to assist in ease of reading and searches. This thread will be marked as solved **[see edit list].
All times are UTC+02:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited