We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

CultureCustom cell style seems to be broken in 8.7.2

Hi


I am in the process of upgrading our use of Aspose to latest version.

When upgrading Aspose Cells from 8.7.1 to 8.7.2 I get a failing unittest.

The problem is reproduced in the test snippet below.

When setting cell.GetStyle().CultureCustom to the string “dd-MM-yyyy hh:mm” and retrieving it again afterwards, Aspose has changed the ‘y’ and ‘h’ to escaped characters \y and \h.

This means Excel don’t recognize the pattern - and don’t format the cell as DateTime. When entering data in excel and entering a date, 22-02-2016 13:02 excel formats it to 22-02-yyyy hh:02.

I can’t seem to find anything in release notes of this being changed - so it might be a sideeffect from some other change.

I have attached four screenshots, two from 8.7.1 and two from 8.7.2. They show how the entered date is rendered in the formatted cell, and the Format Dialog. It is clearly to see in the 8.7.1 that Excel knows yyyy is the year, and represented as åååå - å is the first letter in the danish translation of year - år.

Please let me know if this can be fixed, or I am doing something wrong.

I was hoping to get this in our next release which should ship in the beginning of next week.

Best regards
/Anders

Code to reproduce:

[TestMethod]
[TestCategory(“Excel”)]
public void EnsureCellIsDateTimeFormat_CellIsFormattedAsDateTime_NoChange_FailedOnUpgrade()
{
LicenseHandler.EnsureLicenseIsSet(); // Ensure license is set - you should do that in another way
var workbook = new Aspose.Cells.Workbook();
var cell = workbook.Worksheets[0].Cells[0, 0];
var style = cell.GetStyle();
const string expectedStyle = “dd-MM-yyyy hh:mm”;
style.CultureCustom = expectedStyle;
cell.SetStyle(style);

// cell.EnsureCellIsDateTimeFormat();

var actualStyle = cell.GetStyle().CultureCustom;

var file = @“C:\temp\test.xlsx”;
if (File.Exists(file))
File.Delete(file);
workbook.Save(file);
System.Diagnostics.Process.Start(file);

Assert.AreEqual(expectedStyle, actualStyle);
}

Hi Anders,


Thank you for contacting Aspose support.

I have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 8.7.2.3 and Excel 2013 x64 to inspect the result. I am afraid, I am not able to replicate the said issue on my side. Attached to this post is the resultant spreadsheet which I have tested by manually inserting a DateTime string to cell A1. Could you please give the latest version a try on your side as well? In case the problem persists, please provide us details of your environment as follow.

  • Operating system version & architecture
  • .NET Framework version
  • Locale/Region of the machine
  • Version & Language of the Excel application

Hi


Your attached spreadsheet seems fine to me - and has the correct åååå for the year part in Excel.

I get a bunch of compile errors if I try to compile my source code with the 8.7.2.3 you link to.

Primarily about places where I do stuff like foreach (var sheet in Workbook.Worksheets) , my variable sheet now becomes of type Object - and thus when i use it later in code it can’t recognize it.

Is this because 8.7.2.3 is some sort of dev release - or had you introduced a breaking change on your collections so they no longer return correct types - but objects if you enumerate them? Or because it is a .Net 2.0 ref you sent?

Anyway, my info is:
OS: Win 10 Pro, 64 bit
.Net : .Net 4.6.1.
Locale:
Country : Denmark
Languages : Dansk (danish), English (US)
Region:
Format : Danish (Denmark)
Excel : Excel 2013 (15.0.4816.1000 (MSO 15.0.4816.1000) 32-bit - in English

As a footnote here, is that it is important that I can set the date format in a culture invariant way, so that I can write generic software that works all over the worlds in various cultures.

Hi again


If you supply me with a download link to 8.7.2.3 for .Net 4.0 I will happily test out if that works for me.

I can’t find a link for it on the download pages.

Hi again,


Please find the attachment for the same release, however, the binaries are compiled against .NET Framework 4.0. I hope you will not face the compilation errors with this assembly. Please give the scenario another try and feed us back with your results.

Please note, Aspose.Cells for .NET 8.7.2.3 is a maintenance release which we share in forums only, however, the major releases are available in Aspose download section as well as through NuGet.
Hi Anders,

licenselogimatic:
I get a bunch of compile errors if I try to compile my source code with the 8.7.2.3 you link to.

Primarily about places where I do stuff like foreach (var sheet in Workbook.Worksheets) , my variable sheet now becomes of type Object - and thus when i use it later in code it can't recognize it.

Is this because 8.7.2.3 is some sort of dev release - or had you introduced a breaking change on your collections so they no longer return correct types - but objects if you enumerate them? Or because it is a .Net 2.0 ref you sent?

I believe the mentioned problem (as quoted above) is caused due to the reason that the previously shared assembly was compiled against .NET Framework 2.0. I have faced the similar problem with that assembly but as soon as I have re-added the reference of Aspose.Cells.dll compiled against .NET Framework 4.0, I am no longer facing this issue.

licenselogimatic:

Anyway, my info is:
OS: Win 10 Pro, 64 bit
.Net : .Net 4.6.1.
Locale:
Country : Denmark
Languages : Dansk (danish), English (US)
Region:
Format : Danish (Denmark)
Excel : Excel 2013 (15.0.4816.1000 (MSO 15.0.4816.1000) 32-bit - in English


Thank you for sharing the environment details. I will wait for your feedback before moving forward to setup the required environment for reevaluation of the said scenario.

licenselogimatic:

As a footnote here, is that it is important that I can set the date format in a culture invariant way, so that I can write generic software that works all over the worlds in various cultures.


What I understand from your comments as quoted above is that you wish to format the date values in such a way that they are automatically converted to locale based formats when spreadsheet is loaded in Excel from different region. I believe, Excel application handles the format conversion very well, however, the selected format should be from the predefined list available in Format Cells dialog (Number tab).

If I haven't understood the concerns correctly then please share more details, preferably along with illustrations or samples.

Hi again


The 8.7.2.3 release does compile better, but has the same flaw as 8.7.2.0.

Result Message: Assert.AreEqual failed. Expected:. Actual:<dd-MM-\y\y\y\y \h\h:mm>.

Best regards
/Anders

Hi Anders,


Thank you for sharing your feedback.

We have reevaluated the presented scenario after simulating your environment and we are able to replicate the said issue on our side. Moreover, we have performed tests against Aspose.Cells for .NET 8.7.1 to receive expected results. Based on these observations, we have logged this incident for further investigation by the product team. The ticket Id for your future reference is CELLSNET-44378. Please spare us little time to properly analyze the scenario and get back to you with updates in this regard.

We are sorry for the inconvenience.

Hi again,


This is to update you that we have further investigated the matter logged earlier as CELLSNET-44378, and have found the said behaviour as not a bug on the part of Aspose.Cells APIs, rather an enhancement. Please note, the previous releases of Aspose.Cells APIs did not support the year pattern(å) of Denmark and the API used to take ‘y’ as the year pattern for CultureCustom. With recent revision of the API, we have supported the correct pattern char(å) for Denmark and made the CultureCustom act same as Excel application. You can confirm it as well with the region as Denmark, when you input “yyyy” in the custom field for formatting cell, the “yyyy” will be taken as normal string value instead of year place-holder.

That said, if you wish to use the standard pattern for date formatting, you should use Style.Custom instead of Style.CultureCustom. This is because the API always uses ‘y’ as the year and ‘h’ as the hour for Style.Custom, regardless of the Workbook’s region.

Thank you for your understanding.

Hi Babar


I have replicated this with 8.7.2.3 (and 8.7.2.0) and by using cellStyle.Custom as you suggest everything works fine.

Thanks for the replies - you can consider this issue closed.

best regards
/Anders

Hi,


Good to know that your issue is sorted out by Style.Custom attribute. We have closed your ticket now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.