FoxPress – Febrero 2000

Crear Tablas dinámicas en Excel desde VFP

http://www.fpress.com/

Por Luis Maria Guayán

Crear una planilla de Excel, dar formato al texto y a las celdas de una planilla de Excel y crear un gráfico en Excel desde Visual FoxPro mediante OLE, ya se vio en números anteriores de FoxPress.

También podemos crear una Tabla Dinámica en Excel y así brindar una información resumida a los usuarios de nuestros sistemas.

Esto lo lograremos mediante OLE de una forma muy simple como se verá a continuación. Luego podemos darle formato a la tabla dinámica con los comandos vistos anteriormente o convirtiendo las macros de Excel en código de Visual FoxPro.

Para este ejemplo generamos una tabla y la llenamos con datos aleatorios. Nuestra tabla "VENTAS.DBF" tiene la información de las ventas de una concecionaria de automóviles que esta agrupada por por Vendedor, Zona, Marca, Modelo y Año.

Nuestro resumen agrupará las ventas por Marca y Modelo y podremos seleccionar la Zona y el Vendedor para filtrar los datos. En Excel 2000 también podremos filtrar las Marcas, los Modelos y los Años.

Para generar nuestra tabla dinámica, partimos del generador de tablas dinámicas de Excel, el cual nos permite seleccionar los datos en cuatro áreas como se ve en el siguiente gráfico:

En el área Página pondremos los campos Vendedor y Zona, por los cuales podremos filtrar nuestra información. En el área Fila colocaremos los campos Marca y Modelo. Al campo Año lo colocaremos en el área Columnas. Finalmente, para obtener un total de las ventas, en el área Datos colocaremos el campo Cantidad. Una vez finalizada esta tarea, el diseño de la tabla dinámica se muestra en el siguiente gráfico.

Código para hacerlo desde Visual FoxPro

Una vez obtenida la tabla VENTAS, la copiamos en formato XLS con la sentencia COPY TO ... TYPE XL5.

Creamos un objeto “Excel.Application”, abrimos la planilla y creamos la tabla dinámica, según el diseño que vimos anteriormente.

Luego grabamos y cerramos la planilla. Como se dijo en artículos anteriores, si deseamos que luego de generar la planilla con su tabla dinámica, quede el Excel visible, no cerramos la planilla y la hacemos visible con la propiedad: loExcel.APPLICATION.VISIBLE = .T.

El código en VFP lo mostramos a continuación y está entre los fuentes del mes.

*==========================================

SET SAFETY OFF

*--- Crea tabla de ventas

DO CreaTabla

*--- Genera tabla dinámica

DO GenTabla

CLOSE TABLES ALL

RETURN

*------------------------------------------

* PROCEDURE CreaTabla

*------------------------------------------

* Crea y llena la tabla de ejemplo y la

* copia

* como archivo tipo .XLS

*------------------------------------------

PROCEDURE CreaTabla

 LOCAL lnVend, lnZona, lnMarca, lnmodelo, lnAnio, lnCant

 LOCAL laZona(3), laMarca(3), laModelo(3,4)

 laZona(1) = "SUR"

 laZona(2) = "NORTE"

 laZona(3) = "CENTRO"

 *---- Cargo las marcas y modelos

 laMarca(1) = "FORD"

 laMarca(2) = "FIAT"

 laMarca(3) = "VOLKSWAGEN"

 laModelo (1,1) = "MONDEO"

 laModelo (1,2) = "FIESTA"

 laModelo (1,3) = "ESCORT"

 laModelo (1,4) = "FOCUS"

 laModelo (2,1) = "MAREA"

 laModelo (2,2) = "BRAVO"

 laModelo (2,3) = "SIENA"

 laModelo (2,4) = "PALIO"

 laModelo (3,1) = "GOL"

 laModelo (3,2) = "GOLF"

 laModelo (3,3) = "PASSAT"

 laModelo (3,4) = "SAVEIRO"

 *--- Inicializo la funcion RAND()

 lnCant = RAND(-1)

 *---- Creo tabla

 CREATE TABLE Ventas (Vendedor C(2), Zona C(10), ;

  Marca C(10), Modelo C(10), Anio N(4), Cantidad N(4))

 *--- Cargo datos ALEATORIOS

 FOR lnVend = 1 TO 3

  FOR lnZona = 1 TO 3

   FOR lnMarca = 1 TO 3

    FOR lnmodelo = 1 TO 4

     FOR lnAnio = 1998 TO 2000

      lnCant = INT(RAND()*9)

      IF lnCant > 0

       INSERT INTO Ventas ;

        (Vendedor, Zona, Marca, Modelo, Anio, Cantidad) ;

        VALUES ;

        (TRANSFORM(lnVend, "@L 99"), laZona(lnZona), ;

        laMarca(lnMarca), laModelo(lnMarca,lnModelo), ;

        lnAnio, lnCant)

      ENDIF

     ENDFOR

    ENDFOR

   ENDFOR

  ENDFOR

 ENDFOR

 *--- Guardo como tipo XL5

 SELECT Ventas

 COPY TO Ventas TYPE XL5

 RETURN

ENDPROC

*------------------------------------------

* PROCEDURE GenTabla

*------------------------------------------

* Crea una tabla dinámica en Excel

*------------------------------------------

PROCEDURE GenTabla

 LOCAL laPagina(2), laFilas(2), laColumnas(1), lnI, ;

  lnReg, lcRango, lcArchivo, loExcel

 lnReg = RECCOUNT()

 lcRango="A1:F"+ALLTRIM(STR(lnReg+1))  && Rango de celdas de la planilla

 lcArchivo= SYS(5) + CURDIR() + "Ventas.xls"

 *--- Arrays con los datos de la tabla dinámica

 laPagina(1)="Vendedor"

 laPagina(2)="Zona"

 laFilas(1)="Marca"

 laFilas(2)="Modelo"

 laColumnas="Anio"

 loExcel=CREATEOBJECT("Excel.application")

 WITH loExcel.APPLICATION

  .VISIBLE = .F. && oculto el trabajo en la aplicacion Excel

  .workbooks.OPEN(lcArchivo)

  *--- Formato datos numéricos

  .Cells.SELECT

  .SELECTION.COLUMNS.AutoFit

  .RANGE("A1").SELECT

  *=== Tabla dinámica ===

  *--- Llamo al generador de Tablas Dinámicas

  .ActiveSheet.PivotTableWizard(1,lcRango,"","MiTablaDinamica")

  *--- Armo la Tabla

  .ActiveSheet.PivotTables("MiTablaDinamica").AddFields(@laFilas, @laColumnas, @laPagina)

  .ActiveSheet.PivotTables("MiTablaDinamica").PivotFields("Cantidad").ORIENTATION = 4

  *--- Selecciono toda la hoja y ajusto columnas

  .Cells.SELECT

  .SELECTION.COLUMNS.AutoFit

  .Sheets("Hoja1").NAME = "Tabla dinámica"

  *--- Selecciono la celda donde queda el cursor

  .RANGE("A3").SELECT

  *--- Grabo planilla y cierro

  .VISIBLE = .F.

  .ActiveWorkbook.SAVE

  .workbooks.CLOSE

 ENDWITH

 RELE loExcel

 RETURN

ENDPROC

*==========================================