Dyanmo Python Node - Datalink class, is there a property to turn off "use excel formatting"?

Civil 3D
, ,
1

I have been creating datalinks to be used in tables via a python node (the Autodesk.AutoCAD.DatabaseServices library). A google search has stated that the datalink class should have a DataLinkOption property that one can set and remove the “KeepExcelFormatting” enum value.

But I cannot find this enumerator in the documentation. Is this property exposed? There is also a SetOption function, maybe the call is here? it does exist somewhere it is in the GUI, but maybe this isnt exposed.

2

DataLinkOption Enum is related to the connection, not the format. In this case Google is producing non-deterministic slop.
The ‘deterministic’ documentation is here:
Data Link Manager
DATALINK (Command)
About Linking a Table to External Data

3

I looked at the links content! The question I have is can this be called within the code? Here is the property in the New Excel Data Link window I’m trying to call within python:

image
image678×618 15.5 KB

But these are the only properties I see with the datalink class. I am probably missing something, but I cannot find anywhere any code option for this cell formatting flag.

image
image1020×662 38.6 KB

4

It looks like this relates to table formatting and it looks like it takes the formatting from the file and applies it to the table. The Table.GenerateLayout() method generates the table and is used once the data is linked and the table placed.

You can set the UpdateOption to OverwriteFormatModifiedAfterUpdate before setting the link and generating the table

The DataLink.UpdateOption property is a bitwise cumulative flag. To set use bitwise operations. Use |= <enum> (OR) to set a flag, and &= ~<enum> (AND NOT) to remove a flag. Something like this… (untested, but you get the idea)

dl = DataLink()
dl.DataAdapterId = "AcExcel"
dl.Name = "link_name"
dl.ConnectionString = "path_to_file.xlsx"
dl.DataLinkOption = DataLinkOption.PersistCache
# Turn off skip format. Use bitwise '&' to check if set
if dl.UpdateOption & UpdateOption.SkipFormat == UpdateOption.SkipFormat:
    dl.UpdateOption &= ~UpdateOption.SkipFormat  # Turn off
# Keep table fomat aligned to Excel file
dl.UpdateOption |= UpdateOption.OverwriteFormatModifiedAfterUpdate
# Add DataLink via DataLink Manager, place and generate, etc.

Source: Creating an AutoCAD table linked to an Excel spreadsheet using .NET - Through the Interface

5

Howdy Mike! Thank you so much for this got back to this task, almost there! Toggling Skip format on and off does infact change the setting, it appears to enable or disable “Use Excel Formatting”

I DO want to use excel formatting - at first. There is an option to use it initially and ignore later and I would like to use that, like this.

image
image372×217 2.6 KB

Changing the OverwriteFormatModifiedAfterUpdate does not seem to update this, but maybe I need to set the flags differently. Currently I do this:
dl.UpdateOption &= ~UpdateOption.SkipFormat
dl.UpdateOption |= UpdateOption.OverwriteFormatModifiedAfterUpdate

But maybe I need to set both flags as one call, if that’s possible. or this is the wrong flag for that setting

6

I’ve done some digging and you would never believe it, there is an undocumented flag!
Anyhow when you flip the ‘Keep table updated’ / ‘do not update’ the UpdateOption flags do not change, however the DataLink.UpdateOption integer is different which indicates a setting has changed.
If you need to set/unset the flag the number is 67108864 for bitwise operations (Edit: This number equals 226 which would imply up to 27 flags)
So from my digging

# Keep table updated to Excel formatting.
DataLink.UpdateOption = 1835009

# Start with Excel Formatting, do not update.
DataLink.UpdateOption = 68943873

# UpdateOption Enum flags for both settings
flags = [
    ["UpdateRowHeight", False],
    ["AllowSourceUpdate", True],
    ["OverwriteFormatModifiedAfterUpdate", False],
    ["OverwriteContentModifiedAfterUpdate", False],
    ["ForceFullSourceUpdate", False],
    ["None", True],
    ["SkipFormat", True],
    ["UpdateColumnWidth", False],
    ["ForPreview", False],
    ["IncludeXrefs", False],
]

Here is my testing code - Note: Use IronPython 3

import clr

from System import Enum

# Add Assemblies for AutoCAD
clr.AddReference("AcMgd")
clr.AddReference("AcDbMgd")

# Import references from AutoCAD
from Autodesk.AutoCAD.ApplicationServices import Application
from Autodesk.AutoCAD.DatabaseServices import OpenMode, UpdateOption


adoc = Application.DocumentManager.MdiActiveDocument

uo_enum = dict(zip(Enum.GetNames(UpdateOption), map(int, Enum.GetValues(UpdateOption))))

with adoc.LockDocument():
    with adoc.Database as db:
        with db.TransactionManager.StartTransaction() as t:
            dlm = db.DataLinkManager
            links = [t.GetObject(id, OpenMode.ForRead) for id in dlm.GetDataLink()]
            # work with first data link
            uo = links[0].UpdateOption
            flags = [
                (key, uo & uo_enum.get(key) == uo_enum.get(key))
                for key in uo_enum.keys()
            ]

            t.Commit()

OUT = uo, flags
7

I set the Solution to your first response as it was the technical answer (Turn on/off Use Excel Formatting) - but this last post is incredible! The bitwise flag worked, and now I use excel formatting inititally as I showed in the picture Thank you so much Mike, Gonna forward this to my threads in the Autodesk forums as well.