Topic: BindingType if no Excel is installed

[Fred Tomke]

Hello,

I have a project and a very little part is to export the contents of a table to Excel or OpenOffice. The buttons only will be visible when the addressed product are installed. That's not the problem. I'll solve that asking the registry. Since I have no TypeLibrary for OpenOffice, I'll translate my existing LispCode to C# easily for the OpenOffice export.

The bigger problem is: shall I use early binding or late binding for Excel? Sure, I'll capsule the functionality in an own class with the using directives for Excel, but I'm not sure that it will be enough to avoid that my DLL will fail to load when no Excel is installed.

So my question is: how does the dll behave at loading time, when the list of bindings contains Microsoft Excel TypeLibrary although Excel is not installed at the customers workstation?
I have not sample workstation for testing. But maybe some others have experiences in that.

Regards,
Fred

[kaefer]

Since I have no TypeLibrary for OpenOffice, I'll translate my existing LispCode to C# easily for the OpenOffice export.

What type library? Early binding means typically building against PIA (Primary Interop Assembly).

The bigger problem is: shall I use early binding or late binding for Excel?

Late binding means no or little version dependancy, and if you can use C# 4.0 dynamics, it's pretty concise. If your target Interop application isn't installed, it will fail at runtime, which may be better to handle than failure at loading time.

Regards

[vegbruiser]

Fred,

If you want to create .xls Spreadsheet files without Excel installed on the target machine, you might like to take a look at the following:

EPPlus-Create advanced Excel 2007 spreadsheets

I started using it on this topic with some success. Although I've been busy the past month or so on other things so haven't had time to look at my implementation of it.

[Fred Tomke]

Hi, kaefer,

What type library? Early binding means typically building against PIA (Primary Interop Assembly).

Yes, I meant this, wasn't sure how to explain in English   

Late binding means no or little version dependancy, and if you can use C# 4.0 dynamics, it's pretty concise.

Hm,  I have read that I have to use Framework 3.5 for building dll's for AutoCAD. Do you use .NET FW 4.0?

If your target Interop application isn't installed, it will fail at runtime, which may be better to handle than failure at loading time.

I agree with you.

Regards,
Fred

[kaefer]

Late binding means no or little version dependancy, and if you can use C# 4.0 dynamics, it's pretty concise.

Hm,  I have read that I have to use Framework 3.5 for building dll's for AutoCAD. Do you use .NET FW 4.0?

Not yet, as v4.0 requires 2011 or above. With regard to C#'s dynamic operators, there's little hope to get them to work under v3.5, where you are pretty much reduced to method calls via Reflection.

On a more positive note, I was just successful in getting dynamic operators to work under F#, via System.Reflection and a little F# specific magic for argument conversion.  This allows the use of dynamic operators under .NET v3.5 and Rel. 2010 and earlier. The following code sample is the old Simple Block Counter again, now new and hopefully much improved...

Code: [Select]

// Simple Block Counter with Excel late binding Interop and Dynamic Operators
// based on a previous installment
// http://www.theswamp.org/index.php?topic=35123.msg403378#msg403378

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

type acApp = Autodesk.AutoCAD.ApplicationServices.Application

// Implementing the dynamic operators for InterOp, derived from http://fssnip.net/2V
open System.Reflection
open Microsoft.FSharp.Reflection

// Set a property
let (?<-) (o: obj) propName value =
    o.GetType().InvokeMember(propName, BindingFlags.SetProperty, null, o, [| value |])
    |> ignore

// Get a property or invoke a method
let (?) (o: obj) name : 'R =
    // When the return type is a function, we have arguments
    if FSharpType.IsFunction(typeof<'R>) then
        let (argType, resType) = FSharpType.GetFunctionElements(typeof<'R>)
        // Build a F# function
        FSharpValue.MakeFunction(typeof<'R>, fun args ->
            let args =
                // If argument is unit, we treat it as no arguments,
                // if it is not a tuple, we create singleton array,
                // otherwise we get all elements of the tuple
                if argType = typeof<unit> then [| |]
                elif not(FSharpType.IsTuple argType) then [| args |]
                else  FSharpValue.GetTupleFields args
            let res =
                o.GetType().InvokeMember(name, BindingFlags.GetProperty, null, o, args)
            // Ignore result if calling context is unit
            if resType = typeof<unit> then box() else res )
    else
        o.GetType().InvokeMember(name, BindingFlags.GetProperty, null, o, null)
    |> unbox<'R>

let getOrCreateInstance appName =
    try
        System.Runtime.InteropServices.Marshal.GetActiveObject appName
    with _ ->
        System.Type.GetTypeFromProgID appName
        |> System.Activator.CreateInstance

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

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

    // Determine rectangular range with two corner cells
    let xlRangef r0 c0 r1 c1 =
        xlSheet?Range(xlSheet?Cells(r0, c0), xlSheet?Cells(r1, c1))
                
    // Fill in header in row 1 and make it bold
    let xlRange = xlRangef 1 1 1 header.Length
    xlRange?NumberFormat <- "@"
    xlRange?Font?Bold <- true
    xlRange?Value2 <- header

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

    xlSheet?Columns?AutoFit()
        
    //Return control of Excel to the user.
    xlApp?Visible <- true
    xlApp?UserControl <- true
    releaseInstance xlApp

[<CommandMethod "BCOUNTDynOp">]
let bCountDynOpCommand() =
    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.groupBy bname
        |> Seq.map (fun (k, v) -> [| box k; box(Seq.length v) |])
        |> Array.ofSeq
        |> writeToExcel [| "Block name"; "Number" |]
                    
        tr.Commit()


[mohnston]

I have a project and a very little part is to export the contents of a table to Excel or OpenOffice. ...

Excel and OpenOffice easily open XML or even csv files.
You could make your life much easier if either of those file types are acceptable.

It could be as easy as System.Data.DataTable.WriteXml(yourFileName).

[Fred Tomke]

It could be as easy as System.Data.DataTable.WriteXml(yourFileName).

Hi mohnston, to do this, I must build up the xml content at first. For Excel and OpenOffice the code for content and formatting already exists.

Nevertheles, it is good to know for other parts of my work.

Regards,
Fred

[dgorsman]

You don't need to build up much of anything in XML.  I've created XML files that point to an XSL files which contains a transform to tables in HTML; when opened in Excel it draws up the tables accordingly.  Same deal but a little more straightforward is to transform the XML to HTML or CSV directly in the creation code.  The upshot is, you roughly outline how your XML is going to look and then either alther the XSL to suit or point to one of several XSL files, both of which are a lot easier than redoing source code for the original application.

[Jeff H]

If you want late binding just use VB.NET

[kaefer]

You don't need to build up much of anything in XML.  I've created XML files that point to an XSL files which contains a transform to tables in HTML; when opened in Excel it draws up the tables accordingly.  Same deal but a little more straightforward is to transform the XML to HTML or CSV directly in the creation code.  The upshot is, you roughly outline how your XML is going to look and then either alther the XSL to suit or point to one of several XSL files, both of which are a lot easier than redoing source code for the original application.

Do you suggest to replace the following workflow...

1. Acquire data
2. Build table in memory
3. Try to acquire InterOp application (may fail)
4. Transfer table (as obj[][] or obj[,])

...with something like this?

1. Acquire data
2. Determine temporary or final file name
3. Build XML
4. Transform XML
5. Save file
6. Try to acquire InterOp application (may fail)
7. Open file in application

then it would appear that the steps 2), 4) and 5) are somewhat additional.

If you want late binding just use VB.NET

Care to expand on that statement? (Surely you don't mean that one can omit Void parameters in VB.)

Cheers, Thorsten

[vegbruiser]

Late binding means no or little version dependancy, and if you can use C# 4.0 dynamics, it's pretty concise.

Hm,  I have read that I have to use Framework 3.5 for building dll's for AutoCAD. Do you use .NET FW 4.0?

Not yet, as v4.0 requires 2011 or above. With regard to C#'s dynamic operators, there's little hope to get them to work under v3.5, where you are pretty much reduced to method calls via Reflection.

On a more positive note, I was just successful in getting dynamic operators to work under F#, via System.Reflection and a little F# specific magic for argument conversion.  This allows the use of dynamic operators under .NET v3.5 and Rel. 2010 and earlier. The following code sample is the old Simple Block Counter again, now new and hopefully much improved...

EDIT: I decided there was no need to quote Thorsten's code again - in case he edited it; someone could have ended up using my quoted code and not the original

That's pretty cool Thorsten - do you mind if I pinch/borrow this for my own nefarious purposes? (that is, as soon as I've read this from cover to cover)  

[Jeff H]

Care to expand on that statement? (Surely you don't mean that one can omit Void parameters in VB.)

Cheers, Thorsten

Just meant
C# dynamics are like vb objects with option strict off.

[kaefer]

Care to expand on that statement? (Surely you don't mean that one can omit Void parameters in VB.)

Just meant
C# dynamics are like vb objects with option strict off.

Thanks for the hint.

Let's Scott Hanselman http://www.hanselman.com/blog/BackToBasicsVarDim.aspx do the talking here. He makes the point that C# 3.0 isn't well suited for late-bound, COM-interop, Office Automation work, and has a VB.NET version with and one without Reflection.

On the other hand I think that Option Strict Off is throwing the baby out with the bathwater. You will get a different language with different semantics, which obviously isn't the case with C# 4.0 dynamics. All in all, that's not sufficient to make me want to install VB.NET.

Regards, Thorsten

[dgorsman]

You don't need to build up much of anything in XML.  I've created XML files that point to an XSL files which contains a transform to tables in HTML; when opened in Excel it draws up the tables accordingly.  Same deal but a little more straightforward is to transform the XML to HTML or CSV directly in the creation code.  The upshot is, you roughly outline how your XML is going to look and then either alther the XSL to suit or point to one of several XSL files, both of which are a lot easier than redoing source code for the original application.

Do you suggest to replace the following workflow...

1. Acquire data
2. Build table in memory
3. Try to acquire InterOp application (may fail)
4. Transfer table (as obj[][] or obj[,])

...with something like this?

1. Acquire data
2. Determine temporary or final file name
3. Build XML
4. Transform XML
5. Save file
6. Try to acquire InterOp application (may fail)
7. Open file in application

then it would appear that the steps 2), 4) and 5) are somewhat additional.
Cheers, Thorsten

To be more precise, use XML if Interop acquisition fails, or just skip detection entirely and go with XML in the first place, using an appropriate XSL downstream to convert to Excel, HTML, CSV, reformatted XML, or whatever else needs to be consumed.  In that case the process would be:

1) Acquire data
2) Build DOMDocument structure
3) Export to file

[mohnston]

It could be as easy as System.Data.DataTable.WriteXml(yourFileName).

Hi mohnston, to do this, I must build up the xml content at first. For Excel and OpenOffice the code for content and formatting already exists.
...

Ah, the formatting would be an issue since WriteXML just writes the data to an XML file.
When you open it in a program (Excel or ?) you get just the basic data, columns and rows.

[dgorsman]

It could be as easy as System.Data.DataTable.WriteXml(yourFileName).

Hi mohnston, to do this, I must build up the xml content at first. For Excel and OpenOffice the code for content and formatting already exists.
...

Ah, the formatting would be an issue since WriteXML just writes the data to an XML file.
When you open it in a program (Excel or ?) you get just the basic data, columns and rows.

Which is why you provide pre-processing instructions that point to an XSL, as such:

Code: [Select]

<?xml-stylesheet type="text/xsl" href="weld report format (BETA1).xsl"?>
When Excel opens the file, it asks the user which stylesheet to use and lists the provided one as a default.

[Jeff H]

Care to expand on that statement? (Surely you don't mean that one can omit Void parameters in VB.)

Just meant
C# dynamics are like vb objects with option strict off.

Thanks for the hint.

Let's Scott Hanselman http://www.hanselman.com/blog/BackToBasicsVarDim.aspx do the talking here. He makes the point that C# 3.0 isn't well suited for late-bound, COM-interop, Office Automation work, and has a VB.NET version with and one without Reflection.

On the other hand I think that Option Strict Off is throwing the baby out with the bathwater. You will get a different language with different semantics, which obviously isn't the case with C# 4.0 dynamics. All in all, that's not sufficient to make me want to install VB.NET.

Regards, Thorsten

Sorry I thought I read was not able to use 4.0

[Fred Tomke]

You don't need to build up much of anything in XML...

Sounds interesting - but I still don't have any idea about the data you send via WriteXML.

Regards, Fred

[Jeff H]

On the other hand I think that Option Strict Off is throwing the baby out with the bathwater. You will get a different language with different semantics, which obviously isn't the case with C# 4.0 dynamics. All in all, that's not sufficient to make me want to install VB.NET.

Regards, Thorsten

Sure you know but to throw it out there
C# 4.0 added optional and named parameters that also simplifies interacting with COM