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

关注

原创最新推荐文章于 2026-11-09 17:45:17 发布 866 阅读 AI 写同款· GEO 优化 ›

Excel 数据分析常用函数汇总很多传统行业的数据分析师只要求掌握 Excel 即可，会 SPSS / SAS 是加分项。哪怕在数据分析岗位很多的互联网行业，Excel 依旧是不可替代的基础工具。Excel 是数据分析最常用、最基础的工具。大量传统行业（商贸、工厂、门店进销存）的数据分析师，熟练掌握 Excel + SQL 就可以完成绝大多数日常工作。对于初学者，不用一上来就啃 Python、R 这类编程语言，Excel 自带的函数、透视表、可视化插件足够支撑大部分统计、核对、报表需求；阅读详情

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