comsample.fsx

#if INTERACTIVE
#r "office.dll"
#r "Microsoft.Office.Interop.Excel.dll"
#endif

open System
open System.IO
open System.Runtime.InteropServices
open Microsoft.Office.Interop.Excel

/// IDisposableにラップするためのクラス
type Disposable<'a>(x:'a, f) =
    member this.Value = x

    interface IDisposable with
        member this.Dispose() =
            f(x)

    static member Create(x, f) = new Disposable<'a>(x, f)

/// comオブジェクトを開放する
let release comObject =
    Marshal.ReleaseComObject(comObject) |> ignore

/// Disposeできるcomオブジェクトを作る
let comdisposable x =
    Disposable.Create(x, release)

let withFinally c f a =
    let o = c()
    try
        a o
    finally
        f o

let withApplication a =
    withFinally (fun () -> new ApplicationClass())
                (fun app ->
                    app.Quit()
                    release app)
                a

let withWorkbook (name:string) (app:ApplicationClass) =
    withFinally (fun () -> app.Workbooks.Open(name))
                (fun workbook ->
                    workbook.Close(false)
                    release workbook)

let withWorksheet (index:int) a (book:Workbook) =
    withFinally (fun () ->
                    use sheets = comdisposable book.Worksheets
                    sheets.Value.Item(index) :?> Worksheet)
                release
                a

let getText (range:Range) row col =
    use cell = range.Item(row, col) :?> Range |> comdisposable
    cell.Value.Text :?> string

/// Rangeを取得する
let getRange (sheet:Worksheet) startRow startCol endRow endCol =
    use startcell = sheet.Cells.Item(startRow, startCol) |> comdisposable
    use endcell = sheet.Cells.Item(endRow, endCol) |> comdisposable
    sheet.Range(startcell.Value, endcell.Value)

/// 一個のセル(Range)を取得する
let getCell (sheet:Worksheet) row col =
    sheet.Cells.Item(row, col) :?> Range

/// 一個のセル(Range)を取得する
let getCellFromRange (range:Range) row col =
    range.Item(row, col) :?> Range

/// 縦に連続してデータが入っている最後の行番号を取得する
let lastRow (range:Range) =
    use x = range.End(XlDirection.xlDown) |> comdisposable
    x.Value.Row

let maybeDate (obj:obj) =
    match obj with
    | :? float as date -> Some(DateTime.FromOADate(date))
    | _  -> None


/// CSVカラム定義とデータをうけとって行のシーケンスを返す
let toCsvLines columns data : seq<string> =
    seq {
        for datum in data do
        let line =
            columns
            |> Seq.map (fun (_, c) -> c datum)
        yield String.Join(", ", line)
    }