Dear Team,
I am converting a CSV file in to XLS 97 or 2003 format. In which the date field is not exporting properly. It is exporting it as some thing like “###################################”. I don’t know the length of these hashes.
Please fix this at the earliest possible. Currently I am using 2.5.4.8 version Aspose.cells. Please friends, don’t break the existing things while fixing this, this is what happening till now.
Regards,
Kiran Kumar
Hi,
Could you try the latest version v7.0.0:
If you still find the issue, we need your input CSV and output XLS files here, We will check your issue soon.
Thank you.
I have attached the files, which I have created using Aspose 2.5.4.8 as well as 7.
Below is the code I am using (Written in 7),
Workbook newWB = new Workbook(new ByteArrayInputStream(data), new LoadOptions(FileFormatType.CSV));
newWB.save(, FileFormatType.EXCEL_97_TO_2003);
Here the “data” is byte array.
Regards,
Kiran Kumar
Dear Team,
One more question.
Through the above process, I can write everything in to a single sheet. For that, I am creating multiple CSV files with each 65,536 rows and creating a workbook for each CSV file and then at the end combining all those workbooks into a single one.
During the process, I am facing Java Heap Space/out of memory issues. If I can write it in the following way it would solve my problem, say 5000 rows(CSV file) at a time on to a single sheet until it completes 65536 rows, and then the next sheet…etc.
Is it possible with Aspose now? If yes, let me know the version and API to do that.
Thanks in advance.
Regards,
Kiran Kumar
Hi Kiran,
Well, DateTime in Excel are stored in numeric values, so you are getting numeric values instead of date values. For your need, you should format the first column to DateTime accordingly. Also, for “####…”, it displays same as MS Excel does when a value is larger. You may open your CSV file into MS Excel and you can see the “####…” for some values in the cells. For this, you may auto-fit Columns feature.
See the complete sample code, the output file is fine as I tested.
Sample code:
Workbook newWB = new Workbook(“Sample_7.csv”, new LoadOptions(FileFormatType.CSV));
//Accessing a column from the Columns collection
Column column = newWB.getWorksheets().get(0).getCells().getColumns().get(0);
Style style = newWB.createStyle();
style.setCustom(“m/d/yyyy h:mm”);
//Creating StyleFlag
StyleFlag styleFlag = new StyleFlag();
styleFlag.setNumberFormat(true);
//Applying the style to the column
column.applyStyle(style, styleFlag);
//Autofit - columns
newWB.getWorksheets().get(0).autoFitColumns();
newWB.save(“out1Sample_7.xls”, FileFormatType.EXCEL_97_TO_2003);
For memory issue, if you are creating many sheets with full capacity e.g records up to 65K, you might need lot of memory for this bigger process. Make sure that you got sufficient memory allocated for the process (in the JVM) when you do the big task.
e.g
You may try some command line too. Following is the command line you may use on the Windows XP/7 command prompt to extend the JVM memory accordingly to complete the task while running the program:
e.g
java -Xms1024m -Xmx1024m MyTestProgram
Thank you.
Dear Amjad Sahi,
Believe you missed the scenario,. We need to automate the conversion and please be informed that it is NOT SUPPOSED TO HAVE ANY MANUAL INTERVENTION.
It is not possible for me to format each and every column, because I just have a CSV to be converted in to XLS format and I do not know any of the columns data type as well as format of the columns. If I need to format each and every column, I cannot automate the conversion process. Because, the file to be converted should always have the same date format.
If this is not supported, I may not go for conversion process at all. Instead, I can read the CSV file and can write the XLS file using Aspose. To avoid such difficult process, I am doing this direct conversion. Without this, your conversion code may not be of much use to us.
In addition to this question, I have an another problem,
When I convert the CSV to Excel, all columns of Date have an issue. (The date in the CSV is correct but the exported one is wrong).
Ex: Date: 26/10/2011 (dd/MM/yyyy) is converted as 10/03/2013 .
Please look in to the issue. It’s high priority for me.
PS1: I am using aspose version 2.5.4.8
PS2: The latest version of ASPOSE seems to be very UNSTABLE. The version 2.3.2.11 was more stable and had only one issue. The date I have updated to the new jars, nothing but all the troubles invited.
PS3: If things are not sorted out, we are forced to look for other alternatives.
Thanks,
Kiran Kumar
Hi,
“I have an another problem,
When I convert the CSV to Excel, all columns of Date have an issue. (The date in the CSV is correct but the exported one is wrong).
Ex: Date: 26/10/2011 (dd/MM/yyyy) is converted as 10/03/2013 .”
Please give us your template and output files here, we will check it soon.
Regarding PS(s):
I am afraid, we cannot help you much regarding older versions. Moerover, we cannot evaluate or fix any issue in the older versions either. You have to use latest version/fix and if you find any issue, we are bound to fix it very soon for your need. So, you need to upgrade to latest version with changed APIs structures. The latest version (e.g. 7.0.2:
http://www.aspose.com/community/files/72/java-components/aspose.cells-for-java/default.aspx) is not stable with many enhancements. We recommend you to try to upgrade to latest version and try our latest fixed version i.e…,
v7.0.2.5.
Thanks for your understanding and sorry for any inconvenience caused!
Hi,
Thank you for your feedback. Yes, we understand and thank you for your understanding and patience. And we always try to make our product more enhanced, stable and flexible for users.
For your another issue and question:
1).The datetime in CSV template file be changed as double value:
We will provide you a fix in this week to solve this issue.
2).Date: 26/10/2011 (dd/MM/yyyy) is converted as 10/03/2013:
Yes, it should have been fixed in our latest fix because we cannot reproduce it here. And we don't think there are other known issues for it.
3).Java Heap Space/out of memory issues for large csv files:
We think you can avoid to hold all CSV files(all Workbook objects) in memory at the same time. You can combine CSV files one by one by yourself. Please refer to the followng code:
Workbook resultBook = new Workbook();
WorksheetCollection resultWss = resultBook.getWorksheets();
Cells resultCells = resultWss.get(0).getCells();
int count = 0;
for(int i=0; i<files.length; i++)
{
Workbook tmpBook = new Workbook(files[i]);
Cells tmpCells = tmpBook.getWorksheets().get(0).getCells();
int nextCount = count+tmpCells.getMaxDataRow()+1;
if(nextCount > 65536)
{
resultCells = resultWss.get(resultWss.add()).getCells();
nextCount -= count;
count = 0;
}
resultCells.copyRows(tmpCells, 0, count, nextCount-count);
count = nextCount;
}
Moreover, if you only want to combine and save multiple CSV files as one XLS/XLSX file, we think you can try LightCells APIs, which will save more memory for you.
Hi,
Please try the attached version: v7.0.2.6.
We have fixed the issue of reading DateTime
value "Jan 01, 2004 12:00:00 AM" as double issue.
For LightCells API, it is used for a to
create large xls/xlsx files with large cells dataset. In this mode a user
provides cells data in saving process and the component will save those data
directly into the resultant file without building the complete data model in the memory. In this way certain, the amount of memory may be saved because not many
cell/row objects are built in memory. However, because those cells data is not
in memory, so those operations and models that need those cells data will not
work at runtime, such as, calculate formulas, create pivottables, ...etc. If it
can fit your requirement, we will provide you sample code next week to show its
usage.
Thank you.
Dear Amjad Sahi,
Yes, it looks like yout lightcells api will be the best option for us. Please give me the link to your lightcells API.
Thanks & Regards,
Kiran Kumar
Hi,
We will soon provide you the example code(s) for LightCells API.
Thank you.
Hi,
Attached is an example program to show how to combine multiple CSV files into one final workbook and save the workbook in Light mode. Hope it can help you for your situation.
Thank you.
The issues you have found earlier (filed as CELLSJAVA-xxxxx) have been fixed in this update.,
Dear Team,
We are using Aspose.cells 7.0.3 jar now. After converting the data from CSV to XLS, the date format is getting changed and the date is showing wrongly (2-may-2010 10:2:2). All leading zeros are ignored. Please take this as high priority as we can not answer our customers.
Check the attached XLS file
Thanks,
Kiran
Hi,
Could you attach the source (input) CSV file from which you saved to XLS file (you attached). We need to test your issue. Also, did you check manually in MS Excel, I mean you may open your CSV file into MS Excel and then save to XLS to check it Date column is rendered in the same way as Aspose.Cells’s generated XLS file.
Thank you.
Dear Amjad Sahi,
I have tried opening my CSV file in MS Excel and saved it as Excel work book 97-2003 format. The XLS file has formatted the date as it is in the CSV file.
Please find the attached CSV and XLS files for your reference. However in the reported case, the hour representation hh:mm:ss (ie., “01:02:01”) in the CSV file is wrongly converted by Aspose as 1:2:1 which is the one not expected. We prefer the formats in the CSV file to be maintained as it is in the converted XLS file.
One more issue with the new jar is, setAutofitcolumns() method is not working.
We would also like to bring to your kind notice that, it is really difficult for us to stop our development activities and to test your jar extensively for breakages, in turn that has a impact in tasks we have planned. The key point is we would like to have a stable build (well tested for all the use cases, free from breakages and with all the reported issues fixed) and less number of mail threads.
Thank you for your support and awaiting a stable build.
Regards,
Kiran Kumar
Hi Kiran,
I can find the issue as you have mentioned. I tried the code with your template file.
Sample code:
TxtLoadOptions lo = new TxtLoadOptions(LoadFormat.CSV);
lo.setSeparator(‘,’);
lo.setEncoding(Encoding.getDefault());
Workbook workbook = new Workbook(“Store_Sales.csv”, lo);
workbook.getWorksheets().get(0).autoFitColumns();
workbook.save(“output_Store_Sales.xls”, SaveFormat.EXCEL_97_TO_2003);
The auto-fitting columns is fine but the date are not rendered fine.
I have logged a ticket with an id: CELLSJAVA-40055. We will look into it soon.
Also, I am trying our latest fixed version, v7.0.3.3: v7.0.3.3.
Hi,
Thank
you for your template file. We have re-built our parser for reading text format
template file to recognize more formated values from V7. The rebuilt parser fits
more requirements of some users, but it is really a complicated task for us to make
it give exact the same result with what shown in ms excel for all situations. We will try to improve
the parser in later fix and versions. Currently if you required, we can make
the formatting as “HH:mm:ss” instead of “H:m:s” for your situation. But at the
same time, the values with “H:m:s” formatting in csv template will also be
converted to formatting “HH:mm:ss” too.
Another option for you currently is to
implement your custom parser for your specific template file, here
is the code example for your reference.
For the issue of autofit columns, we cannot find it by using your given csv file. If you found this issue with our latest fix, please provide us the template file and code to reproduce it, we will look into it.
Thank you.