2

Have created a Gtk "calculator" that has a spreadsheet-like element (similar to and partially based on GtkSheet/GtkExtra). It is capable of, amongst other things, using the usual Ctrl-X/C and Ctrl-V to Cut/Copy/Paste data/strings within itself, and also across to other apps, such as Excel/OpenOffice etc (i.e. in both directions) using GtkClipboard elements.

For example, it can also perform Cut/Copy/Past of ranges (though via a different route, and this in not in Python) discussed in this SO posting.

However, when an Excel, OpenOffice, etc cell contains a formula (and displays the formula's value ... eg. formula in cell =1+2, displays 3), copying the Cell into the proprietary Gtk-Spreadsheet via GtkClipboard only pastes/contains the "display value" (not the formula).

How does one get GtkClipboard to pass the formula instead? Does this require some GDKAtom specification, is it necessary to use gtk_clipboard_request_text() vs. _request_content() etc. and work through the "(Gtk) target", or ???

P.S. Copying, say, an Excel cell (with formula) to, say, an OpenOffice cell works correctly in that OO will contain the formula and display the result (though we cannot be certain if the formula is contained in the OS's clipboard, or if OO et al. are doing something extra). Is it presumptuous to imagine that GtkClipboard should be able to do it as well ... ?

UPDATE: Since the Original Post (OP), we have followed-up with some testing to further assess our "intuition" that perhaps the GtkClipboard or the Windows clipboard, or some combination therein, may not be capable of directly passing spreadsheet formulas (or "full" Cell contents) via Cut/Copy/Paste (CCP). Here are some results:

1) We tried to look at the OO, POI, etc source code to see how they do it, but it's much too big. We tried to look at GNumerics source code, but from our perspective, it uses a coding style that is much too difficult (at least for us) to follow.

2) So, we performed a few additional test to see if we could determine at least the possibility of relying on the OS/GtkClipboard. During these tests we also "kept an eye" on the Windows clipboard via clipbrd.exe (the Windows clipboard viewer (WCV)):

a) As before, when CCP from Excel to OO (and both apps are simultaneous running), everything is passed.

Though, WCV only shows the display value, not the formula or any other property of the cell (e.g. colours, etc)[Note: WCV can pass some colours etc if an image format is used, but not the explicit Cell "contents/formulas/properties" etc]. This is a bit worrying for Gtk, as it implies the passing of the formula etc is not actually via the (basic OS) clipboard.

b) When CCP in reverse, from OO to Excel (with both apps running), it does NOT work, only the display value is passed (no formula's, properties, etc).

Again, a bit worrying with respect to (wrt) GtkClipboard possibilities.

c) When the Copy is performed from Excel, but before OO is launched, and then Excel is closed, and the Paste after OO launched, it does NOT work for formula's etc, only the display value.

Again, a bit worrying wrt GtkClipboard possibilities.

d) Skipping some details to avoid diluting this point, our sense is that the (only) "successful" CCP (from Excel to OO while both are running) is somehow performed with some combination of OO accessing the OfficeClipoard (c.f. the Win clipboard, which Gtk has access to) and/or using some ODBC/OLE or whatever direct internal connection to Excel to effect the passing of the full Cell contents. That the CCP does not work in reverse (i.e. OO -> Excel), or only when both are running, also supports this contention.

Although these tests/results do not in themselves decisively prove that the OS/GtkClipboard is not actually usable for CCP of "full" Cell/Range contents, they are highly suggestive of that. However, for all we know, there may be some GtkClipboard (GdkAtom etc) settings that might permit access to other (e.g. Office) clipboards, but we can't find any info on this.

Similarly, it would be useful to know if anybody has succeeded in CCP'ing these sort of things in a Unix/Linux environment with Gtk (we are testing via a couple different MingW setups on Win, but the Win and Office clipboards seem to have some difference under Gtk compared to under Linux).

... as such, we would be grateful to anybody with actual knowledge on the subject at least to confirm whether or not the Win/GtkClipboard strategy is a waste of time, and if not, any hints for implementation would be much appreciated.

Community
  • 1
  • 1
DrOli
  • 1,065
  • 1
  • 12
  • 13

1 Answers1

1

OK, sussed it, found (an) answer to our own question:

It is possible to CCP Cells/Ranges from Excel to a GTK entity via GtkClipoard.

... having said so, the Gtk side documentation is almost non-existent, and essentially requires a huge amount of trial and error.

... to confuse matters exponentially, the documentation/discussions regarding the Win OS clipboard, and the actual mechanism Excel uses (which is actually via attaching items to the OS clipboard via various machinations of the OfficeClipboard, OLEClipboard, iDatatObjects, etc.) is, indeed, overwhelmingly documented and creates a hugely expensive distraction.

The entire key to the matter does lie in the Gtk/Gdk "Atoms", "Targets" etc. as speculated/enquired in the OP.

One solution (with CAVEATS):

1) Use either gtk_clipboard_request_targets() or gtk_clipboard_request_contents() to obtain a list of the "Targets" that the "owner" of CCP'd material on the clipboard can support (i.e. convert into). gtk_clipboard_request_contents() can do this is it is given gdk_atom_intern("TARGETS") as its "target".

This process can be used to produce the list of targets from which to choose to convert into. For example, in a test case copying an Excel Cell with a formula, colours, grid etc, the "available targets" lists that the "owner" (e.g. Excel) will support was:

"DataObject"
"Biff8"
"Biff5"
"Biff4"
"Biff3"
"Biff"
"Wk1"
"XML Spreadsheet"
"HTML Format"
"UTF8_STRING"
"Csv"
"Rich Text Format"
"Embed Source"
"Native"
"OwnerLink"
"Object Descriptor"
"Link Source"
"Link Source Descriptor" "Link"
"ObjectLink"
"Ole Private Data"
"image/bmp"

CAVEAT: we tested a number of these "targets" for conversion inside Gtk following CCP from Excel, but some of them produced "empty/faulty" 'GtkSelectionData'.

Some worked to various extents, depending on what "you" actually want. For example, the Target "UTF8_STRING" brought just the "display text/value" (e.g. of the Excel-side formula was "=1+2", which displayed as "3", then this Target provides Gtk with "3".

To demonstrate that the "full splendour" of the Cell "object" can be brought into Gtk, the Target "XML Spreadsheet" was used to CCP a Cell with formula, colours, etc from Excel, into Gtk, and inside Gtk it produced a variable (Data_Alloc) with the "value" of:

GtkClipboardContntReceivedFuncX Data_Alloc = <?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
    <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
           <Alignment ss:Vertical="Bottom"/>
           <Borders/>
           <Font/>
           <Interior/>
           <NumberFormat/>
           <Protection/>
       </Style>
      <Style ss:ID="s25">
        <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
       <Font ss:FontName="Bauhaus 93" x:Family="Decorative" ss:Size="12"/>
       <Interior ss:Color="#CCFFFF" ss:Pattern="Solid"/>
      </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
      <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1">
       <Row ss:Height="18.75">
        <Cell ss:StyleID="s25" ss:Formula="=1+2"><Data ss:Type="Number">3</Data></Cell>
       </Row>
     </Table>
 </Worksheet>
</Workbook>
 << GtkClipboardContntReceivedFuncX - Exit

Clearly, all the crucial information was passed into Gtk, and then its just a matter of parsing the "data" to whatever is required in the Gtk app. For example, to CCP the Excel formula to Gtk (instead of the "value"), just parse the 6th line from the bottom where it has Formula = "=1+2"

... whoo hoo :)

Unfortunately, the "XML Spreadsheet" may not be sufficiently robust for all cases (e.g. it seems to have difficulty CPP'ing anything with Cell references e.g. "= 1+B1" gives it trouble (see ** note below), and which may be Gtk version, Excel version, etc issues). So one or another of the other "targets" may be required, and some of which are rather complex (e.g. any of the BIFF's).

** Note: Some of the Targets/conversion actually create an entire spreadsheet based on the Cell/Range that was CCP'd. Thus, in the case of sending a single Cell with a formula, and using the "XML Spreadsheet" Target/conversion in Gtk, the "result" is an "entire single cell spreadsheet" (as can be seen in the XML example above). Since many of these conversions convert "A1" style cell referencing to "(relative) RC" referencing, any dependencies that lie "outside" of the Range/Cell that was CCP'd and converted into an "entire clipboard (XML) spreadsheet" would not include the "outside cells", and then the RC referencing is referencing "nothing", so it cannot convert the formula correctly/at all.

... again, there is much going on here, and much depends on OS, Excel, Gtk etc versions and protocols.

DrOli
  • 1,065
  • 1
  • 12
  • 13