Exportando a Excel con OLE

Por Roberto Martínez
© Copyrights 1997 by FoxPress, All rights reserved
FoxPress, Marzo 1997

En el tratamiento de la información no es infrecuente que los clientes quieran poder manipular de forma interactiva todo el conjunto de datos que se guarda en nuestra aplicación.

Los clientes que se manejan bastante bien con Excel y con Power Point quieren hacer una serie de informes actualizados basándose en las tablas de la aplicación. Muchas veces esos informes superan los report que tienen diseñados y no es infrecuente que rechacen el aprendizaje de hacer reports con el Fox y prefieran trabajar con su Excel.

Nuestra aplicación tiene, por su parte, que controlar los datos e impedir el acceso a los mismos por parte de ‘elementos extraños’. Esta misión, más que ser algo propiamente de la aplicación, sería una competencia del Servidor de Ficheros que impediría la entrada en los directorios donde se guarda nuestra información.

No obstante, la aplicación podría traspasar la información de las tablas a un directorio de acceso público del que los ejecutivos podrían sacar la información para poder trabajar.

Estaríamos en la antesala de lo que sería el Almacén de Datos (Data Warehousing), un lugar en el que los datos se amontonan para que sean utilizados por los usuarios según sus criterios.

El almacén de datos reune un conjunto dispar, sin filtrar, de información y al usuario corresponde el presentarlos de forma creativa y atrayente. A medida que maduran las herramientas para capturar y explorar datos detallados, también desarrollamos nuestra capacidad para sacar partido a la información recopilada.

Con Visual FoxPro puedes pasar tus datos a Excel mediante el uso de dos órdenes:

COPY TO <Nombre Fichero> TYPE XL5

o con la orden

EXPORT TO <Nombre Fichero> XL5

Estas dos órdenes son suficientes en muchos casos pero tienen la desventaja de que son bastante genéricas y no tienen en cuenta el tipo de dato que se está traspasando, las cabeceras, el tamaño, la presentación y un largo etcétera.

Aunque en un principio era reacio a usar OLE, últimamente y como las máquinas están mejorando sustancialmente, este recurso de Microsoft empieza a ser cada vez más últil y además podríamos usar todas las ventajas de Excel desde nuestra aplicación FoxPro.

Con la orden

olExcel = CREATEOBJECT ("Excel.Sheet")

crearíamos la instancia y a continuación vendría la tarea de traspasar los datos y luego mejorar su presentación.

A la hora de traspasar los datos intenté usar el mismo sistema que se sigue en uno de los ejemplos que vienen con VFP en el directorio samples/solution, concretamente oleaut1.scx:

SELECT city, count(city) ;
FROM (sys(2004) + ;
"samples\data\testdata!customer");
GROUP BY city ;
HAVING COUNT(city) > 1 ;
INTO array cust_distrib

objXLsheet = crea("Excel.Sheet")
objXLsheet.application.visible = .T.

for i = 1 to _TALLY
objXLsheet.Cells(i,1).Value = ;
cust_distrib(i,1)

objXLsheet.Cells(i,2).Value = ;
cust_distrib(i,2)

endfor

El sistema es válido y funciona correctamente pero es extremadamente lento porque va cargando cada una de las celdas de Excel. Si la tabla es muy pequeña no hay problema, pero si la tabla es grande tarda un tiempo que es inaceptable.

Viendo que ese sistema no me era válido pero que tampoco estaba dispuesto a dejar de usar el OLE pues durante el tiempo que estuve haciendo pruebas me di cuenta de lo impresionante que es el uso de las posibilidades que se me abren, empecé a ver de qué forma podía usar la rapidez del export to con la brillantez y elegancia del OLE.

La solución la encontré en pasar los datos con el export to y después manipularlos con OLE para que me quedaran bien presentados.

El código lo pongo a continuación. Este código está preparado para trabajar con cualquier tipo de tablas, ya pertenezcan a una Base de Datos o no y según el tipo de campo centra y mejora la presentación de la información.

Al final te sale una ventana de advertencia de Excel preguntando si quieres grabar todos los cambios realizados.

También todos los resultados se graban en un fichero que en este caso siempre es el mismo.

*************************************

LPARAMETERS lcTabla, llEsLibre
LOCAL loExcel, lcArea, llCerrar,;
lcFichero, lnCoincidencias, ;
lnCampo, lcTitCampo, lnAnchoCol,;
lnError, lcColumna, lcRutinaErr

*-- lcTabla: Parámetro cadena obligatorio
*-- que contiene el nombre de la
*-- tabla a traspasar al Excel

*-- llEsLibre: Parámetro booleano
*-- obligatorio en el que se
*-- pasará .t. si se quiere
*-- exportar una tabla libre y
*-- .f. si pertenece a una base
*-- de datos
*-- loExcel:Objeto ole de tipo Application
*-- excel
*-- lcArea: Area actual seleccionada para
*-- restaurarla al final
*-- llCerrar:Booleano para saber si hay que
*-- cerrar la tabla al final o no
*-- lcFichero: Cadena con el nombre del
*-- fichero de hoja de cálculo
*-- generado
*-- lnCoincidencias: Numérica para saber si
*-- existe el directorio
*-- ENLACES, que se emplea
*-- como puente. Si no
*-- existe se creará
*-- lnCampo:Número de campo que se está
*-- procesando en el bucle de
*-- formateo de la hoja de cálculo
*-- lcTitCampo:Cadena con el nombre del
*-- campo o su caption en
*-- función de si es tabla libre
*-- o no
*-- lnAnchoCol:Numérica que contiene la
*-- anchura que se debe dar a
*-- cada columna en función
*-- de la anchura del campo y de
*-- la cabecera
*-- lnError: Número de error detectado, se
*-- emplea para saber si está
*-- instalado Excel
*-- lcColumna: Cadena para identificar las
*-- columnas de hoja de cálculo
*-- que se procesan
*-- lcRutinaErr: Rutina de control de
*-- errores actual, para
*-- restaurarla después

if type("lcTabla")="U"
=messageBox("Debe pasar como parámetro el nombre de la tabla")
return
else
if type("llEsLibre")="U"
=messageBox("Debe pasar como parámetro .t. si es una tabla;
libre o .f. si pertenece a una ;
base de datos")
return
endif
endif
lcArea=select()
lcFichero=sys(5)+sys(2003)+ ;
"\Enlaces\Hoja1.xls"
if used(lcTabla)
select (lctabla)
llCerrar=.f.
else
use (lcTabla) in 0

llCerrar=.t.

endif

lnCoincidencias=adir(aActual,"Enlaces","D")

* aActual guarda la estructura del
* directorio en uso
release aActual
if lnCoincidencias=0

* Si no hay ninguno es que no existe y se
* crea

mkdir Enlaces

endif
erase (lcFichero)

* Si no ha podido borrar el fichero es
* porque está en uso. De esta
* forma se evita el error en caso de que
* repita la operación sin haber cerrado
* excel

if file(lcFichero)

=messagebox("El Fichero de traspaso ; "+lcFichero+" está abierto por alguna ; otra aplicación.;
Debe cerrarlo para poder realizar la ;
exportación")
else
export to .\Enlaces\Hoja1 type XL5
lcRutinaErr=on("error")
lnError=0
on error lnError=error()

* Si se produce error guardar el número.
* Normalmente si hay error será porque no
* está Excel

loExcel=createobject("Excel.application")
on error &lcRutinaErr

if lnError=1426

=messageBox("No puede efectuarse la; exportación porque Excel no está ; instalado o no está ; disponible",48,"FoxPress")

return
endif

 

loExcel.application.visible=.f.
loExcel.application.standardfontsize=8
loExcel.application.workbooks.open;
(lcFichero)
loExcel.application.range("A1:IV16384").font.color=rgb(0,0,128)

loExcel.application.range;
("A1:IV16384").font.size=8
loExcel.application.rows(2).insert

** insertar una fila para separar los
** títulos

loExcel.application.rows(1).font.bold=.t.
loExcel.application.rows(1).font.color=rgb(128,0,128)
loExcel.application.rows(2).font.bold=.t.
loExcel.application.rows(2).font.;
color=rgb(128,0,128)
=afields(aCampos)

for lnCampo=1 to fcount()
if llEsLibre
lcTitCampo=alltrim(aCampos(lnCampo,1))
else
lcTitCampo=alltrim(dbgetprop(lcTabla+;
"."+field(lnCampo),"FIELD",;
"CAPTION"))
endif
lnAnchoCol=max(aCampos(lnCampo,3),len(lcTitCampo))
** Para identificar el nombre de columna
** Excel, daría error si hubiera más de 52
** campos en la tabla
if lnCampo<=26
lcColumna=chr(64+lnCampo)
else
lcColumna="A"+chr(64+lnCampo-26)
endif
loExcel.application.columns(lcColumna).columnwidth=lnAnchoCol

*----- El código que viene a continuación
*-- en la estructura CASE .. ENDCASE se
*-- utiliza para construir los formatos de
*-- campo adecuados en cada columna de
*-- Excel

 

do case

case aCampos(lnCampo,2)="N"
cFormato=""
nDecRestan=aCampos(lnCampo,4)
if aCampos(lnCampo,4)>0
lDecimales=.t.
else

lDecimales=.f.
endif
nMiles=0
lPrimero=.t.
for i=1 to aCampos(lnCampo,3)
if lDecimales
if nDecRestan > 0
cFormato="0"+cFormato
nDecRestan=nDecRestan-1
else
cFormato="."+cFormato
lDecimales=.f.
endif
else
if lPrimero=.t.
cFormato="0"+cFormato
lPrimero=.f.
nMiles=nMiles+1
else
cFormato="#"+cFormato
nMiles=nMiles+1
if nMiles=3 and i < aCampos(lnCampo,3)
cFormato=","+cFormato
nMiles=0
endif
endif
endif
next
case aCampos(lnCampo,2)="D"
cFormato="dd/mm/yyyy"
case acampos(lnCampo,2)="T"
cFormato="dd/mm/yyyy hh:mm:ss"
otherwise
cFormato=""
endcase
loExcel.application.columns(lcColumna).numberFormat=cFormato
loExcel.application.cells(1,lnCampo).value=upper(lcTitCampo)
loExcel.application.cells(2,lnCampo).value="'"+replicate("-",lnAnchoCol*3)
next
loExcel.application.cells(2,fcount()+1).value="' "
loExcel.application.visible=.t.
endif
release aCampos
if llCerrar && Cerrar la tabla solamente si no estaba abierta anteriormente
select (lcTabla)
use
endif
select (lcArea)

*************** FIN ***************

Roberto Martínez ha programado para Informix y en Xbase con Clipper, Fox 2.x y VFP. Se puede entrar en contacto con él en rob@datapress.com