Export formatted date cells to CSV

Aspose.Cells Product Family
1

I’m using Aspose.Cells to extract and process Excel documents. For the extraction I’m using the workbook.save() method to save the Excel to CSV. Then I read the CSV to do the required processing. What I noticed is that the export to CSV loses some formatting rules, especially for date formatted cells. I really need the exported CSV to contain the same formatted values as the original Excel document.

I’m using the following code to export an Excel document to CSV:

Workbook wb = new Workbook();
wb.open(in);
wb.setCalculateBeforeSave(true);
wb.setCalculationMode(CalculationMode.AUTOMATIC);
wb.save(out, ‘|’, “UTF8”);

I also noticed that the month names are adjusted to the regional settings currently active on the machine the code is running. This behaviour also differs from Excel. Within MS Excel the month names do not change when switching between regional settings.

I attached the original Excel document together with 2 CSV export files (one is the result of the save with the NL regional settings applied and one with the UK regional settings). As you can see in the export files the first date is incorrectly formatted (1/14/2011 instead of 14/01/2011). Another difference is that the month name is changed according to the currently used regional settings.

I really need some help how to get a CSV export with exactly the same formatting as when the file is opened in MS Excel.

Kind regards,.

2

Hi,


Please download latest release of Aspose.Cells JAVA v2.5.4. A few releases back, we made enhancements related to setting Locale for workbook. I suggest you should also go through the Aspose.Cells JAVA API reference guide. Right now the class for your concern is WorkbookSettings.

The problem you are facing is related to the Locale settings of the Workbook. Using latest release of Aspose.Cells JAVA API, you can set the Locale of your choice and hopefully you will get consistent results even changing the regional settings of your machine [Please feedback us on this]. Below is the sample source code, also attached is my output file for your reference.

JAVA Source Code:

Workbook wb = new Workbook();
wb.open(“C:\temp\testDateFormatsWorksheet.xls”);
WorkbookSettings setting = wb.getWorkbookSettings();
setting.setLocale(java.util.Locale.UK);
wb.save(“C:\temp\out.csv”,FileFormatType.CSV);

3

Hi,

Thank you for your answer. I downloaded the latest version and it is working better now. However there are still some issues with cells formatted in long date. Please check the attachment for a graphical representation. The image contains views of Excel, format cells boxes, the CSV output and my operation system’s regional settings. As you can see I configured my system to use the Dutch regional settings, and the long date format is set to “day in week; day in month; month; year”. Cell A5 contains a fixed date format and should not change depending the regional settings, but in the CSV output it is using the configured Dutch regional settings. This is unexpected behaviour. As you can see in the left Format Cells box the date format is not prefixed with an asterisk (*) and should not switch date orders. The date in cell A6 is using the configured regional settings but the format in the CSV differs from Excel (vrijdag 14 januari 2011 vs vrijdag, januari 14, 2011).

Please help me how to fix this.

Kind regards,.

4

Hi,


For the issue of month names, we have fixed it to make the output month name be same as what shown in MS Excel. Please try the latest fix version of Aspose.Cells JAVA v2.5.4.3 [attached] and let us know of your feedback.
For the date format of cell A1, we think the output is just same as what shown in MS Excel. When you change the locale from NL to US or vise versa and Re-Open MS Excel and the template file, the date format be changed too. Please see attached screeshots for MS Excel in different locale and the generated csv file for different locale [archive attached]. For your newly reported long date format of cell A6, we need more time to make further investigation.
5

Hi,

I have tested the version you sent to me and it is working better now, but there still are some issues. I get an different output when processing the file date_time-formats.xls [see attachment] with the UK regional settings. The long date differs (cell A1), but you already mentioned that you need more time to investigate that. Could you please also check the differences of the other two cells (A2 and A3)? And could you tell me how to fix this?

Kind regards,.

6

Hi,


Thank you for your feedback.
I have experienced the same date formatting issue in Cells A2 and A3 while converting your provided XLS file to CSV. I have attached your comments to the Ticket associated with this Thread. Hopefully soon we will reply you back on this.
7

Hi,

We have fixed this issue. Please download Aspose.Cells for Java v2.5.4.5.

8

Thank you!