Automatisierte Excel vom SQL Server

» Startseite » netFrame als Plattform Menü » Tipps und Tricks » Automatisierte Excel vom SQL Server | » Erweiterte Suche

• Achtung: Um diesen Vorgang auszuführen muss Excel auf dem SQL Server installiert sein!
  
  
  
  
• Als Erstes müssen wir eine Excelinstanz öffnen, dies geschieht folgendermaßen:
  
  
  DECLARE @xlApp integer, @rs integer
EXECUTE @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT
  
  
  Mit diesem Code haben wir das Excel Programm aufgerufen. In diesem Fall steuert die Variable @xlApp das Programm.
  
  Es ist sehr nützlich die Eigenschaften "ScreenUpdating" und "DisplayAlerts" zu deaktivieren.
  
  
  ScreenUpdating deaktiviert erhöht die Geschwindigkeit des Codes und ansehen werden wir uns das Excel sowieso nicht.
  
  DisplayAlerts deaktiviert schützt uns vor Exceldokumenten die eine Eingabe des Users erfordern.
  
  
  EXECUTE @rs = master.dbo.sp_OASetProperty @xlApp, 'ScreenUpdating', 'False'
EXECUTE @rs = master.dbo.sp_OASetProperty @xlApp, 'DisplayAlerts', 'False'
  
  
  Nun benötigen wir die Möglichkeit um Arbeitsmappen zu öffnen.
  
  
  DECLARE @xlWorkbooks integer
EXECUTE @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTUT
  
  
  Nun müssen wir eine Entscheidung treffen. Öffnen wir eine bestehende Arbeitsmappe oder erstellen wir eine Neue?
  
  
  
  Um eine bestehende Arbeitsmappe zu öffnen.
  
  
  DECLARE @xlWorkbook integer
EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, 'C:\Myspreadsheet.xls'
  
  
  Um eine neue Arbeitsmappe zu erstellen.
  
  
  DECLARE @xlWorkBook integer
EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkBooks, 'Add', @xlWorkBook OUTPUT, -4167
  
  
  -4167 ist der Wert der Variable xlWBATWorksheet, die Excel auffordert eine neue Arbeitsmappe anzulegen.
  
  
  Da wir nun eine Arbeitsmappe haben benötigen wir noch ein Arbeitsblatt
  
  
  DECLARE @xlWorkSheet integer
EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkBook, 'ActiveSheet', @xlWorkSheet OUTPUT
  
  
  Nun müssen wir die letzte Zeile des Arbeitsblattes herausfinden. Dies kann Excel uns verraten.
  
  
  DECLARE @xlLastRow integer
EXECUTE @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, 'Cells.SpecialCells(11).Row', @xlLastRow OUTPUT
  
  
  Wenn wir möchten verrät Excel uns auch die letzte Spalte.
  
  
  DECLARE @xlLastColumn integer
EXECUTE @rs = master.dbo.sp_OAGetProperty @xlWorkSheet, 'Cells.SpecialCells(11).Column', @xlLastColumn OUTPUT
  
  
  Nach dieser ganzen Vorbereitung können wir nun endlich anfangen die Daten in das Arbeitsblatt zu schreiben.
  
  
  Als Erstes brauchen wir Zugang zur Zelle.
  
  
  DECLARE @xlCell integer
SET @LastRow = @LastRow + 1
EXECUTE master.dbo.sp_OAGetProperty @xlWorkSheet, 'Cells', @xlCell OUTPUT, @LastRow, 1
  
  
  Nun können wir Daten in die Zelle schreiben.
  
  
  execute @rs = master.dbo.sp_OASetProperty @xlCell, 'Value', @Value
  
  
  Wenn wir die Zelle formatieren wollen, gehen wir wie folgt vor.
  
  
  EXECUTE @rs = master.dbo.sp_OASetProperty @xlCell, 'NumberFormat', '0%'
  
  
• 0% Prozentzahl ohne Dezimalstellen.
  
• 0.0% Prozentzahl mit einer Dezimalstelle.
  
• 'd-mmm' Datum im Format "10 Okt"
  
• 'mm-dd-yyyy' Datum im normalen Format.
  
• 'mm-dd-yyyy hh:mm:ss' Datum und Zeit Format.
  
• '$#,##0.00' Nummer mit dem aktuellen Symbol, 2 Dezimalstellen und mindestens eine ganze Zahl. Nummern würden nach jeder 3. Stelle gekürzt.
  
  
  Die Schriftart einzustellen ist ein wenig komplexer.
  
  
  DECLARE @objProp varchar(200)
SET @objProp = 'Font.Bold'
EXECUTE @rs = master.dbo.sp_OASetProperty @xlCell, @objProp, 'True'
  
  
  Um die Schrift z.B. zu unterstreichen benutzen Sie einfach: Font.Underline.
  
  
  Achtung: Bevor Sie in eine neue Zelle "springen" müssen Sie die zuvor bearbeitet zuerst abschließen.
  
  
  EXECUTE @rs = master.dbo.sp_OADestroy @xlCell
  
  
  Jetzt müssen wir das Excel abspeichern und Excel schließen.
  
  
  DECLATE @FileName varchar(100)
SET@FileName = 'C:\MyNewExcelSpreadsheet.xls'
EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkBook 'SaveAs', null, @FileName, -4143
  
  
  -4143 ist der Parameter für "Datei speichern als".
  
  
  EXECUTE @rs = master.dbo.sp_OAMethod @xlWorkBook, 'Close'
EXECUTE @rs = master.dbo.sp_OAMethod @xlApp, 'Quit'
  
  
  
  Andere Möglichkeiten:
  
  
  Um den Namen der Arbeitsmappe zu ändern:
  
  
  execute @rs = master.dbo.sp_OASetProperty @xlWorkBook, 'Title', 'My workbook name'
  
  
  Um den Namen des Arbeitsblattes zu ändern:
  
  
  execute @rs = master.dbo.sp_OASetProperty @xlWorkSheet, 'Name', 'My sheet name'
  
  
  Um das Format einer existierenden Zelle zu bekommen:
  
  
  execute @rs = master.dbo.sp_OAGetProperty @xlCell, 'NumberFormat', @Value OUTPUT
  
  
  Um den Wert einer existierenden Zelle zu bekommen:
  
  
  execute @rs = master.dbo.sp_OAGetProperty @xlCell, 'Value', @Value OUTPUT
  
  
  Wenn Sie automatisch alle Spalten auf die Breite der breitesten Spalte setzen wollen:
  
  
  execute @rs = master.dbo.sp_OAMethod @xlWorkSheet, 'Columns.AutoFit'
  
  
  Wie ich vorher sagte, müssen alle Pointer geschlossen werden:
  
  
  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
  
  
  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.
  
  
  
  Merken Sie sich das in allen sp_OA Aufrufen das Ergebnis in die @rs Variable geschrieben wird. Somit können viele Fehler untersucht werden:
  
  
  If @rs <> 0 execute master.dbo.sp_OAGetErrorInfo @Object, @OA_Source OUTPUT, @OA_Descr OUTPUT, @HelpFile OUTPUT, @HelpID OUTPUT