Excel Date Formatting issue

[Siva72]

Hi Team
I have a sheet populated through Excel where the Date 12-December is custom formatted as 12-Dec.
The same thing when i formatted using aspose.cells it is not formatted to 12-Dec.

Below is the logic used for formatting.
Range range = cells.CreateRange(“B” + rowno);
range.Value = mydr[“period”];
Style st23 = xlwkbk.CreateStyle();
st23.SetCustom(“d-mmm”,true);
StyleFlag flag23 = new StyleFlag();
flag23.All = true;
range.ApplyStyle(st23, flag23);
xlclose();

Attached the sheets populated via Excel and Aspose.
Please refer to first sheet - B7:B16
Common.zip (57.0 KB)

Thanks
Siva Poreddy

[Amjad_Sahi]

@Siva72,

Thanks for the sample files.

I evaluated your issue using your sample files and I found the DateTime values (into those cells) are inserted as text/string instead of DateTime/numeric data type. That’s why the custom formatting is not applied to those cells in the range (B7:B16). When you double-click in the cell(e.g., B7) and press enter, MS Excel will do the conversion and thus the formatting is applied properly. Either you need to insert DateTime values into those cells for the worksheet by yourselves or you may do the conversion via Cell.PutValue(stringValue, isConverted) overload in Aspose.Cells APIs (you need to re-insert the values into the specified range of cells). Setting isConverted parameter to true would ensure to convert to proper data type. See the following sample code segment that you may try to add before saving to Excel file to accomplish the task for your reference:
e.g.
Sample code:

.......
Range range = cells.CreateRange("B7:B16" );
IEnumerator ie = range.GetEnumerator();
while (ie.MoveNext())
{
    Cell cell = (Cell)ie.Current;
    cell.PutValue(cell.StringValue, true);
}
......

Hope, this helps a bit.