Pasting from formatted Excel cells into other applications loses underlying precision

[mcnarict]

Hello all,

I am writing a VB.net Windows forms application that needs to paste data captured in the Windows clipboard from an Excel 2010 worksheet into a datagridview. I thought it was working fine until I noticed that if an Excel cell's underlying value is 0.987654321 and it has been formatted to only display 3 decimal points (0.988), the value pasted into the grid is 0.988.

That seems sensible, however, there are some clever applications in the market that manage to extract the original, full precision data, from the clipboard. Does anyone know how they manage to achieve this?

Many thanks,
Richard

 

[shg]

How about just formatting to display in full precision (0.00000000000000E+00) in the workbook before copying?

 

[mcnarict]

Thanks for the reply shg. That would work, but the trouble is that the end users would all need to remember to do it - and there's only a slim chance of that happening.

 

[shg]

That seems sensible, however, there are some clever applications in the market that manage to extract the original, full precision data, from the clipboard.

I don't know the internals of the Office (not Excel) clipboard, but strongly believe it contains only text, so there is no "extracting" to be done.

If the data is being copied via Ctrl+C, then what you get is what you get. If it's being copied by code, you can do whatever you want.

 

[mcnarict]

I thought that too, but when I read about the other applications achieving it, I started to wonder if there was more to it than meets the eye.

 

[mcnarict]

I found a way around the loss of precision when pasting from Excel to my vb.net Windows application. Basically it is to not use the Windows clipboard at all and just simulate the pasting procedure by attaching to the active Excel instance and extracting the selected range. This method should work as long as the Excel instance is running on the user's machine:

 Dim arr(,) As Object
        Try
            Dim xlApp As Excel.Application = DirectCast(System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"), Excel.Application)
            Dim r As Excel.Range = CType(xlApp.Selection, Excel.Range)
            If xlApp.Selection Is Nothing OrElse r.Count < 3 OrElse r.Columns.Count <> 3 Then
                MessageBox.Show("Please select the axial stress, radial strain, and axial strain columns from an Excel worksheet.", strAppName & ": Invalid Excel selection", MessageBoxButtons.OK)
                r = Nothing
                xlApp = Nothing
                Exit Sub
            End If
            arr = CType(r.Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
            r = Nothing
            xlApp = Nothing
        Catch ex As Exception
            MessageBox.Show("An error occurred while attempting to read the data selection from the active Excel instance.", strAppName & ": Invalid Excel selection")
            Exit Sub
        End Try