Kean Walmsley


  • About the Author
    Kean on Google+

April 2014

Sun Mon Tue Wed Thu Fri Sat
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30      







« Using AutoCAD's file selection dialog from .NET | Main | Updating an AutoCAD table linked to an Excel spreadsheet using .NET »

August 22, 2007

Creating an AutoCAD table linked to an Excel spreadsheet using .NET

In the last post I promised to tackle this issue, and so here we are again. :-)

Note: the code in this post relies on enhanced table functionality introduced in AutoCAD 2008, so please don't get frustrated trying to make this work in previous versions.

The following C# code follows on from yesterday's, taking the spreadsheet selected by the user and linking it to a newly-created table in the active AutoCAD drawing. I haven't bothered with line numbering in the below code, as it follows on almost exactly from the code shown last time (aside from renaming the namespace, the command and the function, as well as adding a string constant at the top of the function implementation).

using Autodesk.AutoCAD.ApplicationServices;

using Autodesk.AutoCAD.DatabaseServices;

using Autodesk.AutoCAD.EditorInput;

using Autodesk.AutoCAD.Runtime;

using Autodesk.AutoCAD.Windows;


namespace LinkToExcel

{

  public class Commands

  {

    [CommandMethod("TFS")]

    static public void TableFromSpreadsheet()

    {

      // Hardcoding the string

      // Could also select for it

      const string dlName =

        "Import table from Excel demo";


      Document doc =

        Application.DocumentManager.MdiActiveDocument;

      Database db = doc.Database;

      Editor ed = doc.Editor;


      OpenFileDialog ofd =

        new OpenFileDialog(

          "Select Excel spreadsheet to link",

          null,

          "xls; xlsx",

          "ExcelFileToLink",

          OpenFileDialog.OpenFileDialogFlags.

            DoNotTransferRemoteFiles

        );


      System.Windows.Forms.DialogResult dr =

        ofd.ShowDialog();


      if (dr != System.Windows.Forms.DialogResult.OK)

        return;


      ed.WriteMessage(

        "\nFile selected was \"{0}\".",

        ofd.Filename

      );


      PromptPointResult ppr =

        ed.GetPoint(

          "\nEnter table insertion point: "

        );

      if (ppr.Status != PromptStatus.OK)

        return;


      // Remove the Data Link, if it exists already


      DataLinkManager dlm = db.DataLinkManager;

      ObjectId dlId = dlm.GetDataLink(dlName);

      if (dlId != ObjectId.Null)

      {

        dlm.RemoveDataLink(dlId);

      }


      // Create and add the Data Link


      DataLink dl = new DataLink();

      dl.DataAdapterId = "AcExcel";

      dl.Name = dlName;

      dl.Description =

        "Excel fun with Through the Interface";

      dl.ConnectionString = ofd.Filename;

      dl.DataLinkOption =

        DataLinkOption.PersistCache;

      dl.UpdateOption |=

        (int)UpdateOption.AllowSourceUpdate;


      dlId = dlm.AddDataLink(dl);


      Transaction tr =

        doc.TransactionManager.StartTransaction();

      using (tr)

      {

        tr.AddNewlyCreatedDBObject(dl, true);


        BlockTable bt =

          (BlockTable)tr.GetObject(

            db.BlockTableId,

            OpenMode.ForRead

          );


        Table tb = new Table();

        tb.TableStyle = db.Tablestyle;

        tb.Position = ppr.Value;

        tb.SetDataLink(0, 0, dlId, true);

        tb.GenerateLayout();


        BlockTableRecord btr =

          (BlockTableRecord)tr.GetObject(

            db.CurrentSpaceId,

            OpenMode.ForWrite

          );


        btr.AppendEntity(tb);

        tr.AddNewlyCreatedDBObject(tb, true);

        tr.Commit();

      }


      // Force a regen to display the table

      ed.Regen();

    }

  }

}

Here's what happens when you run the TFS command and select your favourite XLS for linking (I used mass-balance.xls from AutoCAD 2008's Sample\Mechanical Sample folder):

Table_linked_to_excel_spreadsheet

At this stage I haven't focused at all on formating - this is just coming in "as is", without any adjustment of cell alignments, column widths or row heights.

I chose to hardcode the name of the Data Link we use for the spreadsheet. You can run the DATALINK command to check on it, after the command has executed:

Data_link_for_excel_speadsheet_2 

It doesn't seem to be an issue if you repeat the command and bring in a different spreadsheet using the same link - the link appears to continue (although I haven't performed exhaustive testing). If it does prove to be a problem it should be simple enough to create a unique Data Link per spreadsheet imported (or even per time the command is run).

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a00d83452464869e200e54ecdff2d8833

Listed below are links to weblogs that reference Creating an AutoCAD table linked to an Excel spreadsheet using .NET:

blog comments powered by Disqus

10 Random Posts