Issue Exporting records from a 300MB text file

Aspose.Cells Product Family
1

Hi There,

I’m trying to export 800,000 records from a text file (of size 300MB) to a xlxs file.
I’m using ‘aspose-cells-7.0.2-java/JDK 1.6’ and java -Xmx3072m (3GB) to run the java file.

But the xlxs file generated consists of only 28,318 records. and the size of the file is 2.5MB.
Is there a limitation in aspose as to the number of records that can be exported?

I’ve posted the code below too.
/***************************/
values = text file;

//Reading values
String vals = values;
try
{
BufferedReader in = new BufferedReader(new FileReader(values));
String str;
while ((str = in.readLine()) != null)
{
vals =str;
}
in.close();
}
catch (IOException e)
{
}

String[] tempstr;
String delimiter_one = “:”;
tempstr = vals.split(delimiter_one);
for (int j = 0; j < tempstr.length; j++)
{
String[] tempval;
String valdelimiter = “<”;
tempval = tempstr[j].split(valdelimiter);
for (int k = 0; k < tempval.length; k++)
{

cells.get(rowIndex, k).setValue(tempval[k]);
}
rowIndex++;
}
wb.save(template_file_path + “.xlsx”, FileFormatType.XLSX);
}
/***************************/

Your help is much appreciated.
Thanks & Regards,
Nish

2

Hi,

No, there is no such limit imposed by Aspose.Cells. The only limitation is xlsx format itself and it can support 1048576 rows.

You can create a for loop and create an xlsx file and save it using Aspose.Cells.

Java



//place value in A1, A2, A3 …so on

for(int =0; i<1048576; i++)

{

//code to put values

}


workbook.save(“c:\test.xlsx”);

3

Hi shakeel,

Thanks for the reply.
I am using a for loop to add values to the cells and then I save it as you have mentioned above.
But the problem is that the amount of data to be kept in the java memory (until the workbook is saved) is too much. the text file I’m reading from is 375MB.
I want to save the empty work book first and then read the book and append data to it. Is this possible? it’s more like editing/updating the file for each row I want to insert.
How do I do this?

Following is the code I currently have.
---------------------------------------
Workbook wb = new Workbook();

WorksheetCollection worksheets = wb.getWorksheets();
Worksheet sheet = worksheets.get(0);
sheet.setName(“Estimates”);
Cells cells = sheet.getCells();

int rowIndex = 1;
int colIndex = 1;

//reading data from text file
File file = new File(filepath);
int ch;
FileInputStream fin = null;

try {
fin = new FileInputStream(file);
while ((ch = fin.read()) != -1)
{
// code to get tempval
for (int k = 0; k < tempval.length; k++)
{
cells.get(rowIndex, k).setValue(tempval[k]);
}
rowIndex++;
}
fin.close();
} catch (Exception e) {
System.out.println(e);
}
wb.save(template_file_path + “.xlsx”, FileFormatType.XLSX);


Your help is very much appreciated,thanks again.
Regards,
Nish

4

Hi,


We think that for your requirement, you should try the LightCells API. It will save the memory and enhance the performance. Please find attached the samples on LightCells API for your reference.



Thank you.