Kean Walmsley


  • About the Author
    Kean on Google+

August 2014

Sun Mon Tue Wed Thu Fri Sat
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            








« Skiving off | Main | Reality Computing updates »

February 05, 2014

Inserting a cell range from an Excel sheet as an AutoCAD table using .NET

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);

      }

    }

  }

}

blog comments powered by Disqus

10 Random Posts