Differences in date notation

Aspose.Cells Product Family
1

こんにちは

Excelで日付を入力する際に、yy/mm/ddを選択すると
"2020/01/01"と打ち込むと"2020/1/1"となる場合と"2020/01/01"となる場合があります。

表記の際の原因は、表示形式のタイプに※があるかないかによって変わることが分かりました。

表示形式のタイプに※があるものを選択した場合は、01/01のように0まで表示されます。

伺いたいのは、表示形式に※付きのものを選択した際にOfficeとAsposeでPDF変換後の表記が異なる理由になります。

何か理由がわかるようでしたら教えていただきたく。

よろしくお願いします。

Good afternoon.

When entering a date in Excel, if I select yy/mm/dd
When I type ‘2020/01/01’, sometimes it becomes ‘2020/1/1’ and sometimes ‘2020/01/01’.

We have found that the cause for the notation depends on whether the display format type has * or not.

If you select a display format type with *, it will be displayed up to 0, e.g. 01/01/01.

What we would like to ask is why the notation after PDF conversion differs between Office and Aspose when you select a display format with *.

If you know the reason, please let us know.

Thank you in advance.

日付表記.xlsx(Aspose).pdf (7.9 KB)

日付表記.xlsx(Office).pdf (129.5 KB)

スクリーンショット 2024-05-21 133522.png (87.3 KB)

2

@Mishiki_Watanabe
Based on the detailed information you provided, we have created a sample file for testing on the latest version v24.5, and we can obtain the correct results. Please refer to the attachment. result.zip (32.3 KB)

The sample code as follows:

Workbook book = new Workbook(filePath + "sample.xlsx");
book.Save(filePath + "out_net.pdf");

If you still have any questions, please provide your sample files and test code, and we will check it soon.

3

ご回答ありがとうございます。
v24.5に関してはいただいたコードで対応できることは承知いたしました。

説明不足で申し訳ありませんが、お客様都合もあり我々はv24.2を使用しています。
v24.2でもこちらのコードを記載してテストを行えばよろしいでしょうか。

お手数をおかけしますがご確認お願い致します。

Thank you for your response.
We are understood that the code you gave us can be used for v24.5.

Sorry for the lack of explanation, but we are using v24.2 for our customers’ convenience.
Is it correct that we should also test v24.2 with this code?

We apologise for the inconvenience, but please check.

4

@Mishiki_Watanabe
By using the provided sample file for testing on v24.5 and v24.2, we can still obtain the correct results. Please refer to the attachment. result.zip (58.8 KB)

The sample code as follows:

Workbook book = new Workbook(filePath + "sample.xlsx");
book.Save(filePath + "out_net_" + CellsHelper.GetVersion() +".pdf");

If you still have any questions, please provide your sample files and test code, and we will check it soon.

5

@Mishiki_Watanabe
Through our further testing, we found that in the Japanese region, when we set the format to yyyy/mm/dd, Excel will automatically set it to *yyyy/mm/dd. Your other format setting is actually yyyy/m/d. We have created a sample file for testing on v24.5 and v24.2, we can still obtain the correct results. Please refer to the attachment. result.zip (61.7 KB)

The sample code as follows:

Workbook book = new Workbook(filePath + "sample.xlsx");
book.Settings.Region = CountryCode.Japan;
book.Save(filePath + "out_net_" + CellsHelper.GetVersion() + ".pdf");

If you still have any questions, please provide your sample file and Excel language setting, we will check it soon.

6

お世話になっております。
調査結果の共有ありがとうございます。
※付きと※無しの日付表記を統一する予定はありますでしょうか？

Thank you for your assistance.
Thank you for sharing the results of your survey.
Do you have any plans to unify the date notations with and without *?

7

@Mishiki_Watanabe
Sorry, we cannot unify this situation. We follow the rules and specifications of Excel. When reading in the file, we format it completely based on the format pattern saved in the file. The symbol (*) is only the effect displayed on the Excel interface, and it is not present in the actual format pattern in the file.

8

内容は理解できました。
つまり、Asposeを用いてのPDF変換時に2000/01/01のように月と日を 「0」1で表示したい場合は、Excelの書式設定を変更しなければならないということでよろしいでしょうか？

I understand what you are saying.

So, if I want to display month and day as “0” and “1” like 2000/01/01 when converting PDF using Aspose, I have to change the formatting in Excel, is that correct?

9

@Mishiki_Watanabe
Your understanding is correct. Please modify the date format in Excel to meet the requirements.

10

Excelでは「2000/01/01」と入力した際は、Excel原本の見た目上は「2000/1/1」となり、PDF変換後も「2000/1/1」となります。AsposeのPDF変換では「2000/01/01」と入力した際はExcel原本の見た目上は「2000/1/1」でも「2000/01/01」と出力されます。
ExcelとAsposeで見た目を揃えるのもExcel原本のフォーマットを変えるしか手段はありませんか？

In Excel, when “2000/01/01” is entered, the original Excel file will look like “2000/1/1” and the PDF conversion will also look like “2000/1/1”.
In Aspose, when “2000/01/01” is entered, the original Excel file will look like “2000/1/1” but the PDF conversion will also look like “2000/01/01”.
Is there any other way to make the appearance of Excel and Aspose the same, or do I have to change the format of the Excel original?

11

@Mishiki_Watanabe

For the format that displayed with *, generally ms excel takes them as built-in number format. Built-in number formats generally change the formatted result according to different regional settings. Take the provided sample.xlsx as example, in it B2’s format is saved as static pattern “yyyy/m/d” but the format patterns of B3 and B5 are saved as static built-in number 14. So for B2 the formated result is always "2020/1/1“, but B3 and B5 may give different results for different regions, such as "1/1/2020“ for USA, “2020/01/01” for Japan.

When input data time value in ms excel, it will determine the data type and set the format for the cell accordingly. However, as apis used in program, we cannot control or force users to call different apis in fixed order. Users may set style for one cell before setting the value. So we do not change the style of cell automatically when user setting cell value.

In Aspose, when “2000/01/01” is entered, the original Excel file will look like “2000/1/1” but the PDF conversion will also look like “2000/01/01”.

Do you mean you are setting a datetime value to a cell by apis such as Cell.PutValue()? If the cell’s format has been set as built-in number(by your code, or read from template file), then the formatted result of it will vary according to different regions. The rule is just same with what I explained above. To make the formatted result fixed, you need to change the format pattern to fixed pattern, such as Style.Custom = “yyyy/mm/dd”; Or, you may make your application run with the regional setting which may format date to the expected format by default.