I need to set a Date format for a set of Range. Below is the sample code which I used. Once I execute the below code and when I open the excel file I can see the range is changed to custom format but the cell value is not taking effect.
Actual : Cell value is “07/26/2021” and in general format.
Expected : cell value should be “26/07/2021” and in custom date format.
Workbook workbook = new Workbook(“sample.xlsx”);
Worksheet WS = workbook.getWorksheets().get(0);
Cells cells = WS.getCells();
Range range = cells.createRange(“D1:D6”);
Style style = workbook.createStyle();
style.setCustom(“dd/mm/yyyy”);
range.setStyle(style);
style.update();
workbook.calculateFormula(true);
workbook.save(“sample.xlsx”);
Kindly help to resolve.
@JavithMeyandad,
Please note, since the cell values are in General format, so they won’t be formatted to DateTime formatting although the custom DateTime formatting are applied (Open the output file into MS Excel, right-click on any cell to open the Format Cells… dialog box, now check the Number tab in the dialog and you will see the custom formatting is applied). To get it work, the values should be converted to proper (DateTime/Number) type. This is same with MS Excel. But when you click “Enter” in the cell or click Ok button of the dialog, the values are converted automatically to proper type and the custom DateTime formatting would be applied properly. For Aspose.Cells, you have to convert the values in D1:D6 cells range to relevant data type(s) to get the custom formatting applied properly. See the following sample code especially the lines in bold for your reference. I have tested with a sample Excel file and it works fine.
e.g.
Sample code:
Workbook workbook = new Workbook(“Sample.xlsx”);
Worksheet WS = workbook.getWorksheets().get(0);
Cells cells = WS.getCells();
Range range = cells.createRange(“D1:D6”);
//Iterate through cells in the range and convert to DateTime/number type
Iterator rangeIterator = range.iterator();
while(rangeIterator.hasNext())
{
Cell cell = (Cell)rangeIterator.next();
cell.putValue(cell.getStringValue(), true);
}
Style style = workbook.createStyle();
style.setCustom(“dd/mm/yyyy”);
range.setStyle(style);
style.update();
workbook.calculateFormula(true);
workbook.save(“sample.xlsx”);
Hope, this helps a bit.