Ziel: Ein Excel-File(hier Preisliste) mit coolen Design und möglichst wenig VBA-Befehlen und Aufwand dynamsich am Server erzeugen.
Mit VBA ist es sehr kompliziert, ein schönes Excel-File zu genieren. Tabellen, Farben, Formatierungen, Bilder einfügen usw....
Alles in VBA zu schreiben ist langwierig und den Aufwand eigentlich nicht wert. Einfacher ist es eine Vorlage(unten zu finden) in Excel selbst zu schreiben und Sie auf dem Server abzulegen. Diese Excel-Vorlage öffne ich über ASP und fülle Sie mit aktuellen Werten aus der Datenbank. Die vollstängige Preisliste(unten zu finden) speichere ich dann als neues File am Server ab.
Vorteile:
- Nach belieben kann ein aktuelles Excel-File automatisch geniert werden.
- Änderungen im Design und im Inhalt sind schnell zu machen. Einfach eine neue Vorlage in Excel erstellen und minimale Änderungen im Code vornehmen, fertig.
Hier das Script für die Seite...makeExcel.asp
1. Teil:
Überprüfen von Existenz der kompletten Preisliste(xlsfull.xls) und evtl. Löschen der Datei. Dies geschieht mit Hilfe des Scripting.FileSystemObjektes
<%@ Language=VBScript %>
<%
strVorlage = Server.MapPath("./Excel/xlsvorlage.xls")
strFull = Server.MapPath("./Excel/xlsfull.xls")
set Datei = Server.CreateObject("Scripting.FileSystemObject") if Datei.FileExists(strFull) = true then
Datei.DeleteFile strFull
end if |
2. Teil:
Werte aus der Datenbank lesen und in ein Array schreiben
strSQL = "SELECT * FROM Products"
set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset") conn.Open "Provider=SQLOLEDB.1;", "admin", ""
rs.Open "Products",conn,adOpenKeyset,adLockOptimistic varCount = rs.RecordCount-1
dim Arr()
redim Arr(varCount,2)
rs.MoveFirst
if not rs.EOF and not rs.BOF then
do while not rs.EOF
Arr(i,0) = rs.Fields("ProductID")
Arr(i,1) = rs.Fields("ProductName")
Arr(i,2) = rs.Fields("UnitPrice")
rs.MoveNext
i = i + 1
loop
else
Response.End
end if
rs.Close
set rs = nothing
conn.Close
set conn = nothing |
3. Teil: - Excel-Generierung
Und jetzt die Excel-Generierung. TIP: Vergleichen Sie den Code mit der Vorlage(unten), um Ihn besser zu verstehen.
Set oExcel = CreateObject("Excel.Application") ' --- Server.CreateObject ist schneller
With oExcel
'--- Excel-Vorlage öffnen
.Workbooks.Open (strVorlage)
'--- kopieren und einfügen der Tabellen- und DM-Formatierung für alle notwendigen Bereiche
.ActiveSheet.Range("C7:E7").Copy
.ActiveSheet.Range("C8:C" & cint(varCount + 7)).PasteSpecial
i = 0
'--- durchlaufen des Arrays
for i = 0 to varCount
'--- Werte in Zellen des Excel-Sheets schreiben.
'--- i+7 --> weil die Tabelle in Excel im Range("C7") beginnt
.ActiveSheet.Range("C" & cint(i + 7)) = arr(i,0)
.ActiveSheet.Range("D" & cint(i + 7)) = arr(i,1)
.ActiveSheet.Range("E" & cint(i + 7)) = arr(i,2)
next
'--- Breite der Spalte D wird auf "Benötigte Breite" vergrößert(od. verkleinert)
'.Columns("D:D").EntireColumn.AutoFit
'--- Speichern der Datei in neuem File
.ActiveWorkbook.SaveAs strFull
'--- schließen des ActiveWorkbooks - False als Eigenschaft für Änderungen speichern
.ActiveWorkbook.Close False
'--- Quit schließt die Excel-Anwendung
'--- (Wichtig!!! ansonsten bleibt Excel im Speicher(ca.5 MB)
.Quit
End With
'--- Objekt zerstören
Set oExcel = Nothing
%> |
Unter verlinkte Dokumente finden Sie den kompletten Source-Code mit den Excel-Files.
Die Excel-Generierung findet nur im 3. Teil statt! Teil 1 und 2 sind nur zur Ausarbeitung.