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)
1 Answer, 1 is accepted
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 IfYou 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
Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.
Rank 2
Iron
Iron
Iron
Thanks Svilen, it works perfectly :)
Patrick
Glad I could help! Please reach out to us again should you need help again.
Have a great day.