/
FormulaActions.vb
272 lines (218 loc) · 10.9 KB
/
FormulaActions.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
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
Imports System
Imports DevExpress.Spreadsheet
Imports System.Drawing
Namespace SpreadsheetControl_API
Public NotInheritable Class FormulaActions
Private Sub New()
End Sub
#Region "Actions"
Public Shared UseConstantsAndCalculationOperatorsInFormulasAction As Action(Of IWorkbook) = AddressOf UseConstantsAndCalculationOperatorsInFormulas
Public Shared R1C1ReferencesInFormulassAction As Action(Of IWorkbook) = AddressOf R1C1ReferencesInFormulas
Public Shared UseNamesInFormulasAction As Action(Of IWorkbook) = AddressOf UseNamesInFormulas
Public Shared CreateNamedFormulasAction As Action(Of IWorkbook) = AddressOf CreateNamedFormulas
Public Shared UseFunctionsInFormulasAction As Action(Of IWorkbook) = AddressOf UseFunctionsInFormulas
Public Shared CreateSharedAndArrayFormulasAction As Action(Of IWorkbook) = AddressOf CreateSharedAndArrayFormulas
#End Region
Private Shared Sub UseConstantsAndCalculationOperatorsInFormulas(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets(0)
worksheet.Cells("A1").Value = "Formula"
worksheet.Cells("B1").Value = "Value"
worksheet.Range("A1:B1").FillColor = Color.LightGray
worksheet.Range("A1:B2").Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
worksheet.Cells("A2").Value = "'= (1+5)*6"
' #Region "#ConstantsAndCalculationOperators"
' Use constants and calculation operators in a formula.
workbook.Worksheets(0).Cells("B2").Formula = "= (1+5)*6"
' #End Region ' #ConstantsAndCalculationOperators
Finally
workbook.EndUpdate()
End Try
End Sub
Private Shared Sub R1C1ReferencesInFormulas(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Provide static data.
worksheet.Cells("A1").Value = "Data"
worksheet.Range("A2:A11").Formula = "=ROW() - 1"
worksheet.Cells("B1").Value = "Cell Reference Style"
worksheet.Cells("B2").Value = "Relative R1C1 Cell Reference"
worksheet.Cells("B3").Value = "Absolute R1C1 Cell Reference"
worksheet.Cells("C1").Value = "Formula"
worksheet.Cells("C2").Value = "'=SUM(RC[-3]:R[9]C[-3])"
worksheet.Cells("C3").Value = "'=SUM(R2C1:R11C1)"
worksheet.Cells("D1").Value = "Value"
worksheet.Range("A1:D1").AutoFitColumns()
worksheet.Range("A1:D1").FillColor = Color.LightGray
worksheet.Range("A1:D11").Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
' #Region "#R1C1ReferencesInFormulas"
' Switch on the R1C1 reference style in a workbook.
workbook.DocumentSettings.R1C1ReferenceStyle = True
' Specify a formula with relative R1C1 references in cell D2
' to add values contained in cells A2 through A11.
worksheet.Cells("D2").Formula = "=SUM(RC[-3]:R[9]C[-3])"
' Specify a formula with absolute R1C1 references
' to add values contained in cells A2 through A11.
worksheet.Cells("D3").Formula = "=SUM(R2C1:R11C1)"
' #End Region ' #R1C1ReferencesInFormulas
Finally
workbook.EndUpdate()
End Try
End Sub
Private Shared Sub UseNamesInFormulas(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim dataRangeHeader As CellRange = worksheet.Range("A1:C1")
worksheet.MergeCells(dataRangeHeader)
dataRangeHeader.Value = "myRange:"
dataRangeHeader.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
dataRangeHeader.FillColor = Color.LightGray
worksheet.Range("A2:C5").Value = 2
worksheet.Range("A2:C5").Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
worksheet.Range("A2:C5").Borders.SetOutsideBorders(Color.LightBlue, BorderLineStyle.Medium)
Dim sumHeader As CellRange = worksheet.Range("E1:F1")
worksheet.MergeCells(sumHeader)
sumHeader.Value = "Sum:"
sumHeader.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
sumHeader.FillColor = Color.LightGray
worksheet.Range("E2:F2").ColumnWidthInCharacters = 15
worksheet.Cells("E2").Value = "Formula:"
worksheet.Cells("E3").Value = "Value:"
worksheet.Cells("F2").Value = "'= SUM(myRange)"
' #Region "#NamesInFormulas"
' Access the "A2:C5" range of cells in the worksheet.
Dim range As CellRange = worksheet.Range("A2:C5")
' Specify the name for the created range.
range.Name = "myRange"
' Create a formula that sums up the values of all cells included in the specified named range.
worksheet.Cells("F3").Formula = "= SUM(myRange)"
' #End Region ' #NamesInFormulas
Finally
workbook.EndUpdate()
End Try
End Sub
Private Shared Sub CreateNamedFormulas(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
workbook.Worksheets(0).Cells("A1").Value = 2
workbook.Worksheets(0).Cells("B2").Value = 3
workbook.Worksheets(0).Cells("C3").Value = 4
workbook.Worksheets(1).Range("A1:C1").FillColor = Color.LightGray
workbook.Worksheets(1).Range("A1:C1").ColumnWidthInCharacters = 25
workbook.Worksheets(1).Cells("A1").Value = "Formula Name"
workbook.Worksheets(1).Cells("B1").Value = "Formula"
workbook.Worksheets(1).Cells("C1").Value = "Formula Result"
workbook.Worksheets(1).Cells("A2").Value = "Range_Sum"
workbook.Worksheets(1).Cells("A3").Value = "Range_DoubleSum"
workbook.Worksheets(1).Cells("A4").Value = "-"
workbook.Worksheets(1).Cells("B2").Value = "'=SUM(Sheet1!$A$1:$C$3)"
workbook.Worksheets(1).Cells("B3").Value = "'=2*Sheet1!Range_Sum"
workbook.Worksheets(1).Cells("B4").Value = "'=Range_DoubleSum + 100"
' #Region "#NamedFormulas"
Dim worksheet1 As Worksheet = workbook.Worksheets("Sheet1")
Dim worksheet2 As Worksheet = workbook.Worksheets("Sheet2")
' Create a name for a formula that sums up the values of all cells included in the "A1:C3" range of the "Sheet1" worksheet.
' The scope of this name will be limited by the "Sheet1" worksheet.
worksheet1.DefinedNames.Add("Range_Sum", "=SUM(Sheet1!$A$1:$C$3)")
' Create a name for a formula that doubles the value resulting from the "Range_Sum" named formula and
' make this name available within the entire workbook.
workbook.DefinedNames.Add("Range_DoubleSum", "=2*Sheet1!Range_Sum")
' Create formulas that use other formulas with the specified names.
worksheet2.Cells("C2").Formula = "=Sheet1!Range_Sum"
worksheet2.Cells("C3").Formula = "=Range_DoubleSum"
worksheet2.Cells("C4").Formula = "=Range_DoubleSum + 100"
' #End Region ' #NamedFormulas
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets("Sheet2")
Finally
workbook.EndUpdate()
End Try
End Sub
Private Shared Sub UseFunctionsInFormulas(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Provide static data.
worksheet.Cells("A1").Value = "Data"
worksheet.Cells("A2").Value = 15
worksheet.Range("A3:A5").Value = 3
worksheet.Cells("A6").Value = 20
worksheet.Cells("A7").Value = 15.12345
worksheet.Cells("B1").ColumnWidthInCharacters = 30
worksheet.Cells("B1").Value = "Formula String"
worksheet.Cells("B2").Value = "'=IF(A2<10, ""Normal"", ""Excess"")"
worksheet.Cells("B3").Value = "'=AVERAGE(A2:A7)"
worksheet.Cells("B4").Value = "'=SUM(A3:A5,A6,100)"
worksheet.Cells("B5").Value = "'=ROUND(SUM(A6,A7),2)"
worksheet.Cells("B6").Value = "'=Today()"
worksheet.Cells("B7").Value = "'=UPPER(""formula"")"
worksheet.Cells("C1").Value = "Formula"
worksheet.Range("A1:C1").FillColor = Color.LightGray
worksheet.Range("A1:C7").Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left
' #Region "#FunctionsInFormulas"
' If the number in cell A2 is less than 10, the formula returns "Normal"
' and this text is displayed in cell C2. Otherwise, cell C2 displays "Excess".
worksheet.Cells("C2").Formula = "=IF(A2<10, ""Normal"", ""Excess"")"
' Calculate the average value for cell values within the "A2:A7" range.
worksheet.Cells("C3").Formula = "=AVERAGE(A2:A7)"
' Add the values contained in cells A3 through A5, add the value contained in cell A6,
' and add 100 to that result.
worksheet.Cells("C4").Formula = "=SUM(A3:A5,A6,100)"
' Use a nested function in a formula.
' Round the sum of the values contained in cells A6 and A7 to two decimal places.
worksheet.Cells("C5").Formula = "=ROUND(SUM(A6,A7),2)"
' Add the current date to cell C6.
worksheet.Cells("C6").Formula = "=Today()"
worksheet.Cells("C6").NumberFormat = "m/d/yy"
' Convert the specified text to uppercase.
worksheet.Cells("C7").Formula = "=UPPER(""formula"")"
' #End Region ' #FunctionsInFormulas
Finally
workbook.EndUpdate()
End Try
End Sub
Private Shared Sub CreateSharedAndArrayFormulas(ByVal workbook As IWorkbook)
workbook.BeginUpdate()
Try
Dim worksheet As Worksheet = workbook.Worksheets(0)
worksheet.Range("A1:D1").ColumnWidthInCharacters = 10
worksheet.Range("A1:D1").Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center
worksheet.Range("A1:D1").FillColor = Color.LightGray
worksheet.MergeCells(worksheet.Range("A1:B1"))
worksheet.Range("A1:B1").Value = "Use Shared Formulas:"
worksheet.MergeCells(worksheet.Range("C1:D1"))
worksheet.Range("C1:D1").Value = "Use Array Formulas:"
' #Region "#SharedFormulas"
worksheet.Cells("A2").Value = 1
' Use the shared formula in the "A3:A11" range of cells.
worksheet.Range("A3:A11").Formula = "=SUM(A2+1)"
' Use the shared formula in the "B2:B11" range of cells.
worksheet.Range("B2:B11").Formula = "=A2+2"
' #End Region ' #SharedFormulas
' #Region "#ArrayFormulas"
' Create an array formula that multiplies values contained in the cell range A2 through A11
' by the corresponding cells in the range B2 through B11,
' and displays the results in cells C2 through C11.
worksheet.Range.FromLTRB(2,1,2,10).ArrayFormula = "=A2:A11*B2:B11"
' Create an array formula that multiplies values contained in the cell range C2 through C11 by 2
' and displays the results in cells D2 through D11.
worksheet.Range("D2:D11").ArrayFormula = "=C2:C11*2"
' Create an array formula that multiplies values contained in the cell range B2 through D11,
' adds the results, and displays the total sum in cell D12.
worksheet.Cells("D12").ArrayFormula = "=SUM(B2:B11*C2:C11*D2:D11)"
' Re-dimension an array formula range:
' delete the array formula and create a new range with the same formula.
If worksheet.Cells("C13").HasArrayFormula Then
Dim af As String = worksheet.Cells("C13").ArrayFormula
worksheet.Cells("C13").GetArrayFormulaRange().ArrayFormula = String.Empty
worksheet.Range.FromLTRB(2,1,2,10).ArrayFormula = af
End If
' #End Region ' #ArrayFormulas
Finally
workbook.EndUpdate()
End Try
End Sub
End Class
End Namespace