Import to Worksheet not always applying dateformat

Aspose.Cells Product Family
1

Hi There,

I have a routine which imports a custom type to an excel worksheet. For one of my custom types, the dateformat I am after, in this case 'yyyy-mm-dd' is being applied successfully. For another it's not and I can't figure out why.

The actual value for the dates is being imported to the worksheet as I'd expect '40299' for example, but the formatting isn't being applied, it stays as 'General'.

I've even tried explicitly setting the style for the column in question, but this doesn't seem to solve the problem either!

Can anyone point me to anything which may impact the style of my workbooks date columns? At the moment my code is pretty straight forward, see below:

mySheet.Cells.ImportCustomObjects(
(System.Collections.ICollection)myDataList,
GetPropertiesList(typeof(myType)),
true,
0,
0,
myDataList.Count,
true,
"yyyy-mm-dd",
false
);

Just for reference, the 'GetPropertiesList' function simply returns a string array for the properties in the type I'm exporting, and myDataList is simply a List<> of that type.

I can confirm that in the List<> the property in question is being rendered as a datetime.

Any ideas?

Cheers,

Doug

2

Hi,

Thanks for your interest in Aspose.Cells and posting.

Please create a sample project and attach here. Please use some hard coded sample values and post only the part of code where you are having a problem. We will investigate your issue.

3

Hi,

I'm afraid that due to the types which are being exported coming from the entity framework that it would be very hard for me to send a project which you could compile and run without including a chunk of our DB.

I will endevour to hard code a demo type and some demo objects into a single page to demonstrate the issue.

What I'm a bit confused about is why the ImportCustomObjects works for one of my types, but not for the other.

Please stand by for an example which can be used,
Cheers,

Doug

4

Hi,

Thanks for your help.

It could be a bug or it could be some logical error. We can’t say anything at the moment until we investigate it.

5

Hi Again,

I've managed to replicate the issue, please find attached a demo page with code sufficient to reproduce the issue.

Note that the 'Date1' property does not return the custom formatting. Also note that it's set as a nullable field.

As a result I think that the ImportCustomObjects function does not apply the formatting to nullable date fields. This is a problem for me as I'd like to use this formatting.

Would this be classified as a bug? or can I do something code side to allow nullable properties to use the correct formatting?

I'm afraid changing the type to 'nullable = false' is not possible in my example, as the logic of the application requires these dates to be optional.

Cheers,

Doug

6

Just thought I'd chime in here. I've been having the same issue in multiple applications, but hadn't figured out it had to do with nullable datetimes until you mentioned it. I would love to have a better fix for this. What I've been doing is create a date specific cell style and then applying it to all my date columns. This can get to be incredibly annoying though, depending on the number of columns you have in your worksheet.

Any suggestions from the Aspose team?

7

Hi,

Thanks for the sample project with details.

I noticed the issue regarding the first Date field. I have logged a ticket for it with an id: CELLSNET-28190. We will check if we can enhance it (importing collection/objects feature) accordingly.
Once we have any update, we will let you know about it.

Thank you.

8

Hi Both,

Thanks for the feedback, @Amjad - glad to hear this is a candidate for an enhancement, could you let me know where I can monitor/track the progress of this issue specifically? or should I just monitor this thread?

@spgoad, glad to see my example has identified the cause of this problem, hopefully the Aspose team can provide nullable date formatting in a future release/hotfix, but I guess until then, I will be forced to use the same mechanism as yourself and manually format the date columns. This as you say can be a bit of a pain, as if someone alters the type and re-arranged the columns I guess this would fall over :(

Thanks again for the info/feedback,

Doug

9

Hi Doug,


You need to monitor this thread only. Any update regarding your specified issue will be posted to this thread.

Thank you
10

Hi Babar, thanks for the quick response.

I will give this updated file a go and let you know the outcome.

On the topic of Datetime.MinValue, that was purely an example, our db will be a datetime that excel can support, or null, so hopefully that won't crop up.

EDIT: That link doesn't seem to work, I will see if I can get a copy from the main download page. Assuming it's the latest release I guess this will be ok.

EDIT:EDIT: I see the latest version in the downloads section is 5.3.3. Does this include the nullable type fix? if not I'd rather avoid the update overhead and wait for 5.3.4 if that's ok.

Cheers,

Doug

11

Hi,

Please download Aspose.Cells for .NET 5.3.3.4.

We have supported nullable. But the min date time in MS Excel is since 1900/1/1. So DateTime.MinValue is not valid for Excel, we have to put the string value to excel file.

12

Hi Mshakeel,

I've downloaded the 5.3.3.4 and updated the references in the project to use this dll (at the same time as removing the old dll) and I still see the same issue.

EDIT: I had left a reference in there to the older 5.2.3 version, have now updated this and all is working as intended, thanks for all the help,

Cheers,

Doug

13

Hi Doug,

That’s great, you found the source of trouble and your issue is now resolved.

14

The issues you have found earlier (filed as 28190) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.