Skip to content

Instantly share code, notes, and snippets.

@mndrake
Last active March 11, 2020 09:46
Show Gist options
  • Save mndrake/5963107 to your computer and use it in GitHub Desktop.
Save mndrake/5963107 to your computer and use it in GitHub Desktop.
A F# wrapper class for the Excel4/Excel12 methods contained in Excel-DNA to mimic basic methods of the COM object model
// 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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment