Date format issue in Excel of Aspose.cell Java

Aspose.Cells Product Family
1

Hi Team,

I am generating a .xlsx file by combing .csv files. The date format in cell and the formula bar of csv file ‘DD-MM-YYYY’ .
CSVDateFormat.PNG (2.2 KB)

The same csv is combined to forma a excel file where the date format in cell in ‘DD-MM-YYYY’ ExcelCellFormat.PNG (833 Bytes)
and ‘MM-DD-YYYY’ ExcelFormulaBarFormat.PNG (1.6 KB).

Server
Linux OS
Tomcat v8

Code snippet used to merge CSV to Excel:

Workbook ExcelWorkbook = new Workbook();
//In a for loop of number of csv files present
Workbook wb = new Workbook(“Path to csv file”, opt);
Worksheet ws = wb.getWorksheets().get(0);
…
//First csv to be copied to excel
if (FirstCSV) {
ExcelWorkbook .copy(wb);
} else {
//Second CSV onward, should be appended to the existing excel
ExcelWorkbook .combine(wb);
}
wb.dispose();
ExcelWorkbook .dispose();

I am using Aspose aspose-cells.18.5.jar. Kindly let me know how it can be done.

Thanks

2

@Manoj_Velliyatt_in_ey_com,
I have tried to reproduce the issue using my sample CSV files and Excel file but could not identify the issue. Could you please explain it more and share the sample CSV files, template Excel file and output file with us for our testing? Provide us an expected output Excel file which is created using Excel along with the steps to produce this file. We will analyze the information in detail and provide our feedback.

3

Hi Ahsan Iqbal,

Please find below the CSV file which is the input.
DataCSV-csv.zip (385 Bytes)

The excel file generated from the system is attached below.
Data_Excel-xlsx.zip (291.7 KB)

Server : Red Hat Enterprise Linux Server release 7.6 (Maipo)

The code is same as in the prior query. If still there is an issue, we can get into a skype call to demonstrate the problem in our environment.

Thanks

4

@Manoj_Velliyatt_in_ey_com,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-42901 - Date format issue while importing CSV data to Excel
5

Hi Team,

Is there any update on this item, its a little urgent issue.

6

@Manoj_Velliyatt_in_ey_com,

I am afraid, your issue is not resolved yet. I have logged your concerns against your issue into our database. We will try to figure it out soon.

Once we have any new information, we will share it with you.

7

@Manoj_Velliyatt_in_ey_com,
For parsing date values, API will try to use the default field sequence of corresponding region, if it is not a valid date of the default sequence, then it will try other formatting. For your case, the field sequence is Day-Month-Year, so please use a region with the default sequence as it, such as TxtLoadOptions.setRegion(CountryCode.UNITED_KINGDOM);

Let us know your feedback.

8

Hi Team,

The date issue got resolved. Below is the code that helped.

//Setting region To UK while importing CSV file
LoadOptions opt = new LoadOptions();
opt.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);
opt.setRegion(CountryCode.UNITED_KINGDOM);

					Workbook wb = new Workbook(file.getPath(), opt);

…

//Setting Locale and Region to UK before saving the final excel workbook
finalWorkbook.getSettings().setRegion(CountryCode.UNITED_KINGDOM);
finalWorkbook.getSettings().setLocale(Locale.UK);
finalWorkbook.save(“”);

Thanks for all the support given in solving this.

9

@Manoj_Velliyatt_in_ey_com,
Good to know that your issue is sorted out by the suggested line of code. Feel free to contact us at any time if you need further help or have some other issue or queries, we will be happy to assist you soon.