Possible bug in Spread for Windows 4.x

Posted by: j2associates on 8 September 2017, 2:00 pm EST

  • Posted 8 September 2017, 2:00 pm EST

    Hello all,


    I am not sure if this is a bug or not but the Spread does not display strings with embedded Html Entities (eg & or <) correctly. If one is present, the Text property is an empty string even though the Value property correctly shows the entire string.


    ? me.FpSpreadResults.Sheets(1).Cells(3, 0).Value


    "="If this well is drilled deeper than " & Trim(Fields!SamplesDepth.Value) & " feet, samples must be collected from " & Trim(Fields!SamplesTop.Value) & " to total depth and submitted to:" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Indiana Geological Survey" & Chr(13) & Chr(10) & "611 N. Walnut Grove Ave." & Chr(13) & Chr(10) & "Bloomington, IN 46405-2208"" {String}


    String: "="If this well is drilled deeper than " & Trim(Fields!SamplesDepth.Value) & " feet, samples must be collected from " & Trim(Fields!SamplesTop.Value) & " to total depth and submitted to:" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Indiana Geological Survey" & Chr(13) & Chr(10) & "611 N. Walnut Grove Ave." & Chr(13) & Chr(10) & "Bloomington, IN 46405-2208""


    ? me.FpSpreadResults.Sheets(1).Cells(3, 0).Text


    ""


    Perhaps an XmlCellType would solve the problem, or perhaps a property could be added to either or both of the GeneralCellType and TextBoxCellType. Something along the lines of ConvertHtmlEntitValues would work. If this behavior is by design, how do I get it to display in my cell? Thanks in advance for any ideas and/or suggestions!


     

  • Replied 8 September 2017, 2:00 pm EST

    Hello,

    There is difference between the value and text property of the cell. The Value property represents the raw data stored in the cell. The Value property can be almost any data type including Double, Int32, String, DateTime, etc. Value = null (Nothing in VB) indicates that the cell contains no data.The Text property is a text representation of the raw data. The Text property will always be a String.

    Thanks

  • Replied 8 September 2017, 2:00 pm EST

    Hello,


    If you open up the attached project and run it, the 6th row has ControlName = SampleBlock. The ControlValue contains this string delimited by tilde ~: ~="If this well is drilled deeper than " & Trim(Fields!SamplesDepth.Value) & " feet, samples must be collected from " & Trim(Fields!SamplesTop.Value) & " to total depth and submitted to:" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Indiana Geological Survey" & Chr(13) & Chr(10) & "611 N. Walnut Grove Ave." & Chr(13) & Chr(10) & "Bloomington, IN  46405-2208"~. It displays as blank in the spread.


     


    2009/08/Bug.zip
  • Replied 8 September 2017, 2:00 pm EST

    Hello,


    Can you let us know how you set up the value in the cell? I tested with this code and it returned the correct Value and Text from the cell.


    FpSpread1.Sheets(0).Cells(1, 1).Value = "=""If this well is drilled deeper than "" & Trim(Fields!SamplesDepth.Value) & "" feet, samples must be collected from "" & Trim(Fields!SamplesTop.Value) & "" to total depth and submitted to:"" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & ""Indiana Geological Survey"" & Chr(13) & Chr(10) & ""611 N. Walnut Grove Ave."" & Chr(13) & Chr(10) & ""Bloomington, IN 46405-2208"

  • Replied 8 September 2017, 2:00 pm EST

    Hello SureshD,


    I am aware of the differences between the Text and Value properties. The example I posted displays an empty string for the the Text property even though there is a valid string value present. Hence the question about whether or not this is a bug.

  • Replied 8 September 2017, 2:00 pm EST

    As always, thanks for the thorough explanation. That makes sense. Just to be clear on what I am requesting/suggesting, it should not throw an exception in the above case as that would be very difficult to anticipate from a development standpoint. But if it returned some kind of an Excel like error in the Text property, it wouild alert the user to the problem without crashing the app.
  • Replied 8 September 2017, 2:00 pm EST

    Hey Scott,


    Thanks for the quick response! To say the least, that is very subtle. I could spend hours beating my head against a wall for something like this and never think to check in the area required to resolve the issue.


    Would it be possible to have it return some kind of an Excel type error (maybe #MaxLengthError) or some such which would alert the user and/or developer to the issue. Also, why is the default cell type not General, which displays correctly, for all cell types not explicitly set by the developer, either in code or via the designer?

  • Replied 8 September 2017, 2:00 pm EST

    Hello,


    We need to default to TextCellType with any string data in the column, since it is possible for there to be numbers or dates in the same column and if we use GeneralCellType, these data would be formatted as numbers or dates and right align the text. Also if the numbers or dates are string representation of the number or data, the Parse method of the GeneralCellType would unbox this to it's native type which would cause problems. We need to use TextCellType by default in this situation.


    I will pass along the idea of some sort of error to fire when this scenario happens.

  • Replied 8 September 2017, 2:00 pm EST

    Hello,


    When you are setting the DataSource for Spread, you have DataAutoCellTypes left at the default of True, which is going to set up these celltypes as TextCellType. The default MaxLength for TextCellTypes is 255 and since these text are larger than this max, nothing is displayed. You would need to set the DataAutoCellTypes property to False and either leave the default GeneralCellType or set the CellType to TextCellType and increase the length of MaxLength.

  • Replied 8 September 2017, 2:00 pm EST

    Hello,


     


    Thanks for further explanation of the request.


    I have added this as a feature request in our system (Number 27020).


     


    Regards,


    Amit

  • Replied 8 September 2017, 2:00 pm EST

    Hello,


    Is there any kind of absolute limit for MaxLength? Also, is there any kind of performance hit if you set the MaxLength property to a large value? We have an app that displays ViewDefinitions from Sql Server and some of them can be quite lengthy. If one exists, I'd like to set the MaxLength property very high in this case, if I can do it without a performance penalty. Thanks in advance for any ideas and/or suggestions!

  • Replied 8 September 2017, 2:00 pm EST

    Hello Deepak,


    Thanks for the response! I had checked the help before I posted. Unfortunately, I don't have time to do any extensive testing right now on this issue. Can I set the MaxLength to 4000 or 5000 without impacting performance. As noted in an earlier portion of this thread, we are displaying Sql Server View definitions in the column. As you may know, they can sometimes be pretty involved and quite long. If you could check with someone in the development area, it would be appreciated. The total number of rows will be fairly small, probably 100-200, somewhere in there.

  • Replied 8 September 2017, 2:00 pm EST

    Thanks for the explanation! That's why I love your forums, explanations that help you broaden your understanding for the next time also.
  • Replied 8 September 2017, 2:00 pm EST

    j2associates,


    Setting MaxLength to a large number should not impact performance.  MaxLength limits the amount of text that the end user can enter into a cell.  MaxLength does not affect the amount of memory used to store a specific string value.  For example, if Value = "abc" then only enough memory to store "abc" is used regardless of whether MaxLength = 255 or MaxLength = 5000.

  • Replied 8 September 2017, 2:00 pm EST

    Thanks Scott!
  • Replied 8 September 2017, 2:00 pm EST

    Hello,

    From the documentation this is what i Found about "MaxLength property of a Cell as TextCellType :

     

    Default Value


    The default value is 255, which specifies that 255
    characters can be typed in a text cell when in edit mode.

    Remarks:



    This is the number of characters that can be typed, not the number that can
    be displayed. If you enter text by setting the Text property for a cell, this
    property does not limit those characters, only what can be typed by a user when
    in edit mode.

    I don't think it should create any performance issues when the 'MaxLength' is set to a higher number, you may test the same and feel free to share your observations with us.

     

    Thanks,

     

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels