Unable to convert data to date format

Aspose.Cells Product Family
1

Hello,


I copy a date cell value (7/2/2015) to another cell in the spreadsheet using PutValue and the data in the destination cell shows as 42187. I believe is the number of days since Jan 1900. Usually with this issue in excel, I would just simply set the new cell format to datetime to fix it. However, I cannot do this with aspose. Please help.

How I copy cell value/data:

Cell sourceCell = worksheet.Cells[“A1”];
Cell destCell = worksheet.Cells[“B1”];
if (sourceCell != null && destCell != null)
{
destCell.PutValue(sourceCell.Value);
}

How I attempt to set different date format. “yyyy-mm-dd” is my ultimate goal, but I also tried built-in format “m/d/yy” and it still doesn’t work. Note to shorten the code, I omit how I setup “findOptions”.
Cell cell = worksheet.Cells.Find(“TestDate”, null, findOptions);
Style style = workbook.Styles[workbook.Styles.Add()];
StyleFlag flag = new StyleFlag();
flag.NumberFormat = false;
style.Custom = “yyyy-mm-dd”;
worksheet.Cells.ApplyColumnStyle(cell.Column, style, flag);
2

Hi,

Thanks for your posting and using Aspose.Cells.

You need to set the StyleFlag.NumberFormat property true. Please change your following code

Cell cell = worksheet.Cells.Find(“TestDate”, null, findOptions);
Style style = workbook.Styles[workbook.Styles.Add()];
StyleFlag flag = new StyleFlag();
flag.NumberFormat = false;
style.Custom = “yyyy-mm-dd”;
worksheet.Cells.ApplyColumnStyle(cell.Column, style, flag);

into

Cell cell = worksheet.Cells.Find(“TestDate”, null, findOptions);
Style style = workbook.Styles[workbook.Styles.Add()];
StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
style.Custom = “yyyy-mm-dd”;
worksheet.Cells.ApplyColumnStyle(cell.Column, style, flag);

Changes are highlighted in red color.

I have tested this issue with the following sample code with the latest version: Aspose.Cells for .NET 8.5.1 and it generated the correct results. I have attached the output excel file generated by the code for your reference.

C#

Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Style style = workbook.CreateStyle();

style.Custom = “yyyy/mm/dd”;


StyleFlag flag = new StyleFlag();

flag.NumberFormat = true; //set NumberFormat true


worksheet.Cells.ApplyColumnStyle(0, style, flag);


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

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

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

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

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


worksheet.AutoFitColumns();


workbook.Save(“output.xlsx”);

3

Hi Shakeel,

Thank you for your help! It works after I made the change you suggested. Now I just want to understand the StyleFlag a little for future implementation.

I originally set the StyleFlag.NumberFormat to false because I thought date datatype isn’t a “number”. I usually set the StyleFlag to true only if it’s an integer or decimal.

To help me better work with aspose object in the future, can you shed some lights on this?

Thanks!

4

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your original issue is resolved. Actually, date formats or other formats like currency, numbers etc are set using Style.Number and Style.Custom properties. Since it deals with Style.Number property, therefore it is named as StyleFlag.NumberFormat.

If you have any other question, please feel free to ask, we will look into it and help you asap.