Hello,
I am assinging DateTime object to a cell using cell.PutValue method.
When I open up the excel sheet in the MS Excel, I don’t see date string,
instead I see a big Float number.
This is what happening.
I have date value of “6/30/2005”. I have constructed DateTime object directly from the Oracle data base using OracleDataReader.GetDateTime method. I am putting this value into a cell using PutValue method. There is no error when calling this function.
When I open the excel sheet, I see date value as “38533.4753587963”.
If I change the format of cell to Date, then the real date “6/30/2005” appears.
Why it is happening like this, why Excel is not recognizing this cell value as Date/Time type?
Thanks,
Raj
Hi Laurence,
Thanks for you quick reply.
Now I am facing different kind of problem.
I am assiging style to a range of cells.(Range Object). This style
object basically sets the Font type, size, Cell color and text alignment.
To one/more of the cells in the above range(which has style set), DateTime
value is put. I am assigning the style type of these cells to date format, as
specified in the link you gave me. i.e Cell.Style.Number = 14; I am using
this style only for the cell to which I am putting DateTime object.
Now the problem is, when I open the sheet in Excel, all cells which are numbers
in the cell range are displayed as Date. I am wondering what should I do, to just
dispaly the cell of DateTime as Date format. Do I need to create a new style and
assign to these cell??
Thanks,
Raj
Hi,
After using a different style object for the Cell, now it is working.
Thanks,
Raj
@raju_krishnappa,
Aspose.Cells has replaced Aspose.Excel that is no more available now. This product contains all the advanced features available in different versions of MS Excel. You can format the data for date values as shown in the following sample code.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Adding the current system date to "A1" cell
worksheet.Cells["A1"].PutValue(DateTime.Now);
// Getting the Style of the A1 Cell
Style style = worksheet.Cells["A1"].GetStyle();
// Setting the display format to number 14 to show date as "m/d/yy"
style.Number = 14;
// Applying the style to the A1 cell
worksheet.Cells["A1"].SetStyle(style);
// Adding the current system date to "A2" cell
worksheet.Cells["A2"].PutValue(DateTime.Now);
// Getting the Style of the A2 Cell
style = worksheet.Cells["A2"].GetStyle();
// Setting the display format to number 15 to show date as "d-mmm-yy"
style.Number = 15;
// Applying the style to the A1 cell
worksheet.Cells["A2"].SetStyle(style);
// Adding the current system date to "A3" cell
worksheet.Cells["A3"].PutValue(DateTime.Now);
// Getting the Style of the A3 Cell
style = worksheet.Cells["A3"].GetStyle();
// Setting the display format to number 16 to show date as "d-mmm"
style.Number = 16;
// Applying the style to the A1 cell
worksheet.Cells["A3"].SetStyle(style);
// Adding the current system date to "A4" cell
worksheet.Cells["A4"].PutValue(DateTime.Now);
// Getting the Style of the A4 Cell
style = worksheet.Cells["A4"].GetStyle();
// Setting the display format to number 16 to show date as "mmm-yy"
style.Number = 17;
// Applying the style to the A1 cell
worksheet.Cells["A4"].SetStyle(style);
// Saving the Excel file
workbook.Save("book1.xls", SaveFormat.Excel97To2003);
You may follow the link below to get more information about the charts.
Built in number formats
Download the free trial version of this new product here:
Aspose.Cells for .NET (Latest Version)
A comprehensive runnable solution can be downloaded here to test the features of this new product.