Follow Us on Twitter

Eenvoudig gepersonaliseerde Word documenten genereren vanuit Oracle applicaties

Februari 2013 - In de vele reclamebrieven die je ontvangt zie je dat je eigen naam en adresgegevens zijn gebruikt binnen de brief, de rest van de brief is vaak standaard. Is dit ook mogelijk vanuit Oracle en hoe? Er zijn diverse manieren om de Oracle database en Word te koppelen.

Stel je hebt een database vol met contactpersonen en hun adresgegevens. Die gegevens wil je hebben op een gepersonaliseerd document, zoals een uitnodiging of een commerciële brief. De meest voor de hand liggende aanpak is dan gebruik te maken van Oracle BI Publisher of een ander commerciële rapportage-oplossing.

Als je geen extra product wil aanschaffen, zit je al snel vast aan het gebruik van handmatige ODBC-koppelingen in het Word document zelf. Voor elk document kan je dan in Word een template (het basisdocument met de algemene tekst) maken. In iedere template kan je dan via ODBC een koppeling maken naar de database. Het kiezen van de juiste data in combinatie met het juiste template vraagt zorgvuldigheid. Elke aanpassing aan de gegevens die in het Word document moet worden getoond, is erg bewerkelijk.

Dit Whitebook beschrijft een andere manier om gegevens uit een database samen te voegen in een Word document. Deze methode is minder complex, makkelijk te onderhouden en eenvoudig te implementeren. We gebruiken hiervoor de Oracle PL/SQL Utility Library "Alexandria".

Opzet

Voor dit Whitebook maken we een online applicatie waarmee je een persoon kan selecteren uit de Oracle database en daarna een Word template kan kiezen. Met een druk op de knop worden de gegevens van die persoon in het Word document getoond.

De applicatie maakt het eenvoudig om nieuwe Word templates (bijv. brieven) toe te voegen en aan te passen.

Wat we nodig hebben voor deze applicatie:

  1. Word document template in docx formaat (Microsoft Word versie 2007 en hoger);
  2. Oracle packages met tools voor unzippen / editten van Word documenten;
  3. Oracle Application Express (APEX) ( vanaf versie 4 ).

Op de client machine, waarop de browser draait, moet ook de correcte versie van Word geïnstalleerd staan.

Word template

Voor deze case gaan we uit van een simpel Word template. De template ziet er bijvoorbeeld als volgt uit:

Word template

Om aan te geven waar we tekst uit de database willen neerzetten gebruiken we zogenaamde “tags” in het document. In dit geval gebruiken we tags die beginnen en eindigen met een #.

In het eindresultaat willen we dat deze tags vervangen zijn met waarden uit de Oracle database zodat de correcte aanhef en adresgegevens zichtbaar worden in het Word document.

De template mag plaatjes bevatten en mag verder elke opmaak (lettertype, etc.) hebben die gewenst is. Let wel op dat je de tags (bijv. #POSTCODE_MWR# ) in één keer correct intypt en dus niet later een deel van de tag aanpast. De kans is dan groot dat binnen het Word document, in de XML op de achtergrond, de tag niet meer als één geheel wordt gezien. Een goede manier is de tag te kopiëren naar Notepad, aan te passen, te kopiëren en dan weer te plakken in het Word document.

Oracle packages

Het docx formaat dat Word sinds versie 2007 kent, is een gezipped XML-document. Als het is uitgepakt is, kan het worden benaderd via Oracle XML.

Dit is nu mogelijk door de plsql-utils, Oracle PL/SQL Utility Library, codename "Alexandria".

Op code.google.com/p/plsql-utils/ staat een zip file met daarin de benodigde Oracle packages om dit mogelijk te maken.

De volgende packages uit de zip file zijn nodig voor wat we hier willen. Installeer deze packages in je werkomgeving:

  • ooxml_util_pkg.pks
  • ooxml_util_pkg.pkb
  • sql_util_pkg.pks
  • sql_util_pkg.pkb
  • string_util_pkg.pks
  • string_util_pkg.pkb
  • xml_stylesheet_pkg.pks
  • xml_stylesheet_pkg.pkb
  • xml_util_pkg.pks
  • xml_util_pkg.pkb
  • zip_util_pkg.pks
  • zip_util_pkg.pkb

Installeer ook het UTL_FILE package (@?/rdbms/admin/utlfile.sql) in de database ( als SYS user ).

Daarnaast zijn de volgende types nodig, installeer deze in je werkomgeving:

CREATE OR REPLACE TYPE "T_STR_ARRAY" AS TABLE OF VARCHAR2(4000);
CREATE TYPE T_NUM_ARRAY AS TABLE OF NUMBER;

APEX

De gemakkelijkste manier om dit alles aan elkaar vast te knopen is via APEX. Hiervoor hebben we de Word template als blob nodig in de database. Daarvoor moet we eerst de mogelijkheid maken voor het uploaden van de Word template naar de database. Hiervoor maken we een upload template pagina.

Een hele goede beschrijving hiervoor is te vinden op het blog van Ittichai Chammavanijakul.

Maak een tweede APEX pagina met daarin:

  • een selectie LOV naar de medewerker tabel;
  • een selectie LOV naar de tabel waar de templates als blobs opgeslagen zijn;
  • een knop om de generatie te starten;
  • maak een branch terug naar de huidige pagina, met in Request de waarde: "GENEREER_DOC";
  • maak een proces "on-load before header", zet "Request = Expression 1" in Condition type en de waarde "GENEREER_DOC" in Expression 1.

De combinatie van het kiezen van een medewerker en het kiezen van de template op het scherm zorgt voor veel flexibiliteit.

Het proces heeft de volgende code:

DECLARE
  l_new_file blob;
  l_doc_id NUMBER;
  l_names  T_STR_ARRAY := T_STR_ARRAY();
  l_values T_STR_ARRAY := T_STR_ARRAY();
  l_teller NUMBER;
  l_record_nr NUMBER;
  v_mime  VARCHAR2(100) := 'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
  v_length  NUMBER;
  l_new_file_name VARCHAR2(200);

  -- De wordt templates in de database
  CURSOR c_template IS
     SELECT dte_filename
          , dte_data
       FROM document_templates
      WHERE dte_id = :P1_SELECT_TEMPLATE;
  r_template c_template%ROWTYPE;
  
  -- De data uit de database welke we nodig hebben om de tags te vervangen
  CURSOR c_medewerker  IS
    SELECT naam
         , adres
         , postcode
         , woonplaats
     FROM medewerker
    WHERE mdr_id = :P1_MDR_ID;
   r_medewerker c_medewerker%ROWTYPE;

  FUNCTION replace_special_chars ( l_text VARCHAR2 )  RETURN VARCHAR2 IS
     l_return  VARCHAR2(4000);
  BEGIN
     l_return := l_text;
     l_return := SUBSTR(REPLACE ( l_return, '&', '&'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, '<', '<'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, '>', '>'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, chr(10), ''), 1, 4000);

     RETURN l_return;
  END;
BEGIN
   OPEN c_template;
   FETCH c_template INTO r_template;
   IF c_template%NOTFOUND 
   THEN
      Wwv_flow.debug('NOT FOUND');
        raise_application_error ( -20000, 'kan template niet vinden');
        null;
   END IF;
   CLOSE c_template;

   -- Genereer een ID voor het nieuw te maken document
   SELECT "DOCUMENTEN_SEQ".nextval
   INTO l_doc_id
   FROM dual;

   -- Maak arrays met de tag name en tag inhoud
   l_teller := 1;
   OPEN c _medewerker;
   FETCH c_medewerker into r_medewerker;
   CLOSE c_medewerker;
   
   IF r_medewerker IS NOT NULL
   THEN
      -- Hier vullen we een array met de tags, en met welke waarde die vervangen moet worden
      l_names.EXTEND(4);
      l_values.EXTEND(4);
      
      l_names(1) := '#NAAM#';
      l_names(2) := '#ADRES#';
      l_names(3) := '#POSTCODE#';
      l_names(4) := '#WOONPLAATS#';
      
      l_value(1) := r_medewerker.naam;
      l_value(2) := r_medewerker.adres;
      l_value(3) := r_medewerker.postcode;
      l_value(4) := r_medewerker.woonplaats;
      
      -- Het aanpassen van het template document, hier wordt de replace gedaan van de tags naar de Oracle waarden 
      l_new_file := ooxml_util_pkg.get_file_from_template ( r_template.dte_data, l_names, l_values);
   
      -- maken van de nieuwe file
      l_new_file_name := 'Uitnodiging_'||l_doc_id||'.docx';
   
      v_length := DBMS_LOB.GETLENGTH(l_new_file);
      owa_util.mime_header( v_mime, FALSE );
      htp.p('Content-length: ' || v_length);
      htp.p('Content-Disposition:  attachment; filename="'||l_new_file_name||'"');
      owa_util.http_header_close;
 
      wpg_docload.download_file( l_new_file );
   END IF;  
END;

Resultaat

De code "wpg_docload.download_file( l_new_file );" zorgt ervoor dat de aangepaste Word template direct wordt geopend op het scherm in Word. De tags zijn dan reeds vervangen met de Oracle waarden. De gebruiker deze kan nu kiezen voor het direct printen of het document opslaan.

Natuurlijk zijn er vele andere mogelijkheden voor het vullen van de arrays die de tags en te vervangen waardes (l_names en l_values) bevatten. Deze data hiervoor kan ook bijvoorbeeld in een tabel staan, of uit een view gehaald worden. Het gevolg daarvan is dat het onderhoud aan de bovenstaande procedure dan geminimaliseerd kan worden.

De functie "ooxml_util_pkg.get_file_from_template" werkt zowel voor Word (docx), Excel (xlsx), of Powerpoint (pptx). Tevens bevat de ooxml_util_pkg packags diverse mogelijkheden om waardes uit specifieke cellen van een Excel document op te halen.

Conclusie

Wat ik hier heb laten zien is dat met minimale aanpassingen van een Word template, in combinatie met een eenvoudige APEX-applicatie en de kant en klare Oracle packages, integratie van Office met de Oracle database tot de mogelijkheid hoort voor elke applicatie. Een "proof of concept" zou binnen een dag opgeleverd kunnen worden.

De hier getoonde opzet is niet echt geschikt voor het automatisch genereren van bulk documenten, maar meer voor het ad-hoc maken van een specifieke brief voor een persoon. Voor een meer geautomatiseerd proces zou Oracle BI Publisher of Oracle Reports meer geschikt zijn.

Links

Waardering:
 

Reacties

Dit klinkt allemaal heel hoopvol, maar wat ik me afvraag is of dit ook toepasbaar is in batches. In deze batches maak je vanuit 1 template document 1 output document welke meerdere keren de brief (welke b.v. 1 pagina uit de template is) bevat.

 

Hiervoor moet je de beschikking hebben in Alexandria om binnen je template iets van 'loops' te definieeren zodat je repeterende blokken gaat krijgen. Is dit mogelijk of niet?

 

Ook zou je moeten kunnen beschikken over IF constructies, om conditioneel paragraven weer te geven bijvoorbeeld... ook dit zie ik hierboven niet terugkomen, maar is wellicht wel mogelijk.

 

Wie weet hier de antwoorden op?

Beste Michel,

Dit is inderdaad een mooie oplossing om brief-like documenten aan te maken. Te meer omdat je geniet van alle formatteer mogelijkheden van MS-Word, waardoor je een pixel-perfect layout kan benaderen.

Bij iAdvise hebben wij die oplossing ook een aantal keer gebruikt. Maar uiteindelijk zijn wij na verloop van tijd nog een stapje verder gegaan. In jouw oplossing moet je voor elk rapport dat je wenst te bouwen een specifiek PL/SQL procedure maken waarin je je SQL statement(s) zet en ook alle 'replacement tags' moet programmeren. Wij hebben een tool ontwikkeld waarin we de SQL statements (en parameters en de templates) in metadata stoppen -- dit ligt volledig in de lijn van de APEX filosofie --. Daarnaast hebben we dan een PL/SQL engine geschreven die dynamisch alle SQL statements uitvoert én de tags in je DOCX-template vervangt.

Dit maakt het genereren van rapporten vanuit APEX nog eenvoudiger.

Wil je meer info over die oplossing, dan kan je altijd naar www.doxxy.eu navigeren of een mail sturen naar info@doxxy.eu

Mvg,

Het Doxxy-team

Beste Michel,

Tot nu toe gebruikte ik rtf sjablonen om min of meer op de manier zoals hierboven door Doxxy-team is aangegeven, metadata gestuurd, brieven/documenten met data uit een database te genereren.

Probleem bij de rtf sjablonen is dat de tags bij het omzetten van een word document naar een rtf sjabloon vaak in stukken, omgeven met rtf stuurinformatie, verdeeld worden. Het zoeken en vervangen van de tags met gegevens uit de database gaat dan mis. Het corrigeren van die tags in rtf sjablonen
is een beetje een corvee klus, weinig uitdagend.

Ik heb je tip over het voorkomen van breuken in de tags toegepast, ik was net bezig met een omvangrijk document: zeer goede goede tip.

Met vriendelijke groet,

Frans Kuip

Nieuwe reactie inzenden

De inhoud van dit veld is privé en zal niet openbaar worden gemaakt.

Meer informatie over formaatmogelijkheden

CAPTCHA
Deze vraag is om te testen of u een persoon bent en om spam te voorkomen
Image CAPTCHA
Enter the characters shown in the image.