Forum Discussion
Excel Clipboard Format
If this is the wrong place for this question, please point me to the correct place.
If you copy data from Excel, and then paste it into another application, you will generally get numbers formatted in the same manner they appeared in Excel. For for example, if you have a cell with the value "1.025368" that is formatted to 3 decimal places (1.025), and you copy and paste this to another application, then "1.025" is what will be pasted.
If you are writing a program that wants to read the full number from the system Clipboard, there are multiple formats that the clipboard can hold. The normal format is "Text" -- where each cell is separated by a tab, and each row by a newline character. But when you copy from Excel, it also inserts an "XML Spreadsheet" format of data that looks like this when you retrieve it:
<ss: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">
<ss:Styles>
<ss:Style ss:ID="Default" ss:Name="Normal">
<ss:Alignment ss:Vertical="Bottom" />
<ss:Borders />
<ss:Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#000000" />
<ss:Interior />
<ss:NumberFormat />
<ss:Protection />
</ss:Style>
<ss:Style ss:ID="s63">
<ss:NumberFormat ss:Format="0.0000" />
</ss:Style>
<ss:Style ss:ID="s64">
<ss:NumberFormat ss:Format="0.000" />
</ss:Style>
</ss:Styles>
<ss:Worksheet ss:Name="Sheet1">
<ss:Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="5">
<ss:Row>
<ss:Cell ss:Index="2" ss:StyleID="s64">
<ss:Data ss:Type="Number">1.0253680000000001</ss:Data>
</ss:Cell>
</ss:Row>
<ss:Row ss:Index="4">
<ss:Cell ss:Index="3" ss:StyleID="s63">
<ss:Data ss:Type="Number">15.236548900000001</ss:Data>
</ss:Cell>
</ss:Row>
</ss:Table>
</ss:Worksheet>
</ss:Workbook>
So far so good...this allows us to pull out the full number. However, the <ss:Row> and <ss:Cell> values are only present for non-blank rows and non-blank cells. You can see that they have an "Index=" attribute that is supposed to tell you what row number and column number these values belong in.
The problem is that this doesn't seem to be true for the very first row. In the above code, I had actually copied a blank row, as shown in this next picture:
There doesn't seem to be a way to know that the first cell value is actually on the second row instead of the first. This seems like a bug with Excel, right? How does one get that addressed?