Thread: Need Help Speeding This Up

Ok, I am using SmartXLS to write excel sheets and I have one group of loops that takes a rediculous amount of time to run. Much more so than I would expect. Here is the code for my current set of loops (sorry for the indenting).

System.out.println("Passed Check 4");
				c++;
				workBook.setText(r,c,tempName);
				r++;
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Rev Pax per Dept");
					workBook.setText(r,c,"Rev Pax per Dept");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Avg Conx Pax");
					workBook.setText(r,c,"Avg Conx Pax");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Avg Local Pax");
					workBook.setText(r,c,"Avg Local Pax");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Total Depts");
					workBook.setText(r,c,"Total Depts");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Depts per Day");
					workBook.setText(r,c,"Depts per Day");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Total Revenue");
					workBook.setText(r,c,"Total Revenue");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Revenue per Dept");
					workBook.setText(r,c,"Revenue per Dept");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Yield");
					workBook.setText(r,c,"Yield");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"RASM");
					workBook.setText(r,c,"RASM");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Avg Fare");
					workBook.setText(r,c,"Avg Fare");
					r++;
				}
				for(int y=0;y<7;y++)
				{
					if(y==0)
						workBook.setText(r,c+1,"Load Factor");
					workBook.setText(r,c,"Load Factor");
					r++;
				}
				workBook.setColWidthAutoSize(c,true);
				workBook.setColWidthAutoSize(c+1,true);
				System.out.println("Passed Check 5");

A few notes:
Regarding SmartXLS: The workBook is not written to until the very end of my method (well after this), so there is currently no IO things happening here. Later on in the method, it uses the setText(int,int,String) method some 21600 times, and faster than I can realize it did anything.


It has to run this set of loops about 300 times, and this set of loops currently take a good 35 seconds to run.


If anyone can help me speed this up, I would be very appreciative. Also, if anyone knows why it's taking so long, that would be great too.

Are you sure that the loops shown take the time? Time the loop's execution time by using calls to the System.currentTimeMillis() method.

Originally Posted by Norm

Are you sure that the loops shown take the time? Time the loop's execution time by using calls to the System.currentTimeMillis() method.

Well I suspect I found the issue. If I'm correct, the issue isnt with the loops but the two lines after it.

It would seem that:

workBook.setColWidthAutoSize(c,true);
workBook.setColWidthAutoSize(c+1,true);

takes forever. Fortunately, I can tell it just to do this the first time since the columns only need to be adjusted the first time around.

After I fixed that, it adds a few columns of and calculates data for one workbook, it reads 21600 cells, reads another 21600 cells, adds a column of data and sometimes rows to 16 sheets in another workbook, and copies the previous workbook into another workbook and creates 300 printable sheets with the data from the other sheets and formate correct, all in about 51 seconds.

Not too bad.

buy more ram, an ssd and a better cpu