| View previous topic :: View next topic |
| Author |
Message |
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
 Posted: Mon Mar 29, 2004 1:56 pm Post subject: Comprehensive MailMerge for Flat Files |
 |
|
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 |
|
 |
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Wed Mar 31, 2004 12:19 pm Post subject: The Utility Functions/Subroutines |
|
|
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 |
|
|
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Thu Apr 01, 2004 8:26 am Post subject: Medformix.FlatMerge.File API v1.1 |
|
|
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 — 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 |
|
|
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Thu Apr 01, 2004 8:30 am Post subject: Medformix.FlatMerge.File Macro Code |
|
|
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 |
|
|
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Thu Apr 01, 2004 10:49 am Post subject: Medformix.FlatMerge.Printer API v1.1 |
|
|
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 |
|
|
Peter O
Guest
|
| Posted: Thu Jun 17, 2004 12:49 am Post subject: |
|
|
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
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Fri Jul 09, 2004 7:17 am Post subject: |
|
|
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 |
|
|
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Fri Jul 09, 2004 7:56 am Post subject: Medformix.FlatMerge.Printer Macro Code |
|
|
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 |
|
|
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Fri Jul 09, 2004 8:53 am Post subject: Medformix.FlatMerge.Edit API v1.0 |
|
|
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 (&); 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&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 |
|
|
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Fri Jul 09, 2004 9:02 am Post subject: Medformix.FlatMerge.Edit Macro Code |
|
|
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 |
|
|
jbotte
Power User
Joined: 27 Jan 2004
Posts: 57
Location: NC, USA
|
| Posted: Fri Jul 09, 2004 9:09 am Post subject: Request for Feedback... |
|
|
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 |
|
|
MED
General User
Joined: 05 Jan 2006
Posts: 33
|
| Posted: Sat Jan 14, 2006 3:43 am Post subject: |
|
|
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 |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8982
Location: Lexinton, Kentucky, USA
|
| Posted: Sat Jan 14, 2006 7:55 am Post subject: |
|
|
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 |
|
|
RizzaMiky
General User
Joined: 07 Feb 2006
Posts: 6
Location: Lodi, Italy
|
| Posted: Tue Feb 07, 2006 8:15 am Post subject: Using this macro in Openoffice 2.0.1 |
|
|
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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
|
| Back to top |
|
|
|
|
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
|
