Custom date formatting

Hi all.

Im having some problems with formatting the date for a column using Aspose.Cells 4.2.0.0 for .NET 2.0. Im creating a excel document from a database with a date column in each row, but i cant get the date right. The date in the database looks like this “2007-04-05 14:17:54.000” but i want it to look like this “2007-04-05 14:17:54”, no matter how i try to custom format it i cant get it right. Can anyone help me getting it right?

Thanks in advance

Hi,

Thanks for considering Aspose.

Since your underlying date is not common, so you have to convert it to datetime format before you apply your custom formattings.

Here is a ref. code which works fine:

Workbook workbook = new Workbook();

Cells cells = workbook.Worksheets[0].Cells;

cells["B2"].PutValue(Convert.ToDateTime("2007-04-05 14:17:54.000"));

cells["B2"].Style.Custom = "yyyy-mm-dd hh:mm:ss";

cells.SetColumnWidth(1,20);

workbook.Save("d:\\test\\dtformat.xls");

Thank you.

Hello and thanks for your answer.

The problem is that im importing the data from a datatable instead of using putvalue.
I tried using your example in this way and it results in the datecolumn being filled with hashes instead of numbers.

Workbook wbNOK = new Workbook();
wbBook.Worksheets.Add();
Worksheet wsSheet = wbBook.Worksheets[0];
wsSheet.Name = “blabla”;
wsSheet.Cells.Columns[0].Style.Custom = “yyyy-mm-dd hh:mm:ss”;
wsSheet.Cells.ImportDataTable(dtCooler, true, “A1”);

The type of that column in the datatable is already DATETIME so it shouldnt need any converting.
If i dont set any type of formatting at all the column just gets a numeric value, like 36740 or likewise.

Hi,

Please set the width of the date column to expand it to view its contents properly and completely. E.g,

//Set the width of the Ist column (A).

Cells.SetColumnWidth(0,30);

Or you may try Worksheet.AutoFitColumns() / AutoFitColumn() method for your need.

For your info, when data is exported to the worksheet, all the date values get converted to numeric values.

Workbook wbNOK = new Workbook();
wbBook.Worksheets.Add();
Worksheet wsSheet = wbBook.Worksheets[0];
wsSheet.Name = "blabla";
wsSheet.Cells.Columns[0].Style.Custom = "yyyy-mm-dd hh:mm:ss";

wsSheet.Cells.ImportDataTable(dtCooler, true, "A1");

wsSheet.Cells.SetColumnWidth(0,30);

Thank you.

It worked perfectly, thank you very much!

Hi,
I have to export data to an excel. The second column should be in date format but i am not getting it in date format.I am getting “39797.39389” instead. This is the code that i used.

Dim styleFlag As New StyleFlag()
Dim style As Style = workbook.Styles(workbook.Styles.Add())
style.Number = 14
worksheet.Cells.ApplyColumnStyle(1, style, styleFlag)

Hi shu,

Well, MS Excel, by default, takes the date values as numeric values, so, when data is exported to the worksheet, all the date values get converted to numeric values. So, you have to format these values (in the column) to date formats.

Thank you.

Hi,

And since your code has a missing line, please correct it as follows:

Dim styleFlag As New StyleFlag()
Dim style As Style = workbook.Styles(workbook.Styles.Add())
style.Number = 14

styleFlag.NumberFormat = True
worksheet.Cells.ApplyColumnStyle(1, style, styleFlag)

Thank you.