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?
Solved! Go to Solution.
Solved by Hallex. Go to Solution.
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...
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'~