找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

查看: 2260|回复: 2

[分享] Inserting a specific Excel sheet as an AutoCAD table using .NET

[复制链接]
发表于 2013-8-24 23:59:47 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
本帖最后由 Free-Lancer 于 2013-8-25 00:02 编辑

Inserting a specific Excel sheet as an AutoCAD table using .NET                         Last week I received the following question from Adam Schilling:
I have enjoyed your posts on .net programming for datalinks. I have searched high and low and haven’t been able to find answers or any support to help me with a small issue.  
My code (much of which was based off of your posts from 2007) works fine, except I cannot get it to use a different sheet from the workbook that is select.  Since the option to select a specific sheet is available when adding a link manually, I would think that it would be possible to programmatically do it also.
If it would work for a post great, if not, I apologize for taking your time.
Seeing as Adam asked so nicely (and I found the question to be of broad interest, which is also important), I went ahead and dusted off the 6-year old posts that he’d referred to. [Has it really been that long since I wrote them? Sigh.]
Here’s the series that I’ve refreshed for the purposes of this post:
It’s really the code from the first that has been modified to allow selection of a specific sheet, but the code was all in the same file (and the compiler warned of some obsolete functions), so I decided to update the other commands, too, rather than stripping them out.
The quick answer to Adam’s question is that you can specify a particular sheet to link to by passing its name (using the “!” character as a separator) into the DataLink’s ConnectionString property. e.g.:
dl.ConnectionString = "spreadsheet.xlsx!Sheet2";

That’s really all there is to it. But that would make for a pretty short post, so I went ahead and added some code that, for a selected spreadsheet, presents the list of contained sheets to the user and asks for one to be selected.
While not really being what the post is about – the main point is to show the AutoCAD side of things – there are a few different ways to access the contents of an Excel spreadsheet from .NET.
The one I’d personally have preferred to have used is the OleDb provider for Office. The problem with that approach relates to getting the right version of the component working for your app, even though 32- and 64-bit versions are both available (at least for Office 2010, for Office 2007 there’s just the 32-bit version).
Here’s my own situation, as an example: I’m running 32-bit Office on a 64-bit system (the main reason being that’s the way our IT department supports it, as far as I can tell), which means the installer doesn’t let me install the 64-bit component (probably because it needs to match the underlying Office version, which I suppose is fair enough). But while I can install the 32-bit version, my .NET application inside AutoCAD needs to be either x64 or “Any CPU”, and so leads to the “The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine” exception being thrown when the connection attempt is made.
Which led me back to using the COM library for Excel, instead (for which I added a COM project reference to the “Microsoft Excel 14.0 Object Library”). Using this component actually fires up an instance of Excel in the background – which certainly seems like overkill just to access the list of sheets contained in a spreadsheet – but it’s reliable and easy to get working. And the code is certainly more readable.
Speaking of the code, here’s the updated C# code letting a user select a specific Excel sheet for insertion (in the TFS command):
  1. using Autodesk.AutoCAD.ApplicationServices;
  2. using Autodesk.AutoCAD.DatabaseServices;
  3. using Autodesk.AutoCAD.EditorInput;
  4. using Autodesk.AutoCAD.Runtime;
  5. using Autodesk.AutoCAD.Windows;
  6. using System.Collections.Generic;
  7. using Excel = Microsoft.Office.Interop.Excel;

  8. namespace LinkToExcel
  9. {
  10.   public class Commands
  11.   {
  12.     [CommandMethod("S2T")]
  13.     static public void UpdateTableFromSpreadsheet()
  14.     {
  15.       var doc =
  16.         Application.DocumentManager.MdiActiveDocument;
  17.       var db = doc.Database;
  18.       var ed = doc.Editor;
  19.       var opt = new PromptEntityOptions("\nSelect table to update");
  20.       opt.SetRejectMessage("\nEntity is not a table.");
  21.       opt.AddAllowedClass(typeof(Table), false);      
  22.       var per = ed.GetEntity(opt);
  23.       if (per.Status != PromptStatus.OK)
  24.         return;
  25.       using (var tr = db.TransactionManager.StartTransaction())
  26.      {
  27.         try
  28.         {
  29.           var obj = tr.GetObject(per.ObjectId, OpenMode.ForRead);
  30.           var tb = obj as Table;

  31.          // It should always be a table
  32.           // but we'll check, just in case

  33.           if (tb != null)
  34.           {
  35.             // The table must be open for write
  36.             tb.UpgradeOpen();
  37.             // Update data link from the spreadsheet
  38.             var dlIds = tb.Cells.GetDataLink();
  39.             foreach (ObjectId dlId in dlIds)
  40.             {
  41.               var dl =
  42.                 (DataLink)tr.GetObject(dlId, OpenMode.ForWrite);
  43.               dl.Update(
  44.                 UpdateDirection.SourceToData,
  45.                 UpdateOption.None
  46.              );

  47.               // And the table from the data link

  48.               tb.UpdateDataLink(
  49.                 UpdateDirection.SourceToData,
  50.                 UpdateOption.None
  51.               );
  52.             }
  53.           }
  54.           tr.Commit();
  55.           ed.WriteMessage(
  56.             "\nUpdated the table from the spreadsheet."
  57.           );
  58.         }
  59.         catch (Exception ex)
  60.         {
  61.           ed.WriteMessage(
  62.             "\nException: {0}",
  63.             ex.Message
  64.           );
  65.         }
  66.       }
  67.     }

  68.     [CommandMethod("T2S")]

  69.     static public void UpdateSpreadsheetFromTable()
  70.     {
  71.       var doc =
  72.         Application.DocumentManager.MdiActiveDocument;
  73.       var db = doc.Database;
  74.       var ed = doc.Editor;
  75.       var opt =
  76.         new PromptEntityOptions(
  77.           "\nSelect table with spreadsheet to update"
  78.         );
  79.       opt.SetRejectMessage(
  80.         "\nEntity is not a table."
  81.       );

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

  83.       var per = ed.GetEntity(opt);
  84.       if (per.Status != PromptStatus.OK)
  85.         return;
  86.       Transaction tr =
  87.         db.TransactionManager.StartTransaction();
  88.       using (tr)
  89.       {
  90.         try
  91.         {
  92.           DBObject obj =
  93.             tr.GetObject(per.ObjectId, OpenMode.ForRead);
  94.           Table tb = obj as Table;

  95.           // It should always be a table
  96.           // but we'll check, just in case

  97.           if (tb != null)
  98.           {
  99.             // The table must be open for write
  100.             tb.UpgradeOpen();

  101.             // Update the data link from the table

  102.             tb.UpdateDataLink(
  103.               UpdateDirection.DataToSource,
  104.               UpdateOption.ForceFullSourceUpdate
  105.             );
  106.             // And the spreadsheet from the data link

  107.             var dlIds = tb.Cells.GetDataLink();
  108.             foreach (ObjectId dlId in dlIds)
  109.             {
  110.               var dl =
  111.                 (DataLink)tr.GetObject(dlId, OpenMode.ForWrite);

  112.               dl.Update(
  113.                 UpdateDirection.DataToSource,
  114.                 UpdateOption.ForceFullSourceUpdate
  115.               );
  116.             }
  117.           }
  118.           tr.Commit();
  119.           ed.WriteMessage(
  120.             "\nUpdated the spreadsheet from the table."
  121.           );
  122.         }
  123.         catch (Exception ex)
  124.         {
  125.           ed.WriteMessage("\nException: {0}", ex.Message);
  126.         }
  127.       }
  128.     }

  129.     static public List<string> GetSheetNames(string excelFileName)
  130.     {
  131.       var listSheets = new List<string>();
  132.       var excel = new Excel.Application();
  133.       var wbs = excel.Workbooks.Open(excelFileName);
  134.       foreach (Excel.Worksheet sheet in wbs.Worksheets)

  135.       {
  136.         listSheets.Add(sheet.Name);
  137.       }
  138.       excel.Quit();
  139.       return listSheets;

  140.     }



  141.     [CommandMethod("TFS")]

  142.     static public void TableFromSpreadsheet()
  143.     {
  144.       const string dlName = "Import table from Excel demo";
  145.       var doc =
  146.         Application.DocumentManager.MdiActiveDocument;
  147.       var db = doc.Database;
  148.       var ed = doc.Editor;

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

  150.       var ofd =
  151.         new OpenFileDialog(
  152.           "Select Excel spreadsheet to link",
  153.           null,
  154.           "xls; xlsx",
  155.           "ExcelFileToLink",
  156.           OpenFileDialog.OpenFileDialogFlags.
  157.             DoNotTransferRemoteFiles
  158.         );

  159.       var dr = ofd.ShowDialog();



  160.       if (dr != System.Windows.Forms.DialogResult.OK)
  161.         return;

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

  163.       ed.WriteMessage(
  164.         "\nFile selected was \"{0}\". Contains these sheets:",
  165.         ofd.Filename
  166.       );

  167.       // First we get the sheet names

  168.       var sheetNames = GetSheetNames(ofd.Filename);
  169.       if (sheetNames.Count == 0)
  170.       {
  171.         ed.WriteMessage(
  172.           "\nWorkbook doesn't contain any sheets."
  173.         );
  174.         return;
  175.       }



  176.       // And loop through, printing their names

  177.       for (int i=0; i < sheetNames.Count; i++)
  178.       {
  179.         var name = sheetNames;
  180.         ed.WriteMessage("\n{0} - {1}", i + 1, name);
  181.       }

  182.       // Ask the user to select one

  183.       var pio = new PromptIntegerOptions("\nSelect a sheet");
  184.       pio.AllowNegative = false;
  185.       pio.AllowZero = false;
  186.       pio.DefaultValue = 1;
  187.       pio.UseDefaultValue = true;
  188.       pio.LowerLimit = 1;
  189.       pio.UpperLimit = sheetNames.Count;

  190.       var pir = ed.GetInteger(pio);
  191.       if (pir.Status != PromptStatus.OK)
  192.         return;

  193.       // Ask for the insertion point of the table

  194.       var ppr = ed.GetPoint("\nEnter table insertion point");
  195.       if (ppr.Status != PromptStatus.OK)
  196.         return;

  197.       // Remove any Data Link, if one exists already

  198.       var dlm = db.DataLinkManager;
  199.       var dlId = dlm.GetDataLink(dlName);
  200.       if (dlId != ObjectId.Null)
  201.       {
  202.         dlm.RemoveDataLink(dlId);
  203.       }

  204.       // Create and add the new Data Link, this time with
  205.       // a direction connection to the selected sheet

  206.       var dl = new DataLink();
  207.       dl.DataAdapterId = "AcExcel";
  208.       dl.Name = dlName;
  209.       dl.Description = "Excel fun with Through the Interface";
  210.       dl.ConnectionString =
  211.         ofd.Filename + "!" + sheetNames[pir.Value - 1];
  212.       dl.DataLinkOption =
  213.         DataLinkOption.PersistCache;
  214.       dl.UpdateOption |=
  215.         (int)UpdateOption.AllowSourceUpdate;
  216.       dlId = dlm.AddDataLink(dl);
  217.       using (var tr = doc.TransactionManager.StartTransaction())
  218.       {
  219.         tr.AddNewlyCreatedDBObject(dl, true);
  220.         var bt =
  221.           (BlockTable)tr.GetObject(
  222.             db.BlockTableId,
  223.             OpenMode.ForRead
  224.           );

  225.         // Create our table

  226.         var tb = new Table();
  227.         tb.TableStyle = db.Tablestyle;
  228.         tb.Position = ppr.Value;
  229.         tb.Cells.SetDataLink(dlId, true);
  230.         tb.GenerateLayout();

  231.         // Add it to the drawing

  232.         var btr =
  233.           (BlockTableRecord)tr.GetObject(
  234.             db.CurrentSpaceId,
  235.             OpenMode.ForWrite
  236.           );
  237.         btr.AppendEntity(tb);
  238.         tr.AddNewlyCreatedDBObject(tb, true);
  239.         tr.Commit();
  240.       }
  241.     }
  242.   }
  243. }

When we run the TFS command, we see the user gets presented with the usual file selection dialog, but then a command-line interface for choosing a specific sheet from the selected spreadsheet:


Command: TFS

File selected was "C:\Data\Spreadsheet.xlsx". Contains these sheets:
1 - Sheet1
2 - Sheet2
3 - Sheet3
Select a sheet <1>: 2
Enter table insertion point:
Which results in the specified sheet getting inserted as a table into the current AutoCAD drawing.

评分

参与人数 1D豆 +5 收起 理由
ScmTools + 5 很给力!经验;技术要点;资料分享奖!

查看全部评分

论坛插件加载方法
发帖求助前要善用【论坛搜索】功能,那里可能会有你要找的答案;
如果你在论坛求助问题,并且已经从坛友或者管理的回复中解决了问题,请把帖子标题加上【已解决】;
如何回报帮助你解决问题的坛友,一个好办法就是给对方加【D豆】,加分不会扣除自己的积分,做一个热心并受欢迎的人!

已领礼包: 1632个

财富等级: 堆金积玉

发表于 2013-8-25 10:20:03 | 显示全部楼层
支持一个!主题很好!
论坛插件加载方法
发帖求助前要善用【论坛搜索】功能,那里可能会有你要找的答案;
如果你在论坛求助问题,并且已经从坛友或者管理的回复中解决了问题,请把帖子标题加上【已解决】;
如何回报帮助你解决问题的坛友,一个好办法就是给对方加【D豆】,加分不会扣除自己的积分,做一个热心并受欢迎的人!
回复 支持 反对

使用道具 举报

已领礼包: 1个

财富等级: 恭喜发财

发表于 2020-12-29 13:04:40 | 显示全部楼层
能更详细写一下怎么获取Excel的range,比如“A1:H7”吗?
论坛插件加载方法
发帖求助前要善用【论坛搜索】功能,那里可能会有你要找的答案;
如果你在论坛求助问题,并且已经从坛友或者管理的回复中解决了问题,请把帖子标题加上【已解决】;
如何回报帮助你解决问题的坛友,一个好办法就是给对方加【D豆】,加分不会扣除自己的积分,做一个热心并受欢迎的人!
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|申请友链|Archiver|手机版|小黑屋|辽公网安备|晓东CAD家园 ( 辽ICP备15016793号 )

GMT+8, 2024-11-22 17:58 , Processed in 0.355812 second(s), 39 queries , Gzip On.

Powered by Discuz! X3.5

© 2001-2024 Discuz! Team.

快速回复 返回顶部 返回列表