Unwanted date string formatting with ConvertStringToNumericValue

Aspose.Cells Product Family
1

Dear Support,

My sheets contains cells with values like “2015-02-10”.
Alhtough these are dates but the formatting of cells is set to “Text” type in Excel.

Once I apply Cells.ConvertStringToNumericValue() command for the sheet the string given below is converted to some numerical value like “41639”.
This is not what I expected.

I would rather expect no modifications applied to my string.
Is there some way to achieve it ?

2

Hi,

Thanks for your posting and using Aspose.Cells.

ConvertStringToNumeric() method will convert all the possible convertible string values into numbers and dates. It will not see if the string value has been set as text type.

As a workaround, you should set it as Date type instead of Text type, then the Excel will display proper date instead of numeric value of date.

3

Hi,

The more I work with this issue the more doubts I have about the library behaviour…

Considering an Excel cell of Text format with value “2015-02-10”.
ConvertStringToNumericValue() is producing a single integer number (like 41639) from this value. But what actually is that integer ? If it is a numerical representation of the date than how can it guess a possible formatting of the date, in general case ? Is it February or October in this particular case ?

I believe the library goes to far in this case. If a string is really a number than ConvertStringToNumericValue() behaviour is great. But “2015-02-10” is not “number” in general case so it could be ignored by ConvertStringToNumericValue(), I think.

Is there any possibility so the ConvertStringToNumericValue() method has a boolean parameter to be set by developer ? It could switch a default behaviour for dates not to convert them to numbers automatically…

4

Hi,

Thanks for your posting and using Aspose.Cells.

It depends on the short date format of your system settings, it tells Aspose.Cells to decide if the day comes first or month comes first.

Once, you got the number, you format it as Date and it will show you what date it is. Please see the screenshot for your reference.

I have also attached the output Excel file generated with the following sample code which is also shown inside the screenshot.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“A1”];

cell.PutValue(“2015-02-10”);


worksheet.Cells.ConvertStringToNumericValue();


workbook.Save(“output.xlsx”);
5

OK. However since the original format of the cell is “Text” than the number received from ConvertStringToNumericValue() might be different on different machines (different OS locales set), right ?


6

Hi,

Thanks for your posting and using Aspose.Cells.

Yes, it depends on the short date format of the system. I have looked into this issue further and found that when date is not ambiguous, Aspose.Cells resolves it, for example 2015-14-02, here the month is February and there is no ambiguity. However in 2015-02-03, month is ambiguous, here Aspose.Cells will decide on the basis of short date format of the system.

So your number might differ on different systems because of the short date format setting. But for non-ambiguous dates, you will always have a same number.

7

OK. I understand current behaviour. Thank you for detailed explanation.

Still, I think you should give developers a choice to disable automatic date string convertion to number. Since it creates machine-dependent results it’s a problem maker.

8

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, it is not possible because to convert dates correctly, Aspose.Cells relies on the System settings. There must be some way to differentiate between day and month to process dates and for that purpose Aspose.Cells have to use short date format of the System.

Microsoft Excel also behaves the same way. Suppose you create a csv file having a date like this

02-03-2015

Now change your System short date format to m/d/yyyy and open your csv file in MS-Excel, it will read the date as February 03, 2015.

Now change the date format to d/m/yyyy and open your csv file again in MS-Excel, it will read the date as March 02, 2015.

So Aspose.Cells and Microsoft Excel both depends on System settings to interpret dates.

9

@shakeel.faiz @Amjad_Sahi Is there still no option for correcting this.

In my excel I have multiple kind of values like string and dates, but when fetching the data into datatable the value for date “4/20/2022” becomes “44671”.

Can you please let me know if there is a fix for this after so many updates to Aspose dll.

10

@kchaniyal,

Please note, DateTime values are stored as numeric notations in MS Excel file formats. You need to specify your desired DateTime formatting for the specified column/row or cells in your code for it, see following code segment for your reference:
e.g.
Sample code:

DataTable table = new DataTable();
            table.Columns.Add("ID");
            table.Columns.Add("Test");
            table.Columns.Add("Start Date");
            string[] addrow = {"1","abc", DateTime.Now.ToString()};
            table.Rows.Add(addrow);

            var workbook = new Workbook();
            var ws = workbook.Worksheets[0];
            ws.Cells.ImportData(table, 0, 0, new ImportTableOptions
            {
                IsFieldNameShown = true,
                ConvertNumericData = true,
                 NumberFormats = new string[] {null, null, "yyyy-mm-dd"},
               
            });
            ws.AutoFitColumns();
            workbook.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.