Welcome Guest, you are in: Anmelden

CP Wiki

RSS RSS

Navigation



  1. Administration

Suche im wiki
»
 » 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam.