Author Topic: AutoCAD to Excel and back again.  (Read 17956 times)

0 Members and 1 Guest are viewing this topic.

vegbruiser

  • Guest
AutoCAD to Excel and back again.
« on: December 09, 2010, 08:46:23 AM »
Hi all,

We currently make use of version 2.53 of this (AutoXLSTable - originally created by Cadaddontools.)

And I'd like to make my own implementation, but haven't done any work thus far linking AutoCAD to Excel.

This is primarily so we don't have to use AutoCAD 2006 any more as it's incompatible with Windows 7 and I'd ideally like to see this tired old dinosaur retired for good.

Does anyone have any suggestions or examples I might be able to work from or lists of things to avoid when linking these 2 programs together?

Thanks in advance.

Alex.

Matt__W

  • Seagull
  • Posts: 12955
  • I like my water diluted.
Re: AutoCAD to Excel and back again.
« Reply #1 on: December 09, 2010, 08:48:11 AM »
Every time I see your user name, my mind automatically thinks "Axel F" and I start humming the song.  :)
Autodesk Expert Elite
Revit Subject Matter Expert (SME)
Owner/FAA sUAS Pilot @ http://skyviz.io

vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #2 on: December 09, 2010, 09:27:54 AM »
Hehe, I hadn't realised that before now.  :lmao:

Do, do, do, de, de, do, do etc.


kaefer

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #3 on: December 09, 2010, 11:01:24 AM »
We currently make use of version 2.53 of this (AutoXLSTable - originally created by Cadaddontools.)

And I'd like to make my own implementation, but haven't done any work thus far linking AutoCAD to Excel.

What feature exactly is it that you need implemented? By perusing http://www.gstarsoft.com/autoxlstable.shtm, it is my impression that they provide an alternative to OLE objects for tables inserted into AutoCAD. Native AutoCAD tables aren't that good in replicating a spreadsheet's behaviour, but it isn't immediately clear that one would need custom software to accomplish something remotely similar... Another aspect of data communication between AutoCAD and Excel is the transfer of attribute values, lengths, areas, block counts etc.

General advice: Look out for "late binding", and as an example of block count see this http://www.theswamp.org/index.php?topic=35123.0

Regards, Thorsten

vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #4 on: December 09, 2010, 01:18:58 PM »
We currently make use of version 2.53 of this (AutoXLSTable - originally created by Cadaddontools.)

And I'd like to make my own implementation, but haven't done any work thus far linking AutoCAD to Excel.

What feature exactly is it that you need implemented? By perusing http://www.gstarsoft.com/autoxlstable.shtm, it is my impression that they provide an alternative to OLE objects for tables inserted into AutoCAD. Native AutoCAD tables aren't that good in replicating a spreadsheet's behaviour, but it isn't immediately clear that one would need custom software to accomplish something remotely similar... Another aspect of data communication between AutoCAD and Excel is the transfer of attribute values, lengths, areas, block counts etc.

General advice: Look out for "late binding", and as an example of block count see this http://www.theswamp.org/index.php?topic=35123.0

Regards, Thorsten
Thanks for the late binding tip Thorsten.

The part of third tool I need to recreate is the counting of blocks, measuring of lengths and outputting all of that to an Excel spreadsheet.

Is it possible to bind an AutoCAD table to a spreadsheet in the same way you can bind data from other sources to a regular datagrid?

Jeff H

  • Needs a day job
  • Posts: 6150
Re: AutoCAD to Excel and back again.
« Reply #5 on: December 09, 2010, 02:06:19 PM »
Here is a simple one slapped together that adds Block Name and count using COM

Remeber to reference Microsoft Excel 12.0 Object Library

Code: [Select]
[CommandMethod("ExcelAndCom")]
        public void ExcelAndCom()
        {
            Document doc = Application.DocumentManager.MdiActiveDocument;
            Database db = doc.Database;
            Editor ed = doc.Editor;

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Add("");
            Microsoft.Office.Interop.Excel.Worksheet ws = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
            ws.Range["A1"].Value = "Block Name";
            ws.Range["B1"].Value = "Block Count";           
            int row = 1;
            using (Transaction trx = db.TransactionManager.StartTransaction())
            {
                BlockTable bt = db.BlockTableId.GetObject(OpenMode.ForRead) as BlockTable;
                foreach (ObjectId objId in bt)
                {
                    BlockTableRecord btr = objId.GetObject(OpenMode.ForRead) as BlockTableRecord;
                    if (!(btr.IsLayout))
                    {
                        row++;
                        int coloum = 1;
                        ws.Cells[row, coloum] = btr.Name;
                        ObjectIdCollection objIdColl = btr.GetBlockReferenceIds(true, true);
                        coloum++;
                        ws.Cells[row, coloum] = objIdColl.Count.ToString();
                    }
                }
               
            }
            excel.Visible = true;


        }


kaefer

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #6 on: December 09, 2010, 09:45:46 PM »
Remeber to reference Microsoft Excel 12.0 Object Library

Remember me to forget all references to COM libraries. Sometimes it seems appropriate to replace one evil with another, lesser one. What do y'all think about type extensions on System.Object?

Here's the simple block counter again, late binding and all, in C#. The actual data resides in a 2D array, that should ensure scalability. Passing of arguments is via params arrays, that saves on overloads (this is actually ripped out off Tony's COMHelper class; http://www.theswamp.org/index.php?topic=26612.msg321007#msg321007). It doesn't provide for nested blocks, but accepts a user selection instead.

Code: [Select]
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;

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

            // Fill in header in row 1 and make it bold
            var xlRange = xlSheet.XlRangef(1, 1, 1, header.Length);
            xlRange.Set("NumberFormat", "@");
            xlRange.Get("Font").Set("Bold", true);
            xlRange.Set("Value2", new object[] { header });

            // Transfer data
            xlRange = xlSheet.XlRangef(2, 1, arr2.GetLength(0) + 1, arr2.GetLength(1));
            xlRange.Set("NumberFormat", "@");
            xlRange.Set("Value2", new object[] { arr2 });

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

            // Optimal column width
            xlSheet.Get("Columns").Invoke("AutoFit");

            //Return control of Excel to the user.
            xlApp.Set("Visible", true);
            xlApp.Set("UserControl", true);
            xlApp.ReleaseInstance();
        }

        // Acquire and release 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); }
        }
        // Type extensions on System.Object
        static void ReleaseInstance(this object o) {
            Marshal.ReleaseComObject(o);
        }
        // Get, set and invoke for all objects
        static object Get(this object o, string name, params object[] args) {
            return o.GetType().InvokeMember(name, BindingFlags.GetProperty, null, o, args);
        }
        static void Set(this object o, string name, params object[] args) {
            o.GetType().InvokeMember(name, BindingFlags.SetProperty, null, o, args);
        }
        static object Invoke(this object o, string name, params object[] args) {
            return o.GetType().InvokeMember(name, BindingFlags.InvokeMethod, null, o, args);
        }
        // Operates on Excel's Range object only
        static object XlRangef(this object o, int r0, int c0, int r1, int c1) {
            return o.Get("Range", o.Get("Cells", r0, c0), o.Get("Cells", r1, c1));
        }
    }
    public static class BlockCountCommand
    {
        [CommandMethod("BCount")]
        public static void BCount() {
            var doc = Application.DocumentManager.MdiActiveDocument;
            var db = doc.Database;
            var ed = doc.Editor;

            var dict = new System.Collections.Generic.Dictionary<string, 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);
                        string name;
                        try { name = bref.Name; } catch { name = ""; };
                        if(dict.ContainsKey(name)) dict[name]++;
                        else dict.Add(name, 1);
                    }
                    tr.Commit();
                }
                var arr2 = Array.CreateInstance(typeof(object), dict.Count, 2);
                var i = 0;
                foreach(KeyValuePair<string, int> entry in dict) {
                    arr2.SetValue(entry.Key, i, 0);
                    arr2.SetValue(entry.Value, i, 1);
                    i++;
                }
                BlockCountUtils.WriteToExcel(new object[] { "Block name", "Count" }, arr2);
            }
        }
    }
}

Cheers, Thorsten

mohnston

  • Bull Frog
  • Posts: 305
  • CAD Programmer
Re: AutoCAD to Excel and back again.
« Reply #7 on: December 10, 2010, 01:07:14 PM »
Are you sure the Table/Excel link feature in the newer versions won't get you where you need to go? Or maybe part way there?
Much of the functionality of the programs that were written to link the two in earlier versions of AutoCAD is not built into the program.

The whole Excel - .NET thing is not as easy as it should be.
If you are going to go forward with this you might want to look at another way of working with Excel files that doesn't use COM. (slow and messy)
I found an open source tool called Koogra that works pretty well. http://koogra.sourceforge.net/
It's amazing what you can do when you don't know what you can't do.
CAD Programming Solutions

jgr

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #8 on: December 10, 2010, 08:19:15 PM »

mohnston

  • Bull Frog
  • Posts: 305
  • CAD Programmer
Re: AutoCAD to Excel and back again.
« Reply #9 on: December 13, 2010, 01:44:55 AM »
I found an open source tool called Koogra that works pretty well. http://koogra.sourceforge.net/

check this:
http:/npoi.codeplex.com/
http://code.google.com/p/excellibrary//

Npoi is the best (free) solution to create, read and modify Excel files.


They look interesting but neither supports Excel 2007 format which was a requirement for me.
It's amazing what you can do when you don't know what you can't do.
CAD Programming Solutions

vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #10 on: December 13, 2010, 03:33:06 AM »
Fortunately for me, 2007 format although available isn't used for these documents in our office.

I'll try and find time to look at both of these solutions later.

Also, when I ran your sample Thorsten, all I get is a list in the prompt window of AutoCAD. I realise you don't tell the Excel instance to save anything, bit I expected to at least see Excel open and then disappear again.

kaefer

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #11 on: December 13, 2010, 05:10:21 AM »
Also, when I ran your sample Thorsten, all I get is a list in the prompt window of AutoCAD. I realise you don't tell the Excel instance to save anything, bit I expected to at least see Excel open and then disappear again.

Does it look like this?
Block.....Count
---------------
CHAIR ....10
TABLE ....9
DESK .....4


That's because the Command BCOUNT is unfortunately named like its cousin from the Express tools. If you get the right command to run, you should see Excel pop up, filled with values, and stay open. Or you will receive a runtime error, because there's no error handling in the interop.

Regards,  Thorsten
« Last Edit: December 13, 2010, 05:32:13 AM by kaefer »

vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #12 on: December 13, 2010, 06:37:07 AM »
Thanks Thorsten, that is indeed the problem - I've renamed your routine and it seems to work ok now. :)

vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #13 on: December 13, 2010, 11:55:03 AM »
Right, I haven't gotten much further with this, but what I have realised is that the bulk of the "work" that the AutoXLSTable tool does is within Excel itself - we have an Excel worksheet with column headers for the "floor" each item is on, coupled with normal and rotated options for most of the 200+ blocks. Then down the left hand side is a row for each of the blocks.

When you run the AutoXLSTable tool, it searches all the commented cells in the worksheet and populates each cell accordingly. Something I hadn't spotted before now is that the AutoXLS - generated comments are in the following format:

Code: [Select]
Attcount(BlockName,LayerName,AttributeName,AttributeValue) eg. AttCount(XXX-3612D,*All*,FLOOR, Floor B)

&

Length(GroupName,LayerName,Precision) eg. Length(USER02-B,*All*,0.001) (The "-B" signifies Floor)

This then would explain the seemingly hours (if you take into account that we've been using it for 5 years!) of time each of us has sat waiting for the BoM to populate.

I think it would be much faster to simply pass a list of the currently inserted blocks sorted by the Non-anonymous BlockName* to Excel rather than, (as seems to happen now) search through the entire blocktable on each commented cell - of which there are more than 3000 if you take into account 200+ (rows) x 2 (normal or reverse blocks) x 8 (possible floors).

As for the Groups, I was planning on implementing something (loosely) similar to this:

http://www.theswamp.org/index.php?topic=30413.msg361848#msg361848

But using the groupname to grab only the groups I need. With that in place it should simply be a case of iterating through each group (that matches our required naming convention) and adding together the length of the polylines contained within.

Then I can pass those values back to my BoM command.

*Currently, the AutoXLSTable tool doesn't support Dynamic blocks - because I assume they tend to end up as *U22 etc?

vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #14 on: December 13, 2010, 03:37:51 PM »
Here's an initial offering:
Code: [Select]
[CommandMethod("CGs")]
        public static void CountGroupLengths()
        {
            try
            {
                Document doc = AcadApp.DocumentManager.MdiActiveDocument;
                Editor ed = doc.Editor;
                Database db = doc.Database;
                Dictionary<string,double> grouplengths = new Dictionary<string,double>();
                using (Transaction tr = db.TransactionManager.StartTransaction())
                {
                    DBDictionary groupdict = (DBDictionary)tr.GetObject(db.GroupDictionaryId, OpenMode.ForRead);
                    foreach (DBDictionaryEntry item in groupdict)
                    {
                        Group group = (Group)tr.GetObject(item.Value, OpenMode.ForRead);
                        if (group.NumEntities > 0)
                        {
                            ObjectId[] EntIds = group.GetAllEntityIds();
                            ObjectIdCollection IDCol = new ObjectIdCollection(EntIds);
                            double grouplength = MeasureGroupLength(IDCol);
                            grouplengths.Add(group.Name, grouplength);
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("The error was: " + ex.Message + "\n\r" + ex.StackTrace);
            }
        }

        private static double MeasureGroupLength(ObjectIdCollection group)
        {
            Document doc = AcadApp.DocumentManager.MdiActiveDocument;
            Editor ed = doc.Editor;
            Database db = doc.Database;
            Double length = 0;
            using (Transaction tr = db.TransactionManager.StartTransaction())
            {
                foreach (ObjectId id in group)
                {
                    Entity ent = (Entity)tr.GetObject(id, OpenMode.ForRead);
                    if (ent is Polyline) //then we're on the right track!
                    {
                        Polyline pl = (Polyline)tr.GetObject(ent.ObjectId, OpenMode.ForRead);
                        length = length + pl.Length;
                    }
                }
                return length;
            }
        }

I haven't done anything with the resultant Dictionary of lengths - although I noticed that the groups in my test file which were created using the AutoXLSTable tool are in the naming convention "*XX" which is annoying..


vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #16 on: January 04, 2011, 12:22:52 PM »
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

  • Needs a day job
  • Posts: 6150
Re: AutoCAD to Excel and back again.
« Reply #17 on: January 05, 2011, 03:39:12 AM »
Take a look at these classes
DataLink
DataLinkManager
LinkedTableData


for more info

AcDbDataLink
AcDbDataLinkManager
AcDbLinkedTableData

kaefer

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #18 on: January 05, 2011, 09:32:36 AM »
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

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #19 on: January 12, 2011, 09:20:07 AM »
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

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #20 on: January 13, 2011, 12:03:43 PM »
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

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #21 on: January 13, 2011, 01:25:05 PM »
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; }
        }
« Last Edit: January 13, 2011, 01:51:16 PM by kaefer »

vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #22 on: January 14, 2011, 04:05:22 AM »
Thanks Thorsten, that (a struct) was exactly what was needed.   :lol:

fixo

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #23 on: January 14, 2011, 04:42:02 AM »
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
« Last Edit: January 14, 2011, 05:51:18 PM by fixo »

Jeff H

  • Needs a day job
  • Posts: 6150
Re: AutoCAD to Excel and back again.
« Reply #24 on: January 14, 2011, 04:55:26 AM »
Thorsten, you're awesome!
5 stairs!
Many regards

Oleg


I give him 9 stairs!

fixo

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #25 on: January 14, 2011, 06:56:09 AM »
Agreed :)

vegbruiser

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #26 on: January 14, 2011, 11:54:24 AM »
Stairs (or Stars  :-o ) 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

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #27 on: January 14, 2011, 01:02:45 PM »
...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

  • Guest
Re: AutoCAD to Excel and back again.
« Reply #28 on: January 18, 2011, 07:24:58 AM »
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.