topic Re: Attribute to excel with count in .NET Forum

Attribute to excel with count

CAD-Geek — Thu, 22 Feb 2018 00:09:52 GMT

After searching in forum I found code that extracts data from attribute block

but I need to modify the excel output result as DATAEXTRACTION command

            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[4];
                    foreach (ObjectId id in attcoll)
                    {
                        AttributeReference atref = (AttributeReference)tr.GetObject(id, OpenMode.ForRead);

                        if (atref.Tag=="QUANTITY")
                        {
                            info[0] = atref.TextString;
                        }
                        if (atref.Tag=="MATERIAL")
                        {
                            info[1] = atref.TextString;
                        }
                        if (atref.Tag=="STANDARD")
                        {
                            info[2] = atref.TextString;
                        }

                        if (!rooms.Contains(info))
                        {
                            
                            rooms.Add(info);
                        }
                    }
                }
                tr.Commit();
            }

the excel output

I need the result to be something like this

Re: Attribute to excel with count

SENL1362 — Thu, 22 Feb 2018 07:36:22 GMT

search for GROUP BY

Re: Attribute to excel with count

_gile — Thu, 22 Feb 2018 08:01:12 GMT

Hi,

You can have a look at this:

http://www.theswamp.org/index.php?topic=31859.msg452527#msg452527

Re: Attribute to excel with count

CAD-Geek — Thu, 22 Feb 2018 08:50:06 GMT

thnx @_gile for reply I tried your code but I got this exception

Re: Attribute to excel with count

CAD-Geek — Thu, 22 Feb 2018 08:54:05 GMT

thnx @SENL1362 for the reply I tried your suggest like this

if (!rooms.Contains(info))
                        {
                            info[3] = rooms.GroupBy(x=>x).Count() + "c";
                            rooms.Add(info);
                        }

Re: Attribute to excel with count

SENL1362 — Thu, 22 Feb 2018 08:54:52 GMT

For this:

Do this:

                //var info = new object[4];
                var rooms = new List<object[]>();
                rooms.Add(new object[] { 3, "", "MS02" });
                rooms.Add(new object[] { 1, "", "MS19E" });
                rooms.Add(new object[] { 1, "", "MS19E" });
                rooms.Add(new object[] { 3, "", "MS17B" });
                rooms.Add(new object[] { 1, "", "MS19E" });
                rooms.Add(new object[] { "1m", "439C", "" });
                rooms.Add(new object[] { "1m", "439C", "" });
                rooms.Add(new object[] { "1m", "439C", "" });
                rooms.Add(new object[] { 1, "", "MS47G" });
                rooms.Add(new object[] { 1, "", "MS19E" });
                rooms.Add(new object[] { 1, "", "MS19E" });
                rooms.Add(new object[] { 1, "", "MS19E" });
                rooms.Add(new object[] { "5m", "439C", "" });
                rooms.Add(new object[] { "5m", "439C", "" });
                rooms.Add(new object[] { "5m", "430D", "" });
                rooms.Add(new object[] { "5m", "439C", "" });


                var groupedRooms = rooms.GroupBy(x => new { A = x[1], B = x[2], C = x[0] }).Select(g => new { Count = g.Count(), Quantity = g.Key.C, Material = g.Key.A, Standard = g.Key.B });
                ed.WriteMessage("\nCount, 2-Quantity, 3-Material, 4-Standard");
                //foreach (var r in groupedRooms.OrderBy(n=>n.Count).ThenBy(m=>m.Material))
                foreach (var gr in groupedRooms)
                {
                    ed.WriteMessage($"\n{gr.Count,5}, {gr.Quantity,-10}, {gr.Material,-10}, {gr.Standard,-10}");
                }

Re: Attribute to excel with count

CAD-Geek — Thu, 22 Feb 2018 09:58:11 GMT

I tried

                        if (atref.Tag=="QUANTITY")
                        {
                            rooms.Add(new object[] { atref.TextString });
                            //info[0] = atref.TextString;
                        }
                        if (atref.Tag=="MATERIAL")
                        {
                            rooms.Add(new object[] { atref.TextString });
                            //info[1] = atref.TextString;
                        }
                        if (atref.Tag=="STANDARD")
                        {
                            rooms.Add(new object[] { atref.TextString });
                            //info[2] = atref.TextString;
                        }
var groupedRooms = rooms.GroupBy(x => new { A = x[1], B = x[2], C = x[0] }).Select(g => new { Count = g.Count(), Quantity = g.Key.C, Material = g.Key.A, Standard = g.Key.B });
foreach (var r in groupedRooms.OrderBy(n=>n.Count).ThenBy(m=>m.Material))

and I use this code to export to excel @Hallex

ExcelTool.FillExistingBook(@"C:\Test\Programming\BCOUNT.xlsx", new object[] { "Count","QUANTITY", "MATERIAL", "Standard" },  excdata);

but I got the error

Index was outside the bounds of the array

Re: Attribute to excel with count

_gile — Thu, 22 Feb 2018 10:04:04 GMT

@CAD-Geek wrote:

thnx @_gile for reply I tried your code but I got this exception

As you have a tag named "QUANTITY" you should change the "Quantity" column name of the DataTable.

Try replacing:

dTable.Columns.Add("Quantity", typeof(int));

with:

dTable.Columns.Add("Count", typeof(int));

or whatever else different from any attribute Tag.