Blog Home  Home Feed your aggregator (RSS 2.0)  
artiso Blog - Excel
Neues rund um's Thema .Net
 
 Sunday, May 11, 2008

Ich wollte aus Excel auf einen Webservice zugreifen. Also kurz gegoogelt (oder gelived ;-)), eigentlich gar nicht so schwer...

Aber wie so oft liegt der Teufel im Detail und es waren doch ein paar Kleinigkeiten zu beachten, deshalb hier nochmals der komplette Lösungsweg:

Zunächst habe ich einen WebService erstellt, zum Testen was ganz triviales, eigentlich das Webservice Template nur noch um den Parameter Name erweitert:

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
    public Service () 
    {
    }

    [WebMethod]
    public string HelloWorld(string Name) {
        return "Hello World " + Name;
    }    
}

 

Die URL um auf den Webservice mit ausgefülltem Parameter zuzugreifen lautet http://localhost:49408/WebSite2/Service.asmx/HelloWorld?Name=Test, jedoch funktioniert das standardmäßig noch nicht. Wir müssen erst noch in der web.config folgenden Eintrag hinzufügen:

<webServices>
    <protocols>
        <add name="HttpGet"/>
        <add name="HttpPost"/>
    </protocols>
</webServices>

Nun bekommen wir mit diesem Aufruf den XML-Response des Webservices direkt zurückgegeben. Nun wollen wir den Webservice aus VBA aufrufen.

Sub CallWebService()
    Dim MSXML As New MSXML2.DOMDocument
    Dim strAnfrage As String
    strAnfrage = "http://localhost:49408/WebSite2/Service.asmx/HelloWorld?Name=Test"
    With MSXML
        .async = False
        .preserveWhiteSpace = False
        .validateOnParse = True
        .resolveExternals = False
    End With

    If MSXML.Load(strAnfrage) = True Then
        Response = MSXML.DocumentElement.Text
    Else
        Response = "Fehler"
    End If
End Sub
 
Damit das funktioniert müssen wir noch die Bibliothek "Microsoft XML, vx.0" einfügen. Wir werten im Moment das XML-Dokument sehr einfach aus, da wir davon ausgehen, dass der Rückgabewert einfach als Textim XML-Dokument zurückgegeben wird. Damit können wir einfach mit MSXML.DocumentElement.Text den gewünschten Wert auslesen. Natürlich können auf diese Weise auch komplexer Rückgabewerte ausgewertet werden, aber das wollen wir hier nicht näher beleuchten.
Sunday, May 11, 2008 8:54:13 PM (Mitteleuropäische Zeit, UTC+01:00)  #    Comments [2]    |   | 
 Friday, June 22, 2007

In Excel kann man ein Textfeld anlegen und diesem einen variablen Inhalt geben. Hierzu markiert man das Textfeld und gibt in der Formelzeile z.B. einen Verweis auf eine Zelle ein (=A1). In A1 kann nun eine beliebige Formel stehen, z.B. ="Summe: " & SUMME(A2:A20). Möchte man in den text einen Zeilenumbruch einfügen, dann kann man die Funktion ZEICHEN(10) verwenden, also z.B. = "Summe 2006: " & SUMME(A2:A20) & ZEICHEN(10) & "Summe 2007: " & SUMME(B2:B20)

Friday, June 22, 2007 4:22:39 PM (Mitteleuropäische Zeit, UTC+01:00)  #    Comments [0]    | 
 Wednesday, May 09, 2007

Gruppierungen in Excel sind eine feine Sache. Möchte man allerdings untenstehendes Beispiel gruppieren, ergibt sich ein Problem.

Die Gruppenzeile soll in diesem Fall oben stehen. Standardmäßig geht Excel aber davon aus, dass die unterste Zeile einer Gruppe bei einer eingeklappten Gruppierung übrig bleibt. Damit sieht dann das Resultat in dem Beispiel so aus, dass die Gruppe gar nicht mehr angezeigt wird:

Es gibt eine Möglichkeit, dies über eine Einstellung zu ändern. In Excel 2003 findet man die Menü Daten / Gruppierung und Gliederung / Einstellungen. In Excel 2007 ist die Einstellung etwas versteckt. Unter Data befindet sich im Bereich Outline rechts unten ein kleines Kästchen.

 

Klickt man darauf, dann öffnet sich ein Fenster, in dem man nun die Option Summary rows below detail abwählen kann.


Damit kann man die Gruppierung nun wie gewünscht einstellen.

Noch ein kleiner Tipp: Mit Shift + Alt + Pfeil rechts kann man die Gruppierung auch per Tastatur für die aktuell markierten Zeilen einstellen. Shift + Alt + Pfeil links hebt die entsprechende Gruppierung auf.

Wednesday, May 09, 2007 1:06:20 PM (Mitteleuropäische Zeit, UTC+01:00)  #    Comments [0]    | 
 Sunday, January 14, 2007

Mit OleDB lassen sich sehr einfach Excel-Dateien lesen. Man macht sich einen Connectionstring der ungefähr so aussieht:

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelFileName & ";Extended Properties='Excel 8.0;HDR=No;"

Nun kann man eine ganz normale Connection öffnen und wie gewohnt z.B. über ein Select-Statement ein DataSet befüllen.

Dim conn As OleDbConnection = New OleDbConnection(connectionString)
conn.Open()
Dim dt As DataTable = New DataTable()
Dim oleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Tabelle1]", conn)
oleDbDataAdapter.Fill(dt)
oleDbDataAdapter.Dispose()
oleDbDataAdapter =
Nothing
conn.Close()

Das Problem ist dabei, wenn in Excel z.B. Integer und Textwerte in einer Spalte gemischt stehen und die Integerwerte dabei überwiegen, wird diese Spalte als Integer angelegt und die Textwerte stehen im Dataset dann als NULL. Das kann man umgehen, wenn man am Connectionstring den Parameter IMEX=1 anhängt. Damit wird ein "ImportMixedTypes"-Mode aktiviert, der gemischte Spalten immer als Text interpretiert.

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.tbImportdatei.Text & ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'"

Das Problem bei der ganzen Sache ist nur, dass nun standardmäßig die ersten 8 Zeilen geprüft werden. Sind diese alle Integers, wird die Saplte auch als Integer definiert, auch wenn weiter unten noch Textwerte kommen. Man findet hier an verschiedenen Stellen den Hinweis auf einen weiteren Parameter MaxScanRows. Dieser scheint aber nicht zu funktionieren, wie man auch aus diesem MSDN-Artikel ersehen kann. Statt dessen muss man die Einstellung in der Registry vornehmen, wenn man eine andere Anzahl prüfen möchte. Hierzu passt man im Zweig

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

 

den Parameter TypeGuessRows an. Dieser Wert kann auf 1-16 Zeilen eingestellt werden. Ein Wert von 0 bedeutet dass die ersten 16384 Zeilen geprüft werden, um festzustellen, ob der mixed Mode verwendet werden muss oder ob der Datentyp eindeutig bestimmt werden kann. Dass das eigentlich keine saubere Lösung ist, ist klar. Wer will schon seinen Kunden zumuten, in der Registry rumzupfuschen? Aber es scheint im Moment keine bessere Lösung zu geben.

HDR=No bedeutet im obigen Fall übrigens, dass die Excel-Datei keine Header-Zeile hat.

Sunday, January 14, 2007 4:23:55 PM (Mitteleuropäische Zeit, UTC+01:00)  #    Comments [0]    |   | 
Copyright © 2008 Thomas. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.
Pick a theme: