hi team
is there any way to read .csv file and dump data in to excel file and also apply formatting for Columns or cells.
we are opening csv file as workbook and dump data into another workbook cell wise and apply the format to cells.i previously posted code and input file in this forum. it will take more time to generate excel file from csv. which has 1,56,778 records
please provide solution to decrease the generating time.
Hi,
Do you mean to say that you have a csv file that has lots of records “1,56,778 records”, you are opening the csv file and then apply some formatting etc. and then you finally save the file to Excel file format (e.g XLS/XLSX etc.).
Well, we have done some optimization work recently in the Aspose.Cells for Java internal model for loading large data sets. We recommend you to try the following (this should work efficiently):
- Open your csv file by Workbook.open() method first.
- Apply formatting to the cells.
- Finally save it to your desired format.
If you still find the issue e.g the time is taken too long, please give us your sample code and template csv file here to reproduce the issue on our end, we will check it soon.
Thank you.
hi Amjad Sahi
thanks for giving solution
this is my code
Workbook readWorkBook = new Workbook();
FileInputStream fstream = new FileInputStream(“D:\jdev11i\jdev9\jdevhome\jdev\myhtml\OA_HTML\eisrs\tmp\1008404_1589.csv”);
readWorkBook.getOpenOptions().setEncoding(“UTF-8”);
readWorkBook.open(fstream,FileFormatType.CSV);
applyFormat(readWorkBook );
fstream.close();
readWorkBook.getSaveOptions().setEncoding(“UTF-8”);
readWorkBook.save(“D:\jdev11i\jdev9\jdevhome\jdev\myhtml\OA_HTML\eisrs\tmp\1008404_3265_1.xls”);
in this case i am getting error like " this file in different format than specified by the file extension"
please find the attachement.
Hi,
Kindly attach your input csv file and output XLS file here, so, that we may check your issue.
Also, make sure that you are using latest version/fix v2.5.2.7
Thank you.
Hi,
After closely looking into your code, we think one possible factor that you are getting error message in MS Excel (while opening the generated file) is you are not specifying file format type when saving the csv file to xls format. For your information, if you do not specify the file format when saving the workbook, we will use the original file format for which the workbook was opened, in this case it is CSV.
So please change your line of code:
readWorkBook.save(“D:\jdev11i\jdev9\jdevhome\jdev\myhtml\OA_HTML\eisrs\tmp\1008404_3265_1.xls”);
to:
readWorkBook.save(“D:\jdev11i\jdev9\jdevhome\jdev\myhtml\OA_HTML\eisrs\tmp\1008404_3265_1.xls”, FileFormatType.EXCEL97TO2003
);
and let us know if it works fine.
Thank you.
Hi Amjad Sahi
case 1:
in my csv the total no of ows are more than 1,56,777 and 100 columns.
for this we are taking copyCellRange function (readcells.getCells(),0,0,65535,readcells.getMaxDataColumn());
here this function works for csv which has less than 65535 rows.
if it is having more than it gives out of meory error.
case2:
but i tried in another way.
taken one csv file having 5 rows and 15 columns.
now i rotate the loop up to 65536 using copyRow().
this is repeated for 5 sheets.
in this case it is working fine.
in first case why it is failed.
any range restriction for copyCellRange() method and copyRow() method.
i am using cells java 2.5.2.8
Hi,
For case 1:
Well, to create such a large data set (e.g 156777 * 100 cells), it is certain that lot of memory will be needed and the ouput file would be large sized too. Also, if you are saving to XLS it is restricted to 65536 while saving to XLSX don’t have this large data set restrictions. I think you may assign sufficient memory to JVM for the bigger process.
Also if you just want to save the csv file to xls/xlsx file, we recommend you to use LightCellsDataProvider interface to save to xlsx file format in light mode, that will save much more memory for you.
See some threads for your reference:
http://www.aspose.com/community/forums/211605/sequential-reading-writing-excel-files/showthread.aspx#211605
http://www.aspose.com/community/forums/287346/out-of-memory-issue-with-aspose-cells-for-java/showthread.aspx#287346
Thank you.
hi team
thanks for the information.
we executed the program using “LightCellsDataProvider” interface in Jdeveloper and eclipse.
in eclipse it successfully executed with 20,00,000 , 140 columns and 40 sheets in 90 min above with xlsx format
but it is failed in case .xls format.
in Jdeveloper it gives the OutOf Memoryerror.
we need to generate the excel file with large amount of data as i mentioned above in different format
like 2003,2007,2010. and also apply format to cells .
please give the efficient way to generate excel file.
Hi,
"in eclipse it successfully executed with 20,00,000 , 140 columns and 40 sheets in 90 min above with xlsx format
but it is failed in case .xls format.
in Jdeveloper it gives the OutOf Memoryerror."
Well, for your information for XLS format, we can only have 65536 records in a worksheet, also the columns should not be more than 256. This is the limitation of MS Excel 97-2003) XLS format.
Where as for XLSX (MS Excel 2007 - 2010) format, we can have millions of records. So, you should use XLSX format if you are having big records (check the limitation put forth by MS Excel regarding formats).
Thanks for your understanding!
hi Sahi
thanks for the information.
we need to generate excel file different formats like 2003,2007, 2010.
the csv file contains lots records recently get requirement to generate excel(formats:2003,2007,2010) from csv having 34,00,000 rows and 140 columns.
our server details:
we executed the previous program(LightCellsDataProviderPerf) in oracle apps (server:apache)
it is fail to generate excel file.
gives error like “OutOfMemoryError”.
excel2003:
for this file generation we are using this technique."when rows are exceeds 65,536. we add the new worksheet to the workbook.this will continue until end of rows(34,00,000) columns140."
we find that excel 2003file does not support more than 18 columns and 65,536. when we are trying to add new sheet to exccel2003 file we get error “OutOfMemoryError”.
excel2007:
for this file generation we are using this technique.“when rows
are exceeds 1,048,576<span style=“font-size: 12pt; font-family: “Verdana”,“sans-serif”;”>. we add the new worksheet to the workbook.this will
continue until end of rows(34,00,000) columns140.”
we get
error “OutOfMemoryError”.
1.how to fix this issue.
2.is there any way to generate excel file from csv in 5min.
Hi,
I am afraid there is no better way to make such big processes to use lesser amount of memory and to consume little time. Anyways, we will make further investigation soon.
Keep in touch.
Thank you.
hi Sahi,
1.could please tell me how many rows and columns supported by aspose excel2003 in dynamic generation.(means when row no is exceeds 65536 then add new sheet to workbook like this how many sheets and columns are supported)
and also give details of these formats excel2007 and 2010 in case of dynamic excel generation.
2.aspose behaves differently in different environments.
1.in eclipse excel 2003 allow only 1sheet with 65536 rows and 18 using lightdataprovider
2.in oracle application excel2003 allow 10 sheets with 50000 rows and 14 columns in 5 sheets using copyRow() method.
could please clarify this one.
3.could please give optimize solution to generate excel from csv having 34,00,000R and 140 C.
Hi,
Aspose.Cells for Java is a pure Java component and does not depend on any special environment what so ever. For different application servers, the different performance (memory and time cost) should only depend on the optimization and management of the JDK/JRE itself. For the limit of Rows/Columns/Worksheets, it is only determined by the different file formats (when the available memory is enough for the Workbook model). Currently we are afraid we cannot find better way to save much memory for creating large files. With LightCellsDataProvider, it should not cost too much memory for generating xlsx files. By our test, to create one xlsx (even the final file size will be 5-10M) only about 2-3M memory will be consumed (When saving the Workbook directly to a File or FileOutputStream). So, if you would like, please send us the sample code and template file to create your desired xlsx file, we will check whether some optimization can be made for your code accordingly when using Aspose.Cells APIs.
Thank you.
hi
any update on this issue?
to create large xls file in less time.
Thanks & Regards
Ranjith
Hi,
Please check my previous reply.
With LightCellsDataProvider, it should not cost too much memory for
generating xlsx files. By our test, to create one xlsx (even the final
file size will be 5-10M) only about 2-3M memory will be consumed (When
saving the Workbook directly to a File or FileOutputStream). So, please send us the sample code and template file(s) to create
your desired xlsx file to show the issue, we will check whether some optimization can be
made for your code accordingly when using Aspose.Cells APIs.
Thanks for your understanding!
hi
we are using Light Cell API.
we fail to create xlsx from csv having 10,00,000 and 100 cols
.
we need your help to do some optimization on code.
run this jsp in oc4j server on Oracle Applications
please find the attached files
i attached one sample csv file and code.
create one csv having 10,00,000 rows and 100 cols
perform some optimization on code.
give us some optimized solution for this.
how to apply formatting to cols which are generated by LightCell API
Tanks & Regards
Ranjith.