Yes Tim.... really bad code.
I will bring it back to servlet. Here my problem is that how to append the 1000, 1000 records to the already existing excel. means first it fetches 1 to 1000 records from database and inserts to excel and again it comes back fetches 1000 to 2000 records and should append from row 1001 in the existing excel.
For a while below is the code in jsp transfering data to excel.
Hashtable htDataToExport = null;
try{
response.setHeader("Content-Disposition","attachment; filename=\"DetailReport.xls\"");
htDataToExport = (Hashtable)request.getAttribute("ResultObject");
String strException = (String)request.getAttribute("EXCEPTION");
int iFlag = 1;
String strDtFrom = "";
%>
<HTML>
<title> Details Report</title>
<SCRIPT>
function init()
{
<%
if(strException != null)
{
%>
alert('<%= strException %>');
<%
}
%>
return false;
}
</SCRIPT>
<BODY onLoad="init();">
<form name="frmMain">
<%
if(htDataToExport != null && htDataToExport.size() > 0){
strDtFrom = (String)htDataToExport.get("fromDate");
}
%>
<TABLE>
<TR>
<TD>
<B>Detailed Collection </B>
</TD>
<TD ALIGN="RIGHT">
<B>Date : </B>
</TD>
<TD>
<B><%=JspUtil.checkNull(strDtFrom)%> </B>
</TD>
</TR>
<TR></TR>
</TABLE>
<table border=1 cellpadding=0 cellspacing=0 width="100%">
<TR>
<TD><B>Depositing Location</B></TD>
<TD><B>Sun GL Code</B></TD>
<TD><B>Instrument Category</B></TD>
<TD><B>Instrument Type</B></TD>
<TD><B>Instrument Creation ID</B></TD>
<TD><B>Instrument Creation Date</B></TD>
<TD><B>Instrument Receipt Date</B></TD>
<TD><B>Collection Deleted ? (Y/N)</B></TD>
<TD><B>Policy Number</B></TD>
<TD><B>Instrument Number</B></TD>
<TD><B>Instrument Date</B></TD>
<TD><B>MICR Code</B></TD>
<TD><B>Bank Name</B></TD>
<TD><B>Bank Branch</B></TD>
<TD><B>Clearance Type</B></TD>
<TD><B>Instrument Amount</B></TD>
<TD><B>PA Posted (Y/N)?</B></TD>
<TD><B>Collection Rejected (Y/N)?</B></TD>
<TD><B>PA Number</B></TD>
<TD><B>PA Date</B></TD>
<TD><B>
RTF Number</B></TD>
<TD><B>RTF Date</B></TD>
<TD><B>RTF Posted (Y/N) ?</B></TD>
<TD><B>RTF Rejected (Y/N)?</B></TD>
<TD><B>RTF Cancelled (Y/N)?</B></TD>
<TD><B>Bank Pickup Date</B></TD>
<TD><B>Depositing Bank</B></TD>
<TD><B>Credit Date</B></TD>
<TD><B>Bounce Date</B></TD>
<TD><B>Reason of Bounce</B></TD>
<TD><B>Recovered Date</B></TD>
<TD><B>PA Minus Receipt Date</B></TD>
<TD><B>RTF Date Minus PA Date</B></TD>
<TD><B>Bank Pickup Date Minus RTF Date</B></TD>
<TD><B>Credit Date Minus Bank Pickup Date</B></TD>
<TD><B>Bounce Date Minus Credit Date</B></TD>
<TD><B>Transfer to Ingenium Date</B></TD>
<TD><B>Service Agent ID</B></TD>
<TD><B>Service Agent Name</B></TD>
<TD><B>Payment Reason</B></TD>
</TR>
<tr>
<%
if(htDataToExport != null && htDataToExport.size() > 0){
Hashtable htData = (Hashtable)htDataToExport.get("ExcelExport");
for(int i=0;i<htData.size();i++){
String[] arrRowData = (String[])htData.get("Row"+(i+1));
if(iFlag == 2){
%>
<tr>
<%
iFlag = 1;
}
else{%>
<tr style="background-color:grey">
<%
iFlag = 2;
}
for(int j=0;j<arrRowData.length;j++){
%>
<td> <%=JspUtil.checkNull(arrRowData[j])%> </td>
<%
}
%>
</tr>
<%
}
}
else{
%>
<tr> <td> No Data Available </td> </tr>
<%
}
}
catch(Exception ex){
out.println(ex.getMessage());
}
htDataToExport.clear();
GregorianCalendar gcBusinessDate = DateUtil.getBusinessDate();
%>
</table>
<table>
<TR></TR>
<TR></TR>
<tr>
<td ALIGN="RIGHT"><B>Generated on </B></TD>
<td></td>
<td ALIGN="RIGHT"><B>Date : </B></TD>
<td><%=DateUtil.retStrDate(gcBusinessDate)%></td>
<td ALIGN="RIGHT"><B>Time : </B></TD>
<td ALIGN="LEFT"><%=gcBusinessDate.HOUR%>:<%=gcBusinessDate.MINUTE%>:<%=gcBusinessDate.SECOND%></td>
<td ALIGN="RIGHT"><B>Verified By : </B></TD>
<td></td>
<td ALIGN="RIGHT"><B>Approved By : </B></TD>
<td></td>
</TR>
<TR></TR>
<TR><TD Colspan="9"><B>Information contained herein is confidential and for internal use and may be used only for business purposes authorized by xxxxxxxxxxxx</B></TD>
</TR>
</TABLE>
</form>
</BODY>
</HTML>