Atom
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 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: 
. 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).
August 27, 2007 in AutoCAD, AutoCAD .NET, Excel, Tables | Permalink | Comments (0) | TrackBack
Updating an AutoCAD table linked to an Excel spreadsheet using .NET
Thanks to Viru Aithal, from DevTech India, for providing the code for this post (I converted the C# code 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 2008. AutoCAD does a great job of looking for changes in the Excel spreadsheet, and asking whether you want to update the linked table:
There may be times, however, when you want to force the update programmatically, whether from the spreadsheet to the table ot vice-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 spreadsheet 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;
namespace LinkToExcel
{
public class Commands
{
[CommandMethod("S2T")]
static public void UpdateTableFromSpreadsheet()
{
Document doc =
Application.DocumentManager.MdiActiveDocument;
Database db = doc.Database;
Editor ed = doc.Editor;
PromptEntityOptions opt =
new PromptEntityOptions(
"\nSelect table 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 spreadsheet
ObjectId dlId = tb.GetDataLink(0, 0);
DataLink 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
);
}
}
}
}
}
When you run the S2T (for Spreadsheet-to-Table) command, you will be prompted to select a table. The code retrieves 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...
August 24, 2007 in AutoCAD, AutoCAD .NET, Excel, Tables | Permalink | Comments (4) | TrackBack
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. :-)
Note: the code in this post relies on enhanced table functionality introduced in AutoCAD 2008, so please don't get frustrated trying to make this work in previous versions.
The following C# code follows on from yesterday's, taking the spreadsheet selected by the user and linking it to a newly-created 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 constant at the top of the function implementation).
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("TFS")]
static public void TableFromSpreadsheet()
{
// Hardcoding the string
// Could also select for it
const string dlName =
"Import table from Excel demo";
Document doc =
Application.DocumentManager.MdiActiveDocument;
Database db = doc.Database;
Editor ed = doc.Editor;
OpenFileDialog ofd =
new OpenFileDialog(
"Select Excel spreadsheet to link",
null,
"xls; xlsx",
"ExcelFileToLink",
OpenFileDialog.OpenFileDialogFlags.
DoNotTransferRemoteFiles
);
System.Windows.Forms.DialogResult dr =
ofd.ShowDialog();
if (dr != System.Windows.Forms.DialogResult.OK)
return;
ed.WriteMessage(
"\nFile selected was \"{0}\".",
ofd.Filename
);
PromptPointResult ppr =
ed.GetPoint(
"\nEnter table insertion point: "
);
if (ppr.Status != PromptStatus.OK)
return;
// Remove the Data Link, if it exists already
DataLinkManager dlm = db.DataLinkManager;
ObjectId dlId = dlm.GetDataLink(dlName);
if (dlId != ObjectId.Null)
{
dlm.RemoveDataLink(dlId);
}
// Create and add the Data Link
DataLink dl = new DataLink();
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);
Transaction tr =
doc.TransactionManager.StartTransaction();
using (tr)
{
tr.AddNewlyCreatedDBObject(dl, true);
BlockTable bt =
(BlockTable)tr.GetObject(
db.BlockTableId,
OpenMode.ForRead
);
Table tb = new Table();
tb.TableStyle = db.Tablestyle;
tb.Position = ppr.Value;
tb.SetDataLink(0, 0, dlId, true);
tb.GenerateLayout();
BlockTableRecord btr =
(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-balance.xls from AutoCAD 2008's Sample\Mechanical Sample folder):
At this stage I haven'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 chose to hardcode the name of the Data Link we use for the spreadsheet. You can run the DATALINK command to check on it, after the command has executed:
It doesn't seem to be an issue if you repeat the command and bring in a different spreadsheet using the same link - the link appears to continue (although I haven't performed exhaustive testing). If it does prove to be a problem it should be simple enough to create a unique Data Link per spreadsheet imported (or even per time the command is run).
August 22, 2007 in AutoCAD, AutoCAD .NET, Excel, Tables | Permalink | Comments (0) | TrackBack
Creating a table of block attributes in AutoCAD using .NET - Part 2
In the last post we looked at some code to create a table of attribute values for a particular block. In this post we'll extend that code and show how to use a formula to create a total of those values.
Below is the C# code. I've numbered the lines, and those in red are new since the last post. The complete source file can be downloaded here.
Firstly, a quick breakdown of the changes:
- Lines 60-81 deal with user input, and the forcing of the decision to "embed" rather than "link", if we're performing the total (table formulae do not work with fields, even if they have numeric results, so we're forced to create the table with the current value, rather than a field pointing to the attribute reference)
- Line 134 and subsequently lines 159-166 declare and set a variable indicating for which column we're going to provide a total
- Lines 169-181 deal with the exceptional case that we don't find the specified attribute definition
- Lines 310-336 create our additional row, and insert the total in the appropriate cell. We're using a formula such as this: %<\AcExpr (Sum(A2:A4)) \f "%lu2%pr2">%
- The \f flag specifies we want a numeric value with 2 decimal places - these codes are not documented, but you can find them out by using the FIELD command, as described in this previous post
- Line 343 performs a regen, to update the value of our field
And now for the code:
1 using Autodesk.AutoCAD.ApplicationServices;
2 using Autodesk.AutoCAD.DatabaseServices;
3 using Autodesk.AutoCAD.EditorInput;
4 using Autodesk.AutoCAD.Geometry;
5 using Autodesk.AutoCAD.Runtime;
6 using System.Collections.Specialized;
7 using System;
8
9 namespace TableCreation
10 {
11 public class Commands
12 {
13 // Set up some formatting constants
14 // for the table
15
16 const double colWidth = 15.0;
17 const double rowHeight = 3.0;
18 const double textHeight = 1.0;
19 const CellAlignment cellAlign =
20 CellAlignment.MiddleCenter;
21
22 // Helper function to set text height
23 // and alignment of specific cells,
24 // as well as inserting the text
25
26 static public void SetCellText(
27 Table tb,
28 int row,
29 int col,
30 string value
31 )
32 {
33 tb.SetAlignment(row, col, cellAlign);
34 tb.SetTextHeight(row, col, textHeight);
35 tb.SetTextString(row, col, value);
36 }
37
38 [CommandMethod("BAT")]
39 static public void BlockAttributeTable()
40 {
41 Document doc =
42 Application.DocumentManager.MdiActiveDocument;
43 Database db = doc.Database;
44 Editor ed = doc.Editor;
45
46 // Ask for the name of the block to find
47
48 PromptStringOptions opt =
49 new PromptStringOptions(
50 "\nEnter name of block to list: "
51 );
52 PromptResult pr = ed.GetString(opt);
53
54 if (pr.Status == PromptStatus.OK)
55 {
56 string blockToFind =
57 pr.StringResult.ToUpper();
58 bool embed = false;
59
60 // And the attribute to provide total for
61
62 opt.Message =
63 "\nEnter name of column to total <\"\">: ";
64 pr = ed.GetString(opt);
65
66 if (pr.Status == PromptStatus.None ||
67 pr.Status == PromptStatus.OK)
68 {
69 string columnToTotal =
70 pr.StringResult.ToUpper();
71
72 if (columnToTotal != "")
73 {
74 // If a column has been chosen, we need
75 // to embed the attribute values
76 // as otherwise the "sum" formula will fail
77
78 embed = true;
79 }
80 else
81 {
82 // Ask whether to embed or link
83
84 PromptKeywordOptions pko =
85 new PromptKeywordOptions(
86 "\nEmbed or link the attribute values: "
87 );
88
89 pko.AllowNone = true;
90 pko.Keywords.Add("Embed");
91 pko.Keywords.Add("Link");
92 pko.Keywords.Default = "Embed";
93 PromptResult pkr =
94 ed.GetKeywords(pko);
95
96 if (pkr.Status == PromptStatus.None ||
97 pkr.Status == PromptStatus.OK)
98 {
99 if (pkr.Status == PromptStatus.None ||
100 pkr.StringResult == "Embed")
101 embed = true;
102 else
103 embed = false;
104 }
105 }
106
107 Transaction tr =
108 doc.TransactionManager.StartTransaction();
109 using (tr)
110 {
111 // Let's check the block exists
112
113 BlockTable bt =
114 (BlockTable)tr.GetObject(
115 doc.Database.BlockTableId,
116 OpenMode.ForRead
117 );
118
119 if (!bt.Has(blockToFind))
120 {
121 ed.WriteMessage(
122 "\nBlock "
123 + blockToFind
124 + " does not exist."
125 );
126 }
127 else
128 {
129 // And go through looking for
130 // attribute definitions
131
132 StringCollection colNames =
133 new StringCollection();
134 int colToTotalIdx = -1;
135
136 BlockTableRecord bd =
137 (BlockTableRecord)tr.GetObject(
138 bt[blockToFind],
139 OpenMode.ForRead
140 );
141 foreach (ObjectId adId in bd)
142 {
143 DBObject adObj =
144 tr.GetObject(
145 adId,
146 OpenMode.ForRead
147 );
148
149 // For each attribute definition we find...
150
151 AttributeDefinition ad =
152 adObj as AttributeDefinition;
153 if (ad != null)
154 {
155 // ... we add its name to the list
156
157 colNames.Add(ad.Tag);
158
159 if (ad.Tag.ToUpper() == columnToTotal)
160 {
161 // Save the index of the column
162 // we want to total
163
164 colToTotalIdx =
165 colNames.Count - 1;
166 }
167 }
168 }
169 // If we didn't find the attribute to be totalled
170 // then simply ignore the request and continue
171
172 if (columnToTotal != "" && colToTotalIdx < 0)
173 {
174 ed.WriteMessage(
175 "\nAttribute definition for "
176 + columnToTotal
177 + " not found in "
178 + blockToFind
179 + ". Total will not be added to the table."
180 );
181 }
182 if (colNames.Count == 0)
183 {
184 ed.WriteMessage(
185 "\nThe block "
186 + blockToFind
187 + " contains no attribute definitions."
188 );
189 }
190 else
191 {
192 // Ask the user for the insertion point
193 // and then create the table
194
195 PromptPointResult ppr =
196 ed.GetPoint(
197 "\nEnter table insertion point: "
198 );
199
200 if (ppr.Status == PromptStatus.OK)
201 {
202 Table tb = new Table();
203 tb.TableStyle = db.Tablestyle;
204 tb.NumRows = 1;
205 tb.NumColumns = colNames.Count;
206 tb.SetRowHeight(rowHeight);
207 tb.SetColumnWidth(colWidth);
208 tb.Position = ppr.Value;
209
210 // Let's add our column headings
211
212 for (int i = 0; i < colNames.Count; i++)
213 {
214 SetCellText(tb, 0, i, colNames[i]);
215 }
216
217 // Now let's search for instances of
218 // our block in the modelspace
219
220 BlockTableRecord ms =
221 (BlockTableRecord)tr.GetObject(
222 bt[BlockTableRecord.ModelSpace],
223 OpenMode.ForRead
224 );
225
226 int rowNum = 1;
227 foreach (ObjectId objId in ms)
228 {
229 DBObject obj =
230 tr.GetObject(
231 objId,
232 OpenMode.ForRead
233 );
234 BlockReference br =
235 obj as BlockReference;
236 if (br != null)
237 {
238 BlockTableRecord btr =
239 (BlockTableRecord)tr.GetObject(
240 br.BlockTableRecord,
241 OpenMode.ForRead
242 );
243 using (btr)
244 {
245 if (btr.Name.ToUpper() == blockToFind)
246 {
247 // We have found one of our blocks,
248 // so add a row for it in the table
249
250 tb.InsertRows(
251 rowNum,
252 rowHeight,
253 1
254 );
255
256 // Assume that the attribute refs
257 // follow the same order as the
258 // attribute defs in the block
259
260 int attNum = 0;
261 foreach (
262 ObjectId arId in
263 br.AttributeCollection
264 )
265 {
266 DBObject arObj =
267 tr.GetObject(
268 arId,
269 OpenMode.ForRead
270 );
271 AttributeReference ar =
272 arObj as AttributeReference;
273 if (ar != null)
274 {
275 // Embed or link the values
276
277 string strCell;
278 if (embed)
279 {
280 strCell = ar.TextString;
281 }
282 else
283 {
284 string strArId =
285 arId.ToString();
286 strArId =
287 strArId.Trim(
288 new char[] { '(', ')' }
289 );
290 strCell =
291 "%<\\AcObjProp Object("
292 + "%<\\_ObjId "
293 + strArId
294 + ">%).TextString>%";
295 }
296 SetCellText(
297 tb,
298 rowNum,
299 attNum,
300 strCell
301 );
302 }
303 attNum++;
304 }
305 rowNum++;
306 }
307 }
308 }
309 }
310
311 // Now let's add a row for our total
312
313 if (colToTotalIdx >= 0)
314 {
315 tb.InsertRows(rowNum, rowHeight, 1);
316 char colLetter =
317 Convert.ToChar(
318 (Convert.ToInt32(
319