v1.33 produces invalid excel files

[MartinGreen]

Excel Type

• [x] XLSX
• [ ] XLSM
• [ ] CSV
• [ ] OTHER

Upload Excel File

System Monitor Export 2024-07-12 15-31.xlsx

MiniExcel Version

1.33.0

Description

Version 1.33 produces invalid files using the same code that worked well up to 1.32.1. See example below:

var values = new List<Dictionary<string, object>>();
foreach (var item in List)
{
    var dict = new Dictionary<string, object>();
    dict.Add("Time", item.Time.ToLocalTime());
    dict.Add("CPU Usage (%)", Math.Round(item.Cpu, 1));
    dict.Add("Memory Usage (%)", Math.Round(item.MemoryPercent, 1));
    dict.Add("Disk Usage (%)", Math.Round(item.DiskPercent, 1));
    dict.Add("CPU Temperature (°C)", Math.Round(item.Temp, 1));
    dict.Add("Voltage (V)", Math.Round(item.Voltage, 1));
    dict.Add("Network Usage (Kb/s)", Math.Round(item.Network, 1));
    dict.Add("Instrument", instrument);
    values.Add(dict);
}

var config = new OpenXmlConfiguration
    {
        TableStyles = TableStyles.None,
        DynamicColumns = new DynamicExcelColumn[]
        {
            new DynamicExcelColumn("Time") { Index = 0, Width = 20, Format = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern + " " + CultureInfo.CurrentCulture.DateTimeFormat.LongTimePattern },
        }
    };

using var ms = new MemoryStream();
await MiniExcel.SaveAsAsync(ms, values, excelType: ExcelType.XLSX, configuration: config);

await BlazorDownloadFileService.DownloadFile($"System Monitor Export {DateTime.Now:yyyy-MM-dd HH-mm}.xlsx", ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

Excel offers to recover the file when opening and most of it looks fine but the time column shows as a number. I run this in Blazor WASM but I dont know if that matters.

[meld-cp]

Hi @MartinGreen,

I tested with similar code and it seems to work with the latest commit. Does the code below work for you on v1.33.0?

[Fact]
public void Issue632_1()
{
    //https://github.com/mini-software/MiniExcel/issues/632
    var values = new List<Dictionary<string, object>>();

    foreach ( var item in Enumerable.Range( 1, 100 ) ) {
        var dict = new Dictionary<string, object>
        {
            { "Id", item },
            { "Time", DateTime.Now.ToLocalTime() },
            { "CPU Usage (%)", Math.Round( 56.345, 1 ) },
            { "Memory Usage (%)", Math.Round( 98.234, 1 ) },
            { "Disk Usage (%)", Math.Round( 32.456, 1 ) },
            { "CPU Temperature (°C)", Math.Round( 74.234, 1 ) },
            { "Voltage (V)", Math.Round( 6.3223, 1 ) },
            { "Network Usage (Kb/s)", Math.Round( 4503.23422, 1 ) },
            { "Instrument", "QT800050" }
        };
        values.Add( dict );
    }

    var config = new OpenXmlConfiguration
    {
        TableStyles = TableStyles.None,
        DynamicColumns = new DynamicExcelColumn[]
        {
            new DynamicExcelColumn("Time") { Index = 0, Width = 20, Format = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern + " " + CultureInfo.CurrentCulture.DateTimeFormat.LongTimePattern },
        }
    };

    var path = Path.Combine(
        Path.GetTempPath(),
        string.Concat( nameof( MiniExcelIssueTests ), "_", nameof( Issue632_1 ), ".xlsx" )
    );

    MiniExcel.SaveAs( path, values, excelType: ExcelType.XLSX, configuration: config, overwriteFile: true );

}```

[MartinGreen]

Hi @meld-cp

I tested your example and it does fail to produce a valid file for me. However, I was able to narrow it down further.

If I remove the Format value from the config it will create a valid file.

var config = new OpenXmlConfiguration
{
    TableStyles = TableStyles.None,
    DynamicColumns = new DynamicExcelColumn[]
    {
        new DynamicExcelColumn("Time") { Index = 0, Width = 20, /*Format = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern*/ },
    }
};

The moment I add Format it fails again, even with a simpler value than I was originally using. I also tried InvariantCulture with the same result.

I also tested this with 1.34 and its the same behaviour.

Thanks.

[meld-cp]

@MartinGreen interesting 🤔

Does changing the style to: TableStyles = TableStyles.Default, work by chance?

[MartinGreen]

@meld-cp Yeah changing the TableStyles fixes it!

[meld-cp]

Nice!... so the None style is broken/out of date... working on a fix 🤞