.NET

Reply
Active Member
toyter
Posts: 9
Registered: ‎03-20-2006
Message 1 of 4 (2,093 Views)
Accepted Solution

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

2093 Views, 3 Replies
06-01-2012 09:10 AM

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?

 

Valued Mentor
cadMeUp
Posts: 331
Registered: ‎05-11-2006
Message 2 of 4 (2,072 Views)

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

06-01-2012 04:40 PM in reply to: toyter

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

*Expert Elite*
Hallex
Posts: 1,569
Registered: ‎10-08-2008
Message 3 of 4 (2,055 Views)

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

06-02-2012 03:05 AM in reply to: toyter

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'~

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Active Member
toyter
Posts: 9
Registered: ‎03-20-2006
Message 4 of 4 (2,004 Views)

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

06-05-2012 01:54 PM in reply to: Hallex

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

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
Are you familiar with the Autodesk Expert Elites? The Expert Elite program is made up of customers that help other customers by sharing knowledge and exemplifying an engaging style of collaboration. To learn more, please visit our Expert Elite website.

Need installation help?

Start with some of our most frequented solutions to get help installing your software.

Ask the Community