Word Document Merge with BLOB DB Fields


#1

Hi,
I am evaluating Aspose.Word
I have a database table with coloms for eg: Name (Char), Address1 (Char), State (Char) and Resume (BLOB) Fields. Resume Field Contains a BLOB Word Document of the candidates Resume.
I need to create one document for all candidates Resume for the given Selected State.

Expected output should look like
-----------------------------------------------------------------------------
Name : ABC
Address: 123 Street

Resume: (ABC’s Resume Doc With Formated Document Text)

------------------------------------------------------------------------------
Name : DEF
Address : 453 Main

Resume: (DEF’s Resume Doc with formatted Document Text)

------------------------------------------------------------------------------
etc…
Is this something possible with Aspose.Word. If so how do i achieve it. Any direction or sample code would be greatly appreciated.

Thanks


#2

Hi,

1. Create a report template document and insert merge fields into it.

2. Document.MailMerge execute will work fine as is for simple string fields.

3. It is a little more complicated with inserting other MS Word documents, but possible. You need to define an event handler for the MergeField event.

4. Inside your event handler, you need to load another Documnet object from your BLOB field. Once the resume document is loaded, you need to copy or move content from the resume document to your report document.

5. Moving/copying document is done section by section. See Section object methods. Basically, you need a loop to go through all sections of the resume document. Please note that some formatting (especially list formatting) might be lost when moving content from one document to another.

Search the forums, I believe there are examples for everything I described, some of them are:
http://www.aspose.com/forums/ShowPost.aspx?PostID=5477
http://www.aspose.com/forums/ShowPost.aspx?PostID=11194
http://www.aspose.com/forums/ShowPost.aspx?PostID=9414
http://www.aspose.com/forums/ShowPost.aspx?PostID=12455


#3

Hi Roman,

In the above situation when i create the template how should the placeholder (MergeField) is it ?Image:BlobData? ?

This is how i tried coding it.

1. Document from the Database
2. Temporary document
3. Final Report

Created my word template and then created a temp blank document Test.doc (just for a placeholder)
then removed the section from the document(1) read from the DB and added it on to the temp document(2).
Now how to i make it to add to my final document(3). When i use the code below I get a

System.ArgumentException: Invalid parameter used. exception. Am i doing something totally wrong?

Private Sub HandleMergeEmployeePhoto(ByVal sender As Object, ByVal e As MergeImageFieldEventArgs)
'The field value is a byte array, just cast it and create a stream on it.
Dim imageStream As MemoryStream = New MemoryStream(CType(e.FieldValue, Byte()))
'Now the mail merge engine will retrieve the image from the stream.
Dim word As word = New word
Dim doc As Document = word.Open(imageStream)
Dim doc2 As Document = word.Open("C:\test.doc")
Dim section As section
section = doc.Sections(0)
doc.Sections.RemoveAt(0)
doc2.Sections.Add(section)
Dim ioStr As new MemoryStream
doc2.Save(ioStr, SaveFormat.FormatDocument)
e.ImageStream = ioStr
End Sub

Thanks


#4

In the last two lines of your code you save a DOC into a stream and then tell Aspose.Word to load an Image from it. Have you seen the doc: http://www.aspose.com/Products/Aspose.Word/Api/Aspose.Word.MergeImageFieldEventArgs.html
It's certainly not what you need to do.

Don't use <> in your case because it just tells Aspose.Word to treat the value for this field as a file name of the image to load and insert into the document. Just use a simple field name <>. Also, use MergeField event, not MergeImageField.

Not sure why you need a placeholder document. You should be able to just load the document from BLOB data and then insert a section (or better yet all sections) from that document to the final document.

Let me know if you need more help. I might be able to write up sample code.


#5

Roman,

I tried my level best knowledge about Aspose.Word to make this work but unfortunately was not able to get the required result.
Details:
I have an database table "BLOBTABLE" with two fields "ID" and a Blob document data field "BLOBDATA"

I have template created (BlobTestTemplate.doc")with
Mailmerge Fields
?TableStart:BLOBTABLE?
?MYID?
?BlobData?
?TableEnd:BLOBTABLE?


The result i am looking for is something like this
--------------------------------------
ID: 1
Document : Blob Document Data for ID 1


ID: 2
Document : Blob Document Data for ID 2


ID: 3
Document : Blob Document Data for ID 3

------------------------------------------

This is the code i used :

Private Function CreateDocument(ByVal param As String) As Document

Dim sqlString As String
Dim SpCode As Integer
Dim KeyValues As String
Dim WhereClause As String
Dim KeyItems As String()
Dim i As Integer
Dim j As Integer
Dim doc As Document
Dim TemplateName As String
TemplateName = "BlobTestTemplate.doc"
doc = OpenDoc(TemplateName)

Dim conn As OracleConnection = Nothing
Dim dataReader As OracleDataReader = Nothing
Try
'Open database connection.
conn = New OracleConnection(GetConnString())
conn.Open()
sqlString = "SELECT * FROM BLOBTABLE "
Dim cmd As OracleCommand = New OracleCommand(sqlString, conn)
'Open the data reader. It needs to be in the normal mode that reads all record at once.
dataReader = cmd.ExecuteReader()
AddHandler doc.MailMerge.MergeField, AddressOf HandleBlobField
doc.MailMerge.ExecuteWithRegions(dataReader, "BLOBTABLE")
Finally
If Not dataReader Is Nothing Then
dataReader.Close()
End If
If Not conn Is Nothing Then
conn.Close()
End If
End Try
Return doc
End Function


Private Sub HandleMergeEmployeeField(ByVal sender As Object, ByVal e As MergeFieldEventArgs)
If e.FieldName.ToUpper = "BLOBDATA" Then
Dim imageStream As MemoryStream = New MemoryStream(CType(e.FieldValue, Byte()))
'Now the mail merge engine will retrieve the image from the stream.
Dim word As word = New word
Dim doc As Document = word.Open(imageStream)
Dim section As section
section = doc.Sections(0)
doc.Sections.RemoveAt(0)
e.Document.Sections.Add(section)
End If
End Sub

If you can get me some piece of code which would direct me in the right path it would be of great help.

Thank you


#6

Great, I think your code is 99% correct now. Try adding this at the end:

e.Document.Sections.Add(section)
//Don’t let the mail merge engine to insert text too.
e.Text = “”;

Also, you are only moving one section. If you want to be sure it will move all sections in case the document in the blob field has more than one section, you need a loop.

Thank you for helping me to realize the documentation on merge field events needs improving.

///


/// Gets or sets text that will be inserted into the document for the current merge field.
///

///
///

When your event handler is called, this property is set to null.


///

If you leave Text as null, the mail merge engine will insert in place of the merge field.


///

If you set Text to a non empty string, the string will be inserted into the document in place of the merge field.


///

If you set Text to an empty string, the mail merge engine will remove the merge field.


///
public string Text

#7

Thanks Roman,
With this code i output document created is like below

ID. 1

ID. 2

ID. 3


Blob Data document section of ID. 1

Blob Data document section of ID. 2

Blob Data document section of ID. 3

Is there a way to create this blob data section just after each id.

I really appreciate your support.
Thank you
Sreenath


#8

Sorry for misdirections. I think we should not really use mail merge here. Gives no advantage. Should really just loop through all records of the table and use DocumentBuilder.

Something like this (pseudocode):

Document dstDoc = new Document();
foreach record in the table
{
dstDoc.MoveToDocumentEnd();
dstDoc.Writeln("ID: " + id);
dstDoc.Writeln(“Document:”);
Document srcDoc = LoadDocFromBlobField()
add src doc section to dst doc
}