使用 Tcl 呼叫 LibreOffice Basic macro 執行

#!c:/tcl/bin/tclsh86.exe
package require http
package require vfs::zip

########################################################################
# Now download file from network
########################################################################
set day [clock format [clock seconds] -format {%d}]
set now [clock seconds]

if {$day > 7} {
 set value [clock format [clock add $now -30 day] -format {%Y%m}]
} else {
 set value [clock format [clock add $now -60 day] -format {%Y%m}]
}

set remoteFile "http://www.twse.com.tw/ch/statistics/download/04/001/"
append remoteFile $value
append remoteFile "_C04001.zip"

puts "======================================================"
puts "URL: $remoteFile"

set localFile $value
append localFile "_C04001.zip"

puts "Download File name: $localFile"
puts "======================================================"

set token [::http::geturl $remoteFile -binary 1]
set data [::http::data $token]

set channel [open $localFile w+]
fconfigure $channel -encoding binary -translation binary
puts -nonewline $channel $data
close $channel

::http::cleanup $token

########################################################################
# Now handle zip file
########################################################################
set mnt_file [vfs::zip::Mount $localFile $localFile]
file copy -force [glob $localFile/*] ./
vfs::zip::Unmount $mnt_file $localFile
file delete $localFile

set DIR [pwd]
set DOCS [glob *.xls]

foreach file $DOCS {
    set inputFile $DIR
    append inputFile "/" $file
    set exeList [list "C:\\Program Files\\LibreOffice 4.0\\program\\soffice.exe" "macro:///Standard.Module1.AutoFindGoodMan($inputFile)"]
    exec {*}$exeList
}

exit

這只是備份。


而 LibreOffice 使用的 macro：

REM ***** BASIC *****

Option Explicit

Sub AutoFindGoodMan(cFile)
Dim Doc As Object
Dim Dummy()

Dim Sheet As Object
Dim Cell As Object
Dim Cell2 As Object
Dim Cell3 As Object
Dim Cell4 As Object
Dim Cell5 As Object
Dim Count As Integer

Doc = StarDesktop.loadComponentFromURL(ConvertToUrl(cFile), "_default", 0, Dummy())

Sheet = Doc.Sheets(0)

dim document   as object
dim dispatcher as object
rem ---------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Zoom.Value"
args1(0).Value = 100
args1(1).Name = "Zoom.ValueSet"
args1(1).Value = 28703
args1(2).Name = "Zoom.Type"
args1(2).Value = 0
dispatcher.executeDispatch(document, ".uno:Zoom", "", 0, args1())

Dim CellRange As Object
CellRange = Sheet.getCellRangeByName("A66:J845")
CellRange.CellBackColor = RGB(255, 255, 255)

For Count = 66 To 950 Step 1
Cell = Sheet.getCellByPosition(0, Count)
IF Cell.Value > 1000 Then
    Cell.CellBackColor = RGB(0, 0, 255)
    Cell2  = Sheet.getCellByPosition(3, Count)
    Cell3  = Sheet.getCellByPosition(5, Count)
    Cell4  = Sheet.getCellByPosition(7, Count)
    Cell5  = Sheet.getCellByPosition(9, Count)

    IF Cell3.Type = com.sun.star.table.CellContentType.EMPTY Then
        IF Cell4.Value = 0.00 Then
            Cell3.CellBackColor = RGB(255, 0, 0)
            Cell4.CellBackColor = RGB(255, 0, 0)
        ENDIF
    ELSE
        IF Cell3.Value < 10 Then
            IF Cell2.Value <= 15 Then
                Cell2.CellBackColor = RGB(255, 255, 0)
            ELSEIF Cell2.Value > 15 AND Cell2.Value <= 40 THEN
                Cell2.CellBackColor = RGB(0, 255, 0)
            ELSEIF Cell2.Value > 40 THEN
                Cell2.CellBackColor = RGB(0, 255, 255)
            END IF                       
   
            Cell3.CellBackColor = RGB(0, 255, 0)
            
            IF Cell4.Value > 7 THEN
                   Cell4.CellBackColor = RGB(0, 255, 0)
            END IF
        ELSEIF Cell3.Value >= 75 Then
            Cell3.CellBackColor = RGB(0, 255, 255)
        End IF
      End IF  
      
      IF  Cell5.Value <= 2.0 Then
          Cell5.CellBackColor = RGB(0, 255, 255)
      END IF 
End IF
Next Count

If (Doc.isModified) Then
If (Doc.hasLocation And (Not Doc.isReadOnly)) Then
    Doc.store()
End If
End If


Doc.close(True)

End Sub

從證交所下載資料，解壓縮，再使用巨集來標出顏色。新版的 LibreOffice 會有警告，但是不影響使用。