export room area to excel using c# and autocad .net api

[Anonymous]

i've been asked to provide a way for some cad employees to update via an export to an excel spreadsheet the latest area total for each room in the drawing.  my assumption is the spreadsheet will already contain a column for room numbers from the drawing and a room area column. 

so far i've found out how to iterate the drawing layers.  what i have yet to figure out is how to capture the room area property on the layer so i can copy it into the excel cell that matches with the captured room number from the drawing.  is there a way to access the geometry of the particular layer?  can anyone point me in the right direction?

[cadMeUp]

The info your looking for would be stored in the PropertySet(s) of the Space objects applied to the tagged rooms.

The attached code file is produced for Arch/MEP 2013, but should be similar in past versions. Depending on the property sets applied to your rooms/spaces you might get different results, but the code shows how you can go about getting the room names, areas, and room numbers applied. I ran the command using default tags applied to the spaces, see the attached image.

The code file explains other detalis as well.

Hope this gets you in the right direction...

[Hallex · Accepted solution]

You can play with this code, change whatever you need to your suit,

I used layer "A-ANNO-TEXT" for blocks

using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.ComponentModel;
using System.Globalization;
using acadApp = Autodesk.AutoCAD.ApplicationServices.Application;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;
using Autodesk.AutoCAD.Geometry;

[assembly: CommandClass(typeof(BlockCS.BlockCommands))]

namespace BlockCS
{

    public static class BlockCommands
    {
        //-----------------------------------------------------------------------------------------------//
        // require blocks "room label" with 2 attributes: "ROOMNUMBER" and "ROOMAREA"
        [CommandMethod("rooms")]
        public static void BCount()
        {
            Document doc = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument;

            Database db = doc.Database;

            Editor ed = doc.Editor;

            string ctab = (string)Autodesk.AutoCAD.ApplicationServices.Application.GetSystemVariable("CTAB");
            // set layer name to lookup blocks
            string layername = "A-ROOM-TEXT";
            // set block name
            string blockname = "room label";

            // create selection filter
            SelectionFilter sf = 
                new SelectionFilter(new TypedValue[] {
                new TypedValue(0, "INSERT"),
                new TypedValue(2, blockname),
                new TypedValue(8, layername),
                new TypedValue(410, ctab),
            });

            PromptSelectionOptions pso = new PromptSelectionOptions();
            pso.MessageForAdding = "Selecting the room labels... ";
            pso.AllowDuplicates = false;
            PromptSelectionResult psr = ed.SelectAll(sf);
            if (psr.Status == PromptStatus.Error) return;

            List<object[]> rooms = new List<object[]>();

                using (var tr = db.TransactionManager.StartTransaction())
                {
                    foreach (SelectedObject so in psr.Value)
                    {
                        BlockReference bref = (BlockReference)tr.GetObject(so.ObjectId, OpenMode.ForRead);
                        //populate array with attribute data
                        Autodesk.AutoCAD.DatabaseServices.AttributeCollection attcoll = bref.AttributeCollection;
                        object[] info = new object[2];
                        foreach (ObjectId id in attcoll)
                        {
                            AttributeReference atref = (AttributeReference)tr.GetObject(id, OpenMode.ForRead);
                            switch (atref.Tag)
                            {
                                case "ROOMNUMBER":
  
                                info[0] = atref.TextString;
                                    break;

                                case "ROOMAREA":
                            
                                info[1] = atref.TextString;
                                    break;
                                default:
                                    break;
                            }
                            if (!rooms.Contains(info))
                            rooms.Add(info);
                        }
                    }
                    tr.Commit();
                }
            //sort list by room number using delegate
       rooms.Sort( delegate(object[]a, object[] b)
       {
           int res = -1;
          if( Convert.ToInt32(a[0])> Convert.ToInt32(b[0]))
          {
              res=0;
          }
          else if (Convert.ToInt32(a[0]) == Convert.ToInt32(b[0]))
          {
              res = 1;
          }
          return res;
       }
    );
            // create and populate an array
             Array excdata = Array.CreateInstance(typeof(object), rooms.Count,2);
               
               int c = 0;
                foreach (object[] item in rooms)
                {
                    excdata.SetValue(item[0], c, 0);
                    excdata.SetValue(item[1], c, 1);
                    c++;
                }
            // add information to display the result
                StringBuilder sb = new StringBuilder();
                for (c = 0; c <= excdata.GetUpperBound(0); c++)
                {
                    sb.AppendLine(string.Format("{0}  =  {1}", excdata.GetValue(c, 0), excdata.GetValue(c, 1)));
                }

                Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog(string.Format("Write info to Excel:\n{0}", sb.ToString()));

                //write array to existing workbook

//           ExcelTool.FillRange(
//@"C:\Test\Programming\BCOUNT.xlsx", // excel file name
//"Room Info",// sheet name
//new object[] { "Room Area", "Room Number" },// headers
//excdata);// array to populate
            //------------------------- or to write to the new book -----------------------//
            ExcelTool.FillExistingBook(
                @"C:\Test\Programming\BCOUNT.xlsx", // excel file name
new object[] { "Room Area", "Room Number" },// headers
excdata);
        }
}
}

//--------------------------------------------------------------------------------------------//
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Runtime.CompilerServices;

using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;

[assembly: CommandClass(typeof(BlockCS.ExcelTool))]

namespace BlockCS
{
  static class ExcelTool
   {
     
        //------------------------ written by kaefer ------------------------------//
        // 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 void FillRange(string fname, string sheetname, object[] header, Array arr)
        {
            object xlApp = GetOrCreateInstance("Excel.Application");

            object xlBooks = xlApp.Get("Workbooks");
            object xlBook = xlBooks.Invoke("Open", fname);
            object xlSheets = xlBook.Get("Worksheets");
            object xlSheet = xlSheets.Get("Item", sheetname);

            // Fill in header in row 1 and make it bold
            object 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, arr.GetLength(0) + 1, arr.GetLength(1));
            xlRange.Set("NumberFormat", "@");
            xlRange.Set("Value2", new object[] { arr });

            // This column has numeric format
            xlRange = xlSheet.XlRangef(2, 2, arr.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();
        }
        public static void FillExistingBook(string fname, object[] header, Array arr2)
        {
            object xlApp = GetOrCreateInstance("Excel.Application");

            object xlBooks = xlApp.Get("Workbooks");
            object xlBook = xlBooks.Invoke("Add");
            object xlSheets = xlBook.Get("Worksheets");
            object xlSheet = xlSheets.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();
        }
}
}

~'J'~

[Anonymous]

thanks a TON to those who replied. very helpful and appreciated