[.NET] Excel automation (+ uitlezen) in een service

[NickThissen]

Hey,

Ik moet in een windows service een Excel bestand uitlezen. Dit moet elke dag 1 keer na 22.00 gebeuren, totaal automatisch, zonder tussenkomst van een gebruiker. Het Excel bestand wordt elke dag vervangen door een nieuwere versie.

Voor zover ik weet zijn er in .NET (VB op het moment maar C# kan ook) twee methodes om Excel bestanden uit te lezen:

1. Office automation / interop.
2. OleDbConnection (weet niet precies de naam, in ieder geval vergelijkbaar met het werken met een Access database).

Met beide methodes heb ik echter verschillende problemen.


Methode 1: Office automation
Met Office automation kan ik een instantie van Excel openen, daarin een Workbook (= xls bestand) openen, daarin een worksheet (= tabblad) aanvragen, en ten slotte daaruit simpel weg met de Cells property de waarde van verschillende cellen uitlezen.

Dit lijkt allemaal te werken, maar er zijn een aantal problemen. Ten eerste: zodra ik het Excel bestand open lijkt hij automatisch al iets aan te passen. Daardoor komt hij met een popup, hij vraagt of ik de wijzigingen wil opslaan of niet. Dit is een groot probleem want de code wacht nu totdat deze popup gesloten is (Excel sluit niet af voordat de popup weg is natuurlijk). Aangezien er geen gebruiker bij komt kijken, en de service ten alle tijden moet blijven draaien, is dit natuurlijk geen optie, er mag absoluut geen popup komen.

Nou heb ik deze popup kunnen verbergen door een DisplayAlerts property op False te zetten, maar toen ik die oplossing vond kwam ik ook een artikeltje tegen van Microsoft waar ze duidelijk aangeven dat Office automation niet gebruikt moet worden in een omgeving zonder gebruikers, omdat het onstabiel is en er mogelijk dingen fout kunnen gaan (Considerations for server-side Automation of Office).
Dat moet ik natuurlijk niet hebben! Het lijkt er dus op dat Office automation geen goeie oplossing is, tenzij er echt geen andere manier is.


Methode 2: OleDbConnection
Hierbij maak ik gebruik van een OleDbConnection object, met een connection string die ik van www.connectionstrings.com geplukt heb. Met behulp van een OleDbCommand kan ik daarna een SELECT query versturen die de data van een specifieke worksheet ophaalt

Visual Basic:

Dim dt As DataTable

Dim connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";", _
                               Me.ExcelFilePath)
                               
Using conn As New OleDbConnection(connString)
    Using cmd As New OleDbCommand()
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = String.Format("select * from [{0}$]", sheetName)
        
        conn.Open()
        dt = New DataTable()
        dt.Load(cmd.ExecuteReader())
        conn.Close
    End Using
End Using

Return dt

Dit werkt voor een simpele Excel worksheet met wat losse data, maar het werkt niet voor de Excel bestanden waar ik mee moet werken (die komen van een ander bedrijf, ik kan dus niets aan hun format doen).

De bestanden die ik krijg bevatten in de eerste 14 regels wat 'zooi' die ik niet nodig heb. Bijvoorbeeld samengevoegde kolommen, buttons, checkboxes, allemaal dingen waar ik zou verwachten dat deze manier van data laden inderdaad mis kan gaan.

Als ik deze code nou draai dan krijg ik de error "Not a legal OleAut data", wat zoveel betekent dat hij een Excel datum niet kan converteren naar een .NET DateTime. Deze fout komt inderdaad door de eerste 14 rijen, want als ik puur en alleen de data (alles onder de eerste 14 rijen) kopieer naar een nieuw Excel bestand leest hij het wel goed in.

Is het niet mogelijk om in de select query meteen al de eerste 14 regels over te slaan? Het gaat namelijk al mis tijdens de query, niet nadat ik de data uitlees (ik probeer immers pas te lezen vanaf regel 15, maar daar komt de code dus niet eens).


Een oplossing die ik al bedacht had is om een kopie te maken van het in te lezen bestand, en daarvan de eerste 14 regels te verwijderen. Dat zou kunnen werken, maar voor zover ik weet kan dat verwijderen alleen via Office automation, en dan zijn we weer terug bij probleem 1...


Weet iemand hoe ik dit het beste kan oplossen? Ervaringen met Office automation misschien?
Ik had wel al een pakket gevonden dat lijkt te doen wat ik wil, maar het uitlezen van een Excel bestandje kan toch niet zoveel moeite kosten dat ik hier hele pakketen voor moet gaan kopen?


Bedankt!

[NickThissen]

Ok, met behulp van een ander forum is dit inmiddels opgelost (het blijkt dat je in de select query ook meteen een range kan opgeven....). Dit kan dicht denk ik.

[Reptile209]

Nog even 'for the sake of argument': waar komt de Excelsheet vandaan? Komt die uit een ander proces (programma), of wordt daar handmatig in geklust? Als het uit een ander programma komt, zou je daar misschien direct(er) mee kunnen communiceren, zodat je Excel niet als boodschapper nodig hebt. M.a.w.: staar je niet blind op de huidige situatie, misschien is er een makkelijkere oplossing.

[Gadgets]

NPOI

npoi.codeplex.com

[NickThissen]

Reptile209 schreef op donderdag 23 december 2010 @ 20:08:
Nog even 'for the sake of argument': waar komt de Excelsheet vandaan? Komt die uit een ander proces (programma), of wordt daar handmatig in geklust? Als het uit een ander programma komt, zou je daar misschien direct(er) mee kunnen communiceren, zodat je Excel niet als boodschapper nodig hebt. M.a.w.: staar je niet blind op de huidige situatie, misschien is er een makkelijkere oplossing.

Het excel bestand is van een ander bedrijf en wordt door meerdere programma's (of gewoon door mensen, weet ik zo niet) gebruikt / bekeken. Ik kan dus niets veranderen aan de format of zelfs helemaal zonder het bestand werken, ik zit hier aan vast.

Gadgets schreef op donderdag 23 december 2010 @ 20:13:
NPOI

npoi.codeplex.com

Dat ziet er veelbelovend uit... Maar goed, het werkt ondertussen prima dus ik laat het hier maar bij denk ik. Toch bedankt

[Sven_Vdb]

Openxml kan misschien ook nog een oplossing zijn.

[RaZ]

NickThissen schreef op donderdag 23 december 2010 @ 17:11:
Ok, met behulp van een ander forum is dit inmiddels opgelost (het blijkt dat je in de select query ook meteen een range kan opgeven....). Dit kan dicht denk ik.

Dicht gooien doen ze hier niet zo snel, want dit probleem kan volgende week wel weer bij iemand opduiken.

Het zou ook fijn zijn als je ipv meldt dat het is opgelost, ook de oplossing even post, dan ben jij niet de enige die wat aan dit topic heeft

[Sebazzz]

RaZ schreef op donderdag 23 december 2010 @ 21:37:
[...]
Het zou ook fijn zijn als je ipv meldt dat het is opgelost, ook de oplossing even post, dan ben jij niet de enige die wat aan dit topic heeft

De oplossing is erbij gepost: Je moet een range opgeven in de select query. Oplossing is dus methode 2 met een range.

[RaZ]

En ik quote het ook nog gewoon he Tijd voor koffie...

* RaZ schaamt

[NickThissen]

Ik had misschien iets nadrukkelijker kunnen zijn inderdaad... Bij deze

Selecteren van cellen A15 tot V100 uit worksheet met naam 'Sheetname' :

code:

select * from [Sheetname$A15:V100]

[ Voor 22% gewijzigd door NickThissen op 23-12-2010 22:26 ]