Hi Jan, have you seen http://development.openoffice.org/releases/DEV300_m50_snapshot.html ?
-> Transporting newlines in spreadsheet formulas
http://www.openoffice.org/issues/show_bug.cgi?id=35913
Oliver
Am 12.06.2009 22:39 schrieb Jan Holst Jensen:
> 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: dev-unsubscr...@api.openoffice.org
> For additional commands, e-mail: dev-h...@api.openoffice.org
>
--
GnuPG key 0xCFD04A45: 8822 057F 4956 46D3 352C 1A06 4E2C AB40 CFD0 4A45
signature.asc
Description: OpenPGP digital signature
