[BUG] - [Numeric Value (decimal) in dictionary causes Excel erros]

[Guz-TsTech]

Description

Using a dictionary to store key values to use SaveAsTemplateAsync causes errors when opening the file in Excel

Expected Behavior

No error when the file is opened

Actual Behavior

There's error when the file is opened.

Workarounds

Using String to export decimal value "works" but Excel process these values as string.

Environment (please fill the following information):

• MiniExcel Version: 1.42.0
• .NET Version: net8.0
• OS: macOS French (decimal separator in , not .)

[Guz-TsTech]

I got it working modifying source code as follow :

MiniExcel.Core / OpenXml / templates / OpenXmlTemplate.Impl.cs

Line 747 : if (/*!isDictOrTable && */TypeHelper.IsNumericType(type))

Removing test to detect if we process Dict or Table resolve the issue. The value is processed as numeric and everything is good.

I tried to push a pull request but I don't have the rights.

Regards.

Guillaume

[michelebastione]

Please provide your template file and a sample of your code.

[Guz-TsTech]

Hi,

My code is like that

var value = new Dictionary<string, object>();

....

value["Data"] = new Dictionnary<string, object>
{
...
["Altitude"] = 7.5, // double value from another object in real world but bug is present using these value
....
}

In Excel Template I use {{Data.Altitude}}

Opening the result, Excel gives me an error. The value is here but as string not number.

Debugging the code I saw that the value passed is "7,5" using coma not 7.5 using dot.

Using the ToString(CultureInfo.InvariantCulture) corrects the problem

[michelebastione]

If I undersand correctly what you're trying to do, your code should actually follow this structure:

var value = new Dictionary<string,object>
{
	["Data"] = new[] 
	{ 
		new { Altitude = 6.4m }, 
		new { Altitude = 7.5m }, 
		new { Altitude = 8.6m } 
	}
};

MiniExcel.SaveAsByTemplate(path, templatePath, value);

You can also use strongly typed objects if you prefer:

var value = new Dictionary<string,object>
{
	["Data"] = new Data[] 
	{ 
		new() { Altitude = 6.4m }, 
		new() { Altitude = 7.5m }, 
		new() { Altitude = 8.6m } 
	}
};

class Data
{
    public decimal Altitude { get; set; }
}

The important bit is that you use a collection of objects for your dictionary values, not other dictionaries.

[Guz-TsTech]

My code used in strong type object as my scheme was static.

So Data was a List

The object value passed to MiniExcel was a Dictionary.
The dictionary contains 3 entries

The entry Data was a list of strong type object as the 1 other entries.

But now I have to manage a dynamic scheme (defined by configuration) so I can used a list of strong typed object.

So now entry Data of the dic is a Dictionary<string, obecjt>, so yes it's a dictionary inside the dictionary.

So I decide to use (as described in doc) a List of Dictionary<string, object>. Every double value causes Excel error if the value is not integer. Every value with decimals cause Excel error.

The problem is that the cell is type as a number but the value is not formatted so the Xml value is 7,5. Using the formatting used when it'a a strong type object resolve the issue.

All the rest of MiniExcel code handle dic in dic perfectly.

I don't understand why this formatting is used for strong type object but not for dateable or dictionary. Removing the test causes formatting for numeric value for every king of data source.

Regards.

[michelebastione]

I'm not sure I understand your premise but it seems you were correct in identifying that the specific check is superfluous and prevents desired behaviour. I will make sure that removing it doesn't have unwanted side effects and if so it'll make the next release.

[Guz-TsTech]

Thanks a lot.

Using Dictionary permits to have "dynamic" scheme for exported data. So I use it.

Hope there is no reason and no unwanted side effects.

Regards