Using the SaveAsByTemplate, configuration has no effect

[The-Mojoo]

Excel Type

• XLSX
• XLSM
• CSV
• OTHER

Upload Excel File

template.xlsx

MiniExcel Version

1.41.2

Description

Because I previously mentioned the issue of not supporting the long type being set as text,this.
So I changed my mind and went through the formatted template to upgrade to a file, but I found that the formatting set by miniexcel configuration did not work, including other configurations did not work, and I wanted to know what the problem was.

var tmplatePath = Path.Combine("C:\\Demo\\", $"template.xlsx");
var path = Path.Combine("C:\\Demo\\", $"{Guid.NewGuid()}.xlsx");

var data = new[]
{
    new
    {
        Num= 50.5, 
        BaiFen= 0.5, 
        LongText = "1550432695793487872",  
        DateField  = new DateTime(2025,6,17),
        TextField  = "ABC123"
    }
};

var config = new OpenXmlConfiguration
{
    AutoFilter = false,
    EnableAutoWidth=true,
    FastMode=true,
    DynamicColumns = new[]
    {
        new DynamicExcelColumn("Num")    { Format = "0.00"},
        new DynamicExcelColumn("BaiFen")    { Format = "0.00%"},
        new DynamicExcelColumn("DateField"){ Format = "yyyy-mm-dd"},
        new DynamicExcelColumn("TextField"){ Format = "@" }
    }
};

//MiniExcel.SaveAs(path, data , configuration: config);
MiniExcel.SaveAsByTemplate(path, tmplatePath, new { items = data }, configuration: config);

[michelebastione]

Currrently almost all configuration settings are unsupported by SaveAsByTemplate. If you could you please translate your original issue I might be able to help you out

[The-Mojoo]

Currrently almost all configuration settings are unsupported by SaveAsByTemplate. If you could you please translate your original issue I might be able to help you out

So that's it. I specifically checked the parameters of the method in SaveAsByTemplate. There is a configuration. I thought it was my problem, but I found that it would not work when only EnableAutoWidth was enabled.

Thank you for your answer. I didn't expect you to care about my fundamental problem.

Below is the link to my original question. I will repeat my question again.

There are long type fields in my original data, such as "1550432695793487872". I want to set it to text format. I set the format to @ in Miniexcel, but in fact, in the exported file, after clicking on the data, and then clicking on other positions, the original data will become scientific notation "1.55043E+18", and the original value will also become "1550432695793480000". The cell is not actually set to text type. If it is successfully set to text type, then there will be no problem and its value will not change.

var path = Path.Combine("C:\\Demo\\", $"{Guid.NewGuid()}.xlsx");
var config = new OpenXmlConfiguration
{
    AutoFilter = false,
    DynamicColumns =
    [
        new DynamicExcelColumn("long2") { Format = "@", Width = 25 },
    ]
};
var value = new[] { new { long2 = "1550432695793487872" } };
MiniExcel.SaveAs(path, value, configuration: config);

[michelebastione]

This looks like a problem related to some DynamicColumn, I will investigate but since this doesn't appear to happen when exporting strong typed classes, in the meantime if it's feasible for you I would suggest going for that route:

var path = Path.Combine("C:\\Demo\\", $"{Guid.NewGuid()}.xlsx");
var config = new OpenXmlConfiguration
{
    AutoFilter = false
};
Test[] value = [new() { long2 = 1550432695793487872 } ];
MiniExcel.SaveAs(path, value, configuration: config);

class Test
{
    [ExcelColumn(Format = "#.#"), Width = 25)]
    public long long2 { get; set; }
}

If you really need to use anonymous types the best next thing I can suggest is to try applying a direct transformation:

var path = Path.Combine("C:\\Demo\\", $"{Guid.NewGuid()}.xlsx");
var config = new OpenXmlConfiguration
{
    AutoFilter = false,
    DynamicColumns =
    [
        new DynamicExcelColumn("long2") { Width = 25 },
    ]
};
var value1 = new[] { new { long2 = 1550432695793487872 } };
var value2 = value1.Selecyt(x => new { long2 = x.long2.ToString("#.#") });
MiniExcel.SaveAs(path, value2, configuration: config);

[The-Mojoo]

This looks like a problem related to some DynamicColumn, I will investigate but since this doesn't appear to happen when exporting strong typed classes, in the meantime if it's feasible for you I would suggest going for that route:

var path = Path.Combine("C:\Demo\", $"{Guid.NewGuid()}.xlsx");
var config = new OpenXmlConfiguration
{
AutoFilter = false
};
Test[] value = [new() { long2 = 1550432695793487872 } ];
MiniExcel.SaveAs(path, value, configuration: config);

class Test
{
[ExcelColumn(Format = "#.#"), Width = 25)]
public long long2 { get; set; }
}
If you really need to use anonymous types the best next thing I can suggest is to try applying a direct transformation:

var path = Path.Combine("C:\Demo\", $"{Guid.NewGuid()}.xlsx");
var config = new OpenXmlConfiguration
{
AutoFilter = false,
DynamicColumns =
[
new DynamicExcelColumn("long2") { Width = 25 },
]
};
var value1 = new[] { new { long2 = 1550432695793487872 } };
var value2 = value1.Selecyt(x => new { long2 = x.long2.ToString("#.#") });
MiniExcel.SaveAs(path, value2, configuration: config);

I can`t use .ToString("#.#"),my IDE will have error.
Moreover, the above code does not seem to make any adjustments to the Excel format settings. Although I did not successfully compile and run the second code, the output of the first code is the same as the problem I raised before. Although it looks normal in the file, if you click on the cell and then click on other cells, its value will change.
There will be no problem only when the cell format is text format.

[michelebastione]

You can try using ToString without any parameters, it doesn't make a difference when the value is an int or a long.

[The-Mojoo]

You can try using ToString without any parameters, it doesn't make a difference when the value is an int or a long.

Yes,i know.
5a86df83-f670-4a75-a281-384112e96501.xlsx
I think you can try what I said, double-click the cell A2 to trigger the edit state, and then click any other cell, you will find that the value of A2 has changed.
The main reason is because it is not in the Text format.

[michelebastione]

I see. This could take a while to address properly. The best workaround I can suggest is to apply a transformation that adds an apostrophe to your value:

var value1 = new[] { new { long2 = 1550432695793487872 } };
var value2 = value1.Selecyt(x => new { long2 = "'" + x.long2.ToString() });
MiniExcel.SaveAs(path, value2, configuration: config);

I know this is far from ideal but at least accidentally double clicking the cell will not change the value, and for the most part Excel ignores it.

[The-Mojoo]

I have noticed that updates have become much more frequent recently, and it seems that the official version 2.0 is approaching. I would like to ask if there has been any progress on this issue. I did not adopt the method involving "`" before, as it was somewhat cumbersome, and instead used other methods.

[michelebastione]

Hello @The-Mojoo. The plan is to eventually resolve every issue but I cannot give specific guarantees regarding any particular one. What I can say is that your original issue is more likely to get resolved before this one, as a lot more work has been put into improving and optimizing the exporting functionality and the base mapper compared to the templating engine. A new preview version is also coming soon, and it will probably be the last one before a full fledged v2.0, so stay tuned!

[The-Mojoo]

Hello @The-Mojoo. The plan is to eventually resolve every issue but I cannot give specific guarantees regarding any particular one. What I can say is that your original issue is more likely to get resolved before this one, as a lot more work has been put into improving and optimizing the exporting functionality and the base mapper compared to the templating engine. A new preview version is also coming soon, and it will probably be the last one before a full fledged v2.0, so stay tuned!

Thank you very much. I have also been continuously following the updates on the version. With the major version update approaching, I am concerned that this issue might eventually be overlooked. My issue with 810 was also to address the initial problem with 627. I just checked the time, and it has already been more than a year. Thank you for your efforts!

[michelebastione]

I understand the frustration, there are more than a couple issue in regards to the formatting of data. It's a tricky problem to tackle but sooner or later it will be addressed. Thank you for your patience.