EXCEL y PHP

[dayanfer]

Hola! por favor necesito ayuda, mi problema es que tengo que enviar los datos del resultado de una consulta en mysql a un archivo excel, esta consulta siempre me devolverá únicamente 6 datos y con ellos debo de aplicar la formula del calculo de la tendencia en excel, el resultado de la formula debo de regresar nuevamente al formulario y guardarlo en una tabla de mysql.

De lo q he investigado en lugar de guardarlo como un .xls deberia de guardarlo como un .cvs pero los .cvs no me permiten utilizar fórmulas, entonces cómo puedo hacer para enviar los datos a un .xls y q este me devuelva este unico valor calculado???

Por favor, me estoy iniciando en php, alguna idea con la q me puedan ayudar??

Gracias de antemano.

[dantrix]

y dime no has intentado hacer el calculo en php?

[andyuruguayo]

Mira no se como ayudarte para que una vez que ingreses los datos al excel los vuelvas a meter al mysql automaticamente... lo que te puedo ayudar es a sacar los datos de la consulta hacia un Excel y aqui esta :

Código PHP:

$tipo_reporte = $_POST['SelectReporte'];
                   $base = "banred" ;
                   $fecha_des = $_POST['FechaDesde'];
                   $fecha_has = $_POST['FechaHasta'];
 

Estos son datos que me llegan a mi de otra pagina, a ti te llegaran otros supongo, son datos traidos de un formulario por el metodo POST (me imagino que esto lo debes saber)

Código PHP:

$shtml = "<table>";
                               // Aqui comienzo a armar una tabla con la consulta
 
        $conn = mysql_connect("localhost","root","3ntradA");
 
        $sql = "select cli.CodCliente, cli.TipoLlamada, cli.Nombre, cli.Telefono, cli.Interno, cont.CodContacto, cont.Fecha, cont.Hora, cont.Asesor, cont.Resultado, cont.Motivo, cont.Observaciones from cliente cli, contacto cont where cli.CodCliente=cont.CodCliente and Fecha between '$fecha_des' and '$fecha_has' and cont.CodContacto in (select max(CodContacto) maximo from contacto group by CodCliente)"; //Aqui tendras que poner tu consulta SQL
 
        $rs = mysql_db_query($base,$sql);
 
        $total_datos = mysql_num_rows($rs);
 
        $i=0; //esta es la variable que usare luego en un iterador
 

Hasta ahora lo unico que he hecho aqui es hacer la consulta SQL para luego comenzar a armar la tabla, y esa tabla es la que exportaremos al EXCEL,

Código PHP:

while($fila = mysql_fetch_array($rs)){
            $CodCliente_[$i] = $fila['CodCliente'];
            $tipo_llamada[$i] = $fila['TipoLlamada'];
            $nombre_[$i] = $fila['Nombre'];
            $telefono_[$i] = $fila['Telefono'];
            $interno_[$i] = $fila['Interno'];
            $codcontacto_[$i] = $fila['CodContacto'];
            $fecha_[$i] = $fila['Fecha'];
            $hora_[$i] = $fila['Hora'];
            $asesor_[$i] = $fila['Asesor'];
            $resultado_[$i] = $fila['Resultado'];
            $motivo_[$i] = $fila['Motivo'];
            $observaciones_[$i] = $fila['Observaciones'];
            $i++;
        }
 

Lo anterior lo unico que hace es almacenar los datos en Arrays usando el iterador que inicializamos en 0.

Código PHP:

$shtml=$shtml."<tr>";
            $shtml=$shtml."<td>CodCliente</td>";
            $shtml=$shtml."<td>TipoLlamada</td>";
            $shtml=$shtml."<td>Nombre</td>";
            $shtml=$shtml."<td>Telefono</td>";
            $shtml=$shtml."<td>Interno</td>";
            $shtml=$shtml."<td>CodContacto</td>";
            $shtml=$shtml."<td>Fecha</td>";
            $shtml=$shtml."<td>Hora</td>";
            $shtml=$shtml."<td>Asesor</td>";
            $shtml=$shtml."<td>Resultado</td>";
            $shtml=$shtml."<td>Motivo</td>";
            $shtml=$shtml."<td>Observaciones</td>";
        $shtml=$shtml."</tr>";
 

Aqui lo que hice fue concatenar a la tabla que comence a formar al principio con los titulos que va a llevar cada columna.

Código PHP:

for($i=0;$i<$total_datos;$i++){
            $shtml=$shtml."<tr>";
                                                $shtml=$shtml."<td>".$CodCliente_[$i]."</td><td>".$tipo_llamada[$i]."</td><td>".$nombre_[$i]."</td><td>".$telefono_[$i]."</td><td>".$interno_[$i]."</td><td>".$codcontacto_[$i]."</td><td>".$fecha_[$i]."</td><td>".$hora_[$i]."</td><td>".$asesor_[$i]."</td><td>".$resultado_[$i]."</td><td>".$motivo_[$i]."</td><td>".$observaciones_[$i]."</td>";
            $shtmo=$shtm."</tr>";   
        }
        $shtml=$shtml."</table>";
 

Aqui lo que hice fue usar el iterador nuevamente desde la posicion 0 hasta la posicion $total_datos (es el total de datos obtenidos de la consulta) y con ello voy armando las diferentes filas con los datos que me tira la consulta.

Código PHP:

$sfile="./Temporal/ConsultaListadoResultadosBanred.xls"; //ruta del archivo a generar 
        $fp=fopen($sfile,"w"); 
        fwrite($fp,$shtml); 
        fclose($fp);
 

Por ultimo genero un archivo en la carpeta con la ruta indicada en $sfile , la abro luego con el fopen y le digo que escriba los datos que concatene en texto (la tabla que generamos) y por ultimo cierro el archivo.

Fijate que la carpeta en la cual guardas el archivo tiene que tener permisos de escritura !!

Cualquier duda a las ordenes !!!

[dayanfer]

Sabes q la persona q debia de explicarme le formula, me dijo q ese calculo es un tanto complicado y q mejor lo intentara de esta manera, haciendolo con excel, pero de lo q he leido al parecer no es tan facil acceder a un archivo .xls. Entonces no sabes si hay algún tipo de solución o q otro camino tomar??

[Twonex]

quieres pasar los datos a un excel y que del excel solitos se vallan a la base de datos?

eso es imposible amigo, por otro lado hasta donde sé los datos que guardas en el excel son como si fueran texto plano, no puedes mandar una formula...

por tanto... todo lo que quieres hacer debes hacerlo en php
y luego exportar a excel como un mero informe!!

[DeeR]

Hola

Segun te entiendo :

1. Quieres generar dinamicamente un xls desde PHP
2. Abrir el xls desde Excel para aplicar una formula a los datos, luego guardar el excel
3. Leer el ultimo excel con los nuevos datos e ingresarlos a una db


De preferencia, preferia estudiar la formula que estás aplicando y realizar el calculo desde el PHP, pero si deseas pasar por excel te pueden servir los siguientes articulos.

1.- Generación de Planillas Excel desde PHP
2.- Leer datos de planillas excel en PHP

Como puedes ver, el primer articulo te ayuda a crear un archivo excel desde php, mientras el segundo te enseña a leer los datos de un excel desde php.

Saludos.

[dayanfer]

Gracias! adapte el código a mis requerimientos y funciona muy bien. Gracias nuevamente, pero mi problema aun sigue siendo el como realizo la formula en excel y traer los datos.

La otra manera q estaba intentando hacer es usar la opcion de Excel de Importar datos (Crear una consulta) implementar la formula y ahi mismo activar la opcion q cada vez q se abre el archivo.xls se actualicen los datos, lo cual he probado y me funciona cargo mi consulta en el query de excel y me extrae los datos, lo que no se es como extraer del excel esa única celda q me devuelve el valor del resultado de la formula.

Por favor, si alguien sabe como puedo hacer esto. Gracias...

disculpen mi torpeza pero en que directorio ejecuto los comandos:
pear install OLE-1.0.0RC1
pear install Spreadsheet_Excel_Writer-0.9.2
que indican en la pagina de 'Generación de Planillas Excel desde PHP'

y no encuentro este directorio /usr/share/php5/php y dice q cambia de acuerdo al S.O.,
tengo wXP cual es el path q debo de buscar?

Gracias.

[dantrix]

Iniciado por dayanfer

disculpen mi torpeza pero en que directorio ejecuto los comandos:
pear install OLE-1.0.0RC1
pear install Spreadsheet_Excel_Writer-0.9.2
que indican en la pagina de 'Generación de Planillas Excel desde PHP'

y no encuentro este directorio /usr/share/php5/php y dice q cambia de acuerdo al S.O.,
tengo wXP cual es el path q debo de buscar?

Gracias.

1) claro en el tuyo debe estar en Archivos de programas o donde instalaste el PHp

2) preferirira que lo hagas en el mismo mysql

si quieres puedes poner la fórmula para apoyarte, ese consumo de memoria y codigo es inproductivo.

Saludos

[dayanfer]

Realmente, no tengo la formula matematica exacta, lo único que me dijeron es que calcule la tendencia a traves de excel porque eso es lo que ellos hacen, meter datos y formula y no saben como se genera, algo asi en excel:

A B C

ANIO MES NUM_PERSONAS
2009 10 1
2009 11 6
2009 12 8
2010 1 0
2010 2 2
2010 3 6
3,476190476 --> Este valor es la tendencia al aplicar =TENDENCIA(C2:C7)

Estos datos representan el numero de personas que han salido en determinado mes y año de cada un local y les sirve para informacion estadistica, y solo se coge los datos de los 6 últimos meses.

[dantrix]

Que travesia....

he buscado información sobre tendencia, minimos cuadrados, etc, pedi ayuda a un amigo que estudia estadistica, me comento:

- La funcion Tendencia es aplicable solo para datos lineales.
- Los datos que muestras son de comportamiento Ciclico.
- La funcion Pronostico tiene casi el mismo comportamiento que la Tendencia

Te sugiero que hagas notar eso a las personas para la cual estas haciendo el sistema.

Te sugeriria que busques en:
http://www.php.net/manual/en/ref.math.php
http://www.php.net/manual/en/book.stats.php

pero esta bien verde encontrar algo, de todas maneras seguire buscando....

me gusta mucho tu caso

Saludos

[Marvin]

Si estas haciendo un excel desde php en formato <table> lo que deberia funcionar seria poner la formula en el td y el rango de valores, algo como esto:

Código:

<td>=SUM(B2:B3)</td>

En donde B2 y B3 son numeros.

Puedes probar con el nombre que tiene la formula en la celda y si esta en español hacer lo siguiente:

Grabar Macro con cualquier nombre,
Copiar la celda que contiene la formula,
Pegar la celda en una celda vacia,
Parar Macro,
y finalmente Editar Macro

En la Macro deberias ver tus acciones y dentro de ellas el nombre que excel comprende como la funcion que necesitas.

De esta manera solo generas el excel una vez y no tienes que volver a abrirlo para que le pongas la funcion.

Suerte!

[DeeR]

Iniciado por dayanfer

Gracias! adapte el código a mis requerimientos y funciona muy bien. Gracias nuevamente, pero mi problema aun sigue siendo el como realizo la formula en excel y traer los datos.

La otra manera q estaba intentando hacer es usar la opcion de Excel de Importar datos (Crear una consulta) implementar la formula y ahi mismo activar la opcion q cada vez q se abre el archivo.xls se actualicen los datos, lo cual he probado y me funciona cargo mi consulta en el query de excel y me extrae los datos, lo que no se es como extraer del excel esa única celda q me devuelve el valor del resultado de la formula.

Por favor, si alguien sabe como puedo hacer esto. Gracias...

disculpen mi torpeza pero en que directorio ejecuto los comandos:
pear install OLE-1.0.0RC1
pear install Spreadsheet_Excel_Writer-0.9.2
que indican en la pagina de 'Generación de Planillas Excel desde PHP'

y no encuentro este directorio /usr/share/php5/php y dice q cambia de acuerdo al S.O.,
tengo wXP cual es el path q debo de buscar?

Gracias.

Hola dayanfer

Creo que te puede ser mas facil bajar directamente el ejemplo que tengo en el articulo Generación de Planillas Excel desde PHP (donde dice 'Download Example + Pear'), ya que en el se encuentran las librerias necesarias.

Leyendo en la red encontre una función que implementa el algoritmo de regresión lineal en PHP.

Llame a la función con los datos que nos entregastes ( TENDENCIA(C2:C7) , considerando a la variable indepediente x como 1 2 3 4 5 6 7) y obtube el mismo resultado 3,476190476

Código PHP:

  <?php
 
/**
 * linear regression function
 * @param $x array x-coords
 * @param $y array y-coords
 * @returns array() m=>slope, b=>intercept
 */
function linear_regression($x, $y) {
 
  // calculate number points
  $n = count($x);
 
  // ensure both arrays of points are the same size
  if ($n != count($y)) {
 
    trigger_error("linear_regression(): Number of elements in coordinate arrays do not match.", E_USER_ERROR);
 
  }
 
  // calculate sums
  $x_sum = array_sum($x);
  $y_sum = array_sum($y);
 
  $xx_sum = 0;
  $xy_sum = 0;
 
  for($i = 0; $i < $n; $i++) {
 
    $xy_sum+=($x[$i]*$y[$i]);
    $xx_sum+=($x[$i]*$x[$i]);
 
  }
 
  // calculate slope
  $m = (($n * $xy_sum) - ($x_sum * $y_sum)) / (($n * $xx_sum) - ($x_sum * $x_sum));
 
  // calculate intercept
  $b = ($y_sum - ($m * $x_sum)) / $n;
 
  // return result
  return array("m"=>$m, "b"=>$b);
 
}
 
$datos = ( linear_regression(array(1, 2, 3, 4,5,6), array(1,6,8,        0 ,2 ,6)) );
 
 
echo $datos["m"] + $datos["b"];
 
 
?>   

Creo que te puede ayudar.

Saludos.