Free Support Forum - aspose.com

Cell with a paragraph and mutiple bold strings

I am working on a spreadsheet that has a few paragraphs of text that is placed in a single cell. I need certain portions of the text to be filled in and marked as bold. In my template file, I have bolded the appropriate sections, and inserted tags that will be replaced with values from our database. So our text looks like this:

In accordance with the terms and conditions outlined in your maintenance agreement, blah blah blah.

Please fax this form along with any changes to your company's information to <%FaxTo%>. This form can also be emailed to <%EmailTo%>.

If you have any questions, please contact us by calling <%Contact%> at <%ContactPhone%>. Thank you.

I am using the following code to try and set the tags, but it seems that whenever I use the .Charaters(x,y).isBold = true, it resets all bolding for the entired string. How can I replace the values and keep the bold text?

ReplaceAndBold(.Cells(6, 0), "<%FaxTo%>", _dr("FaxTo"))

ReplaceAndBold(.Cells(6, 0), "<%FaxTo%>", _dr("FaxTo"))

ReplaceAndBold(.Cells(6, 0), "<%EmailTo%>", _dr("EmailTo"))

ReplaceAndBold(.Cells(6, 0), "<%Contact%>", _dr("Contact"))

ReplaceAndBold(.Cells(6, 0), "<%ContactPhone%>", _dr("ContactPhone"))

ReplaceAndBold(.Cells(6, 0), "along with any changes", "along with any changes")

Private Sub ReplaceAndBold(ByRef _cell As Cell, ByVal _target As String, ByVal _value As String, Optional ByVal _bold As Boolean = True)

'Get starting point

Dim _start As Integer = _cell.StringValue.IndexOf(_target)

'Make the swap

If _value IsNot Nothing Then

_cell.PutValue(_cell.StringValue.Replace(_target, _value))

End If

'If they wanted bold, and we found a match make it bold!

If _bold AndAlso _start > 0 Then

_cell.Characters(_start, _value.Length).Font.IsBold = True

End If

End Sub

Does it have something to do with the PutValue that is losing formatting?

Got it - or at least this works

.Cells(6, 0).PutValue(.Cells(6, 0).StringValue.Replace("<%FaxTo%>", _dr("FaxTo")))

.Cells(6, 0).PutValue(.Cells(6, 0).StringValue.Replace("<%EmailTo%>", _dr("EmailTo")))

.Cells(6, 0).PutValue(.Cells(6, 0).StringValue.Replace("<%Contact%>", _dr("Contact")))

.Cells(6, 0).PutValue(.Cells(6, 0).StringValue.Replace("<%ContactPhone%>", _dr("ContactPhone")))

BoldText(.Cells(6, 0), _dr("FaxTo"))

BoldText(.Cells(6, 0), _dr("EmailTo"))

BoldText(.Cells(6, 0), _dr("Contact"))

BoldText(.Cells(6, 0), _dr("ContactPhone"))

BoldText(.Cells(6, 0), "along with any changes")

Private Sub BoldText(ByRef _cell As Cell, ByVal _target As String)

Dim _start As Integer = _cell.StringValue.IndexOf(_target)

_cell.Characters(_start, _target.Length).Font.IsBold = True

End Sub