Date format problem

Aspose.Cells Product Family
1

I’m using Aspose.Cells v.4.5.0.0 (trial) for .NET 2.0

OS: Windows XP (Polish version), MS Office XP (Polish version)

I export data which contains Dates. And I’ve problem with formatting them.

Used code:
//sRes is my value, DataType isn’t known earlier

Aspose.Cells.Cell wcItem = ws.Cells[j, i]
DateTime dtTmp=DateTime.MinValue;
if (DateTime.TryParse(sRes, out dtTmp))
{
wcItem.PutValue(dtTmp); //if it is DateTime it uses this code for sure

Aspose.Cells.Style style= wb.Styles[wb.Styles.Add()];

style.Custom = “dd/MM/yyyy;@”;

Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();

flag.NumberFormat = true;

ws.Cells.ApplyColumnStyle(i, style, flag);
}
else
{
wcItem.PutValue(sRes);
}



I’ve also tried writing like:
wcItem.Style.Custom=“dd/mm/yyyy”;
or

wcItem.Style.Number = 15;

In every case generated Excel file doesn’t format datetime properly (like in attachment test.zip).

What can I do to have DateTime data exported and formatted well?
2

Hi,

I think you may try to use Style.Number property to set it to 15 value. If you use built-in number format, it will be changed accordingly with the languange on the machine. But if you use custom number format , it will always be the same as your regional settings (so you should change the regional settings if you try custom formats).

Could you try to change your code to:

Aspose.Cells.Cell wcItem = ws.Cells[j, i]
DateTime dtTmp=DateTime.MinValue;
if (DateTime.TryParse(sRes, out dtTmp))
{
wcItem.PutValue(dtTmp); //if it is DateTime it uses this code for sure

Aspose.Cells.Style style= wb.Styles[wb.Styles.Add()];
style.Number = 15;
Aspose.Cells.StyleFlag flag = new Aspose.Cells.StyleFlag();
flag.NumberFormat = true;
ws.Cells.ApplyColumnStyle(i, style, flag);

}
else
{
wcItem.PutValue(sRes);
}

Thank you.

3

Thanks for your reply.
But it still doesn’t solves the problem.
Dates are still displayed in “string format” - 2008-08-29 00:00:00. And when I look into formatting options, it’s not a date but non-standard formatting.
I’ve also noticed, that pressing F2 on date, and adding, for example a space and pressing Enter, causes Excel to display the date correctly. But when I try to sort such column, it sorts dates made by adding space correctly, but “dates” not touched are sorted like string (example in attachment).

Have you got any solution for this?

4

Hi,

Thanks for providing us further details with template excel file.

We will look into your issue soon.

Thank you.

5

Hi,

After checking the file, we find the cell value type is string. So please use Cell.PutValue(stringValue,true).Such as :

string cellValue = workbook.Worksheets[0].Cells["A5"].StringValue;

workbook.Worksheets[0].Cells["A5"].PutValue(cellValue, true);

6

Hi,
Great, this helped. Now, date is displayed correctly.

But I’m sure it was sent as a DateTime not as a String earlier, I had tested it even with PutValue(DateTime.Now).

Thanks for your help.

7

Hi,

We tried the following codes , it works fine.

Workbook workbook = new Workbook();
workbook.Worksheets[0].Cells["A1"].PutValue(DateTime.Now);
workbook.Worksheets[0].Cells["A1"].Style.Custom = "dd/mm/ yyyy";
workbook.Save(@"F:\FileTemp\dest.xls");

So i think it's caused by DateTime.TryParse() could not parse your string value correctly.