Toegang tot en beheer van MS Excel-sheets met Delphi

In deze stapsgewijze handleiding wordt beschreven hoe u verbinding kunt maken met Microsoft Excel, bladgegevens kunt ophalen en de gegevens kunt bewerken met behulp van DBGrid. U vindt ook een lijst met de meest voorkomende fouten die tijdens het proces kunnen optreden, plus hoe u hiermee kunt omgaan.

Wat hieronder wordt behandeld:

• Methoden voor het overbrengen van gegevens tussen Excel en Delphi. Hoe verbinding te maken met Excel met ADO (ActiveX-gegevensobjecten) en Delphi.
• Een Excel-spreadsheet-editor maken met Delphi en ADO
• Gegevens ophalen uit Excel. Verwijzen naar een tabel (of bereik) in een Excel-werkmap.
• Een discussie over Excel veld (kolom) types
• Hoe Excel-bladen te wijzigen: rijen bewerken, toevoegen en verwijderen.
• Gegevens overbrengen van een Delphi-toepassing naar Excel. Een werkblad maken en vullen met aangepaste gegevens uit een MS Access-database.

Microsoft Excel is een krachtige spreadsheetcalculator en tool voor gegevensanalyse. Aangezien rijen en kolommen van een Excel-werkblad nauw verband houden met de rijen en kolommen van een databasetabel, veel ontwikkelaars vinden het gepast om hun gegevens voor analyse naar een Excel-werkmap te transporteren; en daarna gegevens terughalen naar de toepassing.

De meest gebruikte methode voor gegevensuitwisseling tussen uw toepassing en Excel is Automatisering. Automatisering biedt een manier om Excel-gegevens te lezen met behulp van het Excel-objectmodel om in het werkblad te duiken, de gegevens eruit te halen en deze weer te geven in een rasterachtige component, namelijk DBGrid of StringGrid.

Automatisering biedt u de grootste flexibiliteit voor het lokaliseren van de gegevens in de werkmap, evenals de mogelijkheid om het werkblad op te maken en verschillende instellingen te maken tijdens runtime.

Om uw gegevens zonder automatisering van en naar Excel over te dragen, kunt u andere methoden gebruiken, zoals:

• Schrijf gegevens in een door komma's gescheiden tekstbestand en laat Excel het bestand in cellen parseren
• Gegevens overdragen met DDE (Dynamic Data Exchange)
• Breng uw gegevens over van en naar een werkblad met ADO

Omdat Excel compatibel is met JET OLE DB, kunt u er verbinding mee maken met Delphi via ADO (dbGO of AdoExpress) en vervolgens de werkbladgegevens in een ADO-gegevensset door een SQL-query uit te voeren (net zoals u een gegevensset voor elke database zou openen tafel).

Op deze manier zijn alle methoden en functies van het ADODataset-object beschikbaar om de Excel-gegevens te verwerken. Met andere woorden, met behulp van de ADO-componenten kunt u een applicatie bouwen die een Excel-werkmap als database kan gebruiken. Een ander belangrijk feit is dat Excel niet meer werkt ActiveX-server. ADO voert een proces uit en bespaart de overhead op kostbare out-of-process-oproepen.

Wanneer u via ADO verbinding maakt met Excel, kunt u alleen onbewerkte gegevens van en naar een werkmap uitwisselen. Een ADO-verbinding kan niet worden gebruikt voor bladopmaak of het implementeren van formules in cellen. Als u uw gegevens echter overbrengt naar een werkblad dat vooraf is opgemaakt, blijft de indeling behouden. Nadat de gegevens vanuit uw toepassing in Excel zijn ingevoegd, kunt u elke voorwaardelijke opmaak uitvoeren met een (vooraf opgenomen) macro in het werkblad.

U kunt via ADO verbinding maken met Excel met de twee OLE DB Providers die deel uitmaken van MDAC: Microsoft Jet OLE DB Provider of Microsoft OLE DB Provider voor ODBC-stuurprogramma's. We zullen ons richten op Jet OLE DB Provider, die kan worden gebruikt om toegang te krijgen tot gegevens in Excel-werkmappen via installeerbare ISAM-stuurprogramma's (Indexed Sequential Access Method).

Tip: Zie de Beginnerscursus naar Delphi ADO Database Programming als je nieuw bent bij ADO.

De eigenschap ConnectionString vertelt ADO hoe verbinding moet worden gemaakt met de gegevensbron. De waarde die wordt gebruikt voor ConnectionString bestaat uit een of meer argumenten die ADO gebruikt om de verbinding tot stand te brengen.

In Delphi omvat de component TADOConnection het ADO-verbindingsobject; het kan worden gedeeld door meerdere ADO-gegevensset (TADOTable, TADOQuery, etc.) componenten via hun verbindingseigenschappen.

Om verbinding te maken met Excel, bevat een geldige verbindingsreeks slechts twee extra stukjes informatie - het volledige pad naar de werkmap en de Excel-bestandsversie.

Een legitieme verbindingsreeks kan er zo uitzien:

ConnectionString: = 'Provider = Microsoft. Jet. OLEDB.4.0; Gegevensbron = C: \ MyWorkBooks \ myDataBook.xls; Uitgebreide eigenschappen = Excel 8.0; ';

Wanneer u verbinding maakt met een externe database-indeling die wordt ondersteund door de Jet, moeten de uitgebreide eigenschappen voor de verbinding worden ingesteld. In ons geval worden bij het verbinden met een Excel "database" uitgebreide eigenschappen gebruikt om de Excel-bestandsversie in te stellen.

Voor een Excel95-werkmap is deze waarde "Excel 5.0" (zonder de aanhalingstekens); gebruik "Excel 8.0" voor Excel 97, Excel 2000, Excel 2002 en ExcelXP.

Belangrijk: U moet de Jet 4.0-provider gebruiken, omdat Jet 3.5 de ISAM-stuurprogramma's niet ondersteunt. Als u de Jet Provider instelt op versie 3.5, ontvangt u de foutmelding 'Kan ISAM niet vinden'.

Een andere uitgebreide eigenschap van Jet is "HDR =". "HDR = Ja" betekent dat er een koprij in het bereik is, zodat de Jet de eerste rij van de selectie niet in de gegevensset opneemt. Als "HDR = No" is opgegeven, neemt de provider de eerste rij van het bereik (of benoemd bereik) op in de gegevensset.

De eerste rij in een bereik wordt standaard beschouwd als de koprij ("HDR = Ja"). Daarom hoeft u deze waarde niet op te geven als u een kolomkop hebt. Als u geen kolomkoppen hebt, moet u "HDR = No" opgeven.

Nu je helemaal klaar bent, is dit het gedeelte waar dingen interessant worden, omdat we nu klaar zijn voor wat code. Laten we eens kijken hoe we een eenvoudige Excel-spreadsheet-editor kunnen maken met Delphi en ADO.

Notitie: U moet doorgaan, zelfs als u geen kennis heeft van ADO en Jet-programmering. Zoals u zult zien, is het bewerken van een Excel-werkmap net zo eenvoudig als het bewerken van gegevens uit elke standaarddatabase.