Through the Interface

using System;

using System.Collections.Generic;

using System.Text.RegularExpressions;

using Autodesk.AutoCAD.ApplicationServices;

using Autodesk.AutoCAD.DatabaseServices;

using Autodesk.AutoCAD.EditorInput;

using Autodesk.AutoCAD.Runtime;

using Autodesk.AutoCAD.Windows;

using Excel = Microsoft.Office.Interop.Excel;

 

namespace LinkToExcel

{

  public class Commands

  {

    [CommandMethod("S2T")]

    static public void UpdateTableFromSpreadsheet()

    {

      var doc =

        Application.DocumentManager.MdiActiveDocument;

      var db = doc.Database;

      var ed = doc.Editor;

 

      var opt = new PromptEntityOptions("\nSelect table to update");

      opt.SetRejectMessage("\nEntity is not a table.");

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

 

      var per = ed.GetEntity(opt);

      if (per.Status != PromptStatus.OK)

        return;

 

      using (var tr = db.TransactionManager.StartTransaction())

      {

        try

        {

          var obj = tr.GetObject(per.ObjectId, OpenMode.ForRead);

          var tb = obj as Table;

 

          // 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 data link from the spreadsheet

 

            var dlIds = tb.Cells.GetDataLink();

 

            foreach (ObjectId dlId in dlIds)

            {

              var 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 (Autodesk.AutoCAD.Runtime.Exception ex)

        {

          ed.WriteMessage("\nException: {0}", ex.Message);

        }

      }

    }

 

    [CommandMethod("T2S")]

    static public void UpdateSpreadsheetFromTable()

    {

      var doc =

        Application.DocumentManager.MdiActiveDocument;

      var db = doc.Database;

      var ed = doc.Editor;

 

      var opt =

        new PromptEntityOptions(

          "\nSelect table with spreadsheet to update"

        );

      opt.SetRejectMessage(

        "\nEntity is not a table."

      );

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

 

      var per = ed.GetEntity(opt);

      if (per.Status != PromptStatus.OK)

        return;

 

      using (var tr = db.TransactionManager.StartTransaction())

      {

        try

        {

          var obj = tr.GetObject(per.ObjectId, OpenMode.ForRead);

          var tb = obj as Table;

 

          // It should always be a table

          // but we'll check, just in case

 

          if (tb != null)

          {

            // The table must be open for write

 

            tb.Upg
radeOpen();

 

            // Update the data link from the table

 

            tb.UpdateDataLink(

              UpdateDirection.DataToSource,

              UpdateOption.ForceFullSourceUpdate

            );

 

            // And the spreadsheet from the data link

 

            var dlIds = tb.Cells.GetDataLink();

            foreach (ObjectId dlId in dlIds)

            {

              var dl =

                (DataLink)tr.GetObject(dlId, OpenMode.ForWrite);

              dl.Update(

                UpdateDirection.DataToSource,

                UpdateOption.ForceFullSourceUpdate

              );

            }

          }

          tr.Commit();

 

          ed.WriteMessage(

            "\nUpdated the spreadsheet from the table."

          );

        }

        catch (Autodesk.AutoCAD.Runtime.Exception ex)

        {

          ed.WriteMessage("\nException: {0}", ex.Message);

        }

      }

    }

 

    static public List<string> GetSheetNames(string excelFileName)

    {

      var listSheets = new List<string>();

 

      var excel = new Excel.Application();

      var wbs = excel.Workbooks.Open(excelFileName);

      foreach (Excel.Worksheet
sheet in wbs.Worksheets)

      {

        listSheets.Add(sheet.Name);

      }

      excel.Quit();

 

      return listSheets;

    }

 

    [CommandMethod("TFS")]

    static public void TableFromSpreadsheet()

    {

      const string dlName = "Import table from Excel demo";

 

      var doc =

        Application.DocumentManager.MdiActiveDocument;

      var db = doc.Database;

      var ed = doc.Editor;

 

      // Ask the user to select an XLS(X) file

 

      var ofd =

        new OpenFileDialog(

          "Select Excel spreadsheet to link",

          null,

          "xls; xlsx",

          "ExcelFileToLink",

          OpenFileDialog.OpenFileDialogFlags.

            DoNotTransferRemoteFiles

        );

 

      var dr = ofd.ShowDialog();

 

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

        return;

 

      // Display the name of the file and the contained sheets

 

      ed.WriteMessage(

        "\nFile selected was \"{0}\". Contains these sheets:",

        ofd.Filename

      );

 

      // First we get the sheet names

 

      var sheetNames = GetSheetNames(ofd.Filename);

 

      if (sheetNames.Count == 0)

      {

     

60;  ed.WriteMessage(

          "\nWorkbook doesn't contain any sheets."

        );

        return;

      }

 

      // And loop through, printing their names

 

      for (int i = 0; i < sheetNames.Count; i++)

      {

        var name = sheetNames[i];

 

        ed.WriteMessage("\n{0} - {1}", i + 1, name);

      }

 

      // Ask the user to select one

 

      var pio = new PromptIntegerOptions("\nSelect a sheet");

      pio.AllowNegative = false;

      pio.AllowZero = false;

      pio.DefaultValue = 1;

      pio.UseDefaultValue = true;

      pio.LowerLimit = 1;

      pio.UpperLimit = sheetNames.Count;

 

      var pir = ed.GetInteger(pio);

      if (pir.Status != PromptStatus.OK)

        return;

 

      // Ask the user to select a range of cells in the spreadsheet

 

      // We'll use a Regular Expression that matches a column (with

      // one or more letters) followed by a numeric row (which we're

      // naming "row1" so we can validate it's > 0 later),

      // followed by a colon and then the same (but with "row2")

 

      const string rangeExp =

        "^[A-Z]+(?<row1>[0-9]+):[A-Z]+(?<row2>[0-9]+)$";

      bool done = false;

      string range = "";

 

      do

      {

        var psr = ed.GetString("\nEnter cell range <entire sheet>");

        if (psr.Status != PromptStatus.OK)

          return;

 

        if (String.IsNullOrEmpty(psr.StringResult))

        {

          // Default is to select entire sheet

 

          done = true;

        }

        else

        {

          // If a string was entered, make sure it's a

          // valid cell range, which means it matches the

          // Regular Expression and has positive (non-zero)

          // row numbers

 

          var m =

            Regex.Match(

              psr.StringResult, rangeExp, RegexOptions.IgnoreCase

            );

          if (

            m.Success &&

            Int32.Parse(m.Groups["row1"].Value) > 0 &&

            Int32.Parse(m.Groups["row2"].Value) > 0

          )

          {

            done = true;

            range = psr.StringResult.ToUpper();           

          }

          else

          {

            ed.WriteMessage("\nInvalid range, please try again.");

          }

        }

      } while (!done);

 

      // Ask for the insertion point of the table

 

      var ppr = ed.GetPoint("\nEnter table insertion point");

      if (ppr.Status != PromptStatus.OK)

        return;

 

      try

      {

        // Remove any Data Link, if one exists already

 

        var dlm = db.DataLink
Manager;

        var dlId = dlm.GetDataLink(dlName);

        if (dlId != ObjectId.Null)

        {

          dlm.RemoveDataLink(dlId);

        }

 

        // Create and add the new Data Link, this time with

        // a direction connection to the selected sheet

 

        var dl = new DataLink();

        dl.DataAdapterId = "AcExcel";

        dl.Name = dlName;

        dl.Description = "Excel fun with Through the Interface";

        dl.ConnectionString =

          ofd.Filename +

          "!" + sheetNames[pir.Value - 1] +

          (String.IsNullOrEmpty(range) ? "" : "!" + range);

        dl.DataLinkOption = DataLinkOption.PersistCache;

        dl.UpdateOption |= (int)UpdateOption.AllowSourceUpdate;

 

        dlId = dlm.AddDataLink(dl);

 

        using (var tr = doc.TransactionManager.StartTransaction())

        {

          tr.AddNewlyCreatedDBObject(dl, true);

 

          var bt =

            (BlockTable)tr.GetObject(

              db.BlockTableId,

              OpenMode.ForRead

            );

 

          // Create our table

 

          var tb = new Table();

          tb.TableStyle = db.Tablestyle;

          tb.Position = ppr.Value;

          tb.Cells.SetDataLink(dlId, true);

          tb.GenerateLayout();

 

          // Add it to the drawing

 

          var btr =

            (BlockTableRecord)tr.GetObject(

              db.CurrentSpaceId,

              OpenMode.ForWrite

            );

 

          btr.AppendEntity(tb);

          tr.AddNewlyCreatedDBObject(tb, true);

          tr.Commit();

        }

      }

      catch (Autodesk.AutoCAD.Runtime.Exception ex)

      {

        ed.WriteMessage("\nException: {0}", ex.Message);

      }

    }

  }

}