I have the next php code:
$arraylicitaciones=json_decode($_POST['ids']);
$x=0;
$arraydatos=array();
while($x<count($arraylicitaciones))
{
$id=$arraylicitaciones[$x];
$tipo=$arraylicitaciones[$x+1];
if($tipo=='X' || $tipo=='A')
{
$sql="SELECT
T1.id AS COLUMNA_A,
T1.Expediente AS COLUMNA_B,
T1.Organo_Contratante AS COLUMNA_C,
T1.tipo AS COLUMNA_D,
T1.F_Publicacion AS COLUMNA_E,
T1.F_Vencimiento AS COLUMNA_F,
T2.Estado AS COLUMNA_G,
T3.Total_Licitacion_Lote_sVat AS COLUMNA_H,
T3.Importe_Total_Lote_sVat AS COLUMNA_I,
T3.Nro_Orden AS COLUMNA_J,
T3.Nombre_Lote AS COLUMNA_K,
T3.Cotizable AS COLUMNA_L,
T4.Referencia_Organo AS COLUMNA_M,
T4.Denominacion_Organo AS COLUMNA_N,
T4.Unidades_Total AS COLUMNA_O,
T4.Precio_Licitacion_sVat AS COLUMNA_P,
T4.Nombre_Comercial AS COLUMNA_Q,
T4.Forma_Presentacion AS COLUMNA_R,
T4.Referencia_Proveedor AS COLUMNA_S,
T4.Precio_sVat_Unidad_Oferta AS COLUMNA_T,
T4.Codigo_Nacional_Med AS COLUMNA_U,
T4.Porcentaje_Vat AS COLUMNA_V,
T4.adjudicado_competencia AS COLUMNA_W
FROM tesera_expedientes AS T1,tesera_estados_expediente AS T2,tesera_lotes_productos_expedientes AS T3,tesera_productos_expediente AS T4
WHERE T1.id=T3.IdExpediente
AND T3.Id=T4.IdLoteProductos
AND T2.Id=T1.Estado_Expediente
AND T1.id=".$id;
}
if($tipo=='D')
{
$sql="SELECT
CONCAT(T1.id_expediente,'.',T1.id) AS COLUMNA_A,
T1.derivado AS COLUMNA_B,
T1.nombre_cliente AS COLUMNA_C,
'Derivado' AS COLUMNA_D,
T1.fecha_solicitud AS COLUMNA_E,
T1.fecha_vencimiento AS COLUMNA_F,
T2.Estado AS COLUMNA_G,
T3.total_licitacion_lote_sin_iva AS COLUMNA_H,
T3.total_lote_sin_iva AS COLUMNA_I,
T3.nro_lote AS COLUMNA_J,
T3.nombre_lote AS COLUMNA_K,
T3.cotizable AS COLUMNA_L,
T4.referencia_organo AS COLUMNA_M,
T4.denominacion_organo AS COLUMNA_N,
T4.unidades_oferta AS COLUMNA_O,
T4.oferta_am_sin_iva AS COLUMNA_P,
T4.nombre_comercial AS COLUMNA_Q,
T4.forma_presentacion AS COLUMNA_R,
T4.referencia_proveedor AS COLUMNA_S,
T4.oferta_sin_iva AS COLUMNA_T,
T4.codigo_nacional_medicamento AS COLUMNA_U,
T4.tipo_iva AS COLUMNA_V,
T4.adjudicado_competencia AS COLUMNA_W
FROM tesera_derivados AS T1,tesera_estados_expediente AS T2,tesera_lotes_derivados AS T3,tesera_productos_derivados AS T4
WHERE T1.id=T3.id_derivado
AND T3.id=T4.id_lote
AND T2.Id=T1.id_estado_derivado
AND T1.id=".$id;
}
$result= mysql_query($sql);
$lineasdevueltas=mysql_num_rows($result);
if($lineasdevueltas>0)
{
while($row=mysql_fetch_array($result,MYSQL_ASSOC))
{
//Comprobaciones y ediciones de columnas
if($row["COLUMNA_D"]==0)
{
$tipoarray='Expediente';
}
if($row["COLUMNA_D"]==1)
{
$tipoarray='Acuerdo Marco';
}
$fechapubadate=date_create($row['COLUMNA_E']);
$fechavenadate=date_create($row['COLUMNA_F']);
$fechapublicacion=date_format($fechapubadate, "d/m/Y");
$fechavencimiento=date_format($fechavenadate, "d/m/Y");
if($row['COLUMNA_L']==0)
{
$cotiza='NO';
}
else
{
$cotiza='SI';
}
if($row['COLUMNA_W']==0)
{
$adjudicado='NO';
}
else
{
$adjudicado='SI';
}
array_push($arraydatos, array(
"COLUMNA_A" => $row["COLUMNA_A"],
"COLUMNA_B" => $row["COLUMNA_B"],
"COLUMNA_C" => $row["COLUMNA_C"],
"COLUMNA_D" => $tipoarray,
"COLUMNA_E" => $fechapublicacion,
"COLUMNA_F" => $fechavencimiento,
"COLUMNA_G" => $row["COLUMNA_G"],
"COLUMNA_H" => $row["COLUMNA_H"],
"COLUMNA_I" => $row["COLUMNA_I"],
"COLUMNA_J" => $row["COLUMNA_J"],
"COLUMNA_K" => $row["COLUMNA_K"],
"COLUMNA_L" => $cotiza,
"COLUMNA_M" => $row["COLUMNA_M"],
"COLUMNA_N" => $row["COLUMNA_N"],
"COLUMNA_O" => $row["COLUMNA_O"],
"COLUMNA_P" => $row["COLUMNA_P"],
"COLUMNA_Q" => $row["COLUMNA_Q"],
"COLUMNA_R" => $row["COLUMNA_R"],
"COLUMNA_S" => $row["COLUMNA_S"],
"COLUMNA_T" => $row["COLUMNA_T"],
"COLUMNA_U" => $row["COLUMNA_U"],
"COLUMNA_V" => $row["COLUMNA_V"],
"COLUMNA_W" => $adjudicado
));
}
}
$x=$x+2;
}
require_once '../../PHPExcel/Classes/PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$plantilla = '../../template_excel/LicitproductdetalletemplateBIS.xls';
$objPHPExcel = $objReader->load($plantilla);
//------GENERACION EXCEL------//
$posicion=3;
$inicio=3;
$final=1650+2;
for($i=0;$i<1650;$i++)
{
$objeto=$arraydatos[$i];
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$posicion, $objeto[COLUMNA_A])
->setCellValue('B'.$posicion, $objeto[COLUMNA_B])
->setCellValue('C'.$posicion, $objeto[COLUMNA_C])
->setCellValue('D'.$posicion, $objeto[COLUMNA_D])
->setCellValue('E'.$posicion, $objeto[COLUMNA_E])
->setCellValue('F'.$posicion, $objeto[COLUMNA_F])
->setCellValue('G'.$posicion, $objeto[COLUMNA_G])
->setCellValue('H'.$posicion, $objeto[COLUMNA_H])
->setCellValue('I'.$posicion, $objeto[COLUMNA_I])
->setCellValue('J'.$posicion, $objeto[COLUMNA_J])
->setCellValue('K'.$posicion, $objeto[COLUMNA_K])
->setCellValue('L'.$posicion, $objeto[COLUMNA_L])
->setCellValue('M'.$posicion, $objeto[COLUMNA_M])
->setCellValue('N'.$posicion, $objeto[COLUMNA_N])
->setCellValue('O'.$posicion, $objeto[COLUMNA_O])
->setCellValue('P'.$posicion, $objeto[COLUMNA_P])
->setCellValue('Q'.$posicion, $objeto[COLUMNA_Q])
->setCellValue('R'.$posicion, $objeto[COLUMNA_R])
->setCellValue('S'.$posicion, $objeto[COLUMNA_S])
->setCellValue('T'.$posicion, $objeto[COLUMNA_T])
->setCellValue('U'.$posicion, $objeto[COLUMNA_U])
->setCellValue('V'.$posicion, $objeto[COLUMNA_V])
->setCellValue('W'.$posicion, $objeto[COLUMNA_W]);
//------FORMATO SI/NO FINAL------//
if($objeto[COLUMNA_W]=='SI')
{
$objPHPExcel->getActiveSheet()->getStyle('W'.$posicion)->getFill()
->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => 'FFFF00')
));
}
else
{
$objPHPExcel->getActiveSheet()->getStyle('W'.$posicion)->getFill()
->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => 'FFFFCC')
));
}
//++++++FORMATO SI/NO FINAL++++++//
$posicion=$posicion+1;
}
//++++++GENERACION EXCEL++++++//
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$file = '../../ficheros_excel/LicitProducDetalle.xls';
$objWriter->save($file);
$path_php=$_SERVER[HTTP_REFERER];
$path_xls=str_replace('js/Licitaciones.html','ficheros_excel/LicitProducDetalle.xls',$path_php);
?>
<script>
var control=0;
var control2=0;
ele=document.getElementsByTagName('div');
for (i=0;(i<ele.length)&&(control==0);i++){
if((ele[i].style.width == '158px')&&(ele[i].class='x-shadow')){
ele[i].style.display='none';
control2=1;
}
if((ele[i].style.width == '150px')&&(control2==1)){
ele[i].style.display='none';
control=1;
}
}
window.open('<?php echo $path_xls; ?>');
</script>
The query array generated after executing the query returns more than 5000 rows.I want to know,if someone can help,why if i only write 1640 rows with 23 columns each row,the phpexcel generates the .xls file but if I write 1650 rows with 23 columns too the server gives me an error 500(Internal Server Error)
1.) Edit: true.. seems not to be the case with the PHP implementation .. stay with xls
2.) PHPExcel is inefficient when it comes to memory usage. The standard PHP limit is only 100mb of memory - either try increasing the memory and see if it's good enough for you, or move on to other libraries.
consider https://github.com/mk-j/PHP_XLSXWriter for lower memory usage It's incredibly fast and uses little memory - but has no styling support.
Another option is https://github.com/SystemDevil/PHP_XLSXWriter_plus I've never used it but in theory it can support stying and is a fork of the other project, I would give it a go
Check the error logs, check for memory usage or timeout, consider using cell caching to reduce memory usage, or increasing PHPs time limit if you're getting timeouts.
Consider writing the database rows directly to PHPExcel rather than building a large array from the database rows and then iterating over that array to build the spreadsheet.... that is faster and it saves memory.