Excel-DNA plugin not reading data from embedded resource

Hamish Brown

unread,

Feb 14, 2023, 7:51:23 PM2/14/23

to Excel-DNA

I have written a prototype tool with an interface in excel that uses an ExcelDNA add-in to call some c# code for its calculations. (https://github.com/PlantandFoodResearch/SVS/tree/master). This works very well in general but I am having a problem with the add-in not working when I distribute it to other computers.

In the C# code I read some data from an embedded resource (see below) which works fine when I run it in the debugger on my computer and when I run it directly from excel using the .xll add-in in bin\Debug\net4.8-windows directory. However, when I test the .xll in the publish folder (SVSModel-ADDIn64-packed.xll") one function is not working.

This is the part that is causing the problem

public static DataFrame LoadCropCoefficients()
{
string resourceName = "SVSModel.Data.CropCoefficientTableFull.csv";
var assembly = Assembly.GetExecutingAssembly();
Stream csv = assembly.GetManifestResourceStream(resourceName);
DataFrame allCropCoeffs = DataFrame.LoadCsv(csv);
return allCropCoeffs;
}

The function that forms the excel custom function and calls this is:

public static object[,] GetCropCoefficients()
{
return Functions.packDataFrame(SVSModel.Crop.LoadCropCoefficients());
}

Where the packDataFrame method simply converts the dataframe object into a 2D array of objects so they return in excel.

Are there any tricks to using embedded resources in distributed excel DNA add-ins?

Govert van Drimmelen

unread,

Feb 18, 2023, 3:07:17 PM2/18/23

to Excel-DNA

Hi Hamish,

1. Your TargetFramework moniker in the project file does not look right.

You should make it

(The format with the "."' and the "-windows" is only valid for .NET Core, so it would be right to say "net6.0-windows", but for .NET Framework, you need "net472" or "net48".)

2. Don't add the "ExcelDna.Integration" package explicitly - it is implied by the "ExcelDna.AddIn" package, and seems to cause trouble when debugging.

So delete this line

3. I suggest you upgrade to the latest Excel-DNA package version:

4. The reason when your -packed add-in is not working is not related to the embedded resource, but rather that the code fails to JIT-compile since the required assemblies are not found. You reference a whole bunch of other libraries, and these are not automatically added to the -packed.xll output. In order to pack extra assemblies into the -packed.xll, you can add a list into a <ExcelAddInInclude> property of the project file. If you want to add all the .dll files in the output directory, you can do a wildcard target into the project file like this:

<Target Name="PackedReferences" AfterTargets="AfterBuild" BeforeTargets="ExcelDnaBuild">
<ItemGroup>
<References Include="$(OutDir)*.dll" Exclude="$(OutDir)$(TargetFileName)"/>
</ItemGroup>

<PropertyGroup>
<ExcelAddInInclude>@(References)</ExcelAddInInclude>
</PropertyGroup>
</Target>

After making all these changes, the -packed add-in worked for me (at least the GetCropCoefficients() function).

Please let me know if this works, or you run into other trouble.

It looks like a nice add-in and a perfect application of Excel-DNA.

-Govert

Hamish Brown

unread,

Feb 19, 2023, 4:43:34 PM2/19/23

to Excel-DNA

Hi Govert. Thank you so much for taking the time to debug this and apologies for the miss diagnosis. This is my first time trailing ExcelDNA but I have found it very powerful and will use it a lot.

I encounter a build error with step 4 above. "Could not load file or assembly 'System.Reflection.Metadata, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified"

It seems problems can come up with targeting assembly references after you retarget a different version of .net.

Hamish Brown

unread,

Feb 26, 2023, 10:29:14 PM2/26/23

to Excel-DNA

Hi Govert. I have been trying to solve this assembly reference but can't crack it.

I have retargeted the .net reference you suggested, removed redundant ref to ExcelDNA.Integration, updated the version of ExcelDNA-Addin and added the additional assembly references as suggested. However one of them causes the build to throw an error on my system. I am not sure how you were able to get this working. The full error message is below. My most current push to https://github.com/PlantandFoodResearch/SVS/tree/master/modelCsharp is the code I am using that generates this error.

If there is anything further you can suggest I would be most greateful

Severity Code Description Project File Line Suppression State
Error DNA964120249 System.IO.FileNotFoundException: Could not load file or assembly 'System.Reflection.Metadata, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
File name: 'System.Reflection.Metadata, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
at ExcelDna.PackedResources.ExcelDnaPack.IsAssembly(String path, Boolean& isPE)
at ExcelDna.PackedResources.ExcelDnaPack.IsNativeLibrary(String path)
at ExcelDna.PackedResources.ExcelDnaPack.PackDnaLibrary(String dnaPath, Byte[] dnaContent, String dnaDirectory, ResourceUpdater ru, Boolean compress, Boolean multithreading, List`1 filesToPublish, Boolean packManagedDependencies, String[] dependenciesToExcludeParam, String outputBitness, IBuildLogger buildLogger)
at ExcelDna.PackedResources.ExcelDnaPack.Pack(String dnaPath, String xllOutputPathParam, Boolean compress, Boolean multithreading, Boolean overwrite, String usageInfo, List`1 filesToPublish, Boolean packNativeLibraryDependencies, Boolean packManagedDependencies, String excludeDependencies, Boolean useManagedResourceResolver, String outputBitness, IBuildLogger buildLogger)
at ExcelDna.AddIn.Tasks.PackExcelAddIn.Execute()

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].
SVSModel 1

Govert van Drimmelen

unread,

Feb 28, 2023, 5:06:12 AM2/28/23

to Excel-DNA

Hi Hamish,

When I rebuild your solution it build correctly, and then everything seems to work as expected.

It looks like, during the build process, we (the Excel-DNA packing task) look for a file called System.Reflection.Metadata.dll in the output directory, but on your machine it is not found.

We have simplified the packing inclusion in the latest Excel-DNA v 1.7.0-rc1, and I also think you might get away without some of those package references.

Maybe you can try the simpler project file below. I only checked the GetCropCoefficients() function, but that works and the project compiles without the extra packages.

-Govert

Hamish Brown

unread,

Mar 1, 2023, 3:15:53 PM3/1/23

to Excel-DNA

Thanks Govert.

You are right, my system will also build with the simpler profile you suggest above. It runs fine from the bin/debug/net48 folder. But the add in in the bin/debug/net48/publish folder does not work because it needs the packed references.

When I add the stuff to pack references (below) the System.Reflection.MetaData error returns.

<Target Name="PackedReferences" AfterTargets="AfterBuild" BeforeTargets="ExcelDnaBuild">
<ItemGroup>
<References Include="$(OutDir)*.dll" Exclude="$(OutDir)$(TargetFileName)"/>
</ItemGroup>

<PropertyGroup>
<ExcelAddInInclude>@(References)</ExcelAddInInclude>
</PropertyGroup>
</Target>

Is there a way to force my solution to build the missing file?

Govert van Drimmelen

unread,

Mar 1, 2023, 3:34:49 PM3/1/23

to exce...@googlegroups.com

Hi Hamish,

I’m sorry, you are right. When targeting .NET Framework you do still need the explicit packing directive as you show.

In my test I also reduced the list of PackageReferences to this:

</ItemGroup>

So I removed the explicit packages that look like this:

Microsoft.NETFramework.ReferenceAssemblies

System.Reflection.MetaData

Without these extra packages, and adding the <Target Name=”PackedReference” etc. as you show, the project builds (and does not have the System.Reflection.MetaData.dll in the output directory. And both the unpacked and packed versions work at least somewhat.

Are you sure you need the additional packages, beyond the three I show above?

They seem all that is required to build, and should pull in their further dependencies.

In particular, do you have a specific reason for including System.Reflection.MetaData?

Otherwise, there are some further ways to tweak the packing list, and we figure out how to exclude a specific file from the generated list.

Regards,

Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/94fa27db-c2be-4ded-9446-b420085c5f7en%40googlegroups.com.

Hamish Brown

unread,

Mar 1, 2023, 8:37:25 PM3/1/23

to Excel-DNA

Thanks Govert. I can build with the below profile below excluding other references (Reflections.Metadata was only added in attempt to fix the build error so no needed. The others were need at one point but redundant now).

However the getcropcoefficients function from the packed version of the addin still returns a #value error.

</Target>

</Project>

Govert van Drimmelen

unread,

Mar 1, 2023, 11:27:34 PM3/1/23

to Excel-DNA

Hi Hamish,

Sorry, my message was not quite clear about the extra <Target>.

This is my project file – can you try like this.

-Govert

</PropertyGroup>

</ItemGroup>

</ItemGroup>

</ItemGroup>

</ItemGroup>

<ExcelAddInInclude>@(References)</ExcelAddInInclude>

</PropertyGroup>

</Target>

</Project>

Hamish Brown

unread,

Mar 2, 2023, 9:19:47 PM3/2/23

to Excel-DNA

Thanks Govert. I have tried the profile you have include above but get the Reflections.Metadata reference build error with it.

Govert van Drimmelen

unread,

Mar 3, 2023, 12:38:13 AM3/3/23

to exce...@googlegroups.com

Hi Hamish,

With the project file like that, I don't expect you to get a System.Runtime.Metadata.dll file in the output directory. Are you seeing one there? If you delete it and rebuild, does it return?

As far as I could see, none of the three packages reference or depend on System.Runtime.MetaData, so it would be mystery why it gets put in the output.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/498228b9-7d22-4a8e-a300-db31533160ebn%40googlegroups.com.