Generar y Leer archivos excel (xls) con KumbiaPHP

Creando un archivo excel en KumbiaPHP

Esto es un tema sencillo, solo hay que implementar las buenas practicas de programación.

Para implementar el tema de las 3 capas veamos un ejemplo sencillo

listado de nombres de usuarios. (id y nombre);

Tabla de usuarios

CREATE TABLE  `usuarios` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Programación MVC

Modelo

Creamos nuestro modelo llamado usuarios.php

<?php
/**
 * Generar y Leer archivos excel (xls) con KumbiaPHP
 * PHP version 5
 * LICENSE
 *
 *
 * Modelo Usuarios
 * 
 * @category app
 * @package models
 * @author Fidel Oyarzo <fidel.oyarzo@gmail.com>
 */
class Usuarios extends ActiveRecord{
	public function findByName($conditions){
		return $this->find("nombre like '$conditions'");
	}
}
?>

Creamos un controlador.

<?php
/**
 * Generar y Leer archivos excel (xls) con KumbiaPHP
 * PHP version 5
 * LICENSE
 *
 *
 * Controlador Usuarios
 * 
 * @category app
 * @package controllers
 * @author Fidel Oyarzo <fidel.oyarzo@gmail.com>
 */
Load::lib('excel');
Load::model('usuarios');
class Usuarios extends ApplicationController{
	public function sendFile(){
                View::template(NULL); //Agregado para que no envié todo el html(Beta2). En Beta1 $this->template=NULL
		View::response('xls');
		$usuarios = new Usuarios();
		if(!$this->usuarios = $usuarios->findByName('%')){
			Flash::warning('No existen registros para exportar');
			$this->render(NULL);
		}
	}
}
?>

Creamos una vista con el nombre sendFile.xls.phtml por que le estamos indicando una salida xls View::response('xls') en beta2 y en beta1 $this->set_response('xls')

<?php
/**
 * Generar y Leer archivos excel (xls) con KumbiaPHP
 * PHP version 5
 * LICENSE
 *
 *
 * Vista sendFile
 * @category app
 * @package views
 * @author Fidel Oyarzo <fidel.oyarzo@gmail.com>
 */

//invoco la clase para generar el libro excel
$libro = new Spreadsheet_Excel_Writer();

//creo una hoja, es decir, puedo crear N hojas
$hoja1 = $libro->addWorksheet("Hoja1");

//charset
$hoja1->setInputEncoding('utf-8');

//Formato de letra, en este caso negrita, existen más, números, fecha, etc...
$negrita =& $libro->addFormat();
$negrita->setBold();

//titulos
$hoja1->write(0, 0, "ID", $negrita);
$hoja1->write(0, 1, "NOMBRE", $negrita);

//detalle
$row = 1;
foreach($usuarios as $usuario):
    $hoja1->write($row, 0, $usuario->id);
    $hoja1->write($row, 1, $usuario->nombre);
    $row++;
endforeach;
$libro->send("Libro1.xls");
$libro->close();
?>

Con esto tenemos un ejemplo sencillo y bien hecho, no deben olvidar que una programacion MVC les ayudara con el tiempo a realizar mantenciones y en este caso hacer otra salida que no sea un excel, por ejemplo un pdf, solo hay que crear una vista sin tocar el controlador ni el modelo.

El modelo debe tener contacto con la base de datos. el controlador solo envia esos datos a la vista, pero ni uno de los dos deben estar en contacto con la base de datos.

Importando un archivo excel en KumbiaPHP

Esta segunda parte vamos a tomar un archivo similar al que exportamos para poder poblar nuestraba base, aplicaremos la misma libreria.

la forma de implementar este tema es primero subir el archo xls al servidor, para esto se debe dejar en una carpeta que tenga acceso de lectura y escritura. despues que el archivo este disponible en el servidor, leeremos el excel y mostraremos los primeros 10 registros, para que el usuario sepa que el archivo que va a importar es el correcto. y con un boton finalizar importremos los datos que se almaceno en memoria (array), yo lo implemento de esta manera por varias razones, y una que es la mas importante, la rapidez, es mas rápido poblar un array en vez estar poblando directo a la base, y una vez que el usuario este conforme poble ese array a la base. También es bueno utilizar las transacciones de la base, por que puede ocurrir un error, y seria bueno hacerlo desde cero, en vez que este buscando cuantos registro quedaron.

utilizaremos el mismo controller usuarios.

con el control FILE subimos un archivo al servidor. para esto utilizamos una vista, llama readFile.phtml

<?php
/**
 * Generar y Leer archivos excel (xls) con KumbiaPHP
 * PHP version 5
 * LICENSE
 *
 *
 * Vista readFile
 * @category app
 * @package views
 * @author Fidel Oyarzo <fidel.oyarzo@gmail.com>
 */
?>
<?php View::content() ?>
<?php echo form_tag("$controller_name/$action_name", "enctype: multipart/form-data")?>
<table>
  <tr>
    <th>ARCHIVO</th>
    <th>&nbsp;</th>
  </tr>
  <tr>
    <td><?php echo file_field_tag('file') ?></td>
    <td><?php echo submit_tag('Leer Excel')?></td>
  </tr>
</table>
<?php echo end_form_tag() ?>

en el controller con la accion firstStep subimos el excel y creamos el array

/**
 * Subimos el archivo excel y creamos un array con los indices
 * para ser insertados más tarde en la base
 **/
public function firstStep(){
	$new_name		=	"pagos.xls";
	$dir			=	"temp";
	if(!$this->upload_file("file",$dir,$new_name)){
		Flash::success("Error al intentar de subir el archivo");
	}else{
		error_reporting(E_ALL ^ E_NOTICE);
		Session::unset_data('array_pagos');
		$reader	=	new Spreadsheet_Excel_Reader();     
		$reader->setUTFEncoder('UTF8');
		$reader->setOutputEncoding('UTF­8');
		$reader->read("$dir/$new_name");
		for ($i = 2; $i <= 65535; $i++){// EMPEZAMOS A LEER DESDE LA SEGUNDA LINEA 'POR QUE LA 1° SON LOS TITULOS'
			$array_usuarios[]	= 	array(
							'id'		=>	$reader->sheets[0]['cells'][$i][1],
							'nombre'	=>	$reader->sheets[0]['cells'][$i][2]
							);	
			$c=$i+1;
			if($reader->sheets[0]['cells'][$c][1]=="") $i = 65535;	//LEEMOS HASTA EL FINAL, PERO SI ENCUENTRA UN ESPACIO BASIO TERMINA DE LEER
		}
		Session::set('array_usuarios', $array_usuarios);		
	}
}

vista firstStep.phtml, muestra siempre los primeros 10 registros. En una tabla.

<?php
/**
 * Generar y Leer archivos excel (xls) con KumbiaPHP
 * PHP version 5
 * LICENSE
 *
 *
 * Vista firstStep
 * @category app
 * @package views
 * @author Fidel Oyarzo <fidel.oyarzo@gmail.com>
 */
?>

<table>
<tr>
	<th>ID</th>
	<th>NOMBRE</th>
</tr>
<?php for($i=0;$i<10;$i++){?>
<tr>
	<td><?php echo $array_usuarios[$i]['id'] ?></td>
	<td><?php echo $array_usuarios[$i]['nombre'] ?></td>
</tr>
<?php } ?>
</table>
<?php 
	echo form_tag("$controller_name/insertArray");
	echo submit_tag("Agregar");
	echo end_form_tag();
?>

insertamos los datos a la base.

Controlador usuarios_controller.php

/**
 * invocamos el modelo para que
 * insertamos los datos del array a la base de datos
 */
public function insertArray(){
	$this->render(NULL);
	$usuarios		= 	new Usuarios();
	$usuarios->insertArray();
}

Modelo usuarios.php

/**
 * Recorremos el array para insertar en la base.
 **/
public function insertArray(){
	$array_usuarios		=	Session::get("array_usuarios");
	$tamano			=	count($array_usuarios);
	$this->sql('BEGIN');
	for($i=0;$i<$tamano;$i++){
		$this->nombre	=	$array_usuarios[$i]['nombre'];
		if(!$this->create()){
			Flash::error("Error registro $i");
		}
	}
	$this->sql('COMMIT');
	Session::unset_data("array_usuarios");
}

PD: es mejor utilizar el create en vez del save, cuando uno debe insertar muchos registros el tiempo de respuesta es más rápido.

Errores Conocidos

Notice: Uninitialized string offset: 2199023255040 in /../../oleread.inc

La solución es remplazar el contenido de la función GetInt4d de la siguiente manera.

function GetInt4d($data, $pos){
  $_or_24 = ord($data[$pos+3]);
  if ($_or_24>=128)
    $_ord_24 = -abs((256-$_or_24) << 24);
  else
    $_ord_24 = ($_or_24&127) << 24;
  return ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | $_ord_24;
}