Tutorial

Erstellen eines Arbeitsblattes

Voraussetzungen

Du brauchst ein Verzeichnis (user_directory), in das du mit utl_file schreiben kannst. Und natürlich eine ORACLE Datenbank.

Die Dateiendung muss auf .xml lauten, sonst kommt es bei neuern Excelversionen zu Fehlermeldungen.

Unterschiede zwischen Excel und OO-Calc

In Calc sind einige Funktionen nicht verfügbar. Hierauf wird bei den Tutorials an den entsprechenden Stellen verwiesen.

Ganz einfach

Im ersten Schritt wird eine Datei aus einer SQL-Abfrage erstellt.

DECLARE

  -- test_1.sql
BEGIN
  xml_spreadsheet.abfrage(
     p_query => 'SELECT   username, user_id, created ' ||
                'FROM     all_users ' ||
                'WHERE    UPPER(username) = ''HR'' '

    ,p_path => 'DOC'
    );
END;
/

Das wars, mehr ist nicht notwendig. Der Dateiname wird automatisch auf "query_<User>_<Datum>_<Uhrzeit>.xml" gesetzt, für die Überschriften ein Autofilter.

Ergebnisse für Test 1 and 2
Excel	OO-Calc
	nach Ändern des generierten Dateinamens zu <...>.xml
Bilder zum Vergrößern anklicken

Dasselbe Ergebnis bekommst du, wenn du einen Cursor übergibst.

DECLARE
  -- test_2.sql
  l_cursor NUMBER := dbms_sql.open_cursor;

BEGIN
  dbms_sql.parse(
     l_cursor
    ,'SELECT   username, user_id, created ' ||
     'FROM     all_users ' ||
     'WHERE    UPPER(username) = ''HR'' '
    ,dbms_sql.native
    );
  xml_spreadsheet.abfrage(
     p_cursor => l_cursor
    ,p_path => 'DOC'

    );
END;
/

test_1-2.zip test_1.sql, test_2.sql und generierte Datei

Überschriften und Summenspalten setzen

Überschriften und Summenspalten sind jeweils eine Liste aus VARCHAR2

DECLARE
  -- test_3.sql
  l_cursor number := dbms_sql.open_cursor;

BEGIN
  dbms_sql.parse(
     l_cursor
    ,'SELECT   employee_id,first_name,last_name,email,' ||
     '         phone_number,hire_date,job_id,salary,' ||
     '         commission_pct,manager_id,department_id ' ||
     'FROM     employees '
    ,dbms_sql.native
    );
  xml_spreadsheet.abfrage(
     p_cursor => l_cursor
    ,p_path => 'DOC'

    ,p_sumColumns => xml_spreadsheet.t_tab_abfrage(
        'N','N','N','N','N','N','N','Y','N','N','N')
    ,p_titles => xml_spreadsheet.t_tab_abfrage(
        'Id','First Name','Last Name','Email','Phone Number',
        'Hire Date','Job Id','Salary','Commission Pct',
        'Manager Id','Department Id')
    );

END;
/

Ergebnisse für Test 3
Excel	OO-Calc
	nach Ändern des generierten Dateinamens zu <...>.xml
Bilder zum Vergrößern anklicken

Dieses Beispiel funktioniert genauso mit einem Cursor.

test_3.zip test_3.sql und generierte Datei

Ein zweites Arbeitsblatt hinzufügen

Wenn du eine Liste der Angestellten hast willst du vielleicht auch eine Liste der zugehörigen Abteilungen.

In diesem Fall musst du dir den file handle merken, damit das zweite Arbeitsblatt an die richtige Datei angehängt wird.

In diesem Fall ist auch ein Name für jedes Arbeitsblatt zu vergeben, der innerhalb der Datei eindeutig sein muss.

Um ein drittes Arbeitsblatt anlegen zu können müsstest du den Parameter "p_close => FALSE" übergeben, damit die Datei geöffnet bleibt.

DECLARE
  -- test_4.sql
  v_fileHandle utl_file.FILE_TYPE;
BEGIN
  v_fileHandle := xml_spreadsheet.abfrage(
     p_query =>

         'SELECT   employee_id,first_name,last_name,' ||
         '         email,phone_number,hire_date,job_id,' ||
         '         salary,commission_pct,manager_id,' ||
         '         department_id ' ||
         'FROM     employees '
    ,p_path => 'DOC'

    ,p_filename => 'test_4.xml'
    ,p_sheetname => 'Emp'
    ,p_sumColumns => xml_spreadsheet.t_tab_abfrage(
        'N','N','N','N','N','N','N','Y','N','N','N')
    ,p_titles => xml_spreadsheet.t_tab_abfrage(
        'Id','First Name','Last Name','Email',
        'Phone Number','Hire Date','Job Id','Salary',
        'Commission Pct','Manager Id','Department Id')
   ,p_close => FALSE

   );
  xml_spreadsheet.abfrage(
     p_query => 'SELECT   department_id,department_name,' ||
                '         manager_id,location_id ' ||
                'FROM     departments '
    ,p_fileHandle => v_fileHandle
    ,p_sheetname => 'Dept'

    ,p_titles => xml_spreadsheet.t_tab_abfrage(
        'Id','Name','Manager Id','Location')
    );
END;
/

Ergebnisse für Test 4
Excel	OO-Calc


Bilder zum Vergrößern anklicken

Dieses Beispiel funktioniert genauso mit einem Cursor.

test_4.zip test_4.sql und generierte Datei

Arbeitsblatt mit verschieden formatierten Zahlen

Anstelle dass man die Formatierung der Daten den Einstellungen des Programms überläßt, kann man das Ergebnis auch kontrollieren. Zunächst verwenden wir die vordefinierten Zellformate für Zahlen.

Anders als zuvor müssen wir uns jetzt selbst um das Öffnen der Datei, neue Arbeitsblätter und Datenzeilen kümmern.

DECLARE
  -- test_5.sql

  v_fileHandle            utl_file.FILE_TYPE;
BEGIN
  -- New File
  v_fileHandle   := xml_spreadsheet.createNewFile('DOC','test_5.xml');
  -- open first worksheet

  xml_spreadsheet.newWorksheet(
       v_fileHandle
      ,'Numbers'
      );

  -- Write a data row
  xml_spreadsheet.writeData(v_fileHandle,'Number Default');
  xml_spreadsheet.writeData(v_fileHandle,100/39,xml_spreadsheet.c_numberDefault);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'Number unformatted');
  xml_spreadsheet.writeData(v_fileHandle,100/39,xml_spreadsheet.c_unformatted);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'Number with two decimal digits');
  xml_spreadsheet.writeData(v_fileHandle,100/39,xml_spreadsheet.c_number2decimal);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'number as percent');
  xml_spreadsheet.writeData(v_fileHandle,39/100,xml_spreadsheet.c_prozent);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'number as integer');
  xml_spreadsheet.writeData(v_fileHandle,100/39,xml_spreadsheet.c_integer);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'number as currency');
  xml_spreadsheet.writeData(v_fileHandle,-100/39,xml_spreadsheet.c_currency);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'number as accounting format');
  xml_spreadsheet.writeData(v_fileHandle,39/19,xml_spreadsheet.c_accounting);

  -- close worksheet

  xml_spreadsheet.closeWorksheet(
       v_fileHandle
      ,0 -- fix no rows in first worksheet when scrolling
      );

  -- Close file
  xml_spreadsheet.closeFile(v_fileHandle);

END;
/

Ergebnisse für Test 5
Excel	OO-Calc

Bilder zum Vergrößern anklicken

test_5.zip test_5.sql und generierte Datei

Unterschiede

Excel zeigt "unformatierte" Zahlen mit 8, OO-Calc mit 2 Nachkommastellen
Excel zeigt "Währung" korrekt mit EUR-Zeichen (lokaler Standard), OO-Calc verwendet stattdessen das USD-Zeichen. Das Währungssymbol steht in Excel nach, in OO-Calc vor dem Wert.

Arbeitsblatt mit verschieden formatierten Texten und Daten

Genau wie bei den Zahlen kann man das gewünschte Ergebnis für Text und Datum angeben.

DECLARE
  -- test_6.sql
  v_fileHandle            utl_file.FILE_TYPE;

BEGIN
  -- New File
  v_fileHandle   := xml_spreadsheet.createNewFile('DOC','test_6.xml');
  -- open first worksheet

  xml_spreadsheet.newWorksheet(
       v_fileHandle
      ,'Text and Dates'
      );

  -- Write a data row
  xml_spreadsheet.writeData(v_fileHandle,'Text Default');
  xml_spreadsheet.writeData(v_fileHandle,'Sample text',xml_spreadsheet);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'Bold');
  xml_spreadsheet.writeData(v_fileHandle,'Sample text',xml_spreadsheet.c_textBold);
  -- New data row
  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'Underlined');
  xml_spreadsheet.writeData(v_fileHandle,'Sample text',xml_spreadsheet.c_textUnderline);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'Bold and underlined');
  xml_spreadsheet.writeData(v_fileHandle,'Sample text',xml_spreadsheet.c_textBoldUnderline);
  -- New data row
  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'Date');
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_date);
  -- New data row

  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,'Datetime');
  xml_spreadsheet.writeData(v_fileHandle,-SYSDATE,xml_spreadsheet.c_dateTime);

  -- close worksheet
  xml_spreadsheet.closeWorksheet(
       v_fileHandle
      ,0 -- fix no rows in first worksheet when scrolling

      );

  -- Close file
  xml_spreadsheet.closeFile(v_fileHandle);

END;
/

Ergebnisse für Test 6
Excel	OO-Calc

Bilder zum Vergrößern anklicken

test_6.zip test_6.sql und generierte Datei

Unterschiede

Excel und OO-Calc zeigen Datum und Zeit mit verschiedenen Formatierungen
OO-Calc passt die Spaltenbreite nicht an die Länge der Zeitangabe an

oracle@matzberger.de

Impressum