Hi
I want to make an pdf from xlsx-file and make some writable field in the pdf. I am using your aspose.pdf and aspose.cells for the .Net.
The field that I want to make writably can be more then one row heigh and there can be more than one fields to be writably. My problem is that I can’t make a writably cell with more than one row heigh and I don’t manage to get the box around the field to be correct. Am I count something wrong? I want the size of the red box to be the same size as the merged cell “boarder”. What is the correct way to make it work?
I have made a function GeneratePDFFieldsNew which is included in this post. The sample xlsx-file is attached.
I call my function with:
Call GeneratePDFFieldsNew(“c:\temp\AvvAnmalan.xlsx”, _
“C:\Temp\MyTest2.pdf”, _
“SKRIVBAR” )
Private Function GeneratePDFFieldsNew(ByVal pathToExcel As String, ByVal pathToResult As String, ByVal fieldName As String) As Boolean
Dim Ret As Boolean = True
Dim cellTextWidth As New Dictionary(Of String, String)()
Dim ms As New MemoryStream()
Dim book = New Aspose.Cells.Workbook(pathToExcel)
Dim sheet = book.Worksheets(0)
Dim cells = sheet.Cells
Dim range As Aspose.Cells.Range = cells.MaxDisplayRange
Dim iTmpCounter As Integer = 0
Try
For row As Integer = 0 To range.RowCount - 1
For col As Integer = 0 To range.ColumnCount - 1
iTmpCounter += 1
Dim cell = cells(row, col)
If cell.DisplayStringValue <> String.Empty Then
Dim iRek As Integer = 0
Dim fieldWidth As Double = 0.0
Dim fieldHeight As Double = 0.0
If cell.DisplayStringValue.StartsWith(fieldName) Then
Dim sTmp As String = “”
If cell.IsMerged Then
Dim mergedrange = cell.GetMergedRange()
Dim sectionStartColumn As Integer = mergedrange.FirstColumn
Dim sectionEndColumn As Integer = mergedrange.ColumnCount - mergedrange.FirstColumn + 1
For iRek = sectionStartColumn To sectionEndColumn
fieldWidth = fieldWidth + cells.GetColumnWidthInch(iRek)
Next
If mergedrange.RowCount > 1 Then
Dim sectionStartRow As Integer = mergedrange.FirstRow + 1
Dim sectionEndRow As Integer = sectionStartRow + mergedrange.RowCount - 1
For iRek = sectionStartRow To sectionEndRow
fieldHeight = fieldHeight + cells.GetRowHeightInch(iRek)
Next
Else
fieldHeight = cells.GetRowHeightInch(mergedrange.FirstRow)
End If
Else
fieldWidth = cells.GetColumnWidthInch(cell.Column)
fieldHeight = cells.GetRowHeightInch(cell.Row)
End If
End If
If cell.DisplayStringValue.StartsWith(fieldName) Then
Dim sTmpId As String = “”
'save cells text and their width and height in inches for text replacement with form fields using Aspose.Pdf
sTmpId = cell.DisplayStringValue
cellTextWidth.Add(sTmpId, fieldWidth.ToString + “" + fieldHeight.ToString)
End If
End If
Next
Next
'Save the document in PDF format
book.Save(ms, Aspose.Cells.SaveFormat.Pdf)
Dim pdfDocument As Document = New Aspose.Pdf.Document(ms)
Dim formEditor As New FormEditor(pdfDocument)
For Each pair As KeyValuePair(Of String, String) In cellTextWidth
'TextFragmentAbsorber textFragmentAbsorber = new Aspose.Pdf.Text.TextFragmentAbsorber(pair.Key);
Dim textFragmentAbsorber As New TextFragmentAbsorber("\b" + pair.Key + “\b”)
'set text search option to specify regular expression usage
Dim textSearchOptions As New Aspose.Pdf.Text.TextOptions.TextSearchOptions(True)
textFragmentAbsorber.TextSearchOptions = textSearchOptions
pdfDocument.Pages.Accept(textFragmentAbsorber)
If textFragmentAbsorber.TextFragments.Count > 0 Then
Dim iCounter As Integer = 0
For Each fragment As TextFragment In textFragmentAbsorber.TextFragments
Dim lLX As Single = 0.0
Dim lLY As Single = 0.0
Dim uRX As Single = 0.0
Dim uRY As Single = 0.0
Dim textRect = fragment.Rectangle
fragment.Text = String.Empty
’ measuring unit of Aspose.Pdf is Point 72 points=1, Converted width of cell to points for form field width
Dim sArr() As String = pair.Value.Split("”) 'Width_Height
lLX = CSng(textRect.LLX)
lLY = CSng(textRect.LLY) - CSng(sArr(1) * 72)
uRX = CSng(textRect.LLX + CSng(sArr(0)) * 72)
uRY = CSng(textRect.URY)
formEditor.AddField(FieldType.Text, “Field_” + iCounter.ToString, fragment.Page.Number, lLX, lLY, uRX, uRY)
Dim fieldFacade As New FormFieldFacade
formEditor.Facade = fieldFacade
fieldFacade.Font = Aspose.Pdf.Facades.FontStyle.TimesRoman
fieldFacade.TextColor = System.Drawing.Color.Black
fieldFacade.Alignment = FormFieldFacade.AlignLeft
fieldFacade.BorderStyle = FormFieldFacade.BorderStyleSolid
fieldFacade.FontSize = 8
fieldFacade.BackgroundColor = System.Drawing.Color.White
fieldFacade.BorderColor = System.Drawing.Color.Red
formEditor.DecorateField(“Field_” + iCounter.ToString)
iCounter += 1
Next
End If
Next
formEditor.Save(pathToResult)
Catch ex As Exception
Ret = False
Throw ex
End Try
Return Ret
End Function