FsExcel
FsExcel copied to clipboard
Regression tests failing - Sep versus Sept
Expected: Sept Actual: Sep
This seems odd to me. The code that generates the abbreviated month name for September uses the "en-GB" culture and it's my understanding that will generate the string "Sept". "Sep" would get generated by the invariant culture or the "en-US" culture.
I'm not seeing this error when I run the tests on my machine. Are you seeing this when you run the tests locally or when they run as part of the build?
I get the same error running "dotnet test" locally. This is because row 33 value in "Actual/Worksheets.xlsx" (Sep) does not match "Expected/Worksheets.xlsx" (Sept) When I run this I get "Sep" back
open System.Globalization
let britishCulture = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB")
let monthAbbreviation = britishCulture.DateTimeFormat.AbbreviatedMonthNames[8]
monthAbbreviation = "Sep"
I guess the expected value in "Expected/Worksheets.xlsx" should be changed to "Sep".
If we run
open System
open System.Globalization
let print (x: CultureInfo) =
printfn "%-20s | %-10s | %s" x.DisplayName x.IetfLanguageTag x.DateTimeFormat.AbbreviatedMonthNames[8]
CultureInfo.GetCultures(CultureTypes.NeutralCultures)
|> Array.filter (fun x -> x.DateTimeFormat.AbbreviatedMonthNames[8].Contains("sept", StringComparison.InvariantCultureIgnoreCase))
|> Array.iter print
Output:
Spanish | es | sept.
Estonian | et | sept
French | fr | sept.
Latvian | lv | sept.
Romanian | ro | sept.
we can see that only Spanish, Estonian, French, Latvian and Romanian cultures contain "Sept".
This turns out to be a .NET version/underlying OS issue. On Windows 11 (with the latest updates), .NET Core 3.1 the abbreviated month name for September in the "en-GB" culture is "Sep". On the same OS, .NET 5 and .NET 6 it is "Sept". I knew that culture data could change. I wasn't expecting that change though.
I'm not sure how we should fix this.
Can I suggest that the values are hard-coded to get around this.
The code below has the minimal changes to get it to work:
open System.IO
open FsExcel
open ClosedXML.Excel
let savePath = $@"{__SOURCE_DIRECTORY__}\temp"
let britishCultureNativeName = "English (United Kingdom)"
let ukrainianCultureNativeName = "українська"
let britishCultureDateTimeFormatGetMonthName =
[ "January"; "February"; "March"; "April"; "May"; "June"; "July";
"August"; "September"; "October"; "November"; "December" ]
let britishCultureDateTimeFormatAbbreviatedMonthNames =
[ "Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct";
"Nov"; "Dec" ]
let ukrainianCultureDateTimeFormatGetMonthName =
[ "січень"; "лютий"; "березень"; "квітень"; "травень"; "червень";
"липень"; "серпень"; "вересень"; "жовтень"; "листопад"; "грудень" ]
let ukrainianCultureDateTimeFormatAbbreviatedMonthNames =
[ "січ"; "лют"; "бер"; "кві"; "тра"; "чер"; "лип"; "сер"; "вер"; "жов";
"лис"; "гру" ]
[
Worksheet britishCultureNativeName
for m in 0..11 do
let monthName = britishCultureDateTimeFormatGetMonthName.[m]
Cell [ String monthName ]
Cell [ Integer monthName.Length ]
Go NewRow
Worksheet ukrainianCultureNativeName
for m in 0..11 do
let monthName = ukrainianCultureDateTimeFormatGetMonthName.[m]
Cell [ String monthName ]
Cell [ Integer monthName.Length ]
Go NewRow
Worksheet britishCultureNativeName // Switch back to the first worksheet
Go (RC(13, 1))
for m in 0..11 do
let monthAbbreviation = britishCultureDateTimeFormatAbbreviatedMonthNames.[m]
Cell [ String monthAbbreviation ]
Cell [ Integer monthAbbreviation.Length ]
Go NewRow
Worksheet ukrainianCultureNativeName // Switch back to the second worksheet
Go (RC(13, 1))
for m in 0..11 do
let monthAbbreviation = ukrainianCultureDateTimeFormatAbbreviatedMonthNames.[m]
Cell [ String monthAbbreviation ]
Cell [ Integer monthAbbreviation.Length ]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "Worksheets.xlsx"))
and
// Open Worksheets.xlsx created in the previous snippet:
let workbook = new XLWorkbook(Path.Combine(savePath, "Worksheets.xlsx"))
let britishCultureNativeName = "English (United Kingdom)"
let ukrainianCultureNativeName = "українська"
let altMonthNames = [| "Vintagearious"; "Fogarious"; "Frostarious"; "Snowous"; "Rainous"; "Windous"; "Buddal"; "Floweral"; "Meadowal"; "Reapidor"; "Heatidor"; "Fruitidor" |]
[
Workbook workbook
Worksheet ukrainianCultureNativeName
Go(RC(1,3))
Cell [FormulaA1 $"='{britishCultureNativeName}'!B1*2" ]
Worksheet britishCultureNativeName
InsertRowsAbove 12 // The cell reference in the formula above will be updated to B13
for m in 0..11 do
Cell [ String altMonthNames[m] ]
Cell [ Integer altMonthNames[m].Length ]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "Worksheets.xlsx")) // Typically, you would save to a different file.
This has been done and works. Thanks!