Hi all.
I am developing an add-in that has to work with multi-line text entries
in cells. The text entries that will be processed are protein entries
that in most formats are line-oriented, so there is no way around
dealing with multi-line cells.
The code below illustrates that multi-line text entered into a cell lose
linefeeds when passed to a formula (seems like linefeeds are converted
to spaces ?). Open a blank spreadsheet document, paste the code into the
Standard module of that document and run the code.
As you can see, all linefeeds are stripped when the Cell value is passed
to a formula: B1 shows 0. However, if linefeeds occur in the result of a
formula they are retained: B2 shows 2 (control chars of A1 are lost but
the added control chars of the ExtraLineFunc() function are counted).
The really perplexing part is that if you access the Cell's String
property directly the control chars are accessible. See results in B4
and B5.
Is there any way that I can trick Calc to pass the cell value
differently to the underlying spreadsheet function ? The spreadsheet
function is implemented in Python as an UNO component if that's any help.
Behavior observed in both OpenOffice 3.0.1 and 3.1.0. Only tested on
Windows XP.
Cheers
-- Jan
<code>
Sub Main
Sheet = ThisComponent.Sheets(0)
Sheet.GetCellByPosition(0, 0).String = "Hello" & Chr(13) & Chr(10) &
"World"
Sheet.GetCellByPosition(0, 1).Formula = "=EXTRALINEFUNC(A1)"
' Passing Cell's text to function via formula.
Sheet.GetCellByPosition(1, 0).Formula = "=CONTROLCHARCOUNT(A1)"
Sheet.GetCellByPosition(1, 1).Formula = "=CONTROLCHARCOUNT(A2)"
' Passing Cell's text directly to function via the String property.
S = Sheet.GetCellByPosition(0, 0).String
Sheet.GetCellByPosition(1, 3).Value = ControlCharCount(S)
S = Sheet.GetCellByPosition(0, 1).String
Sheet.GetCellByPosition(1, 4).Value = ControlCharCount(S)
End Sub
Function ExtraLineFunc(S1 as String)
ExtraLineFunc = S1 & Chr(13) & Chr(10) & "Extra line"
End Function
Function ControlCharCount(S1 as String)
Dim Result as Integer
Result = 0
For i = 1 to Len(S1) Step 1
If Asc(Mid(S1, i, 1)) < 32 Then
Result = Result + 1
End If
Next i
ControlCharCount = Result
End Function
</code>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
