CODEWITHSUNDEEP
javaxls
Hamad
Hamad

Reputation: 383

Not writing to new xls sheet using Java

I have a java app where it’s writing sql statement output from database to an excel file(xls) So when it reaches a the limited number of row that xls has which is around 65000 then it writes to a new sheet.

This was done previously where it was able to write to second sheet, but when adding the new sheet(sheet4) it’s not working.

I’m using JXL library and error I’m getting is exceed maximum number of rows, so the two sheets are full but when adding the third it’s not writing to it

Below is the code.

rs5 is nothing but an sql statement.


WritableSheet sheet2 = workbook.getSheet("ServiceSummary");
                WritableSheet sheet3 = workbook.getSheet("ServiceSummary2");
                WritableSheet sheet4 = workbook.getSheet("ServiceSummary3"); // added third sheet to handle more rows
 
                       
                     // Added sheet4 and if else statement to handle max row exception 65000
                        
                        System.out.println("number of rows in xls" + pos );
                       
                        
                        
                        if (pos > 130000)
                        {
                              System.out.println("third sheet for ServiceSummary3");
                               while(rs5.next()){
                                     sheet4.addCell(new Label(0,1+(counter), customerAccount, arial10format));
                                     sheet4.addCell(new Label(1,1+(counter), rs5.getString(1), arial10format));
                                     sheet4.addCell(new Label(2,1+(counter), rs5.getString(2), arial10format));
                                     sheet4.addCell(new Label(3,1+(counter), rs5.getString(3), arial10format));
                                     sheet4.addCell(new Label(4,1+(counter), rs5.getString(4), arial10format));
                                     sheet4.addCell(new Label(5,1+(counter), rs5.getString(5), arial10format));
                                     sheet4.addCell(new Label(6,1+(counter), rs5.getString(6), arial10format));
                                     sheet4.addCell(new Label(7,1+(counter), rs5.getString(7), arial10format));
                                 try{sheet4.addCell(new Number(8,1+(counter), Double.parseDouble(rs5.getString(8)), arial10format));}catch(Exception e){sheet4.addCell(new Label(8,1+(counter), rs5.getString(8), arial10format));}
                                 try{sheet4.addCell(new Number(9,1+(counter), Double.parseDouble(rs5.getString(9)), arial10format));}catch(Exception e){sheet4.addCell(new Label(9,1+(counter), rs5.getString(9), arial10format));}
                                 try{sheet4.addCell(new Number(10,1+(counter), Double.parseDouble(rs5.getString(10)), arial10format));}catch(Exception e){sheet4.addCell(new Label(10,1+(counter), rs5.getString(10), arial10format));}
                                 counter ++;
                                 pos++;
                             }
                             
                        }
                       
                        else if (pos >= 65000)
                        {
                               
                               while(rs5.next()){
                                 sheet3.addCell(new Label(0,1+(counter), customerAccount, arial10format));
                                 sheet3.addCell(new Label(1,1+(counter), rs5.getString(1), arial10format));
                                 sheet3.addCell(new Label(2,1+(counter), rs5.getString(2), arial10format));
                                 sheet3.addCell(new Label(3,1+(counter), rs5.getString(3), arial10format));
                                 sheet3.addCell(new Label(4,1+(counter), rs5.getString(4), arial10format));
                                 sheet3.addCell(new Label(5,1+(counter), rs5.getString(5), arial10format));
                                 sheet3.addCell(new Label(6,1+(counter), rs5.getString(6), arial10format));
                                 sheet3.addCell(new Label(7,1+(counter), rs5.getString(7), arial10format));
                                 try{sheet3.addCell(new Number(8,1+(counter), Double.parseDouble(rs5.getString(8)), arial10format));}catch(Exception e){sheet3.addCell(new Label(8,1+(counter), rs5.getString(8), arial10format));}
                                 try{sheet3.addCell(new Number(9,1+(counter), Double.parseDouble(rs5.getString(9)), arial10format));}catch(Exception e){sheet3.addCell(new Label(9,1+(counter), rs5.getString(9), arial10format));}
                                 try{sheet3.addCell(new Number(10,1+(counter), Double.parseDouble(rs5.getString(10)), arial10format));}catch(Exception e){sheet3.addCell(new Label(10,1+(counter), rs5.getString(10), arial10format));}
                                 counter ++;
                                 pos++;
                             }
                             
                        }
                       
                        else
                         
                        {
                        
                        while(rs5.next()){
                            sheet2.addCell(new Label(0,1+(pos), customerAccount, arial10format));
                            sheet2.addCell(new Label(1,1+(pos), rs5.getString(1), arial10format));
                            sheet2.addCell(new Label(2,1+(pos), rs5.getString(2), arial10format));
                            sheet2.addCell(new Label(3,1+(pos), rs5.getString(3), arial10format));
                            sheet2.addCell(new Label(4,1+(pos), rs5.getString(4), arial10format));
                            sheet2.addCell(new Label(5,1+(pos), rs5.getString(5), arial10format));
                            sheet2.addCell(new Label(6,1+(pos), rs5.getString(6), arial10format));
                            sheet2.addCell(new Label(7,1+(pos), rs5.getString(7), arial10format));
                            try{sheet2.addCell(new Number(8,1+(pos), Double.parseDouble(rs5.getString(8)), arial10format));}catch(Exception e){sheet2.addCell(new Label(8,1+(pos), rs5.getString(8), arial10format));}
                            try{sheet2.addCell(new Number(9,1+(pos), Double.parseDouble(rs5.getString(9)), arial10format));}catch(Exception e){sheet2.addCell(new Label(9,1+(pos), rs5.getString(9), arial10format));}
                            try{sheet2.addCell(new Number(10,1+(pos), Double.parseDouble(rs5.getString(10)), arial10format));}catch(Exception e){sheet2.addCell(new Label(10,1+(pos), rs5.getString(10), arial10format));}
                            pos++;
                        }}
                      
                        rs5.close();
                        rs5 = null;
                    }
                }

Upvotes: 0

Views: 62

Answers (1)

tnavidi
tnavidi

Reputation: 1418

You need to reset counter to 0 when starting the 3rd sheet. As it is now sheet 3 will start on row 65000 and going down towards 65535

Upvotes: 1

Related Questions