Java - Retrieve XML (Excel) Spreadsheet from clipboard

user_5841827 0
Viewed 341

I was wondering whether it's possible to get the raw XML spreadsheet from the windows clipboard. Since when looking into the clipboard with a clipboard viewer (https://www.freeclipboardviewer.com/windowsclipboard/), XML Spreadsheet is one of the listed flavors. However, when looking into the available Dataflavors via the Java Clipboard class, all I can find is text/plain and text/html with different encodings and streams/buffers/strings/...

Therefore using the custom flavor new DataFlavor("text/xml", "XML Spreadsheet"); didn't work as I expected. While using the HTML would be an option, I'd prefer having the spreadsheet, as it contains some additional information.

UPDATE

I've found out that this seems to be possible via the Java FX Clipboard class, however, since onward Java 9? FX isn't bundled anymore this would be quite bad and it would also kind of suck to have the whole FX stack inititalized just to be able to properly access the clipboard.

swing clipboard excel xml java
1 Answers

There is no AWT or Swing way to get the Excel-XML (or binary) data. Also see this JDK Enhancement-Ticket from the year 2013.

You could use JavaFX, SWT or the "Windows-Only" native Win32 API via JNA. The Clipboard-Documentation from Microsoft helped me a lot:

import java.awt.Toolkit;
import java.awt.datatransfer.Clipboard;
import java.awt.datatransfer.DataFlavor;

import com.sun.jna.Native;
import com.sun.jna.platform.win32.Kernel32;
import com.sun.jna.platform.win32.WinDef.BOOL;
import com.sun.jna.platform.win32.WinDef.DWORD;
import com.sun.jna.platform.win32.WinDef.HWND;
import com.sun.jna.platform.win32.WinDef.UINT;
import com.sun.jna.platform.win32.WinNT.HANDLE;
import com.sun.jna.win32.StdCallLibrary;

/**
 * Shows the Excel-XML-clipboard content on Windows. Be sure to select some
 * cells in Excel and copy them with "Ctrl+C".
 * 
 * @author bobndrew
 */
public class WindowsSystemClipboardAccess {

    public interface User32 extends StdCallLibrary {
        User32 INSTANCE = (User32) Native.loadLibrary("user32", User32.class);
        int CountClipboardFormats();
        UINT RegisterClipboardFormatA(String lpszFormat);
        BOOL OpenClipboard(HWND hWndNewOwner);
        BOOL CloseClipboard();
        HANDLE GetClipboardData(UINT uFormat);
        DWORD GetLastError();
    }

    public static void main(String args[]) throws Exception {
        Clipboard clipboard = Toolkit.getDefaultToolkit().getSystemClipboard();
        System.out.println("There are " + clipboard.getAvailableDataFlavors().length
                + " data flavors, but all are leading to a plainText- or htmlText-representation of the clipboard content:");
        for (DataFlavor flavor : clipboard.getAvailableDataFlavors()) {
            System.out.println(flavor.getHumanPresentableName() + "\t" + flavor);
        }

        System.out.println("\n\nSo we are using JNA to call the windows-API. The number of Windows-ClipboardFormats is "
            + User32.INSTANCE.CountClipboardFormats() + ".");

        UINT formatNumber = User32.INSTANCE.RegisterClipboardFormatA("XML Spreadsheet");
        System.out.println(formatNumber);
        System.out.println(User32.INSTANCE.OpenClipboard(null));
        HANDLE xmlData = User32.INSTANCE.GetClipboardData(formatNumber);
        System.out.println(xmlData);
        System.out.println(Kernel32.INSTANCE.GetLastError());
        System.out.println(Native.getLastError());
        System.out.println(User32.INSTANCE.CloseClipboard());

        System.out.println("\n" + xmlData.getPointer().getString(0));
    }
}

The last line oft this SSCCE example produces xml like this:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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 ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Tabelle1">
  <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="4"
   ss:DefaultColumnWidth="62.400000000000006" ss:DefaultRowHeight="14.4">
   <Row>
    <Cell ss:Index="2"><Data ss:Type="Number">1.2</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="String">A String with 1 Number</Data></Cell>
    <Cell ss:Formula="=R[-1]C+R[-1]C[-1]"><Data ss:Type="Number">124.2</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

If you want to co grazy there's also some "change-messaging" possible, registering your application-window in the Windows "Clipboard Viewer Chain".

user_3612270
Related