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

本文提供了一段C#代码,用于从AutoCAD中的链接表格更新Excel电子表格。用户需要先解锁表格,然后运行代码,即使Excel打开着也能工作,但需关闭并重新打开文件才能看到更新。代码包括对表格读写操作,以及异常处理。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

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:

Unlocking_the_table

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:

Overwrite_spreadsheet

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

 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值