Skrevet af Tobias Hinnerup den 15. september 2006. Sidst redigeret den 18. januar 2006. © Alle rettigheder forbeholdt.

• ↑ Brug Excel som database med ASP og ADO

Er du blandt de mange der har brugt Excel til at indtaste data i, eller har du bare opdaget at der faktisk ofte ligger værdifulde og anvendelige data i regneark? Selvfølgelig skal disse data ikke ligge og samle støv - og heldigvis kan de nemt tilgåes i ASP gennem ADO.

Så længe man holder sig for øje, at Excel ikke ER en database, er der ofte fornuft i at bruge programmet til at lagre og specielt analysere data. Her gennemgås, hvordan man forbinder til Excel med ADO, samt hvordan man henter og gemmer data.

Første trin i raketten giver mening til Microsoft's ide med Universal Data Access (UDA): På samme måde som man normalt opretter en forbindelse til en database med ADO, kan man oprette en forbindelse til et Excel-ark enten gennem Jet eller Open DataBase Connecticity (ODBC) (begge OLE DB providere der kommer med Microsoft Data Access Components (MDAC).

Forbind med Jet


Dim xlCn
Set xlCn = Server.CreateObject("ADODB.Connection")

With xlCn
.Provider         = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & Server.MapPath("Regneark.xls") & _
                    ";Extended Properties=""Excel 8.0; HDR=No;"""
.Open
End With

Når man forbinder med Jet, er de grundliggende informationer man skal bruge filnavn og sti til regnearket, og versionen på den Excel der oprettede arket. Er det et Excel 95 ark, skal specificeres version 5.0 (selvom 95 faktisk er version 7.0). Er det et ark fra Excel 97, 2000 eller 2002 (XP) skal angives version 8.0.

Hvorvidt HDR sættes til No eller Yes afgør om den første række i regnearket skal bruges til at navngive felter i de recordset's man åbner på forbindelsen, eller ej. Er der ikke angivet navne, kan felterne kun tilgåes ved deres placering i recordset'et.

Bemærk, at det SKAL være Jet version 4 eller nyere der benyttes. Jet version 3.51 understøtter ikke Jet ISAM driverne, der benyttes til at forbinde med Excel.

Forbind med ODBC

Har man et Data Source Name (DSN) oprettet til sit Excel-ark, kan man forbinde til den. En sådan forbindelse kan f.eks. oprettes gennem ODBC-manageren i Windows.

Først skal oprettes en ny "Data Source", hvor Microsoft Excel driveren skal vælges, og derefter skal denne sættes op. Det vil bland andet sig, at det skal angives hvilket regneark den skal henvise til.

I setup'et kan også vælges hvilken version af Excel's data man ønsker at arbejde på.

En god idé er også at angive både et beskrivende DSN og en sigende beskrivelse - efterhånden som antallet af "projekter" på ens computer vokser, kan det ellers hurtigt blive uoverskueligt at finde rundt i ODBC manageren.

Bemærk, at det i ODBC Data Source Administrator'en under options kan vælges om der skal være skriveadgang eller ikke. Som standard er der valgt "Read Only", så husk at ændre det, hvis du også vil ændre data i regnearket.

Dér kan også angives hvor mange rækker der skal skannes i arket, for at afgøre data-typen af hver kolonne. Dette afgøres til enten at være numerisk eller tekst, afhængigt af simpel statistik. 0 angiver alle rækker i arket. Bemærk, at denne feltbestemmelse bliver foretaget på såvel Jet som ODBC-forbindelser.

Når først forbindelsen er oprettet, er det en smal sag at forbinde til den med Visual Basic Script (VBS), på nøjagtig samme måde som man forbinder til andre DSN.


With xlCn
.Provider = "MSDASQL"
.ConnectionString = "DSN=ExcelDSN;"
.Open
End With

Forbindelsen kan også oprettes via ODBC uden at oprette et DSN.

Denne fremgangsmåde er normalt mindre anbefalelsesværdig, idet en sådan forbindelse er sværere at vedligeholde og indeholder større fejlmuligheder - men arbejder man f.eks. på et webhotel er det ofte mere praktisk end et DSN.

Vær opmærksom på, at ReadOnly angives med True/False, hvorimod FirstRowHasNames angives med 0/1 hvor nul betyder falsk. Den egenskab der i ODBC manageren hedder Rows to Scan hedder her MaxScanRows, men fungerer ellers på samme måde.


With xlCn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
                    "DBQ=C:\Documents\asdf.xls; ReadOnly=False;" & _
                    "FirstRowHasNames=1;MaxScanRows=8;"
.Open
End With

Hent data fra Excel

Når forbindelsen er åbnet, kan data hentes, som om hvert ark eller navngivne område i filen er en tabel. Bemærk, at ark-navne skal angives efterfulgt af et dollar-tegn, og derfor skal indrammes i firkantede klammer. Benyttes navngivne områder (uden specialtegn i navnet) som tabeller, kan dollar-tegnet og de firkantede klammer udelades.


Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

'Don't use client-side cursors - no updates will be allowed
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM [Ark1$]", xlCn, adOpenDynamic, adLockOptimistic, adCmdText

'Open range (note: non-adjacent ranges cannot be opened via JET)
'rs.Open "SELECT * FROM [Ark1$A1:10]", xlCn, adOpenDynamic, adLockOptimistic, adCmdText

'Open named range (note: named non-adjacent ranges cannot be opened via JET)
'rs.Open "SELECT * FROM [CustomersRange]", xlCn, adOpenDynamic, adLockOptimistic, adCmdText

Det returnerede recordset kan anvendes som alle andre recordsets. For eksempel kan udskrives indholdet af første og anden kolonne i arket.


Response.Write "<h1>Indhold af kolonne A og B i ""Ark1""</h1>"
Response.Write "<table cellspacing=""10"">" & vbCrLf
Response.Write "	<tr>" & vbCrLf
Response.Write "		<th>" & vbCrLf
Response.Write "			Column 1" & vbCrLf
Response.Write "		</th>" & vbCrLf
Response.Write "		<th>" & vbCrLf
Response.Write "			Column 2"  & vbCrLf
Response.Write "		</th>" & vbCrLf
Response.Write "	</tr>" & vbCrLf

Do Until rs.EOF
	Response.Write "	<tr>" & vbCrLf
	Response.Write "		<td>" & vbCrLf
	Response.Write "			" & rs(0)  & vbCrLf
	Response.Write "		</td>" & vbCrLf
	Response.Write "		<td>" & vbCrLf
	Response.Write "			" & rs(1)  & vbCrLf
	Response.Write "		</td>" & vbCrLf
	Response.Write "	</tr>" & vbCrLf
	rs.MoveNext
Loop
Response.Write "</table>" & vbCrLf

Her er det måske en god idé at eksperimentere lidt med hvad der sker med kolonner der indeholder en blanding af tekst og tal, for at få indblik i hvilke resultater man trækker ud i hvilke tilfælde. Hermed en øvelse til den nysgerrige læser - og et godt råd om at undgå blandede kolonner i videst muligt omfang.

Send data til Excel


Do Until rs.EOF
rs(0) = Chr(65+i)
rs(0) = i
rs.Update()

i = i + 1
rs.MoveNext
Loop

rs.AddNew()
rs(2) = "Lavet af"
rs(4) = "Tobias Hinnerup"
rs.Update()

Lige så nemt som det var at trække data ud, lige som nemt er det at sætte det ind. Skrivning forudsætter naturligvis, at IUSR på maskinen har skriverettigheder på filen, eller den brugeren via IIS'en er identificeret til een, der har skriverettigheder på filen.

Bemærk, at der er en kendt fejl i driveren, der gør det umuligt at skrive til navngivne områder, i fald disse er udspændt af én celle.

På samme måde kan data tilføjes, med de gammelkendte ADO-metoder. Husk altid at kalde Update() på recordset'et inden du forlader den ændrede række, når der er blevet indsat nye værdier, og bemærk, at nye rækker med AddNew() tilføjes i bunden af arket.

Mere avancerede operationer på regnearket kan også lade sig gøre, men kræver for de flestes tilfælde at man bruger automation i stedet, til at kommunikere med Excel. Sådanne ting kunne f.eks. være at kalde makroer, printe, gemme under andet navn eller generere grafer.

... men det er alt sammen stof til en anden artikel.


If rs.State = adStateOpen Then rs.Close
If xlCn.State = adStateOpen Then xlCn.Close

Afslutningsvis

Som altid, er det naturligvis fornuftigt at lukke recordset og forbindelse, når man er færdig med at benytte dem.

Enjoy your visit.

Tobias Hinnerup
18. januar 2006