DEV390
Extending Word and Excel with
Visual Studio Tools for Office
Ken Getz
Senior Consultant
MCW Technologies, LLC
Me.About
Senior Consultant with
MCW Technologies, LLC
Microsoft Regional Director, SoCal
Technical editor for Access-VB-SQL
Advisor (www.advisor.com)
Author of several developer’s books on
ASP.NET, VB, Access, and VBA
ASP.NET, VB.NET, ADO.NET, VB6 and Access video
training for AppDev
(www.appdev.com)
www.mcwtech.com
www.developershandbook.com
Agenda
Review Visual Studio Tools for Office
Calling a Web Service
Word and SQL Server
Creating an Excel Chart
Working with Menus
Using Office Controls
Language Issues
Simple Deployment Issues
Visual Studio Tools for Office
Extends .NET development to Microsoft
Office 2003
Facilitates smart client application
development with Office 2003 and the .NET
Framework
Visual Studio Tools for Office
Developers using VS .NET 2003 can write
code to run in reaction to Microsoft Word
2003 and Microsoft Excel 2003 events
Use Visual Basic .NET or Visual C# .NET
Code can run in Word and Excel
Not a replacement for VBA
Existing event code in VBA?
Order of event handling with managed code
non-deterministic
Call a Web Service
Suppose you have a Web service that
returns a DataSet
Need to display data in Excel
No simple solution for working with
DataSet in Excel
Can easily retrieve and display data using
Visual Studio Tools
Dem
o
Set Up the Demo
Create new Excel project
Add Web Reference
http://www.mcwtech.com/
NorthwindData/Products.asmx
Rename to NorthwindData
Retrieve list of average sales
Fill range with results
Format range
Notice Imports statement
Dem
o
Excel Objects
ThisApplication, ThisWorkbook
Excel.Range
Offset
CurrentRegion
AutoFormat
Font
Columns
Note use of enumerations
Intellisense doesn’t do the job
How Does It Work?
Developer creates assembly, sets
document properties, perhaps deploys
assembly to centralized location
End user opens document
Office uses document properties to locate and
download assembly
.NET security verifies assembly is OK to run
Code Loading Process
User opens
document
Custom
Props?
Yes
Load CLR
Trusted
doc?
No
Yes
Fail
Hook up
events
Yes
Trusted
code?
Download
assembly
Fail
No
STOP
STOP
Create
AppDomain Fail
STOP
New / Open
event fires
No
Unload
AppDomain
Fail
Word and SQL Server
Create new Word project
Add Web Reference
Create Table
Add data to cells within table
Dem
o
Word Objects
ThisApplication, ThisDocument
Word.Range
InsertBefore, InsertParagraphAfter
Font
Word.Table
Cell
Creating Excel Chart
Chart object in Excel completely
programmable
Can embed on existing sheet, or can create
separate Chart sheet
Worksheet and Chart both inherit from Sheet
base class
Cast Sheet object as correct type as needed
Example creates chart on new sheet
Creating a Chart
Create new chart (on its own sheet)
Use Excel macro recorder to create code
Sometimes it’s just easier!
Requires cleanup and modification
Add code to reset workbook
So you can run the demo more than once
Dem
o
Working with Menus
Office CommandBar object model
required
Somewhat obtuse
Collection of menu controls of various types
Must know name of top-level menu
Use tools within application to find this
Wouldn’t it be better to run sample code
from menu, rather than on open?
Creating Menus
Retrieve reference to top-level menu
Call Controls.Add method to add new
CommandBarControl
Cast as CommandBarPopup
Call Controls.Add method to add
CommandBarButton
Must remove the menu when workbook
closes
Hook up Click event somehow
Dem
o
Using Office Controls
Add COM reference to MS Forms 2.0
Watch out for multiple instances in registry
Select first reference
Import Microsoft.Vbe.Interop.Forms or
MSForms
Depending on which instance you select
Call FindControl method (provided by
template) to retrieve reference
Hook up event handler
Dem
o
Dialect Dyslexia
Normally, language doesn’t matter
Visual Basic .NET is easier in this context
C# developers at a slight disadvantage
Problem areas:
Parameterized properties
Optional parameters
Passing parameters (Word likes ref)
Variant members
Late binding
Parameterized Properties
If a property accepts a parameter
And the parameter isn’t an indexer
C# can’t handle it
PIAs provide accessor methods, instead:
Offset becomes get_Offset
Range becomes get_Range
Value property in Excel accepts parameter
C# developers can use Value2 property
Parameterized Properties
' Visual Basic
rng.Offset(0, col).Value = _
dt.Columns(col).ColumnName
// C#
rng.get_Offset(0, col).Value2 =
dt.Columns[col].ColumnName;
Optional Parameters
VBA (and VB.NET) support optional
parameters
Must be at the end of the parameter list
C# does not, so must include all
parameters
Can pass Type.Missing to indicate missing
value
Some methods have a large number of
optional parameters (up to 30 or more!)
Optional Parameters
' Visual Basic
rng.AutoFormat( _
Excel.XlRangeAutoFormat. _
xlRangeAutoFormatColor2)
// C#
rng.AutoFormat( _
Excel.XlRangeAutoFormat. _
xlRangeAutoFormatColor2,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Passing Parameters
Most of Word’s methods require
parameters passed by reference
VBA and VB.NET make this transparent
C# requires the ref keyword
No literal values!
Passing Parameters
' Visual Basic
ThisDocument.Range.Delete()
// C#
Object start = Type.Missing;
Object end = Type.Missing;
Object unit = Type.Missing;
Object count = Type.Missing;
ThisDocument.Range(ref start, ref end).
Delete(ref unit, ref count);
Variant Members
Some (not many) Word and Excel
properties return/accept Variant values
C# can’t handle Variants
Look for accessor methods, or replacement
properties
Variant Members
' Visual Basic
tbl.Style = "Table Grid 8"
// C#
Object style = "Table Grid 8";
tbl.set_Style(ref style);
Late Binding
Word dialogs use late binding (“expando
interfaces”) to determine members at
runtime
C# can’t support late binding
Workarounds include using Reflection to
determine available members, or using
the Visual Basic .NET CallByName
method
No example in this demo
Doesn’t come up often
Deployment Issues
Generally a simple process
Need to be aware of security issues
Policies control behavior
See separate discussion of deployment and
security
What about simply handing a document
and its assembly to another developer?
Even that requires some knowledge
Debugging and Paths
Need to ensure that path to Excel or Word
has been set correctly
Watch for path locations
Check Project Properties|Configuration
Properties|Debugging
Fix path to host application
Finding the Documents
Properties window for project
Assembly Link Location
Office Document
Class Names Can Kill
What if you change the default
namespace for your project?
Or the default class name?
Your code won’t run
See DescriptionAttribute in class
<Assembly: DescriptionAttribute( _
"OfficeStartupClass, Version=1.0, " & _
"Class=ExcelProject1.OfficeCodeBehind")>
Loading the Assembly
Creating the project adds two document
properties to the Office doc
_AssemblyLocation0
_AssemblyName0
These must match reality
And That’s Still Not Enough
It’s managed code
Won’t run unless you grant permissions
Visual Studio .NET sets up permissions
for project in its original folder
What if you move the project?
Or hand the project to another developer?
What if you want to run this session’s
samples?
Create a Policy
Add a code access group
Create a new policy?
Easiest, for development purposes, to
allow all code within a folder to run
Specify c:\path\* as the location
Specify “FullTrust”
This isn’t a good idea on users’ machines
Works fine for development
But Wait, There’s More!
Just barely scratched the surface
Word/Excel object models are huge
Provide a ton of useful functionality
Language issues?
C# users will want to create “wrapper”
classes
What about no-touch deployment?
Granting permissions for download code?
Requires an entire separate session
Next Steps
Get Visual Studio Tools for Office
msdn.microsoft.com/vstudio/office
Install Visual Studio .NET 2003 and then
full installation of Office 2003
Try out some of the walkthroughs/demos
Take existing Office automation code and
try recreating in managed code
Community Resources
White papers:
Visual Studio Tools for Office:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/odc_vsto2003_ta/html/
VSTOIntro.asp?frame=true
Introduction to the Word Object Model:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/odc_vsto2003_ta/html/
WordObject.asp?frame=true
Introduction to the Excel Object Model:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/odc_vsto2003_ta/html/
ExcelObj.asp?frame=true
Ask The Experts
Get Your Questions Answered
Stop by Ask the Experts area
16:00 to 17:00, Wednesday
Community Resources
Community Resources
http://www.microsoft.com/communities/default.mspx
Most Valuable Professional (MVP)
http://www.mvp.support.microsoft.com/
Newsgroups
Converse online with Microsoft Newsgroups, including Worldwide
http://www.microsoft.com/communities/newsgroups/default.mspx
User Groups
Meet and learn with your peers
http://www.microsoft.com/communities/usergroups/default.mspx
evaluations
© 2003 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Descargar

Slide 1