I wasn’t planning on covering this particular topic today, but then this comment came in overnight and I ended up taking a look into it. Paul has been trying to append a specific cell range to his connection string when creating a data-link for an Excel spreadsheet, adding to the code from this previous post.
I gave it a try myself with a hardcoded 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. What’s probably most interesting about the code in this post is its use of a regular expression – something I don’t use often enough, in truth – which is a great way of validating that strings conform to a particular pattern.
It’s also possible to apply group labels within a regex to tag groups of characters for later extraction and validation. We use this technique to check that the row numbers are both greater than 0, for instance (“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 I’ve said, I don’t use them as often as I should – but hopefully the overall technique of using them will be a helpful reminder for people.
On a more general note, this kind of input validation is extremely important if you’re asking for user input and then using it to build database queries: if you don’t protect against arbitrary strings being entered then your application will be wide open to SQL Injection attacks. This isn’t something that’s as important with desktop applications accessing local databases, perhaps, but as applications move more to the web this becomes absolutely critical (as the unfortunate autocomplete snafu on healthcare.gov highlighted a couple of months ago).
Here’s the updated C# code... the TFS command is the only section that’s had significant changes from the previous version, in case:
using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;
using Autodesk.AutoCAD.Windows;
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 (Autodesk.AutoCAD.Runtime.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;
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 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 (Autodesk.AutoCAD.Runtime.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 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")
const string rangeExp =
"^[A-Z]+(?<row1>[0-9]+):[A-Z]+(?<row2>[0-9]+)$";
bool done = false;
string range = "";
do
{
var psr = ed.GetString("\nEnter cell range <entire sheet>");
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
var m =
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
var ppr = ed.GetPoint("\nEnter table insertion point");
if (ppr.Status != PromptStatus.OK)
return;
try
{
// 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] +
(String.IsNullOrEmpty(range) ? "" : "!" + range);
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();
}
}
catch (Autodesk.AutoCAD.Runtime.Exception ex)
{
ed.WriteMessage("\nException: {0}", ex.Message);
}
}
}
}
