Stripping formatting from AutoCAD tables pasted from Excel using .NET

I’m back in San Rafael after a tiring – but very rewarding – two days down in Santa Cruz. I’ve decided to rattle out a quick post – inspired by this recent comment – before heading out for dinner, so I hope my brain isn’t playing tricks on me when it says the code’s good enough to post. Please do let me know if you see anything wrong with it.

Here’s the question:

Kean, I am wondering how to do this on table objects. When importing a table from excel as pasting special ACAD objects, you can reset the table overrides, but this does not reset the cell content. You manually have to go through each cell to reset the formatting of the content. I have written an app that iterates through the selected table's rows/columns and gets the content in an attempt to use the text editor to reset the content of each cell. However, the texteditor.create only allows for MTEXT in which the table content is not MTEXT. I've looked on your other blog posts on tables and don't see a way to do this. Please help if you can. Thanks. Steve

When we run the PASTESPEC command – with some Excel data in the clipboard – and choose to insert “AutoCAD entities”, we get an AutoCAD table containing formatted text that shows up as black:

If we look at the contents of a particular cell, we see it has embedded formatting codes, such as this: "{\\fCalibri|b0|i0|c0;\\c0;A"

To remove this formatting, I adopted the technique suggested by Tony Tanzillo in an earlier comment on that post – and that I’ve looked at in some depth in this other post – to get at each cell’s unformatted contents. The code uses an MText object to strip off the formatting rather than the MText editor.

Here’s the C# code:

using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;
 
namespace TableFormatting
{
  public class Commands
  {
    [CommandMethod("STF")]
    public static void StripTableFormatting()
    {
      Document doc =
        Application.DocumentManager.MdiActiveDocument;
      Editor ed = doc.Editor;
 
      // Ask the user to select a table
 
      var peo = new PromptEntityOptions("\nSelect table");
      peo.SetRejectMessage("\nMust be a table.");
      peo.AddAllowedClass(typeof(Table), false);
      peo.AllowNone = false;
      var per = ed.GetEntity(peo);
      if (per.Status != PromptStatus.OK)
        return;
 
      var tabId = per.ObjectId;
 
      using (var tr = doc.TransactionManager.StartTransaction())
      {
        // Open the table for write
 
        var tab = tr.GetObject(tabId, OpenMode.ForWrite) as Table;
        if (tab == null)
          return;
 
        // Start by clearing any style overrides
 
        tab.Cells.ClearStyleOverrides();
 
        // We'll use an MText object to strip embedded formatting
 
        using (var mt = new MText())
        {
          for (int r = 0; r < tab.Rows.Count; r++)
          {
            for (int c = 0; c < tab.Columns.Count; c++)
            {
              // Get the cell and its contents
 
              var cell = tab.Cells[r, c];
              mt.Contents = cell.TextString;
 
              // Explode the text fragments
 
              mt.ExplodeFragments(
                (a, b) =>
                {
                  if (a != null)
                  {
                    // Assuming we have a fragment, use its text
                    // to set the cell contents
 
                    cell.TextString = a.Text;
                  }
                  return MTextFragmentCallbackStatus.Continue;
                }
              );
            }
          }
        }
        tr.Commit();
      }
    }
  }
}