Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

FormatOracle2Excel – Kako prebaciti podatke iz Oracle baze u Excel

Tuesday, 24.11.2009 – Dejan

… a da podaci budu uredno posloženi u odgovarajućem formatu, sa raznim bojama, linkovima i sl. karakefama !?

Vjerujem da ste mnogo puta pravili export podataka u CSV formatu (Comma Separated Values) namijenjenih za korištenje u Excelu, a da su vaši korisnici često priželjkivali podatke u određenom formatu ili sa nekom bojom u pozadini.
Da li ste od onih, koji su svojim korisnicima odgovarali: “Nažalost, tehnički to nije moguće…” ?

Ako je tako, onda ćete nakon čitanja kompletnog teksta moći taj odgovor promijeniti u :”Naravno da je moguće! Recite nam samo, kakav format želite i koje boje da uzmemo? Želite li podatke odvojene u više tablica (sheets) ? Da Vam pošaljemo Excel datoteku na email ili da bude prikazana direktno u browseru? Nema problema – sve se može obaviti!!”

Moguće je!

Ključ leži u XML-u. Znači, podatke treba strpati u jednu XML datoteku, a ne koristiti obični suhoparni CSV format.

Naime, taj problem imamo i mi već dugo vremena. Moj kolega, Oracle programer, riješio je taj problem pomoću dinamički generisane XML datoteke, ali na pomalo nezgrapan način – koristeći View i XMLType u Oracle bazi, pri čemu se View svaki put morao ponovo kreirati (Dynamic DDL). Osim toga, generisanje jedne Excel datoteke u XML formatu na ovaj način traje prilično dugo – između 30 i 45 sekundi.

To i nije tako strašno, ako morate samo par takvih datoteka da kreirate, ali šta ako morate stotine ili hiljade !? Pa pomnoženo sa 30 sekundi? Vječnost …

Mene je kopkalo pitanje – “Može li se ovo ikako ubrzati? Sigurno da postoji neki način…”, pa sam se prvo dao u potragu preko Googlea, da ne otkrivam toplu vodu… I traženje se isplatilo! Našao sam jedno zaista korisno i efikasno rješenje pomalo nemaštovitog naziva – ExcelDocumentType. Ja bih ga prije nazvao FormatOracle2Excel.
Napomena: Morate biti registrovani OTN korisnik da biste pristupili ovim stranicama.

Ovo rješenje se takođe zasniva na exportu podataka u XML formatu, ali je daleko fleksibilnije i brže od trenutnog rješenja, koje mi koristimo.

ExcelDocumentType – instalacija

Nakon što ste skinuli ZIP paket za instalaciju (nije potrebna registracija za direktan download), potrebno je ispraviti postojeći bug u datoteci ExcelDocumentTypeGTT.udt:
– otvorite datoteku ExcelDocumentTypeGTT.udt
– pronađite proceduru createStyle i u njoj liniju, koja sadrži ‘</Style>||NLCHAR’;
– prepravite tu liniju da bude ‘</Style>’||NL_CHAR;
– snimite izmjene i zatvorite datoteku

Funkcionalnost ovog rješenja zahtjeva određene korisničke privilegije u bazi (DB User Privileges) – uvjerite se da sve potrebne privilegije postoje. Možete kreirati i novog korisnika, ako znate SYS lozinku. 🙂
Za primjer, koji slijedi u nastavku, kreirajmo novog korisnika u bazi. Spojite se kao SYS i pokrenite slijedeći SQL skript:

create user dejan
identified by dejan
default tablespace users
temporary tablespace temp;

grant resource, connect to dejan;

grant execute on utl_file to dejan;

create or replace directory EXCEL_DIR as 'C:\Temp';

grant read, write on directory EXCEL_DIR to dejan;

Potom se spojite na bazu pod novim korisničkim nalogom i pokrenite installUtil.sql skript:

C:\Temp>sqlplus dejan/dejan
SQL>@installUtil.sql

Sad možete početi sa programiranjem.

ExcelDocumentType – primjer

Na kraju ovog primjera trebali biste dobiti ovakvu Excel datoteku:

Za potrebe ovog primjera, kreirajmo novu tabelu i unesimo testne podatke:

CREATE TABLE oracle2excel(
  id number,
  ime varchar2(32),
  prezime varchar2(64),
  email varchar2(255)
);


insert into oracle2excel
values(1, 'Pera','Peric','pera.peric@example.com');
insert into oracle2excel
values(2, 'Dejan','Topalovic','dejan.topalovic@misesvamazezamo.com');
insert into oracle2excel
values(3, 'Marko','Markovic','marko.markovic@example.com');
insert into oracle2excel
values(4, 'Laza','Lazic','laza.lazic@example.com');
insert into oracle2excel
values(5, 'Stiv','Rezonski','stiv.rezonski@example.com');
insert into oracle2excel
values(6, 'Mihail','Dugokontov','mihail.dugokontov@example.com');
insert into oracle2excel
values(7, 'Mujo','Mujic','mujo.mujic@vicevi.com');
insert into oracle2excel
values(8, 'Fata','Morgana','fata.morgana@vicevi.com');
insert into oracle2excel
values(9, 'Haso','Sabanadzovic','haso.sabanadzovic@example.com');
insert into oracle2excel
values(10, 'Zgembo','Hadislic','zgembo.hadislic@tln.com');

commit;

Za export podataka možete direktno koristiti ExcelDocumentType ili dodatni wrapper paket ExcelDocTypeUtils. U ovom slučaju se koristi direktno ExcelDocumentType:

CREATE OR REPLACE PACKAGE plsql2excel
IS
  function test1 return number;
END plsql2excel;
/


CREATE OR REPLACE PACKAGE BODY plsql2excel IS

  function test1 return number
  IS
    excelReport   ExcelDocumentType := ExcelDocumentType();
    documentArray ExcelDocumentLine := ExcelDocumentLine();

    lnCntRange      number;
    lnMaxIme        number;
    lnMaxPrezime    number;
    lnMaxEmail      NUMBER;
    
    v_file   UTL_FILE.FILE_TYPE;

  BEGIN
    -- Open the Excel-Document:
    excelReport.documentOpen;

    -- Configure Styles:
    excelReport.stylesOpen;

      -- Include default style:
      excelReport.defaultStyle;

      -- Add custom styles:
      /* Style for columnd header row */
      excelReport.createStyle(
        p_style_id  => 'ColumnHeader',
        p_font      => 'Verdana',
        p_ffamily   => 'Swiss',
        p_fsize     => '12',
        p_bold      => 'Y',
        p_align_horizontal => 'Center',
        p_align_vertical => 'Bottom',
        p_cell_color => '#FF6600',
        p_cell_pattern => 'Solid',
        p_custom_xml => ''||
                        ''||
                        ''||
                        ''||
                        ''||
                        ''
      );

      excelReport.createStyle(
        p_style_id  => 'WithLinesString',
        p_font      => 'Verdana',
        p_ffamily   => 'Swiss',
        p_fsize     => '12',
        p_custom_xml => ''||
                        ''||
                        ''||
                        ''||
                        ''
      );

      excelReport.createStyle(
        p_style_id  => 'WithLinesNumber',
        p_font      => 'Verdana',
        p_ffamily   => 'Swiss',
        p_fsize     => '12',
        p_align_horizontal => 'Right',
        p_custom_xml => ''||
                        ''||
                        ''||
                        ''||
                        ''
      );

      excelReport.createStyle(
        p_style_id  => 'ClosingRow',
        p_custom_xml => ''||
                        ''||
                        ''
      );

      excelReport.createStyle(
        p_style_id  => 'ClosingRowNumber',
        p_number_format => '###',
        p_custom_xml => ''||
                        ''||
                        ''
      );


    -- Close Styles
    excelReport.stylesClose;

    -- Get values for the settings (range and column width):
    select count(*),
           max(length(ime)) as max_ime,
           max(length(prezime)) as max_prezime,
           max(length(email)) as max_email
      into lnCntRange, lnMaxIme, lnMaxPrezime, lnMaxEmail
      from oracle2excel;

    -- Open Worksheet:
    excelReport.worksheetOpen('Popis osoba');

    -- Define Sheet Conditional Formatting values:

    excelReport.worksheetCondFormatOpen(p_range => 'R2C1:R'||TO_CHAR(lnCntRange + 1)||'C4');

    excelReport.createCondFormat(
      p_qualifier => NULL,
      p_value     => 'MOD(ROW(),2)=0',
      p_format_style => 'background:#555555;color:#EEEEEE'
    );
    excelReport.createCondFormat(
      p_qualifier => NULL,
      p_value     => 'MOD(ROW(),2)=1',
      p_format_style => 'background:#DDDDDD;color:#444444'
    );

    excelReport.worksheetCondFormatClose;

    -- Define columns:
    excelReport.defineColumn(p_index => '1', p_width => 13);
    excelReport.defineColumn(p_index => '2', p_width => lnMaxIme + 5);
    excelReport.defineColumn(p_index => '3', p_width => lnMaxPrezime + 5);
    excelReport.defineColumn(p_index => '4', p_width => lnMaxEmail + 5);

    -- Define header row:
    excelReport.rowOpen;

      -- Define header row data cells:
      excelReport.addCell(p_style => 'ColumnHeader', p_data=> ' Br. ');
      excelReport.addCell(p_style => 'ColumnHeader', p_data=> ' Ime ');
      excelReport.addCell(p_style => 'ColumnHeader', p_data=> ' Prezime ');
      excelReport.addCell(p_style => 'ColumnHeader', p_data=> ' Email ');

    excelReport.rowClose;

    -- Populate rows and cells:
    FOR rec IN(select *
                 from oracle2excel
                order by id
              )
    LOOP
      excelReport.rowOpen;

        excelReport.addCell(
          p_style => 'WithLinesNumber',
          p_data_type => 'Number',
          p_data => rec.id
          --p_formula => 'SUM(RC[-5]:RC[-1])'
        );
        excelReport.addCell(
          p_style => 'WithLinesString',
          p_data_type => 'String',
          p_data => rec.ime
        );
        excelReport.addCell(
          p_style => 'WithLinesString',
          p_data_type => 'String',
          p_data => rec.prezime
        );
        excelReport.addCell(
          p_style => 'WithLinesString',
          p_data_type => 'String',
          p_data => rec.email,
          p_HRef => 'mailto:'||rec.email
        );

        --lnUngerade := lnUnGerade + 1;

      excelReport.rowClose;
    END LOOP;

    -- Add closing row:
    excelReport.rowOpen;
    excelReport.addCell(p_style => 'ClosingRowNumber', p_data_type => 'Number');
    excelReport.addCell(p_style => 'ClosingRow', p_data_type => 'String');
    excelReport.addCell(p_style => 'ClosingRow', p_data_type => 'String');
    excelReport.addCell(p_style => 'ClosingRow', p_data_type => 'String');
    excelReport.rowClose;

    -- Close the worksheet:
    excelReport.worksheetClose;

    -- Close the document:
    excelReport.documentClose;

    -- Get the document content:
    documentArray := excelReport.getDocumentData;

    v_file := utl_file.fopen('EXCEL_DIR', 'oracle2excel.xls', 'w', 4000);

    FOR i IN 1..documentArray.COUNT LOOP
      UTL_FILE.put_line(v_file, documentArray(i));
    END LOOP;

    utl_file.fclose(v_file);

    return 1; 


  EXCEPTION
    WHEN OTHERS THEN
      IF utl_file.is_open(v_file) THEN
        utl_file.fclose(v_file);
      END IF;
      dbms_output.put_line(SQLERRM);
      return 0; 
  END test1;


END plsql2excel;
/

NAPOMENA: Syntax highlighter je poblesavio, pa ne prikazuje kôd kako treba. Kliknite na “view plain” ili “copy to clipboard”, kako biste vidjeli i snimili originalni kôd! Ako ni tada ne radi, kliknite ovdje za download – Create Package plsql2excel .

Ako vam nešto u kôdu nije jasno, ne ustručavajte se – slobodno pitajte.

Ima li šta ljepše, nego imati srećne i zadovoljne korisnike?