Trying to build document with html string - taken from database query using ExecuteDataTable Function

Aspose.Words Product Family
1

I’ve been using the following to build a document with multiple sections.

Dim doc As Document = New Document(Server.MapPath("slcreport.dot"))

doc.MailMerge.ExecuteWithRegions(GetGrades())

Return doc

I'm now trying to insert html into the document for a couple of the merge fields (I have the enterprise edition). Is there an easy way to do this?

I'm familiar with this method:

builder.MoveToMergeField("TeacherResponse")

builder.InsertHtml(teachercomment)

But need to build a document from a query taken from a database. For example:

Private Function GetGrades() As DataTable

Return ExecuteDataTable("Grades", String.Format( _

"Select *, Title + ' ' + LastName as TeacherName from viewslcreport WHERE StudentID = " & Request.QueryString("studentid") & " and AcademicYearID = " & System.Configuration.ConfigurationManager.AppSettings("AcademicYear") & " and Quarter = " & System.Configuration.ConfigurationManager.AppSettings("Quarter") & " order BY CourseName"))

End function

Cheers,

Neil.

2

Hi Neil,

Your case seems like a perfect job for Document.MailMerge.MergeField event.

First you need to subscribe to the MergeField event:

AddHandler doc.MailMerge.MergeField, AddressOf HandleMergeField

and write a handler - something like this:

Private Sub HandleMergeField(ByVal sender As Object, ByVal e As Aspose.Word.Reporting.MergeFieldEventArgs)

If (e.DocumentFieldName.StartsWith("h")) Then

builder.MoveToMergeField(e.DocumentFieldName)

builder.InsertHtml(e.FieldValue)

End If

End Sub

Note that we need somehow to distinguish the fields that should be substituted with HTML. In the above sample we presume they are having 'h' prefix, but it can be done in any other way convenient to you.

3

I seem to be having trouble. Please excuse my lack of knowledge.

My full code is this.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'If Len(Session("UserID")) = 0 Then

'Response.Redirect("../comment_writing/teacher_login.aspx")

'End If

Dim SLCID As Integer = Request.QueryString("studentid")

Dim sql As String

Dim studentname As String

Dim advisorname As String

Dim gradelevel As String

Dim academicyear As String

'Me.curPath = MapPath(".")

'Dim licenseFile As String = MapPath("License") + "\\Aspose.Word.lic"

'Open precreated designer file.

Dim license As License = New License

license.SetLicense(Server.MapPath("Aspose.Word.lic"))

'Create the document that user requested. Action is specified in the request string.

Dim doc As Document

doc = CreateEvalReportFromDataTables()

'Dim sql As String

Dim mydatareader As SqlDataReader

Dim a As New hollandhall.Database

sql = "select * from viewAcademicYear where AcademicYearID = " & ConfigurationSettings.AppSettings("AcademicYear")

mydatareader = a.GetDR(sql)

Do While mydatareader.Read

academicyear = mydatareader.Item("AcademicYear")

Loop

sql = "select * from ViewStudentInfo where ID = " & SLCID

mydatareader = a.GetDR(sql)

Do While mydatareader.Read

studentname = mydatareader.Item("StudentFirstName") & " " & mydatareader.Item("StudentLastName")

gradelevel = mydatareader.Item("Grade")

advisorname = mydatareader.Item("Title") & " " & mydatareader.Item("LastName")

Loop

doc.MailMerge.Execute( _

New String() {"StudentName", "AdvisorName", "GradeLevel", "AcademicYear"}, _

New Object() {studentname, advisorname, gradelevel, academicyear})

'Stream the document to the client browser.

doc.Save( _

String.Format("report.doc", CreateEvalReportFromDataTables()), _

SaveFormat.FormatDocument, _

IIf(saveTypeBtn.SelectedIndex = 0, SaveType.OpenInBrowser, SaveType.OpenInWord), _

Me.Response)

Response.End()

End Sub

Private Sub HandleMergeField(ByVal sender As Object, ByVal e As aspose.Word.Reporting.MergeFieldEventArgs)

If (e.DocumentFieldName.StartsWith("h")) Then

builder.MoveToMergeField(e.DocumentFieldName)

builder.InsertHtml(e.FieldValue)

End If

End Sub

V isual studio does not like aspose.Word.Reporting.MergeFieldEventArgs (I get a type not defined error).

Also, where should I declare the builder object?

4

OK, I figured it out.