Transcript Visual Studio Excel Development

Using and Extending Excel with
Visual Studio
A high level discussion of the main options
Simon Murphy
[email protected]
Developer – Codematic Ltd
www.codematic.net – Excel Development.
DDD
Agenda
•
•
•
•
•
•
Background
Patterns and technical options
VSTO demo
The main questions (how to choose)
Automation v COM Add-ins
Demos
– Com Add-in
– Automation Add-in
• Deployment
• Justification
• Summary & questions
DDD
Background
•
•
•
•
•
On-line confusion
No (easy to find) succinct info from MS
Mild panic after VB6 retirement (VB6 ~ VBA)
Few books (changing rapidly)
Why target Excel?
– 400 Million installations
– Very powerful, very programmable client
•
•
•
•
~
~
~
~
200 objects
80 events
200 enumerations
4,500 methods and properties
• Note: this presentation pretty much ignores VBA
DDD
Design Patterns
• Workbook with code behind
• Excel Add-in
– COM Add-in
– Automation Add-in
• Automation executable
DDD
Working with Excel - The options
•
•
•
•
VSTO (Visual Studio Tools for the Office System)
COM Add-ins
Automation Add-ins
Standard automation projects
– Open, Automate, use features and Close Excel
– Excel 97 maybe earlier?
• XLL Add-ins
– Native win32 DLLs (ie pretty much C++)
– Excel 4.0 maybe earlier?
– Still very relevant
• VBA Add-ins – Not discussed here
DDD
VSTO Demo
• VSTO (Visual Studio Tools for the Office
System)
– Special VS template projects that incorporate
strong office links.
– Closest replacement for workbooks with
embedded VBA.
– Excel 2003 professional or better
– VSTO 2003 US only VSTO 2005 any locale.
– Locale still very much an issue for other .net
developments
DDD
4 Big Questions
1.
2.
3.
4.
What version of Excel?
Function or Feature?
Application or Workbook Level?
Who initiates connection?
•
DDD
Or Hosted v None Hosted
1. Which Version of Excel
• If Excel 2003 all options are possible
• Anything less than 2003
(professional)
– VSTO (Visual Studio Tools for the Office
System) is not possible
• Anything less than Excel XP
– Automation Add-ins not possible
• Anything less than Excel 2000
– COM Add-ins not possible
• Total Office Installs – around 400M
Excel Versions ~
2003 – 15% - 60M
2002 – 35% - 140M
2000 – 40% - 160M
1997 – 10% - 40M
DDD
2. Function or Feature?
• Function (as in worksheet function)
– Automation add-in
• Visual Studio 6 (generally VB6.0)
• Visual Studio .net
• VSTO not appropriate
– XLL
• Best performance
• Feature
– COM Add-in
– VSTO
– Standard automation
DDD
3. Application or Workbook Level?
• Application
– COM Add-in
– Automation Add-in
– Standard automation project
• Workbook
– VSTO
– VBA
DDD
4. Who Initiates connection?
• Excel (hosted)
– VSTO
– COM Add-in
– Automation Add-in
• Your application (not hosted)
– Standard automation project
DDD
Demo
• COM Add-in – load up events
– Application level functionality
– Excel 2000 or better
– Visual Studio 5 >>
DDD
Automation v COM Add-in
• Automation Add-ins are COM add-ins that can be
called directly from a worksheet cell.
• COM Add-ins
– Must Implement IDTExtensibility2
– Generally load on Excel start up (can be changed)
• Automation Add-ins
– May Implement IDTExtensibility2 (discouraged)
– Loaded on demand only
DDD
Demo
• Automation Add-in – Worksheet formula
– Directly callable worksheet formulas
– Excel XP (2002) or better
– Visual Studio 5 >>
DDD
Development & Deployment
• Excel is written in unmanaged C with COM
interfaces for VBA and other apps.
• VBA/XLL has the easiest initial deployment
(updates are more complex though)
• VSTO has the most flexible and overall
easiest deployment (server or local)
• COM Add-ins & Automation Add-ins
– standard COM local registry issues.
– Should have a shim (Custom CLR loader)
DDD
COM Shims
• What are they?
– COM dll that loads Mscoree.dll which then
loads our managed component
– Excel>>COMSHIM>>Mscoree>>MyAddin.dll
• Why use them?
– Security (code signing)
– AppDomain Isolation
• Protect other apps from failures in our component
• Protect our component from other failures
DDD
Why bother?
• Why change from the current VBA / COM
approach?
– Deployment and maintenance easier
• 1 version
• Write controlled location
• Cleaner tiered architecture
– .net framework functionality
– Visual studio .net IDE better than VBAIDE
– Enterprise code reuse
– .net security may be better than macro
security?
DDD
When to bother?
• Note: my personal guess only!
• Mainstream in 2007!
• Excel 12 due mid/late 2006
– Excel 12 is a genuine improvement
• VSTO 3 will probably match that time scale
– Very likely to include more application level features
• Task panes, Add-ins, etc
• Approx 12 months to get reasonable uptake
• VBA will be looking very shabby by then
• VSTA (Visual Studio Tools for Applications) will have fully
arrived
• .net framework should have reached saturation level.
DDD
Resources
DDD
Summary
Technology Type
VSTO
COM Add-in
Automation Add-in
Win32 XLL
VBA
Standalone exe
Solution Type
Workbook
Application
With Code Level Feature
Behind
Custom
Worksheet
Function
Automation
Project








• VSTO for modern workbook based solutions
• COM Add-ins for application level features
• Automation Add-ins for worksheet functions
DDD

Finally
• Any questions?
• Simon Murphy
• [email protected]
• www.codematic.net – Excel Development.
DDD