/
Form1.vb
199 lines (172 loc) · 10.5 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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
#Region "#usings"
Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Export
#End Region ' #usings
Imports System
Imports System.Data
Imports System.Windows.Forms
Imports DevExpress.Skins
Imports DevExpress.XtraEditors
Namespace ExportToDataTableExample
Partial Public Class Form1
Inherits DevExpress.XtraBars.Ribbon.RibbonForm
Public Sub New()
InitializeComponent()
spreadsheetControl1.LoadDocument("TopTradingPartners.xlsx")
ribbonControl1.SelectedPage = exportDataExampleRibbonPage
End Sub
Private Sub barButtonItemRangeToDataTable_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barButtonItemRangeToDataTable.ItemClick
If barCheckItemStopEmptyRow.Checked Then
ExportSelectionStopOnEmptyRow()
Return
End If
' #Region "#SimpleDataExport"
Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet
Dim range As CellRange = worksheet.Selection
Dim rangeHasHeaders As Boolean = Me.barCheckItemHasHeaders1.Checked
' Create a data table with column names obtained from the first row in a range if it has headers.
' Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
Dim dataTable As DataTable = worksheet.CreateDataTable(range, rangeHasHeaders)
'Validate cell value types. If cell value types in a column are different, the column values are exported as text.
For col As Integer = 0 To range.ColumnCount - 1
Dim cellType As CellValueType = range(0, col).Value.Type
For r As Integer = 1 To range.RowCount - 1
If cellType <> range(r, col).Value.Type Then
dataTable.Columns(col).DataType = GetType(String)
Exit For
End If
Next r
Next col
' Create the exporter that obtains data from the specified range,
' skips the header row (if required) and populates the previously created data table.
Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders)
' Handle value conversion errors.
AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError
' Perform the export.
exporter.Export()
' #End Region ' #SimpleDataExport
' A custom method that displays the resulting data table.
ShowResult(dataTable)
End Sub
Private Sub ExportSelectionStopOnEmptyRow()
' #Region "#StopExportOnEmptyRow"
Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet
Dim range As CellRange = worksheet.Selection
' Determine whether the first row in a range contains headers.
Dim rangeHasHeaders As Boolean = Me.barCheckItemHasHeaders1.Checked
' Determine whether an empty row must stop conversion.
Dim stopOnEmptyRow As Boolean = barCheckItemStopEmptyRow.Checked
' Create a data table with column names obtained from the first row in a range if it has headers.
' Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
Dim dataTable As DataTable = worksheet.CreateDataTable(range, rangeHasHeaders)
' Create the exporter that obtains data from the specified range,
' skips the header row (if required) and populates the previously created data table.
Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders)
' Handle value conversion errors.
AddHandler exporter.CellValueConversionError, Sub(sender,args) args.Action = DataTableExporterAction.Continue
If stopOnEmptyRow Then
exporter.Options.SkipEmptyRows = False
' Handle empty row.
AddHandler exporter.ProcessEmptyRow, Sub(sender, args) args.Action = DataTableExporterAction.Stop
End If
' Perform the export.
exporter.Export()
' #End Region ' #StopExportOnEmptyRow
' A custom method that displays the resulting data table.
ShowResult(dataTable)
End Sub
Private Sub barButtonItemUseExporterOptions_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barButtonItemUseExporterOptions.ItemClick
' #Region "#DataExportWithOptions"
Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
Dim range As CellRange = worksheet.Tables(0).Range
' Create a data table with column names obtained from the first row in a range.
' Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
Dim dataTable As DataTable = worksheet.CreateDataTable(range, True)
' Create the exporter that obtains data from the specified range which has a header row and populates the previously created data table.
Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, True)
' Handle value conversion errors.
AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError
' Specify exporter options.
exporter.Options.ConvertEmptyCells = True
exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0
exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = barCheckItemSkipErrors.Checked
' Perform the export.
exporter.Export()
' #End Region ' #DataExportWithOptions
' A custom method that displays the resulting data table.
ShowResult(dataTable)
End Sub
#Region "#DataExportWithCustomConverter"
Private Sub barButtonItemUseCustomConverter_ItemClick(ByVal sender As Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles barButtonItemUseCustomConverter.ItemClick
Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
Dim range As CellRange = worksheet.Tables(0).Range
' Create a data table with column names obtained from the first row in a range.
' Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
Dim dataTable As DataTable = worksheet.CreateDataTable(range, True)
' Change the data type of the "As Of" column to text.
dataTable.Columns("As Of").DataType = System.Type.GetType("System.String")
' Create the exporter that obtains data from the specified range and populates the specified data table.
Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, True)
' Handle value conversion errors.
AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError
' Specify a custom converter for the "As Of" column.
Dim toDateStringConverter As New DateTimeToStringConverter()
exporter.Options.CustomConverters.Add("As Of", toDateStringConverter)
' Set the export value for empty cell.
toDateStringConverter.EmptyCellValue = "N/A"
' Specify that empty cells and cells with errors should be processed.
exporter.Options.ConvertEmptyCells = True
exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = False
' Perform the export.
exporter.Export()
' A custom method that displays the resulting data table.
ShowResult(dataTable)
End Sub
' A custom converter that converts DateTime values to "Month-Year" text strings.
Private Class DateTimeToStringConverter
Implements ICellValueToColumnTypeConverter
Public Property SkipErrorValues() As Boolean
Public Property EmptyCellValue() As CellValue Implements ICellValueToColumnTypeConverter.EmptyCellValue
Public Function Convert(ByVal readOnlyCell As Cell, ByVal cellValue As CellValue, ByVal dataColumnType As Type, <System.Runtime.InteropServices.Out()> ByRef result As Object) As ConversionResult Implements ICellValueToColumnTypeConverter.Convert
result = DBNull.Value
Dim converted As ConversionResult = ConversionResult.Success
If cellValue.IsEmpty Then
result = EmptyCellValue
Return converted
End If
If cellValue.IsError Then
' You can return an error, subsequently the exporter throws an exception if the CellValueConversionError event is unhandled.
'return SkipErrorValues ? ConversionResult.Success : ConversionResult.Error;
result = "N/A"
Return ConversionResult.Success
End If
result = String.Format("{0:MMMM-yyyy}", cellValue.DateTimeValue)
Return converted
End Function
End Class
#End Region ' #DataExportWithCustomConverter
#Region "#CellValueConversionErrorHandler"
Private Sub exporter_CellValueConversionError(ByVal sender As Object, ByVal e As CellValueConversionErrorEventArgs)
MessageBox.Show("Error in cell " & e.Cell.GetReferenceA1())
e.DataTableValue = Nothing
e.Action = DataTableExporterAction.Continue
End Sub
#End Region ' #CellValueConversionErrorHandler
#Region "#ShowResultForm"
Private Function ShowResult(ByVal result As DataTable) As Form
Using newForm As New XtraForm()
newForm.Width = 600
newForm.Height = 300
Dim grid As New DevExpress.XtraGrid.GridControl()
grid.Dock = DockStyle.Fill
grid.DataSource = result
newForm.Controls.Add(grid)
grid.ForceInitialize()
CType(grid.FocusedView, DevExpress.XtraGrid.Views.Grid.GridView).OptionsView.ShowGroupPanel = False
newForm.ShowDialog(Me)
Return newForm
End Using
End Function
#End Region ' #ShowResultForm
End Class
End Namespace