Datetime column displayed as numeric?

Aspose.Cells Product Family
1

I’m creating a new workbook and importing a datatable that has datetime columns in it. The dates are showing in excel as “42090.4233588773”. Do I have to manually set the style for them to be formatted as dates instead of numeric?

2

Hi,

Thanks for your posting and using Aspose.Cells.

Actually, dates are stored as numbers inside the excel file, so you will have to set the style of those cells so that those dates could show properly.

Please see the following sample code. It adds current date in the cell A1 and formats it with built-in (14) date style.

I have attached the output excel file generated by it for your reference.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


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

cell.PutValue(DateTime.Now);


//Set the style to Date

Style style = cell.GetStyle();

style.Number = 14;

cell.SetStyle(style);


workbook.Save(“output.xlsx”);



3

Thanks, is there any way to set default styles for things?

4

Hi,

Thanks for your posting and using Aspose.Cells.

Yes, you can also set the default style of the entire workbook using the Workbook.DefaultStyle property.

Please see the following sample code and its output excel file attached with this post for your reference.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


worksheet.Cells[“A1”].PutValue(DateTime.Now);


worksheet.Cells[“A2”].PutValue(DateTime.Now);


worksheet.Cells[“A3”].PutValue(DateTime.Now);


//Set the default style to Date

Style style = workbook.CreateStyle();

style.Number = 14;

workbook.DefaultStyle = style;


workbook.Save(“output.xlsx”);


5

Thanks,
Can you please explain why this doesn’t work:

Style dateStyle = new Style();

style.Number = 14;
var range = worksheet.Cells.CreateRange(0, colIndex, dataSet.Tables[i].Rows.Count+1, 1);

range.SetStyle(dateStyle);
But this does:
var range = worksheet.Cells.CreateRange(0, colIndex, dataSet.Tables[i].Rows.Count+1, 1);

var cellStyle = worksheet.Cells[1, colIndex].GetStyle();

cellStyle.Number = 14;

range.SetStyle(cellStyle);

6

Nevermind.

7

Hi,

Thanks for your posting and using Aspose.Cells.

Whenever you need to create a new style, please use Workbook.CreateStyle() method instead of the Style() constructor. It will work without any issue.