A F# wrapper class for the Excel4/Excel12 methods contained in Excel-DNA to mimic basic methods of the COM object model

Excel4.fs

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE.

namespace Utility

open System
open System.Collections.Generic
open System.Linq
open ExcelDna.Integration

module XL4 = 
    type ActiveWorkbook() = 
        let workbookName = XlCall.Excel(XlCall.xlfGetWorkbook, 16).ToString()
        let sheets = new Dictionary<string, Sheet>()
        
        do 
            XlCall.Excel(XlCall.xlfGetWorkbook, 1, workbookName) :?> obj[,]
            |> Seq.cast<obj> 
            |> Seq.toArray
            |> Array.map(fun x -> x.ToString().Split(']').LastOrDefault())
            |> Array.iter(fun x -> sheets.Add(x, Sheet(x)))
        
        member this.Name = workbookName
        member this.Sheets = sheets
    
    and Sheet = 
        val private SheetRef : ExcelReference
        new(name : string) = 
            { SheetRef = XlCall.Excel(XlCall.xlSheetId, name) :?> ExcelReference }
        new(id : IntPtr) = { SheetRef = ExcelReference(0, 0, 0, 0, id) }
        member this.FullName = XlCall.Excel(XlCall.xlSheetNm, this.SheetRef)
        member this.Name = this.FullName.ToString().Split(']').LastOrDefault()
        member this.Select() = 
            XlCall.Excel(XlCall.xlcWorkbookSelect, this.FullName) |> ignore
        
        member this.Value 
            with get () = this.SheetRef.GetValue()
            and set (v : obj) = 
                if this.SheetRef.SetValue(v) = false then 
                    invalidArg "v" "could not set value to sheet"
        
        member this.Cells(row, col) = Cells(row, col, this)
        member this.Range(begCell : Cells, endCell : Cells) = 
            Range(begCell, endCell)
        member this.Range(rangeName : string) = Range(rangeName, this)
    
    and Cells = 
        val private CellRef : ExcelReference
        new(row : int, col : int, sheet : Sheet) = 
            { CellRef = 
                  ExcelReference(row - 1, row - 1, col - 1, col - 1, sheet.Name) }
        member this.Row = this.CellRef.RowFirst + 1
        member this.Column = this.CellRef.ColumnFirst + 1
        member this.Parent = Sheet(this.CellRef.SheetId)
        
        member this.Select() = 
            this.Parent.Select()
            XlCall.Excel(XlCall.xlcFormulaGoto, this.CellRef) |> ignore
        
        member this.Offset(offsetRows : int, offsetColumns : int) = 
            Cells
                (this.Row + offsetRows, this.Column + offsetColumns, this.Parent)
        member this.Offset(offsetRows : int, offsetColumns : int, rows : int, 
                           columns : int) = 
            Range
                (this.Offset(offsetRows, offsetColumns), 
                 this.Offset(offsetRows + rows - 1, offsetColumns + columns - 1))
        
        member this.Value 
            with get () = this.CellRef.GetValue()
            and set (v : obj) = 
                if this.CellRef.SetValue(v) = false then 
                    invalidArg "v" "could not set value to cell"
    
    and Range = 
        val private RangeRef : ExcelReference
        
        new(begCell : Cells, endCell : Cells) = 
            { RangeRef = 
                  if begCell.Parent.Name <> endCell.Parent.Name then 
                      invalidArg "endCell" "parent sheet not same as begCell"
                  ExcelReference
                      (begCell.Row - 1, endCell.Row - 1, begCell.Column - 1, 
                       endCell.Column - 1) }
        
        new(rangeName : string) = 
            let rRef = 
                XlCall.Excel(XlCall.xlfEvaluate, rangeName) :?> ExcelReference
            let sRef = XlCall.Excel(XlCall.xlfSelection) :?> ExcelReference
            let sheet = Sheet(sRef.SheetId)
            Range
                (sheet.Cells(rRef.RowFirst + 1, rRef.ColumnFirst + 1), 
                 sheet.Cells(rRef.RowLast + 1, rRef.ColumnLast + 1))
        
        new(rangeName : string, sheet : Sheet) = 
            Range(sheet.Name + "!" + rangeName)
        member this.Parent = Sheet(this.RangeRef.SheetId)
        
        member this.Select() = 
            this.Parent.Select()
            XlCall.Excel(XlCall.xlcFormulaGoto, this.RangeRef) |> ignore
        
        member this.BegCell = 
            Cells
                (this.RangeRef.RowFirst + 1, this.RangeRef.ColumnFirst + 1, 
                 this.Parent)
        member this.EndCell = 
            Cells
                (this.RangeRef.RowLast + 1, this.RangeRef.ColumnLast + 1, 
                 this.Parent)
        
        member this.Rows = 
            [|for i = 0 to this.RowCount - 1 do
                  yield this.Parent.Range
                            (
                             this.Parent.Cells
                                 (this.BegCell.Row + i, this.BegCell.Column), 
                             
                             this.Parent.Cells
                                 (this.BegCell.Row + i, 
                                  this.BegCell.Column + this.ColumnCount - 1))|]
        
        member this.Columns = 
            [|for i = 0 to this.ColumnCount - 1 do
                  yield this.Parent.Range
                            (
                             this.Parent.Cells
                                 (this.BegCell.Row, this.BegCell.Column + i), 
                             
                             this.Parent.Cells
                                 (this.BegCell.Row + this.RowCount - 1, 
                                  this.BegCell.Column + i))|]
        
        member this.RowCount = 
            this.RangeRef.RowLast - this.RangeRef.RowFirst + 1
        member this.ColumnCount = 
            this.RangeRef.ColumnLast - this.RangeRef.ColumnFirst + 1
        member this.Cells = 
            Array2D.init (this.EndCell.Row - this.BegCell.Row + 1) 
                (this.EndCell.Column - this.BegCell.Column + 1) 
                (fun i j -> 
                    Cells
                        (this.BegCell.Row + i, this.BegCell.Column + j, 
                         this.BegCell.Parent))
        
        member this.Value 
            with get () = this.RangeRef.GetValue()
            and set (v : obj) = 
                if this.RangeRef.SetValue(v) = false then 
                    invalidArg "v" "could not set value to range"
    
    let SelectedSheet() = 
        let s = XlCall.Excel(XlCall.xlfSelection) :?> ExcelReference
        Sheet(s.SheetId)
    
    let Selection() = 
        let s = XlCall.Excel(XlCall.xlfSelection) :?> ExcelReference
        let sh = Sheet(s.SheetId)
        sh.Range
            (sh.Cells(s.RowFirst, s.ColumnFirst), 
             sh.Cells(s.RowLast, s.ColumnLast))