-
Notifications
You must be signed in to change notification settings - Fork 3
/
Form1.vb
143 lines (119 loc) · 7.32 KB
/
Form1.vb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
Imports DevExpress.Spreadsheet
Imports System
Imports System.Data
Imports System.Linq
Imports System.Windows.Forms
Imports System.Collections.Generic
Namespace DataBindingToListExample
Partial Public Class Form1
Inherits DevExpress.XtraBars.Ribbon.RibbonForm
Public Sub New()
InitializeComponent()
#Region "#DataBindingErrorHandling"
AddHandler spreadsheetControl1.Document.Worksheets(0).DataBindings.Error, AddressOf DataBindings_Error
#End Region ' #DataBindingErrorHandling
End Sub
#Region "#DataBindingErrorHandler"
Private Sub DataBindings_Error(ByVal sender As Object, ByVal e As DataBindingErrorEventArgs)
MessageBox.Show(String.Format("Error at worksheet.Rows[{0}]." & ControlChars.Lf & " The error is : {1}", e.RowIndex, e.ErrorType.ToString()), "Binding Error")
End Sub
#End Region ' #DataBindingErrorHandler
Private Sub barBtnBindWeather_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barBtnBindWeatherListToTable.ItemClick
BindWeatherReportToTable(MyWeatherReportSource.Data, spreadsheetControl1.ActiveCell)
End Sub
Private Sub barBtnWeatherBindingList_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barBtnWeatherBindingListToRange.ItemClick
BindWeatherReportToRange(MyWeatherReportSource.DataAsBindingList, spreadsheetControl1.ActiveCell)
End Sub
Private Sub barBtnBindWeatherListToTableUsingTableCollection_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barBtnBindWeatherListToTableUsingTablecCollection.ItemClick
BindWeatherReportToFixedTable(MyWeatherReportSource.Data, spreadsheetControl1.Selection)
End Sub
#Region "#BindingListBoundToRange"
Private Sub BindWeatherReportToRange(ByVal weatherDatasource As Object, ByVal bindingRange As CellRange)
Dim sheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
' Check for range conflicts.
Dim dataBindingConflicts = sheet.DataBindings.Where(
Function(binding)
Return (binding.Range.RightColumnIndex >= bindingRange.LeftColumnIndex) OrElse (binding.Range.BottomRowIndex >= bindingRange.TopRowIndex)
End Function)
If dataBindingConflicts.Count() > 0 Then
MessageBox.Show("Cannot bind the range to data." & ControlChars.CrLf & "The worksheet contains other binding ranges which may conflict.", "Range Conflict")
Return
End If
' Specify the binding options.
Dim dsOptions As New ExternalDataSourceOptions()
dsOptions.ImportHeaders = True
dsOptions.CellValueConverter = New MyWeatherConverter()
dsOptions.SkipHiddenRows = True
' Bind the data source to the worksheet range.
Dim sheetDataBinding As WorksheetDataBinding = sheet.DataBindings.BindToDataSource(weatherDatasource, bindingRange, dsOptions)
' Adjust the column width.
sheetDataBinding.Range.AutoFitColumns()
End Sub
#End Region ' #BindingListBoundToRange
#Region "#ListBoundToTable"
Private Sub BindWeatherReportToTable(ByVal weatherDatasource As Object, ByVal bindingRange As CellRange)
Dim sheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
' Remove all data bindings bound to the specified data source.
sheet.DataBindings.Remove(weatherDatasource)
' Specify the binding options.
Dim dsOptions As New ExternalDataSourceOptions()
dsOptions.ImportHeaders = True
dsOptions.CellValueConverter = New MyWeatherConverter()
dsOptions.SkipHiddenRows = True
' Create a table and bind the data source to the table.
Try
Dim sheetDataBinding As WorksheetTableDataBinding = sheet.DataBindings.BindTableToDataSource(weatherDatasource, bindingRange, dsOptions)
sheetDataBinding.Table.Style = spreadsheetControl1.Document.TableStyles(BuiltInTableStyleId.TableStyleMedium14)
' Adjust the column width.
sheetDataBinding.Range.AutoFitColumns()
Catch e As Exception
MessageBox.Show(e.Message, "Binding Exception")
End Try
End Sub
#End Region ' #ListBoundToTable
#Region "#ListBoundToFixedTableUsingTableCollectionAdd"
Private Sub BindWeatherReportToFixedTable(ByVal weatherDatasource As Object, ByVal selection As CellRange)
Dim sheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
' Remove all data bindings bound to the specified data source.
sheet.DataBindings.Remove(weatherDatasource)
' Specify the binding options.
Dim dsOptions As New ExternalDataSourceOptions()
dsOptions.ImportHeaders = True
dsOptions.CellValueConverter = New MyWeatherConverter()
dsOptions.SkipHiddenRows = True
' Create a table and bind the data source to the table.
Try
Dim boundTable As Table = sheet.Tables.Add(weatherDatasource, selection, dsOptions)
boundTable.Style = spreadsheetControl1.Document.TableStyles(BuiltInTableStyleId.TableStyleMedium15)
' Adjust the column width.
boundTable.Range.AutoFitColumns()
Catch e As Exception
MessageBox.Show(e.Message, "Binding Exception")
End Try
End Sub
#End Region ' #ListBoundToFixedTableUsingTableCollectionAdd
Private Sub barBtnAddWeatherReport_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barBtnAddWeatherReport.ItemClick
MyWeatherReportSource.DataAsBindingList.Insert(1, New WeatherReport() With {.Date = New Date(1776, 2, 29), .Weather = Weather.Sunny, .HourlyReport = MyWeatherReportSource.GenerateRandomHourlyReport()})
End Sub
Private Sub barBtnUnbind_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barBtnUnbind.ItemClick
spreadsheetControl1.Document.Worksheets(0).DataBindings.Clear()
End Sub
Private Sub barBtnBindingInfo_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barBtnBindingInfo.ItemClick
#Region "#GetDataBindings"
Dim sheet As Worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet
Dim bindings = sheet.DataBindings.GetDataBindings(sheet.Selection)
Dim message As String = "No data bindings found"
If bindings.Count <> 0 Then
message = "The selected range contains the following data bindings:" & ControlChars.CrLf
For Each binding As WorksheetDataBinding In bindings
message &= String.Format("Binding {0}" & ControlChars.CrLf, binding.Range)
Next binding
End If
MessageBox.Show(message)
#End Region ' #GetDataBindings
End Sub
Private Sub btnReload_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnReload.ItemClick
MyWeatherReportSource.Reload()
End Sub
End Class
End Namespace