I have a process that exports me the information of a Resultset to a normal excel file that works well with 500 to 600 records, but if they go beyond that number of records, I do not write all the information, that is, it omits a lot of data.
Here is my code for this process:
<%@page contentType="application/vnd.ms-excel"%>
<%@include file="../js/$GLOBAL.jsp"%>
<%
response.setHeader("Content-Disposition", "attachment; filename=\"Exporta.xls\"");
String StRem, StPlz,StFVence="";
StRem = request.getParameter("Rm");
StPlz = request.getParameter("Pl");
String StFIni = request.getParameter("fechaInicial");
String StFFin = request.getParameter("fechaFinal");
String StNCte = request.getParameter("cliente");
String FechaCaptura = "", Nombre = "", CParentesco = "", Tag = "", Attribute1 = "", Identificacion = "", Comentarios = "",
Num_Identificador = "", Num_Visitas = "",Puerta="",VisibleN="",Tipo="",Motivo="",FechaEntrega="",Clave="",CSituacion="",
Motivocliente="";
%>
<table style='font-size:14px;border:1px solid lightgray;border-spacing:6px;border-collapse:collapse;box-shadow:0px 0px 2px #ccc,0px 5px 5px #eee;'>
<thead>
<tr bgcolor='lightgray' ALIGN='CENTER'>
</tr>
</thead><tbody>
<%
try{
int r = 0;
$_Query = "SELECT distinct R.CLIENTE_ID,r.remesa_id,R.ACCOUNT_NUMBER,R.OT,R.DESCRIPCION,to_char(R.FECHA,'dd-mm-yyyy') fecha, RL.CODIGO_QP,RL.CUENTA,RL.CODIGO,RL.DESTINATARIO,RL.CALLE_NUMERO,RL.COLONIA,RL.CIUDAD_MUNICIPIO,"
+ "COALESCE(RL.ESTADO,'') AS ESTADO,RL.CP,COALESCE(RL.TELEFONO,'') AS TELEFONO,COALESCE(RL.DIARIO_ID,0) AS DIARIO_ID,COALESCE(to_char(DLN.FECHA_CREA_DIARIO,'dd-mm-yyyy'),'') fsalida,COALESCE(to_char(DLN.FECHA_CIERRE_DIARIO,'dd-mm-yyyy'),'') frecibo,"
+ "PF.FIRST_NAME || ' ' || PF.LAST_NAME mensajero,LKE.MEANING estatus,substr(HP.PARTY_NAME,1,35) cte,bl.ordered_item nprod,IT.DESCRIPTION prod,lk.attribute1 nplaza,LK.MEANING plaza,case when lk.description = '(L)' then 'Local' "
+ " when lk.description = '(F)' then 'Foraneo' end as agrupacion,bl.attribute13,coalesce(to_char(gps.fecha,'dd-mm-yyyy'),' ') as escaneo,gps.latitud||' '||gps.longitud as datogps,CASE WHEN ORIGEN !=4 THEN 'NO' WHEN ORIGEN = 4 THEN 'SI' END AS SINERGIA "
+ "from XXQP.XXQP_REMESA_TBL r inner join XXQP.XXQP_REMESA_CORTE_HD_TBL rc on R.REMESA_ID = RC.REMESA_ID inner join XXQP.XXQP_REMESA_CORTE_LN_TBL rl "
+ "on RC.CORTE_ID = RL.CORTE_ID left join XXQP.XXQP_DIARIO_HD_TBL dln on RL.DIARIO_ID = DLN.DIARIO_ID "
+ " left join XXQP.XXQP_DIARIO_LN_TBL diario on diario.diario_id= RL.DIARIO_ID "
+ " left join XXQP.XXQP_GPS_TBL gps on gps.gps_id = diario.gps_id inner join AR.HZ_CUST_ACCOUNTS hz on r.cliente_id = HZ.CUST_ACCOUNT_ID inner join AR.HZ_PARTIES hp on HZ.PARTY_ID = HP.PARTY_ID "
+ "inner join APPS.MTL_SYSTEM_ITEMS_TL it on (R.PRODUCTO_ID=it.inventory_item_id and R.ALMACEN_ID=it.organization_id and it.language='ESA') "
+ "inner join apps.oe_blanket_lines_all bl on IT.INVENTORY_ITEM_ID = BL.INVENTORY_ITEM_ID and bl.CONTEXT = 'Operaciones' "
+ "inner join APPLSYS.FND_LOOKUP_VALUES lk on (LK.LOOKUP_TYPE='XXQP_PLAZAS' and LK.LANGUAGE='ESA' and RC.PLAZA_ID = LK.LOOKUP_CODE) "
+ "inner join APPLSYS.FND_LOOKUP_VALUES lkE on (LKE.LOOKUP_TYPE='XXQPN_ESTATUS_FLUJO_GRAL' and LKE.LANGUAGE='ESA' and RL.ESTATUS_ID = LKE.LOOKUP_CODE) "
+ " left join per_assignments_f pa "
+ "on DLN.COLABORADOR_ID = PA.ASSIGNMENT_NUMBER left join APPS.PER_PEOPLE_F pf on PA.PERSON_ID = PF.PERSON_ID WHERE 1=1";
if(!StRem.equals("")){
$_Query = $_Query + " and r.remesa_id in ("+StRem+")";
}
if(StRem.equals("")){
$_Query = $_Query + " and R.FECHA >=TO_DATE('" + StFIni + " 00:00:00','yyyy-mm-dd HH24:MI:SS')"
+ "and R.FECHA <= TO_DATE('" + StFFin + " 23:59:00','yyyy-mm-dd HH24:MI:SS') and rl.estatus!='CANCELADO'";
if(!StNCte.equals("")){
$_Query = $_Query + " and hz.account_number='"+StNCte+"'";
}
}
if (!StPlz.equals("0"))
$_Query = $_Query + " and LK.ATTRIBUTE1='"+StPlz+"'";
$_Query = $_Query + " and rl.estatus!='CANCELADO' order by r.remesa_id";
rs1 = Gdr.consultar($_Query, 2);
System.out.println("Query >>" + $_Query);
while (rs1.next()) {
if (r == 0) {%>
<tr bgcolor='white' ALIGN='CENTER'>
<%
r = 1;
} else {
r = 0;
%>
<tr bgcolor='#E6E6E6' ALIGN='CENTER'>
<% }%>
<td><%=rs1.getString("cliente_id")%></td>
<td><%=rs1.getString("remesa_id")%></td>
<td><%=rs1.getString("account_number")%></td>
<td><%=rs1.getString("ot")%></td>
<td><%=rs1.getString("descripcion")%></td>
<td><%=rs1.getString("fecha")%></td>
<td><%=rs1.getString("codigo_qp")%></td>
<td><%=rs1.getString("cuenta")%></td>
<td><%=rs1.getString("codigo")%></td>
<td><%=rs1.getString("destinatario")%></td>
<td><%=rs1.getString("calle_numero")%></td>
<td><%=rs1.getString("colonia")%></td>
<td><%=rs1.getString("ciudad_municipio")%></td>
<td><%=rs1.getString("estado")%></td>
<td><%=rs1.getString("cp")%></td>
<td><%=rs1.getString("telefono")%></td>
<td><%=rs1.getString("diario_id")%></td>
<td><%=rs1.getString("fsalida")%></td>
<td><%=rs1.getString("frecibo")%></td>
<td><%=rs1.getString("mensajero")%></td>
<td><%=rs1.getString("estatus")%></td>
<td><%=rs1.getString("cte")%></td>
<td><%=rs1.getString("nprod")%></td>
<td><%=rs1.getString("prod")%></td>
</tr>
<% }
Gdr.cerrar();
%>
</tbody>
</table>
<%
} catch (Exception e) {
System.out.println(e.getMessage());
}%>
</tbody>
</table>
And as I tell you, after 600 rows it starts to work badly.