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