Topic: AutoCAD to Excel and back again.

[Jeff H]

Do a paste special?

http://www.infiniteskills.com/demos/movie-player.php?h=685&w=890&movie=http://iskills-media.s3.amazonaws.com/autocadadv-demo/0907.mp4

[vegbruiser]

That does look like an interesting approach, but my current set of users are too used to clicking buttons and having a tool do it for them to be willing to change to a more manual approach.

What I might try and do is work on a variation of the method used in that video coupled with my (slightly) modified implementation of the BlockCountUtils class kindly provided by Thorsten.

Cheers,

Alex.

[Jeff H]

Take a look at these classes
DataLink
DataLinkManager
LinkedTableData


for more info

AcDbDataLink
AcDbDataLinkManager
AcDbLinkedTableData

[kaefer]

That does look like an interesting approach, but my current set of users are too used to clicking buttons and having a tool do it for them to be willing to change to a more manual approach.

What I might try and do is work on a variation of the method used in that video coupled with my (slightly) modified implementation of the BlockCountUtils class kindly provided by Thorsten.

Hi Alex,

just in case you ever wanted to write "static dynamic" in a C# 4.0 project, here's the BlockCountUtils class based on dynamic type. Much easier on the eyes, but requires AutoCAD Rel. 2011, since earlier support was gone with VS2010 beta2.

Besides, did any of you check the potential for replacing AutoCAD.Interop with dynamics?

Code: [Select]

    static class BlockCountUtils
    {
        public static void WriteToExcel(object[] header, Array arr2)
        {
            dynamic xlApp = GetOrCreateInstance("Excel.Application");
            dynamic xlSheet = xlApp.Workbooks.Add.Worksheets[1];

            // Fill in header in row 1 and make it bold
            dynamic xlRange = XlRangef(xlSheet, 1, 1, 1, header.Length);
            xlRange.NumberFormat = "@";
            xlRange.Font.Bold = true;
            xlRange.Value2 = header;

            // Transfer data
            xlRange = XlRangef(xlSheet, 2, 1, arr2.GetLength(0) + 1, arr2.GetLength(1));
            xlRange.NumberFormat = "@";
            xlRange.Value2 = arr2;

            // This column has numeric format
            xlRange = XlRangef(xlSheet, 2, 2, arr2.GetLength(0) + 1, 2);
            xlRange.NumberFormat = "0";

            // Optimal column width
            xlSheet.Columns.AutoFit();

            //Return control of Excel to the user.
            xlApp.Visible = true;
            xlApp.UserControl = true;
            Marshal.ReleaseComObject(xlApp);
        }
       
        // Acquire Application objects
        static object GetInstance(string appName) {
            return Marshal.GetActiveObject(appName);
        }
        static object CreateInstance(string appName) {
            return Activator.CreateInstance(Type.GetTypeFromProgID(appName));
        }
        static object GetOrCreateInstance(string appName) {
            try { return GetInstance(appName); }
            catch { return CreateInstance(appName); }
        }
        // Excel Range function
        static dynamic XlRangef(dynamic d, int r0, int c0, int r1, int c1)
        {
            return d.Range(d.Cells(r0, c0), d.Cells(r1, c1));
        }
    }
Cheers, Thorsten

[vegbruiser]

HI Thorsten,

Thanks for the "static dynamic" example.

I will have to read up on that topic before experimenting with it.

In the meantime, I'm thinking of using one of Kean's old posts that makes use of the DataLink class suggested by Jeff earlier.

[vegbruiser]

Hi All,

I need some help:

In Thorsten's original code, he was only counting the number of each block in a particular space; my problem is I need to count the number of each block that has a "Floor" attribute set to (for instance) "Floor G".

This left with the problem of how to store "BlockName, FloorName, AND Count"?

My solution for this was to create a Dictionary<TKey,TValue> using the following class as the TKey:

Code: [Select]

public class BomRow
{
public string BlockName { get; set; }
public string FloorName {get; set; }
}
This then presents me with the problem of how to check whether there is already a block with the BlockName "myblock1" and FloorName "Floor G".

In the preceding example from Thorsten he used this:

Code: [Select]

if(dict.ContainsKey(name)) dict[name]++;
else dict.Add(name,1);
But try as I might using:

Code: [Select]

public static void BCount()
        {
            var doc = AcadApp.DocumentManager.MdiActiveDocument;
            var db = doc.Database;
            var ed = doc.Editor;
            var dict = new Dictionary<string, int>();
            var lst = new Dictionary<BomRow,int>();
            var sf = new SelectionFilter(new TypedValue[] { new TypedValue(0, "INSERT") });
            var pso = new PromptSelectionOptions();
            pso.MessageForAdding = "Select blocks (or enter for all): ";
            pso.AllowDuplicates = false;
            var psr = ed.GetSelection(pso, sf);
            // Error it's not, actually
            if (psr.Status == PromptStatus.Error) psr = ed.SelectAll(sf);
            // Ensure that the selection isn't empty
            if (psr.Status == PromptStatus.OK && psr.Value.Count > 0)
            {
                using (var tr = db.TransactionManager.StartTransaction())
                {
                    foreach (SelectedObject so in psr.Value)
                    {
                        var bref = (BlockReference)tr.GetObject(so.ObjectId, OpenMode.ForRead);
                        var Floor = "";
                        var attrefcoll = bref.AttributeCollection;
                        if (attrefcoll.Count > 0)
                        {
                            foreach (ObjectId attrefid in attrefcoll)
                            {
                                var attref = (AttributeReference)tr.GetObject(attrefid, OpenMode.ForRead);
                                switch (attref.Tag)
                                {
                                    case "FLOOR":
                                        Floor = attref.TextString;
                                        break;
                                }
                            }
                        }
                        string name;
                        try { name = bref.Name; }
                        catch { name = ""; };
                        var bomrow = new BomRow
                        {
                            BlockName = name,
                            FloorName = Floor,
                        };
                        //this next line simply doesn't work - and I don't know why!?
                        if (lst.ContainsKey(bomrow)) lst[bomrow]++;
                        else lst.Add(bomrow, 1);
                        if (dict.ContainsKey(name)) dict[name]++;
                        else dict.Add(name, 1);
                    }
                    tr.Commit();
                }
                var arr3 = Array.CreateInstance(typeof(object), lst.Count, 3);
                var j = 0;
                foreach (KeyValuePair<BomRow, int> entry in lst)
                {
                    arr3.SetValue(entry.Key.BlockName, j, 0);
                    arr3.SetValue(entry.Key.FloorName, j, 1);
                    arr3.SetValue(entry.Value, j, 2);
                    j++;
                }
                //amended this line to include an extra column for my FloorName attribute.
                BlockCountUtils.WriteToExcel(new object[] { "Block Name", "Floor Name", "Count" }, arr3);
            }
        }
public class BomRow
{
public string BlockName { get; set; }
public string FloorName {get; set; }
}
I always end up with a line for every block rather than a count of each block on each floor.

Can anyone shed any light on what I'm missing/doing wrong?

[kaefer]

I always end up with a line for every block rather than a count of each block on each floor.

I think you need an EqualityComparer, http://msdn.microsoft.com/en-us/library/ms132151.aspx

Code: [Select]

       class BomRowEqualityComparer : IEqualityComparer<BomRow>
        {
            public bool Equals(BomRow b1, BomRow b2)
            {
                return b1.BlockName == b2.BlockName & b1.FloorName == b2.FloorName;
            }
            public int GetHashCode(BomRow bx)
            {
                return bx.BlockName.GetHashCode() ^ bx.FloorName.GetHashCode();
            }
        }
Better yet, use a struct. That derives from Sytem.ValueType, with its associated if ineffective notion of equality; when a class derives from System.Object, which knows reference equality only.

Code: [Select]

       struct BomRow
        {
            public string BlockName { get; set; }
            public string FloorName { get; set; }
        }

[vegbruiser]

Thanks Thorsten, that (a struct) was exactly what was needed.   

[fixo]

That does look like an interesting approach, but my current set of users are too used to clicking buttons and having a tool do it for them to be willing to change to a more manual approach.

What I might try and do is work on a variation of the method used in that video coupled with my (slightly) modified implementation of the BlockCountUtils class kindly provided by Thorsten.

Hi Alex,

just in case you ever wanted to write "static dynamic" in a C# 4.0 project, here's the BlockCountUtils class based on dynamic type. Much easier on the eyes, but requires AutoCAD Rel. 2011, since earlier support was gone with VS2010 beta2.

Besides, did any of you check the potential for replacing AutoCAD.Interop with dynamics?

Code: [Select]

    static class BlockCountUtils
    {
        public static void WriteToExcel(object[] header, Array arr2)
        {
            dynamic xlApp = GetOrCreateInstance("Excel.Application");
            dynamic xlSheet = xlApp.Workbooks.Add.Worksheets[1];

            // Fill in header in row 1 and make it bold
            dynamic xlRange = XlRangef(xlSheet, 1, 1, 1, header.Length);
            xlRange.NumberFormat = "@";
            xlRange.Font.Bold = true;
            xlRange.Value2 = header;

            // Transfer data
            xlRange = XlRangef(xlSheet, 2, 1, arr2.GetLength(0) + 1, arr2.GetLength(1));
            xlRange.NumberFormat = "@";
            xlRange.Value2 = arr2;

            // This column has numeric format
            xlRange = XlRangef(xlSheet, 2, 2, arr2.GetLength(0) + 1, 2);
            xlRange.NumberFormat = "0";

            // Optimal column width
            xlSheet.Columns.AutoFit();

            //Return control of Excel to the user.
            xlApp.Visible = true;
            xlApp.UserControl = true;
            Marshal.ReleaseComObject(xlApp);
        }
       
        // Acquire Application objects
        static object GetInstance(string appName) {
            return Marshal.GetActiveObject(appName);
        }
        static object CreateInstance(string appName) {
            return Activator.CreateInstance(Type.GetTypeFromProgID(appName));
        }
        static object GetOrCreateInstance(string appName) {
            try { return GetInstance(appName); }
            catch { return CreateInstance(appName); }
        }
        // Excel Range function
        static dynamic XlRangef(dynamic d, int r0, int c0, int r1, int c1)
        {
            return d.Range(d.Cells(r0, c0), d.Cells(r1, c1));
        }
    }
Cheers, Thorsten

Thorsten, you're awesome!
5 stars!
Many regards

Oleg

[Jeff H]

Thorsten, you're awesome!
5 stairs!
Many regards

Oleg

I give him 9 stairs!

[fixo]

Agreed

[vegbruiser]

Stairs (or Stars  ) aside; I can't for the life of me figure out how to get Thorsten's code to open Excel using a specific template file. I'm pretty sure it has to do with the .Invoke("Add") line of this:

Code: [Select]

var xlSheet =
                xlApp
                .Get("Workbooks")
                .Invoke("Add")
                .Get("Worksheets")
                .Get("Item", 1);
But have no idea how to amend it.

[kaefer]

...open Excel using a specific template file. I'm pretty sure it has to do with the .Invoke("Add") line of this

I'm stairing in the air as I don't know the answer myself. You can call the Add method with the XlWBATemplate enum (XlWBATemplate.xlWBATWorksheet is -4167), but that's about it. Did you try Workbooks.Open?

.

[vegbruiser]

I'll be honest, I haven't tried Workbooks.Open

Using dynamics would that look something like:

Code: [Select]

dynamic xlSheet = xlApp.Workbooks.Open(MyTemplateFile);
And (a complete guess) at the non-dynamic version:

Code: [Select]

var xlSheet =
                xlApp
                .Get("Workbooks")
                .Invoke("Open",MyTemplateFile)
                .Get("Worksheets")
                .Get("Item", 1);


Am I correct in assuming that if the commands I want to use can be called in (*gasp*) vba then I can call them via this late-binding approach? It would help me to understand how this all works if so.

What I am also now looking at is the EPPlus project. It replaces the need to interface with Excel at all. Which is ace as far as my initial setup of my BoM file is concerned.