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,
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,
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);
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,
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,
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
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.gSample 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
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,
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,