Oracle en Excel

Augustus 2008 - Microsoft Excel is een veelgebruikte, veelzijdige applicatie. Heel veel afdelingen gebruiken de spreadsheetapplicatie. Van (vakantie)rooster tot management informatiesysteem, van budgetoverzicht tot huishoudboekje, bijna iedere computergebruiker krijgt er wel mee te maken. De kracht van de applicatie zit ‘m in de mogelijkheid om in elke cel een getal of berekening te zetten, waarbij alle afhankelijkheden (formules) van een cel mee veranderen als de waarde van de cel verandert. Ook het netjes tonen van data, compleet met kleur en formatteringen wordt als een groot pluspunt van deze applicatie gezien.

Een belangrijk nadeel van Excel is dat het in principe niet bedoeld is om in een multi-user omgeving gebruikt te worden. Een voorbeeld is het omzetoverzicht dat door verschillende afdelingen is aangepast zodat er niet meer bekend is welk sheetkopie de waarheid spreekt. Veel Excel bestanden beginnen ook vaak als een klein overzichtje dat gaandeweg wordt uitgebreid met een kolommetje voor de ene afdeling en een totaalveldje voor de andere afdeling. Op deze manier groeit zo’n Excel bestand dan uit naar een belangrijker, onmisbaar document waarin essentiële informatie is opgeslagen. Voor dit soort belangrijke gegevens en informatie zou het dan verstandig zijn om de gegevens in een database te bewaren en desgevraagd deze data naar een Excel bestand te exporteren.

Dit Whitebook gaat in op de mogelijkheden om data uit te wisselen tussen Microsoft Excel en de Oracle database.

IDE

IDE tools als Toad en SQLdeveloper bieden ondersteuning aan om data uit te wisselen tussen Oracle en Excel. Nadeel hierbij is dat er enige handmatige actie nodig is. Echt geautomatiseerd data inlezen of wegschrijven is niet mogelijk. Deze tools worden dus voornamelijk gebruikt tijdens development of echte adhoc behoeften.

Frameworks

De meeste PHP en Java frameworks bieden kant en klare functionaliteit om vanuit Oracle te importeren of exporteren. Vooral bij het maken van webapplicaties is het heel eenvoudig om te exporteren naar Excel omdat dan simpel het mimetype op “Excel” hoeft te worden gezet. Deze exports of "opslaan als" worden dus in web applicaties aangeboden en zijn zodoende bedoeld voor de eindgebruikers.

BI Publisher

BI Publisher biedt mogelijkheden om data vanuit de Oracle database te presenteren als Excel bestand. In het Whitebook Praktijkervaring met Oracle BI Publisher is hierover meer geschreven.

Apex

In Oracle Apex is het mogelijk om de gepresenteerde data te exporteren naar Microsoft Excel. Dit gebeurt in het csv formaat. Het csv formaat is een bestandsformaat waarbij gegevens gescheiden worden door een vast teken, bijvoorbeeld de puntkomma (;). Ook is het mogelijk om vanuit een bestaand Excel bestand een application system aan te maken. De data uit het bestand wordt dan als input gebruikt om rapporten en schermen aan te maken. Het Excel bestand moet als csv-bestand worden ingelezen en kan ook alleen maar als csv-bestand worden geëxporteerd. Bij het inlezen kan dit een nadeel zijn want formules worden in een csv bestand niet opgeslagen. Wel heel mooi is dat Apex van de data in het sheet een tabel maakt en hierin de informatie opslaat. Ook handig is dat Apex al de schermen aanmaakt, compleet met CRUD-functionaliteit. Deze functionaliteit wordt dus door Apex ontwikkelaars gebruikt. Uiteraard kunnen de gewenste Excel bestanden ook worden gegenereerd en worden aangeboden vanuit de door Apex voortgebrachte webapplicaties.

Oracle External tables

Oracle External tables is een manier om data via een bestand in de Oracle database te importeren. Het maakt gebruik van de SQLloader functionaliteit.

Met External tables kan zowel data vanuit een bestand in de Oracle database geïmporteerd worden, alsmede data vanuit de Oracle database naar een bestand geëxporteerd worden.

External tables wordt aangeroepen door een extra optie bij het create table statement. Het Excel bestand moet als csv file in een door de Oracle user toegankelijke directory worden opgeslagen en er is de beperking dat er slechts één werkblad per file kan worden ingelezen. In de external table opties wordt onder andere de directory en de naam van het bestand dat de gegevens bevat opgegeven.

Voorbeeld: Stel, de volgende gegevens zijn opgeslagen in een csv bestand:

10;Accounting;Amsterdam
20;Research;Haarlem
30;Sales;Maastricht
40;IT;Eindhoven

Dan kan met de volgende stappen deze data in de Oracle database worden ingelezen:

-- Voer de eerste twee commando’s uit onder de sys of system user
create or replace directory ora_dir as 'c:\ora_dir';
grant read,write on directory ora_dir to xls_usr;
create table afdeling_ext
(afd_nr number(3)
,naam   varchar2(30)
,plaats varchar2(40)
)
organization external
(default directory ora_dir
access parameters
(records delimited by newline
fields terminated by ';'
)
location ('afdeling.csv')
);

Bij het selecteren uit deze tabel wordt de SQLloader gestart en zal getracht worden de rijen in te lezen. Net als bij SQLloader wordt dan een logfile gemaakt waarin het resultaat te zien is. Verder worden er bestanden aangemaakt waarin eventuele geweigerde rijen te zien zijn.

select *
from afdeling_ext;

Afd_nr Naam       Plaats
------ ----       ------
    10 Accounting Amsterdam
    20 Research   Haarlem
    30 Sales      Maastricht
    40 IT         Eindhoven

Om de data definitief op te slaan in Oracle kan het volgende gedaan worden:

create table afdelingen as select * from afdeling_ext;

External tables is met name geschikt om frequent aangeleverde data in te laten lezen in de Oracle database. Door de vaste directory kunnen scripts hier mooi gebruik van maken.

Spoolen

Een veel gebruikte methode om data vanuit een Oracle database in een Excel bestand te krijgen is het gebruik van het spool commando in SQL*Plus. Daarbij wordt de mogelijkheid geboden om de query output naar een bestand te sturen. Als de kolommen daarbij gescheiden worden door het tab-teken (chr(9)) en de bestandsnaam heeft de extensie .csv (komma gescheiden bestand) dan is Excel in staat het bestand te lezen. Nadeel hierbij is dat er geen formatteringen, decimalen of kleuren mogelijk zijn. Bovendien mogen getallen maar maximaal een bepaald aantal posities lang zijn. Dit kan een probleem zijn bij bijvoorbeeld ISBN nummers. Deze worden in Excel dan als een wetenschappelijke getalnotatie getoond.

Voorbeeld:

set echo off
set feedback off
set heading off
set pages 0
spool c:\output\isbn.csv
select isbn||chr(9)||naam||chr(9) from boeken

spool off
set pages 11
set heading on
set feedback on
set echo on

SQL*Plus biedt ook de mogelijkheid om data in HTML formaat te spoolen. Ook hierbij zijn er beperkingen. In HTML spoolen gaat door middel van het commando set markup HTML on. Na dit commando wordt alle output in HTML getoond, dus ook op het scherm in SQLplus. Deze data kan vervolgens gespooled worden. HTML output uitzetten gaat middels het commando set markup HTML off. Voorbeeld:

set markup html on
spool c:\output\tekst.xls
select * from user_tables;
spool off
set markup html off

Spoolen wordt veel gebruikt tijdens ontwikkeling en bij adhoc vragen.

Microsoft Office in XML formaat

Sinds Microsoft Office 2003 is Excel in staat ook XML documenten te lezen en om documenten in XML formaat op te slaan. Met de huidige mogelijkheden met betrekking tot XML in de Oracle database is dit de beste methode om vanuit Oracle Excel bestanden aan te maken. Kleuren, formatteringen, getalnotaties, datum formaten, alles kan met XML geregeld worden. Het XML document kent typisch een aantal secties.

Figuur 1: voorbeeld van een Excel bestand in xml formaat

In het begin komen de document properties waarin vermeld staat wie de auteur is en wanneer het document is aangepast. Daarna komt de werkboek info. Hierin staat onder andere hoe groot de vensters zijn. Vervolgens worden alle stijlen opgesomd. Iedere unieke formattering van een cel wordt apart als stijl genoemd. Bij het vullen van de cel wordt dan verwezen naar deze bepaalde stijl. De stijlen hebben een naam die begint met ss, gevolgd door een nummer. Na de stijlen komt de daadwerkelijke vulling van het sheet. Na het vullen van de cellen komt de sectie workbook options. Hierin staan dingen als de printmarge, papierformaat, regel- of kolomvergrendelingen, vergroting/verkleining enz.

Een voorbeeld staat in het bestand spl_xml.sql uit de Whitebook_Oracle_en_Excel_files.zip.

Gebruik van het XML formaat voor Excel bestanden is aan te bevelen wanneer data geëxporteerd moet worden waarin verschillende datatypen en formatteringen voorkomen.

Apache poi

Apache poi is een project dat is opgericht door een groep open-source ontwikkelaars. Het project heeft als doel het toegankelijker maken van het Microsoft Office formaat. Zo bestaat er een subproject dat HSSF heet, wat een afkorting is van "Horrible SpreadSheet Format" en een set van tools heeft opgeleverd dat het mogelijk maakt om met behulp van Java, Microsoft Excel bestanden te kunnen lezen en schrijven.

Met behulp van Apache poi is het mogelijk om een Excel bestand aan te maken dat leesbaar is door Microsoft Excel. Tevens is het mogelijk om Excel bestanden te lezen. Dit gebeurt met Java procedures. Aangezien het tegenwoordig mogelijk is om Java procedures op te slaan in de Oracle database (Java Stored Procedures) kan met behulp van Java en PL/SQL een procedure geschreven worden die vanuit Oracle kan lezen van en schrijven naar een Excel bestand. Een klein nadeel hierbij is dat Oracle XE geen Java stored procedures ondersteunt. In dat geval moet de Java procedure apart worden aangeroepen. In onderstaand voorbeeld wordt een bestaand Excel bestand als template gebruikt waaruit een formule wordt gekopieerd. In het nieuw aan te maken Excel bestand wordt een deel van de inhoud van de EMP tabel gespooled en de formule uit het template bestand rekent het totaal van alle salarissen uit.

Kijk voor voorbeelden in de bestanden xls_example.java en template.xls uit de voorbeeld-zip.

Deze methode kan gebruikt worden als er data volgens een bepaalde lay-out, en met behoud van formules in een Excel bestand terecht moet komen.

Office Open XML

Sinds Microsoft Office 2007 kunnen documenten worden opgeslagen in het door Microsoft zelf geïntroduceerde Office Open XML, een variant op XML die onlangs als standaard werd geaccepteerd door het ISO. Documenten die in dit formaat worden opgeslagen krijgen de extensie .xlsx. Deze documenten zijn eigenlijk verzamelingen van (xml)bestanden die gezipt zijn. Inderdaad, Office 2007 documenten zijn gewoon zip-bestanden. In het zip archief staan dus allemaal xml bestandjes in een directory structuur. Deze kunnen allemaal worden uitgepakt en vervolgens als xml gelezen worden. De directory structuur heeft een vaste indeling en is onderdeel van de Office Open XML standaard.

Figuur 2: Bestandssysteem van een Office Open XML document

Het nieuwe formaat maakt onderscheid tussen verschillende datatypen en deze worden dan ook in aparte bestanden opgeslagen. Door relaties tussen de verschillende bestanden ook weer in bestanden op te slaan wordt door Excel herkend wat bij elkaar hoort. Tekstvelden worden in het bestand xl\sharedStrings.xml opgeslagen. De numerieke data wordt in xl\worksheets\sheet1.xml opgeslagen. En formules worden in xl\calcChain.xml opgeslagen. Het is mogelijk om dit alles te maken in Oracle, met behulp van PL/SQL stored procedures en java (stored) procedures. In de PL/SQL procedure worden de xml bestanden aangemaakt en in de java procedures worden de aangemaakte xml bestanden in een zipfile gestopt. Zoals eerder vermeld biedt Oracle XE geen ondersteuning voor Java Stored procedures. De Java code kan echter wel gewoon standalone worden gecompileerd en middels een (batch)script worden aangeroepen na uitvoering van de PL/SQL procedure. In onderstaand voorbeeld worden de diverse XML bestanden aangemaakt en naar een directory gekopieerd, volgens de Office Open XML standaard. En vervolgens kan met een Java script de data gezipt worden met .xlsx als extensie van het bestand. Allereerst zorgen dat er vanuit Oracle gelezen en geschreven mag worden in een directory:

create directory XLS_XL            as 'c:\ora_dir\xls\xl';
create directory XLS_XL_WORKSHEETS as 'c:\ora_dir\xls\xl\worksheets';
grant read,write on directory XLS_XL to xls_usr;
grant read,write on directory XLS_XL_WORKSHEETS to xls_usr;

Vervolgens onderstaand PL/SQL script uitvoeren waarin de data uit een tabel eerst in een PL/SQL tabel wordt opgeslagen om deze vervolgens eerst naar sharedStrings.xml te spoolen en daarna naar sheet1.xml. De rest van de directory is voor het voorbeeld even van een ander bestand gekopieerd.

In het bestand maak_xlsx.sql uit de voorbeeld-zip staat een voorbeeld.

Voor het aanmaken van een zipfile en het kopiëren van de aangemaakte bestanden naar deze zipfile zijn op internet diverse scripts in Java te vinden.

Importeren en exporteren volgens de Office Open XML standaard is vanwege de geaccepteerde standaard aan te bevelen wanneer men het Excel bestand in verschillende Office applicaties gebruikt. De nieuwste versie van Open Office ondersteunt dit formaat namelijk ook. De huidige versie van Open Office kan met behulp van een converter ook het Office Open XML formaat lezen. Dit geldt tevens voor Microsoft Office 2003.

Met behulp van bovenstaande code kan een applicatie gemaakt worden waarbij eindgebruikers de mogelijkheid krijgen om gegevens uit een database naar een Excel bestand te exporteren.

Conclusie

Er zijn diverse manieren om data tussen de Oracle database en Microsoft Excel uit te wisselen. Inlezen van Excel bestanden kan meestal alleen maar als het sheet is opgeslagen als csv bestand. Exporteren naar Excel bestanden kan op veel verschillende manieren maar om het maximale resultaat te behalen is echter wel het gebruik van Java en het Apache poi project aan te raden. Door het nieuwe Office Open XML formaat van Microsoft is data heel goed uitwisselbaar met behulp van XML. En het nieuwe formaat is ook door Openoffice te lezen.

Relevante links en referenties:

• Apache poi project
  poi.apache.org
• Interactie tussen MS Excel en Java
  www.ster.be/java/java9.html
• Developers' guide to HSSF features
  poi.apache.org/hssf/quick-guide.html
• Office Open XML
  nl.wikipedia.org/wiki/Office_open_xml
• Office Open XML (engelse versie)
  en.wikipedia.org/wiki/Office_Open_XML
• Compress files using Java zip
  www.java2s.com/Code/Java/File-Input-Output/CompressfilesusingtheJavaZIPAPI.htm
• Whitebook Oracle9i feature van de maand: external tables
  www.whitehorses.nl/oracle9i_feature_van_de_maand%253A_external_tables_986.html

Download de voorbeeldbestanden uit dit Whitebook: Whitebook_Oracle_en_Excel_files.zip

Over de auteur
Marcel van der Plas is Oracle Consultant bij Whitehorses en heeft ruim 12 jaar ervaring in de IT. Hij houdt zich voornamelijk bezig met het ontwerpen en ontwikkelen van applicaties in PL/SQL, Forms, Reports, Designer en Apex.