Enviando una consulta a EXCEL

2 comentarios

Hay muchas formas de enviar el resultado de una consulta a una planilla Excel, aquí veremos una de esas formas.

NOTA: el código fuente está basado en Visual FoxPro pero puede ser fácilmente adaptado a otros lenguajes de programación.

La ventaja de este método es que nos resultará muy fácil enviar la consulta que queremos, al libro Excel que queremos, a la hoja Excel que queremos, en el rango que queremos mostraremos la consulta, y esa consulta puede estar basada en cualquier tabla o en cualquier vista de nuestra Base de Datos del Firebird.

Primero, creamos la función que se encargará de realizar la tarea.

Listado 1.

FUNCTION ENVIAR_EXCEL_CON_DSN
LParameters tcDSN, tcUsuario, tcPassword, tcConsulta, tcRango
Local lcMensajeError, lcConsulta, loExcel, loLibro, loHoja, loTabla, lcConexion, lcBaseDatos

  lcMensajeError = ""

  tcRango = iif(VarType(tcRango) <> "C", "A1", tcRango)    && Si no se definió donde colocar la tabla, se la coloca en la celda A1

  *-- Tratamos de crear un objeto Excel
  if Empty(lcMensajeError)
    loExcel = CreateObject("Excel.Application")
    lcMensajeError = iif(VarType(loExcel) <> "O", "No se pudo crear el objeto EXCEL, verifica si está instaldo EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, tratamos de crear un libro Excel
  if Empty(lcMensajeError)
    loLibro = loExcel.Workbooks.Add
    lcMensajeError = iif(VarType(loLibro) <> "O", "No se pudo crear el libro de EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, tratamos de crear una hoja en el libro Excel
  if Empty(lcMensajeError)
    loHoja = loLibro.Worksheets(1)
    lcMensajeError = iif(VarType(loHoja) <> "O", "No se pudo crear la hoja en el libro EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, creamos el string de conexión a la Base de Datos del Firebird, usando el DSN de esa Base de Datos
  if Empty(lcMensajeError)
    lcConexion = "ODBC;DSN=" + tcDSN + ";USER=" + tcUsuario + ";PASSWORD=" + tcPassword
  endif

  *-- Si todo OK, tratamos de conectarnos a la Base de Datos del Firebird
  if Empty(lcMensajeError)
    loTabla = loHoja.QueryTables.Add(lcConexion, loHoja.Range(tcRango), tcConsulta)
    lcMensajeError = iif(VarType(loTabla) <> "O", "No se pudo crear la tabla de consulta en la planilla Excel", lcMensajeError)
  endif

  *-- Si todo OK, insertamos la tabla en la planilal Excel
  if Empty(lcMensajeError)
    #DEFINE xlInsertEntireRows 2
    loTabla.RefreshStyle = xlInsertEntireRows
    loTabla.Refresh
  endif

  *-- Si todoOK, mostramos la planilla Excel
  if Empty(lcMensajeError)
    loExcel.Visible = .T.
  endif

  *-- Liberamos de la memoria los objetos que habíamos creado
  loTabla = .NULL.
  loHoja  = .NULL.
  loLibro = .NULL.
  loExcel = .NULL.

Return(lcMensajeError)
*
*

Y después llamamos a esa función, con los parámetros que deseemos usar.

Listado 2.

Local lcDSN, lcUsuario, lcPassword, lcConsulta, lcMensajeError, lcRango

  lcDSN = "AUTOMUNDO-FIREBIRD"

  lcUsuario = "SYSDBA"
  lcPassword = "masterkey"

   lcConsulta = "SELECT " ;
              + "   ASC_ANOEJE, " ;
              + "   ASC_CODSUC, " ;
              + "   ASC_FECHAX, " ;
              + "   ASC_NUMERO " ;
              + "FROM " ;
              + "   V_CONTA_ABM_ASIENTOS " ;
              + "WHERE " ;
              + "   ASC_NUMERO >= 100 AND " ;
              + "   ASC_NUMERO <= 120"

  lcRango = "B5"

  lcMensajeError = ENVIAR_EXCEL_CON_DSN(lcDSN, lcUsuario, lcPassword, lcConsulta, lcRango)

  *-- Si ocurrió algún error, mostramos un mensaje al usuario
  if !Empty(lcMensajeError)
    #DEFINE MSG_ICONO_ERROR 16
    #DEFINE MSG_BOTON_ACEPTAR 0
    =MessageBox(lcMensajeError, MSG_ICONO_ERROR + MSG_BOTON_ACEPTAR, "Hay un problema...")
  endif

Return
*
*

Y así obtendremos una planilla similar a la siguiente:

EXCEL01

Captura 1. Si haces clic en la imagen la verás más grande

Por lo tanto, nos resultará muy sencillo crear una planilla Excel con los datos que el usuario pueda necesitar. Inclusive podríamos tener en nuestra aplicación una opción que le muestre todas las vistas disponibles y que le permita crear consultas personalizadas, a su gusto y paladar. Ya depende de la creatividad de cada uno hacer algo así.

Artículos relacionados:

Usando EXCEL para leer bases de datos de Firebird

Planillas EXCEL dinámicas con Firebird (1)

Planillas EXCEL dinámicas con Firebird (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Planillas EXCEL dinámicas con Firebird (1)

Deja un comentario

Si queremos darle un muy alto valor agregado a nuestras aplicaciones entonces podemos proveerles de planillas Excel dinámicas. A nuestros clientes les encantará eso.

¿Qué es una planilla Excel dinámica?

Una planilla cuyos datos no fueron introducidos a mano sino que provienen de una fuente externa, en nuestro caso de una Base de Datos de Firebird.

Pero lo interesante aquí es que si cambian los datos de nuestra Base de Datos, también cambia la planilla Excel, automáticamente. Y si cambiamos el valor de algunas celdas en nuestra planilla Excel, entonces obtendremos una planilla Excel actualizada.

Muy interesante ¿verdad?

En este primer artículo de la serie veremos como obtener datos de las tablas y de las vistas que tenemos en nuestra Base de Datos de Firebird. En el siguiente artículo veremos algo quizás más interesante: cambiar el valor de una celda en la planilla Excel y que la planilla se actualice automáticamente.

EXCEL01

Diagrama 1. Si haces clic en la imagen la verás más grande

El Diagrama 1. nos muestra el proceso.

  1. Es nuestra Base de Datos creada con Firebird
  2. Usamos OLEDB u ODBC para enviar los datos
  3. Un archivo de conexión define  toda la información que se necesita para acceder y recuperar los datos de nuestra Base de Datos
  4. La información de la conexión es copiada desde el archivo de conexión hacia la planilla Excel
  5. Los datos son copiados dentro de la planilla Excel y ya pueden ser usados como si hubieran sido introducidos manualmente

Usando ODBC para la conexión

  1. El driver ODBC de Firebird debe estar instalado en la computadora donde se encuentra la Base de Datos
  2. Hay que definir un DSN (Data Source Name) para que la conexión pueda realizarse: Inicio | Panel de Control | Herramientas administrativas | Orígenes de datos ODBC | DSN de usuario | Agregar … | Firebird/Interbase driver | completar los datos pedidos

EXCEL02

Captura 1. Si haces clic en la imagen la verás más grande

Para verificar que la conexión puede realizarse exitosamente es aconsejable hacer clic sobre el botón “Probar conexión”. Si todo está ok veremos algo así:

EXCEL03

Captura 2. Si haces clic en la imagen la verás más grande

Conectando la planilla Excel a la Base de Datos de Firebird

  1. Abrir Excel
  2. Crear una nueva planilla
  3. Datos | De otras fuentes | Desde Microsoft Query | elegimos el DSN de nuestra Base de Datos

EXCEL04

Captura 3. Si haces clic en la imagen la verás más grande

EXCEL05

Captura 4. Si haces clic en la imagen la verás más grande

 EXCEL06Captura 5. Si haces clic en la imagen la verás más grande

Como podemos ver en la Captura 5., el Excel nos muestra los nombres de todas las tablas y de todas las vistas que se encuentran en nuestra Base de Datos. Para ver solamente tablas o solamente vistas, podemos hacer clic en Opciones, como vemos a continuación:

EXCEL19

Captura 6. Si haces clic en la imagen la verás más grande

y si hacemos clic sobre el signo + que se encuentra a la izquierda de las tablas y de las vistas, veremos los nombres de las columnas pertenecientes a la tabla o a la vista elegida.

EXCEL07

Captura 7. Si haces clic en la imagen la verás más grande

Para elegir cuales de esas columnas queremos ver en nuestra planilla Excel, la seleccionamos y luego hacemos clic en el botón que la trasladará a “Columnas en la consulta:”

EXCEL08

Captura 8. Si haces clic en la imagen la verás más grande

A continuación podemos Filtrar los datos y elegir el Criterio de ordenación y pedirle que muestre los datos en una planilla Excel.

EXCEL09

Captura 9. Si haces clic en la imagen la verás más grande

EXCEL10

Captura 10. Si haces clic en la imagen la verás más grande

Y ahora viene lo más interesante de todo, si elegimos “Informe de tabla dinámica” o si elegimos “Informe de gráfico y tabla dinámicos” entonces cada vez que se cambien los datos de nuestra Base de Datos también se cambiarán en la planilla Excel.

Algunos puntos a considerar

a) Los datos en la tabla dinámica están resumidos, o agrupados por filas. Varias filas de los datos originales ocuparán una sola fila en la planilla Excel

EXCEL11

Captura 11. Si haces clic en la imagen la verás más grande

EXCEL12

Captura 12. Si haces clic en la imagen la verás más grande

En la Captura 11. podemos ver los datos originales que tenemos en la tabla de nuestra Base de Datos. En la Captura 12. vemos la planilla Excel que fue creada a partir de esos datos. Como puedes ver, las filas se agruparon por fechas, y en las columnas numéricas se muestran las sumas de dichas columnas. Eso muchas veces no tiene sentido (como sumar los años en la columna ASC_ANOEJE, año del ejercicio contable, por ejemplo) pero es lo que hace Excel.

Para ver las filas originales, no agrupadas, podemos hacer clic sobre una de las filas de la planilla, clic con el botón derecho, y luego elegir la opción “Mostrar detalles”.

EXCEL13

Captura 13. Si haces clic en la imagen la verás más grande

En este caso, como el cursor está sobre la fila que tiene fecha 03/01/2014, las filas de esa fecha que tenemos en nuestra tabla original serán las mostradas.

 EXCEL14

Captura 14. Si haces clic en la imagen la verás más grande

b) Los resultados se crean en una nueva hoja. Un detalle a notar, es que el resultado de esa consulta fue creado en una nueva hoja de la planilla, no en la hoja que estábamos usando.

c) Se puede ver el detalle de todas las filas importadas. Para conseguirlo, arrastramos los nombres de las columnas que se encuentran en “Etiquetas de fila” hacia “Etiquetas de columna”.

EXCEL17

Captura 15. Si haces clic en la imagen la verás más grande

EXCEL18

Captura 16. Si haces clic en la imagen la verás más grande

y luego continuamos igual que en la Captura 13.

Actualizando la planilla Excel

Por defecto, el contenido de la tabla dinámica que creamos en Excel no se actualiza cuando cambian los datos en el origen (o sea, en nuestra Base de Datos del Firebird)

Si el contenido de nuestra tabla en la Base de Datos de Firebird cambió, podemos mostrar los datos actualizados en la planilla Excel. Tenemos tres opciones:

a) Actualización manual. Colocamos el mouse en cualquier celda de la tabla que insertamos y luego clic con el botón derecho y elegimos la opción Actualizar

EXCEL15

Captura 17. Si haces clic en la imagen la verás más grande

Si después de hacer clic en Actualizar volvemos a pedirle que nos muestre los detalles, como ya vimos en la Captura 13., entonces nuestra planilla Excel mostrará los datos actualizados.

EXCEL16

Captura 18. Si haces clic en la imagen la verás más grande

EXCEL23

Captura 19. Si haces clic en la imagen la verás más grande

También se puede actualizar manualmente la planilla Excel haciendo clic sobre cualquier celda de la tabla dinámica, luego en Opciones | Actualizar | Actualizar o Actualizar todo

b) Actualización automática. También podemos conseguir que la planilla Excel se actualice automáticamente, para ello hacemos clic en cualquier celda de la tabla dinámica creada, luego Opciones | Cambiar origen de datos | Propiedades de conexión … | elegimos el tiempo deseado

EXCEL20

Captura 20. Si haces clic en la imagen la verás más grande

En la Captura 20. se pide que la planilla Excel sea actualizada cada 1 minuto, desde luego que tú puedes elegir otro tiempo, esto es sólo un ejemplo.

IMPORTANTE: La hoja creada con “Mostrar detalles”, tal como vimos en la Captura 13. no cambiará, solamente cambia la hoja original.

EXCEL21

Captura 21. Si haces clic en la imagen la verás más grande

EXCEL22

Captura 22. Si haces clic en la imagen la verás más grande

En la Captura 21. vemos el contenido original de la tabla, se cambió un valor en la columna ASC_COTIZA en la Base de Datos del Firebird y un minuto después vemos que la planilla Excel se actualizó automáticamente, tal como podemos observar en la Captura 22.

c) Actualizar al abrir la planilla Excel. Si queremos que cada vez que se abra la planilla Excel muestre a sus datos actualizados, debemos marcar la casilla “Actualizar al abrir el archivo” que se muestra en la Captura 20.

Conclusión:

Poder enviar el resultado de consultar a nuestras tablas y a nuestras vistas de una Base de Datos del Firebird a una planilla Excel les resultará muy útil a nuestros clientes. Las tablas dinámicas muestran los datos agrupados y permiten que esos datos se actualicen cuando la tabla subyacente del Firebird se actualice. Eso es muy bueno para poder tomar buenas decisiones empresariales.

Hay muchísimo más que aprender sobre las tablas dinámicas de Excel. Es muy aconsejable que busques información en Internet sobre ellas, te resultará muy útil para que tus aplicaciones se destaquen sobre la competencia.

Artículos relacionados:

Tablas dinámicas en Excel

El índice del blog Firebird21

El foro del blog Firebird21

Usando EXCEL para leer bases de datos de Firebird

33 comentarios

A veces te puede resultar muy útil que el contenido de algunas tablas de Firebird sean migradas a Excel. Posiblemente la mayoría de los usuarios de tus aplicaciones conocen como usar Excel entonces para ellos será fácil y sencillo realizar sus propias consultas, ver gráficos, etc.

Los pasos a seguir son los siguientes:

  1. Instalar el Cliente del Firebird o el archivo fbclient.dll
  2. Instalar el driver ODBC
  3. Crear una conexión DSN a tu Base de Datos
  4. Abrir Excel
  5. Importar el contenido de una tabla o de una vista

Paso 1. Instalar el Cliente del Firebird o el archivo fbclient.dll

La computadora debe ya tener instalado el Cliente del Firebird. Si ese no es el caso entonces hay que copiar el archivo fbclient.dll de cualquier computadora que tenga al Firebird instalado. Ese archivo normalmente se encuentra en la carpeta: “C:\Archivos de Programa\Firebird\Firebird_2_5\bin”.

Paso 2. Instalar el driver ODBC

Si la computadora ya se puede conectar a una Base de Datos de Firebird entonces es muy probable que ya tenga instalado el driver ODBC. Si ese no es el caso, lo puedes descargar desde:

http://www.firebirdsql.org/en/odbc-driver/

Allí encontrarás varias opciones, elige la que corresponda a tu Sistema Operativo y a los bits de tu computadora

Paso 3. Crear una conexión DSN a tu Base de Datos

Cuando creas una conexión DSN permites que la conexión a tu Base de Datos pueda realizarse desde muchos programas, uno de esos programas es Excel.

Inicio | Panel de control | Herramientas administrativas | Orígenes de datos ODBC 

EXCEL1

Captura 1. Si haces clic en la imagen la verás más grande

Ahora si haces clic en el botón “Agregar…” podrás agregar un nuevo origen de datos ODBC.

EXCEL2

Captura 2. Si haces clic en la imagen la verás más grande

Estos son todos los drivers ODBC que tiene instalados la computadora, lógicamente debes elegir el que dice “Firebird/Interbase(r) driver”

A continuación debes indicarle los parámetros de conexión, o sea los datos que se necesitan para que pueda conectarse a una Base de Datos de Firebird:

EXCEL3

Captura 3. Si haces clic en la imagen la verás más grande

Siempre es recomendable que verifiques que has escrito todo correctamente y por lo tanto que la conexión pueda realizarse exitosamente, eso lo haces con un clic en el botón “Probar conexión”

EXCEL4

Captura 4. Si haces clic en la imagen la verás más grande

Si la conexión es satisfactoria entonces ya tienes creada una conexión a tu Base de Datos mediante DSN. Esa conexión DSN luego podrás usarla desde Excel o desde muchos otros programas, incluidos los que escribas con tu lenguaje de programación (Visual FoxPro, Visual Basic, Delphi, C, C++, Java, etc.)

Naturalmente que el nombre de tu origen de datos DSN lo eliges tú, ERP2000 es solamente un ejemplo, tú ponle el nombre que más te guste.

EXCEL5

Captura 5. Si haces clic en la imagen la verás más grande

Como puedes ver en la Captura 5. ahora hay un nuevo origen de datos ODBC, el que acabamos de crear hace un instante.

Paso 4. Abrir Excel

Bueno, este paso no merece comentarios, porque si aún no sabes abrir Excel …

Paso 5. Importar el contenido de una tabla o de una vista

EXCEL6

Captura 6. Si haces clic en la imagen la verás más grande

Primero, le indicamos al Excel que queremos importar datos desde un lugar externo, lo hacemos mediante clics en las opciones:

Datos | De otras fuentes | Desde Microsfot Query

tal como podemos ver en la Captura 6.

EXCEL7

Captura 7. Si haces clic en la imagen la verás más grande

Y ahora en el cuadro de diálogo “Elegir origen de datos” vemos que … ¡¡¡está nuestra conexión DSN!!!

Excelente, significa que podemos conectarnos sin problemas a nuestra Base de Datos de Firebird.

EXCEL8

Captura 8. Si haces clic en la imagen la verás más grande

A la izquierda vemos los nombres de todas las tablas y de todas las vistas que tiene nuestra Base de Datos, a la derecha las columnas que aparecerán en la planilla Excel. Si queremos ver a todas las columnas entonces hacemos clic en el botón “>” tal como lo muestra la flecha roja. Pero si queremos elegir solamente algunas columnas entonces deberíamos hacer clic en el botón “+” que se encuentra a la izquierda del nombre de nuestra tabla o vista.

EXCEL9

Captura 9. Si haces clic en la imagen la verás más grande

Si luego hacemos clic en el botón “Siguiente” podremos filtrar datos (es decir, establecer las condiciones para que sean mostrados), ordenarlos por alguna columna, y elegir si deseamos verlos en una planilla Excel o en una consulta de Microsoft Query. Finalmente en el cuadro de diálogo “Importar datos” elegimos otras opciones y luego clic en el botón “Aceptar”

EXCEL10

Captura 10. Si haces clic en la imagen la verás más grande

Y como resultado de haber seguido todos estos pasos obtenemos esto:

EXCEL11

Captura 11. Si haces clic en la imagen la verás más grande

O sea que el contenido de nuestra tabla ASIENTOSCAB de la Base de Datos ERP2000 ahora se encuentra en una planilla de Excel. Interesante. ¿Verdad?

Conclusión:

Si creamos una conexión DSN a nuestra Base de Datos entonces podremos acceder a sus tablas y a sus vistas desde muchos programas externos, por ejemplo Excel, tal como se vio en este artículo.

Artículo relacionado:

El índice del blog Firebird21