Calc: Serious problems accessing multi-line cell text.

[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-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Oliver Brinzing]

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-unsubscribe@...
> For additional commands, e-mail: dev-help@...
>

--

GnuPG key 0xCFD04A45: 8822 057F 4956 46D3 352C 1A06 4E2C AB40 CFD0 4A45

[Jan Holst Jensen]

Oliver Brinzing wrote:
> 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
>  
Ahhh!! You guys are already solving it. Fantastic :-).

I have downloaded the m50 snapshot and will give it a spin and report back.

Cheers
-- Jan

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

[Jan Holst Jensen]

Jan Holst Jensen wrote:

> Oliver Brinzing wrote:
>> 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
>>  
> Ahhh!! You guys are already solving it. Fantastic :-).
>
> I have downloaded the m50 snapshot and will give it a spin and report
> back.

And the verdict is: Works great as far as I can see.

The code I posted now behaves consistently. I can copy multi-line text,
and formulas working on it, to another sheet or workbook without
problems. My extension's spreadsheet functions no longer complain and
built-ins like "=CONCATENATE(A1;A2)" also work, nicely preserving linefeeds.

My thanks to the developers. I am a happy camper when 3.2 gets out.

Cheers
-- Jan

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...