Accessing Data from Excel within Spreadsheet Workbook

[Patgat]

This is probably a very trivial issue, but - just starting with UI for Winforms - I am unable to access the data apparently loaded in the workbook below  (what I am trying to do is to load an excel file in a Worksheet to use it in a datatable)

When I run this, it goes well, but the only output displayed by the Debug.Writeline instruction is :

 "> Telerik.Windows.Documents.Spreadsheet.Model.RangePropertyValue`1[Telerik.Windows.Documents.Spreadsheet.Model.ICellValue]"

 

Private Sub RadButton1_Click(sender As Object, e As EventArgs) Handles RadButton1.Click

        Dim fileName As String = "C:\Users\patrick\testfile.xlsx"
        Dim MyWorkbook As New Telerik.Windows.Documents.Spreadsheet.Model.Workbook
        Dim WorkbookFormatProvider As New XlsxFormatProvider()
        Dim MyInput As New FileStream(fileName, FileMode.Open)
        MyWorkbook = WorkbookFormatProvider.Import(MyInput)
   
        Dim MySheet As Worksheet = MyWorkbook.Sheets.ActiveSheet
        RadSpreadsheet1 = MyWorkbook.Sheets

        Dim Res As String = MySheet.Cells(1, 1).GetValue().ToString
        Debug.WriteLine(" > " & Res)
       
    End Sub

 

Can someone be kind to tell me what I do wrong ?

Thanks

(using Win 11, VS 2019, DEvCraft UI)

[Svilen]

Hello, Patrick,

My name is Svilen, and I'd be glad to help out with this question!

Getting the value of a cell can be done in several ways.

1. By using the GetValue().Value.RawValue property of the cell. This works for both values and formulas. In the example below, we create a workbook from scratch and add a worksheet. Afterward, we assign the formula =SUM(A2, 3) to cell A1 and the value of 10 to A2:

Dim workbook As New Workbook()
Dim worksheet As Worksheet = workbook.Worksheets.Add()

worksheet.Cells(0, 0).SetValue("=SUM(A2, 3)") ' cell A1, equal to cell A2+3 or 13
worksheet.Cells(1, 0).SetValue(10) ' cell A2

Dim rawValueA1 = worksheet.Cells(0, 0).GetValue().Value.RawValue ' returns the string "=SUM(A2, 3)"
Dim rawValueA2 = worksheet.Cells(1, 0).GetValue().Value.RawValue ' returns the string "10"

Please note that this method returns a string, instead of their evaluated result. Shared below, is a second method of getting both the evaluated result and the formula as a string.

2. This code uses the first two cells created previously:

Dim cellValue As ICellValue = worksheet.Cells(0, 0).GetValue().Value 'This interface is implemented by the five supported Cell Value types.

Dim format As CellValueFormat = worksheet.Cells(0, 0).GetFormat().Value ' A specific data format is required, in order to evaluate the resultValue of a formula. In this case we can use the one present in the cell itself.

Dim valueAsString As String = cellValue.GetValueAsString(format) ' returns the string "=SUM(A2, 3)"
Dim resultValue As String = cellValue.GetResultValueAsString(format) ' returns the string "13"

In both cases, one needs to convert the results to the appropriate data type.

3. Another method of getting the evaluated formula result is listed below:

Dim formulaValue As FormulaCellValue = TryCast(cellValue, FormulaCellValue) 'This cast allows the use of the GetResultValueAsCellValue() method
If formulaValue IsNot Nothing Then
    Dim numberCellValue As NumberCellValue = TryCast(formulaValue.GetResultValueAsCellValue(), NumberCellValue) ' This cast allows access to the Value property of the specific CellValue. Please make sure to check the appropriate CellValueType needed before casting.
    Dim result As Double = numberCellValue.Value ' returns double 13
End If

You can read more about the supported Cell Values and their properties here - SpreadProcessing - Cell Value Types. Since you also mentioned you'd be using a Datatable, here is an article on using the DataTableFormatProvider, which you might find useful. Both articles include code examples in C# only, so I am also sharing a neat tool we have that converts C# to VB and vice versa, found here.

I hope this helps. Should you have any other questions do not hesitate to ask. Have a great day!

Regards,
Svilen
Progress Telerik

[Patgat]

Thanks Svilen, it works  perfectly :)

Patrick

[Svilen]

Hey, Patrick,

Glad I could help! Please reach out to us again should you need help again.

Have a great day.