/
SearchActions.vb
56 lines (47 loc) · 2.21 KB
/
SearchActions.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
Imports DevExpress.Spreadsheet
Imports System
Imports System.Collections.Generic
Imports System.Drawing
Namespace SpreadsheetControl_API
Friend Class SearchActions
#Region "Actions"
Public Shared SimpleSearchAction As Action(Of IWorkbook) = AddressOf SimpleSearchValue
Public Shared AdvancedSearchAction As Action(Of IWorkbook) = AddressOf AdvancedSearchValue
#End Region
Private Shared Sub SimpleSearchValue(ByVal workbook As IWorkbook)
' #Region "#SimpleSearch"
workbook.LoadDocument("Documents\ExpenseReport.xlsx")
workbook.Calculate()
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Find and highlight cells containing the word "holiday".
Dim searchResult As IEnumerable(Of Cell) = worksheet.Search("holiday")
For Each cell As Cell In searchResult
cell.Fill.BackgroundColor = Color.LightGreen
Next cell
' #End Region ' #SimpleSearch
' Add a note.
worksheet("E1").Value = "Find the word ""holiday"" in the expense report"
End Sub
Private Shared Sub AdvancedSearchValue(ByVal workbook As IWorkbook)
' #Region "#AdvancedSearch"
workbook.LoadDocument("Documents\ExpenseReport.xlsx")
workbook.Calculate()
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Specify the search term.
Dim searchString As String = Date.Today.ToString("d")
' Specify search options.
Dim options As New SearchOptions()
options.SearchBy = SearchBy.Columns
options.SearchIn = SearchIn.Values
options.MatchEntireCellContents = True
' Find all cells containing today's date and paint them light-green.
Dim searchResult As IEnumerable(Of Cell) = worksheet.Search(searchString, options)
For Each cell As Cell In searchResult
cell.Fill.BackgroundColor = Color.LightGreen
Next cell
' #End Region ' #AdvancedSearch
' Add a note.
worksheet("E1").Value = "Find today's date in the expense report"
End Sub
End Class
End Namespace