C# Add excel text-formatted data to clipboard

Question

In C# I need to copy data-grid rows to excel. Because some values in a row are doubles, "-Infinity" values are possible.

I've tried to copy the rows as DataFormats.UnicodeText or DataFormats.Text but this gave me the output "#NAME?" where I should see "-Infinity" (because excel automatically inserts a "=" before the minus in "-Infinity" due to the standard cell format).

When I format the cells to "Text" before pasting, excel does not automatically insert an "=" before the "-Infinity". By the way, I do not need to do any calculations with the double values in excel, so a text-format would be alright for me.

So my question is how to copy data to clipboard and paste it into excel while setting the cell format to "text".

Answer 1

Starting with a Raw Clipboard viewer you can see that copying

to the clipboard results in Excel throwing a large number of different formats to the clipboard.

Most of these aren't helpful, but some of them are internal to excel, meaning it will (almost) guarantee that the data will be the same as copied. If I were you I'd probably target XML SpreadSheet or if your feeling brave Biff12 which is also xml (but zipped). This will give you far more control over the paste than normal text.

As an example the above clip results in

<?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>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="@"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2"
   ss:DefaultRowHeight="15">
   <Row>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">2</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Test</Data></Cell>
    <Cell ss:StyleID="s63"><Data ss:Type="String" x:Ticked="1">-Infinity</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

So looking a little deeper still... It seem's the .Net Clipboard class does some wierd and not so wonderful things when I tried to use Clipboard.SetData to write the xml to the clipboard

The clipboard starts with a load of chaff. This of course results in Excel rejecting the clipboard contents.

To get around this I use the Windows API (user32) calls to work with the clipboard

    [DllImport("user32.dll", SetLastError = true)]
    static extern uint RegisterClipboardFormat(string lpszFormat);
    [DllImport("user32.dll")]
    static extern IntPtr SetClipboardData(uint uFormat, IntPtr hMem);
    [DllImport("user32.dll", SetLastError = true)]
    static extern bool CloseClipboard();
    [DllImport("user32.dll", SetLastError = true)]
    static extern bool OpenClipboard(IntPtr hWndNewOwner);

    private static void XMLSpreadSheetToClipboard(String S)
    {
        var HGlob = Marshal.StringToHGlobalAnsi(S);
        uint Format = RegisterClipboardFormat("XML SpreadSheet");
        OpenClipboard(IntPtr.Zero);
        SetClipboardData(Format, HGlob);
        CloseClipboard();
        Marshal.FreeHGlobal(HGlob);
    }

Answer 2

Thanks for the info. After some more searching, I found that you can use the .Net clipboard class, but you can't pass a String to SetData(). This works for me:

System::Text::UTF8Encoding^ enc = gcnew System::Text::UTF8Encoding();
System::Windows::Forms::Clipboard::SetData("XML Spreadsheet", gcnew MemoryStream(enc->GetBytes(data)));

Answer 3

The win32 api solution did not work for me. I had had random crashes. Following code works fine, also with unicode chars.

var xml = File.ReadAllText(@"..\..\SampleData\Clipboard-Example3.xml");

var stream = new MemoryStream(Encoding.UTF8.GetBytes(xml));
var dataObject = new DataObject();
dataObject.SetData("XML SpreadSheet", stream);

Clipboard.Clear();
Clipboard.SetDataObject(dataObject);