Through the Interface

Updating an Excel spreadsheet from a linked AutoCAD table using .NET

In the last post we saw some code to update an AutoCAD table linked to an Excel spreadsheet. In this post we go the other way, updating an Excel spreadsheet from a linked AutoCAD table.

Here's the C# code:

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("T2S")]

    static public void UpdateSpreadsheetFromTable()

    {

      Document doc =

        Application.DocumentManager.MdiActiveDocument;

      Database db = doc.Database;

      Editor ed = doc.Editor;

      PromptEntityOptions opt =

        new PromptEntityOptions(

          "\nSelect table with spreadsheet to update: "

        );

      opt.SetRejectMessage(

        "\nEntity is not a table."

      );

      opt.AddAllowedClass(typeof(Table), false);

      PromptEntityResult per =

        ed.GetEntity(opt);

      if (per.Status != PromptStatus.OK)

        return;

      Transaction tr =

        db.TransactionManager.StartTransaction();

      using (tr)

      {

        try

        {

          DBObject obj =

            tr.GetObject(

              per.ObjectId,

              OpenMode.ForRead

            );

          Table tb = (Table)obj;

          // It should always be a table

          // but we'll check, just in case

          if (tb != null)

          {

            // The table must be open for write

            tb.UpgradeOpen();

            // Update the data link from the table

            tb.UpdateDataLink(

              UpdateDirection.DataToSource,

              UpdateOption.ForceFullSourceUpdate

            );

            // And the spreadsheet from the data link

            ObjectId dlId = tb.GetDataLink(0, 0);

            DataLink dl =

              (DataLink)tr.GetObject(

                dlId,

                OpenMode.ForWrite

              );

            dl.Update(

              UpdateDirection.DataToSource,

              UpdateOption.ForceFullSourceUpdate

            );

          }

          tr.Commit();

          ed.WriteMessage(

            "\nUpdated the spreadsheet from the table."

          );

        }

        catch (Exception ex)

        {

          ed.WriteMessage(

            "\nException: {0}",

            ex.Message

          );

        }

      }

    }

  }

}

Tables with linked spreadsheets are locked by default and display this glyph when you hover over them: . Before you run the code you will need to unlock the table by right-clicking the cell(s) you wish to edit:

One point to note is that the code will work even if the spreadsheet is open in Excel, but the contents will not be updated automatically - you have to close and reopen the file to see the results. And you will probably see this dialog come up twice:

For the best (most logical) results, the T2S command should really be run when the spreadsheet is not open in Excel. I expect it's possible to determine whether a spreadsheet is open in Excel from using standard file access functions in .NET (requesting exclusive access, to see whether it's possible to get it), but that's being left as an exercise for the reader (or for another day, at least :-).

For your convenience, here's a source file containing the code from the last three posts (command implementations for TFS, S2T and T2S).

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

Thanks to Viru Aithal, from DevTech India, for providing the code for this post (I converted the C# code below from some C++ he had sent to a developer).

In the last post we showed how to create a table linked to an Excel spreadsheet using .NET in AutoCAD 2008. AutoCAD does a great job of looking for changes in the Excel spreadsheet, and asking whether you want to update the linked table:

There may be times, however, when you want to force the update programmatically, whether from the spreadsheet to the table ot vice-versa. In this post we'll show the code to update the table from the spreadsheet, and in the next post we'll see some code to update the spreadsheet from the table (should it have been unlocked and edited).

Here's the C# code:

using Autodesk.AutoCAD.ApplicationServices;

using Autodesk.AutoCAD.DatabaseServices;

using Autodesk.AutoCAD.EditorInput;

using Autodesk.AutoCAD.Runtime;

namespace LinkToExcel

{

  public class Commands

  {

    [CommandMethod("S2T")]

    static public void UpdateTableFromSpreadsheet()

    {

      Document doc =

        Application.DocumentManager.MdiActiveDocument;

      Database db = doc.Database;

      Editor ed = doc.Editor;

      PromptEntityOptions opt =

        new PromptEntityOptions(

          "\nSelect table to update: "

        );

      opt.SetRejectMessage(

        "\nEntity is not a table."

      );

      opt.AddAllowedClass(typeof(Table), false);

      PromptEntityResult per =

        ed.GetEntity(opt);

      if (per.Status != PromptStatus.OK)

        return;

      Transaction tr =

        db.TransactionManager.StartTransaction();

      using (tr)

      {

        try

        {

          DBObject obj =

            tr.GetObject(

              per.ObjectId,

              OpenMode.ForRead

            );

          Table tb = (Table)obj;

          // It should always be a table

          // but we'll check, just in case

          if (tb != null)

          {

            // The table must be open for write

            tb.UpgradeOpen();

            // Update the data link from the spreadsheet

            ObjectId dlId = tb.GetDataLink(0, 0);

            DataLink dl =

              (DataLink)tr.GetObject(

                dlId,

                OpenMode.ForWrite

              );

            dl.Update(

              UpdateDirection.SourceToData,

              UpdateOption.None

            );

            // And the table from the data link

            tb.UpdateDataLink(

              UpdateDirection.SourceToData,

              UpdateOption.None

            );

          }

          tr.Commit();

          ed.WriteMessage(

            "\nUpdated the table from the spreadsheet."

          );

        }

        catch (Exception ex)

        {

          ed.WriteMessage(

            "\nException: {0}",

            ex.Message

          );

        }

      }

    }

  }

}

When you run the S2T (for Spreadsheet-to-Table) command, you will be prompted to select a table. The code retrieves the link information from the table and then requests the data link to pull down new data from the spreadsheet before updating the table. Next time we'll look at the code for T2S...

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