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:
-
Creating an AutoCAD table linked to an Excel spreadsheet using .NET
-
Updating an AutoCAD table linked to an Excel spreadsheet using .NET
-
Updating an Excel spreadsheet from a linked AutoCAD table using .NET
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):
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;
using Autodesk.AutoCAD.Windows;
using System.Collections.Generic;
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 (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;
Transaction tr =
db.TransactionManager.StartTransaction();
using (tr)
{
try
{
DBObject obj =
tr.GetObject(per.ObjectId, OpenMode.ForRead);
Table 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 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 (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)
{
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 for the insertion point of the table
var ppr = ed.GetPoint("\nEnter table insertion point");
if (ppr.Status != PromptStatus.OK)
return;
// Remove any Data Link, if one exists already
var dlm = db.DataLinkManager;
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];
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();
}
}
}
}
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.
