Welcome
Guest
, you are in:
<root>
•
Anmelden
CP Wiki
Navigation
¶
CP Solutions
Startseite
domizil+
Startseite
Administration
Administration
Neue Seite
Alle Seiten
Kategorien
Navigations-Pfade
Datei Manager
Neuer Benutzer
Suche im wiki
»
Zurück
Automatisierte Excel vom SQL Server
Modified on 10/06/2009 14:32
by Manuel Siebert
Categorized as
Nicht kategorisiert
((([image| |http://www.cp-austria.at/website/DesktopModules/Wiki/images/Home.gif|MainPage] » [MainPage|Startseite] » [netFramePlattform|netFrame als Plattform Menü] » [TippsundTricks|Tipps und Tricks] » Automatisierte Excel vom SQL Server | {searchbox} [Search.aspx?FullText=1|Erweiterte Suche]))) {BR} *'''Achtung:''' Um diesen Vorgang auszuführen muss Excel auf dem SQL Server installiert sein!{BR}{BR} {BR} * Als Erstes müssen wir eine Excelinstanz öffnen, dies geschieht folgendermaßen:{BR}{BR} <code sql>DECLARE @xlApp integer, @rs integer EXECUTE @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT</code> {BR} Mit diesem Code haben wir das Excel Programm aufgerufen. In diesem Fall steuert die Variable @xlApp das Programm.{BR} Es ist sehr nützlich die Eigenschaften "ScreenUpdating" und "DisplayAlerts" zu deaktivieren.{BR}{BR} '''ScreenUpdating''' deaktiviert erhöht die Geschwindigkeit des Codes und ansehen werden wir uns das Excel sowieso nicht.{BR} '''DisplayAlerts''' deaktiviert schützt uns vor Exceldokumenten die eine Eingabe des Users erfordern.{BR}{BR} <code sql>EXECUTE @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False' EXECUTE @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'</code>{BR}{BR} Nun benötigen wir die Möglichkeit um Arbeitsmappen zu öffnen.{BR}{BR} <code sql>DECLARE @xlWorkbooks integer EXECUTE @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTUT</code>{BR}{BR} Nun müssen wir eine Entscheidung treffen. Öffnen wir eine bestehende Arbeitsmappe oder erstellen wir eine Neue?{BR}{BR}{br} Um eine bestehende Arbeitsmappe zu öffnen.{br}{br} <code sql>DECLARE @xlWorkbook integer EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, 'C:\Myspreadsheet.xls'</code>{br}{br} Um eine neue Arbeitsmappe zu erstellen.{br}{br} <code sql>DECLARE @xlWorkBook integer EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkBooks, 'Add', @xlWorkBook OUTPUT, -4167</code>{br}{br} '''-4167''' ist der Wert der Variable '''xlWBATWorksheet''', die Excel auffordert eine neue Arbeitsmappe anzulegen.{br}{br} Da wir nun eine Arbeitsmappe haben benötigen wir noch ein Arbeitsblatt{br}{br} <code sql>DECLARE @xlWorkSheet integer EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkBook, 'ActiveSheet', @xlWorkSheet OUTPUT</code>{br}{br} Nun müssen wir die letzte Zeile des Arbeitsblattes herausfinden. Dies kann Excel uns verraten.{br}{br} <code sql>DECLARE @xlLastRow integer EXECUTE @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, 'Cells.SpecialCells(11).Row', @xlLastRow OUTPUT</code>{br}{br} Wenn wir möchten verrät Excel uns auch die letzte Spalte.{br}{br} <code sql>DECLARE @xlLastColumn integer EXECUTE @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, 'Cells.SpecialCells(11).Column', @xlLastColumn OUTPUT</code>{br}{br} Nach dieser ganzen Vorbereitung können wir nun endlich anfangen die Daten in das Arbeitsblatt zu schreiben.{br}{br} Als Erstes brauchen wir Zugang zur Zelle.{br}{br} <code sql>DECLARE @xlCell integer SET @LastRow = @LastRow + 1 EXECUTE master.dbo.sp_OAGetProperty @xlWorkSheet, 'Cells', @xlCell OUTPUT, @LastRow, 1</code>{br}{br} Nun können wir Daten in die Zelle schreiben.{br}{br} <code sql>execute @rs = master.dbo.sp_OASetProperty @xlCell, 'Value', @Value</code>{br}{br} Wenn wir die Zelle formatieren wollen, gehen wir wie folgt vor.{br}{br} <code sql>EXECUTE @rs = master.dbo.sp_OASetProperty @xlCell, 'NumberFormat', '0%'</code>{br}{br} *'''0%''' Prozentzahl ohne Dezimalstellen.{br} *'''0.0%''' Prozentzahl mit einer Dezimalstelle.{br} *''''d-mmm'''' Datum im Format "10 Okt"{br} *''''mm-dd-yyyy'''' Datum im normalen Format.{br} *''''mm-dd-yyyy hh:mm:ss'''' Datum und Zeit Format.{br} *''''$#,##0.00'''' Nummer mit dem aktuellen Symbol, 2 Dezimalstellen und mindestens eine ganze Zahl. Nummern würden nach jeder 3. Stelle gekürzt.{br}{br} Die Schriftart einzustellen ist ein wenig komplexer.{br}{br} <code sql>DECLARE @objProp varchar(200) SET @objProp = 'Font.Bold' EXECUTE @rs = master.dbo.sp_OASetProperty @xlCell, @objProp, 'True'</code>{br}{br} Um die Schrift z.B. zu unterstreichen benutzen Sie einfach: '''Font.Underline'''.{br}{br} '''__Achtung__:''' Bevor Sie in eine neue Zelle "springen" müssen Sie die zuvor bearbeitet zuerst abschließen.{br}{br} <code sql>EXECUTE @rs = master.dbo.sp_OADestroy @xlCell</code>{br}{br} Jetzt müssen wir das Excel abspeichern und Excel schließen.{br}{br} <code sql>DECLATE @FileName varchar(100) SET@FileName = 'C:\MyNewExcelSpreadsheet.xls' EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkBook 'SaveAs', null, @FileName, -4143</code>{br}{br} '''-4143''' ist der Parameter für "Datei speichern als".{br}{br} <code sql>EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkBook, 'Close' EXECUTE @rs = master.dbo.sp_OAMethod @xlApp, 'Quit'</code>{br}{br}{br} '''Andere Möglichkeiten:'''{br}{br} Um den Namen der Arbeitsmappe zu ändern:{br}{br} <code sql>execute @rs = master.dbo.sp_OASetProperty @xlWorkBook, 'Title', 'My workbook name'</code>{br}{br} Um den Namen des Arbeitsblattes zu ändern:{br}{br} <code sql>execute @rs = master.dbo.sp_OASetProperty @xlWorkSheet, 'Name', 'My sheet name'</code>{br}{br} Um das Format einer existierenden Zelle zu bekommen:{br}{br} <code sql>execute @rs = master.dbo.sp_OAGetProperty @xlCell, 'NumberFormat', @Value OUTPUT</code>{br}{br} Um den Wert einer existierenden Zelle zu bekommen:{br}{br} <code sql>execute @rs = master.dbo.sp_OAGetProperty @xlCell, 'Value', @Value OUTPUT</code>{br}{br} Wenn Sie automatisch alle Spalten auf die Breite der breitesten Spalte setzen wollen:{br}{br} <code sql>execute @rs = master.dbo.sp_OAMethod @xlWorkSheet, 'Columns.AutoFit'</code>{br}{br} Wie ich vorher sagte, müssen alle Pointer geschlossen werden:{br}{br} <code sql>execute @rs = master.dbo.sp_OADestroy @xlWorkSheet execute @rs = master.dbo.sp_OADestroy @xlWorkBook execute @rs = master.dbo.sp_OADestroy @xlWorkBooks execute @rs = master.dbo.sp_OADestroy @xlApp</code>{br}{br} Wollen Sie eine Formel benutzen, dann setzen Sie den Wert der Zelle mit der Formel, z.B.: '=SUMME(a4.a50)' oder '=(+a4+a5)/a6'. Das Gleichheitszeichen (=) muss das erste Zeichen sein um der Zelle zu sagen, dass es sich um eine Formel handelt.{br} {br} Merken Sie sich das in allen sp_OA Aufrufen das Ergebnis in die @rs Variable geschrieben wird. Somit können viele Fehler untersucht werden:{br}{br} <code sql>If @rs <> 0 execute master.dbo.sp_OAGetErrorInfo @Object, @OA_Source OUTPUT, @OA_Descr OUTPUT, @HelpFile OUTPUT, @HelpID OUTPUT</code>
ScrewTurn Wiki
version 3.0.1.400. Some of the icons created by
FamFamFam
.