How do I get OpenXML onto the clipboard so that it will paste into Excel?

Question

I am generating OpenXml using the DocumentFormat.OpenXML library from Microsoft. I am tring to figure out how to get this document into my clipboard so that I can paste my data into Excel (as if it were copied from Excel). I am able to see OpenXml formated data coming out of Excel, when I copy from within Excel. I need to do the reverse, copy out of a WPF application, and paste into Excel using advanced Excel formatting (hence needing OpenXML).

Here is a snippet of what I have so far:

MemoryStream documentStream = new MemoryStream();
  SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(documentStream, SpreadsheetDocumentType.Workbook, true);

  // create the workbook
  spreadsheet.AddWorkbookPart();
  Stream workbookStream = spreadsheet.WorkbookPart.GetStream();
  spreadsheet.WorkbookPart.Workbook = new Workbook();     // create the worksheet
  spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
  spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();

  ...

  const string SPREADSHEET_FORMAT = "XML Spreadsheet";
  Clipboard.SetData(SPREADSHEET_FORMAT, clipboardData);

score 2 · Answer 1 · answered Jun 22 '11 at 07:22

I've constructed XML manually (not through OpenXML), that has been accepted by excel.

Some interesting details about the header:

Notice the second line.

Full XML I'm sending (for testing):

<?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/>
            <Interior/>
            <NumberFormat/>
            <Protection/>
        </Style>
        <Style ss:ID="s21">
            <NumberFormat ss:Format="Currency"/>
        </Style>
        <Style ss:ID="s22">
            <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/>
        </Style>
    </Styles>
    <Worksheet ss:Name="Sheet2">
        <Table ss:ExpandedColumnCount="256" ss:ExpandedRowCount="65536"
         x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="13.2">
            <Row>
                <Cell>
                    <Data ss:Type="String">test</Data>
                </Cell>
                <Cell ss:StyleID="s21">
                    <Data ss:Type="Number">1.12</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">1.1213</Data>
                </Cell>
                <Cell ss:StyleID="s21">
                    <Data ss:Type="Number">12.12121</Data>
                </Cell>
                <Cell ss:StyleID="s22">
                    <Data ss:Type="String">test</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>

Code to send it to clipboard (VB.Net):

Dim xml As String = File.ReadAllText("excel.xml")

Dim xmlStream As Stream = New MemoryStream()
xmlStream.Write(System.Text.ASCIIEncoding.ASCII.GetBytes(xml), 0, xml.Length)
Clipboard.SetData("XML Spreadsheet", xmlStream)

This will work, but that XML is not compatible with the OpenXML SDK (it is the 2003 format, not the OpenXML flat package format). — BrainSlugs83, Jun 13 '13 at 03:14

score 1 · Answer 2 · answered May 25 '11 at 03:00

1

You can't. I've been through this with Microsoft and Office will not accept an OpenXML format in the clipboard or drag/drop from any app other than Office. (They also claim Office itself does not use this format but ClipSpy shows that they do - in the flat package format.)

HTML is your best bet.

answered May 25 '11 at 03:00

David Thielen

28,723
34
119
193

The format shown in ClipSpy *is* XML but it is, however, NOT the OpenXML flat package format as you claim -- If you look closer, you'll notice it's actually the 2003 XML format and not the 2007 (and later) OpenXML flat package format. The two formats are not interchangeable. – BrainSlugs83 Jun 13 '13 at 03:11
@BrainSlugs83 The SpreadsheetML format is also used. However SpreadsheetML does not support images and you can copy/paste images between worksheets. So they are also offering XLSX somehow. – David Thielen Jun 13 '13 at 14:13
There are other formats provided by the clipboard object that support images -- none of those formats are the OpenXML flat package, nor are they an OpenXML binary package (zip file). There are a few Biff formats though -- one of which is "Biff12" which contains an xlsb (a non OpenXML zip file; it can be opened with the packaging API, but it contains binary files and not XML). I've not seen ANY OpenXML formats in the clipboard. – BrainSlugs83 Jun 14 '13 at 00:15

How do I get OpenXML onto the clipboard so that it will paste into Excel?

2 Answers2

Linked