Then I found out that Excel understands copied HTML pretty well.
That required some DllCalls or GPT magic to put HTML format into clipboard, but still was way faster.
Then I found out that I don't need that special clipboard format, I can use regular text structured like that format.
This seems to work with my olde Excel 2007.
Can anyone confirm if it works in older/newer versions? Screenshot of expected result is attached.
Code: Select all
#Requires AutoHotkey v2.0
ExcelHtmlToClip( htmlText, styleText := '' ) {
htmlTextBefore := '<html><head><style>' . styleText . '</style></head><body><table><!--StartFragment-->`r`n'
htmlTextAfter := '`r`n<!--EndFragment--></table></body></html>'
; Text will be interpreted as UTF-8. Minus one for zero-terminator
strLenBefore := StrPut(htmlTextBefore, 'UTF-8') - 1
strLenUser := StrPut(htmlText , 'UTF-8') - 1
strLenAfter := StrPut(htmlTextAfter, 'UTF-8') - 1
; Offsets of text chunks. First (header) is 105 characters including newlines
ptr1 := 105
ptr2 := ptr1 + strLenBefore
ptr3 := ptr2 + strLenUser
ptr4 := ptr3 + strLenAfter
header := Format(
'Version:1.0`r`n'
. 'StartHTML:{1:010d}`r`n'
. 'EndHTML:{2:010d}`r`n'
. 'StartFragment:{3:010d}`r`n'
. 'EndFragment:{4:010d}`r`n'
, ptr1, ptr2, ptr3, ptr4
)
A_Clipboard := header . htmlTextBefore . htmlText . htmlTextAfter
}
defaultXlStyles := '
(
br { mso-data-placement: same-cell; }
td {
font-size: 11.0pt;
font-family: Calibri, sans-serif;
mso-number-format: '"\@"';
white-space:nowrap;
}
)'
myStyles := defaultXlStyles . '
(
.red {font-weight: bold; color: red; font-style: italic;}
.redcell {color: red; background: #FFF0F0; border-bottom: 2pt solid #FF0000;}
)'
myHtml := '
(
<tr><td colspan=2>Γεια <font class=red>σου κόσμο</font>!</td></tr>
<tr><td class=redcell style="white-space:normal">Red<br/>cell</td></tr>
)'
Loop 100 {
r := 255 - Random(0, 32)
g := 255 - Random(0, 32)
b := 255 - Random(0, 32)
myHtml .= Format('<tr><td style="background-color:#{1:06x}">', (r<<16)|(g<<8)|(b<<0))
. "It'sa me, line <b>#" . A_Index . "</b></td></tr>"
}
ExcelHtmlToClip(myHtml, myStyles)
Msgbox('Now try pasting that in Excel!')