Download - Through the Interface_ Excel
-
7/22/2019 Through the Interface_ Excel
1/19
Through the Interface
Excel
February 05, 201 4
Inserting a cell range from an Excel sheet as an AutoCAD table using .NET
I wasnt planning on cov ering this particular topic today , but then this comment came in ov ernight and Iended up taking a look into it. Paul has been try ing to append a specific cell range to his co nnection string
when creating a data-link for an Excel spreadsheet, adding to the c ode from this previous post.
I gav e it a try myself with a hardcode d cell range and it seemed to work fine, and so went ahead and modified
the implementation of the TFS command to ask the user to enter their own cell range. Whats probably most
interesting about the code in this post is its use of a regular expression something I dont use often enough,
in truth which is a great way o f validating that strings conform to a particular pattern.
Its also possible to apply gro up labels within a regex to tag groups of characters for later extraction and
validation. We use this tec hnique to check that the ro w numbers are both greater than 0 , for instanc e
(A0:B3 and A10 :B0 are both invalid cell ranges that otherwise meet the pattern we define).
There may well be a better way to do this within a regex as Ive said, I do nt use them as often as I should
but ho pefully the ov erall tec hnique of using them will b e a helpful reminder for peo ple .
On a more general note, this kind of input v alidation is extremely important if youre asking for user input
and then using it to build database queries: if you dont protect against arbitrary str ings being entered then
your application will be wide open to SQL Injection attacks. This isnt something thats as important with
desktop applications acce ssing local databases, perhaps, but as applications mov e more to the web this
becomes absolutely critic al (as the unfortunate autocomplete snafu on healthcare.gov highlighted a couple
of months ago).
Heres the updated C# code... the TFS command is the only section thats had significant changes from theprev ious version, in case:
usingSystem;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
usingAutodesk.AutoCAD.Runtime;
using Autodesk.AutoCAD.Windows;
usingExcel = Microsoft.Office.Interop.Excel;
namespaceLinkToExcel{
publicclassCommands
{
[CommandMethod("S2T")]
staticpublicvoid UpdateTableFromSpreadsheet()
{
vardoc =
Application.DocumentManager.MdiActiveDocument;
vardb = doc.Database;
vared = doc.Editor;
varopt = newPromptEntityOptions("\nSelect table to update");
opt.SetRejectMessage("\nEntity is not a table.");
opt.AddAllowedClass(typeof(Table), false);
varper = ed.GetEntity(opt);
if(per.Status != PromptStatus.OK)
return;
http://sitesdoneright.com/blog/2013/11/sql-injection-hacking-on-healthcare-govhttp://en.wikipedia.org/wiki/SQL_injectionhttp://through-the-interface.typepad.com/through_the_interface/2013/06/inserting-a-specific-excel-sheet-as-an-autocad-table-using-net.html#comment-6a00d83452464869e201a511647d30970chttp://through-the-interface.typepad.com/through_the_interface/http://through-the-interface.typepad.com/through_the_interface/http://sitesdoneright.com/blog/2013/11/sql-injection-hacking-on-healthcare-govhttp://en.wikipedia.org/wiki/SQL_injectionhttp://en.wikipedia.org/wiki/Regular_expressionhttp://through-the-interface.typepad.com/through_the_interface/2013/06/inserting-a-specific-excel-sheet-as-an-autocad-table-using-net.html#comment-6a00d83452464869e201a511647d30970chttp://through-the-interface.typepad.com/through_the_interface/2013/06/inserting-a-specific-excel-sheet-as-an-autocad-table-using-net.htmlhttp://through-the-interface.typepad.com/through_the_interface/2013/06/inserting-a-specific-excel-sheet-as-an-autocad-table-using-net.html#comment-6a00d83452464869e201a3fcb496a5970bhttp://through-the-interface.typepad.com/through_the_interface/2014/02/inserting-a-cell-range-from-an-excel-sheet-as-an-autocad-table-using-net.htmlhttp://through-the-interface.typepad.com/through_the_interface/ -
7/22/2019 Through the Interface_ Excel
2/19
using(vartr = db.TransactionManager.StartTransaction())
{
try
{
varobj = tr.GetObject(per.ObjectId, OpenMode.ForRead);
vartb = obj asTable;
// 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
vardlIds = tb.Cells.GetDataLink();
foreach(ObjectIddlId indlIds)
{
vardl =
(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.Exceptionex)
{
ed.WriteMessage("\nException: {0}", ex.Message);
}
}
}
[CommandMethod("T2S")]
staticpublicvoid UpdateSpreadsheetFromTable()
{
vardoc =
Application.DocumentManager.MdiActiveDocument;
vardb = doc.Database;
vared = doc.Editor;
varopt =
newPromptEntityOptions(
"\nSelect table with spreadsheet to update"
);
opt.SetRejectMessage(
"\nEntity is not a table."
);
opt.AddAllowedClass(typeof(Table), false);
varper = ed.GetEntity(opt);
-
7/22/2019 Through the Interface_ Excel
3/19
if(per.Status != PromptStatus.OK)
return;
using(vartr = db.TransactionManager.StartTransaction())
{
try
{
varobj = tr.GetObject(per.ObjectId, OpenMode.ForRead);
vartb = obj asTable;
// 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
vardlIds = tb.Cells.GetDataLink();
foreach(ObjectIddlId indlIds)
{
vardl =
(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.Exceptionex)
{
ed.WriteMessage("\nException: {0}", ex.Message);
}
}
}
staticpublicList GetSheetNames(stringexcelFileName)
{
varlistSheets = newList();
varexcel = newExcel.Application();
varwbs = excel.Workbooks.Open(excelFileName);
foreach(Excel.Worksheetsheet inwbs.Worksheets)
{
listSheets.Add(sheet.Name);
}
excel.Quit();
returnlistSheets;
}
[CommandMethod("TFS")]
-
7/22/2019 Through the Interface_ Excel
4/19
staticpublicvoidTableFromSpreadsheet()
{
conststringdlName = "Import table from Excel demo";
vardoc =
Application.DocumentManager.MdiActiveDocument;
vardb = doc.Database;
vared = doc.Editor;
// Ask the user to select an XLS(X) file
varofd =
newOpenFileDialog(
"Select Excel spreadsheet to link",
null,
"xls; xlsx",
"ExcelFileToLink",
OpenFileDialog.OpenFileDialogFlags.
DoNotTransferRemoteFiles
);
vardr = 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
varsheetNames = GetSheetNames(ofd.Filename);
if(sheetNames.Count == 0)
{
ed.WriteMessage(
"\nWorkbook doesn't contain any sheets."
);
return;
}
// And loop through, printing their names
for(inti = 0; i < sheetNames.Count; i++)
{ varname = sheetNames[i];
ed.WriteMessage("\n{0} - {1}", i + 1, name);
}
// Ask the user to select one
varpio = newPromptIntegerOptions("\nSelect a sheet");
pio.AllowNegative = false;
pio.AllowZero = false;
pio.DefaultValue = 1;
pio.UseDefaultValue = true;
pio.LowerLimit = 1; pio.UpperLimit = sheetNames.Count;
varpir = ed.GetInteger(pio);
if(pir.Status != PromptStatus.OK)
return;
-
7/22/2019 Through the Interface_ Excel
5/19
// 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")
conststringrangeExp =
"^[A-Z]+(?[0-9]+):[A-Z]+(?[0-9]+)$" ;
booldone = false;
stringrange = "";
do
{
varpsr = ed.GetString("\nEnter cell range ");
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
varm =
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
varppr = ed.GetPoint("\nEnter table insertion point");
if(ppr.Status != PromptStatus.OK)
return;
try
{
// Remove any Data Link, if one exists already
vardlm = db.DataLinkManager;
vardlId = dlm.GetDataLink(dlName);
if(dlId != ObjectId.Null)
{
dlm.RemoveDataLink(dlId);
}
-
7/22/2019 Through the Interface_ Excel
6/19
// Create and add the new Data Link, this time with
// a direction connection to the selected sheet
vardl = newDataLink();
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(vartr = doc.TransactionManager.StartTransaction())
{
tr.AddNewlyCreatedDBObject(dl, true);
varbt =
(BlockTable)tr.GetObject(
db.BlockTableId,
OpenMode.ForRead
);
// Create our table
vartb = newTable();
tb.TableStyle = db.Tablestyle;
tb.Position = ppr.Value;
tb.Cells.SetDataLink(dlId, true);
tb.GenerateLayout();
// Add it to the drawing
varbtr =
(BlockTableRecord)tr.GetObject(
db.CurrentSpaceId,
OpenMode.ForWrite
);
btr.AppendEntity(tb);
tr.AddNewlyCreatedDBObject(tb, true);
tr.Commit();
}
}
catch (Autodesk.AutoCAD.Runtime.Exceptionex)
{
ed.WriteMessage("\nException: {0}", ex.Message);
}
}
}
}
Posted at 05:33 PM in Auto CAD, A utoCAD .NET, Exc el, Selection, Tables | Permalink | 6 Comments
June 18, 201 3
Inserting a specific Excel sheet as an AutoCAD table using .NET
Last week I received the following question from A dam Schilling:
I have e njoye d your posts on .ne t pro gramming fo r datalinks. I have searc he d high and low and
have nt been able to find answers or any support to help me w ith a small issue.
My code (much o f wh ich w as based off of yo ur posts from 2007 ) works fine , except I cannot get it to
http://through-the-interface.typepad.com/through_the_interface/2013/06/inserting-a-specific-excel-sheet-as-an-autocad-table-using-net.htmlhttp://through-the-interface.typepad.com/through_the_interface/2014/02/inserting-a-cell-range-from-an-excel-sheet-as-an-autocad-table-using-net.html#disqus_threadhttp://through-the-interface.typepad.com/through_the_interface/2014/02/inserting-a-cell-range-from-an-excel-sheet-as-an-autocad-table-using-net.htmlhttp://through-the-interface.typepad.com/through_the_interface/tables/http://through-the-interface.typepad.com/through_the_interface/selection/http://through-the-interface.typepad.com/through_the_interface/excel/http://through-the-interface.typepad.com/through_the_interface/autocad_net/http://through-the-interface.typepad.com/through_the_interface/autocad/ -
7/22/2019 Through the Interface_ Excel
7/19
use a different sheet from the wo rkbook that is select. Since the option to select a specific sheet is
available when adding a link manually, I w ould think that it would be possible to programmatically do
it also.
If it would work for a post great, if not, I apologize fo r taking yo ur time .
Seeing as Adam asked so nice ly (and I found the question to be o f broad interest, which is also important), I
went ahead and dusted off the 6-year o ld posts that hed referred to . [Has it really been that long since I wrote
them? Sigh.]
Heres the series that Ive refreshed for the purposes o f this post:
1 . Creating an AutoCAD table linked to an Exc el spreadsheet using .NET
2. Updating an AutoCAD table linked to an Excel spreadsheet using .NET
3. Updating an Exc el spreadsheet from a linked AutoCAD table using .NET
Its really the c ode 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 c ompiler warned of some obsolete functio ns), so I dec ided to update the
other c ommands, too, rather than stripping them out.
The quick answer to A dams question is that yo u can specify a particular sheet to link to by passing its name
(using the ! character as a separator) into the DataLinks ConnectionString property . e.g.:
dl.ConnectionString = "spreadsheet.xlsx!Sheet2" ;
Thats 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 po int is to sho w the A utoCA D side of things there
are a few different ways to access the co ntents of an Exc el spreadsheet from .NET.
The one Id personally have preferred to hav e used is the OleDb prov ider for Office. The problem with that
approac h relates to getting the right version of the component working for y our app, ev en though 32- and
64-bit ver sions are both av ailable (at least for Office 201 0, for Office 2007 theres just the 32-bit version).
Heres my own situation, as an example: Im running 32-bit Office on a 64-bit sy stem (the main reason being
thats the way our IT department suppo rts it, as far as I can tell), which means the installer doesnt let me
install the 64-bit component (probably be cause it needs to match the underly ing Office v ersion, which I
suppose is fair enough). But while I c an install the 32-bit v ersion, my .NET application inside AutoCAD needs
to be either x 64 or Any CPU, and so leads to the The 'Microsoft.ACE.OLEDB.12.0' prov ider is not
registered on the local machine ex cept ion being thrown when the connection attempt is made.
Which led me back to using the COM library for Exc el, instead (for whic h I added a COM project reference to
the Microso ft Exc el 14 .0 Object Library). Using this component actually fires up an instance o f Excel in the
background which certainly seems like overkill just to access the list o f sheets contained in a spreadsheet but it s reliable and easy to get wo rking. And the code is certainly more readable.
Speaking of the co de, here s the updated C# code letting a user selec t a specific Exc el 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;
usingExcel = Microsoft.Office.Interop.Excel;
namespaceLinkToExcel
{
publicclassCommands
{
[CommandMethod("S2T")]
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/1d5c04c7-157f-4955-a14b-41d912d50a64http://www.microsoft.com/en-us/download/details.aspx?id=23734http://www.microsoft.com/en-us/download/details.aspx?id=13255http://stackoverflow.com/a/13677840http://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-exc.htmlhttp://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-aut.htmlhttp://through-the-interface.typepad.com/through_the_interface/2007/08/creating-an-aut.html -
7/22/2019 Through the Interface_ Excel
8/19
staticpublicvoid UpdateTableFromSpreadsheet()
{
vardoc =
Application.DocumentManager.MdiActiveDocument;
vardb = doc.Database;
vared = doc.Editor;
varopt = newPromptEntityOptions("\nSelect table to update");
opt.SetRejectMessage("\nEntity is not a table.");
opt.AddAllowedClass(typeof(Table), false);
varper = ed.GetEntity(opt);
if(per.Status != PromptStatus.OK)
return;
using(vartr = db.TransactionManager.StartTransaction())
{
try
{
varobj = tr.GetObject(per.ObjectId, OpenMode.ForRead);
vartb = obj asTable;
// 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
vardlIds = tb.Cells.GetDataLink();
foreach(ObjectIddlId indlIds)
{
vardl =
(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(Exceptionex)
{
ed.WriteMessage(
"\nException: {0}",
ex.Message
);
}
}
}
[CommandMethod("T2S")]
-
7/22/2019 Through the Interface_ Excel
9/19
staticpublicvoid UpdateSpreadsheetFromTable()
{
vardoc =
Application.DocumentManager.MdiActiveDocument;
vardb = doc.Database;
vared = doc.Editor;
varopt =
newPromptEntityOptions(
"\nSelect table with spreadsheet to update"
); opt.SetRejectMessage(
"\nEntity is not a table."
);
opt.AddAllowedClass(typeof(Table), false);
varper = ed.GetEntity(opt);
if(per.Status != PromptStatus.OK)
return;
Transactiontr =
db.TransactionManager.StartTransaction();
using(tr)
{ try
{
DBObjectobj =
tr.GetObject(per.ObjectId, OpenMode.ForRead);
Tabletb = obj asTable;
// 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
vardlIds = tb.Cells.GetDataLink();
foreach(ObjectIddlId indlIds)
{
vardl =
(DataLink)tr.GetObject(dlId, OpenMode.ForWrite);
dl.Update(
UpdateDirection.DataToSource,
UpdateOption.ForceFullSourceUpdate
);
}
}
tr.Commit();
ed.WriteMessage(
"\nUpdated the spreadsheet from the table."
);
}
catch(Exceptionex)
{
ed.WriteMessage("\nException: {0}", ex.Message);
-
7/22/2019 Through the Interface_ Excel
10/19
}
}
}
staticpublicList GetSheetNames(stringexcelFileName)
{
varlistSheets = newList();
varexcel = newExcel.Application();
varwbs = excel.Workbooks.Open(excelFileName);
foreach(Excel.Worksheetsheet inwbs.Worksheets) {
listSheets.Add(sheet.Name);
}
excel.Quit();
returnlistSheets;
}
[CommandMethod("TFS")]
staticpublicvoidTableFromSpreadsheet()
{
conststringdlName = "Import table from Excel demo";
vardoc =
Application.DocumentManager.MdiActiveDocument;
vardb = doc.Database;
vared = doc.Editor;
// Ask the user to select an XLS(X) file
varofd =
newOpenFileDialog(
"Select Excel spreadsheet to link",
null,
"xls; xlsx",
"ExcelFileToLink",
OpenFileDialog.OpenFileDialogFlags.
DoNotTransferRemoteFiles
);
vardr = 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
varsheetNames = GetSheetNames(ofd.Filename);
if(sheetNames.Count == 0)
{
ed.WriteMessage(
"\nWorkbook doesn't contain any sheets."
); return;
}
// And loop through, printing their names
-
7/22/2019 Through the Interface_ Excel
11/19
for(inti=0; i < sheetNames.Count; i++)
{
varname = sheetNames[i];
ed.WriteMessage("\n{0} - {1}", i + 1, name);
}
// Ask the user to select one
varpio = newPromptIntegerOptions("\nSelect a sheet");
pio.AllowNegative = false;
pio.AllowZero = false;
pio.DefaultValue = 1;
pio.UseDefaultValue = true;
pio.LowerLimit = 1;
pio.UpperLimit = sheetNames.Count;
varpir = ed.GetInteger(pio);
if(pir.Status != PromptStatus.OK)
return;
// Ask for the insertion point of the table
varppr = ed.GetPoint("\nEnter table insertion point");
if(ppr.Status != PromptStatus.OK)
return;
// Remove any Data Link, if one exists already
vardlm = db.DataLinkManager;
vardlId = 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
vardl = newDataLink();
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(vartr = doc.TransactionManager.StartTransaction())
{
tr.AddNewlyCreatedDBObject(dl, true);
varbt =
(BlockTable)tr.GetObject(
db.BlockTableId,
OpenMode.ForRead
);
// Create our table
vartb = newTable();
tb.TableStyle = db.Tablestyle;
tb.Position = ppr.Value;
-
7/22/2019 Through the Interface_ Excel
12/19
tb.Cells.SetDataLink(dlId, true);
tb.GenerateLayout();
// Add it to the drawing
varbtr =
(BlockTableRecord)tr.GetObject(
db.CurrentSpaceId,
OpenMode.ForWrite
);
btr.AppendEntity(tb);
tr.AddNewlyCreatedDBObject(tb, true);
tr.Commit();
}
}
}
}
When we run the TFS co mmand, we see the user gets presented with the usual file sele ctio n 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 : 2
Enter table insertion point:
Which results in the spe cified sheet getting inserted as a table into the current A utoCAD drawing.
Posted at 09:58 A M in AutoCAD, Auto CAD .NET, Exc el, Selection, Tables | Permalink | 6 Comments
August 27 , 200 7
Updating an Excel spreadsheet from a linked AutoCAD table using .NET
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 Exc el 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;
namespaceLinkToExcel
{
publicclassCommands
{
[CommandMethod("T2S")]
staticpublicvoid UpdateSpreadsheetFromTable()
{
Documentdoc =
Application.DocumentManager.MdiActiveDocument;
Databasedb = doc.Database;
Editored = doc.Editor;
PromptEntityOptionsopt =
newPromptEntityOptions(
"\nSelect table with spreadsheet to update: "
);
opt.SetRejectMessage(
"\nEntity is not a table."
http://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-aut.htmlhttp://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-exc.htmlhttp://through-the-interface.typepad.com/through_the_interface/2013/06/inserting-a-specific-excel-sheet-as-an-autocad-table-using-net.html#disqus_threadhttp://through-the-interface.typepad.com/through_the_interface/2013/06/inserting-a-specific-excel-sheet-as-an-autocad-table-using-net.htmlhttp://through-the-interface.typepad.com/through_the_interface/tables/http://through-the-interface.typepad.com/through_the_interface/selection/http://through-the-interface.typepad.com/through_the_interface/excel/http://through-the-interface.typepad.com/through_the_interface/autocad_net/http://through-the-interface.typepad.com/through_the_interface/autocad/ -
7/22/2019 Through the Interface_ Excel
13/19
);
opt.AddAllowedClass(typeof(Table), false);
PromptEntityResultper =
ed.GetEntity(opt);
if(per.Status != PromptStatus.OK)
return;
Transactiontr =
db.TransactionManager.StartTransaction();
using(tr)
{
try
{
DBObjectobj =
tr.GetObject(
per.ObjectId,
OpenMode.ForRead
);
Tabletb = (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
ObjectIddlId = tb.GetDataLink(0, 0);
DataLinkdl =
(DataLink)tr.GetObject(
dlId,
OpenMode.ForWrite
);
dl.Update(
UpdateDirection.DataToSource,
UpdateOption.ForceFullSourceUpdate
);
}
tr.Commit();
ed.WriteMessage(
"\nUpdated the spreadsheet from the table."
);
}
catch(Exceptionex)
{ ed.WriteMessage(
"\nException: {0}",
ex.Message
);
}
-
7/22/2019 Through the Interface_ Excel
14/19
}
}
}
}
Tables with linked spreadsheets are locked by default and display this glyph when y ou hover ov er them: .
Before y ou run the code y ou will need to unloc k the table by r ight-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 co ntents will not
be updated automatically - you hav e to c lose and reopen the file to see the results. And y ou will probably see
this dialog co me up twice:
For the best (most logical) results, the T2S command should really be run when the spreadsheet is not open
in Excel. I ex pect it's possible to determine whether a spre adsheet is open in Exc el from using standard file
access functions in .NET (reque sting exc lusive ac cess, to see whether it's possible to get it), but that's being
left as an exe rcise for the reader (or for another day , at least :-).
For y our conv enience, here's a sourc e file containing the code from the last three posts (command
implementations for TFS, S2T and T2S).
Posted at 02:15 PM in AutoCAD, AutoCAD .NET, Exc el, Tables | Permalink | 1 Comment | TrackBack (0)
http://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-exc.html#trackbackhttp://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-exc.html#disqus_threadhttp://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-exc.htmlhttp://through-the-interface.typepad.com/through_the_interface/tables/http://through-the-interface.typepad.com/through_the_interface/excel/http://through-the-interface.typepad.com/through_the_interface/autocad_net/http://through-the-interface.typepad.com/through_the_interface/autocad/http://through-the-interface.typepad.com/through_the_interface/files/excel-table.cs -
7/22/2019 Through the Interface_ Excel
15/19
August 24, 20 07
Updating an AutoCAD table linked to an Excel spreadsheet using .NET
Thanks to Viru Aithal, from DevTec h India, for providing the c ode for this post (I co nverted the C# co de
below from some C++ he had sent to a developer).
In the last post we showed how to create a table linked to an Excel spreadsheet using .NET in AutoCAD 200 8.
AutoCAD does a great job o f loo king for c hanges in the Exc el spreadsheet, and asking whether y ou want to
update the linked table:
There may be times, howeve r, when y ou want to force the update programmatically, whether from the
spreadsheet to the table ot v ice-versa. In this post we'll show the code to update the table from the
spreadsheet, and in the next post we'll see some code to update the spre adsheet from the table (should it
have been unlocked and edited).Here's the C# code:
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;
namespaceLinkToExcel
{
publicclassCommands
{
[CommandMethod("S2T")]
staticpublicvoid UpdateTableFromSpreadsheet()
{
Documentdoc =
Application.DocumentManager.MdiActiveDocument;
Databasedb = doc.Database;
Editored = doc.Editor;
PromptEntityOptionsopt =
newPromptEntityOptions(
"\nSelect table to update: "
);
opt.SetRejectMessage(
"\nEntity is not a table."
);
opt.AddAllowedClass(typeof(Table), false);
PromptEntityResultper =
ed.GetEntity(opt);
if(per.Status != PromptStatus.OK)
return;
Transactiontr =
db.TransactionManager.StartTransaction();
using(tr) {
try
{
DBObjectobj =
tr.GetObject(
http://through-the-interface.typepad.com/through_the_interface/2007/08/creating-an-aut.htmlhttp://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-aut.html -
7/22/2019 Through the Interface_ Excel
16/19
per.ObjectId,
OpenMode.ForRead
);
Tabletb = (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 spreadsheet
ObjectIddlId = tb.GetDataLink(0, 0);
DataLinkdl =
(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(Exceptionex)
{
ed.WriteMessage(
"\nException: {0}",
ex.Message
);
}
}
}
}
}
When y ou run the S2T (for Spreadsheet-to-Table) command, you will be pro mpted to selec t a table. The code
retriev es the link information from the table and then requests the data link to pull down new data from the
spreadsheet before updating the table. Next time we'll look at the code for T2S...
Posted at 04:23 PM in A utoCAD, AutoCAD .NET, Exc el, Tables | Permalink | 7 Comments | TrackBack (0)
August 22, 20 07
Creating an AutoCAD table linked to an Excel spreadsheet using .NET
In the last post I promised to tackle this issue, and so here we are again. :-)
http://through-the-interface.typepad.com/through_the_interface/2007/08/using-autocads-.htmlhttp://through-the-interface.typepad.com/through_the_interface/2007/08/creating-an-aut.htmlhttp://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-aut.html#trackbackhttp://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-aut.html#disqus_threadhttp://through-the-interface.typepad.com/through_the_interface/2007/08/updating-an-aut.htmlhttp://through-the-interface.typepad.com/through_the_interface/tables/http://through-the-interface.typepad.com/through_the_interface/excel/http://through-the-interface.typepad.com/through_the_interface/autocad_net/http://through-the-interface.typepad.com/through_the_interface/autocad/ -
7/22/2019 Through the Interface_ Excel
17/19
Note:the code in this post relies on enhanced table functionality introduc ed in AutoCAD 2008, so please
don't get frustrated try ing to make this work in prev ious versions.
The following C# code follows on from yesterday 's, taking the spreadsheet selected by the user and linking it
to a newly-cr eated table in the active AutoCAD drawing. I haven't bothered with line numbering in the below
code, as it follows on almost exactly from the code shown last time (aside from renaming the namespace, the
command and the function, as well as adding a string co nstant at the top o f the function implementation).
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;
using Autodesk.AutoCAD.Windows;
namespaceLinkToExcel
{
publicclassCommands
{
[CommandMethod("TFS")]
staticpublicvoidTableFromSpreadsheet()
{
// Hardcoding the string
// Could also select for it
conststringdlName =
"Import table from Excel demo";
Documentdoc =
Application.DocumentManager.MdiActiveDocument;
Databasedb = doc.Database;
Editored = doc.Editor;
OpenFileDialogofd =
newOpenFileDialog( "Select Excel spreadsheet to link",
null,
"xls; xlsx",
"ExcelFileToLink",
OpenFileDialog.OpenFileDialogFlags.
DoNotTransferRemoteFiles
);
System.Windows.Forms.DialogResultdr =
ofd.ShowDialog();
if(dr != System.Windows.Forms.DialogResult.OK)
return;
ed.WriteMessage(
"\nFile selected was \"{0}\".",
ofd.Filename
);
PromptPointResultppr =
ed.GetPoint(
"\nEnter table insertion point: "
); if(ppr.Status != PromptStatus.OK)
return;
// Remove the Data Link, if it exists already
-
7/22/2019 Through the Interface_ Excel
18/19
DataLinkManagerdlm = db.DataLinkManager;
ObjectIddlId = dlm.GetDataLink(dlName);
if(dlId != ObjectId.Null)
{
dlm.RemoveDataLink(dlId);
}
// Create and add the Data Link
DataLinkdl = newDataLink();
dl.DataAdapterId = "AcExcel";
dl.Name = dlName;
dl.Description =
"Excel fun with Through the Interface";
dl.ConnectionString = ofd.Filename;
dl.DataLinkOption =
DataLinkOption.PersistCache;
dl.UpdateOption |=
(int)UpdateOption.AllowSourceUpdate;
dlId = dlm.AddDataLink(dl);
Transactiontr =
doc.TransactionManager.StartTransaction();
using(tr)
{
tr.AddNewlyCreatedDBObject(dl, true);
BlockTablebt =
(BlockTable)tr.GetObject(
db.BlockTableId,
OpenMode.ForRead
);
Tabletb = newTable();
tb.TableStyle = db.Tablestyle;
tb.Position = ppr.Value;
tb.SetDataLink(0, 0, dlId, true);
tb.GenerateLayout();
BlockTableRecordbtr =
(BlockTableRecord)tr.GetObject(
db.CurrentSpaceId,
OpenMode.ForWrite
);
btr.AppendEntity(tb);
tr.AddNewlyCreatedDBObject(tb, true);
tr.Commit();
}
// Force a regen to display the table
ed.Regen();
}
}
}Here's what happens when you run the TFS command and select your favourite XLS for linking (I used mass-
balanc e.x ls fro m A utoCA D 200 8's Sample\ Mec hanical Sample folder):
-
7/22/2019 Through the Interface_ Excel
19/19
At this stage I hav en't focused at all on formating - this is just coming in "as is", without any adjustment of cell
alignments, column widths or row heights.
I cho se to hardcode the name of the Data Link we use for the spreadsheet. Y ou can run the DATALINK
command to chec k on it, after the co mmand has exec uted:
It doesn't seem to be an issue if you repeat the c ommand and bring in a different spreadsheet using the same
link - the link appears to c ontinue (although I hav en't performed ex haustive testing). If it does pro v e to be a
problem it should be simple enough to c reate a unique Data Link per spreadsheet imported (or e ven per time
the co mmand is run).
Posted at 05:40 PM in AutoCAD, AutoCAD .NET, Excel, Tables | Permalink | 5 Comments | TrackBack (0)
http://through-the-interface.typepad.com/through_the_interface/2007/08/creating-an-aut.html#trackbackhttp://through-the-interface.typepad.com/through_the_interface/2007/08/creating-an-aut.html#disqus_threadhttp://through-the-interface.typepad.com/through_the_interface/2007/08/creating-an-aut.htmlhttp://through-the-interface.typepad.com/through_the_interface/tables/http://through-the-interface.typepad.com/through_the_interface/excel/http://through-the-interface.typepad.com/through_the_interface/autocad_net/http://through-the-interface.typepad.com/through_the_interface/autocad/http://through-the-interface.typepad.com/.shared/image.html?/photos/uncategorized/2007/08/22/data_link_for_excel_speadsheet_2.pnghttp://through-the-interface.typepad.com/.shared/image.html?/photos/uncategorized/2007/08/22/table_linked_to_excel_spreadsheet.png