Hi all
I need a small help. I have an excel file which has 22,000 lines. I am reading the file data and write it into another excel file by making some changes.
wokbook.save(filename,FileFormatType.Default);
while saving i am getting this exception.
java.lang.ArrayIndexOutOfBoundsException: 8228
please help me how i can resolve it.
Thanking you,
Regards,
Bertha
Hi Bertha,
Could you post your template file here. We will check it soon.
Thank you.
Hi All,
Thanks for your reply. I have attached my file. I am getting array out of bounds exception while saving the file.
please help me as soon as possible.
Thanking you.
Regards,
Bertha.
Hi,
Thanks for considering Aspose.
I don't find the problem you are mentioning about.
Following is my sample code and attached the output file.
Hi,
Could you try the attached version.
If the problem still persists, please post your sample code here.
Thank you.
Hi all,
Thanks for your reply.Still i am getting Array index out ofbounds exception. here is my code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Iterator;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.Font;
import com.aspose.cells.Style;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.Cells;
public class Nosales {
int index=1;
static double ntotcost=0;
Workbook finalworkbook = new Workbook();
Worksheet worksheet2 = finalworkbook.getWorksheets().getSheet(0);
public void nosalesGrouping(String TrackId2,String Publisher2,String filename2,String filename3)throws Exception
{
double d=0;
double f=0;
double d2=0;
try{
Connection c1=null;
Statement stmt1=null;
worksheet2.getCells().getCell(0,0).setValue("TrackId");
worksheet2.getCells().getCell(0,1).setValue("AdGroup");
worksheet2.getCells().getCell(0,2).setValue("Publisher");
worksheet2.getCells().getCell(0,3).setValue("Cost");
worksheet2.getCells().getCell(0,4).setValue("Amount");
worksheet2.getCells().getCell(0,5).setValue("Campaign");
worksheet2.getCells().getCell(0,6).setValue("P/L");
worksheet2.getCells().getCell(0,7).setValue("P/L%");
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
String f1="e:\\rama.xls";
//String url="jdbc:excel:////opt/tomcat5/webapps/Bertha/Report/"+adwordsfile+ "";
String url="jdbc:excel:////"+f1+" ";
c1=DriverManager.getConnection(url, "", "");
stmt1 = c1.createStatement();
String s=null;
String query = " Select * from Sheet1 where TrackId = '"+TrackId2+"' and Publisher = '"+Publisher2+"';" ;
int k=0;
ResultSet rs6 = stmt1.executeQuery( query );
while(rs6.next())
{
d=rs6.getDouble("Cost");
f=d+f;
worksheet2.getCells().getCell(index,0).setValue(TrackId2);
worksheet2.getCells().getCell(index,1).setValue(rs6.getString("AdGroup"));
worksheet2.getCells().getCell(index,2).setValue(Publisher2);
worksheet2.getCells().getCell(index,3).setValue(rs6.getDouble("Cost"));
worksheet2.getCells().getCell(index,4).setValue(" ");
worksheet2.getCells().getCell(index,5).setValue(rs6.getString("Campaign"));
worksheet2.getCells().getCell(index,6).setValue("");
worksheet2.getCells().getCell(index,7).setValue("");
index++;
}
int o=worksheet2.getCells().getMaxRow();
int y=o+1;
ntotcost = ntotcost+f;
worksheet2.getCells().getCell(y,0).setValue(" ");
worksheet2.getCells().getCell(y,1).setValue(" ");
worksheet2.getCells().getCell(y,2).setValue(" ");
Style nstyle4 = worksheet2.getCells().getCell(y,3).getStyle();
Font nfont4 = nstyle4.getFont();
nfont4.setName("Times New Roman");
nfont4.setSize(12);
nfont4.setBold(true);
worksheet2.getCells().getCell(y,3).setStyle(nstyle4);
worksheet2.getCells().getCell(y,3).setValue(f);
worksheet2.getCells().getCell(y,4).setValue(" ");
worksheet2.getCells().getCell(y,5).setValue(" ");
worksheet2.getCells().getCell(y,6).setValue(" ");
worksheet2.getCells().getCell(y,7).setValue(" ");
try{
finalworkbook.save(filename3,FileFormatType.DEFAULT);
}
catch(Exception e)
{
System.out.println("exception is ffffffffffffff "+e);
}
index=y+1;
}catch(Exception e){
e.printStackTrace();
}
}
public HashSet distinctTidPub(String adwordsfile )throws Exception
{
String str="/opt/tomcat5/webapps/Bertha/Report/"+adwordsfile;
String f="e:\\rama.xls";
Connection c = null;
Statement stmnt = null;
Workbook workbook1 = new Workbook();
Worksheet worksheet = workbook1.getWorksheets().getSheet(0);
workbook1.open(f,FileFormatType.DEFAULT);
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
String url="jdbc:excel:////"+f+" ";
c = DriverManager.getConnection(url, "", "");
stmnt = c.createStatement();
stmnt.setFetchSize(1000);
String s=null;
System.out.println("Before query");
String query = "Select TrackId,Publisher from Sheet1 where Status=0" ;
System.out.println("After query");
ResultSet rs = stmnt.executeQuery( query );
HashSet hs1=new HashSet();
HashSet hs2=new HashSet();
while(rs.next())
{
String tid=rs.getString("TrackId");
String pub=rs.getString("Publisher");
String gh=tid+"@"+pub;
System.out.println(gh);
hs1.add(gh);
}
return hs1;
}
public void distinctValues(String adwords_filename,String nosales_filename)throws Exception
{
String f1=" ";
String f2=" ";
Nosales s=new Nosales();
HashSet hi=s.distinctTidPub(adwords_filename);
Iterator ik= hi.iterator();
while(ik.hasNext())
{
String f=(String)ik.next();
System.out.print("\nrama "+f);
try{
f1=f.substring(0,f.indexOf("@"));
f2=f.substring(f.indexOf("@")+1,f.length());
}
catch(Exception e)
{
System.out.println("EXCEPTION IS "+e);
}
System.out.println("\nf1 is "+f1);
System.out.println("\nf2 is "+f2);
s.nosalesGrouping(f1,f2,adwords_filename,nosales_filename);
}
}
public static void main(String args[])
{
String f="e:/rama.xls";
String s="e:/NoSales.xls";
Nosales v=new Nosales();
try
{
v.distinctValues(f,s);
}
catch(Exception e)
{
System.out.println("Exception due to "+e);
}
}
}
Thanking you,
Regards,
Bertha.
Hi Bertha,
We will check and get back to you soon.
Thank you.
Hi All,
Thank you very much.
Regards,
Bertha.
Hi Bertha,
While trying the code with the attached file rama.xls, I got following exception:
---------------------------------------------------------------------------------
Before query
After query
Exception due to java.sql.SQLException: Invalid column: Status
---------------------------------------------------------------------------------
Would you please check the data in the attachment to make sure it is just the file cause the ArrayIndexOutOfBoundsException you mentioned?
Hi All,
Sorry for the delay.we have changed our code a little bit but again we are getting the same exception as we mentioned earlier.here we are attaching the latest code with the required files.
please check this and get back to us as early as possible.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import com.aspose.cells.Cells;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
public class Five{
Workbook finalworkbook = new Workbook();
Worksheet worksheet2 = finalworkbook.getWorksheets().getSheet(0);
static int index=1;
public void combineReport(String filename,String filename2,String filename3) throws Exception {
double d1=0;
double f1=0;
System.out.println("In combinereport()");
Five d=new Five();
Workbook cbworkbook = new Workbook();
System.out.println("Before opening the cb workbook()");
cbworkbook.open(filename);
Worksheet sheet = cbworkbook.getWorksheets().getSheet(0);
HashSet hs2=adwordsProgram(filename2);
Iterator ik= hs2.iterator();
while(ik.hasNext())
{
String f=(String)ik.next();
System.out.print("\nrama "+f);
String f2=f.substring(0,f.indexOf("@"));
String f3=f.substring(f.indexOf("@")+1,f.indexOf("$"));
String f4=f.substring(f.indexOf("$")+1,f.indexOf("#"));
String f5=f.substring(f.indexOf("#")+1,f.length());
System.out.println("\nf2 is "+f2);
System.out.println("\nf3 is "+f3);
System.out.println("\nf4 is "+f4);
System.out.println("\nf5 is "+f5);
combineReport1(f2,f3,f4,f5,filename,filename2,filename3);
// s.nosalesGrouping(f1,f2,adwords_filename,nosales_filename);
}
}
public void combineReport1(String tid1,String pub1,String Adg,String camp,String filename,String filename2,String filename3)throws Exception
{
double d=0;
double f=0;
Connection c = null;
Statement stmnt = null;
System.out.println("In combinereport1");
worksheet2.getCells().getCell(0,0).setValue("TrackId");
worksheet2.getCells().getCell(0,1).setValue("AdGroup");
worksheet2.getCells().getCell(0,2).setValue("Publisher");
worksheet2.getCells().getCell(0,3).setValue("Cost");
worksheet2.getCells().getCell(0,4).setValue("Amount");
worksheet2.getCells().getCell(0,5).setValue("Campaign");
worksheet2.getCells().getCell(0,6).setValue("P/L");
worksheet2.getCells().getCell(0,7).setValue("P/L%");
Cells worksheet2_cells=worksheet2.getCells();
worksheet2_cells.setColumnWidth(0,10);
worksheet2_cells.setColumnWidth(1,35);
worksheet2_cells.setColumnWidth(2,35);
worksheet2_cells.setColumnWidth(3,8);
worksheet2_cells.setColumnWidth(4,35);
worksheet2_cells.setColumnWidth(5,45);
worksheet2_cells.setColumnWidth(6,10);
worksheet2_cells.setColumnWidth(7,10);
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
System.out.println(" Before url");
String url="jdbc:excel:////"+filename2+" ";
System.out.println(" After url");
System.out.println("Before Connection");
c = DriverManager.getConnection(url, "", "");
System.out.println("After Connection");
stmnt = c.createStatement();
stmnt.setFetchSize(1000);
String s=null;
System.out.println("Before query");
String query = " Select * from Sheet1 where TrackId = '"+tid1+"' and Publisher = '"+pub1+"' and AdGroup = '"+Adg+"' and Campaign='"+camp+"' ;" ;
System.out.println("After query");
ResultSet rs = stmnt.executeQuery( query );
System.out.println("After Exceuting the query");
while(rs.next())
{
d=rs.getDouble("Cost");
System.out.println("the value of d is"+d);
f=d+f;
worksheet2.getCells().getCell(index,0).setValue(tid1);
worksheet2.getCells().getCell(index,1).setValue(Adg);
worksheet2.getCells().getCell(index,2).setValue(pub1);
worksheet2.getCells().getCell(index,3).setValue(d);
worksheet2.getCells().getCell(index,5).setValue(camp);
index++;
}
Cells p=worksheet2.getCells();
int m1=p.getMaxRow();
System.out.println("m1 is "+m1);
int m2=m1+1;
worksheet2.getCells().getCell(m2,0).setValue(" ");
worksheet2.getCells().getCell(m2,1).setValue(" ");
worksheet2.getCells().getCell(m2,2).setValue(" ");
worksheet2.getCells().getCell(m2,3).setValue(f);
int m3=p.getMaxRow();
System.out.println("m3 is "+m3);
int m4=m3+1;
worksheet2.getCells().getCell(m4,0).setValue(" ");
worksheet2.getCells().getCell(m4,1).setValue(" ");
worksheet2.getCells().getCell(m4,2).setValue(" ");
worksheet2.getCells().getCell(m4,3).setValue(" ");
worksheet2.getCells().getCell(m4,4).setValue(" ");
worksheet2.getCells().getCell(m4,5).setValue("");
System.out.println("m4 is index is "+m4);
int m5=m4+1;
index=m5;
try{
finalworkbook.save(filename3,FileFormatType.DEFAULT);
}
catch(Exception e)
{
e.printStackTrace();
}
}
public HashSet adwordsProgram(String filename4)throws Exception
{
HashSet hs1=new HashSet();
int i=0;
Workbook adwordsworkbook = new Workbook();
System.out.println("Before opening the second work book");
System.out.println("filename2 is "+filename4);
adwordsworkbook.open(filename4);
Worksheet worksheet = adwordsworkbook.getWorksheets().getSheet(0);
System.out.println("that is adwords file");
Connection c = null;
Statement stmnt = null;
try
{
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
System.out.println(" Before url");
String url="jdbc:excel:////"+filename4+" ";
System.out.println(" After url");
System.out.println("Before Connection");
c = DriverManager.getConnection(url, "", "");
System.out.println("After Connection");
stmnt = c.createStatement();
stmnt.setFetchSize(1000);
String s=null;
System.out.println("Before query");
String query = " Select * from Sheet1" ;
System.out.println("After query");
ResultSet rs = stmnt.executeQuery( query );
System.out.println("After Exceuting the query");
while(rs.next())
{
String tid=rs.getString("TrackId");
String pub=rs.getString("Publisher");
String Adgroup=rs.getString("AdGroup");
String camp=rs.getString("Campaign");
//System.out.println("RAMA Trackid is "+tid);
//System.out.println("RAMA publisher is "+pub);
//System.out.println("RAMA Adgroup is "+Adgroup);
//System.out.println("RAMA campaign is "+camp);
String finalString=tid+"@"+pub+"$"+Adgroup+"#"+camp;
//System.out.println("srikanth finalString is "+finalString);
hs1.add(finalString);
i++;
}
System.out.println("i is "+i);
int n=hs1.size();
System.out.println("Hash set size is "+n);
}
catch(Exception e)
{
System.out.println("Exception due to "+e);
}
return hs1;
}
public static void main(String[] args)throws Exception {
String f="e:/report2.xls";
String j="e:/CB1.xls";
String s="e:/report3.xls";
Five u=new Five();
u.combineReport(j,f,s);
}
}
Hi All,
previously i attached the report2.xls file.here i am attaching another file which is required for running the program.Both these files will be input to the program.
please refer to it.
Hi Bertha,
Thanks for the template files,
We will check and get back to you soon.
Thank you.
Hi Bertha,
This exception is caused by too many save actions on the same Workbook object. For program and data safety, it is recommend to call save() method only once on one Workbook object when all data have been put into the Workbook object.
For your program, please remove the code for saving workbook("finalworkbook.save(...);") from the method “combineReport1”, and put it into the method “combineReport” just following the end of loop “while(ik….)”.
Hi Bertha,
Please follow the previous advice.
And please try this fix.
Hi All,
It worked fine.You people are really great.Thanks for your support.
Regards,
Bertha Team.