Created attachment 194392 [details] Sample Procedure: 1. Create a new xlsx file in MS Office 2021 (sample: LOtest.xlsx) 2. Create a cell containing a date value and set the format to the Japanese calendar 3. Save it in xlsx format 4. Open it in CALC 5. The date format set in the cell is invalidated, and the numerical value is displayed Version of LO that reproduced the phenomenon Version: 7.6.6.3 (X86_64) / LibreOffice Community Build ID: d97b2716a9a4a2ce1391dee1765565ea469b0ae7 CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win Locale: ja-JP (ja_JP); UI: ja-JP Calc: threaded Version: 24.2.0.0.beta1 (X86_64) / LibreOffice Community Build ID: 5f390384195b7264c6e52add9e90a39790285249 CPU threads: 12; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win Locale: ja-JP (ja_JP); UI: ja-JP Calc: threaded
Created attachment 194393 [details] Expected behavior
Using code number in LibreOffice [$-411]GGGE\年M\月D\日 and saving as XLSX open fine in Excel, but converts it to: [$-ja-JP,103]ggge年m月d日 saving with Excel again open fine with Calc and Excel doesn't accept introduce directly [$-411]GGGE\年M\月D\日 The code number in the sample file [$-ja-JP-x-gannen]ggge"年"m"月"d"日";@ having in Excel the option for code number, to select 'Imperial' as calendar. Version: 24.2.3.2 (X86_64) / LibreOffice Community Build ID: 433d9c2ded56988e8a90e6b2e771ee4e6a5ab2ba CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded
On pc Debian x86-64 with master sources updated today, I could reproduce this. I noticed on console: warn:legacy.osl:39403:39403:sc/source/filter/oox/numberformatsbuffer.cxx:1847: lclCreateFormat - cannot create number format '[$]ggge"年"m"月"d"日";@' part of bt: #0 SvNFFormatData::PutEntry(SvNFLanguageData&, NativeNumberWrapper const&, rtl::OUString&, int&, SvNumFormatType&, unsigned int&, o3tl::strong_int<unsigned short, LanguageTypeTag>, bool) (this=0x55641914d1b8, rCurrentLanguage=..., rNatNum=..., rString="[$]ggge\"年\"m\"月\"d\"日\";@", nCheckPos=@0x7ffc6fecb1b0: 18, nType=@0x7ffc6fecb1ae: SvNumFormatType::ALL, nKey=@0x7ffc6fecb1b4: 0, eLnge=..., bReplaceBooleanEquivalent=true) at svl/source/numbers/zforlist.cxx:706 #1 0x00007f72b07bcdd5 in SvNumberFormatter::PutEntry(rtl::OUString&, int&, SvNumFormatType&, unsigned int&, o3tl::strong_int<unsigned short, LanguageTypeTag>, bool) (this=0x55641914d1a0, rString="[$]ggge\"年\"m\"月\"d\"日\";@", nCheckPos=@0x7ffc6fecb1b0: 18, nType=@0x7ffc6fecb1ae: SvNumFormatType::ALL, nKey=@0x7ffc6fecb1b4: 0, eLnge=..., bReplaceBooleanEquivalent=true) at svl/source/numbers/zforlist.cxx:717 #2 0x00007f72b07bd002 in SvNumberFormatter::PutandConvertEntry(rtl::OUString&, int&, SvNumFormatType&, unsigned int&, o3tl::strong_int<unsigned short, LanguageTypeTag>, o3tl::strong_int<unsigned short, LanguageTypeTag>, bool, bool) (this=0x55641914d1a0, rString="[$]ggge\"年\"m\"月\"d\"日\";@", nCheckPos=@0x7ffc6fecb1b0: 18, nType=@0x7ffc6fecb1ae: SvNumFormatType::ALL, nKey=@0x7ffc6fecb1b4: 0, eLnge=..., eNewLnge=..., bConvertDateOrder=false, bReplaceBooleanEquivalent=true) at svl/source/numbers/zforlist.cxx:736 #3 0x00007f72b078ff3d in SvNumberFormatsObj::addNewConverted(rtl::OUString const&, com::sun::star::lang::Locale const&, com::sun::star::lang::Locale const&) (this=0x55641b924bc0, aFormat="[$]ggge\"年\"m\"月\"d\"日\";@", nLocale=..., nNewLocale=...) at svl/source/numbers/numfmuno.cxx:519 #4 0x00007f7273c09290 in oox::xls::(anonymous namespace)::lclCreateFormat(com::sun::star::uno::Reference<com::sun::star::util::XNumberFormats> const&, rtl::OUString const&, com::sun::star::lang::Locale const&, com::sun::star::lang::Locale const&) (rxNumFmts=uno::Reference to (SvNumberFormatsObj *) 0x55641b924be8, rFmtCode="[$]ggge\"年\"m\"月\"d\"日\";@", rToLocale=..., rFromLocale=...) at sc/source/filter/oox/numberformatsbuffer.cxx:1830 #5 0x00007f7273c091fb in oox::xls::NumberFormat::finalizeImport(com::sun::star::uno::Reference<com::sun::star::util::XNumberFormats> const&, com::sun::star::lang::Locale const&) (this=0x55641b6b6bd0, rxNumFmts=uno::Reference to (SvNumberFormatsObj *) 0x55641b924be8, rFromLocale=...) at sc/source/filter/oox/numberformatsbuffer.cxx:1956 #6 0x00007f7273c0e204 in oox::xls::(anonymous namespace)::NumberFormatFinalizer::operator()(oox::xls::NumberFormat&) const (this=0x7ffc6fecb418, rNumFmt=...) at sc/source/filter/oox/numberformatsbuffer.cxx:1860 nCheckPos is calculated in SvNumberformat ctr (svl/source/numbers/zformat.cxx) Eike: thought you might be interested in this one.
Odd, the attachment 194392 [details] sample file's generator is said to be <Application>Microsoft Excel Online</Application> but there's also <ext uri="{7626C862-2A13-11E5-B345-FEFF819CDC9F}" xmlns:loext="http://schemas.libreoffice.org/"> <loext:extCalcPr stringRefSyntax="CalcA1ExcelA1"/> </ext> which indicates the file has been saved also (probably earlier) in LibreOffice. So this is a round-trip result. (In reply to m_a_riosv from comment #2) > Using code number in LibreOffice > [$-411]GGGE\年M\月D\日 > and saving as XLSX open fine in Excel, > but converts it to: > [$-ja-JP,103]ggge年m月d日 > saving with Excel again open fine with Calc [$-ja-JP,103] looks like (in the UI) they now use a language tag (ja-JP) instead of LCID and add a calendar identifier (103). > and Excel doesn't accept introduce directly [$-411]GGGE\年M\月D\日 Great, always something new in Excel.. > The code number in the sample file > [$-ja-JP-x-gannen]ggge"年"m"月"d"日";@ > having in Excel the option for code number, to select 'Imperial' as calendar. Which is bad. x-gannen (as any x-...) is a private-use extension in a language tag. We'd have to create mappings for those. So, x-gannen is the (now) Reiwa imperial calendar in ja-JP. Related: https://support.microsoft.com/en-us/office/japan-era-changes-and-gannen-c52091af-848d-481f-a861-26ae170f8dbd As a side note, laughably on that page the format codes for "older version of Excel" contain question marks instead of Japanese characters.. (In reply to Julien Nabet from comment #3) > warn:legacy.osl:39403:39403:sc/source/filter/oox/numberformatsbuffer.cxx: > 1847: lclCreateFormat - cannot create number format '[$]ggge"年"m"月"d"日";@' It seems (didn't investigate but looks like) the "-ja-JP-x-gannen" was skipped/stripped and then the single [$] doesn't make sense (the entire [$-ja-JP-x-gannen] wouldn't make sense either as it is unhandled). If Excel now really introduced language tags instead of LCIDs in number format codes saved to files we'll have some more fun. Well, at least it seems they can still read their own old dog food.
What is there even stored in the file? <numFmt numFmtId="176" formatCode="[$]ggge"年"m"月"d"日";@" x16r2:formatCode16="[$-ja-JP-x-gannen]ggge"年"m"月"d"日";@"/> A x16r2:formatCode16 attribute?!? Of course we read only the formatCode attribute. The OOXML ECMA standard does not have formatCode16. Seems to be https://learn.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/8c82391e-f128-499a-80a1-734b8504f60e For formatCode="[$]..." we maybe could ignore the lone single stray leading "[$]" so at least the remaining format code would be valid (and the Gengou calendar would be switched to due to the GGGE sequence), but it lost its locale designator, which already Excel didn't save, so it would work only in a default ja-JP locale.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b3f6d4f0d380b26fb88ff586a09b4525ed6585b9 Related: tdf#161301 strip single stray leading "[$]" garbage from formatCode It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
With that fix, in a ja-JP environment the date in the sample document is displayed as expected. To force test on Linux or macOS one can start LO from the command line with LC_CTYPE=ja-JP.UTF-8 soffice In other locales the date will be displayed as something like AD24年5月28日 (where AD is the era of the locale and 24 the two-digit year of the calendar). Pending review https://gerrit.libreoffice.org/c/core/+/168311 for 24-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-24-2": https://git.libreoffice.org/core/commit/bda60888cdae9922ea933190e391628ba2e3e3b9 Related: tdf#161301 strip single stray leading "[$]" garbage from formatCode It will be available in 24.2.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/04954ffee19bb2357dca69f929ac660f99d80f16 tdf#161301: sc_subsequent_filters_test2: Add unittest It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.