Topic: Connecting Excel and Autocad block attributes

[David Hall]

OK, so I haven't played with C# in a really long time, in fact, I just figured out Autodesk added new reference files, which is why all my code wont run now.  So I wanted to know if there was a better way to connect to excel than the way you guys helped me with in the past.  Here is the old code

Code: [Select]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using acApp = Autodesk.AutoCAD.ApplicationServices.Application;
using Autodesk.AutoCAD.Runtime;


namespace ProjLog
{
    public class Plog2013
    {
        [CommandMethod("PLog")]
        public static void ProjectLog()
        {
            Document doc = acApp.DocumentManager.MdiActiveDocument;
            Database db = doc.Database;
            Editor ed = doc.Editor;
            string DwgPath = Path.GetDirectoryName(acApp.DocumentManager.MdiActiveDocument.Name);
            string ProjNum = DwgPath.Substring(DwgPath.LastIndexOf("\\") + 1);
            PromptSelectionOptions pso = new PromptSelectionOptions();
            TypedValue[] filter = new TypedValue[2] { new TypedValue((int)DxfCode.Start, "INSERT"), new TypedValue((int)DxfCode.BlockName, "VTEP-INFO,TEP-INFO-SS,TEP-INFO,TEP-CIP,VTEP-CIP,VUES-INFO,UES-INFO,UES-CIP,VUES-CIP") };
            SelectionFilter selFilter = new SelectionFilter(filter);
            PromptSelectionResult psr = ed.SelectAll(selFilter);
            if (psr.Status != PromptStatus.OK)
            {
                acApp.ShowAlertDialog("Invalid Titleblock in drawing!");
                return;
            }
            string strDwgLine1 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE1");
            string strDwgLine2 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE2");
            string strDwgNum = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "NUM");
            string strDwgVendor = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNAME");
            string strDwgVendorNumber = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNUMBER");
            string strUserPath = GetUser(System.Environment.UserName);
            string strMyFile = string.Concat(strUserPath, "\\", ProjNum, "\\", ProjNum, ".xlsx");
            string strFilename = string.Concat(ProjNum + ".xlsx");


            Excel.Application xlApp;
            bool xLopen = IsExcelRunning();
            xlApp = (Excel.Application)GetOrCreateInstance("Excel.Application");
            Workbook wb;
            if (xlApp.Workbooks.Count < 1)
            {
                wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            else
            {
                wb = xlApp.Workbooks[strFilename];
            }
            Worksheet ws = wb.Sheets["Sheet1"] as Worksheet;
            xlApp.Visible = false;
            xlApp.UserControl = false ;


            Excel.Range currentFind = null;
            Excel.Range firstFind = null;


            currentFind = (Excel.Range)ws.Columns["B", Type.Missing];


            firstFind = currentFind.Find(strDwgNum, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, 0, 0, Type.Missing);
            string foundAddress = firstFind.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
            firstFind.Activate();
            int rowNum = firstFind.Row;


            if (!string.IsNullOrEmpty(strDwgVendor))
            {
                ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2 + "/" + strDwgVendor + "," + strDwgVendorNumber;
            }
            else
            {
                ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2;
            }
            ws.Cells[rowNum, 1] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "SHT");
            ws.Cells[rowNum, 3] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "REV");
            ws.Cells[rowNum, 4] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "CODE");
            if (xLopen)
            {
                acApp.ShowAlertDialog("Excel will now close!");
            }
           
                wb.Close(true, Type.Missing, Type.Missing);
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);
        }


        public static bool IsExcelRunning()
        {
            Process[] pros = Process.GetProcesses();
            foreach (var process in pros)
            {
                if (process.ProcessName == "EXCEL")
                {
                    return true;
                }
            }
            return false;
        }






        static object GetOrCreateInstance(string appName)
        {
            try { return GetInstance(appName); }
            catch { return CreateInstance(appName); }
        }
        static object GetInstance(string appName)
        {


            return Marshal.GetActiveObject(appName);


        }
        static object CreateInstance(string appName)
        {


            return Activator.CreateInstance(Type.GetTypeFromProgID(appName));


        }
       
        public static string GetAttributeValueFromBlock(Database db, ObjectId blockRefId, string attName)
        {
            string textString = null;
            using (Transaction tr = db.TransactionManager.StartTransaction())
            {
                BlockReference blockRef = tr.GetObject(blockRefId, OpenMode.ForRead) as BlockReference;
                if (blockRef != null)
                {
                    foreach (ObjectId id in blockRef.AttributeCollection)
                    {
                        AttributeReference attRef = tr.GetObject(id, OpenMode.ForRead) as AttributeReference;
                        if ((attRef != null) && (attRef.Tag.ToUpper() == attName))
                        {
                            textString = attRef.TextString;
                            break;
                        }
                    }
                }
                tr.Commit();
            }
            return textString;
        }
        private static string GetUser(string User)
        {
            switch (User.ToUpper())
            {
                case "UA02038":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Hall";
                case "UA50151":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Richardson";
                case "UA00648":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Livingstone";
                case "UA50270":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Dominguez";
                case "UA00071":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Taylor";
                case "UA50050":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Jimenez";
                case "UA00664":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Olivas";
                 case "UA52087":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Armstrong";
                 case "UA52092":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Nrichardson";
                default:
                    return @"No Path Defined";






            }


        }
    }
}


Any help would be appreciated

[Bert]

.. which is why all my code wont run now...

Can you tell me wich line of codes returns an error ?

[kaefer]

.. which is why all my code wont run now...

Can you tell me wich line of codes returns an error ?

I'm guessing that it is in this line:

Code: [Select]

    <Reference Include="AcCoreMgd">
      ...
    </Reference>
@Cmdr: It's hard to tell if you're doing late binding proper, since you kept the references for Microsoft.Office.Interop.Excel (which in that case you wouldn't need anymore).

[David Hall]

I never got the late binding to work.  I have become very frustrated because I can't do as much coding as I used to, and I am forgetting more than I can remember.  I would like to get the late binding to work such that I would not have to update this code every time we upgrade autocad or office.

[WILL HATCH]

Here's my routine to pull sheet data from a workbook. Hope it's a good place to start

Code - C#: [Select]

1.         public static object[,] ExtractSheetData(string FilePath, int SheetNumber)
2.         {
3.             try
4.             {
5.                 dynamic xlApp = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
6.                 xlApp.Visible = true;
7.                 //open workbook
8.                 dynamic xlWorkBooks = xlApp.Workbooks;
9.                 dynamic xlWorkBook = xlWorkBooks.Open(FilePath);
10.                 //open worksheets, load into arrays
11.                 dynamic xlWorkSheet1 = xlWorkBook.Sheets[SheetNumber];
12.                 dynamic r = xlWorkSheet1.UsedRange;
13.                 object[,] Sheet1;
14.                 Sheet1 = (object[,])r.Value2;
15.                 xlWorkBook.Close(false,null,null);
16.                 xlApp.Quit();
17.                 return Sheet1;
18.             }
19.             catch (System.Exception e)
20.             {
21.                 e.showException();
22.                 throw;
23.             }
24.         }

[MexicanCustard]

ClosedXML is what I use instead of COM. No COM no need for late binding.

[kaefer]

Code - C#: [Select]

1.                 object[,] Sheet1;
2.                 Sheet1 = (object[,])r.Value2;

This will throw when it encounters either a range with no data, or just a single cell. Blame Microsoft.

If you happen to fancy cell addressing through the array's indices, at this point you'll need to create a one-based array containing the value of that single cell. The other edge-case reduces from "empty range" to "empty single cell".

Code - C#: [Select]

1.         public static object[,] chkArr(object value)
2.         {
3.             object[,] result = value as object[,];
4.             if (result != null) return result;
5.             result = (object[,])System.Array.CreateInstance(typeof(object), new int[]{1,1}, new int[]{1,1});
6.             result[1, 1] = value;
7.             return result;
8.         }

I'm beginning to see MexicanCustard's point about avoiding that mess altogether.

[WILL HATCH]

interesting, thanks.  I always have lots of data to point at in my application so I've never experienced this problem.