Free Support Forum - aspose.com

Maintain built-in Number format for Time according to User's Regional Settings for Russia using Aspose.Cells for .NET in C#

#1

Original regional settings culture: ru-UA
Date-Time value setup in cell: “16.09.2019 18:50:00”.
Build-in Number style setup in cell: 22.
Result cell’s display value: “16.09.2019 18:50”

After changing user’s regional settings for short time and short date format to following:
Short time format: “hh:mm tt” (was HH:mm).
Short date format: “dd.MM.yy” (was dd.MM.yyyy).
and building the save Excel file with the same Number style 22 we have display value “16.09.19 18:50”.
So as we see, date part is changed as required: year is now in two digits.
But time part still the same but it should be AM\PM format, i.e. “6:50 PM”.

#2

@23W,

Thanks for the details.

Please provide your template file(s) and paste your sample code (runnable) that your using to evaluate your issue. Also provide some screenshots to demonstrate the issue, we will check it soon.

Could you try your steps in MS Excel manually and check if you see different results in the output file than what is produced by Aspose.Cells. Aspose.Cells follows MS Excel standards and specifications when applying formattings/styles to cells values, so I have doubt if this is an issue with Aspose.Cells as it might be expected behavior.

#3

Code sample:

                // Date only
                {
                    ws.Cells[0, 0].Value = "Date:";
                    ws.Cells[0, 1].Value = DateTime.Now.Date;
                    var style = ws.Cells[0, 1].GetStyle();
                    style.Number = 14;
                    ws.Cells[0, 1].SetStyle(style);
                }

                // Time only
                {
                    ws.Cells[1, 0].Value = "Time:";
                    ws.Cells[1, 1].Value = DateTime.Now;
                    var style = ws.Cells[0, 1].GetStyle();
                    style.Number = 20;
                    ws.Cells[1, 1].SetStyle(style);
                }

                // Date&Time
                {
                    ws.Cells[2, 0].Value = "Date & Time:";
                    ws.Cells[2, 1].Value = DateTime.Now;
                    var style = ws.Cells[0, 1].GetStyle();
                    style.Number = 22;
                    ws.Cells[2, 1].SetStyle(style);
                }

Result with full C# code:
AsposeCellsTets.zip (6.9 KB)

Screenshots. All from the same PC but different regional settings:

  1. ru-UA
    CaptureRUUA.PNG (2.9 KB)

  2. en-US
    CaptureENUS.PNG (2.7 KB)

As you see, after changing regional settings to en-US, time format in cells “Time:” and “Date & Time:” still “hh:mm”, but should be “hh:mm AM\PM” according to short time format pattern from regional settings.

#4

@23W,
I have checked the scenario using the following sample code and observed that this is expected behaviour. You may please visit this page where a list of number values and their corresponding formatted strings are displayed. You can see that Number value 22 does not contain the AM/PM part.

Workbook wb = new Workbook();
for (int i = 0; i < 48; i++)
{
    Cell cell = wb.Worksheets[0].Cells[i,0];
    var style = cell.GetStyle();
    style.Number = i;
    cell.Value = DateTime.Now;
    cell.SetStyle(style);

}

wb.Save("output.xlsx");

If you want to display AM/PM with the long date time, you may please try the custom format as depicted in the following sample code and share the feedback.

Workbook wb = new Workbook();
Cell cell = wb.Worksheets[0].Cells[0, 0];
Aspose.Cells.Style style = wb.CreateStyle();
StyleFlag flag = new StyleFlag();
style.Custom = "dd.MM.yy hh:mm AM/PM";
flag.NumberFormat = true;
cell.SetStyle(style, flag);
cell.Value = DateTime.Now;
wb.Save("CustomFormat.xlsx");
#5

Thank you, but it is not that I asked.
At the first your doc is not complete, it shows examples for en-US (or en_GB, I don’t know exactly) culture only.
For example number 22 is “m/d/yy h:mm” that transformed in ru-UA culture to “dd.mm.yyyy hh:mm”, i.e
it reads regional setting and transform it pattern.

At the second, I asked about built-in format that is able to transform in regional settings completely, i.e. date part to DateTimeFormat.ShortDatePattern and time part to DateTimeFormat.ShortTimePattern. As we see from example, date part works prefectly in built-in format 22, but time part not.
Also it would be nice to have built-in format for time part only that is also depends on regional settings.

#6

@23W,
We are working on it and request you to spare us little time to share our feedback.

1 Like
#7

@23W,
Could you please explain if you are able to achieve these requirements using MS Excel which could show your desired results in both locales. Share those files and screenshots for our analysis.

#8

Hi, I’ll try explain my aims with more details.
I need cell formatting setting that will allow me to show date\time value in following formats:

  • time only, in short time pattern of current user (from regional settings),
    STP.png (10.7 KB)
  • date only, in short date pattern of current user (from regional settings),
    SDP.png (11.6 KB)
  • date + time as combination of short date pattern + short time pattern.

It would be nice if Excel showed these values based on current regional settings. So if result xlsx file is opened in en-US region, it will be format “mm/dd/yyyy” and “hh:mm AM/PM”. Or if the same file is opened in ru-UA region. it will be “dd.mm.yyy” and “hh:mm”. I think it’s possible via built-in formats only.
But I suspect, Excel doesn’t support (doesn’t contain) such built-in formats. I’ve tried all built-in formats from range 0-48 (from your example) and didn’t find suitable formats, but maybe I missed out something, you knows these formats better than me.

Ok, if it is not possible by built-in, could you pleas add utility method to build such custom formats based on CurrentCulture as input parameter or for current Thread.CurrentThread.CurrentCulture ?

Thank you.

#9

@23W,
We are working on it and will share our feedback soon.

1 Like
#10

@23W,
We have logged the issue in our database for investigations. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46921 - Values based on regional formats
1 Like
#11

@23W,

We evaluated your issue. We cannot fix your issue or support your needs.

  1. For your information, the displayed value for a cell with specified built-in number is controlled by MS Excel completely and there is not any available way for us to change it. So, we are afraid, you have to use specified custom format pattern to format the cells accordingly.

  2. Secondly, your requirement and the corresponding solution is a special case and should be only applicable for your special scenario/case. For such situation, we are afraid there are very little things that we can do for the CultureInfo relevant information. Instead, it should be easier for you to manipulate the CultureInfo you prefer to, or build required format pattern from it by yourselves, such as parsing or rebuilding the properties of CultureInfo.DateTimeFormat.

Thanks for your understanding!

#12

Thank you for answer.