Date formating

Hello Team,

Iam using “aspose 7.7.0”(Java) to create XLS from CSV. In that, i have a column date with the format of (dd/MM/yyyy). Ex: 05/09/2014

I want to convert it to the format “d-mmm-yy. i used the below code to convert the column.

style.setNumber(15); or style.setCustom(“d-mmm-yy”);

I expect the result should be 5 sep 2014. but the result is 9 may 2014. Since it takes the input format as mm/dd/yyyy.

How can i overcome this problem.

Thanks and regards,
Farhana Y.

Hi Farhana,


I have tested your scenario/ case with the following sample code using our latest version/fix: Aspose.Cells for Java v8.2.0.2 ,it works fine.
I have also attached the input and output Excel files for your reference.
e.g
Sample code:

Workbook wb = new Workbook(“Dt_Book1.xlsx”);
Cells cells = wb.getWorksheets().get(0).getCells();
Cell cell = cells.get(“A1”);
Style style = cell.getStyle();
style.setCustom(“dd-mmm-yy”);
cell.setStyle(style);

wb.save(“outDt_Book1.xlsx”);

Please try our latest version/fix v8.2.0.2 and let us know your feedback.

Thank you.

Hello Amjad,

Thank you for your reply.

In your code, The input file is an xls file. In that file you gave 05/09/2014 as dd/mm/yy format. So when parsing that with the style.setCustom(“dd-mmm-yyyy”) method is not a problem.
Since it knows that the format of the column as dd/mm/yyyy.

In my case i have a CSV file with a raw data. Which has the date in “dd/mm/yyyy” display format.
[Check the attachment sample_CSV.csv]

Conversion code:

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setSeparator(’,’);
opts.setEncoding(Encoding.getDefault());
opts.setConvertDateTimeData(true);
Workbook newWB = new Workbook(stream, opts);
Worksheet newSheet = newWB.getWorksheets().get(0);
Cells tmpCells = newSheet.getCells();
Column column = tmpCells.getColumns().get(0);
Style style = resultBook.createStyle();
style.setNumber(15);
StyleFlag styleFlag = new StyleFlag();
styleFlag.setNumberFormat(true);

when i do like this, It sets the date column as “mm/dd/yyyy” format by default. while reformatting it with the style class gives me the result as 9 may 2014. The problem is not with the setNumber or setCustom methods. It is in the conversion part of CSV to XLS.
[Check the attachment setConvertDateTimeData_true.xls]

While executing the code for conversion alone

Workbook newWB = new Workbook(stream, opts);
Worksheet newSheet = newWB.getWorksheets().get(0);
Cells tmpCells = newSheet.getCells();
Column column = tmpCells.getColumns().get(0);

gives me the result as “mm/dd/yyyy” instead of “dd/mm/yyyy”.
[Check the attachment output_XLS_withoutstyle.xls- see the format cells]

So i gave opts.setConvertDateTimeData(false);( It makes date column as string). then converted it with the style.setCustom(“d-mmm-yy”) method. The resultant XLS displays the date
as “mm/dd/yyyy” format. But the format cells option shows that as a date of “d-mmm-yy” format. When i click the cell the input box which displays the value has an apostrophe(’) before the date. when i erase that apostrophe the display format of the date automatically changes to d-mmm-yy.
[Check the attachment setConvertDateTimeData_false.xls]

Hope this clarifies my requirement.

Thanks and Regards
Farhana Y







Hi,


Thanks for the template files, sample code and details.

After an initial test, I observed the issue as you mentioned by using the following sample code with your template file while converting the CSV file to XLS file format. The applied DateTime formatting to the cells in the first column is not up to the mark in the generated Excel file. It might be an issue with locale or regional settings which is by default applied to US when saving the files, we need to look into it.
e.g
Sample code:

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setSeparator(’,’);
opts.setEncoding(Encoding.getDefault());
opts.setConvertDateTimeData(false);
Workbook newWB = new Workbook(“sample_csv.csv”, opts);
Worksheet newSheet = newWB.getWorksheets().get(0);
Cells tmpCells = newSheet.getCells();
Column column = tmpCells.getColumns().get(0);
Style style = newWB.getStyles().get(newWB.getStyles().add());
style.setNumber(15);
StyleFlag styleFlag = new StyleFlag();
styleFlag.setNumberFormat(true);

column.applyStyle(style, styleFlag);

newWB.save(“out1.xls”, SaveFormat.EXCEL_97_TO_2003);

I have logged a ticket with an id “CELLSJAVA-40982” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

Hi,

Thanks for using Aspose.Cells

It is not issue of Aspose.Cells. When time fields of the default locale of user’s
environment is month/day/year, then for the time value “05/09/2014”, 5 will be
taken as month and 9 be taken as day.

You can specify another region(locale)
whose order is day/month/year, such as below code:

Java
TxtLoadOptions
loadOptions = new TxtLoadOptions();

loadOptions.setRegion(CountryCode.UNITED_KINGDOM);
wb = new
Workbook(f, loadOptions);

Hi,


And, please see the complete sample code using your template file for your reference, I have tested it works fine.
e.g
Sample code:

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setSeparator(’,’);
opts.setRegion(CountryCode.UNITED_KINGDOM);
opts.setEncoding(Encoding.getDefault());

Workbook newWB = new Workbook(“sample_csv.csv”, opts);
Worksheet newSheet = newWB.getWorksheets().get(0);
Cells tmpCells = newSheet.getCells();
Column column = tmpCells.getColumns().get(0);
Style style = newWB.getStyles().get(newWB.getStyles().add());
style.setNumber(15);
StyleFlag styleFlag = new StyleFlag();
styleFlag.setNumberFormat(true);

column.applyStyle(style, styleFlag);


newWB.save(“out1.xls”, SaveFormat.EXCEL_97_TO_2003);



Thank you.

Hello,

Thank you for your solution,

I have following queries regarding that,

1.Do this region code will affect all the columns in the workbook? I want to apply some other country format to another column in same workbook.
[Example: date should be UNITED_KINGDOM and the currency should be UNITED_STATES]
Is it possible to do that in column wise.

2.Is it possible to set the formats for each and every column before i parse the CSV to EXCEL. because applying the style to the column (using appleStyle()) after the conversion of CSV to EXCEL is not working properly.

[Example: I have a telephone number column which is more than 12 digits in the CSV, after converting this to excel it shows the value in scientific notation.

applying the text style after parsing it with the code style.setNumber(49); is not giving the expected result. It shows the value as ###]

My requirement is, I have a csv and i know the datatype and format should be applied to each and every column of that. how can i set the formats and datatype in the excel.

Thanks and regards
Farhana Y



Hi,


Well, Aspose.Cells follows MS Excel standards.

1) How could you do this in Ms Excel manually? When you specify the region, it would apply to whole workbook, how could you specify two regions in the same sheet or workbook? If you could accomplish the task in MS Excel, give us your template file (that you may manually create in MS Excel) and steps involved to do it, we will check it soon.

2) Well, again this is MS Excel behavior and you have to accept it. When a number has 12 or more digits, Ms Excel would render that number in scientific notations. I think if you are not comfortable with applying text formatting to those cells, then you may try to set the custom number formatting for the Style object, see the sample line of code:
e.g
Sample code:

style.setCustom(“000000000000”);

Hope, this helps a bit.

PS. for “style.setNumber(49); is not giving the expected result. It shows the value as ###]”:
Please extend the width of the column or apply auto-fit operation via Aspose.Cells APIs (e.g Worksheet.autoFitColumn()) for your needs.

Thank you.

Hi Amjad,

I can understand that the region code will affect all the columns in the workbook. But i can set one column in mm/dd/yy and another column in dd/mm/yy in an excel manualy.

In my CSV one column is mm/dd/yy and the another one is dd/mm/yy what should i do. setting the country code will not work for me. please provide a solution for this case.

Refer the attached xls file which i created manualy in excel, it has both mm/dd/yy and dd/mm/yy formats in a same workbook. i want this same result when converting form CSV to XLS.

In aspose, if i create a cell/row/column, i can set any datatype and format easily. It will be helpful if i can do the same when it is created
from the CSV. I just want to avoid the automatic/default/Locale formats
which is getting applied to the XLS which is created from the CSV.

And date alone is not my problem, i have currency columns of different countires in the CSV. In excel $ alone identified as currency. another formats are not identified as currency.

Hope this clarifies my requirement.

Thanks and regards
Farhana Y

Hi,

Farhana:


I can understand that the region code will affect all the columns in the workbook. But i can set one column in mm/dd/yy and another column in dd/mm/yy in an excel manually.

Refer the attached xls file which i created manualy in excel, it has both mm/dd/yy and dd/mm/yyformats in a same workbook

Well, this is not what I meant. Your attached file can be created by setting different custom date formatting to different cells/ range of cells, see the sample code. I have also attached the output file for your reference:
e.g
Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
Cell cell = worksheet.getCells().get("A1");
cell.putValue("1-12-2014",true);
Style style = cell.getStyle();
style.setCustom("mm/dd/yy");
cell.setStyle(style);

cell = worksheet.getCells().get("B1");
cell.putValue("1-12-2014",true);
style = cell.getStyle();
style.setCustom("dd/mm/yy");
cell.setStyle(style);


workbook.save("outdtTest1.xlsx");


Farhana:

In my CSV one column is mm/dd/yy and the another one is dd/mm/yy what should i do. setting the country code will not work for me. please provide a solution for this case.

...................

Well, you might already know the limitation of CSV file format, the problem with CSV file format is, it is just a text format and does not store the formatting attached to it, you may confirm this when you manually create the CSV file format or check its contents when opening the file into notepad. The CSV file format does not attach any type of formatting so when you load the file and save it to Excel file format, here your region settings comes into play, so when you open it into Ms Excel, it would display the Date cells according to locale settings.

Thank you.

Hi,

We have evaluated your issue further. Well, for your requirement, we think you can use PreferredParsers to parse columns by your own logic. Below is an example for using your custom parser, in fact you can build your own parsers to parse any type of any column data:
e.g.
Sample code:

TxtLoadOptions loadOptions = new TxtLoadOptions();

...

loadOptions.PreferredParsers = new ICustomParser[] { null, new CC1(), new CC2(), null }; //let the 2nd and 3rd column use the special parser, others use Cells’ default parser

...

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(csvStream, loadOptions);

class CC1 : ICustomParser

{

private SimpleDateFormat parser = new SimpleDateFormat(“mm/dd/yyyy”);

public object ParseObject(string value)

{

return parser.parse(value);

}

public string GetFormat()

{

return “mm/dd/yyyy”;

}

}

class CC2 : ICustomParser

{

private SimpleDateFormat parser = new SimpleDateFormat(“dd/mm/yyyy”);

public object ParseObject(string value)

{

return parser.parse(value);

}

public string GetFormat()

{

return “dd/mm/yyyy”;

}

}

Hope, this helps a bit.

Thank you.

Thank you so much… It solves my requirement :slight_smile:

Hi,
Good to know that it figures out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you

Hi Amjad,

I have an issue, when i parse the date with time constrains in the below logic.

private SimpleDateFormat
parser = new SimpleDateFormat(dateFormat);

public object
ParseObject(string value)

{

return parser.parse(value);

}

public string GetFormat()

{


return dateFormat;

}

When i use a format (dd/MM/yyyy) with a value (27/10/2014) It gives the proper result (27/10/2014).

But when i give the format as (MM/dd/yyyy hh:mm:ss a) with the value (11/24/2014 02:30:00 PM) It gives the results us 11/24/2014 14:30:00 a instead of 11/24/2014 02:30:00 PM. Is there any way to solve this.

[Note: i gave the hours as small hh not capital HH]

Kindly refer the attached sample output file.

Thanks and regards,

Farhana Y








Hi,


I think you may format it as “mm/dd/yyyy hh:mm:ss AM/PM” for your requirements.

Thank you.

Hello Amjad,

Thank you, It works when i give it as “mm/dd/yyyy hh:mm:ss AM/PM”. And the same kind of problem occurs in the formats of date with time zones.

Examples:


24 Nov 2005 GMT+05:30 [Code : dd MMM yyyy z]
1994-11-05T13:15:30+0530 [Code : yyyy-MM-dd’T’HH:mm:ssZ]

I don’t know whether its possible or not. I referred the below page for formatting dates and time in excel

Microsoft Support

And i tried with (z/zz/zzz/Z) but its not working.

Thanks and regards,
Farhana Y

Hi,


Thanks for providing some details.

Could you provide us a template file in which you got your expected results by applying the custom formattings. We are also not too sure if MS Excel allows this or does not allow it. If MS Excel allows this, we can surely look into it and support it.

Thank you.