Free Support Forum - aspose.com

Style.setcustom does not display the correct date for 'mmm-yy'

Hello Aspose community,

I am using the style.setcustom method to set the date format mmm-yy and insert a date in that format e.g Oct-14. However it prints Oct-13 on excelsheet instead of Oct-14. It changes the year to current year not sure why.

Here is the sample code:

package test;

import com.aspose.cells.Cell;

import com.aspose.cells.Cells;

import com.aspose.cells.Style;

import com.aspose.cells.Workbook;

import com.aspose.cells.Worksheet;

public class datetest {

public static void main(String[] args)

{

Workbook workbook;

try {

workbook = new Workbook("C:\\Test.xlsx");

Worksheet worksheet = workbook.getWorksheets().get(1);

Cell cell = worksheet.getCells().get("C3");

Style style = cell.getStyle();

style.setCustom("mmm-yy");

cell.setStyle(style);

cell.putValue("Oct-14",false);

Cells cells = worksheet.getCells();

cells.convertStringToNumericValue();

System.out.println("cell.getValue" + cell.getValue());

workbook.save("C:\\Test.xlsx");

} catch (Exception e2) {

e2.printStackTrace();

}

}

}

I have used cells.convertStringToNumericValue(); to convert all other numeric values in sheet to number format since I cannot do it at individual cell level.

Thanks

Neha

Hi,

Thanks for your posting and using Aspose.Cells for Java.

After initial investigation, we were able to notice this issue. We will look into your issue more and provide you a fix or advice.

We have logged this issue in our database. Once, the issue is resolved or we have some other update for you, we will share it with you asap.

This issue has been logged as CELLSJAVA-40433.

Hi,

When parsing a string value to a datetime value, the number
format(Style.Custom) is useless.
So we simply process “Oct-14” as “mmm-dd”.
It works as MS Excel.

If you open the generated file in MS Excel, select
A1, and input “Enter”," Oct-13" displays.

Java


public static void main(String[] args)

{

Workbook workbook;

try {

workbook = new Workbook(“C:\Test.xlsx”);

Worksheet worksheet = workbook.getWorksheets().get(1);

Cell cell = worksheet.getCells().get(“C3”);

Style style = cell.getStyle();

style.setCustom(“mmm-yy”);

cell.setStyle(style);

cell.putValue(“Oct-14”,false);

Cells cells = worksheet.getCells();

//cells.convertStringToNumericValue();

//System.out.println(“cell.getValue” + cell.getValue());

workbook.save(“C:\Test.xlsx”);

} catch (Exception e2) {


e2.printStackTrace();

}



}



Ok, then how do you specify a custom date value and format in Aspose? We need to be able to represent a value as a date (not just text) in the format mmm-yy.

Hi,

Thanks for your feedback.

We have added your comments in our database. Please spare us some time. We will look into your issue more and help you asap.

any update?

Hi,

Please directly input the date time value not a string value.

Please see
the following code:

Java


Workbook workbook = new Workbook();

Worksheet worksheet = workbook.getWorksheets().get(0);

Cell cell = worksheet.getCells().get(“C3”);

Style style = cell.getStyle();

style.setCustom(“mmm-yy”);

cell.setStyle(style);

SimpleDateFormat dateFormat = new SimpleDateFormat(“MMM-yy”);

java.util.Date date = dateFormat.parse(“Oct-14”);

cell.putValue(date);

Hi,

To get your expected datetime value and formatting, please parse the datetime value in text format by yourself and then set the parsed datetime value to cells. Code example:
...
SimpleDateFormat df = new SimpleDateFormat("MMM-yy");
cell.putValue(df.parse("Oct-14"));
style.setCustom("yy-mmm");
cell.setStyle(style);
Without specified formatting of text value, aspose.cells can only try to guess it. It is a time-consumed process and maybe give different result than what you expect, such as take the two digits year value as month or take the month value as two digits year, just like ms excel does.