Topic: AutoCAD communication with Excel using late binding in F#

[kaefer]

Hi, all!

Enclosed is a simple block counter inspired by perusing this microsoft support page on the differences between early and late binding, http://support.microsoft.com/?scid=kb%3Ben-us%3B302902&x=9&y=12. Funny thing is, something similiar was discussed here a lttle while ago, http://www.theswamp.org/index.php?topic=26612.msg321007#msg321007.

I'm very happy to say: Good riddance, Excel interop libraries! No version dependance any more, and no type safety to speak of lost.

Code: [Select]

module LateBindings =

    type bf = System.Reflection.BindingFlags

    let getInstance appName =
        System.Runtime.InteropServices.Marshal.GetActiveObject appName

    let createInstance appName =
        System.Type.GetTypeFromProgID appName |> System.Activator.CreateInstance

    let getOrCreateInstance appName =
        try getInstance appName with _ -> createInstance appName
   
    let get propName parameter (o: obj) =
        o.GetType().InvokeMember(propName, bf.GetProperty, null, o, parameter)

    let set propName parameter (o: obj) =
        o.GetType().InvokeMember(propName, bf.SetProperty, null, o, parameter)
        |> ignore

    let invoke methName parameter (o: obj) =
        o.GetType().InvokeMember(methName, bf.InvokeMethod, null, o, parameter)

    let releaseInstance (o: obj) =
        System.Runtime.InteropServices.Marshal.ReleaseComObject o |> ignore

module Bcount =

    open LateBindings

    open Autodesk.AutoCAD.DatabaseServices
    open Autodesk.AutoCAD.EditorInput
    open Autodesk.AutoCAD.Runtime

    type acApp = Autodesk.AutoCAD.ApplicationServices.Application

    let writeToExcel (header: _ []) (arr2: _ [] list) =
        // connect to existing application or start new one
        let xlApp = getOrCreateInstance "Excel.Application"
       
        // Add workbook, return first worksheet
        let xlSheet =
            xlApp
            |> get "Workbooks" null
            |> invoke "Add" null
            |> get "Worksheets" null
            |> get "Item" [| 1 |]

        // Determine rectangular range with two corner cells
        let xlRangef r0 c0 r1 c1 =
            let rect =
                [|  get "Cells" [| r0; c0 |] xlSheet
                    get "Cells" [| r1; c1 |] xlSheet |]
            get "Range" rect xlSheet

        // Fill in header in row 1 and make it bold
        let xlRange = xlRangef 1 1 1 header.Length
        set "NumberFormat" [| "@" |] xlRange
        get "Font" null xlRange |> set "Bold" [| true |]
        set "Value2" [| header |] xlRange

        arr2
        |> List.iteri (fun r row ->
            let xlRange = xlRangef (r + 2) 1 (r + 2) row.Length
            set "NumberFormat" [| "@" |] xlRange
            set "Value2" [| row |] xlRange )
       
        // This column has numeric format
        let xlRange = xlRangef 2 2 (arr2.Length + 1) 2
        set "NumberFormat" [| "0" |] xlRange

        get "Columns" null xlSheet |> invoke "AutoFit" null |> ignore
       
        //Return control of Excel to the user.
        set "Visible" [| true |] xlApp
        set "UserControl" [| true |] xlApp
        releaseInstance xlApp

    let foldCountToMap (m: Map<_,_>) s =
        m.Add(s, match m.TryFind s with Some v -> v + 1 | None -> 1)

    [<CommandMethod "BCOUNT">]
    let bCountCommand() =
        let doc = acApp.DocumentManager.MdiActiveDocument
        let ed = doc.Editor
        let db = doc.Database

        let sf = new SelectionFilter[| new TypedValue(0, "INSERT") |]
        let pso =
            new PromptSelectionOptions(
                MessageForAdding = "Select blocks (or enter for all): ",
                AllowDuplicates = false )
        let psr = ed.GetSelection(pso, sf)
        // If error then select all
        let psr =
            if psr.Status = PromptStatus.Error then ed.SelectAll sf else psr
        // Ensure that the selection isn't empty
        if  psr.Status = PromptStatus.OK && psr.Value.Count > 0 then

            use tr = db.TransactionManager.StartTransaction()

            let bname (so: SelectedObject) =
                let bref =
                    tr.GetObject(so.ObjectId, OpenMode.ForRead)
                        :?> BlockReference
                try bref.Name with _ -> ""

            psr.Value
            |> Seq.cast
            |> Seq.map bname
            |> Seq.fold foldCountToMap Map.empty
            |> Map.toList
            |> List.map (fun (k, v) -> [| box k; box v |])
            |> writeToExcel [| "Block name"; "Number" |]
                   
            tr.Commit()
Cheers, Thorsten

[Jeff H]

Thanks kaefer I just got in This Book
When I get a chance to read more of it I will need to come back and study all of your posts

[David Hall]

kaefer, by chance do you have this in C#, and an example of opening a file instead of creating one?  I haven't quite gotten to the point of translating F# to C#.  thanks

[kaefer]

Cmdr,

can you use C# 4.0? See the thread AutoCAD to Excel and back again.

The basics of late binding were last covered by gile, see NET and COM with AcCmColor, almost one is wrong....

Cheers, Thorsten

[Jeff H]

If not 4.0 you can write a method with Interop libraries for reference to use for checking Name, number and type of args, return type, etc................

Code: [Select]

        [CommandMethod("OpenExcelEarlyBind")]
        public void OpenExcelEarlyBind()
        {
            Microsoft.Office.Interop.Excel.Application exl = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
            Microsoft.Office.Interop.Excel.Worksheet ws = exl.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

            exl.Visible = true;
        }

        [CommandMethod("OpenExcelLateBind")]
        public void OpenExcelLateBind()
        {
            Type excelType = Type.GetTypeFromProgID("Excel.Application");
            object exl = Activator.CreateInstance(excelType);

            object wb = exl.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, exl, null);

            object[] param = new object[1];
            param[0] = @"C:\Test\ExcelTest.xlsx";
            wb.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, wb, param);

            param = new Object[1];
            param[0] = true;
            exl.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, exl, param);       

        }



[Jeff H]

Or the same thing in previous post but using 4.0's dynamic type
 

Code: [Select]

        [CommandMethod("OpenExcelLateBindDynamic")]
        public void OpenExcelLateBindDynamic()
        {
            dynamic exl = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
            dynamic wb = exl.Workbooks;
            wb.Open( @"C:\Test\ExcelTest.xlsx");
            exl.Visible = true;
        }
 


[David Hall]

Thanks guys, that was just the push i needed.  Now to figure out how to find a cell and post to either side of it