Creating a Test Document with Cached Data

1. Using VSTO, create an Excel workbook project called CachedDataSample.

2. In cell A1, type First Name, and in cell A2, type Last Name.

3. Select cell B1, and create a named range called FirstName. Select cell B2, and create a named range called LastName. The document should look like the one in Figure 13.2.

Figure 13.2. Cached data sample project

4. Right-click Sheetl.vb in Solution Explorer or Sheetl in the designer, and click View Code. Add the code in Listing 13.3 to the Sheetl class. This code adds public cached fields to class Sheetl.

Listing 13.3. Adding cached fields to the Sheetl class

Public CachedFirstName As String

Public CachedLastName As String

This action creates two public string fields. You add the Cached attribute to tell the VSTO runtime to cache these fields. It is important that you make these fields public. If you do not explicitly specify the visibility of the field, Visual Basic will mark them Private by default. The data will be cached only if it is marked Public. If it is not public, you may not see an exception message.

5. On Startup set the named range values to the values that are cached in the document. Add the code in Listing 13.4 to initialize the named ranges you created earlier.

Listing 13.4. Initializing named ranges with cached data values

Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup

' Set the FirstName and LastName to the value in the cache. FirstName.Value = CachedFirstName LastName.Value = CachedLastName

End Sub

6. Update the cached values when the named range changes. You will handle the Change event of the named range to update the value of the cached data fields. Add the code in Listing 13.5.

Listing 13.5. Updating cached data values on the named range Change event

Private Sub FirstName_Change(ByVal Target As _

Microsoft.Office.Interop.Excel.Range) Handles _ FirstName.Change

CachedFirstName = FirstName.Value

End Sub

Private Sub LastName_Change(ByVal Target As _

Microsoft.Office.Interop.Excel.Range) Handles _ LastName.Change

CachedLastName = LastName.Value

End Sub

7. Press F5 to run the solution. When the solution opens for the first time, the named ranges will be empty because the data cache is not yet set. Fill the named ranges with data. You can see the results in Figure 13.3.

IE Microsalt Excel - ExcelWorkbookl.xIs

jajxj

IS] File

Edit View Insert Format

Jools Data Team

Window

Help

-Sx

J ¡3iA QâlJiai^sË

a- *> •

' ft £

• il iü

!oo% . (gi

i L^ New List I ^ Get Work Items Publish H Refresh jf Configure List

FirstName ' fx Paul

A

B

c

D-

1

First Name:

Paul

2

Last Name:

Stubbs

3

4

5

6

7

8

9

10

11

\n A ► H [\sheetl / Sheet2 / Sheet3 /

'1

► ir

Ready

NUM

A

Figure 13.3. Create the cached data on first run by filling the named ranges.

Figure 13.3. Create the cached data on first run by filling the named ranges.

8. Save and close the document.

0 0

Post a comment