Tuesday, September 6, 2022

Clipboard data for Excel

 I recently wrote a new app for Windows, the Simple Wi-Fi Analyzer. It will scan for nearby Wi-Fi hotspots and present you will lots of information about each one. But that's not what this post is about. This post is about how to put text on the clipboard that Excel can read.

You might think that CSV is the answer. It's not; Excel will treat it badly. An Excel expert will know to do a Paste Special and then laboriously tell Excel all about the delimiters, but that's  terrible solution.

The right solution is to use HTML. You can put text onto the clipboard as text, but encoded as HTML. In my new app, the HTML on the clipboard looks like this:

<html>

<body>

<table><tr><td>WiFiSsid</td><td>Bssid</td><td>BeaconInterval</td><td>Frequency</td><td>IsWiFiDirect</td><td>NetworkKind</td><td>Rssi</td><td>PhyKind</td><td>SignalBars</td><td>Uptime</td><td>AuthenticationType</td><td>EncryptionType</td></tr>

<tr><td>APName</td><td>1g:x2:9a:86:02</td><td>0.1024</td><td>5.745</td><td>False</td><td>Infrastructure</td><td>-72</td><td>Vht</td><td>4</td><td>21.13:42:21.4404040</td><td>RsnaPsk</td><td>Ccmp</td></tr>

</table>

</body>

</html>

And shazam! it pastes perfectly! Except that Excel seems to think that setting the column width is beneath its dignity, but whatever.


BTW: the <body> isn't needed. And you'll need to encode the strings with System.Net.Webutility.HtmlEncode?view=net-7.0. That method will escape all of the HTML characters (like "<") into their HTML-safe version (&LT;)