Acceso y administración de hojas MS Excel con Delphi

Esta guía paso a paso describe cómo conectarse a Microsoft Excel, recuperar datos de hoja y habilitar la edición de los datos utilizando DBGrid. También encontrará una lista de los errores más comunes que pueden aparecer en el proceso, además de cómo lidiar con ellos.

Lo que se cubre a continuación:

• Métodos para transferir datos entre Excel y Delphi. Cómo conectarse a Excel con ALHARACA (ActiveX Data Objects) y Delphi.
• Crear un editor de hojas de cálculo Excel usando Delphi y ADO
• Recuperando los datos de Excel. Cómo hacer referencia a una tabla (o rango) en un libro de Excel.
• Una discusión sobre los tipos de campo (columna) de Excel
• Cómo modificar hojas de Excel: editar, agregar y eliminar filas.
• Transferencia de datos desde una aplicación de Delphi a Excel. Cómo crear una hoja de trabajo y llenarla con datos personalizados de una base de datos de MS Access.

Microsoft Excel es una poderosa calculadora de hoja de cálculo y herramienta de análisis de datos. Dado que las filas y columnas de una hoja de cálculo de Excel se relacionan estrechamente con las filas y columnas de una tabla de base de datos, muchos desarrolladores consideran apropiado transportar sus datos a un libro de Excel para fines de análisis; y recuperar datos a la aplicación después.

El enfoque más utilizado para el intercambio de datos entre su aplicación y Excel es Automatización. La automatización proporciona una forma de leer datos de Excel utilizando el Modelo de objetos de Excel para sumergirse en la hoja de trabajo, extraer sus datos y mostrarlos dentro de un componente similar a una cuadrícula, a saber, DBGrid o StringGrid.

La automatización le brinda la mayor flexibilidad para ubicar los datos en el libro de trabajo, así como la capacidad de formatear la hoja de trabajo y realizar varias configuraciones en tiempo de ejecución.

Para transferir sus datos hacia y desde Excel sin automatización, puede usar otros métodos como:

• Escriba datos en un archivo de texto delimitado por comas y deje que Excel analice el archivo en celdas
• Transfiera datos utilizando DDE (Intercambio dinámico de datos)
• Transfiera sus datos hacia y desde una hoja de trabajo usando ADO

Dado que Excel es compatible con JET OLE DB, puede conectarse a él con Delphi usando ADO (dbGO o AdoExpress) y luego recuperar el los datos de la hoja de trabajo en un conjunto de datos ADO emitiendo una consulta SQL (al igual que abriría un conjunto de datos en cualquier base de datos mesa).

De esta manera, todos los métodos y características del objeto ADODataset están disponibles para procesar los datos de Excel. En otras palabras, el uso de los componentes ADO le permite crear una aplicación que puede usar un libro de Excel como base de datos. Otro hecho importante es que Excel está fuera de proceso Servidor ActiveX. ADO se ejecuta en proceso y ahorra la sobrecarga de costosas llamadas fuera de proceso.

Cuando se conecta a Excel con ADO, solo puede intercambiar datos sin procesar hacia y desde un libro de trabajo. Una conexión ADO no se puede utilizar para formatear hojas o implementar fórmulas en celdas. Sin embargo, si transfiere sus datos a una hoja de trabajo que está formateada previamente, el formato se mantiene. Después de insertar los datos desde su aplicación a Excel, puede llevar a cabo cualquier formateo condicional utilizando una macro (pregrabada) en la hoja de trabajo.

Puede conectarse a Excel mediante ADO con los dos proveedores OLE DB que forman parte de MDAC: el proveedor Microsoft Jet OLE DB o el proveedor Microsoft OLE DB para controladores ODBC. Nos centraremos en el proveedor Jet OLE DB, que se puede utilizar para acceder a los datos en los libros de Excel a través de los controladores del Método de acceso secuencial indexado (ISAM) instalable.

Propina: Ver el Curso de principiantes a Delphi Programación de base de datos ADO si eres nuevo en ADO.

La propiedad ConnectionString le dice a ADO cómo conectarse al origen de datos. El valor utilizado para ConnectionString consta de uno o más argumentos que ADO utiliza para establecer la conexión.

En Delphi, el componente TADOConnection encapsula el objeto de conexión ADO; puede ser compartido por múltiples componentes del conjunto de datos ADO (TADOTable, TADOQuery, etc.) a través de sus propiedades de conexión.

Para conectarse a Excel, una cadena de conexión válida involucra solo dos datos adicionales: la ruta completa al libro y la versión del archivo Excel.

Una cadena de conexión legítima podría verse así:

ConnectionString: = 'Proveedor = Microsoft. Chorro. OLEDB.4.0; Fuente de datos = C: \ MyWorkBooks \ myDataBook.xls; Propiedades extendidas = Excel 8.0; ';

Cuando se conecta a un formato de base de datos externo compatible con Jet, se deben establecer las propiedades extendidas para la conexión. En nuestro caso, cuando se conecta a una "base de datos" de Excel, las propiedades extendidas se utilizan para configurar la versión del archivo de Excel.

Para un libro de Excel95, este valor es "Excel 5.0" (sin las comillas); use "Excel 8.0" para Excel 97, Excel 2000, Excel 2002 y ExcelXP.

Importante: Debe usar el proveedor Jet 4.0 ya que Jet 3.5 no es compatible con los controladores ISAM. Si configura el proveedor de Jet en la versión 3.5, recibirá el error "No se pudo encontrar el ISAM instalable".

Otra propiedad extendida de Jet es "HDR =". "HDR = Sí" significa que hay una fila de encabezado en el rango, por lo que Jet no incluirá la primera fila de la selección en el conjunto de datos. Si se especifica "HDR = No", el proveedor incluirá la primera fila del rango (o rango con nombre) en el conjunto de datos.

La primera fila de un rango se considera la fila de encabezado de forma predeterminada ("HDR = Sí"). Por lo tanto, si tiene encabezado de columna, no necesita especificar este valor. Si no tiene encabezados de columna, debe especificar "HDR = No".

Ahora que ya está todo listo, esta es la parte en la que las cosas se vuelven interesantes ya que ahora estamos listos para algún código. Veamos cómo crear un editor simple de hoja de cálculo de Excel usando Delphi y ADO.

Nota: Debe continuar incluso si no tiene conocimientos sobre programación ADO y Jet. Como verá, editar un libro de Excel es tan simple como editar datos de cualquier base de datos estándar.