OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Comprehensive MailMerge for Flat Files
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Mon Mar 29, 2004 1:56 pm    Post subject: Comprehensive MailMerge for Flat Files Reply with quote

As part of our deployment of OpenOffice (we went to the field with 1.1.1) as part of our application, we needed to develop a set of "enterprise grade" macros that would allow us to do MailMerges "headless" on remote servers and workstations (see my post Linux: Automated OOo User Workstation Installation for information on how to set up a Linux system to run OpenOffice "headless" using the X Windows Virtual Frame Buffer Server). We also needed to work around several bugs and design limitations of OpenOffice in the process. Specifically:
    Issue #25686: MailMerge.OutputType=PRINTER hangs OOo on Linux
    Issue #26678: Need ability to set PrintOptions on MailMerge
    Issue #16297: Save with password automatically set without user notification
    The need to merge to a single Postscript output file from the (part of Issue #26678)
    The ability to dynamically instantiate and revoke data sources on a per-document basis
    The ability to receive detailed status and error information in a "headless" invocation
    And while I'm at it, allowing merging to a single .sxw document

I will be presenting the code in four sections:
    Utility functions/subroutines
    Merging to a set of OpenOffice.org files (.sxw)
    Merging to a printer (or printer files or files, and also to a single .sxw file)
    Editing a document for use as a merge template

As I present each, I will also present the API documentation for each, examples, etc.

The code is being released under the GPL (Note: not the LGPL!) for all to use. All documentation is released under the GFDL... and an .sxw of it will be made available once I find somewhere I can put it...

Next post: The utility functions...


Last edited by jbotte on Fri Jul 09, 2004 7:08 am; edited 1 time in total
Back to top
View user's profile Send private message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Wed Mar 31, 2004 12:19 pm    Post subject: The Utility Functions/Subroutines Reply with quote

The following code chunks do some stuff that is used by more than one of the API functions. Each function or subroutine is fairly well documented by itself... if you have any questions about any of these, do not hesitate to ask (via email preferably... I'll post important clarifications to this thread)! Also, if you make any changes to the code that fixes anything or adds enhancements (or you port it to another platform, etc.), please send me the code (or a "diff -c" of what you did), and I'll consider it for inclusion in a future release of the package).

Of particular bizarreness is the function LogMessage() that allows the programs to output messages to stdout. Be aware that if there is already an instance of OOo running, the new invocation will merely dispatch the job to it and exit, and these messages will come out of the stdout of the first instance, which is not necessarily what you might have expected... I am working on a generalized solution to this problem under Linux (and also a way of being able to have the macros cause an exit code value to be passed back to the shell... more later, in a different post).

The CreateUniqueFolder() function was needed primarily because of Issue #25686 where the MailMerge.OutputType=PRINTER hangs OOo on Linux (and purportedly crashes OOo on Solaris as per Issue #23426); however, it's probably going to stay because it provides an infrastructure to merge to a single document as well as (to allow, for instance, the results of a merge to be edited manually, or to become a new document based on merge data, e.g. populating a reference manual with data extracted from program source code...).

Note also, that before you get all hyped up and start copying these macros into the OOo macro editor, you will either need to strip the comments (this worked for me) or break these up into different modules as per Issue #26345: macro size limits... which appears to place a (I'm guessing) 64K limit on the size of the source code (... <flame>I thought this programming style went out with the VIC20</flame>, but... it'll be fixed and OOo is still great ...).

To create the module, you need for perform the following steps in OOo:
    - Start openoffice (with or without a document... you'll be using the macro editor)
    - Open the macro editor dialogue by selecting "Tools->Macros->Macro..."
    - Select the "Organizer..." button to open the "Macro Organizer" dialogue
    - Select the "Libraries" tab at the top of the "Macro Organizer" dialogue and press the "New..." button
    - Type in the name Medformix in the "New Library" dialogue, then press OK
    - Select the "Modules" tab at the top of the Macro Organizer dialogue, then select the "Medformix" library and press the "New Module..." button
    - Type in the name FlatMerge in the "New Module" dialogue, then press OK
    - When the Medformix library was created, OOo automatically created a module in it called "Module1", select this module then press the "Delete" button and respond "Yes" when asked if you really want to
    - You can now edit the module by selecting the "FlatMerge" module, and pressing the "Edit" button
    - The "blank" module will contain:
    Code:
    REM  *****  BASIC  *****

    Sub Main

    End Sub

    You should select and delete this code, it won't be needed, then paste in the code from these messages...
The libraries and modules are created locally for the user you are when you do it. To install the modules for all users on the system, check out my post: Linux: Automated OOo User Workstation Installation.

Code:
'**************************************************************************

'
'   Medformix.FlatMerge OpenOffice macro library
'
'**************************************************************************
'
' Copyright (c) 2004 Crowell Systems
' http://www.crowellsystems.com/
'
' This file is part of the Medformix.FlatMerge OpenOffice macro library.
'
' The Medformix.FlatMerge OpenOffice macro library is free software; you
' can redistribute it and/or modify it under the terms of the GNU General
' Public License as published by the Free Software Foundation; either
' version 2 of the License, or (at your option) any later version.
'
' The Medformix.FlatMerge OpenOffice macro library is distributed in the
' hope that it will be useful, but WITHOUT ANY WARRANTY; without even the
' implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
' See the GNU General Public License for more details.
'
' You should have received a copy of the GNU General Public License along
' with the Medformix.FlatMerge OpenOffice macro library; if not, write to
' the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
' MA 02111-1307 USA or view it at "http://www.gnu.org/licenses/gpl.html".
'
'**************************************************************************
' Contributors:
'   jbotte: James Botte, jbotte@crowellsystems.com
'**************************************************************************
' ChangeLog:
'   0.1.1: 02feb04: jbotte: Used only for testing code snippets.
'   0.1.2: 03feb04: jbotte: Created File, LogMessage, and
'          URLCharToString macros. Used for internal testing only.
'   0.1.3: 11feb04: jbotte: Created Edit macro. Revised File macro and
'          other subroutines. Broken listener. Used for internal testing.
'   0.1.4: 12feb04: jbotte: Fixed the listener, data source is now revoked.
'   0.1.5: 13feb04: jbotte: Added Printer macro for sending to the Default
'          printer only. Broken print to file capability. Broken send to
'          different printer capability.
'   0.1.6: 18feb04: jbotte: Added tests for FileName URL in Printer macro.
'          Added On Error Goto code to all functions. Changed default for
'          Collate to "True". Removed vestigial code leftover from testing
'          in the Edit macro (was preventing listener from working). There
'          seems to be a bug in MailMerge PRINTER that causes soffice to
'          hang after the merge is complete (workaround is to have another
'          copy of soffice running in the background before attempting
'          to invoke the macro from the command line). Posted to OOo issue
'          tracker as part of Issue #25686 to help debugging effort.
'   0.1.7: 20feb04: jbotte: Rewrote Printer to use the FILE based MailMerge
'          and using the output to create a printer file rather than trying
'          to use the buggy MailMerge.OutputType=PRINTER code. Found the
'          undocumented PrintOptions "Wait" property and that allowed for
'          the use of synchronous printing without the fuss of setting a
'          listener... logged Issue #25742 against the API documentation to
'          track the "bug".
'   0.1.8: 23feb04: jbotte: Inlined SetDefaultDocProps (only used once).
'          Fixed problem with page breaking (required a paragraph break
'          before inserting the next document if there was no carriage
'          return at the end of the document).
'   0.1.9: 15mar04: jbotte: Fixed some code formatting. Changed all
'          declarations to UNO types to be done with a "New". Holding off
'          on Medformix error codes until Medformix 12.3. Changed listener
'          connection in function Edit to use explicit type declaration
'          oDoc.com_sun_star_document_XEventBroadcaster_addEventListener
'          so a "notifyEvent" listener could be set (with just the
'          oDoc.addEventListener invocation it used the
'          com.sun.star.lang.XEventListener addEventListener interface).
'          Added the DocListener_notifyEvent function to watch for when the
'          user saves their document. This function checks to see if the
'          "phantom password" bug is triggered in this document, and
'          automatically resaves the document with the "phantom password"
'          argument cleared [a workaround for Issue #16297].
'   0.2.0: 16mar04: jbotte: Up-reved to 0.2.x for feature freeze and entry
'          into Beta test. Changed DocListener_notifyEvent to only respond
'          to "OnSaveDone" rather than both it and "OnSaveAsDone" because
'          there's no way to save a blank password from the SaveAs...
'          dialogue box. Also added documentation to all functions and
'          subroutines in preparation for general release.
'   0.2.1: 18mar04: jbotte: Use actual source document as the empty
'          template when doing a Printer() instead of using factory:writer
'          to create a new document. This preserves the margins and other
'          page format information from the source document in the output.
'   0.2.2: 12apr04: jbotte: Added option to specify {None} as printerName
'          in sPrinterSpec to cause Printer() to save to an OOo file.
'   0.2.3: 12jul04: jbotte: Fixed page numbering in Printer(). Added new
'          sPrinterSpec parameters: ResetPageNums and StartOnRight and the
'          code to support them. Last known bug is the document page total.
'**************************************************************************


' ++ These constants are used by multiple functions or subroutines or might
' ++   need to be changed for a particular installation.

' Set this to the correct threshold for message output (0 suppresses most
'   but 7 allows all messages to be output, including debugging info)
Const nMsgThreshold As Integer = 5

' This is the number of times CreateUniqueFolder() will try to create a folder
'   with a unique name before giving up. The chances of going past one attempt
'   are astronomically small... I can't imagine getting up to 10 unless something
'   was wrong with the system it was running on.
Const eTMPDIR_LOOPMAX As Integer = 10

' These should be used for the nMsgLevel argument to LogMessage() to allow
'   for eventual internationalization. See the Un*x man page for syslog(2) for
'   more information on these "message levels".
Const eMSG_EMERG    As Integer = 0
Const eMSG_ALERT    As Integer = 1
Const eMSG_CRIT     As Integer = 2
Const eMSG_CRITICAL As Integer = 2
Const eMSG_ERR      As Integer = 3
Const eMSG_ERROR    As Integer = 3
Const eMSG_WARN     As Integer = 4
Const eMSG_WARNING  As Integer = 4
Const eMSG_NOTICE   As Integer = 5
Const eMSG_INFO     As Integer = 6
Const eMSG_DEBUG    As Integer = 7

' These constants are used to reference array elements when setting objects and are
'   used by SetDefaultDbProps(), and the File() and Printer() macros.
Const eDBPROP_EXTENSION         As Integer = 0
Const eDBPROP_FIELDDELIMITER    As Integer = 1
Const eDBPROP_STRINGDELIMITER   As Integer = 2
Const eDBPROP_DECIMALDELIMITER  As Integer = 3
Const eDBPROP_THOUSANDDELIMITER As Integer = 4
Const eDBPROP_HEADERLINE        As Integer = 5
Const eDBPROP_CHARSET           As Integer = 6

Const eHIGHEST_DBPROP           As Integer = 6


' ++ These are the "helper" functions and subroutines that are used by more than one
' ++   of the core/highest level functions (the core functions are intended to be
' ++   invoked from the command line, these functions/subroutines usually are not).

' Subroutine: SetDefaultDbProps
' Description:
'   This helper subroutine is used by File() and Printer() to set the default values
'   of their database properties array.
' Global Variables:
'   eMSG_*: These global constants are used to tag the importance of a message.
' Arguments:
'   mDbProperties: This is an array of com.sun.star.beans.PropertyValues of the
'     correct size to populate the default values that might need to be changed. It
'     is passed by reference, so its contents are changed by this subroutine.
' History:
'   16mar04: jbotte: Added header comment block. Added error checking.

Sub SetDefaultDbProps(mDbProperties() As com.sun.star.beans.PropertyValue)
   On Error Goto labelErrorHandler
   
   mDbProperties(eDBPROP_CHARSET).Name = "Charset"
   mDbProperties(eDBPROP_HEADERLINE).Name = "HeaderLine"
   mDbProperties(eDBPROP_EXTENSION).Name = "Extension"
   mDbProperties(eDBPROP_FIELDDELIMITER).Name = "FieldDelimiter"
   mDbProperties(eDBPROP_STRINGDELIMITER).Name = "StringDelimiter"
   mDbProperties(eDBPROP_DECIMALDELIMITER).Name = "DecimalDelimiter"
   mDbProperties(eDBPROP_THOUSANDDELIMITER).Name = "ThousandDelimiter"
   
   ' And then the defaults for this application
   mDbProperties(eDBPROP_CHARSET).Value = "iso-8859-1"
   mDbProperties(eDBPROP_HEADERLINE).Value = True
   mDbProperties(eDBPROP_FIELDDELIMITER).Value = Chr(9)  ' The tab character
   mDbProperties(eDBPROP_STRINGDELIMITER).Value = """"   ' A double quote
   mDbProperties(eDBPROP_DECIMALDELIMITER).Value = "."
   mDbProperties(eDBPROP_THOUSANDDELIMITER).Value = ","
   
   Exit Sub
labelErrorHandler:
   LogMessage(eMSG_CRIT, "Medformix.FlatMerge.SetDefaultDbProps script [line: " + Erl + _
      "] error [" + Err + "]: " + Error$)
   Exit Sub
End Sub


' Function: LogMessage
' Description:
'   This function takes an "error level" and a string and outputs that message
'   to Un*x standard output as part of the infrastructure of running macros "headless"
'   from the command line. It prefixes the string with the syslog() style message
'   importance (e.g. ERROR: , CRITICAL: , etc.).
' Global Variables:
'   nMsgThreshold: This integer is set to a number between 0 and 7 inclusive and is the
'     threshold for what messages get output by the LogMessage facility (it is set on a
'     macro-wide basis). If a message has an nMsgLevel that is equal to or lower than
'     nMsgThreshold, it is output, otherwise it is ignored.
' Arguments:
'   nMsgLevel: This an integer that describes the "importance" of the message as
'     per the syslog() Un*x facility (0 through 7). A set of global constants is provided
'     to use to allow this to be set symbolically (see Global Variables). This value
'     will also be translated into a prefix for sMessage (see sMessage).
'   sMessage: This is the string to output. It will have a prefix added depending on the
'     nMsgLevel of the message like the syslog() Un*x facility.
' Return: Boolean
'   Returns a True if successful and a False if it or any shell commands fail.
' Notes:
'   See the Un*x man page syslog(2) for information on how to use the nMsgLevels.
'   This version of LogMessage just uses a shell to execute the program "/bin/echo". This
'     program needs to be present at that location (or the script needs to be modified)
'     for this to work. One issue that you will find is that if there is already a copy
'     of OOo running for a user, LogMessage will output to the stdout of the first
'     instance of OOo running for that user and not to the stdout of the console/xterm
'     or whatever that is reading the stdout of the instance that launched these macros.
'   One of the reasons for doing it this way (using numbers for nMsgLevel) is that it
'     lends itself easily to internationalization. Add it to the "to do" list...
' History:
'   16mar04: jbotte: Added header comment block.

Function LogMessage(nMsgLevel As Integer, sMessage As String) As Boolean
   On Error Goto labelErrorHandler
   
   Dim nReturnCode As Integer
   
   If nMsgLevel > nMsgThreshold Then
      LogMessage() = True
      Exit Function
   End If
   
   Select Case nMsgLevel
      Case 0:
         sMsgLevel = "EMERGENCY: "
      Case 1:
         sMsgLevel = "ALERT: "
      Case 2:
         sMsgLevel = "CRITICAL: "
      Case 3:
         sMsgLevel = "ERROR: "
      Case 4:
         sMsgLevel = "WARNING: "
      Case 5:
         sMsgLevel = "NOTICE: "
      Case 6:
         sMsgLevel = "INFO: "
      Case 7:
         sMsgLevel = "DEBUG: "
      Case Else:
         sMsgLevel = ""
   End Select
   
   nReturnCode = Shell("/bin/echo", 0, Chr(13) + Chr(10) + sMsgLevel + sMessage)
   
   If nReturnCode = 0 Then
      LogMessage = True
   Else
      LogMessage = False
   End If
   
   Exit Function
   
labelErrorHandler:
   MsgBox "Medformix.FlatMerge.LogMessage script [line: " + Erl + _
      "] error [" + Err + "]: " + Error$
   LogMessage() = False
   Exit Function
End Function


' Function: URLCharToString
' Description:
'   Takes a URL "escape code" hexadecimal character (e.g. %20 is the "space" character)
'   and converts it into its regular character format and returns it. Extensive error
'   checking is done on the passed in string. Also, if a single regular character is
'   passed in, it is just returned (this means that a character specification can just
'   be passed to this function without checking to see what format it's in first).
' Global Variables:
'   eMSG_*: These global constants are used to tag the importance of a message.
' Arguments:
'   sCharValue: The character or URL "escape code" to convert.
' Return: String
'   This is either the character that was passed in (if it was only a single character)
'   or the character that was represented by the "escape code" (e.g. %20 is converted to
'   an ASCII "space" character). Note: The OOo Basic function Chr() is used...
' Notes:
'   This will probably need to cope with Unicode some day. More "to do" list...
' History:
'   16mar04: jbotte: Added header comment block.

Function URLCharToString(sCharValue As String) As String
   On Error Goto labelErrorHandler

   Dim nChar, nCharValue As Integer
   
   If Len(sCharValue) <= 1 Then
      URLCharToString() = sCharValue
      Exit Function
   End If
   
   If Mid(sCharValue, 1, 1) = "%" Then
      If Len(sCharValue) = 3 Then
         nChar = Asc(LCase(Mid(sCharValue, 2, 1)))
         If (nChar < Asc("a") Or nChar > Asc("f")) And _
           (nChar < Asc("0") Or nChar > Asc("9")) Then
            LogMessage(eMSG_NOTICE, _
              "Invalid character specifier passed to URLCharToString: " + sCharValue)
            URLCharToString() = sCharValue
            Exit Function
         End If
         
         If nChar >= Asc("a") And nChar <= Asc("f") Then
            nCharValue = (nChar - Asc("a") + 10) * 16
         Else
            nCharValue = (nChar - Asc("0")) * 16
         End If
         
         nChar = Asc(LCase(Mid(sCharValue, 3, 1)))
         If (nChar < Asc("a") Or nChar > Asc("f")) And _
           (nChar < Asc("0") Or nChar > Asc("9")) Then
            LogMessage(eMSG_NOTICE, _
              "Invalid character specifier passed to URLCharToString: " + sCharValue)
            URLCharToString() = sCharValue
            Exit Function
         End If
         
         If nChar >= Asc("a") And nChar <= Asc("f") Then
            nCharValue = nCharValue + (nChar - Asc("a") + 10)
         Else
            nCharValue = nCharValue + (nChar - Asc("0"))
         End If
         
         ' Success in converting two digit hex value
         URLCharToString() = Chr(nCharValue)
         Exit Function
      Else
         LogMessage(eMSG_NOTICE, _
           "Invalid character specifier passed to URLCharToString: " + sCharValue)
         URLCharToString() = sCharValue
         Exit Function
      End If
   Else
      LogMessage(eMSG_NOTICE, _
        "Invalid character specifier passed to URLCharToString: " + sCharValue)
      URLCharToString() = sCharValue
      Exit Function
   End If
   
labelErrorHandler:
   LogMessage(eMSG_CRIT, "Medformix.FlatMerge.URLCharToString script [line: " + _
      Erl + "] error [" + Err + "]: " + Error$)
   URLCharToString() = ""
   Exit Function
End Function


' Function: CreateUniqueFolder
' Description:
'   This function creates a subdirectory with a unique name in the directory specified
'   in sTempDirURL. The format of the name will be <sTempDirURL>/YYYYMMDDhhmmss.XXXXXX
'   where "XXXXXX" is a six digit random number generated when this function is invoked
'   (each invocation will result in a different number). The subdirectory is created
'   with an atomic transaction and, if successful, the name of the resultant directory
'   is returned. Since it is theoretically possible to generate the same random number
'   more than one time in a day, CreateUniqueFolder() loops up to eTMPDIR_LOOPMAX times.
' Global Variables:
'   eMSG_*: These global constants are used to tag the importance of a message.
' Arguments:
'   sTempDirURL: This is the properly formed URL (e.g. "file:///tmp") of the directory
'     to use to create the subdirectory in. The user must have write access to that location.
' Return: String
'   This function returns the full, properly formed, URL for the resultant directory, or ""
'   if the directory could not be created.
' History:
'   16mar04: jbotte: Added header comment block.

Function CreateUniqueFolder(sTempDirURL As String) As String
   On Error Goto labelErrorHandler
   
   Dim dCurrentDate As Date
   Dim sDate As String
   
   ' Set as default return in case of error
   CreateUniqueFolder() = ""
   
   ' Get current date and time for creating unique identifier and format for fixed length
   dCurrentDate = Now()
   sDate = Format(Year(dCurrentDate), "0000") + Format(Month(dCurrentDate), "00") + _
     Format(Day(dCurrentDate), "00") + Format(Hour(dCurrentDate), "00") + _
     Format(Minute(dCurrentDate), "00") + Format(Second(dCurrentDate), "00")

   Dim oFileAccess As Variant
   oFileAccess = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
   
   If Not oFileAccess.Exists(sTempDirURL) Then
      LogMessage(eMSG_CRIT, _
         "CreateUniqueDirectory temporary directory/folder does not exist: " + sTempDir)
      Exit Function
   End If
   
   If Not oFileAccess.IsFolder(sTempDirURL) Then
      LogMessage(eMSG_CRIT, _
         "CreateUniqueDirectory temporary directory/folder is not a directory: " + sTempDir)
      Exit Function
   End If

   Dim nI As Integer
   Dim bDirCreated As Integer
   Dim sUniqueFolder As String
   ' Index to the last character in the string
   nI = Len(sTempDirURL)
   If Mid(sTempDirURL, nI, nI) = "/" Then
      sTempDirURL = sTempDirURL + sDate
   Else
      sTempDirURL = sTempDirURL + "/" + sDate
   End If
   
   nI = 0
   bDirCreated = False
   
   Do While nI < eTMPDIR_LOOPMAX And Not bDirCreated
      sUniqueFolder = sTempDirURL + "." + Format(Int((Rnd(235.532) * 999999) + 0.5), "000000")
      ' CreateFolder only throws an error if it fails, it returns Void
      On Error Goto labelDirCreateFailed
      oFileAccess.CreateFolder(sUniqueFolder)
      bDirCreated = True
labelDirCreateFailed:
      ' Reset it just in case...
      On Error Goto labelErrorHandler
   Loop
   
   If Not bDirCreated Then
      LogMessage(eMSG_WARN, "CreateUniqueFolder failed after " + eTMPDIR_LOOPMAX + _
        " attempts: " + sTempDirURL + ".Rnd()")
      CreateUniqueFolder() = ""
   Else
      CreateUniqueFolder() = sUniqueFolder
   End If
   
   Exit Function
   
labelErrorHandler:
   LogMessage(eMSG_CRIT, "Medformix.FlatMerge.CreateUniqueFolder script [line: " + Erl + _
     "] error [" + Err + "]: " + Error$)
   CreateUniqueFolder() = ""
   Exit Function
End Function


Next post: The Medformix.FlatMerge.File() macro...


Last edited by jbotte on Wed Jul 14, 2004 7:24 am; edited 4 times in total
Back to top
View user's profile Send private message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Thu Apr 01, 2004 8:26 am    Post subject: Medformix.FlatMerge.File API v1.1 Reply with quote

Copyright (c) 2004 Crowell Systems -- http://www.crowellsystems.com/. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, with the Front-Cover Texts being "Medformix.FlatMerge OpenOffice.org Macro Library", and no Back-Cover Texts. A copy of the license is available by writing to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA or it can be viewed at "http://www.gnu.org/licenses/fdl.html".

Medformix.FlatMerge.File API v1.1 Documentation

Overview

This macro takes an OpenOffice.org (OOo) document and a Comma Seperated Value (CSV) flat file data source and does a MailMerge with them to a set of files: one per record in the data source. There are many options on how this is done, for more information on the options, see Invocation Syntax below and the OOo API documentation (available at the OOo web site http://api.openoffice.org/). Note: To MailMerge to a printer file, use the macro Medformix.FlatMerge.Printer.

Because this macro is intended to be used in an "enterprise" environment, running on a remote server with no operator present, it is designed to run -invisible and -headless. To that end, it uses the shell's stdout facility to pass status information back to the program that invoked it. By capturing the stdout of the soffice program while running this macro, it is possible to determine what, if any, errors or warnings there were; and whether or not the macro thought it ran successfully. The output files can be checked for as well, but the macro has extensive error checking built in.

Changes since API v1.0

The changes from the first "official" version of the API (v1.0) are the addition of the {None} option for delimiters specified as part of sDataSourceURL, and the method that a fileNamePrefix is specified with sOutputURL has changed. It now requires a ? between the path portion of the URL and the fileNamePrefix. A few minor editorial changes have also been made to clarify certain points.

Invocation Syntax

Code:
Medformix.FlatMerge.File(<sTemplateURL>, <sDataSourceURL>, <sOutputURL>, <sColumnName>)

where (N.B. path in any of the URLs can be absolute or relative):
    - sTemplateURL is the file:// URL to the template document (.sxw file). For the Medformix.FlatMerge.File macro, this file must exist. The format for the sTemplateURL parameter is:

      file://path/fileNamePrefix.sxw

    For example:
    Code:
    file:///mfx/12002/OOo/inst/docs/1_101.sxw
    file://~/2_Aardvark.sxw file://../.mfx/Elmer_Fudd.sxw
    file://./Wascawy_Wabbit.sxw
    - sDataSourceURL is the proprietary file:// URL to the data source file (usually a .csv file, but it can have any suffix). Note: csv stands for Comma Separated Value; however, any delimiter can be specified. For an excellent overview of csv files, format, and issues, see The Comma Separated Value (CSV) File Format (How To) by John Rapici. For the Medformix.FlatMerge.File macro, this file must exist. The format for the sDataSourceURL parameter is:

      file://path/fileNamePrefix[.fileNameSuffix][?name=value[&name=value]...]

    where fileNamePrefix.fileNameSuffix is the full name of the file; if no fileNameSuffix is specified, then the period (.) should not be included as this will be stripped anyway; if options are specified, the first must be preceeded by a question mark (?), and all subsequent options must be preceeded by an ampersand (&); and any number of options may be specified and will be passed to the appropriate object or set on the appropriate object. Recognized properties include (these apply to the com.sun.star.sdb.DataSource object for the most part):


    Notes: The names of the arguments are case insensitive, and will be recognized with any capitalization, so use what is clearest for your application. The Extension property of com.sun.star.sdbc.FLATConnectionProperties is automatically set to the value of fileNameSuffix. Also, when specifying delimiters or strings on the command line, the standard URL %XX construction may be used, where XX is a pair of hexadecimal digits that specify the character to use (e.g. %09 is the tab character, %20 is a space). This allows arbitrary characters to be specified without worrying about how they will be interpreted by the shell or some component between the shell and the macro. Where a character is known to cause no problems on a command line, and will not be confused with the reserved shell delimiter characters, it can be used directly (e.g. the colon character (:), etc.). The percent sign character (%) can be specified on its own if desired, and it will be interpreted as the percent sign character. Filter is an SQL statement that will be executed by the MailMerge object on the data source before doing a mail merge. If an explicit no delimiter condition is desired, the special value {None} can be specified. The default values for the arguments (for our application, as set in the macro) are:
    Code:
    Charset = "iso-8859-1"
    HeaderLine = True
    FieldDelimiter = Chr(9)  ' The tab character
    StringDelimiter = "
    DecimalDelimiter = .
    ThousandDelimiter = ,
    Filter = {None}

    For more information about these properties, see com.sun.star.sdbc.FLATConnectionProperties, com.sun.star.sdbc.FILEConnectionProperties, com.sun.star.sdb.DataAccessDescriptor, the StarOffice 6.0 Office Suite -- Basic Programmer's Guide, and the StarOffice Programmer's Tutorial.

    For example:
    Code:
    file:///home/mfx123/mergedata.csv
    file://~/bobsyouruncle.csv?DecimalDelimiter=,&ThousandDelimiter={None}
    file:///home/mfx123/odddata.csv?FieldDelimiter=%FE&StringDelimiter=:
    - sOutputURL is the optional file:// URL to the output directory or filename template. If this parameter is specified, a valid and existing directory path must be specified. If it is just a valid directory path, then it is used as the directory (or folder as it is called in OpenOffice.org) that is used to output the merged document files (one file will be generated for each merge record, see below for information on how the files will be named). If the URL also specifies a fileNamePrefix, then this will be used as the template for the file names created in the directory path specified (they will have names fileNamePrefix0.sxw, fileNamePrefix1.sxw, ... fileNamePrefixN.sxw where N is the number of rows in the data source after any Filter has been applied. If there are already files with that fileNamePrefix in the directory path, then the numbering does not start at 0, but rather starts with the next number after the highest numbered file in that directory). Also, a fileNamePrefix and the sColumnName parameter cannot both be supplied &mdash; this will result in an error. If sOutputURL is not specified, then the path for the files will be taken from the URL of the sTemplateURL. If the fileNamePrefix is not specified, the template for the file names wil be taken from the sTemplateURL specification. The format for the optional sOutputURL parameter is:

      file://path[?fileNamePrefix]

    For example:
    Code:
    file://home/mfx235
    file://home/mfx235/
    file://home/mfx235/tmp?mergeOut
    - sColumnName is the optional name of the data source column (from the sDataSourceURL file) to use to generate the file names. If specified, the file name prefixes will be set to the value of the data in the named column of the row that is used to generate the merged file (they will have the fileNamePrefix as retrieved from the data source, a sequential number, and the file name suffix .sxw. If there is already a file with that filename structure, then the next sequential number will be used). Needless to say, the data source CSV file must have a header line for this option to work. A valid header line containing the sColumnName string is verified before the merge is attempted. Note: The directory that these files are put into is either set by the sOutputURL, if it is specified, otherwise it is put in the same directory as the sTemplateURL specified for the template document.


    For example, given the data source:
    Code:
    CustID,LastName,FirstName,Shoe Size
    0001a,Thumb,Tom,0.2
    0002a,Giant,Jolly Green,256
    0003a,Average,Mary Joe,7

    Specifying the sColumnName as CustID would result in the following files being generated: 0001a0.sxw (containing the information from Tom Thumb), 0002a0.sxw (containing the information from Jolly Green Giant), and 0003a0.sxw (containing the information from Mary Joe Average). Invoking the macro again without cleaning up the directory would result in the following files being generated: 0001a1.sxw, 0002a1.sxw, 0003a1.sxw.
Note: All leading and trailing whitespace is explicitly stripped off all of the parameters passed into all of these macros, as it is not done automatically by OpenOffice.org.

Example

An example command line invocation on the following flat file is as follows (this presumes the existence of a previously defined template .sxw document with appropriate merge fields... in this example, UserName, Password, UserID, GroupID, GECOS, HomeDir, and Shell merge fields):

Sample data source (a short slice from a Linux /etc/passwd file):
Code:
UserName:Password:UserID:GroupID:GECOS:HomeDir:Shell
root:x:0:0::/root:/bin/bash
bin:x:1:1:bin:/bin:
daemon:x:2:2:daemon:/sbin:
adm:x:3:4:adm:/var/log:

Sample command line (backslashes are added at line ends for readability, it should be one long line when executed):
Code:
soffice "macro:///Medformix.FlatMerge.File(file:///home/jbotte/Projects/OOo/Test.sxw,\
  file:///home/jbotte/Projects/OOo/Test.csv?FieldDelimiter=:,\
  file:///home/jbotte/Projects/OOo/, UserName)"

The result will be four files (presuming no files already in that directory that matched the filename templates): root0.sxw, bin0.sxw, daemon0.sxw, adm0.sxw.


Last edited by jbotte on Mon Aug 02, 2004 12:47 pm; edited 4 times in total
Back to top
View user's profile Send private message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Thu Apr 01, 2004 8:30 am    Post subject: Medformix.FlatMerge.File Macro Code Reply with quote

And now that you know how to use it, here's the code (remember that OOo Basic has a size limit and you will either need to strip the comments or put these in seperate modules):
Code:
'**************************************************************************
'
'   Medformix.FlatMerge OpenOffice macro library
'
'**************************************************************************
'
' Copyright (c) 2004 Crowell Systems
' http://www.crowellsystems.com/
'
' This file is part of the Medformix.FlatMerge OpenOffice macro library.
'
' The Medformix.FlatMerge OpenOffice macro library is free software; you
' can redistribute it and/or modify it under the terms of the GNU General
' Public License as published by the Free Software Foundation; either
' version 2 of the License, or (at your option) any later version.
'
' The Medformix.FlatMerge OpenOffice macro library is distributed in the
' hope that it will be useful, but WITHOUT ANY WARRANTY; without even the
' implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
' See the GNU General Public License for more details.
'
' You should have received a copy of the GNU General Public License along
' with the Medformix.FlatMerge OpenOffice macro library; if not, write to
' the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
' MA 02111-1307 USA or view it at "http://www.gnu.org/licenses/gpl.html".

' Function: File
' Description:
'   This function takes a document and a flat file data source and does a MailMerge
'   with them to a set of files: one per record in the data source. To MailMerge to
'   a printer file, use the Printer() function. There are many, many options on how
'   this is done, for more information on the options, see Arguments and the API docs.
' Global Variables:
'   eMSG_*: These global constants are used to tag the importance of a message.
' Arguments:
'   sTemplateURL: the "file://" URL to the template document (.sxw file). This file must
'     exist. The format is: "file://<path>/<fileNamePrefix>.sxw".
'   sDataSourceURL: is the proprietary "file://" URL to the data source file (usually a .csv
'     file, but it can have any suffix -- "csv" stands for Comma Separated Value; however,
'     any delimiter can be specified). The format is (see the API docs for more information):
'     "file://<path>/<fileNamePrefix>.<fileNameSuffix>[?<name>=<value>[&<name>=<value>]...]".
'     Valid properties are "Charset", "HeaderLine", "FieldDelimiter", "StringDelimiter",
'     "DecimalDelimiter", "ThousandDelimiter", and "Filter".
'   sOutputURL [optional]: is the optional "file://" URL to the output directory. If this
'     parameter is specified, a valid and existing directory path must be specified. If it is
'     just a valid directory path, then it is used as the destination directory/folder that
'     is used to output the merged document files (one file will be generated for each merge
'     record, filenaming is controlled by the optional <fileNamePrefix> parameter or the
'     optional sColumnName argument). The format is: "file://<path>[?<fileNamePrefix>]".
'   sColumnName [optional]: the optional name of the data source column to use to generate
'     the file names. If specified, the fileNamePrefixes will be set to the value of the data
'     in the named column of the row that is used to generate the merged file (they will have
'     the fileNamePrefix as retrieved from the data source, a sequential number, and the
'     fileNameSuffix ".sxw". If there is already a file with that filename structure, then the
'     next sequential number will be used). Needless to say, the data source csv file must have
'     a header line for this option to work. A valid header line containing the <sColumnName>
'     string is verified before the merge is attempted. Note: the directory that these files
'     are put into is either set by the sOutputURL, if it is specified, otherwise it is put in
'     the same directory as the sTemplateURL specified for the template document.
' Return: Boolean
'   Returns True if successful, or False if it fails.
' Notes:
'   You really need to check out the Medformix.FlatMerge API documentation and probably the
'     OpenOffice IDL Reference for this all to make any sense.
' History:
'   16mar04: jbotte: Added header comment block.

Function File(sTemplateURL As String, sDataSourceURL As String, Optional sOutputURL As String, _
  Optional sColumnName As String) As Boolean
    On Error Goto labelErrorHandler
   
    ' ++ Utility declarations used repeatedly in code
    Dim nI As Integer
    Dim bResult As Boolean
    Dim bIsOutputURL As Boolean
    Dim bIsColumnName As Boolean
    Dim mSplitOut() As String
    Dim mParmArgs() As String  ' Used for storage while processing arguments
    Dim bDbIsRegistered As Boolean
   
    ' Will cause the function to exit with the value False on Exit. Set to True if Exit okay...
    File() = False
   
    LogMessage(eMSG_DEBUG, "Running Medformix.FlatMerge.File macro...")
   
    ' Until it is registered, this should be set false. It is used for cleanup...
    bDbIsRegistered = False

    Dim oFileAccess As Variant
    oFileAccess = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")

    Dim oURLTransformer As Variant
    oURLTransformer = CreateUnoService("com.sun.star.util.URLTransformer")

    ' ++ Parse and validate the URLs from the command line

    ' + Start with the document URL which is a simple file:// type specification
    Dim aDocURL As New com.sun.star.util.URL
    aDocURL.Complete = Trim(sTemplateURL)

    bResult = oURLTransformer.parseSmart(aDocURL, "")
    If bResult = False Then
        LogMessage(eMSG_ERR, "Failed to parse document URL!")
        Goto labelExitClean
    End If
   
    If Not oFileAccess.exists(aDocURL.Main) Then
        LogMessage(eMSG_ERR, "Document URL does not exist: " + aDocURL.Main)
        Goto labelExitClean
    End If
   
    If oFileAccess.isFolder(aDocURL.Main) Then
        LogMessage(eMSG_ERR, "Document URL cannot be a folder/directory: " + aDocURL.Main)
        Goto labelExitClean
    End If
    ' + We have a valid document URL that exists as a file on the system
   
    ' + Now we tackle the data source URL, which is an unstandard specification. We need
    ' + to parse the passed URL into a valid file:// URL and the unstandard suffix, if any
    mSplitOut() = Split(sDataSourceURL, "?")
   
    ' Only allow zero or one ?s in the URL
    If UBound(mSplitOut) > 1 Then
        LogMessage(eMSG_ERR, "Malformed data source URL... too many ?s: " + sDataSourceURL)
        Goto labelExitClean
    End If

    Dim aDbURL  As New com.sun.star.util.URL
    aDbURL.Complete = Trim(mSplitOut(0))

    bResult = oURLTransformer.parseSmart(aDbURL, "")
    If bResult = False Then
        LogMessage(eMSG_ERR, "Failed to parse data source URL!")
        Goto labelExitClean
    End If
   
    ' Store the arguments, if any, in the URL.Arguments element of the structure
    ' Don't forget we've already taken care of the > 1 case above...
    If UBound(mSplitOut) = 1 Then
        aDbURL.Arguments = Trim(mSplitOut(1))
        If aDbURL.Arguments = "" Then
            LogMessage(eMSG_WARN, _
                "A data source argument was indicated with a ?, but not specified...")
        End If
    End If
   
    If Not oFileAccess.exists(aDbURL.Main) Then
        LogMessage(eMSG_ERR, "Data source URL does not exist: " + aDbURL.Main)
        Goto labelExitClean
    End If
   
    If oFileAccess.isFolder(aDbURL.Main) Then
        LogMessage(eMSG_ERR, "Data source URL cannot be a folder/directory: " + aDbURL.Main)
        Goto labelExitClean
    End If 
    ' + We have a valid data source URL that exists as a file on the system

    ' + Now we do the output URL, which is an unstandard specification. We need
    ' + to parse the passed URL into a valid file:// URL and the unstandard suffix, if any
    If Not IsMissing(sOutputURL) Then
        sOutputURL = Trim(sOutputURL)
        If sOutputURL = "" Then
            bIsOutputURL = False
            Goto labelSkipOutputURL
        Else
            bIsOutputURL = True
        End If
    Else
        bIsOutputURL = False
        Goto labelSkipOutputURL
    End If
   
    ' We know that it exists and is not an empty string, so start processing it
    mSplitOut() = Split(sOutputURL, "?")
   
    ' Only allow zero or one ?s in the URL
    If UBound(mSplitOut) > 1 Then
        LogMessage(eMSG_ERR, "Malformed output URL... too many ?s: " + sOutputURL)
        Goto labelExitClean
    End If

    Dim aOutURL As New com.sun.star.util.URL
    aOutURL.Complete = Trim(mSplitOut(0))

    bResult = oURLTransformer.parseSmart(aOutURL, "")
    If bResult = False Then
        LogMessage(eMSG_ERR, "Failed to parse output URL!")
        Goto labelExitClean
    End If
   
    ' Store the arguments, if any, in the URL.Arguments element of the structure
    ' Don't forget we've already taken care of the > 1 case above...
    If UBound(mSplitOut) = 1 Then
        aOutURL.Arguments = Trim(mSplitOut(1))
   
        If aOutURL.Arguments = "" Then
            LogMessage(eMSG_WARN, _
                "An output argument was indicated with a ?, but not specified...")
        End If
       
        ' Since we know we have a fileNamePrefix specified, we need to check
        ' to make sure that there is no sColumnName specified as this is an
        ' illegal invocation of this macro
        If Not IsMissing(sColumnName) Then
            ' Well, it seems to be there... even if it's empty, call it an error
            LogMessage(eMSG_ERR, _
                "An sOutputURL?fileNamePrefix and the sColumnName " + _
                "parameters cannot both be specified")
            Goto labelExitClean
        End If
    End If
   
    If Not oFileAccess.isFolder(aOutURL.Main) Then
        LogMessage(eMSG_ERR, _
            "Output URL must specify a valid folder/directory: " + aOutURL.Main)
        Goto labelExitClean
    End If
    ' + We have a valid output URL that exists as a directory on the system

labelSkipOutputURL:

    ' + If specified, trim the whitespace off sColumnName, and set the flag
    If Not IsMissing(sColumnName) Then
        sColumnName = Trim(sColumnName)
        If sColumnName = "" Then
            bIsColumnName = False
        Else
            bIsColumnName = True
        End If
    Else
        bIsColumnName = False
    End If

    ' + Now it's time to process and set any arguments to the DataSource and MailMerge objects
    Dim sDbName As String
    Dim oDbContext As Object
    Dim oDbInstance As Object
    Dim oMailMerge As Variant
   
    ' This array of PropertyValue's will be passed to the Data Source object
    Dim mDbProperties(eHIGHEST_DBPROP) As New com.sun.star.beans.PropertyValue
    SetDefaultDbProps(mDbProperties())  ' Get it set up with the default values

    ' Extract the fileNameSuffix and fileNameSuffix for the data source
    mSplitOut() = Split(aDbURL.Name, ".")
    sDbName = mSplitOut(0)
    If UBound(mSplitOut) = 0 Then
        mDbProperties(eDBPROP_EXTENSION).Value = ""
    Else
        mDbProperties(eDBPROP_EXTENSION).Value = mSplitOut(UBound(mSplitOut))
    End If
   
    ' Instantiate data source and mail merge objects and set what we know...
    oDbContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")

    If oDbContext.hasByName(sDbName) Then
        LogMessage(eMSG_NOTICE, "Data source name """ + _
            sDbName + """ existed... revoking from registry")
        oDbContext.revokeObject(sDbName)
    End If
       
    oDbInstance = oDbContext.createInstance()
    oDbContext.registerObject(sDbName, oDbInstance)
    bDbIsRegistered = True
   
    oDbInstance.URL = "sdbc:flat:" + aDbURL.Protocol + aDbURL.Path
   
    oMailMerge = CreateUnoService("com.sun.star.text.MailMerge")
    oMailMerge.DataSourceName = sDbName
    oMailMerge.DocumentURL = aDocURL.Main
    oMailMerge.CommandType = com.sun.star.sdb.CommandType.TABLE
    oMailMerge.Command = sDbName
    oMailMerge.OutputType = com.sun.star.text.MailMergeType.FILE
   
    ' Note that the mutually exclusive conditions have already been tested for
    ' (or will be tested for), so this is safe to do.
    If bIsOutputURL Then
        oMailMerge.OutputURL = aOutURL.Main
        If aOutURL.Arguments <> "" Then
            oMailMerge.FileNamePrefix = aOutURL.Arguments
        End If
    End If
   
    If bIsColumnName Then
        oMailMerge.FileNameFromColumn = True
        oMailMerge.FileNamePrefix = sColumnName
    Else
        oMailMerge.FileNameFromColumn = False
    End If
   
    ' Now we parse the arguments to sDataSourceURL, if any, and set oDbInstance.Info with them
    If aDbURL.Arguments <> "" Then
        ' Break apart multiple arguments (name=value) into an array of strings
        mParmArgs() = Split(aDbURL.Arguments, "&")
       
        For nI = 0 To UBound(mParmArgs)
            mSplitOut() = Split(mParmArgs(nI), "=")
            mSplitOut(0) = Trim(mSplitOut(0))
            If UBound(mSplitOut) = 0 Or UBound(mSplitOut) > 1 Then
                LogMessage(eMSG_ERR, "Malformed argument to sDataSourceURL: " + mParmArgs(nI))
                Goto labelExitClean
            End If
            mSplitOut(1) = Trim(mSplitOut(1))
            If mSplitOut(0) = "" Or mSplitOut(1) = "" Then
                LogMessage(eMSG_ERR, _
                    "Proper sDataSourceURL argument format is name=value: " + mParmArgs(nI))
                Goto labelExitClean
            End If
           
            ' Yup, every program has to have "special cases"...
            Select Case LCase(mSplitOut(0))
                Case "charset":
                    ' Just have to assume the format is correct...
                    mDbProperties(eDBPROP_CHARSET).Value = mSplitOut(1)
                Case "decimaldelimiter":
                    If LCase(mSplitOut(1)) = "{none}" Then
                        mDbProperties(eDBPROP_DECIMALDELIMITER).Value = ""
                    Else
                        mDbProperties(eDBPROP_DECIMALDELIMITER).Value = _
                            URLCharToString(mSplitOut(1))
                        If Len(mDbProperties(eDBPROP_DECIMALDELIMITER).Value) <> 1 Then
                            LogMessage(eMSG_ERR, _
                                "Invalid value specified for DecimalDelimiter argument: " + _
                                mSplitOut(1))
                            Goto labelExitClean
                        End If
                    End If
                Case "fielddelimiter":
                    If LCase(mSplitOut(1)) = "{none}" Then
                        mDbProperties(eDBPROP_FIELDDELIMITER).Value = ""
                    Else
                        mDbProperties(eDBPROP_FIELDDELIMITER).Value = _
                            URLCharToString(mSplitOut(1))
                        If Len(mDbProperties(eDBPROP_FIELDDELIMITER).Value) <> 1 Then
                            LogMessage(eMSG_ERR, _
                                "Invalid value specified for FieldDelimiter argument: " + _
                                mSplitOut(1))
                            Goto labelExitClean
                        End If
                    End If
                Case "filter":
                    ' Just pass it in untouched... nothing else we can do...
                    oMailMerge.Filter = mSplitOut(1)
                Case "headerline":
                    Select Case LCase(mSplitOut(1))
                        Case "true":
                            mDbProperties(eDBPROP_HEADERLINE).Value = "True"
                        Case "false":
                            If IsColumnName Then
                                LogMessage(eMSG_ERR, _
                                    "Specifying HeaderLine=False and an sColumnName " + _
                                    "parameter is invalid combination")
                                Goto labelExitClean
                            End If
                            mDbProperties(eDBPROP_HEADERLINE).Value = "False"
                        Case Else:
                            LogMessage(eMSG_ERR, _
                                "Invalid boolean specifier (True/False) " + _
                                "for HeaderLine argument: " + mSplitOut(1))
                            Goto labelExitClean
                    End Select
                Case "stringdelimiter":
                    If LCase(mSplitOut(1)) = "{none}" Then
                        mDbProperties(eDBPROP_STRINGDELIMITER).Value = ""
                    Else
                        mDbProperties(eDBPROP_STRINGDELIMITER).Value = _
                            URLCharToString(mSplitOut(1))
                        If Len(mDbProperties(eDBPROP_STRINGDELIMITER).Value) <> 1 Then
                            LogMessage(eMSG_ERR, _
                                "Invalid value specified for StringDelimiter argument: " + _
                                mSplitOut(1))
                            Goto labelExitClean
                        End If
                    End If
                Case "thousanddelimiter":
                    If LCase(mSplitOut(1)) = "{none}" Then
                        mDbProperties(eDBPROP_THOUSANDDELIMITER).Value = ""
                    Else
                        mDbProperties(eDBPROP_THOUSANDDELIMITER).Value = _
                            URLCharToString(mSplitOut(1))
                        If Len(mDbProperties(eDBPROP_THOUSANDDELIMITER).Value) <> 1 Then
                            LogMessage(eMSG_ERR, _
                                "Invalid value specified for ThousandDelimiter argument: " + _
                                mSplitOut(1))
                            Goto labelExitClean
                        End If
                    End If
                Case Else:
                    LogMessage(eMSG_ERR, _
                        "Invalid argument specified with sDataSourceURL: " + mSplitOut(0))
                    Goto labelExitClean
            End Select
        Next nI
    End If
   
    ' + Okay, now that we've checked for the exclusive condition of sColumnName specified
    ' + and HeaderLine=False, and we have a delimiter, we can verify that the header line
    ' + contains the sColumnName if one was specified.
   
    If bIsColumnName Then
        Dim bFoundColumnName As Boolean
        Dim nFileNo As Integer
        Dim sCurrentLine As String
   
        nFileNo = FreeFile
   
        Open aDbURL.Main For Input As nFileNo
   
        If EOF(nFileNo) Then
            LogMessage(eMSG_ERR, "The data source file is empty! " + aDbURL.Main)
            Goto labelExitClean
        End If
   
        Line Input #nFileNo sCurrentLine
        Close #nFileNo

        ' Parse the first line of the database using the specified field delimiter
        mSplitOut() = Split(sCurrentLine, mDbProperties(eDBPROP_FIELDDELIMITER).Value)
       
        bFoundColumnName = False
        For nI = 0 To UBound(mSplitOut)
            If Trim(mSplitOut(nI)) = sColumnName Then
                bFoundColumnName = True
                Exit For
            End If
        Next nI

        If bFoundColumnName = False Then
            LogMessage(eMSG_ERR, "Could not find specified sColumnName [" + _
                sColumnName + "] in header line of sDataSourceURL")
            Goto labelExitClean
        End If
       
        ' Otherwise it was found, and we can continue on our way...
    End If
   
    ' ++ Everything is ready, all that's left is the final initialization and execution
    Dim oDbConnection As Object

   
    oDbInstance.setPropertyValue("Info", mDbProperties())
   
    oDbConnection = oDbInstance.getConnection("", "")
   
    oMailMerge.ActiveConnection = oDbConnection
   
    Dim w() As New com.sun.star.beans.NamedValue  ' Empty placeholder for invocation
   
    oMailMerge.execute(w)
   
    ' Return with a True value, indicating success... then clean up
    File() = True
   
labelExitClean:
    ' Make sure the registered data source is cleaned up on exit
    If bDbIsRegistered Then
        oDbContext.revokeObject(sDbName)
        LogMessage(eMSG_DEBUG, "Data source " + sDbName + " successfully revoked")
    End If
   
    Exit Function
   
labelErrorHandler:
    LogMessage(eMSG_CRIT, "Medformix.FlatMerge.File script [line: " + Erl + _
        "] error [" + Err + "]: " + Error$
    File() = False
    Goto labelExitClean
End Function
Back to top
View user's profile Send private message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Thu Apr 01, 2004 10:49 am    Post subject: Medformix.FlatMerge.Printer API v1.1 Reply with quote

Copyright (c) 2004 Crowell Systems -- http://www.crowellsystems.com/. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, with the Front-Cover Texts being "Medformix.FlatMerge OpenOffice.org Macro Library", and no Back-Cover Texts. A copy of the license is available by writing to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA or it can be viewed at "http://www.gnu.org/licenses/fdl.html".

Medformix.FlatMerge.Printer API v1.1 Documentation

Overview

This macro takes an OpenOffice.org (OOo) document and a Comma Seperated Value (CSV) flat file data source and does a MailMerge with them to one or more (one per record in the data source if this option is specified) Postscript files. There are many options on how this is done, for more information on the options, see Invocation Syntax below and the OOo API documentation (available at the OOo web site http://api.openoffice.org/). Note: To MailMerge to a collection of OOo files, use the macro Medformix.FlatMerge.File.

Because this macro is intended to be used in an "enterprise" environment, running on a remote server with no operator present, it is designed to run -invisible and -headless. To that end, it uses the shell's stdout facility to pass status information back to the program that invoked it. By capturing the stdout of the soffice program while running this macro, it is possible to determine what, if any, errors or warnings there were; and whether or not the macro thought it ran successfully. The output files can be checked for as well, but the macro has extensive error checking built in.

Note: Earlier documentation (before this macro was started...) referred to Medformix.FlatMerge.Print; however, "print" is a reserved word in OpenOffice Basic, and therefore could not be used as a macro name.

Changes since API v1.0

A significant change from the first "official" version of the API (v1.0) is the addition of the {None} option for the printerName specification in the sPrinterSpec command line parameter. If {None} is specified for the printerName, then the merged output will be saved to an OpenOffice.org (.sxw) document instead of being sent to the printer. If this option is used, then the FileName specification must be given, and this will be the name of the file that will be written with the merged data. The purpose of this feature is to allow the output of a merge job to be edited, if needed (e.g. to build a template that needs personalized information entered by a person, or to automatically build reference manuals from data extracted from source code or another data source). Also, two new properties were added to sPrinterSpec: ResetPageNumbers and StartOnRight to allow the override of having page numbers reset for each merged record and to support double-sided printing respectively.

Invocation Syntax
Code:
Medformix.FlatMerge.Printer(<sTemplateURL>, <sDataSourceURL>[, <sPrinterSpec>])

where (N.B. path in any of the URLs can be absolute or relative):
    - sTemplateURL is the file:// URL to the template document (.sxw file). For the Medformix.FlatMerge.Printer macro, this file must exist. The format for the sTemplateURL parameter is:

      file://path/fileNamePrefix.sxw

    For example:
    Code:
    file:///mfx/12002/OOo/inst/docs/1_101.sxw
    file://~/2_Aardvark.sxw
    file://../.mfx/Elmer_Fudd.sxw
    file://./Wascawy_Wabbit.sxw
    - sDataSourceURL is the proprietary file:// URL to the data source file (usually a .csv file, but it can have any suffix). Note: csv stands for Comma Separated Value; however, any delimiter can be specified. For an excellent overview of csv files, format, and issues, see The Comma Separated Value (CSV) File Format (How To) by John Rapici. For the Medformix.FlatMerge.Printer macro, this file must exist. The format for the sDataSourceURL parameter is:

      file://path/fileNamePrefix[.fileNameSuffix][?name=value[&name=value]...]

    where fileNamePrefix.fileNameSuffix is the full name of the file; if no fileNameSuffix is specified, then the period (.) should not be included as this will be stripped anyway; if options are specified, the first must be preceeded by a question mark (?), and all subsequent options must be preceeded by an ampersand (&); and any number of options may be specified and will be passed to the appropriate object or set on the appropriate object. Recognized properties include (these apply to the com.sun.star.sdb.DataSource object for the most part):


    Notes: The names of the arguments are case insensitive, and will be recognized with any capitalization, so use what is clearest for your application. The Extension property of com.sun.star.sdbc.FLATConnectionProperties is automatically set to the value of fileNameSuffix. Also, when specifying delimiters or strings on the command line, the standard URL %XX construction may be used, where XX is a pair of hexadecimal digits that specify the character to use (e.g. %09 is the tab character, %20 is a space). This allows arbitrary characters to be specified without worrying about how they will be interpreted by the shell or some component between the shell and the macro. Where a character is known to cause no problems on a command line, and will not be confused with the reserved shell delimiter characters, it can be used directly (e.g. the colon character (:), etc.). The percent sign character (%) can be specified on its own if desired, and it will be interpreted as the percent sign character. Filter is an SQL statement that will be executed by the MailMerge object on the data source before doing a mail merge. If an explicit no delimiter condition is desired, the special value {None} can be specified. The default values for the arguments (for our application, as set in the macro) are:
    Code:
    Charset = "iso-8859-1"
    HeaderLine = True
    FieldDelimiter = Chr(9)  ' The tab character
    StringDelimiter = "
    DecimalDelimiter = .
    ThousandDelimiter = ,
    Filter = {None}

    For more information about these properties, see com.sun.star.sdbc.FLATConnectionProperties, com.sun.star.sdbc.FILEConnectionProperties, com.sun.star.sdb.DataAccessDescriptor, the StarOffice 6.0 Office Suite -- Basic Programmer's Guide, and the StarOffice Programmer's Tutorial.

    For example:
    Code:
    file:///home/mfx123/mergedata.csv
    file://~/bobsyouruncle.csv?DecimalDelimiter=,&ThousandDelimiter={None}
    file:///home/mfx123/odddata.csv?FieldDelimiter=%FE&StringDelimiter=:
    - sPrinterSpec is the optional name of the Ooo's "printer" to use for output of the mail merge, and the options to specify for it.The format for sPrinterSpec is:

      [printerName][?name=value[&name=value]...]

    Where printerName can be an OOo recognized real printer to send the print job to, just a printer driver (in the case of printing to a Postscript or other file, e.g. "Generic Printer"), or {None} to output the merged data to a single OpenOffice file as specified by the FileName parameter (see below). If printerName is not specified, the default printer for the document is used for output. There are a number of options that are allowed with the sPrinterSpec parameter, these are:


    Where CopyCount allows for more than one copy of each merged document to be output. If Collate is True, it advises the printer to collate the pages of the copies (i.e. a whole document is printed prior to the next copy, otherwise the page copies are completed together). Pages specifies the pages to print in the same format as in the print dialog of the GUI (e.g. "1, 3, 4-7, 9-"). If SinglePrintJobs is True, then one print job (or a printer output file if FileName is set), is created per merge record. And finally, FileName is a proper file:// URL. If set, the output of the merge goes to a printer output file (usually Postscript, unless the specified printerName driver generates a different format), or to a single OpenOffice .sxw file if printerName is set to {None}. This setting is modified by the SinglePrintJobs flag to create multiple files using FileName as a template to name the multiple files. Note: If printerName is set to {None}, FileName must be specified and CopyCount, Pages, and SinglePrintJobs cannot be specified (however, ResetPageNumbers and StartOnRight are permitted). If ResetPageNumbers is set to False, then the page numbering will not be restarted for each of the merged records. If StartOnRight is set to True, then each merged record will be started on a right-hand page, so it will always be on a new sheet of paper when doing double-sided printing.

    The format of FileName is:

      file://path/fileNamePrefix.fileNameSuffix

    where fileNameSuffix would usually be .ps, but can be any value. If printerName is specified as {None}, then fileNameSuffix would usually be .sxw, but can still be any value. The default values for the arguments (for our application, as set in the macro) are as follows:
    Code:
    Pages = ""
    Collate = True
    FileName = ""
    CopyCount = 1
    SinglePrintJobs = False
    ResetPageNumbers = True
    StartOnRight = False

    For more information about these properties, see (com.sun.star.text.MailMerge, com.sun.star.view.PrinterDescriptor, com.sun.star.view.PrintOptions, and the OpenOffice Developer's Guide).
Note: All leading and trailing whitespace is explicitly stripped off all of the parameters passed into all of these macros, as it is not done automatically by OpenOffice.org.

Example

An example command line invocation on the following flat file is as follows (this presumes the existence of a previously defined template .sxw document with appropriate merge fields... in this example, UserName, Password, UserID, GroupID, GECOS, HomeDir, and Shell merge fields):

Sample data source (a short slice from a Linux /etc/passwd file):
Code:
UserName:Password:UserID:GroupID:GECOS:HomeDir:Shell
root:x:0:0::/root:/bin/bash
bin:x:1:1:bin:/bin:
daemon:x:2:2:daemon:/sbin:
adm:x:3:4:adm:/var/log:

Sample command line (backslashes are added at line ends for readability, it should be one long line when executed):
Code:
soffice "macro:///Medformix.FlatMerge.Printer(file:///home/jbotte/Projects/OOo/Test.sxw,\
  file:///home/jbotte/Projects/OOo/Test.csv?FieldDelimiter=:,\
  Generic Printer?FileName=file:///home/jbotte/Test.ps&\
  CopyCount=5&StartOnRight=True)"

Important Note

The Medformix.FlatMerge.Printer macro code is more complex than would otherwise be required in order to deal with two critical defects in the OOo 1.1.0 and 1.1.1 implementations (and likely for all 1.1.x releases...):
    - Issue #25686: MailMerge.OutputType=PRINTER hangs OOo on Linux
    - Issue #26678: Need ability to set PrintOptions on MailMerge
Specifically, the merge to a single document is accomplished by using code similar to the Medformix.FlatMerge.File macro to generate a set of individual OpenOffice documents in a temporary directory (which is created as a subdirectory of the directory specified as part of the FileName path or in /tmp otherwise). A copy of the sTemplateURL document is then used in memory to "paste" each of the output files into a single document in memory (it uses the document settings of the sTemplateURL document so the formatting will be correct). Between each document, a page break is inserted so they start on separate pages. All the temporary files are deleted after the in-memory document is "printed" (whether it goes to a printer or OOo file or an actual printer, or to an .sxw file...). There may be a few problems left with merging documents this way, so please do not hesitate to let me know if your document breaks this macro!

Also note: There appears to be a bug inside the MailMerge.execute() method that can cause OOo to core dump on certain files when doing a "merge to files" operation. Please let me know if you run into this particular bug.


Last edited by jbotte on Mon Aug 02, 2004 12:48 pm; edited 3 times in total
Back to top
View user's profile Send private message
Peter O
Guest





PostPosted: Thu Jun 17, 2004 12:49 am    Post subject: Reply with quote

While checking out the code I started wondering, isn't Function Printer missing from the code ?

So I tried installing the Macro.
And indeed I only have SetDefaultDbProps, LogMessage, URLCharToString, CreateUniqueFolder and File.
But no Printer.

What did I do wrong ? Where is the code for the Function Printer() ?

Has it not been released ?
Back to top
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Fri Jul 09, 2004 7:17 am    Post subject: Reply with quote

While checking out the code I started wondering, isn't Function Printer missing from the code ?

Indeed... both it and the code for "Edit" are still missing... I ended up getting "revectored" at work and have just made it back to the OpenOffice FlatMerge project... I am posting the code now...
Back to top
View user's profile Send private message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Fri Jul 09, 2004 7:56 am    Post subject: Medformix.FlatMerge.Printer Macro Code Reply with quote

And now that you know how to use it, here's the code (remember that OOo Basic has a size limit and you will either need to strip the comments or put these in seperate modules):
Code:
' Function: Printer
' Description:
'   This function takes a document and a flat file data source and does a MailMerge
'   with them to a specified Printer, printer file (usually Postscript), or to an OOo
'   document (.sxw). To MailMerge to a set of OOo files, use the File() function.
'   There are many, many options on how this is done, for more information on the options,
'   see Arguments and the API docs. Important Note: This function is somewhat more complex
'   than it should have been due to a bug in MailMerge.OutputType=PRINTER as reported in
'   Issue #25686. Because of this, a MailMerge.OutputType=FILE is done to a temporary directory,
'   then the files are assembled into a single file in memory and then that file is printed or
'   saved. It is done this way also because there is a deficiency with MailMerge in that there
'   does not seem to be any way of setting printer related properties for a
'   MailMerge.OutputType=PRINTER and so that an OOo .sxw file can be target of the merge.
' Global Variables:
'   eMSG_*: These global constants are used to tag the importance of a message.
' Arguments:
'   sTemplateURL: the "file://" URL to the template document (.sxw file). This file must
'     exist. The format is: "file://<path>/<fileNamePrefix>.sxw".
'   sDataSourceURL: is the proprietary "file://" URL to the data source file (usually a .csv
'     file, but it can have any suffix -- "csv" stands for Comma Separated Value; however,
'     any delimiter can be specified). The format is (see the API docs for more information):
'     "file://<path>/<fileNamePrefix>.<fileNameSuffix>[?<name>=<value>[&<name>=<value>]...]".
'     Valid properties are "Charset", "HeaderLine", "FieldDelimiter", "StringDelimiter",
'     "DecimalDelimiter", "ThousandDelimiter", and "Filter".
'   sPrinterSpec [optional]: the optional name of the OpenOffice "printer" to use for output
'     of the mail merge, and the options to specify for it. The format for the sPrinterSpec
'     parameter is: "[printerName][?name=value[&name=value]...]" (Note: <name>=<value>
'     properties can be specified without specifying a printerName... in that case, the parameter
'     would just start with a question mark [?]). printerName can be an OpenOffice recognized
'     real printer, a printer driver (in the case of printing to a file -- usually "Generic
'     Printer") or "{None}" if the output is to be saved to an OOo (.sxw) file. If printerName
'     is not specified, the default printer is used. Valid properties are: "CopyCount",
'     "FileName", "Collate", "Pages", and "SinglePrintJobs"; however, if "{None}" is specified
'     for the printerName, "FileName" must be specified, and no other properties are allowed
'     except for "ResetPageNumbers" and "StartOnRight". If "ResetPageNumbers=True"
'     (which is the default), then the page number is reset to 1 at the beginning of each
'     merged document. If "StartOnRight=True" then a page will be inserted if necessary to
'     make sure each merged document starts on its own sheet of paper (for double siding).
' Return: Boolean
'   Returns True if successful, or False if it fails.
' Notes:
'   You really need to check out the Medformix.FlatMerge API documentation and probably the
'     OpenOffice IDL Reference for this all to make any sense.
' History:
'   16mar04: jbotte: Added header comment block.
'   18mar04: jbotte: Modified document merge code to use the source document as the template
'     to preserve the document's format (vs. using a factory built blank document), replace
'     the template's contents with the first document loaded, then make sure that a new
'     paragraph and a page break is put after each document except the last. Removed unused
'     setting of parameter for mInsertProps left over from a previous version.
'   14apr04: jbotte: Modified to accept "{None}" as a printerName to cause the common file
'     (the document containing the mail merge) in memory to be saved rather than printed.
'   12jul04: jbotte: Fixed a bug with the merge that did not reset the page numbers of the
'     individual merged documents (causing the resulting merged document to start at page 1
'     and run to page N where N is the number of pages, rather than having each merged
'     document start at page 1). Thanks to Andrew Pitonyak for the clue on how to do this.
'     Also added support for two more arguments: ResetPageNumbers and StartOnRight (see above).

Function Printer(sTemplateURL As String, sDataSourceURL As String, _
  Optional sPrinterSpec As String) As Boolean
   On Error Goto labelErrorHandler
   
   ' ++ Utility declarations used repeatedly in code
   Dim nI As Integer
   Dim bResult As Boolean
   Dim bIsPrinterSpec As Boolean
   Dim mSplitOut() As String
   Dim mParmArgs() As String  ' Used for storage while processing arguments
   Dim bDbIsRegistered As Boolean
   
   ' Used in case an unexpected exception is thrown
   Dim bIsDocCommonLoaded As Boolean
   Dim bIsUniqueDirCreated As Boolean
   Dim oDocCommon As Object
   Dim sUniqueDir As String
         
   ' Will cause the function to exit with the value False on Exit. Set to True if Exit okay...
   Printer() = False
      
   LogMessage(eMSG_DEBUG, "Running Medformix.FlatMerge.Printer macro...")
   
   ' Until it is registered, this should be set false. It is used for cleanup...
   bDbIsRegistered = False
   
   ' Until the files are loaded, these should be set false...
   bIsDocCommonLoaded = False
   bIsUniqueDirCreated  = False

   Dim oFileAccess As Variant
   oFileAccess = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")

   Dim oURLTransformer As Variant
   oURLTransformer = CreateUnoService("com.sun.star.util.URLTransformer")

   ' ++ Parse and validate the URLs from the command line

   ' + Start with the document URL which is a simple file:// type specification
   Dim aDocURL As New com.sun.star.util.URL
   aDocURL.Complete = Trim(sTemplateURL)

   bResult = oURLTransformer.parseSmart(aDocURL, "")
   If bResult = False Then
      LogMessage(eMSG_ERR, "Failed to parse document URL!")
      Goto labelExitClean
   End If
   
   If Not oFileAccess.exists(aDocURL.Main) Then
      LogMessage(eMSG_ERR, "Document URL does not exist: " + aDocURL.Main)
      Goto labelExitClean
   End If
   
   If oFileAccess.isFolder(aDocURL.Main) Then
      LogMessage(eMSG_ERR, "Document URL cannot be a folder/directory: " + aDocURL.Main)
      Goto labelExitClean
   End If
   ' + We have a valid document URL that exists as a file on the system
   
   ' + Now we tackle the data source URL, which is an unstandard specification
   ' + We need to parse the passed URL into a valid file:// URL and the
   ' + unstandard suffix, if any
   mSplitOut() = Split(sDataSourceURL, "?")
   
   ' Only allow zero or one ?s in the URL
   If UBound(mSplitOut) > 1 Then
      LogMessage(eMSG_ERR, "Malformed data source URL... too many ?s: " + sDataSourceURL)
      Goto labelExitClean
   End If

   Dim aDbURL  As New com.sun.star.util.URL
   aDbURL.Complete = Trim(mSplitOut(0))

   bResult = oURLTransformer.parseSmart(aDbURL, "")
   If bResult = False Then
      LogMessage(eMSG_ERR, "Failed to parse data source URL!")
      Goto labelExitClean
   End If
   
   ' Store the arguments, if any, in the URL.Arguments element of the structure
   ' Don't forget we've already taken care of the > 1 case above...
   If UBound(mSplitOut) = 1 Then
      aDbURL.Arguments = Trim(mSplitOut(1))
      If aDbURL.Arguments = "" Then
         LogMessage(eMSG_WARN, _
           "A data source argument was indicated with a ?, but not specified...")
      End If
   End If
   
   If Not oFileAccess.exists(aDbURL.Main) Then
      LogMessage(eMSG_ERR, "Data source URL does not exist: " + aDbURL.Main)
      Goto labelExitClean
   End If
   
   If oFileAccess.isFolder(aDbURL.Main) Then
      LogMessage(eMSG_ERR, "Data source URL cannot be a folder/directory: " + aDbURL.Main)
      Goto labelExitClean
   End If   
   ' + We have a valid data source URL that exists as a file on the system

   ' + Now it's time to process and set any arguments to the DataSource and MailMerge objects
   Dim sDbName As String
   Dim oDbContext As Object
   Dim oDbInstance As Object
   Dim oMailMerge As Variant
   
   ' This array of PropertyValue's will be passed to the Data Source object
   Dim mDbProperties(eHIGHEST_DBPROP) As New com.sun.star.beans.PropertyValue
   SetDefaultDbProps(mDbProperties())  ' Get it set up with the default values

   ' Extract the fileNameSuffix and fileNameSuffix for the data source
   mSplitOut() = Split(aDbURL.Name, ".")
   sDbName = mSplitOut(0)
   If UBound(mSplitOut) = 0 Then
      mDbProperties(eDBPROP_EXTENSION).Value = ""
   Else
      mDbProperties(eDBPROP_EXTENSION).Value = mSplitOut(UBound(mSplitOut))
   End If
   
   ' Instantiate data source and mail merge objects and set what we know...
   oDbContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")

   If oDbContext.hasByName(sDbName) Then
      LogMessage(eMSG_NOTICE, "Data source name """ + sDbName + _
         """ existed... revoking from registry")
      oDbContext.revokeObject(sDbName)
   End If
      
   oDbInstance = oDbContext.createInstance()
   oDbContext.registerObject(sDbName, oDbInstance)
   bDbIsRegistered = True
   
   oDbInstance.URL = "sdbc:flat:" + aDbURL.Protocol + aDbURL.Path
   
   oMailMerge = CreateUnoService("com.sun.star.text.MailMerge")
   oMailMerge.DataSourceName = sDbName
   LogMessage(eMSG_DEBUG, "aDocURL.Main: " + aDocURL.Main)
   oMailMerge.DocumentURL = aDocURL.Main
   oMailMerge.CommandType = com.sun.star.sdb.CommandType.TABLE
   oMailMerge.Command = sDbName
   
   ' And these are the hacks needed to get around the buggy OutputType=PRINTER
   ' Still need to set oMailMerge.OutputURL, but this is done after parsing sPrinterSpec
   oMailMerge.OutputType = com.sun.star.text.MailMergeType.FILE
   oMailMerge.FileNameFromColumn = False

   ' Set if the FileName parameter is specified
   Dim bIsFileName As Boolean
   bIsFileName = False
      
   ' Now we parse the arguments to sDataSourceURL, if any, and set oDbInstance.Info with them
   If aDbURL.Arguments <> "" Then
      ' Break apart multiple arguments (name=value) into an array of strings
      mParmArgs() = Split(aDbURL.Arguments, "&")
      
      For nI = 0 To UBound(mParmArgs)
         mSplitOut() = Split(mParmArgs(nI), "=")
         mSplitOut(0) = Trim(mSplitOut(0))
         If UBound(mSplitOut) = 0 Or UBound(mSplitOut) > 1 Then
            LogMessage(eMSG_ERR, "Malformed argument to sDataSourceURL: " + mParmArgs(nI))
            Goto labelExitClean
         End If
         mSplitOut(1) = Trim(mSplitOut(1))
         If mSplitOut(0) = "" Or mSplitOut(1) = "" Then
            LogMessage(eMSG_ERR, _
               "Proper sDataSourceURL argument format is name=value: " + mParmArgs(nI))
            Goto labelExitClean
         End If
         
         ' Yup, every program has to have "special cases"...
         Select Case LCase(mSplitOut(0))
            Case "charset":
               ' Just have to assume the format is correct...
               mDbProperties(eDBPROP_CHARSET).Value = mSplitOut(1)
            Case "decimaldelimiter":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDbProperties(eDBPROP_DECIMALDELIMITER).Value = ""
               Else
                  mDbProperties(eDBPROP_DECIMALDELIMITER).Value = _
                     URLCharToString(mSplitOut(1))
                  If Len(mDbProperties(eDBPROP_DECIMALDELIMITER).Value) <> 1 Then
                     LogMessage(eMSG_ERR, _
                        "Invalid value specified for DecimalDelimiter argument: " + _
                        mSplitOut(1))
                     Goto labelExitClean
                  End If
               End If
            Case "fielddelimiter":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDbProperties(eDBPROP_FIELDDELIMITER).Value = ""
               Else
                  mDbProperties(eDBPROP_FIELDDELIMITER).Value = _
                     URLCharToString(mSplitOut(1))
                  If Len(mDbProperties(eDBPROP_FIELDDELIMITER).Value) <> 1 Then
                     LogMessage(eMSG_ERR, _
                        "Invalid value specified for FieldDelimiter argument: " + _
                        mSplitOut(1))
                     Goto labelExitClean
                  End If
               End If
            Case "filter":
               ' Just pass it in untouched... nothing else we can do...
               oMailMerge.Filter = mSplitOut(1)
            Case "headerline":
               Select Case LCase(mSplitOut(1))
                  Case "true":
                     mDbProperties(eDBPROP_HEADERLINE).Value = "True"
                  Case "false":
                     mDbProperties(eDBPROP_HEADERLINE).Value = "False"
                  Case Else:
                     LogMessage(eMSG_ERR, "Invalid boolean specifier (True/False) " + _
                        "for HeaderLine argument: " + mSplitOut(1))
                     Goto labelExitClean
               End Select
            Case "stringdelimiter":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDbProperties(eDBPROP_STRINGDELIMITER).Value = ""
               Else
                  mDbProperties(eDBPROP_STRINGDELIMITER).Value = _
                     URLCharToString(mSplitOut(1))
                  If Len(mDbProperties(eDBPROP_STRINGDELIMITER).Value) <> 1 Then
                     LogMessage(eMSG_ERR, _
                        "Invalid value specified for StringDelimiter argument: " + _
                        mSplitOut(1))
                     Goto labelExitClean
                  End If
               End If
            Case "thousanddelimiter":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDbProperties(eDBPROP_THOUSANDDELIMITER).Value = ""
               Else
                  mDbProperties(eDBPROP_THOUSANDDELIMITER).Value = _
                     URLCharToString(mSplitOut(1))
                  If Len(mDbProperties(eDBPROP_THOUSANDDELIMITER).Value) <> 1 Then
                     LogMessage(eMSG_ERR, _
                        "Invalid value specified for ThousandDelimiter argument: " + _
                        mSplitOut(1))
                     Goto labelExitClean
                  End If
               End If
            Case Else:
               LogMessage(eMSG_ERR, _
                  "Invalid argument specified with sDataSourceURL: " + mSplitOut(0))
               Goto labelExitClean
         End Select
      Next nI
   End If
   
   ' + If specified, trim the whitespace off sPrinterSpec, and set the flag
   If Not IsMissing(sPrinterSpec) Then
      sPrinterSpec = Trim(sPrinterSpec)
      If sPrinterSpec = "" Then
         bIsPrinterSpec = False
         Goto labelSkipPrinterSpec
      Else
         bIsPrinterSpec = True
      End If
   Else
      bIsPrinterSpec = False
      Goto labelSkipPrinterSpec
   End If
   
   ' + Now we need to process the printer name and arguments, if any
   mSplitOut() = Split(sPrinterSpec, "?")
   
   ' Only allow zero or one ?s in the URL
   If UBound(mSplitOut) > 1 Then
      LogMessage(eMSG_ERR, "Malformed printer spec argument... too many ?s: " + sPrinterSpec)
      Goto labelExitClean
   End If

   Dim sPrinterName As String, sPrinterArgs As String
   sPrinterName = Trim(mSplitOut(0))

   ' Don't forget we've already taken care of the > 1 case above...
   If UBound(mSplitOut) = 1 Then
      sPrinterArgs = Trim(mSplitOut(1))
      If sPrinterArgs = "" Then
         LogMessage(eMSG_WARN, _
           "A printer spec argument was indicated with a ?, but not specified...")
      End If
   Else
      sPrinterArgs = ""
   End If
   
   Const ePRTOPT_COPYCOUNT As Integer = 0
   Const ePRTOPT_WAIT      As Integer = 1
   Const ePRTOPT_COLLATE   As Integer = 2
   Const ePRTOPT_PAGES     As Integer = 3
   Const ePRTOPT_FILENAME  As Integer = 4
   Const eHIGHEST_PRTOPT   As Integer = 4
   
   Dim bStartOnRight As Boolean
   Dim bResetPageNumbers As Boolean
   Dim mPrintOptions(eHIGHEST_PRTOPT) As New com.sun.star.beans.PropertyValue

   ' Set default values   
   mPrintOptions(ePRTOPT_COPYCOUNT).Name  = "CopyCount"
   mPrintOptions(ePRTOPT_COPYCOUNT).Value = 1
   mPrintOptions(ePRTOPT_FILENAME).Name  = "FileName"
   mPrintOptions(ePRTOPT_FILENAME).Value = ""
   mPrintOptions(ePRTOPT_COLLATE).Name  = "Collate"
   mPrintOptions(ePRTOPT_COLLATE).Value = True
   mPrintOptions(ePRTOPT_PAGES).Name  = "Pages"
   mPrintOptions(ePRTOPT_PAGES).Value = "1-"
   mPrintOptions(ePRTOPT_WAIT).Name  = "Wait"
   mPrintOptions(ePRTOPT_WAIT).Value = True
   
   bStartOnRright = False
   bResetPageNumbers = True

   ' Now we parse the arguments to sPrinterSpec, if any...
   If sPrinterArgs <> "" Then
      ' Break apart multiple arguments (name=value) into an array of strings
      mParmArgs() = Split(sPrinterArgs, "&")
      
      For nI = 0 To UBound(mParmArgs)
         mSplitOut() = Split(mParmArgs(nI), "=")
         mSplitOut(0) = Trim(mSplitOut(0))
         If UBound(mSplitOut) = 0 Or UBound(mSplitOut) > 1 Then
            LogMessage(eMSG_ERR, "Malformed argument to sPrinterSpec: " + mParmArgs(nI))
            Goto labelExitClean
         End If
         mSplitOut(1) = Trim(mSplitOut(1))
         If mSplitOut(0) = "" Or mSplitOut(1) = "" Then
            LogMessage(eMSG_ERR, "Proper sPrinterSpec argument format is name=value: " + _
               mParmArgs(nI))
            Goto labelExitClean
         End If
         
         ' Yup, every program has to have "special cases"...
         Select Case LCase(mSplitOut(0))
            Case "collate":
               If LCase(sPrinterName) = "{none}" Then
                  LogMessage(eMSG_ERR, "Cannot specify ""Collate"" property with printerName={None}")
                  Goto labelExitClean
               End If
               
               Select Case LCase(mSplitOut(1))
                  Case "true":
                      mPrintOptions(ePRTOPT_COLLATE).Value = "True"
                  Case "false":
                      mPrintOptions(ePRTOPT_COLLATE).Value = "False"
                  Case Else:
                     LogMessage(eMSG_ERR, "Invalid boolean specifier (True/False) " + _
                        "for Collate argument: " + mSplitOut(1))
                     Goto labelExitClean
               End Select
            Case "copycount":
               If LCase(sPrinterName) = "{none}" Then
                  LogMessage(eMSG_ERR, "Cannot specify ""CopyCount"" property with printerName={None}")
                  Goto labelExitClean
               End If
               
               mPrintOptions(ePRTOPT_COPYCOUNT).Value = mSplitOut(1)
            Case "filename":
               bIsFileName = True
               Dim aOutURL As New com.sun.star.util.URL
               aOutURL.Complete = mSplitOut(1)

               bResult = oURLTransformer.parseSmart(aOutURL, "")
               If bResult = False Then
                  LogMessage(eMSG_ERR, "Failed to parse Output FileName parameter URL!")
                  Goto labelExitClean
               End If
   
               If oFileAccess.isFolder(aOutURL.Main) Then
                  LogMessage(eMSG_ERR, _
                     "Output FileName URL cannot be a folder/directory: " + aOutURL.Main)
                  Goto labelExitClean
               End If
               
               If Not oFileAccess.exists(aOutURL.Protocol + aOutURL.Path) Then
                  LogMessage(eMSG_ERR, _
                     "Output FileName target directory does not exist: " + _
                     aOutURL.Protocol + aOutURL.Path)
                  Goto labelExitClean
               End If
               ' Use the parsed URL because parseSmart does some tweaking on it if needed
               mPrintOptions(ePRTOPT_FILENAME).Value = aOutURL.Main
            Case "pages":
               If LCase(sPrinterName) = "{none}" Then
                  LogMessage(eMSG_ERR, "Cannot specify ""Pages"" property with printerName={None}")
                  Goto labelExitClean
               End If
               
               mPrintOptions(ePRTOPT_PAGES).Value = mSplitOut(1)
            Case "singleprintjobs":
               If LCase(sPrinterName) = "{none}" Then
                  LogMessage(eMSG_ERR, "Cannot specify ""SinglePrintJobs"" property with printerName={None}")
                  Goto labelExitClean
               End If
               
               Select Case LCase(mSplitOut(1))
                  Case "true":
                     oMailMerge.SinglePrintJobs = True
                  Case "false":
                     oMailMerge.SinglePrintJobs = False
                  Case Else:
                     LogMessage(eMSG_ERR, "Invalid boolean specifier (True/False) " + _
                        "for SinglePrintJobs argument: " + mSplitOut(1))
                     Goto labelExitClean
               End Select
            Case "resetpagenumbers":
               Select Case LCase(mSplitOut(1))
                  Case "true":
                     bResetPageNumbers = True
                  Case "false":
                     bResetPageNumbers = False
                  Case Else:
                     LogMessage(eMSG_ERR, "Invalid boolean specifier (True/False) " + _
                        "for ResetPageNumbers argument: " + mSplitOut(1))
                     Goto labelExitClean
               End Select
            Case "startonright":
               Select Case LCase(mSplitOut(1))
                  Case "true":
                     bStartOnRight = True
                  Case "false":
                     bStartOnRight = False
                  Case Else:
                     LogMessage(eMSG_ERR, "Invalid boolean specifier (True/False) " + _
                        "for StartOnRight argument: " + mSplitOut(1))
                     Goto labelExitClean
               End Select
            Case Else:
               LogMessage(eMSG_ERR, "Invalid argument specified with sPrinterSpec: " + _
                  mSplitOut(0))
               Goto labelExitClean
         End Select
      Next nI
   End If
   
   If LCase(sPrinterName) = "{none}" And bIsFileName <> True Then
      LogMessage(eMSG_ERR, "You must specify the ""FileName"" property when printerName={None}")
      Goto labelExitClean
   End If
   
labelSkipPrinterSpec:

   ' ++ Everything is ready, all that's left is the final initialization and execution
   Dim oDbConnection As Object
   
   oDbInstance.setPropertyValue("Info", mDbProperties())
   
   oDbConnection = oDbInstance.getConnection("", "")
   
   oMailMerge.ActiveConnection = oDbConnection
   
   If bIsFileName Then
      sUniqueDir = CreateUniqueFolder(aOutURL.Protocol + aOutURL.Path)
   Else
      sUniqueDir = CreateUniqueFolder("file:///tmp")
   End If
   
   bIsUniqueDirCreated = True
   
   oMailMerge.OutputURL = sUniqueDir
   
   Dim mEmptyNamedValue() As New com.sun.star.beans.NamedValue  ' Empty placeholder for invocation
   
   ' This will give us a uniquely named directory full of only the merge files
   oMailMerge.execute(mEmptyNamedValue())
   
   ' ++ Now to the fun part... we need to merge all the files into one so we can print...
   
   ' + Create the common file to gather the merged content
   Dim mEmptyPropertyValue() As New com.sun.star.beans.PropertyValue
   Dim mDocProperties(1) As New com.sun.star.beans.PropertyValue
   mDocProperties(0).Name  = "Hidden"
   mDocProperties(0).Value = True
   mDocProperties(1).Name  = "AsTemplate"
   mDocProperties(1).Value = True
   
   ' Create the common document, but do not display a frame
   oDocCommon = StarDesktop.LoadComponentFromURL(aDocURL.Main, _
      "_blank", 0, mDocProperties())
   bIsDocCommonLoaded = True
   
   ' + Add the contents of each of the merged file into the common file
   Dim sDirListing() As String
   ' Note that GetFolderContents returns full URLs
   sDirListing = oFileAccess.GetFolderContents(sUniqueDir, False)  ' False = No Subdirs
   
   ' Copy the contents of each merge output file into the common file
   Dim oText As Object
   Dim oTextCursor As Object
   Dim oViewCursor As Object
   Dim nPageCount As Integer
   
   oText = oDocCommon.getText()
   oTextCursor = oText.createTextCursor()
   oViewCursor = oDocCommon.CurrentController.getViewCursor()
   
   For nI = 0 To UBound(sDirListing)
      LogMessage(eMSG_DEBUG, "Merging document: " + sDirListing(nI))
      ' Insert the contents of each document into the common document
      If (nI = 0) Then
         ' This is the first document, replace contents of template
         oTextCursor.gotoStart(False)  ' Do not expand the cursor
         oTextCursor.gotoEnd(True)     ' Okay, now expand the cursor
      Else
         oTextCursor.gotoEnd(False)  ' False means do not expand the cursor
      End If
      
      ' Now, insert the document into the common document at the cursor
      oTextCursor.insertDocumentFromURL(sDirListing(nI), mEmptyPropertyValue())
      
      ' This is needed to support double siding so each starts on a new piece of paper
      If bStartOnRight = True Then
         nPageCount = oViewCursor.getPage()

         ' Make sure not to add a page after the last one (it is just bad form...)
         If nPageCount MOD 2 = 1 And nI <> UBound(sDirListing) Then
            oTextCursor.gotoEnd(False)  ' False means do not expand the cursor
            oText.insertControlCharacter(oTextCursor, _
               com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, False)
            oTextCursor.gotoEnd(False)  ' False means do not expand the cursor
         
            oTextCursor.breakType = com.sun.star.style.BreakType.PAGE_BEFORE
         End If
      End If
      
      ' Don't put a page break after the last document
      If nI <> UBound(sDirListing) Then
         oTextCursor.gotoEnd(False)  ' False means do not expand the cursor
         oText.insertControlCharacter(oTextCursor, _
            com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, False)
      
         oTextCursor.gotoEnd(False)  ' False means do not expand the cursor
         
         If bResetPageNumbers = True Then
            ' The following four lines reset the page number to 1 for the first page of each
            '   merged document. Thanks to Andrew Pitonyak for the clue on how to do this.
            oTextCursor.gotoStartOfParagraph(False)
            oTextCursor.gotoEndOfParagraph(True)
            oTextCursor.PageDescName = oTextCursor.PageStyleName
            oTextCursor.PageNumberOffset = 1
         End If
      End If
   Next nI
   
   ' Clean up the directory now. Uses the very scary kill method that even deletes directories
   oFileAccess.Kill(sUniqueDir)
   bIsUniqueDirCreated = False
   
   ' If we are saving to a file, then do it now, and skip the printer initialization
   If LCase(sPrinterName) = "{none}" Then
      Dim oDocArgs() As Variant

      oDocArgs = oDocCommon.getArgs()

      ' Store the common document as specified by the FileName property
      LogMessage(eMSG_DEBUG, "Saving merged document as: " + aOutURL.Main)
      oDocCommon.StoreAsURL(aOutURL.Main, oDocArgs)
      
      oDocCommon.close(True)  ' True means that we don't have to set an event handler
      bIsDocCommonLoaded = False
      
      Printer() = True
      Goto labelExitClean
   End If
   
   If sPrinterName <> "" Then
      ' This is really a com.sun.star.view.PrinterProperties service implementation
      Dim mPrinterDesc(0) As New com.sun.star.beans.PropertyValue
      
      mPrinterDesc(0).Name  = "Name"
      mPrinterDesc(0).Value = sPrinterName
      oDocCommon.setPrinter(mPrinterDesc())
   End If

   ' And it all happens here...
   
   ' Okay, but not yet... this hack is needed until I figure out
   '   dynamic sizing of UNO object arrays...
   If mPrintOptions(ePRTOPT_FILENAME).Value = "" Then
      Dim nJ As Integer
      Dim mNoFileName(UBound(mPrintOptions()) - 1) As New com.sun.star.beans.PropertyValue
      
      ' This is needed to pass a correctly sized array with no FileName property otherwise
      ' an exception is thrown if FileName is equal to an empty string ("")
      nJ = 0
      For nI = 0 To UBound(mPrintOptions())
         If mPrintOptions(nI).Name <> "FileName" Then
            mNoFileName(nJ) = mPrintOptions(nI)
            nJ = nJ + 1
         End If
      Next nI
      
      oDocCommon.print(mNoFileName())
   Else
      ' A FileName has been specified, so it's going to a file...
      oDocCommon.print(mPrintOptions())
   End If
   
   oDocCommon.close(True)  ' True means that we don't have to set an event handler
   bIsDocCommonLoaded = False
   
   ' Return with a True value, indicating success... then clean up
   Printer() = True
   
labelExitClean:
   ' Make sure the registered data source is cleaned up on exit
   If bDbIsRegistered Then
      oDbContext.revokeObject(sDbName)
      LogMessage(eMSG_DEBUG, "Data source " + sDbName + " successfully revoked")
   End If
   
   Exit Function
   
labelErrorHandler:
   LogMessage(eMSG_CRIT, "Medformix.FlatMerge.Printer script [line: " + Erl + _
      "] error [" + Err + "]: " + Error$

   ' Otherwise, if a copy of soffice is already loaded,
   '   these documents will be left in its context
   If bIsDocCommonLoaded Then
      oDocCommon.close(True)
   End If
   
   ' Clean up the temporary directory and all its files
   If bIsUniqueDirCreated Then
      oFileAccess.kill(sUniqueDir)
   End If
   
   Printer() = False
   Goto labelExitClean
End Function


Notes: I have fixed the issue where page numbering was not restarted for each of the merged documents, and I added support for double siding that makes sure that each merged document starts on a new sheet of paper.

However, there are still two known issues with this code: total page count is not reset, and large merges are unfathomably slow. I continue to work on the first issue (I have a few ideas, but I don't think there's a proper answer for it... until then, either don't use the total page count in documents or be aware it will not reset for each of the merged documents), and if anyone has any ideas on how to improve performance... I'm all ears.


Last edited by jbotte on Wed Jul 14, 2004 7:46 am; edited 1 time in total
Back to top
View user's profile Send private message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Fri Jul 09, 2004 8:53 am    Post subject: Medformix.FlatMerge.Edit API v1.0 Reply with quote

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with the Invariant Sections being "GNU Free Documentation License", with the Front-Cover Texts being "Medformix.FlatMerge OpenOffice.org Macro Library", and no Back-Cover Texts. A copy of the license is available by writing to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA or it can be viewed at "http://www.gnu.org/licenses/fdl.html".

Medformix.FlatMerge.Edit API v1.0

Overview

Unlike the Medformix.FlatMerge.File() and Medformix.FlatMerge.Printer() macros, the Medformix.FlatMerge.Edit() macro must be run with a user present to edit the template document that will be used for the MailMerge. However, unlike just letting the user edit the document free-form and be responsible for management of the data source used for the document, this macro manages the attachment and revocation of the data source for the document, allowing entirely arbitrary data sources to be used for each document. For this macro to be fully utilized it is generally invoked from a database application that generates the data source on the fly; however, it can be used from the command line and does offer the advantage easier management of data sources.

Note: As part of the opening process, a "listener" is attached to the document that, when the user is done editing the document (and closes it), causes a subroutine to be invoked that cleans up the data source that was created and registered for the session. This subroutine is not available directly through the Medformix.FlatMerge API, so it is not documented here; however, it may be important to know how the data source is revoked in some instances.

Invocation Syntax
Code:
Medformix.FlatMerge.Edit(<sBaseDocURL>, <sDataSourceURL>)

where (N.B. path in any of the URLs can be absolute or relative):
    - sBaseDocURL is the file:// URL to the starting document. For the Medformix.FlatMerge.Edit() macro, this file must either exist, or the private:factory/swriter invocation must be used to create a new file "from scratch" with no template. Any standard invocation will work, and all of the options for com.sun.star.frame.loadComponentFromURL() are supported. The format for the sBaseDocURL parameter is one of the following:

      file://path/fileNamePrefix[.fileNameSuffix][?name=value[&name=value]...]
      private:factory/swriter[?name=value[&name=value]...]

    where fileNamePrevix.fileNameSuffix is the full name of the file; if no fileNameSuffix is specified, then the period (.) should generally not be included but this is not checked for; if options are specified, the first must be preceeded by a question mark (?), and all subsequent options must be preceeded by an ampersand (&amp;); and any number of options may be specified and will be passed to the appropriate object or set on the appropriate object.

    Recognized properties include (these are passed to the com.sun.star.document.MediaDescriptor service through the loadComponentFromURL() method of its com.sun.star.frame.XComponentLoader interface):


    Notes: The names of the arguments are case insensitive, and will be recognized with any capitalization, so use what is clearest for your application. If an explicit no argument condition is desired, the special value {None} can be specified. The default values for the arguments (for our application, as set in the macro) are as follows:
    Code:
    AsTemplate = False
    CharacterSet = "iso-8859-1"
    FilterName = {None}
    FilterOptions = {None}
    JumpMark = {None}
    Password = {None}
    ReadOnly = False

    For more information about these properties, see (com.sun.star.document.MediaDescriptor, the StarOffice 6.0 Office Suite -- Basic Programmer's Guide, and the OpenOffice Developer's Guide).

    For example:
    Code:
    file:///mfx/12002/OOo/inst/docs/1_101.sxw?AsTemplate=False
    file://~/2_Aardvark.sxw?CharacterSet="iso-8859-2"
    file://./Wascawy_Wabbit.sxw&amp;FilterName=scalc:Text - txt - csv (StarOffice Calc)
    private:factory/swriter

    - sDataSourceURL is the proprietary file:// URL to the data source file (usually a .csv file, but it can have any suffix). Note: csv stands for Comma Separated Value; however, any delimiter can be specified. For an excellent overview of csv files, format, and issues, see The Comma Separated Value (CSV) File Format (How To) by John Rapici. For the Medformix.FlatMerge.Printer macro, this file must exist. The format for the sDataSourceURL parameter is:

      file://path/fileNamePrefix[.fileNameSuffix][?name=value[&name=value]...]

    where fileNamePrefix.fileNameSuffix is the full name of the file; if no fileNameSuffix is specified, then the period (.) should not be included as this will be stripped anyway; if options are specified, the first must be preceeded by a question mark (?), and all subsequent options must be preceeded by an ampersand (&); and any number of options may be specified and will be passed to the appropriate object or set on the appropriate object. Recognized properties include (these apply to the com.sun.star.sdb.DataSource object for the most part):


    Notes: The names of the arguments are case insensitive, and will be recognized with any capitalization, so use what is clearest for your application. The Extension property of com.sun.star.sdbc.FLATConnectionProperties is automatically set to the value of fileNameSuffix. Also, when specifying delimiters or strings on the command line, the standard URL %XX construction may be used, where XX is a pair of hexadecimal digits that specify the character to use (e.g. %09 is the tab character, %20 is a space). This allows arbitrary characters to be specified without worrying about how they will be interpreted by the shell or some component between the shell and the macro. Where a character is known to cause no problems on a command line, and will not be confused with the reserved shell delimiter characters, it can be used directly (e.g. the colon character (:), etc.). The percent sign character (%) can be specified on its own if desired, and it will be interpreted as the percent sign character. Filter is an SQL statement that will be executed by the MailMerge object on the data source before doing a mail merge. If an explicit no delimiter condition is desired, the special value {None} can be specified. The default values for the arguments (for our application, as set in the macro) are:
    Code:
    Charset = "iso-8859-1"
    HeaderLine = True
    FieldDelimiter = Chr(9)  ' The tab character
    StringDelimiter = "
    DecimalDelimiter = .
    ThousandDelimiter = ,
    Filter = {None}

    For more information about these properties, see com.sun.star.sdbc.FLATConnectionProperties, com.sun.star.sdbc.FILEConnectionProperties, com.sun.star.sdb.DataAccessDescriptor, the StarOffice 6.0 Office Suite -- Basic Programmer's Guide, and the StarOffice Programmer's Tutorial.

    For example:
    Code:
    file:///home/mfx123/mergedata.csv
    file://~/bobsyouruncle.csv?DecimalDelimiter=,&ThousandDelimiter={None}
    file:///home/mfx123/odddata.csv?FieldDelimiter=%FE&StringDelimiter=:

Example

An example command line invocation on the following flat file is as follows:

Sample data source (a short slice from a Linux /etc/passwd file):
Code:
UserName:Password:UserID:GroupID:GECOS:HomeDir:Shell
root:x:0:0::/root:/bin/bash
bin:x:1:1:bin:/bin:
daemon:x:2:2:daemon:/sbin:
adm:x:3:4:adm:/var/log:

Note that there doesn't really need to be any data in the fields for Medformix.FlatMerge.Edit() to work properly, the CSV file could just as easily have been:
Code:
UserName:Password:UserID:GroupID:GECOS:HomeDir:Shell
x:x:x:x:x:x:x

The header line is required regardless, so the fields will have labels.

Sample command line (backslashes are added at line ends for readability, it should be one long line when executed):
Code:
soffice "macro:///Medformix.FlatMerge.Edit(\
file:///home/jbotte/Projects/OOo/Test.sxw?AsTemplate=False&Password=XYZZY,\
file:///home/jbotte/Projects/OOo/Test.csv?FieldDelimiter=:)"

Once the document is open, the user can then insert merge fields that are available in the data source (in this example, UserName, Password, UserID, GroupID, GECOS,HomeDir, and Shell). When they are done creating or editing the template document for the merge, they need to save the document and exit (or exit and save the document on the way out). Once a template document is complete, then the macros Medformix.FlatMerge.File() and Medformix.FlatMerge.Printer() can be invoked using the template as the source document (sTemplateURL) along with a real data source to supply the merge field data.

Important Note

The Medformix.FlatMerge.Edit() macro contains a coded workaround to a critical OOo defect in the OOo 1.1.0 and 1.1.1 implementations (and likely for all 1.1.x releases...):
    - Issue #16297: Save with password automatically set without user notification
Essentially, when saving a document (using the regular save method), the password property is turned on in the saved document, but the password itself is empty (this can also be seen as a property set on the document by doing a File->Save As... and noting that the "Save with password" check box has been activated).

To work around this issue, as part of the opening process, a second "listener" is attached to the document that, when the user saves the document, causes a subroutine to be invoked with the workaround. If the document has a zero length password set, it is re-saved without a password property set at all. If a document had this problem, a user would notice that the status bar at the bottom of the screen "flashes" by twice. If the document does not have the problem, then the fix is not executed and the user will see nothing.

Without this fix, Medformix.FlatMerge.File() and Medformix.FlatMerge.Printer() would "throw" exceptions because of their purportedly needing a password to open the file, and this causes another bug to manifest that makes OOo hang if an exception is "thrown" from within certain objects to OOo Basic. Hopefully these will all be fixed in OOo 2.0...


Last edited by jbotte on Mon Aug 02, 2004 12:05 pm; edited 1 time in total
Back to top
View user's profile Send private message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Fri Jul 09, 2004 9:02 am    Post subject: Medformix.FlatMerge.Edit Macro Code Reply with quote

And now that you know how to use it, here's the code (remember that OOo Basic has a size limit and you will either need to strip the comments or put these in seperate modules):
Code:
'**************************************************************************
'
'   Medformix.FlatMerge OpenOffice macro library
'
'**************************************************************************
'
' Copyright (c) 2004 Crowell Systems
' http://www.crowellsystems.com/
'
' This file is part of the Medformix.FlatMerge OpenOffice macro library.
'
' The Medformix.FlatMerge OpenOffice macro library is free software; you
' can redistribute it and/or modify it under the terms of the GNU General
' Public License as published by the Free Software Foundation; either
' version 2 of the License, or (at your option) any later version.
'
' The Medformix.FlatMerge OpenOffice macro library is distributed in the
' hope that it will be useful, but WITHOUT ANY WARRANTY; without even the
' implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
' See the GNU General Public License for more details.
'
' You should have received a copy of the GNU General Public License along
' with the Medformix.FlatMerge OpenOffice macro library; if not, write to
' the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
' MA 02111-1307 USA or view it at "http://www.gnu.org/licenses/gpl.html".

' Function: Edit
' Description:
'   This function associates a dynamic data source with a document, allows the user to edit it,
'   and then revokes the data source when the user exits. This prevents "data source clutter"
'   when preparing MailMerge templates and reduces the chances of the user doing something
'   wrong when working with data sources. This function also uses the listener
'   DocListener_notifyEvent to do a workaround for Issue #16297 where a "phantom password"
'   would be silently added to a document when saved that would break the automated MailMerge
'   functions File() and Printer() [it would throw an exception because the password was not
'   specified when the document was loaded, and the MailMerge would fail].
' Global Variables:
'   eMSG_*: These global constants are used to tag the importance of a message.
' Arguments:
'   sBaseDocURL: the URL to the starting document. Tthis file must either exist, or the
'     "private:factory/swriter" invocation must be used to create a new file "from scratch"
'     with no template. Any standard invocation will work, and all of the options for
'     OOo's loadComponentFromURL() are supported. The format for the sBaseDocURL parameter
'     is one of the following:
'     "file://<path>/<fileNamePrefix>[.<fileNameSuffix>][?<name>=<value>[&<name>=<value>]...]"
'     or "private:factory/swriter[?<name>=<value>[&<name>=<value>]...]". Valid properties are:
'     "AsTemplate", "CharacterSet", "FilterName", "FilterOptions", "JumpMark", "Password", and
'     "ReadOnly".
'   sDataSourceURL: is the proprietary "file://" URL to the data source file (usually a .csv
'     file, but it can have any suffix -- "csv" stands for Comma Separated Value; however,
'     any delimiter can be specified). The format is (see the API docs for more information):
'     "file://<path>/<fileNamePrefix>.<fileNameSuffix>[?<name>=<value>[&<name>=<value>]...]".
'     Valid properties are "Charset", "HeaderLine", "FieldDelimiter", "StringDelimiter",
'     "DecimalDelimiter", "ThousandDelimiter", and "Filter".
' Return: Boolean
'   Returns a True if successful, or a False if it failed.
' Notes:
'   You really need to check out the Medformix.FlatMerge API documentation and probably the
'     OpenOffice IDL Reference for this all to make any sense.
' History:
'   16mar04: jbotte: Added header comment block.

Function Edit(sBaseDocURL As String, sDataSourceURL As String) As Boolean
   On Error Goto labelErrorHandler
   
   ' ++ Utility declarations used repeatedly in code
   Dim nI As Integer
   Dim bResult As Boolean
   Dim mSplitOut() As String, mParmArgs() As String  ' Storage while processing arguments
   Dim bDbIsRegistered As Boolean
   
   ' Will cause the function to exit with the value False on Exit. Set to True if Exit okay...
   Edit() = False
   
   LogMessage(eMSG_DEBUG, "Running Medformix.FlatMerge.Edit macro...")
   
   ' Until it is registered, this should be set false. It is used for cleanup...
   bDbIsRegistered = False

   Dim oFileAccess As Variant
   oFileAccess = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")

   Dim oURLTransformer As Variant
   oURLTransformer = CreateUnoService("com.sun.star.util.URLTransformer")

   ' ++ Parse and validate the URLs from the command line

   ' + Start with the document URL which is a simple file:// type specification
   mSplitOut() = Split(sBaseDocURL, "?")
   
   ' Only allow zero or one ?s in the URL
   If UBound(mSplitOut) > 1 Then
      LogMessage(eMSG_ERR, "Malformed base document URL... too many ?s: " + sDataSourceURL)
      Goto labelExitClean
   End If

   Dim aDocURL As New com.sun.star.util.URL
   aDocURL.Complete = Trim(mSplitOut(0))

   bResult = oURLTransformer.parseSmart(aDocURL, "")
   If bResult = False Then
      LogMessage(eMSG_ERR, "Failed to parse base document URL!")
      Goto labelExitClean
   End If
   
   ' Store the arguments, if any, in the URL.Arguments element of the structure
   ' Don't forget we've already taken care of the > 1 case above...
   If UBound(mSplitOut) = 1 Then
      aDocURL.Arguments = Trim(mSplitOut(1))
      If aDocURL.Arguments = "" Then
         LogMessage(eMSG_WARN, _
            "A base document argument was indicated with a ?, but not specified...")
      End If
   End If
   
   ' Check to see whether we are loading a document or not
   If LCase(aDocURL.Protocol) = "private:" And LCase(aDocURL.Path) = "factory" Then
      ' This macro only supports Writer documents, so check for it...
      If Not LCase(aDocURL.Name) = "writer" Then
         LogMessage(eMSG_ERR, "This macro cannot process anything but Writer documents...")
         Goto labelExitClean
      End If
   Else
      ' Looks like we're loading a document then... make sure it exists
      If Not oFileAccess.exists(aDocURL.Main) Then
         LogMessage(eMSG_ERR, "Document URL does not exist: " + aDocURL.Main)
         Goto labelExitClean
      End If
   
      If oFileAccess.isFolder(aDocURL.Main) Then
         LogMessage(eMSG_ERR, "Document URL cannot be a folder/directory: " + aDocURL.Main)
         Goto labelExitClean
      End If
   End If
   
   Const eDOCPROP_ASTEMPLATE    As Integer = 0
   Const eDOCPROP_CHARACTERSET  As Integer = 1
   Const eDOCPROP_FILTERNAME    As Integer = 2
   Const eDOCPROP_FILTEROPTIONS As Integer = 3
   Const eDOCPROP_JUMPMARK      As Integer = 4
   Const eDOCPROP_PASSWORD      As Integer = 5
   Const eDOCPROP_READONLY      As Integer = 6
   Const eHIGHEST_DOCPROP       As Integer = 6

   ' Set the default document properties
   Dim mDocProperties(eHIGHEST_DOCPROP) As New com.sun.star.beans.PropertyValue

   mDocProperties(eDOCPROP_JUMPMARK).Name = "JumpMark"
   mDocProperties(eDOCPROP_PASSWORD).Name = "Password"
   mDocProperties(eDOCPROP_READONLY).Name = "ReadOnly"
   mDocProperties(eDOCPROP_ASTEMPLATE).Name = "AsTemplate"
   mDocProperties(eDOCPROP_FILTERNAME).Name = "FilterName"
   mDocProperties(eDOCPROP_CHARACTERSET).Name = "CharacterSet"
   mDocProperties(eDOCPROP_FILTEROPTIONS).Name = "FilterOptions"
   
   ' And then the defaults for this application
   mDocProperties(eDOCPROP_JUMPMARK).Value = ""
   mDocProperties(eDOCPROP_PASSWORD).Value = ""
   mDocProperties(eDOCPROP_READONLY).Value = False
   mDocProperties(eDOCPROP_ASTEMPLATE).Value = False
   mDocProperties(eDOCPROP_FILTERNAME).Value = ""
   mDocProperties(eDOCPROP_CHARACTERSET).Value = "iso-8859-1"
   mDocProperties(eDOCPROP_FILTEROPTIONS).Value = ""

   ' Now we parse the arguments to sDataSourceURL, if any, and set oDbInstance.Info with them
   If aDocURL.Arguments <> "" Then
      ' Break apart multiple arguments (name=value) into an array of strings
      mParmArgs() = Split(aDocURL.Arguments, "&")
      
      For nI = 0 To UBound(mParmArgs)
         mSplitOut() = Split(mParmArgs(nI), "=")
         mSplitOut(0) = Trim(mSplitOut(0))
         If UBound(mSplitOut) = 0 Or UBound(mSplitOut) > 1 Then
            LogMessage(eMSG_ERR, "Malformed argument to sBaseDocURL: " + mParmArgs(nI))
            Goto labelExitClean
         End If
         mSplitOut(1) = Trim(mSplitOut(1))
         If mSplitOut(0) = "" Or mSplitOut(1) = "" Then
            LogMessage(eMSG_ERR, "Proper sBaseDocURL argument format is name=value: " + _
               mParmArgs(nI))
            Goto labelExitClean
         End If
         
         ' Yup, every program has to have "special cases"...
         Select Case LCase(mSplitOut(0))
            Case "astemplate":
               Select Case LCase(mSplitOut(1))
                  Case "true":
                     mDocProperties(eDOCPROP_ASTEMPLATE).Value = "True"
                  Case "false":
                     mDocProperties(eDOCPROP_ASTEMPLATE).Value = "False"
                  Case Else:
                     LogMessage(eMSG_ERR, _
                        "Invalid boolean specifier (True/False) " + _
                        "for AsTemplate argument: " + mSplitOut(1))
                     Goto labelExitClean
               End Select
            Case "characterset":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDocProperties(eDOCPROP_CHARACTERSET).Value = ""
               Else
                  ' Just have to assume the format is correct...
                  mDocProperties(eDOCPROP_CHARACTERSET).Value = mSplitOut(1)
               End If
            Case "filtername":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDocProperties(eDOCPROP_).Value = ""
               Else
                  ' Just have to assume the format is correct...
                  mDocProperties(eDOCPROP_).Value = mSplitOut(1)
               End If
            Case "filteroptions":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDocProperties(eDOCPROP_FILTEROPTIONS).Value = ""
               Else
                  ' Just have to assume the format is correct...
                  mDocProperties(eDOCPROP_FILTEROPTIONS).Value = mSplitOut(1)
               End If
            Case "jumpmark":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDocProperties(eDOCPROP_JUMPMARK).Value = ""
               Else
                  ' Just have to assume the format is correct...
                  mDocProperties(eDOCPROP_JUMPMARK).Value = mSplitOut(1)
               End If
            Case "password":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDocProperties(eDOCPROP_PASSWORD).Value = ""
               Else
                  ' Just have to assume the format is correct...
                  mDocProperties(eDOCPROP_PASSWORD).Value = mSplitOut(1)
               End If
            Case "readonly":
               Select Case LCase(mSplitOut(1))
                  Case "true":
                     mDocProperties(eDOCPROP_READONLY).Value = "True"
                  Case "false":
                     mDocProperties(eDOCPROP_READONLY).Value = "False"
                  Case Else:
                     LogMessage(eMSG_ERR, _
                        "Invalid boolean specifier (True/False) " + _
                        "for ReadOnly argument: " + mSplitOut(1))
                     Goto labelExitClean
               End Select
            Case Else:
               LogMessage(eMSG_ERR, "Invalid argument specified with sBaseDocURL: " + _
                  mSplitOut(0))
               Goto labelExitClean
         End Select
      Next nI
   End If
   ' + We have a valid base document URL that exists as a file on the system
   
   ' + Now we tackle the data source URL, which is an unstandard specification. We need
   ' + to parse the passed URL into a valid file:// URL and the unstandard suffix, if any
   mSplitOut() = Split(sDataSourceURL, "?")
   
   ' Only allow zero or one ?s in the URL
   If UBound(mSplitOut) > 1 Then
      LogMessage(eMSG_ERR, "Malformed data source URL... too many ?s: " + sDataSourceURL)
      Goto labelExitClean
   End If

   Dim aDbURL  As New com.sun.star.util.URL
   aDbURL.Complete = Trim(mSplitOut(0))

   bResult = oURLTransformer.parseSmart(aDbURL, "")
   If bResult = False Then
      LogMessage(eMSG_ERR, "Failed to parse data source URL!")
      Goto labelExitClean
   End If
   
   ' Store the arguments, if any, in the URL.Arguments element of the structure
   ' Don't forget we've already taken care of the > 1 case above...
   If UBound(mSplitOut) = 1 Then
      aDbURL.Arguments = Trim(mSplitOut(1))
      If aDbURL.Arguments = "" Then
         LogMessage(eMSG_WARN, _
            "A data source argument was indicated with a ?, but not specified...")
      End If
   End If
   
   If Not oFileAccess.exists(aDbURL.Main) Then
      LogMessage(eMSG_ERR, "Data source URL does not exist: " + aDbURL.Main)
      Goto labelExitClean
   End If
   
   If oFileAccess.isFolder(aDbURL.Main) Then
      LogMessage(eMSG_ERR, "Data source URL cannot be a folder/directory: " + aDbURL.Main)
      Goto labelExitClean
   End If   
   ' + We have a valid data source URL that exists as a file on the system

   ' + Now it's time to process and set any arguments to the DataSource and MailMerge objects
   Dim sDbName As String
   Dim oDbContext As Object
   Dim oDbInstance As Object
   
   ' This array of PropertyValue's will be passed to the Data Source object
   Dim mDbProperties(eHIGHEST_DBPROP) As New com.sun.star.beans.PropertyValue
   SetDefaultDbProps(mDbProperties())  ' Get it set up with the default values

   ' Extract the fileNameSuffix and fileNameSuffix for the data source
   mSplitOut() = Split(aDbURL.Name, ".")
   sDbName = mSplitOut(0)
   If UBound(mSplitOut) = 0 Then
      mDbProperties(eDBPROP_EXTENSION).Value = ""
   Else
      mDbProperties(eDBPROP_EXTENSION).Value = mSplitOut(UBound(mSplitOut))
   End If
   
   ' Instantiate the data source object and set what we know...
   oDbContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")

   If oDbContext.hasByName(sDbName) Then
      LogMessage(eMSG_NOTICE, "Data source name """ + sDbName + _
         """ existed... revoking from registry")
      oDbContext.revokeObject(sDbName)
   End If
      
   oDbInstance = oDbContext.createInstance()
   oDbContext.registerObject(sDbName, oDbInstance)
   bDbIsRegistered = True
   
   oDbInstance.URL = "sdbc:flat:" + aDbURL.Protocol + aDbURL.Path
   
   ' Now we parse the arguments to sDataSourceURL, if any, and set oDbInstance.Info with them
   If aDbURL.Arguments <> "" Then
      ' Break apart multiple arguments (name=value) into an array of strings
      mParmArgs() = Split(aDbURL.Arguments, "&")
      
      For nI = 0 To UBound(mParmArgs)
         mSplitOut() = Split(mParmArgs(nI), "=")
         mSplitOut(0) = Trim(mSplitOut(0))
         If UBound(mSplitOut) = 0 Or UBound(mSplitOut) > 1 Then
            LogMessage(eMSG_ERR, "Malformed argument to sDataSourceURL: " + mParmArgs(nI))
            Goto labelExitClean
         End If
         mSplitOut(1) = Trim(mSplitOut(1))
         If mSplitOut(0) = "" Or mSplitOut(1) = "" Then
            LogMessage(eMSG_ERR, "Proper sDataSourceURL argument format is name=value: " + _
               mParmArgs(nI))
            Goto labelExitClean
         End If
         
         ' Yup, every program has to have "special cases"...
         Select Case LCase(mSplitOut(0))
            Case "charset":
               ' Just have to assume the format is correct...
               mDbProperties(eDBPROP_CHARSET).Value = mSplitOut(1)
            Case "decimaldelimiter":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDbProperties(eDBPROP_DECIMALDELIMITER).Value = ""
               Else
                  mDbProperties(eDBPROP_DECIMALDELIMITER).Value = _
                     URLCharToString(mSplitOut(1))
                  If Len(mDbProperties(eDBPROP_DECIMALDELIMITER).Value) <> 1 Then
                     LogMessage(eMSG_ERR, _
                        "Invalid value specified for DecimalDelimiter argument: " + _
                        mSplitOut(1))
                     Goto labelExitClean
                  End If
               End If
            Case "fielddelimiter":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDbProperties(eDBPROP_FIELDDELIMITER).Value = ""
               Else
                  mDbProperties(eDBPROP_FIELDDELIMITER).Value = _
                     URLCharToString(mSplitOut(1))
                  If Len(mDbProperties(eDBPROP_FIELDDELIMITER).Value) <> 1 Then
                     LogMessage(eMSG_ERR, _
                        "Invalid value specified for FieldDelimiter argument: " + _
                        mSplitOut(1))
                     Goto labelExitClean
                  End If
               End If
            Case "headerline":
               Select Case LCase(mSplitOut(1))
                  Case "true":
                     mDbProperties(eDBPROP_HEADERLINE).Value = "True"
                  Case "false":
                     mDbProperties(eDBPROP_HEADERLINE).Value = "False"
                  Case Else:
                     LogMessage(eMSG_ERR, _
                        "Invalid boolean specifier (True/False) for " + _
                        "HeaderLine argument: " + mSplitOut(1))
                     Goto labelExitClean
               End Select
            Case "stringdelimiter":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDbProperties(eDBPROP_STRINGDELIMITER).Value = ""
               Else
                  mDbProperties(eDBPROP_STRINGDELIMITER).Value = _
                     URLCharToString(mSplitOut(1))
                  If Len(mDbProperties(eDBPROP_STRINGDELIMITER).Value) <> 1 Then
                     LogMessage(eMSG_ERR, _
                        "Invalid value specified for StringDelimiter argument: " + _
                        mSplitOut(1))
                     Goto labelExitClean
                  End If
               End If
            Case "thousanddelimiter":
               If LCase(mSplitOut(1)) = "{none}" Then
                  mDbProperties(eDBPROP_THOUSANDDELIMITER).Value = ""
               Else
                  mDbProperties(eDBPROP_THOUSANDDELIMITER).Value = _
                     URLCharToString(mSplitOut(1))
                  If Len(mDbProperties(eDBPROP_THOUSANDDELIMITER).Value) <> 1 Then
                     LogMessage(eMSG_ERR, _
                        "Invalid value specified for ThousandDelimiter argument: " + _
                        mSplitOut(1))
                     Goto labelExitClean
                  End If
               End If
            Case Else:
               LogMessage(eMSG_ERR, "Invalid argument specified with sDataSourceURL: " + _
                  mSplitOut(0))
               Goto labelExitClean
         End Select
      Next nI
   End If

   ' ++ Everything is ready, all that's left is the final initialization and launch
   oDbInstance.setPropertyValue("Info", mDbProperties())
   
   Dim oDocument As Object
   oDocument = StarDesktop.loadComponentFromURL(aDocURL.Main, "_blank", 0, mDocProperties())
   
   ' ++ Now, we attach a listener to clean up at exit...
   Dim oListener As Variant
   oListener = CreateUnoListener("DocListener_", "com.sun.star.document.XEventListener")
   oDocument.com_sun_star_document_XEventBroadcaster_addEventListener(oListener)
   
   ' And remember, that the oDocument's com.sun.star.text.DocumentSettings service has
   ' the CurrentDatabaseDataSource property that contains the name of the data source
   ' used for the document (that can be used to revoke the data source).
   
   ' We made it and everything's okay
   Edit() = True
   
   Exit Function

labelExitClean:
   ' Make sure the registered data source is cleaned up on exit
   If bDbIsRegistered Then
      oDbContext.revokeObject(sDbName)
      LogMessage(eMSG_DEBUG, "Data source " + sDbName + " successfully revoked")
   End If

   Exit Function
   
labelErrorHandler:
   LogMessage(eMSG_CRIT, "Medformix.FlatMerge.Edit script [line: " + Erl + _
      "] error [" + Err + "]: " + Error$
   Edit() = False
   Goto labelExitClean
End Function


' Subroutine: DocListener_disposing
' Description:
'   This is a listerner subroutine attached by the Edit() function to the document being
'   edited. When the document is past the point of no return and it is being closed (i.e.
'   it's past the point when a veto could be issued), then this is one of the last things
'   called before the document and its related information is no longer available. This
'   listener job is to revoke the registry entry for the data source used in the document
'   so it will not "hang around" after the editing is done.
' Global Variables:
'   eMSG_*: These global constants are used to tag the importance of a message.
' Arguments: None
' Notes:
'   This subroutine relies on the CurrentDatabaseDataSource property of the document to
'     find out what the data source name is. There's probably a way of breaking this...
'   This listener remains attached to the document "in concept" after Edit() exits (which
'     it does before this subroutine is called). However, when this listener is invoked,
'     it is run in a new OOo Basic context, and all context that had been available in
'     OOo Basic when the listener had been set has been lost (it's a brand new context
'     all to itself...). That's why I couldn't use a global variable to pass the data source
'     or any other context dependent information.
' History:
'   16mar04: jbotte: Added header comment block.

Sub DocListener_disposing()
   On Error Goto labelErrorHandler
   
   Dim sDbName As String
   Dim oDbContext As Variant
   Dim oDocSettings As Variant

   ' Get the name of the data source associated with the document being closed
   oDocSettings = ThisComponent.createInstance("com.sun.star.text.DocumentSettings")
   sDbName = oDocSettings.CurrentDatabaseDataSource
   
   ' Get the data source context so we can use the name service
   oDbContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")

   If oDbContext.hasByName(sDbName) Then
      oDbContext.revokeObject(sDbName)
      LogMessage(eMSG_DEBUG, "Data source " + sDbName + " successfully revoked")
   End If
   
   Exit Sub
   
labelErrorHandler:
   LogMessage(eMSG_CRIT, "Medformix.FlatMerge.DocListener_disposing script [line: " + Erl + _
      "] error [" + Err + "]: " + Error$
   Exit Sub
End Sub


' Subroutine: DocListener_notifyEvent
' Description:
'   This is a listerner subroutine attached by the Edit() function to the document being
'   edited. Its job is to wait for a "OnSaveDone" event and check to see if the "phantom
'   password" bug (Issue #16297) has afflicted the document, and if so, repairs the file.
' Global Variables:
'   eMSG_*: These global constants are used to tag the importance of a message.
' Arguments:
'   oEventInfo: This is a com.sun.star.document.EventObject and has two properties: "EventName"
'     and "Source". This subroutine only needs "EventName".
' Notes:
'   The "phatom password" bug (Issue #16297) was present in OOo 1.1.0 and 1.1.1rc that we tested...
'     this fix shouldn't cause problems with later releases, but it probably should be removed
'     for performance reasons when that bug is no longer a problem.
'   If you watch the bottom of the screen closely when you save a document invoked through
'     the Edit() function, you will be able to see the "save progress bar" across the bottom
'     flash twice if the file was repaired.
'   This listener remains attached to the document "in concept" after Edit() exits (which
'     it does before this subroutine is called). However, when this listener is invoked,
'     it is run in a new OOo Basic context, and all context that had been available in
'     OOo Basic when the listener had been set has been lost (it's a brand new context
'     all to itself...). That's why I couldn't use a global variable to pass the data source
'     or any other context dependent information.
' History:
'   16mar04: jbotte: Added header comment block.

Sub DocListener_notifyEvent(oEventInfo As com.sun.star.document.EventObject)
   On Error Goto labelErrorHandler
   
   If (oEventInfo.EventName <> "OnSaveDone") Then
      Exit Sub  ' Sorry, we already gave at the office...
   End If
   
   LogMessage(eMSG_DEBUG, "Event received: " + oEventInfo.EventName + "...")

   Dim nI As Integer
   Dim sDocURL As String
   Dim oDocArgs() As Variant
   Dim bHasURLArg As Boolean
   Dim bIsBadPassword As Boolean

   bHasURLArg = False
   bIsBadPassword = False
   
   oDocArgs = ThisComponent.getArgs()
   
   For nI = 0 to UBound(oDocArgs)
      If (oDocArgs(nI).Name = "Password") Then
         If (Len(oDocArgs(nI).Value) = 0) Then
            bIsBadPassword = True
         End If
         Exit For
      End If
   Next nI
   
   If (bIsBadPassword = False) Then
      Exit Sub  ' Nothing to see here, move on folks...
   End If
   
   For nI = 0 to UBound(oDocArgs)
      If (oDocArgs(nI).Name = "URL") Then
         bHasURLArg = True
         sDocURL = oDocArgs(nI).Value
         Exit For
      End If
   Next nI
   
   If (bHasURLArg = False) Then
      LogMessage(eMSG_NOTICE, "Document with ""phantom password"" bug cannot be repaired...")
   End If
   
   Dim nJ As Integer
   Dim oNewArgs(UBound(oDocArgs) - 1) As New com.sun.star.beans.PropertyValue
      
   LogMessage(eMSG_NOTICE, "Phantom password bug was found in document... repairing the document!")
   nJ = 0
   For nI = 0 to UBound(oDocArgs())
      If (oDocArgs(nI).Name <> "Password") Then
         oNewArgs(nJ).Name = oDocArgs(nI).Name
         oNewArgs(nJ).Value = oDocArgs(nI).Value
         nJ = nJ + 1
      End If
   Next nI
      
   ThisComponent.storeAsURL(sDocURL, oNewArgs())
   
   Exit Sub
   
labelErrorHandler:
   LogMessage(eMSG_CRIT, "Medformix.FlatMerge.DocListener_notifyEvent script [line: " + Erl + _
      "] error [" + Err + "]: " + Error$
   Exit Sub
End Sub
Back to top
View user's profile Send private message
jbotte
Power User
Power User


Joined: 27 Jan 2004
Posts: 57
Location: NC, USA

PostPosted: Fri Jul 09, 2004 9:09 am    Post subject: Request for Feedback... Reply with quote

So there you have it... If you have any comments or questions, suggestions or fixes, bug reports or success stories, please do not hesitate to contact me. You can use the OOo Forum Private Message service, just lift my email address from the comments in the code, or post in the forum here if you think everyone should be aware of what you have to say.

I tend to go through periods of intense workload, so I can't guarantee a timely response. Don't worry, it's not you... I'll do what I can, when I can.
Back to top
View user's profile Send private message
MED
General User
General User


Joined: 05 Jan 2006
Posts: 33

PostPosted: Sat Jan 14, 2006 3:43 am    Post subject: Reply with quote

Hi,

I have been trying to use the code for the Medformix macro but I am getting an error when invoking the macro with the following command :
soffice "macro:///Medformix.FlatMerge.File(file:///c:/opendoc/test.odt,file:///c:/opendoc/c.csv?FieldDelimiter=,,file:///c:/opendoc/,CustId)"
The data file is c.csv and the document containing the merge fields is test.odt.
The contents of c.csv is as follows:

CustId,LastName
0001a,Thumb
0002a,Giant

Test.odt contains two fields as follows:

<CustId><LastName>

I am using OOO 2.0 on Windows XP Pro. I am getting the following error
Medformix.FlatMerge.LogMessage script[line: 240] error [53]: File not found Medformix.FlatMerge.File script [line:613] error [1]: An exception occurred
Type: com.sun.star.lang.IllegalArgumentException
Message: The data source was not saved. Please use the interface XStorage to save the datasource.}

I am a newbie in OO. Please can you advise.

Thank you

Medasha
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Sat Jan 14, 2006 7:55 am    Post subject: Reply with quote

I have never used this macro but it looks like OOo's complaint is that you have not registered your file (c.csv) as a data source. Assuming you are using version 2 this is done by:
File > New > Database - CONNECT to existing database.

After you complete this you can check to see if your file shows as a Table when you open the F4 data source viewer in any Writer doc and the database name you used above.
Back to top
View user's profile Send private message
RizzaMiky
General User
General User


Joined: 07 Feb 2006
Posts: 6
Location: Lodi, Italy

PostPosted: Tue Feb 07, 2006 8:15 am    Post subject: Using this macro in Openoffice 2.0.1 Reply with quote

I tried this macros with version 2.0.1 and also with 1.1.4
With version 1.1.4 everything OK. It works.
With version 2.0.1 I got the same error of MED at this istruction (the bold one)

oDbInstance = oDbContext.createInstance()
oDbContext.registerObject(sDbName, oDbInstance)

I would like to register the csv datasource without manual operation.
Why in version 1.1.4 works and in version 2.0.1 doesn't ?

Thanks in advance
Michele
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Thu Feb 09, 2006 11:17 am    Post subject: Reply with quote

Hass anyone started the job of refactoring this for 2.x?
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group