Topic: AutoCAD to Excel and back again.

[vegbruiser]

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]

Every time I see your user name, my mind automatically thinks "Axel F" and I start humming the song. 

[vegbruiser]

Hehe, I hadn't realised that before now.  

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

[kaefer]

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]

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]

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]

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]

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/

[jgr]

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.

Not free:
www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx
www.tmssoftware.com/site/flexcelnet.asp
www.fpoint.com/netproducts/spreadwin/spreadwin.aspx
www.spreadsheetgear.com

[mohnston]

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.

[vegbruiser]

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]

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

[vegbruiser]

Thanks Thorsten, that is indeed the problem - I've renamed your routine and it seems to work ok now.

[vegbruiser]

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]

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..