Results 1 to 1 of 1
- 03-28-2011, 11:51 AM #1
Member
- Join Date
- Oct 2009
- Posts
- 8
- Rep Power
- 0
POI excel download problem( urgent )
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 {
Similar Threads
-
Java download problem
By Peter Buttuls in forum New To JavaReplies: 2Last Post: 12-07-2010, 07:03 AM -
Weird download file problem
By bubbless in forum Advanced JavaReplies: 12Last Post: 05-28-2010, 03:14 PM -
Help needed in excel download...
By tester23 in forum Java ServletReplies: 8Last Post: 02-25-2010, 02:58 PM -
Excel Multibyte JDBC Driver / Very Urgent Requirement
By phanindar in forum Advanced JavaReplies: 0Last Post: 11-17-2008, 11:46 AM -
Not able to download excel sheet from client mechine using Struts and JExcel
By muralimohanbikki in forum Advanced JavaReplies: 0Last Post: 06-21-2008, 10:53 AM
Bookmarks