按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
Calculating an Average
The spreadsheet calculates the average of a set of numbers； and then subtracts the average
from each number。 The example demonstrates reading a plete spreadsheet to get a number
and reading individual elements to perform a calculation。
…………………………………………………………Page 331……………………………………………………………
CH AP T E R 1 1 ■ L E A R N IN G AB O U T 。 N E T G E N E R I CS 309
Let’s use the following numbers to calculate the average。
Dim items As Double（） = New Double（） ｛ 1。0； 2。0； 3。0 ｝
The average number is 2。0； and if you subtract the average from each number； you will get
the series –1； 0； and 1。
To make this work for a spreadsheet； the first step is to declare and then populate an
IWorksheet（Of BaseType） instance。 To instantiate an IWorksheet（Of BaseType） instance； you
use a factory that will instantiate the Worksheet（Of BaseType） class。 The code looks like this：
Dim sheetAverage As IWorksheet（Of Double） = _
SpreadsheetManager。CreateEmptyWorksheet（Of Double）（〃〃）
Dim items As Double（） = New Double（） ｛1； 2； 3｝
sheetAverage。Dimension（（items。Length ＋ 10）； 3）
For row = 0 To items。Length 1
sheetAverage。SetCellState（row； 0； items（row））
Next row
The worksheet is declared as being of type Double （IWorksheet（Of Double））； allowing you
to manage a Double value。 To populate sheetAverage； the numbers are iterated in a For loop
and assigned to the worksheet using the SetCellState（） method。 The method Dimension（） is
needed to create a fixed…length worksheet。
With the worksheet populated； to make sure everything looks right； you could call the method
ToString（） and see if all is as expected。
The next step is to assign the lambda expressions that will be used to calculate the average
and then the individual differences from the average。 When you assign a calculation to the work
sheet； you need to know whether the lambda expression will be stateful or stateless。 Remember
that lambda expressions have some state。 It is just a question of whether you want a shared
state lambda expression or an individual…state lambda expression。 In the case of the lambda
expressions for the average calculations； a shared state is acceptable。
To calculate the average； you use a technique where the average calculation is the last element
in the series of the array calculations。 Thus； when the average calculation is called； it knows
how many elements there are because of the row in which the average calculation is stored。
Function Average（ByVal worksheet As IWorksheet（Of Double）； _
ByVal cellRow As Integer； ByVal cellCol As Integer）
Dim runningTotal As Double = 0
Dim row As Integer
For row = 0 To cellRow 1
runningTotal = （runningTotal ＋ worksheet。GetCellState（row； 0））
Next row
Return （runningTotal / CDbl（cellRow））
End Function
。 。 。
sheetAverage。AssignCellCalculation（items。Length； 0； AddressOf Average）
In the example； the average is calculated by using the variable cellRow as a maximum row。
Every cell （GetCellState（）） before cellRow is added to a running total （runningTotal）； and then；
finally； an average is returned by dividing runningTotal by cellRow。
…………………………………………………………Page 332……………………………………………………………
310 CH AP T E R 1 1 ■ L E A R N I N G A B OU T 。 N E T G E N E R I CS
With the average calculated； the next step is to calculate the difference between the average
and the individual items。 The result will be stored in a column to the right of the item cell state。
This is done by subtracting the average calculation cell state from the item value； as follows：
For row = 0 To items。Length 1
sheetAverage。AssignCellCalculation（row； 1； _
Function（worksheet As IWorksheet（Of Double）； cellRow As Integer； _
cellCol As Integer）
（worksheet。GetCellState（cellRow； 0） _
worksheet。Calculate（items。Length； 0）））
Next row
The number of cell…state calculations depends on the count of numbers in items。 Each cell
calculation is assigned a locally declared lambda expression； meaning that the lambda expres
sions of all cell states will be identical and share the same state。 The only shared variable is
items。Length。 All the lambda expressions expect the same length； and so it is acceptable to
share this variable。 The average difference is calculated by calculating the average and then
subtracting it from the worksheet cell item value that is in the zeroth column。
Finally； when everything is assigned； you can call the worksheet。Calculate（） method to
calculate the average and difference from the average。
sheetAverage。Calculate（）
Console。WriteLine（sheetAverage。ToString（））
Understanding Why the Calculation Worked
The cell calculations work because the spreadsheet has the ability to track what has been calculated
and what has not been calculated。 In a typical spreadsheet； you can change one cell in a sheet
and have everything magically recalculate。 There is no such feature for this spreadsheet。 However；
this simpler spreadsheet version can make sure that when there are dependencies； they are not
calculated multiple times。
Look back at the source code to calculate the difference between the average and a number。
The only reason the calculation worked is that the cell that contained the average was called
using the Calculate（） method。 Had the GetCellState（） method been used； the average might
not have been cal