Excel copy and paste

Question

I've built a web app which has a paste button for populating a table. The data is originally copied from an Excel spreadsheet and pasted onto my form.

The problem is that I'm only seeing the displayed data, not the underlying values. For example, if the cell's value is 12.223 and the cell's format is only showing 12.2, 12.2 goes on to the clipboard.

Am I missing a trick here? Does anyone know how to pull the full data from the clipboard?

Edit: It appears that Excel makes 25 different formats available on the clipboard, including "XML Spreadsheet" which looks like it contains the actual information I need. However, it appears that only the Text version is available inside the browser. Is there an ActiveX control or something similar that I can use to grab this data?

Answer 1

The only way I can see to do it is to knock up some VBA code that makes use of the Windows Forms Object Library.

I have found a code example that makes use of the clipboard

http://www.dailydoseofexcel.com/archives/2004/12/02/putting-text-into-the-windows-clipboard/

By default this will just copy the visible text of the cell. To get it to output the actual value you'll need to change

cell.Text

to

cell.Value

Although this will probably mess up any dates if you have them.

EDIT:

Actually the dates seem to copy ok

Answer 2

After you've copied the cell, right-click and choose Paste-Special, then choose the Values option under Paste. This will paste the full value.

Answer 3

Provided that you are happy using an extra sheet to contain the data formatted to copy you could also do the following

Sub CopyPasteSpecialCopy()

    'Clear out temp destination
    Sheets("CopyPasteSheet").Select
    Cells.Select
    Selection.ClearContents

    'Copy data
    Sheets("DataSheet").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy

    'Paste data
    Sheets("CopyPasteSheet").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.Select
    Application.CutCopyMode = False

    'Put it onto the clipboard
    Selection.Copy

End Sub

Although I can't see an option for pasting XML only values.

Again this Macro could be attached to the Ctrl+C key combination.

Answer 4

Given that you can't modify the excel spreadsheet it looks like you are stuck. The issue is that windows clipboard doesn't recognize the browser as being able to accept anything but plain text from the paste. Because of this it pastes it in as a plain text table which basically is just the visual representation of the data that excel presents to you.

If you built an ActiveX component for the page that could except spreadsheet data then the excel paste should work I think. A pure browser javascript implementation won't do what you need though.

Answer 5

Have you tried pasting it into a contenteditable?

<div contenteditable='true'>paste table here</div>

(I'm unable to test this myself, but this should grab the html format instead of the text.)

An alternative is to use a flash or java component to accept the paste event, and get the proper form from the clipboard. I had to do this when I wanted to accept image data from the clipboard.