Professional VSTO 2005 - Visual Studio 2005 Tools For Office (2006) [eng]

Kathy, thanks for being there for me all the time.

Boobie tube, you aren’t old enough to read this yet, but I love you.

Acknowledgments

Thanks to the staff of Wiley for believing in this project and having the discipline to see it through. Thanks to my family for putting up with the anxious moments and erratic behavior. It was worth it. Thanks to the One for the energy to pull this one off.

Contents

Acknowledgments	ix
Introduction	xvii

Chapter 1: Visual Studio Tools for Office	1
What’s New in VSTO?	1
VSTO Architecture	2
User Interface	3
Client Interface	3
Server Component	3
The VSTO Package	4
About Microsoft Office PIAs	5
System Requirements	6
Alternatives to the VSTO Office Systems	7
VBA	7
Office Web Components	8
Excel COM Interop Libraries	8
Third Party Products	9
Disadvantages of VSTO	9
.NET Framework Required	9
Security	9
Performance	10
VSTO Automation	10
Office XML Schemas	11
Installation and deployment	11
Creating VSTO Projects	13
VSTO Installation Issues	16
Summary	17

Chapter 2: Excel Automation	19
Excel Data Manipulation	19
Design-Time Data Loads	20
Loading Files at Runtime	21

Application Object Manipulation	30
Workbook Manipulation	31
Worksheet Manipulation	32

Contents

Excel Range Manipulation	34
Working with Named Ranges	35
Working with Cells	37
Working with Unions	38
Working with Intersections	39
Working with Offsets	40
Data Formatting and Presentation	42
Considerations for Excel Automation	44
Excel Case Study — Time Sheet Software	45
Summary	52

Chapter 3: Advanced Excel Automation	55
VSTO Security	55
Workbook Password Protection	55
Worksheet Security	57
Protection through Hidden Worksheets	60
Protecting Ranges	61
VSTO Security through the .NET Framework	62
Working with Excel Formulas	66
Using Formulas at Design Time	66
Using Formulas at Runtime	68
Working with the WorksheetFunctions Method	69
Excel Spreadsheet Error Values	69
Responding to Events	71
Working with Workbook Controls	74
The Range Control	75
The List Control	76
The Actions Pane	78
Printing Workbook Data	80
Excel Toolbar Customization	82
Excel Menu Customization	85
VSTO and Web services	89
Excel Server Automation	95
Excel COM Add-Ins	101
Summary	104

Chapter 4: Word Automation	107
Key Application Objects in Word	107
ThisApplication Instance	108
Working with the Range Object	109
Working with the Bookmark Object	112

xii

	Contents
Working with the Selection Object	114
VSTO Table Manipulation	115
Working with Documents	119
Manipulating Office Documents	120
Working with Word Templates	123
Toolbar Customization	126
Menu Customization	131
Working with Other Office Applications	134
A PowerPoint Automation Example	134
An Office Automation Executable Example	137
A Smart Tag Example	139
Manipulating Office Controls	143
Word Event Model	147
Printing Documents from Word	149
Considerations for Word Development	149
PIAs and RCWs for Office Word	149
Assemblies and Deployment	150
Custom Actions Panes	150
Summary	151
Chapter 5: Outlook Automation	153
Configuring VSTO Outlook	154
Key Outlook Objects	157
What Is MAPI?	157
Application Object	158
Explorer Object	158
Inspector Object	159
Email Integration	161
Creating Emails	161
Manipulating Email Messages	163
Appointments and Meetings	165
Creating and Scheduling Appointments	165
Deleting Appointments	170
Creating Meetings	172
Creating and Scheduling Outlook Items	173
Folder Manipulation	176
Address Book Manipulation	177
Events	180
Data Manipulation	183
Searching for Data	184
Advanced Data Search	187
Object Model Guard	190

xiii

Contents

Working with Office Controls	191
Toolbar Customization	192
Menu Customization	195
Integrating Windows Applications with Outlook	198
Summary	206

Chapter 6: The Charting Tool	209
Design-Time Charting	209
Chart Wizard	210
Charting Nomenclature	211
Creating VSTO Charts	219
Stand-Alone Charts	219
Embedded Charts	220
Loading Charts with Data	221
Essential Chart Object Hierarchy	226
Series Manipulation	226
Axes and Scaling	226
Tick Marks	228
Titles and Captions	232
Chart Groups	234
Formatting Chart Data	236
Font Object Customizations	237
Number Format Customization	238
Chart Surface Customization	240
Chart Legend Manipulation	242
Analysing Data	245
Trending through Trend Lines	246
Internally Supported Trendlines	246
Custom Trendlines	248
Error Bar Analysis	250
Chart Customization through Events	251
Advanced Chart Manipulation	254
Charts with Multiple Series	254
Combination Charts	256
Perspectives and 3-D Drawing	258
Chart Point Customization	260
Adding Objects to the Chart Surface	265
Charts with Special Needs	268
Chart Limitations	271
Summary	272

xiv

	Contents
Chapter 7: Pivot Table Automation	273
Design-Time Pivoting	274
VSTO PivotTable Wizard	275
Pivoting and Drilling	277
Pivot Table Terminology	278
PivotCache Object	279
PivotData Objects	279
Pivot Axis	279
Pivot Fields	280
Pivot Cell Object	280
Pivot Labels	281
Creating Pivot Tables through Code	281
Formatting Pivot Data	285
Font Object Customizations	285
Label Format Customization	286
Style Object Customization	287
Data Formatting with NumberFormats	289
Pivot Axis Iteration	290
Pivot Filtering	292
Adding Miscellaneous Columns	293
Pivot Table Events	294
Creating Pivot Charts	295
Pivot Table Limitations	297
Summary	298
Index	299

xv

Introduction

Visual Studio Tools for Office (VSTO) grew out of a need to write enterprise software based on Office technology. The book chooses to satisfy this need from a practical perspective. Therefore, the book is focused on delivering practical solutions for those seeking to port existing functionality to VSTO. Where necessary, however, the book injects a healthy dose of theory so that developers can learn to think and feel in this new technology.

The book is especially written for enterprise developers, the VBA or COM Interop savvy—who need to leverage the power and productivity of VSTO today. .NET developers seeking to explore VSTO as a potential solution will also find ample material that suits their needs. Even if you are not at all familiar with VSTO, the book is designed to build your knowledge in this new technology, working first with simple concepts on up to more involved strategies and solutions for enterprise software.

Who This Book Is For

This book is written for developers who are considering adopting VSTO as an enterprise solution. The book assumes that you are familiar with object-oriented concepts. The book also assumes that you are familiar with Visual Studio .NET. However, knowledge of Visual Studio .NET is not necessarily a requirement.

For the most part, the technical language in the book is mild to moderate. Concepts are presented from a coding perspective targeting intermediate to advanced developers. The material is presented in such a way that it serves to encourage non-.NET programmers. The language and focus of the examples are not intimidating and have been specifically refined to encourage learning.

The book adopts a code-first approach to satisfy VBA and COM Interop developers. The code to implement common programming tasks possesses a premium above the theory behind the requirement. For the most part, developers seeking to port functionality from VBA to VSTO are less interested in the why and more interested in the how. This book caters wholly to this school of thought.

If you find yourself already comfortable with core concepts, then you may benefit from reading the latter chapters. Even if you are a seasoned developer, you will still find a lot of useful techniques and strategies for solving enterprise-level software problems from an Office perspective.

What This Book Covers

This book covers VSTO 2005 in detail. The approach focuses on the major components that form the suite. One exception is that InfoPath is not covered. All other components benefit from an exploration of the key objects that are most likely to be used in common programming scenarios. Once the basics are out of the way, the material covers common programming requirements from a practical perspective.