Include formatting information in windows clipboard copy buffer for pasting into Excel
My application writes tabular data to the windows copy buffer for the user to paste into Excel. This works fine for unformatted tabular data including tab-stops and new lines for cell and row delimeters.
I'd like to include formatting data in the copy buffer: grid lines, background colors, etc. Can I d开发者_如何学Goo this, and if so, where can I find a specification for encoding the formatting data?
Yes, it's possible (obviously, you can copy formatted Excel data from one running instance of Excel to another via the clipboard, so it must be possible!)
Remember that the Windows clipboard can hold things in more than one format at the same time. The format you want for Excel formatted grid stuff is called BIFF, that is, The Binary Interchange File Format. It's the same format as Excel actually stores files in. A reasonable source of BIFF documentation is available from Open Office.
Once you figure out the basics of BIFF, you'll find that the easiest way to generate the BIFF you want is to copy a model of the cells you want from an Excel spreadsheet and examine what's in the clipboard.
The solution is to write a small program that dumps the contents of the clipboard, including all the different formats. Then, copy some Excel into the clipboard, dump the clipboard, and you will see revealed how to format the data.
Here is the output from an app that I wrote, so I can paste a table to rich text apps:
xC0FC:
Version:0.9
StartHTML:00000120
EndHTML:00000686
StartFragment:00000154
EndFragment:00000650
SourceURL:about:blank
<html><body>
<!--StartFragment--><table style="max-width: 50%; background: rgb(255, 255, 255);">
<tbody>
<tr>
<td valign="center" style="background-color: #cccccc;" width="180"><b><font size="-1">Enter in "Name"<br>
</font></b></td>
<td valign="center">XXX YYY</td>
</tr>
<tr>
<td valign="center" bgcolor="#cccccc" width="300"><b><font size="-1">Enter in "Registration Key"<br>
</font></b></td>
<td valign="center">123456</td>
</tr>
</tbody>
</table>
<!--EndFragment-->
</body>
</html>
The Start/End are byte offsets. The "C0FC" text type code seems to vary from day to day according to a pattern I haven't figured out. You'll have to experiment and use trial & error.
Solution #1
Found a solution with the help of this How to copy HTML code to clipboard using Python?
I use excel to create an example of my table. With the table selected :
- Save as
- Save as type: Web Page with selection option
- Publish
Then if you open this with a text editor you can see the html, modify it or generate it with different value with your code and use PutHtml(html_table_str) provided by the_RR 55698762 to put it inside clipboard or make your own code in another language.
You can then paste into excel and it work perfectly.
sample code:
html_table_str = "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 Transitional//EN\">\r\n<HTML>\r\n<HEAD></HEAD>\r\n<body>\r\n<table>\r\n<tr>\r\n<td style='border:.5pt solid black;color:white;font-weight:700;background:#3A3838'>x</td>\r\n<td style='border:.5pt solid black;color:white;font-weight:700;background:#3A3838'>y</td>\r\n</tr>\r\n<tr>\r\n<td style='border:.5pt solid black;'>5</td>\r\n<td style='border:.5pt solid black;color:red'>6</td>\r\n</tr>\r\n</table>\r\n</body>\r\n</html>"
PutHtml(html_table_str)
Solution #2
You can also simply paste html document text directly into excel but i'm not sure if it work with older excel version and unlike the solution #1, doesn't work in ms word or outlook.
sample html :
<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 Transitional//EN\">
<HTML>
<HEAD></HEAD>
<body>
<table>
<tr>
<td style='border:.5pt solid black;color:white;font-weight:700;background:#3A3838'>x</td>
<td style='border:.5pt solid black;color:white;font-weight:700;background:#3A3838'>y</td>
</tr>
<tr>
<td style='border:.5pt solid black;'>5</td>
<td style='border:.5pt solid black;color:red'>6</td>
</tr>
</table>
</body>
</html>
加载中,请稍侯......
精彩评论