ExcelDNA Packing Tool Manifest Writer

Introduction

ExcelDNA is a great platform for creating Excel add-ins in .NET. It even features a utility, ExcelDnaPack, which lets you pack any libraries referenced by your add-in into one .XLL file, for simple single-file deployment.

However, if you ever add or remove assembly references from your project, you have to go back to your add-in's .DNA file and manually add or remove references. This can result in three different issues:

1. Forgetting to add a reference to the .DNA file can cause config-time or run-time errors.
2. Forgetting to remove a reference adds bloat to your deployment and memory overhead.
3. Editing the DNA file (which is really just an XML file) is prone to spelling errors, which will also result in config-time or run-time errors.

Some of these errors may cause a build-time error in ExcelDnaPack, but often they can slip through.

The code sample below contains a simple C# console app that generates a DNA file listing each assembly referenced by your project. You can then add a reference to this DNA file to your main DNA file, and avoid manually updating the references. Use this console app as a post-build step in Visual Studio, before using ExcelDnaPack.

Background

Before continuing, you should already be familiar with the basics of ExcelDNA's .DNA files, using the ExcelDnaPack tool, and using post-build steps in Visual Studio.

Using the Code

The code below has two classes: the standard Program class and the class DnaFile.

Program.Main

This method takes an array of command line arguments. The first should be the full path of your Visual Studio project file. (example: "C:\Users\Me\Documents\Visual Studio 2015\Projects\MyAddIn\MyAddIn.csproj") and the second should be the build directory for your project (example: "C:\Users\Me\Documents\Visual Studio 2015\Projects\MyAddIn\bin\Release\").

Note the last precondition check in the Main method. If you are using VB.NET (or F#), you will need to change ".csproj" to ".vbproj" (or ".fsproj").

After the preconditions, there is not much to Main. It gets a list of references, filters the list, and sends it to DnaFile to create a file.

Program.GetReferences

This method takes the path of your project file as an argument, and then reads the project file (which is just an XML file) and parses out a list of referenced DLL and DNA files.

Inside GetReferences' while loop, there are three things it checks for.

1. First, it checks for an XML element named "AssemblyName". This is the name of the assembly your project will generate. This DLL will need to be included in your reference list. The main DNA file in your project will have the same name as the assembly, with a .DNA extension instead of .DLL. In the main DNA file, you will have to add a reference to the manifest DNA file that this tool is generating, As such, you should not include a reference to the main DNA file in the manifest DNA file. The local variable assemblyName keeps track of this name, so it can be excluded from the other DNA files you may need to reference in the manifest.
2. It then checks for all XML elements named "Reference". These will each have the assembly name followed by a comma and some version information. Everything after the comma is removed, and ".dll" is added.
3. Finally, it checks for any other DNA files that might be in your project, but excludes any DNA file that has the same name as your assembly. I often use separate files for ribbon controls with lots of buttons.

Program.FilterReferences

This method may need to be customized to meet your needs. This is simply a filter used in the Where clause near the end of the Main method. I've set it up to exclude any references that start with "System", "Microsoft", "ExcelDna", or "Office". This will prevent it from adding references to framework libraries, Excel interop libraries, and ExcelDna libraries. You may need more specific filtering if you need to reference less common Microsoft libraries.

DnaFile Class

DnaFile.CreateFile

This method takes the project's build directory and a filtered list of references as arguments and creates a .DNA file. There's really not much to it.

DnaFile.ouputFilename

Change this string to whatever you want your manifest file to be called. I chose "AddIn References.dna" for simplicity.

DnaFile.dnaHeader

IMPORTANT: This string will be the first line of the generated DNA file. As you can see, I use C#4. If you're using a different platform, change this line accordingly.

DnaFile.dnaFooter

This is the last line of the generated file. You shouldn't need to change this.

DnaFile.CreateFile

This method takes a reference name and turns it into a full line of XML. You may want to change the Pack or ExplicitExports attributes.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml;

namespace ExcelDnaPackManifestWriter {

    public static class Program {

        public static void Main(String[] args) {

            #region Validation
            if (args == null)
                throw new ArgumentNullException("args");

            if (args.Length != 2)
                throw new ArgumentException("Must have 2 input args");

            if (!File.Exists(args[0]))
                throw new FileNotFoundException(args[0] + " was not found");

            if (!Directory.Exists(args[1]))
                throw new DirectoryNotFoundException(args[1] + " was not found");

            //Change this to ".vbproj" if you're using VB.NET
            if (!args[0].EndsWith(".csproj")) 
                throw new ArgumentException();
            #endregion

            String projectPath = args[0]; 
            String outputPath = args[1];

            String[] refs = GetReferences(projectPath)
                .Where(r => FilterReferences(r))
                .ToArray();

            DnaFile.CreateFile(outputPath, refs);
        }

        #region Get References
        private static IEnumerable<String> GetReferences(String projectPath) {
            String assemblyName = "";

            using (var reader = XmlReader.Create(new StreamReader(projectPath))) {
                while (reader.Read()) {
                    if (reader.NodeType == XmlNodeType.Element){
                        String reference;
                        if (reader.Name == "AssemblyName") {
                            //Grab assembly name to exclude DNA file later
                            assemblyName = reader.ReadElementContentAsString();
                            reference = assemblyName + ".dll";
                            yield return reference;
                        }

                        if (reader.Name == "Reference") {
                            reference = reader.GetAttribute("Include");
                            reference = reference.Substring(0, reference.IndexOf(",")) + ".dll";
                            yield return reference;
                        }

                        if (reader.Name == "None") {
                            reference = reader.GetAttribute("Include");
                            if (reference.EndsWith(".dna") 
                            && !reference.StartsWith(assemblyName)) {
                                yield return reference;
                            }
                        }
                    }
                }
            }
        }

        //You may need to change the filter conditions, depending on your references.
        private static Boolean FilterReferences(String reference) {
            return !reference.StartsWith("System.")
                && !reference.StartsWith("Microsoft.")
                && !reference.StartsWith("ExcelDna.")
                && !reference.StartsWith("Office");
        }
        #endregion
    }

//-----------------------------------------------------------------------------

    internal static class DnaFile {

        public static void CreateFile(String outputPath, IEnumerable<String> references) {
            using (var writer = new StreamWriter(outputPath + outputFilename)) {
                writer.WriteLine(dnaHeader);

                foreach (var r in references)
                    writer.WriteLine(CreateDnaLine(r));

                writer.WriteLine(dnaFooter);
            }
        }

        private static String outputFilename = "AddIn References.dna";

        //Make sure you have the right platform in your header!
        private static String dnaHeader = "<DnaLibrary " +
                        "RuntimeVersion=\"v4.0\" " +
                        "Name=\"AddIn References\" " +
                        "Language=\"C#\">";

        private static String dnaFooter = "</DnaLibrary>";

        //You may want to adjust the Pack and ExplicitExports attributes.
        private static String CreateDnaLine(String reference) {
            return "\t<ExternalLibrary " +
                        "Path=\"" + reference + "\" " +
                        "Pack=\"true\" " +
                        "ExplicitExports=\"true\"/>";
        }
    }
}

Once you've built the Manifest Writer as an EXE file, you can add a post-build command like this. (Note the slash at the end of the first line!)

"C:\Program Files\ExcelDna\ExcelDnaPackManifestWriter.exe" 
"$(ProjectPath)" "$(TargetDir)"\

"C:\Program Files\ExcelDna\ExcelDnaPack.exe" 
"$(TargetDir)$(TargetName).dna" /O "$(TargetDir)$(TargetName).xll /Y

I hope this makes your add-in development easier! Please let me know of any issues you come across, or modifications you'd like to see.

History

• Feb 23, 2016: Initial post