jeforth adds super power to excel
HTA 是針對 Windows 族系提供十分類似 Node-Webkit 解除重重限制的應用。jeforth 的 HTA 版沿用 Microsoft 所提供的眾多功能在 Windows 上最實用。先前介紹過可以跑網路上的 VBscript / JScript / JavaScript 範例、操控 IE 網頁、餵 keyboard / mouse 給別的程式等等。今天所要介紹的是針對試算表程式 Excel 的應用。
這個例子在 excel 上，沿著 C column 事先填妥的每個序號 1, 2, 3, . . . 右邊填上一個亂數。表演 jeforth.hta 重複做一件事的實例。
Fig-1, C column 事先填妥的一行序號，excel 目前 focus 在第一個序號 1 的右邊，如下圖：
在 jeforth.hta 上 copy-paste 執行以下這段程式，
\ Random numbers
<?stop random 100 * int cell! down 10 nap rewind
Fig2, 結果變這樣，注意 focus 已經跑到最下面處。
實際操作請看影片講解。以前剛剛寫出 rewind 命令時也介紹過它。
Fig3, 這個工作上遇到的實際例子中，拿到手的 excel 表一看差點昏倒，因為其中有一欄中兩個資料被呈現在同一格 cell 裡，例如下圖 Fig3 裡 94/100.0 其實是 94 與 100.0 兩個資料。如何把它拆成兩 column？
Fig3, 原始資料一格裡放了兩個數字，怎麼把它們拆開?
說不定 excel 也有這種處裡文字的能力? 總之我不知道。然而我很清楚 JavaScript 的 String 都有 match() 方法，一行搞定。因此只要用 jeforth.hta 跑下面這段程式就可以解決問題：
\ 拆開 Days/U.Rate 緊接其右分置兩欄。
0 value data
cut @?stop cell@ <js> pop().match(/^\s*(.*?)\s*\/\s*(.*?)\s*$/) </jsV> to data
1 0 offset :: value=g.data[1];
2 0 offset :: value=g.data[2];
down 10 nap rewind
Fig4, 上面程式完成任務。 Focused cell 移動到了原始 column 的最下方。
進影片講解看實際操作。
證券公司都會提供【看盤軟體】，這些軟體可能有介面可以讓 user 玩 automation。富邦證券提供的看盤軟體是【富邦 e01】有 support "DDE" 讓 user 從 excel 裡經由【富邦 e01】獲取想要的資料。方法很簡單，以查股票公司名稱為例，在 excel 隨便一格填寫如下 formula :
=XQFAP|Quote!'9943.TW-Name'
這一格經過 recalculate 就會呈現台股代號 9943 的公司名稱「好樂迪」。我們不必多麼了解這個 DDE formula 只要細審其中有 "9943" 是「股票代號」，"Name" 是「公司名稱」就夠了。問題是我辛苦嘗試把該 formula 裡的這兩項資訊用變數取代至今不得其法。這類人造的科學沒有追根究柢的必要，搞不定就祭出 jeforth 進去暴力填寫即可。
Fig5, 先布置好 excel 準備讓它去查出指定的股票代碼的現價。為了避免發生誤認昨天價錢的慘劇，日期時間也列上。
上圖中 column K, L, M, N 最上面一列精心布置為 formula 字串以供 jeforth 程式參考引用。圖中看不清楚，詳列如下：
公司名稱 =XQFAP|Quote!'_id_.TW-Name'
成交日期 =XQFAP|Quote!'_id_.TW-TradingDate'
成交時間 =XQFAP|Quote!'_id_.TW-Time'
成交價格 =XQFAP|Quote!'_id_.TW-Price'
其中 ＿id＿ 處是 place holder 準備要被置換成股票代碼。copy-pate 執行以下 jeforth 程式：
\ DDE formula automation
0 value id 0 value targetCell
manual
char j5 jump cut @?stop
cell@ <js> pop().toString().replace(/\s/g,'') </jsV> to id
1 0 offset to targetCell
targetCell :> column 1 cell :> value ( formula(raw) ) :> replace(/_id_/,g.id) ( formula(cooked) )
targetCell :: formula=pop()
down 10 nap rewind
Fig6, 執行結果在某一瞬間可能看起來像這樣：
Fig7, 經過 recalculate 完成後就像這樣：
所以這段 jeforth 程式的工作就是從頂上取得 DDE formula template 從左邊取得股票代號，組合成當格的有效 formula。而且一批只做一個 column。重複跑幾次該 jeforth 程式，每次要手動改一個數字：
1 0 offset to targetCell
如上式中的 1 0 offset 要依序改成 2 0 offset, 3 0 offset, 4 0 offset 跑四次就可以完成整個表。
Fig8, 下圖中改成 2 0 offset 準備執行已完成第二條 column
Fig9, 第二條 column 完成的結果。
Fig10, 四條 column 都跑完經徹底 recalculate 的結果。
到影片講解看實際操作。
Copy-Paste data 進 Excel 有時候很挫折。有時候 excel 會正確地自動分欄，這時候是 100分。有時候它不自動分欄，咱手動用 excel 的 "Text to columns" 來完成分欄動作亦可。最令人哭笑不得者，他堅持要自動分欄，卻篤定地分錯了！真不知道該怎麼辦？
當 Excel 堅持要自動分欄，卻做不好時，下面這段程式讀進 text file 把它一行一行地從上往下填進 excel 格子裡，讓它徹底無法自動分欄，以便隨後咱自己手動去分。
<text>
11 22 33
44 55 66
77 88 99
</text> lines-to-column
您可以 see lines-to-column 去看這個 word 的定義：
: lines-to-column ( "text" -- ) \ Write text to Excel but avoid stupid text-to-columns
:> split('\n') ( a ) dup :> length
?dup if dup for dup r@ - ( a COUNT i )
js> tos(2)[pop()] cell! down
( a COUNT ) next 2drop then
;
它只是抓 text 一行一行寫進 excel 的格子而已。
Written with StackEdit.