Update ExcelDna.AddIn NuGet Package to support the new PackageReference format

[augustoproiete]

Spin-off of #138 to discuss how we want to support the PackageReference format in the ExcelDna.AddIn NuGet package. This relates to #188

Background

• Currently ExcelDna.AddIn relies on PowerShell scripts for installing and uninstalling the package which basically do the following:

install.ps1

1. Rename an _UNINSTALLED_$(ProjectName) to $(ProjectName)-AddIn.dna, if it exists Which would have happened after the user uninstalled a previous version of ExcelDna
2. Add a $(ProjectName)-AddIn.dna file, if step 1 didn't find an old file 2.1. Perform some string replacements inside the .dna file added to include $(ProjectName)
3. ~Add a reference to the .targets file~ Update 2018-05-18: This is now done automatically by a NuGet feature implemented by #167

uninstall.ps1

1. Rename an existing $(ProjectName)-AddIn.dna file to _UNINSTALLED_$(ProjectName), if it exists

Known issues

• The PackageReference format does not support the install.ps1 and uninstall.ps1 PowerShell scripts
• The PackageReference format also does not support content files the same way they do with the packages.config format and introduce another feature contentFiles that behaves in a different way where files are meant to be immutable and therefore they are not shown in the project tree, and are not editable. The (terrible IMO) recommendation from the NuGet team is to "... copy them to the project manually if you need to edit the files ..." :disappointed:
• With the current approach (powershell scripts) If the user renames the .dna file to anything else that is not $(ProjectName)-AddIn, the script doesn't work anymore

Proposed solution

I propose we do the following:

1. Remove the install.ps1 and uninstall.ps1 PowerShell scripts from the NuGet package
2. Rename the ExcelDna-Template.dna to AddIn.dna.pp and use NuGet's source code transformation feature to do the replacements inside the .dna file
3. Document that the user must manually copy the .dna file to the project when using PackageReference (until the NuGet team gives us an alternative) :no_good_man:
4. Update our build process (i.e. ExcelDnaBuild task) to - by convention - use $(ProjectName) as the name of the .xll, if it sees a file named AddIn.dna file in the project. If the user renames it to something else, then use the name of the .dna file for the output .xll

[govert]

I'm confused about where the transformed .pp file ends up. Is it changed in-place in the packages directory, or added to the project, or something else?

[govert]

@augustoproiete Your summary of the situation is great, thank you. Considering the current options, I'm not really feeling ready for this yet, and am not too keen on accepting changes for this issue soon. I wouldn't want you to waste your time (unless you have a burning passion to explorer this...)

[augustoproiete]

I'm confused about where the transformed .pp file ends up. Is it changed in-place in the packages directory, or added to the project, or something else?

With PackageReference, we no longer have the .\packages\ folder at the same level as the solution and the package files are all stored in a local cache (usually %userprofile%\.nuget\packages) and the file transformations are stored in the obj folder of the project. Content files are added to the project as "links" and because of that any changes you make to these files are lost when the package is reinstalled or upgraded.

[augustoproiete]

FYI @govert pointed me to this discussion in the Visual Studio Developer Community which is about the need for restarting Visual Studio after making changes to .props files (which affects Excel-DNA add-ins), and this issue has been fixed in the new .csproj project system (which uses PackageReference) and it doesn't seem it will ever get fixed in the old .csproj project system...

[xp44mm]

I use F# .net framework 4.8 class library in vs 2019(16.7.2), then i use nuget install Excel-DNA, then found a error:

DNA1546 Excel-DNA is not compatible with projects that use NuGet PackageReference. Make sure you create a .NET Framework (Class Library) project and configure Visual Studio to use packages.config ExcelDNA

the fsproj's content is:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Library</OutputType>
    <TargetFramework>net48</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Update="FSharp.Core" Version="4.7.2" />
    <PackageReference Include="Excel-DNA" Version="1.1.1" />
  </ItemGroup>
...

have the newer version resolved this issue about PackageReference?

[augustoproiete]

@xp44mm The latest version of Excel-DNA as of this writing (v1.1.1) doesn't support PackageReference and by default displays the error message you're seeing.

The recommendation at this time is to use the Class Library (.NET Framework) project type instead of Class Library (.NET Standard) project type to get the best development & debugging experience.

If you want to use the new SDK-style project at your own risk, you can set ExcelDnaAllowPackageReferenceProjectStyle to true in your ExcelDna.Build.props. See PR #327 for more details.

[andysprague44]

@augustoproiete hi! Question on the workaround should I want to use ExcelDnaAllowPackageReferenceProjectStyle true.

Is something like the following what is required to get this working (copied from .net framework post-build event command line,), or is there more to it?

xcopy "$(SolutionDir)\packages\ExcelDna.AddIn.0.33.9\tools\ExcelDna.xll" "$(TargetDir)MyExcelAddin-AddIn.xll*" /C /Y
xcopy "$(TargetDir)MyExcelAddin-AddIn.dna*" "$(TargetDir)MyExcelAddin-AddIn64.dna*" /C /Y
xcopy "$(SolutionDir)\packages\ExcelDna.AddIn.0.33.9\tools\ExcelDna64.xll" "$(TargetDir)MyExcelAddin-AddIn64.xll*" /C /Y
"$(SolutionDir)\packages\ExcelDna.AddIn.0.33.9\tools\ExcelDnaPack.exe" "$(TargetDir)MyExcelAddin-AddIn.dna" /Y
"$(SolutionDir)\packages\ExcelDna.AddIn.0.33.9\tools\ExcelDnaPack.exe" "$(TargetDir)MyExcelAddin-AddIn64.dna" /Y

[augustoproiete]

Hey @andysprague44 ! Small world :)

You shouldn't need any of that code if you're using the latest version of Excel-DNA (v1.1.1 as of this writing) as all of that is handled for you by Excel-DNA's build process (even in an SDK-style/PackageReference project). So there's basically only two switches you need to change for it to work (with some limitations (*)):

• Turn on ExcelDnaAllowPackageReferenceProjectStyle to ignore the error that SDK-style projects are not supported
• Turn off RunExcelDnaSetDebuggerOptions to disable the automatic configuration of Visual Studio debugging settings

This should be all you need:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net48</TargetFramework>
  </PropertyGroup>

  <PropertyGroup>
    <ExcelDnaAllowPackageReferenceProjectStyle>true</ExcelDnaAllowPackageReferenceProjectStyle>
    <RunExcelDnaSetDebuggerOptions>false</RunExcelDnaSetDebuggerOptions>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="ExcelDna.AddIn" Version="1.1.1" PrivateAssets="All" />
  </ItemGroup>

</Project>

NB: Change the TargetFramework as needed depending on what .NET Framework version your add-in users have on their machines.

* The feature you'll be missing out when using Excel-DNA with SDK-style/PackageReference projects today is the automatic debugging configuration settings that does two things:

1. Look up where Excel is installed on your machine, and configure Visual Studio to run it (and attach the debugger to) when you press F5 / start a debugging session.

2. Determine if your Excel installation is 32-bit or 64-bit and automatically configure Visual Studio with the the correct .xll to be opened together with Excel when you press F5 / start a debugging session.

SDK-style/PackageReference projects no longer use the .csproj.user to store debugging settings so the API that we use to configure debugging is disabled in the new project system.

The current workaround is to (manually) create multiple profiles in your launchSettings.json (one for each machine/developer) and store it in source control, so that you can debug your add-in without having to configure this every time.

[govert]

The build can detect that it is running from an SDK-style project with

Condition="'$(UsingMicrosoftNETSdk)' == 'true'"

[augustoproiete]

FYI The condition we use today already detects SDK-style projects by checking PackageReferenceCompatibleProjectStyle:

Condition=" ('$(PackageReferenceCompatibleProjectStyle)' == 'true' OR '$(RestoreProjectStyle)' == 'ProjectJson' OR '$(NuGetProjectStyle)' == 'PackageReference') " />

That said adding an extra check on UsingMicrosoftNETSdk just to be safe doesn't hurt.

[altso]

@augustoproiete I just tried your example and got this error from dotnet build command:

MSBUILD : ExcelDnaBuild error DNA-422377164: Could not load file or assembly 'Microsoft.Build.Utilities.v4.0, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified. [C:\Temp\02\Test.csproj]
MSBUILD : ExcelDnaBuild error DNA-422377164: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Build.Utilities.v4.0, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified. [C:\Temp\02\Test.csproj]
MSBUILD : ExcelDnaBuild error DNA-422377164: File name: 'Microsoft.Build.Utilities.v4.0, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' [C:\Temp\02\Test.csproj]
MSBUILD : ExcelDnaBuild error DNA-422377164:    at ExcelDna.AddIn.Tasks.CreateExcelAddIn.AddDnaToListOfFilesToPack(String outputDnaFileName, String outputXllFileName, String outputXllConfigFileName) [C:\Temp\02\Test.csproj]
MSBUILD : ExcelDnaBuild error DNA-422377164:    at ExcelDna.AddIn.Tasks.CreateExcelAddIn.TryBuildAddInFor32Bit(BuildItemSpec[] buildItemsForDnaFiles) [C:\Temp\02\Test.csproj]
MSBUILD : ExcelDnaBuild error DNA-422377164:    at ExcelDna.AddIn.Tasks.CreateExcelAddIn.Execute() [C:\Temp\02\Test.csproj]

Test.csproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net48</TargetFramework>
  </PropertyGroup>

  <PropertyGroup>
    <ExcelDnaAllowPackageReferenceProjectStyle>true</ExcelDnaAllowPackageReferenceProjectStyle>
    <RunExcelDnaSetDebuggerOptions>false</RunExcelDnaSetDebuggerOptions>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="ExcelDna.AddIn" Version="1.1.1" PrivateAssets="All" />
  </ItemGroup>

</Project>

Test.dna:

<?xml version="1.0" encoding="utf-8"?>
<DnaLibrary Name="Test Add-In" RuntimeVersion="v4.0" xmlns="http://schemas.excel-dna.net/addin/2018/05/dnalibrary">
  <ExternalLibrary Path="Test.dll" ExplicitExports="false" LoadFromBytes="true" Pack="true" IncludePdb="false" />
</DnaLibrary>

❯ dotnet --version
5.0.104

I do have net48 developer pack installed. Any ideas on how to make it work?

[augustoproiete]

@altso You need to use msbuild instead of dotnet build

[altso]

@augustoproiete Got it. Thank you.