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):

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:

 

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

Comments

[manohar shinde]

Thank you very much.......

[Monty Lowe]

I have code in vb that does the same thing but I need to have it specify the range name that is predefined in the excel file in the datalink options and I can not for the life of me figure out how to programmably do it. I figure if you can do it manually there should be a way in code.

[Kean]

Monty -

I'm afraid I don't have time to handle this type of question unless directly related to one of my posts (i.e. there's a problem with code I've posted).

Please redirect your question to the AutoCAD .NET Discussion Group, or to ADN, if you're a member.

Kean

[M.Salam]

i wish to have an excel spreadsheet to design runoff and runout of superelevation transitions for civil 3d