Hi All,

I am new to java.

I am working excel download using POI.

I addded all jar file.

while downloading I am getting error , excel can not handle more than 255 columns. It is ardding the rows ( records ) in same row instead of adding in vertical way . I mean it is not appending in excel.

pls let me know what is the problem?

my code is as below.

try{

Statement st = connection.createStatement();
ArrayList cells = new ArrayList();
String sql = "select REQUISITIONS.FOLDERID,REQUISITIONS.STATUS,REQUISIT IONS.AUTOREQ, REQUISITIONS.MANAGERID, REQUISITIONS.RECRUITERID, "+
" REQUISITIONS.JOBTITLE,REQUISITIONS.JOBCODE "+
" from REQUISITIONS "+
" where REQUISITIONS.STATUS ='Open' "+
" and REQUISITIONS.AUTOREQ ='254BR' or REQUISITIONS.AUTOREQ ='253BR'";
System.out.println(sql);
ResultSet rs = st.executeQuery(sql);
ArrayList<ExcelTemplate> etList = new ArrayList<ExcelTemplate>();
ArrayList<ExcelTemplate> etList1 = new ArrayList<ExcelTemplate>();
ArrayList<ExcelTemplate> etList2 = new ArrayList<ExcelTemplate>();
int count=0;

while(rs.next())
{ count++;
System.out.println("Counter"+count);
System.out.println(":kishan test Folder Id " +rs.getString(1));
System.out.println(":kishan test Req Number " +rs.getString(3));
System.out.println(":kishan test Manager Id " +rs.getString(4));
System.out.println(":kishan test Recruiter Id " +rs.getString(5));

ExcelTemplate et = new ExcelTemplate();


et.setFolderId(rs.getString(1));
et.setReqStatus(rs.getString(2));
et.setRequisition_Number(rs.getString(3));
et.setMangerId(rs.getString(4));
et.setManagerRecruiter(rs.getString(5));
et.setBusinessTitle(rs.getString(6));
et.setSAPTitle(rs.getString(6));
et.setJobCode(rs.getString(7));

etList.add(et);
etList1.add(et);
}
rs.close();



for(ExcelTemplate et : etList){


String select1 = "select * from REQUISITIONRESPONSE where FOLDERID=?";
PreparedStatement ps1 = connection.prepareStatement(select1);
ResultSet rs1 = null;

System.out.println(":kishan test data in second Folder id is " +et.getFolderId());
ps1.setString(1,et.getFolderId());
rs1 = ps1.executeQuery();

while(rs1.next())
{

et.setQuesTypeId(rs1.getString(4));

String select = "select * from QUESTION_TYPE where QUESTION_TYPE_ID=?";
PreparedStatement ps2 = connection.prepareStatement(select);
ResultSet rs2 = null;


ps2.setString(1,et.getQuesTypeId());
rs2 = ps2.executeQuery();
System.out.println(":kishan test data in second Question Id is " +et.getQuesTypeId());
while(rs2.next())
{


if (rs2.getString(5).equals("Cost_Center")){


et.setCostCenter(rs1.getString(6));
System.out.println(":kishan test data in second Cost Center " +rs1.getString(6));
}
else if (rs2.getString(5).equals("BG")){

et.setBusinessGroup(rs1.getString(6));
System.out.println(":kishan test data in second BG " +rs1.getString(6));
}
else if (rs2.getString(5).equals("BU")){

et.setBusinessUnit(rs1.getString(6));
System.out.println(":kishan test data in second BU " +rs1.getString(6));
}
else if (rs2.getString(5).equals("Site_Code")){

et.setSiteCode(rs1.getString(6));
System.out.println(":kishan test data in second Site Code " +rs1.getString(6));
}
else if (rs2.getString(5).equals("Grade")){

et.setGrade(rs1.getString(6));
System.out.println(":kishan test data in Grade " +rs1.getString(6));
}
// etList.add(et);
etList1.add(et);
}
rs2.close();
ps2.close();

} rs1.close();
ps1.close();

String select3 = "select * from HR_STATUS_MAIN where FOLDERID=? ";
PreparedStatement ps3 = connection.prepareStatement(select3);
ResultSet rs3 = null;


ps3.setString(1,et.getFolderId());
rs3 = ps3.executeQuery();
System.out.println(":kishan test data in third Folder id is " +et.getFolderId());
while(rs3.next())

{
et.setHrStatus(rs3.getString(3));
et.setCanType(rs3.getString(9));
et.setResumeKey(rs3.getString(1));




ResultSet rs4 = null;
String select4 = "select * from CANDIDATE where RESUMEKEY=? ";
PreparedStatement ps4 = connection.prepareStatement(select4);


ps4.setString(1,et.getResumeKey());
rs4 = ps4.executeQuery();

while(rs4.next()){

et.setFirstName(rs4.getString(13));
et.setMiddleName(rs4.getString(21));
et.setLastName(rs4.getString(18));

} ps4.close();
rs4.close();



} ps3.close();
rs3.close();

String select5 = "select * from USERS where EMPLOYEEID=? ";
PreparedStatement ps5 = connection.prepareStatement(select5);
ResultSet rs5 = null;


ps5.setString(1,et.getMangerId());
rs5 = ps5.executeQuery();

while(rs5.next())

{
et.setManagerFname(rs5.getString(4));
et.setManagerLname(rs5.getString(7));
et.setManagerUsername(rs5.getString(11));


} ps5.close();
rs5.close();
// 4 querys
}


// etList = RemoveDuplicates( etList);
//
for(ExcelTemplate et:etList){
System.out.println(":kishan test data end " +et.getFolderId());
System.out.println(":kishan test data end " +et.getQuesTypeId());
System.out.println(":kishan test data end " +et.getGrade());
System.out.println(":kishan test data end " +et.getCanType());
System.out.println(":kishan test data end " +et.getHrStatus());
cells.add(et.getFolderId());
cells.add(et.getRequisition_Number());
cells.add(et.getBusinessTitle());
cells.add(et.getSiteCode());
cells.add(et.getMangerId());
cells.add(et.getManagerUsername());
cells.add(et.getManagerFname());
cells.add(et.getManagerRecruiter());
cells.add(et.getGrade());
cells.add(et.getJobCode());
cells.add(et.getSAPTitle());
cells.add(et.getCostCenter());
cells.add(et.getBusinessGroup());
cells.add(et.getBusinessUnit());

cells.add(et.getFirstName());
cells.add(et.getMiddleName());
cells.add(et.getLastName());
cells.add(et.getAStartDate());
cells.add(et.getPendImg());
cells.add(et.getPayType());
cells.add(et.getRehireType());
cells.add(et.getCurrentCanType());
cells.add(et.getOriginalCanType());

cells.add(et.getReqStatus());
cells.add(et.getHrStatus());


}

data.add(cells);


//// //Export
exportToExcel("Test", headers, data, file123);
//
}
catch(Exception e){
System.out.println(e.getMessage().toString());
}finally {