Skip to content

Instantly share code, notes, and snippets.

@mndrake

mndrake/Excel4.fs

Last active Mar 11, 2020
Embed
What would you like to do?
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
You can’t perform that action at this time.