Glengamoi (Forum) · AspHeute · .NET Heute (RSS-Suche) · AspxFiles (Wiki) · .NET Blogs

Ein Excel-Diagramm am Server generieren und zum Client schicken

Geschrieben von: Christian Holm
Kategorie: ASP Tricks

This printed page brought to you by AlphaSierraPapa

Diesmal wollen wir nicht nur ein MS-Excel Sheet am Server generieren, sondern einen Schritt weiter gehen und die Daten auch noch visualisieren - und das alles am Web Server. Der Client erhält dann die Excel-Datei mit den aus der Datenbank geholten Datensätze samt dem erstellten Diagramm.

Um das in diesem Artikel verwendete Beispiel selbst ausprobieren zu können, müssen allerdings einige Vorbereitungen getroffen werden. So muß am Webserver, auf dem die Datei generiert wird, Microsoft Excel installiert sein. Da der heutige Sourcecode auf dem Artikel Ein Excel-Sheet am Server generieren und zum Client schicken basiert, gelten auch die gleichen Voraussetzungen hierfür.

Damit der Artikel nicht nur etwas erweitert wurde, habe ich auch ein paar kleine Verbesserungen am Sourcecode vorgenommen. Man darf also gespannt sein!

Das Einlesen der Daten

Im Gegensatz zum Sourcecode des Vorgängerartikels überlappen sich die Instanzen der Datenbankverbindung mit dem des Excel Objektes nicht mehr. Das hat den Vorteil, daß wenn Probleme bei der Datenakquirierung auftreten (z.B. Serverbreakdown) nicht mehr zwei Objekte gleichzeitig offen sind - was vor allem bei out-of-process Komponenten wie Excel ein enormer Vorteil ist.

Die Daten aus der Access-Datenbank werden daher jetzt vorerst in einem Array gespeichert. Das heißt, ich kann nun ruhig die Verbindung zur Datenbank nach Beendigung des Transfers schließen und erstelle erst jetzt die Instantz des Excel Objektes.

Sehen wir uns nun die neue Variante an (GenXlsChart.asp):

...
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConn = strConn & Server.MapPath("Sales00.mdb") 
strQuery = "SELECT ProductName,ProductIncome FROM ProductSales"

objConn.Open strConn

objRS.Open strQuery, objConn

If Not objRS.EOF and Not objRS.BOF Then
  arrData = objRS.GetRows()
Else
  bNoRecords = True
End If
...

Mit der GetRows Methode werden die Datensätze gewonnen und im Array arrData gespeichert. Wenn dieser Vorgang abgeschlossen ist, kann man also die Verbindung zur Datenbank schließen und die für die Verbindung benötigten Objekte wieder sicher zerstören.

Nun kann man wieder auf die im Vorgängerartikel gezeigten Methoden zurückgreifen um z.B. Überschriften, Beschreibungstext, etc. in das Tabellenblatt einzufügen. Da dies ja Thema des vorigen Artikels war, möchte ich dies hier nicht noch einmal erläutern.

Da die Datensätze in einem Array gespeichert wurden, muß man natürlich das Zuweisen in die entsprechenden Zellen ein wenig abändern. Dies habe ich einfach mit einer For ... Next Schleife erledigt:

For i = 0 To Ubound(arrData,2)
    xlBook.Sheets("Data").Cells(iRow+i,1).Value = arrData(0,i)
    xlBook.Sheets("Data").Cells(iRow+i,2).Value = arrData(1,i)
    xlBook.Sheets("Data").Cells(iRow+i,2).NumberFormat = "$#,##0.00"
Next

Beachten Sie, daß das Array in der umgekehrten Reihenfolge befüllt wurde. Daher stehen die auszulesenden Indizes in der zweiten Spalte der Klammern des Arrays:

...
xlBook.Sheets("Data").Cells(iRow+i,1).Value = arrData(0,i)
...

Die Diagramm-Generierung

Nun sind wir endlich soweit, daß wir die Daten visualisieren können. Ich habe mir für diesen Artikel zwei unterschiedliche Typen ausgesucht. Ein 3D Pie Chart - Kuchen-Diagramm - in Explosionsdarstellung (xl3DPieExploded) und ein 3D Column Chart - Säulen Diagramm - (xl3DColumnClustered).

Beim Codieren ist auf die Syntaxunterschiede zu achten. Denn mühsamerweise verwenden VBA und VBScript unterschiedliche Syntaxe im Bezug auf das Excel Objekt. Was sich also auf den ersten Blick als logisch erweist, da es ja zwei unterschiedliche Sprachformen sind, wird dann beim Codieren zur Qual. Also Makro aufzeichen lassen und in ASP Wrapper einfügen ist nicht - und auch wenn es so schön einfach wäre.

Erstellen wir nun den allgemeinen Chart-Teil:

xlBook.Charts.Add
xlBook.ActiveChart.Name = "BarGraph"
xlBook.Charts("BarGraph").SetSourceData (xlBook.Sheets("Data").Range("A4:B9"))

Hier wird ein neues Diagramm (Chart) innerhalb des xlBook Objektes erstellt und danach wird dem Chart der Name "BarGraph" zugewiesen. Für die Erstellung eines neuen Diagramms wird deshalb Charts (in der Mehrzahl) verwendet, da es eine Collection signalisieren soll.

Die Festlegung des Diagrammtyps

Diagrammtypen gibt es in Excel nun viele. Um sie auch außerhalb von VBA verwenden zu können, benötigen Sie die zum Diagramm zugehörige numerische Konstante. Der Einfachheit halber habe ich, wie schon erwähnt, nur zwei aus den vielen Möglichkeiten die Excel bietet ausgewählt.

Für das 3D Pie Chart müssen Sie folgendermaßen vorgehen:

'--- 3DPieExploded
xlBook.Charts("BarGraph").ChartType = 70
xlBook.Charts("BarGraph").PlotBy = 2

Dieses 3D Pie Chart (es gibt auch andere Kuchen-Diagramm Typen) hat als Konstante den Wert 70. Damit das Diagramm auch richtig angezeigt wird, müssen wir noch die Plot-Order setzen. Hier soll nämlich das Diagramm gemäß den Spalten (Columns) gezeichnet werden. Die hierfür verwendete Excel Konstante xlColumns hat den numerischen Wert von 2.

Nach dem Ausführen des ASP Scripts auf einem Web Server - der den obig genannten Anforderungen entspricht - und lokaler Abspeicherung der Excel Datei sieht das Kuchen-Diagramm dann so aus:

Im anderen Fall, der Darstellung als 3D Säulen Diagramm erhält man folgendes Ergebnis:

Um die Säulendarstellung zu erstellen ist folgender Code notwendig:

'--- 3DColumns
xlBook.Charts("BarGraph").ChartType = 54
xlBook.Charts("BarGraph").PlotBy = 1
xlBook.Charts("BarGraph").HasAxis(1) = 0

Für diesen Diagrammtyp und die vorliegenden Daten ist die reihenmäßige Anordnung zu wählen (xlRows = 1). Da wir bei dieser Darstellung eigentlich keine Kategorienachsenbeschriftung (xlCategory = 1) benötigen, setzen wir sie auf 0 (entspricht False).

Damit wären wir schon fast fertig. Damit es bei der Generierung der Excel Dateien am Server nicht zu Problemen kommt, lösche ich eine eventuell vorhandene Datei bevor ich die neue, generierte Datei am Web Server speichere. Dies ist einfach mit dem FileSystemObject gelöst:

Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(Server.MapPath(strFileName))) Then
   Set MyFile = fso.GetFile(Server.MapPath(strFileName))
   MyFile.Delete
End If

xlSheet.SaveAs Server.MapPath(strFileName)

Zuerst wird mit der FileExists Methode überprüft ob es eine Datei gleichen Namens gibt und für den Fall, daß die Expression der If-Verzweigung True zurück gibt, wird sie mit der Delete Methode des FileSystemObjects gelöscht.

Abschließend, gleich wie im vorigen Artikel, wird die Excel-Datei mit der FileCache 2.1 Komponente von AlphaSierraPapa.com zum Client geschickt.

Schlußbemerkung

Nun sind wir schon ein größeres Stück weiter - die Daten werden nicht nur übersichtlich in einer Tabelle gespeichert und textuell formatiert, sondern auch mit Hilfe der Charts Collection des Excel Objektes in Form eines auswählbaren, passenden Diagrammtyps dargestellt.

This printed page brought to you by AlphaSierraPapa

Download des Codes

Klicken Sie hier, um den Download zu starten.
http://www.aspheute.com/code/20010307.zip

Links zu anderen Sites

FileCache 2.1 Komponente
http://www.alphasierrapapa.com/IisDev/Components/FileCache/

©2000-2006 AspHeute.com
Alle Rechte vorbehalten. Der Inhalt dieser Seiten ist urheberrechtlich geschützt.
Eine Übernahme von Texten (auch nur auszugsweise) oder Graphiken bedarf unserer schriftlichen Zustimmung.