DateTime is of type IsNumerical instead of type IsDateTime

Aspose.Cells Product Family
1

Hello,

I'm having a problem with Aspose.Cells 4.4.3.1. When I try the following, I expect the Cell to be of the type IsDateTime, but instead I'm getting IsNumeric.

Here's the code I use:

DateTime sDateTime;
sDateTime = Convert.ToDateTime(sText);
_Cells[ACellRow, ACellColumn].PutValue(sDateTime);

if (_Cells[ACellRow, ACellColumn].Type == CellValueType.IsDateTime)
{
I NEVER GET HERE
}
else if (_Cells[ACellRow, ACellColumn].Type == CellValueType.IsNumeric)
{
I ALWAYS GET HERE
}

I've upgraded my Aspose.Cells to 4.4.3.1, but this problem still persists (I was using 4.4.1.0 before this). Is there something I'm overlooking ?

Thanks in advance,

Alex

2

Hi,

Well, dates are stored as numeric values in MS Excel worksheet. I think you may try to add a line to your code, it will work fine:

e.g..,

DateTime sDateTime;
sDateTime = Convert.ToDateTime(sText);
_Cells[ACellRow, ACellColumn].PutValue(sDateTime);

_Cells[ACellRow, ACellColumn].Style.Custom = "m/d/yyyy";

if (_Cells[ACellRow, ACellColumn].Type == CellValueType.IsDateTime)
{
I NEVER GET HERE
}
else if (_Cells[ACellRow, ACellColumn].Type == CellValueType.IsNumeric)
{
I ALWAYS GET HERE
}

Thank you.

3

Hello,

I knew about the dates being stored as numeric values in MS Excel worksheet. The method I tried was the one that was described in the documentation your site provides, which said that when you stuff a DateTime in PutValue it'll be read as an IsDateTime.

Your solution seemed to solve the problem for me though, thank you for that. I did try to format the DateTime string before I inserted it into the cell, but for some reason didn't think of formatting the Cell-style itself.

Thanks again,

A.

4

I am having roughly the same problem except I need to validate that a cell data type is a datetime on an excel file that I have loaded and the Custom field is empty and the type = IsNumeric.

5

Hi,

Could you post your excel file and paste your sample code here, we will check it soon.

Thank you.

6

Hello,

I have ran in the exact same situation. The provided solution may work, but I still think it is very strange that when a DateTime is passed to .PutValue the cells type is not IsDateTime, why is that enum there then anyway? At the moment the value is set Aspose.Cells does not know whether I'm going to save it as an Excel sheet or CSV or something else. Besides that Excel has it's own default date format based on the users regional settings, I would rather have that date format to be used, than the one I'm putting in the .Style.Custom property.

I hope a future version will handle this more transparently. Is there somewhere I can log an enhancement/irragularity?

Kind regards,

Jorre Meijrink

7

In another thread a post of mine (created by the support staff after an e-mail). The advice was to use Style.Number, if this is set to 14 (fits more to the situation I'm in than the 15 suggested) the date is formatted according to users regional settings. The cell.Type is then also set to IsDateTime.

Would it be wise to apply this automaticaly for DateTime's? I think that would prevent anymore questions about this.

Kind regards,

Jorre Meijrink

8

Hi,

Thank you for your feedback.

We have looked into it but I am afraid we are not going to support the auto-formatting as per your suggestion due to performance issue. So you have to set the style manually.

Sorry for any inconvenience,