Crear gráficos en Excel desde VFP

Podemos crear una planilla de Excel desde Visual FoxPro. También dar formato al texto y a las celdas de una planilla de Excel desde Visual FoxPro mediante OLE.

Todo esto con el fin de brindar a nuestros usuarios una presentación mas elegante de una consulta de Visual FoxPro en una planilla de Excel.

Si queremos sorprender a nuestros usuarios: ¿Por qué no creamos también un gráfico en la planilla Excel?. De esta forma transformaremos la conocida frase: "una imagen vale mas que mil palabras", en "un gráfico vale mas que mil datos".

En este ejemplo, partimos de dos consultas: 1) Una consulta que acumula las ventas anuales, y 2) Una consulta que acumula las ventas semanales de "n" items.

Para simular los resultados de estas consultas crearemos dos tablas: 1) ANIO.DBF y 2) SEMANA.DBF

Con la primera consulta vamos a crear una planilla ANIO.XLS con un gráfico de líneas como muestra la siguiente figura:



Con la segunda consulta vamos a crear una planilla SEMANA.XLS con un bráfico de barras apiladas, como se muestra abajo:



Podemos ver que a los gráficos se le puede añadir títulos, leyendas al gráfico, a las series y a los ejes, y modificar sus atributos para que estos se vean elegantes.

¿Cómo hacer esto desde VFP?

Una vez que tenemos las tabla o cursor con los resultados de la consulta, lo copiamos a formato XLS con la sentencia COPY TO ... TYPE XL5.

Luego creamos un objeto "Excel.Application" con el cual abrimos la planilla generada y allí creamos el gráfico en una hoja nueva, y le damos el formato deseado.

Una vez finalizado, grabamos y cerramos la planilla. Si queremos que quede abierto el Excel con la planilla generada, no cerramos la planilla y la hacemos visible con la propiedad: loExcel.APPLICATION.VISIBLE = .T.

El código en VFP de este ejemplo que nos genera ambas planillas, es el siguiente y lo podemos descargar de graficos.zip

*-----------------------------
SET DATE DMY
SET SAFETY OFF

*--- Crea las tablas que simulan las consultas
DO CreaArchivos

*--- Genera gráfico anual
DO GraficoAnual

*--- Genera gráfico semanal
DO GraficoSemanal

CLOSE TABLES ALL
RETURN
*-----------------------------

*-----------------------------
* PROCEDURE CreaArchivos
*-----------------------------
* Crea y llena las tablas de 
* ejemplo y las copia
* como archivo tipo .XLS
*-----------------------------
PROCEDURE CreaArchivos
   LOCAL lnI, ldFechaIni

   *--- Inicializo la funcion RAND()
   lnI = RAND(-1)
   ldFechaIni = DATE(1998,12,27)

   *---- Creo tablas
   CREATE TABLE Semana (Dia C(10), Item01 N(10,2), ;
      Item02 N(10,2), Item03 N(10,2), Item04 N(10,2))

   CREATE TABLE Anio (Mes C(10), TOTAL N(10,2))

   *--- Cargo datos de los 7 días y los 12 meses
   FOR lnI = 1 TO 12
      IF lnI <= 7
         INSERT INTO Semana ;
            (Dia, Item01, Item02, Item03, Item04) ;
            VALUES (CDOW(ldFechaIni+lnI), RAND()*1000, ;
            RAND()*1000, RAND()*1000, RAND()*1000 )
      ENDIF
      INSERT INTO Anio (Mes, TOTAL) ;
         VALUES (CMONTH(GOMONTH(ldFechaIni,lnI)), RAND()*10000 )
   ENDFOR

   *--- Guardo como tipo XL5
   SELECT Semana
   COPY TO Semana TYPE XL5
   SELECT Anio
   COPY TO Anio TYPE XL5
   RETURN
ENDPROC

*-----------------------------
* PROCEDURE GraficoAnual
*-----------------------------
* Creo un gráfico de Lineas en Excel
*-----------------------------
PROCEDURE GraficoAnual
   LOCAL lnFil, lnCol, lcRango, lcPlanilla, lcHoja, loExcel

   lnFil = RECCOUNT("Anio") + 1
   lnCol = FCOUNT("Anio")
   lcRango = "A1:" + CHR(64+lnCol) + ALLTRIM(STR(lnFil))
   lcHoja = "Anio"
   lcPlanilla = SYS(5) + CURDIR() + "Anio.xls"

   *--- Creo objeto Excel
   loExcel=CREATEOBJECT("Excel.application")
   WITH loExcel.APPLICATION
      .VISIBLE = .F. && oculto el trabajo en la aplicacion Excel
      .workbooks.OPEN(lcPlanilla)

      *---- Añado grafico de líneas
      .Charts.ADD
      .ActiveChart.ChartType = 65 && xlLineMarkers
      .ActiveChart.SetSourceData(.Sheets(lcHoja).RANGE(lcRango), 2)
      .ActiveChart.Location(1, "Grafico")
      .ActiveChart.HasDataTable = .F.

      *--- Estilo y ancho línea
      .ActiveChart.SeriesCollection(1).SELECT
      WITH .SELECTION.BORDER
         .Weight = 4 && xlThick
         .LineStyle = 1 && xlContinuous
      ENDWITH

      *--- Titulo gráfico
      .ActiveChart.HasTitle = .T.
      .ActiveChart.ChartTitle.TEXT = "Ventas acumuladas anuales"
      .ActiveChart.ChartTitle.SELECT
      WITH .SELECTION.FONT
         .NAME = "Arial"
         .FontStyle = "Negrita"
         .SIZE = 16
      ENDWITH

      *--- Leyenda
      .ActiveChart.HasLegend = .T.
      .ActiveChart.Legend.SELECT
      .SELECTION.Position = -4160 && xlTop

      *--- Ejes
      WITH .ActiveChart
         .Axes(1, 1).HasTitle = .T.
         .Axes(1, 1).AxisTitle.TEXT = "Meses del año"
         .Axes(2, 1).HasTitle = .T.
         .Axes(2, 1).AxisTitle.TEXT = "Totales en U$S"
         .Deselect
      ENDWITH

      *--- Grabo planilla y cierro
      .VISIBLE = .F.
      .ActiveWorkbook.SAVE
      .workbooks.CLOSE
   ENDWITH
   RELE loExcel
   RETURN
ENDPROC

*-----------------------------
* PROCEDURE GraficoSemanal
*-----------------------------
* Gráfico de Barras Apiladas en Excel 
*-----------------------------
PROCEDURE GraficoSemanal
   LOCAL lnFil, lnCol, lcRango, lcPlanilla, lcHoja, loExcel

   lnFil = RECCOUNT("Semana") + 1
   lnCol = FCOUNT("Semana")
   lcRango = "A1:" + CHR(64+lnCol) + ALLTRIM(STR(lnFil))
   lcHoja = "Semana"
   lcPlanilla = SYS(5) + CURDIR() + "Semana.xls"

   *--- Creo objeto Excel
   loExcel=CREATEOBJECT("Excel.application")

   WITH loExcel.APPLICATION
      .VISIBLE = .F. && oculto el trabajo en la aplicacion Excel
      .workbooks.OPEN(lcPlanilla)

      *---- Añado grafico barras apiladas
      .Charts.ADD
      .ActiveChart.ChartType = 52 && xlColumnStacked
      .ActiveChart.SetSourceData(.Sheets(lcHoja).RANGE(lcRango), 2)
      .ActiveChart.Location(1, "Grafico")
      .ActiveChart.HasDataTable = .F.

      *--- Titulo gráfico
      .ActiveChart.HasTitle = .T.
      .ActiveChart.ChartTitle.TEXT = "Ventas semanales por items"
      .ActiveChart.ChartTitle.SELECT
      WITH .SELECTION.FONT
         .NAME = "Arial"
         .FontStyle = "Negrita"
         .SIZE = 16
      ENDWITH

      *--- Leyenda 
      .ActiveChart.HasLegend = .T.
      .ActiveChart.Legend.SELECT
      .SELECTION.Position = -4160 && xlTop

      *--- Ejes
      WITH .ActiveChart
         .Axes(1, 1).HasTitle = .T.
         .Axes(1, 1).AxisTitle.TEXT = "Días de la semana"
         .Axes(2, 1).HasTitle = .T.
         .Axes(2, 1).AxisTitle.TEXT = "Totales en U$S"
         .Deselect
      ENDWITH

      *--- Grabo planilla y cierro
      .VISIBLE = .F.
      .ActiveWorkbook.SAVE
      .workbooks.CLOSE
   ENDWITH
   RELE loExcel
   RETURN
ENDPROC
*-----------------------------

Una idea que nos ayudará a crear nuestros gráficos, es abrir la planilla con Excel, y comenzar a grabar una macro; hacemos nuestro gráfico y al terminar, finalizamos la grabación de la macro. El código que genera la macro está en Visual Basic for Applications, y es muy fácil convertirlo a código de Visual FoxPro.

Hasta la próxima,


Luis María Guayán