“All I want is an environment with my data in it.” So said Justin Evans, Chief Systems Engineer, B52 Program, USAF. Me too!
Now I have my data in Aras Innovator®, I need some means of getting data in and out of my favorite hand tool Excel, so I created XamlAml for this purpose. It uses Powershell and AML to get Excel data into Innovator, and to get data out of Innovator into Excel, all from one place. Yes, the batch loader gets data in, and the UI exports to Excel, but XamlAML makes it less work.
Download at https://github.com/4-R-Forum/XamlAml/tree/dev_v0.2
As the name suggests, it uses a Xaml UI and AML, the native, self describing, declarative language of Innovator. XamlAML is a collection of Powershell scripts. Powershell is part of the Windows operating system, so there is nothing to install, and all the tools are open source so there is nothing extra to buy. A module from Powershell Gallery is required to manipulate OpenXML (the second x in .xlsx.) Setup and user steps are in the ReleaseAndDeploymentGuide in the repo. Powershell is a scripting language, it is interpreted not compiled, so all the code is accessible, and can be extended for your own purposes. The syntax is a bit squirrely, but it is worth learning. Its creator, Jeffrey Snover, was made a Microsoft Technical Fellow the year after its release, a significant honor.
The concept of ExceLoader is that each sheet represents, and has the name of, an ItemType. Each column represents a property, named in the first row. Properties of type Item use the AML syntax item_number(keyed_name), the scripts create AML accordingly. Properties of type File are handled using Item.setFileProperty() from a column named physical_file. Sheet and column names to be ignored are prefixed with a selected character (default is underscore), so that the workbook can contain working data not used for loading. Multiple sheets are loaded in tab order. To create an ExceLoader workbook you need to understand the concepts of ItemType, RelationshipType, Property, and AML. If you have read this far, you are probably qualified.
The scripts use the ImportExcel Module by Doug Finke, which is amazing. It provides all the functionality of Excel without it being installed. This is achieved using open source OpenXML.
ExcelReport was originally conceived as an Excel Add-In, but inspired by Doug Finke’s lighting talk (link in the preceding paragraph), it occurred to me that using ImportExcel would bring two advantages: i) there is no need for an msi and ii) Powershell code is accessible to the user, whereas compiled code in an Add-In is not. It is necessary to write code to implement reports, but once written they can be shared with others. ExcelReport can do much of what Power BI does with Innovator data, and can be shared without an additional subscription.
Thanks for sharing. I looked at a bit of the code and it highlights your point about non-compiled code. I could read along easily enough without learning any new language.