GetParseFormat retruns nothing for both ByRef String and IFormatProvider parameters

Posted by: dkimzey on 8 September 2017, 3:03 pm EST

  • Posted 8 September 2017, 3:03 pm EST

    I am trying to obtain information about a cell in an Excel spreadsheet loaded with the OpenExcel method.

    It appears that for Excel files all cell types will be returned by the GetCellType method as a GeneralCellType.  I would like to examine the FormatString, DateTimeFormat and NumberFormat properties of the GeneralCellType object to determine the possible cell types.

    If the contents of my spread control holds an Excel file, I type cast the return of GetCellType into a GeneralCellType and examine the FormatString, DateTimeFormat and NumberFormat properties to set the initial control values for a custom dialog similar to Excel's "Format Cells" dialog.

     I was trying a GetParseFormat to see if any additional information (or more specific information) could be obtained.  My call to GetParseFormat succeeds (does not throw any IndexOutOfRange exceptions) - but also returns nothing (or Null) for the two ByRef parameters.  My code to call GetParseInfo is below.  Is this syntax correct?

    Dim _numberFormatInfo As System.Globalization.NumberFormatInfo
    Dim _formatString As String = String.Empty

    _currentCellType = ActiveSheet.GetCellType(_row, _col)

    If (_isExcelFile = True) Then

    ' All Excel cell types are returned as General. - Cell type info is found in DateAndTimeFormat and NumberFormat...

    Dim _generalCellType As CellType.GeneralCellType = CType(_currentCellType, CellType.GeneralCellType)


    If (_generalCellType.NumberFormat IsNot Nothing) Then


            _generalCellType.GetParseFormat(_row, _col, _formatString, _numberFormatInfo)

        Catch ex As IndexOutOfRangeException

        End Try

    End If

  • Replied 8 September 2017, 3:03 pm EST


    The code that you are using is absolutely correct, but you are getting null for both the parameters i.e FormatString and FormatInfo which is an intended behavior because the celltype's GetParseFormat method returns the values for both these parameters when the cell is being edited. When the cell is not in edit mode, you can use the GetPasreMethod of the style model, to get the FormatString and FormatInfo. But in your case, since you are importing an Excel file which sets celltypes in Spread, using the Style model GetParseFormat method would not return you any values. You can use only the celltype GetParseFormat and that too only when the cell is edited.

    Hope it will help you. Please let me know if you have any queries further.


    Manpreet Kaur

Need extra support?

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

Learn More

Forum Channels