HI I am using Aspose Cells for Java and trying to export to CSV.
and am trying to export 1.5 million rows.
csv export is being used for data dump in our app and I am getting a error “Invalid row index”
I want to confirm that the row limit is getting limited by the fact that the Workbook object although being used for export to csv can hold only excel2017 row limit.
If I wish to export more than 1.5 million rows have to use something different - if you could say anything else within the aspose suite.
We are using Aspose Enterprise Edition - Aspose Total for Java 8.1.2 version
Workbook CSVWB = new Workbook();
Worksheet CSVsheet = CSVWB.getWorksheets().get(0);
Cells cell = CSVsheet.getCells();
TxtSaveOptions opts = new TxtSaveOptions(SaveFormat.CSV);
opts.setSeparator(’,’);
opts.setQuoteType(TxtValueQuoteType.ALWAYS);
for(i=0;i<1500000;i++)
{
cell.get(i,0).setValue(“TEST”);
}
/**AFTER ABOUT 1048576 rows then it throws Invalid row index and errors/
CSVWB.save(outputstream,opts);
NOTE: after 1048576 should I be creating another sheet and starting from the start. Will it put my data properly one after the other. My real code is a multi column output.
On another note: I did try creating a new sheet to put the data to… - it only outputs the first 1048576 rows to the csv file
Sample code below:
public static void main(String[] args) throws Exception
{
Workbook CSVWB = new Workbook();
CSVWB = new Workbook();
Worksheet CSVsheet = CSVWB.getWorksheets().get(0);
Cells cell = CSVsheet.getCells();
TxtSaveOptions opts = new TxtSaveOptions(SaveFormat.CSV);
opts.setSeparator(',');
opts.setQuoteType(TxtValueQuoteType.ALWAYS);
int k=0;
int remain=0;
boolean addedsheet=false;
for(int i=0;i<1500000;i++)
{
if(i<1048575)
{
cell.get(i,0).setValue("TEST");
}
else
{
if(!addedsheet)
{
CSVsheet=CSVWB.getWorksheets().add("new_sheet");
cell = CSVsheet.getCells();
addedsheet=true;
System.out.println("Came into else part : added a new sheet");
}
System.out.println("Came into else part : i is "+ i+" k is : "+k);
cell.get(k,0).setValue("TEST");
k++;
}
}
CSVWB.save("C:\\Users\\mishrach01\\Downloads\\MSTR_Rewrite\\regression\\new_trouble.csv");
}
@chetan.mishra,
Yes, you are right it is row limit set by MS Excel. I have verified it in Excel 2016 as well.
Yes, you need to create new worksheet after 1048576 rows. When we save an Excel file to CSV, it only exports current active sheet. It is the default behaviour of the Excel which saves current active sheet to CSV only. You may please save individual sheet to CSV.