Openoffice Calc と gawk のマクロによる連動に関する備忘録

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

Sub Main
rem ----- define variables
dim document, oSheet, oCell, dispatcher as object
dim pathname, savename1, savename2, id_number as string

rem ----- get access to the document
document = ThisComponent.CurrentController.Frame
oSheet   = ThisComponent.getCurrentController().getActiveSheet()

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----- $A$1にカーソルを合わせる
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----- 1行挿入
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())

rem ----- 保存用IDを入力
id_number = InputBox("IDを入力")
rem MsgBox id_number

rem ----- $A$1にIDを文字として表示
oCell        = oSheet.getCellByPosition(0, 0)
oCell.string = id_number

rem ----- call subroutine

OutCsv(id_number)
CalcCsv(id_number)
InCsv(id_number)

End Sub




Sub OutCsv(x)
rem ----- rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----- 一時保存用フォルダとファイル名の指定
pathname  ="file:///c:/temp/"
id_number = x
savename1 = id_number+".csv"

rem ----- CSVファイルの出力
dim args2(3) as new com.sun.star.beans.PropertyValue
args2(0).Name = "FilterName"
args2(0).Value = "Text - txt - csv (StarCalc)"
args2(1).Name = "FilterOptions"
args2(1).Value = "44,34,60,1"
args2(2).Name = "SelectionOnly"
args2(2).Value = true
args2(3).Name = "URL"
args2(3).Value = pathname+savename1

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args2())

End Sub




Sub CalcCsv(x)
rem -----
id_number = x

pathname  ="file:///c:/temp/"
savename1 = id_number+".csv"
savename2 = id_number+"O.csv"		rem 処理済ファイル名

rem ----- 実行ファイルに引数を渡す
shell(pathname+"awk.bat "+savename1)

rem ----------------------------------------------------------------------

Do While NOT FileExists(pathname+savename2)
Wait 500
Loop

End Sub




Sub InCsv(x)
rem ----- get access to the document
document = ThisComponent.CurrentController.Frame

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

id_number = x
pathname   ="file:///c:/temp/"
savename2 = id_number+"O.csv"

rem ----- 新しい表に読込む方法が分からないので，新しいウィンドウを開く
dispatcher.executeDispatch(document, ".uno:NewWindow", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "FilterName"
args3(0).Value = "Text - txt - csv (StarCalc)"
args3(1).Name = "FilterOptions"
args3(1).Value = "44,34,60,1,2/2/2/1"
args3(2).Name = "URL"
args3(2).Value = pathname+savename2

dispatcher.executeDispatch(document, ".uno:Open", "", 0, args3())

End Sub

1. マクロ解説ページ　　http://life.ess.sci.osaka-u.ac.jp/katsura/software/logplot/macro.html　　（2008.9.12）
2. OOoBasic/Calc/CSV　　http://hermione.s41.xrea.com/pukiwiki/pukiwiki.php?OOoBasic%2FCalc%2FCSV　　（2008.9.12）