使用C/C++开发Excel Add-in：财务应用实战

4星 · 超过85%的资源需积分: 10 66 浏览量更新于2024-07-28 收藏 4.62MB PDF 举报

“Excel Add-in Development”是一本专注于C/C++编程语言在创建Excel Add-ins中的应用，特别是在金融领域的实践书籍。作者Steve Dalton提供了深入的指导，帮助读者提升Excel技能至进阶水平。本书主要知识点： 1. **Excel Add-in基础**：了解Excel Add-ins的基本概念，它们是如何扩展Excel功能的，以及为何使用C/C++进行开发。C/C++语言的低级特性使其成为创建高效且性能强大的Excel插件的理想选择。 2. **开发环境设置**：如何配置开发环境，包括安装必要的软件工具，如Visual Studio，以及设置用于Excel Add-in开发的项目模板。 3. **API交互**：学习如何使用Microsoft Office开发接口（如COM接口）与Excel进行通信，实现自定义功能和自动化任务。 4. **编程技术**：深入理解C/C++编程技巧，如内存管理、错误处理和多线程编程，这些都是开发可靠Excel插件的关键。 5. **函数和宏**：掌握创建自定义函数的方法，以及如何编写和使用VBA宏来增强Excel的脚本能力。 6. **用户界面设计**：设计和实现用户友好的Add-in界面，包括自定义菜单、工具栏和对话框，提高用户体验。 7. **数据处理和分析**：学习如何利用C/C++的强大计算能力处理大量数据，进行复杂的金融模型构建和数据分析。 8. **应用实例**：书中包含多个金融领域的应用案例，如财务建模、风险分析和投资组合优化，这些实例有助于读者将理论知识转化为实际操作。 9. **调试和测试**：了解有效的调试策略和测试方法，确保Add-in的稳定性和可靠性。 10. **发布和部署**：学习如何打包和分发Add-in，以便于用户安装和使用，包括考虑版本兼容性问题。 11. **知识产权和法律**：书中可能涉及版权和商标法的相关内容，提醒开发者尊重并遵守知识产权法规。这本书对于想要在Excel环境中使用C/C++开发专业应用程序，特别是金融领域从业者，提供了宝贵的资源和实践指导。通过学习，读者不仅可以提升Excel技能，还能掌握开发高效、定制化解决方案的能力。

Introduction

1.1 TYPOGRAPHICAL AND CODE CONVENTIONS USED

IN THIS BOOK

To distinguish between the text of the book, Visual Basic code, C/C++ code, and Excel

worksheet functions, formulae and cell references, the following fonts are used throughout:

Excel functions and formulae Arial

Windows application menus and control button text Arial

Visual Basic code Lucida Console

C/C++ code Courier

Directory paths, ﬁle names and ﬁle masks Courier

Passages of source code appear as boxed text in the appropriate font.

The spelling and grammar used throughout this book are British Isles English, with the

occasional Microsoft extension such as dialog.

Examples of non-VB code are mostly in C++-ﬂavoured C. That is, C written in C++

source modules so that some of the useful C++ features can be used including:

• the declaration of automatic variables anywhere in a function body;

• the use of the

bool data type with associated true and false values;

• the use of call-by-reference arguments;

• C++ style comments.

C functions and variables are written in lower case with underscores to improve readabil-

ity, for example,

c_thing. In the few places C++ classes are used, class and instance

names and member functions and variables are written in proper case, and in general,

without underscores, for example,

CppThing. Class member variables are preﬁxed with

‘

m_’ to clarify class body code. Beyond this, no coding standard or variable naming con-

vention is applied. Names of XLL functions, as registered with Excel, are generally in

proper case with no underlines, to distinguish them from Excel’s own uppercase function

names, for example,

MyCppFunction.

Where function names appear in the book text, they appear in the appropriate font

with trailing parentheses but, in general, without their arguments. For example, a C/C++

function is written as

c_function() or CppFunction() and an Excel worksheet

function is written as

Excel_Function(). VB functions may be written as VB_Function(),or

simply

VB_Function where the function takes no arguments, consistent with VB syntax.

Code examples mostly rely on the Standard C Library functions rather than, say, the

C++ Standard Template Library or other C++ language artefacts. Memory allocation

and release use

malloc(), calloc() and free(), rather than new and delete or

the Win32 global memory functions. (There are one or two exceptions to this.) This

is not because the choice of the C functions is considered better, but because it is a

2 Excel Add-in Development in C/C++

simple common denominator. It is assumed that any competent programmer can alter the

examples given to suit their own preferences. String manipulation is generally done with

the standard C library functions such as

strchr(), rather than the C++ String class.

(There is some discussion of

BSTR strings and the functions that handle them, where the

topic is interoperability of C/C++ DLLs and VB.)

The standard C library

sprintf() function is used for formatted output to string

buffers, despite the fact that it is not type-safe and risks buffer overrun. (The book avoids

the use of any other standard input/output routines.)

The object oriented features of C++ have mostly been restricted to two classes. The

ﬁrst is the

cpp_xloper, which wraps the basic Excel storage unit (the xloper)and

greatly simpliﬁes the use of the C API. The second is the

xlName which greatly simpliﬁes

the use of named ranges. (Strictly speaking, deﬁned names can refer to more than just

ranges of cells.) There are, of course, many places where an add-in programmer might

ﬁnd object-abstraction useful, or the functionality of the classes provided in this book

lacking; the choice of how to code your add-in is entirely yours.

C++ throw and catch exception handling are not used or discussed, although it is

expected that any competent C++ programmer might, quite rightly, want to use these.

Their omission is intended to keep the Excel-related points as the main focus.

Many other C++ features are avoided in order to make the code examples accessible

to those with little C++ experience; namespaces, class inheritance and friends, streams

and templates. These are all things that an experienced C++ programmer will be able to

include in their own code with no problem, and are not needed in order to address the

issues of interfacing with Excel.

The C++ terms member variable and member function, and their VB analogues prop-

erty and method, are generally used in the appropriate context, except where readability

is improved.

1.2 WHAT TOOLS AND RESOURCES ARE REQUIRED

TO WRITE ADD-INS

Licensed copies of a 32-bit version of Excel and a 32-bit Windows OS are both assumed.

(16-bit systems are not covered in this book). In addition, and depending on how and

what you want to develop, other software tools may be required, and are described in this

section. Table 1.1 summarises the resources needed for the various levels of capability,

starting with the simplest.

Table 1.1 Resources required for add-in development

What you want

to develop

Required resources Where to get them

VBA macros and add-ins VBA (for Excel) Supplied with Excel

Win32 DLLs whose

functions can be

accessed via VB

VBA

A compiler capable of

building a Win32 DLL from

the chosen source language

(which does not have to be

C or C++)

Supplied with Excel

Various commercial and

shareware/freeware

sources

Introduction 3

Table 1.1 (continued )

C/C++ Win32 DLLs

whose functions can be

accessed via VB and that

can control Excel using

OLE/COM Automation

VBA

A C/C++ compiler capable of

building Win32 DLLs, and

that has the necessary library

and header ﬁle resources for

OLE COM Automation

Supplied with Excel

Various commercial and

shareware/freeware

sources. Microsoft IDEs

provide these resources.

(See below for details.)

C/C++ Win32 DLLs that

can access the Excel

C API whose functions

can be accessed directly

by Excel without the use

of VBA.

A C/C++ compiler capable of

building Win32 DLLs.

The C API library and header

ﬁles.

A copy of the XLM (Excel 4

macro language) help ﬁle.

(Not strictly required but a

very useful resource.)

Various commercial and

shareware/freeware

sources.

Downloadable free from

Microsoft at the time of

writing. (See below for

details.) Static library

also shipped with

Excel.

.NET add-ins and

controllers.

Excel 2002 or later.

A C/C++/C# compiler

capable of building .NET

components for Microsoft

Ofﬁce applications.

At the time of writing, a good starting point for locating Microsoft downloads is

www.microsoft.com/downloads/search.asp

1.2.1 VBA macros and add-ins

VBA is supplied and installed as part of all 32-bit versions of Excel. If you only want

to write add-ins in VB, then that’s all you need. The fact that you are reading this book

already suggests you want to do more than just use VB.

1.2.2 C/C++ DLL add-ins

It is, of course, possible to create Win32 DLLs using a variety of languages other than C

and C++. You may, for example, be far more comfortable with Pascal. Provided that you

can create standard DLLs you can access the exposed functions in Excel via VB. If this

is all you want to be able to do, then all you need is a compiler for your chosen language

that can build DLLs.

Chapter 4 Creating a 32-bit Windows (Win32) DLL using Visual C++ 6.0 or Visual

Studio .NET, page 75, contains step-by-step examples of the use of Microsoft’s Visual

Studio C++ version 6.0 Standard Edition and Visual Studio C++ .NET 2003 integrated

development environments (IDEs). The examples demonstrate compiler and project set-

tings and show how to debug the DLL from within Excel. No prior knowledge of these

IDEs is required. (Standard Win32 DLLs are among the simplest things to create.) Other

IDEs, or even simple command-line compilers, could be used, although it is beyond the

scope of this book to provide examples or comparisons.

4 Excel Add-in Development in C/C++

1.2.3 C/C++ DLLs that can access the C API and XLL add-ins

If you want your DLL to be able to access the C API, then you need a C or C++ compiler,

as well as the C API library and header ﬁle. The C API functions and the deﬁnitions of the

data types that Excel uses are contained in the library and header ﬁles

xlcall32.lib

and xlcall.h. Both of these are contained in a sample project, downloadable from

Microsoft at the time of writing, free of charge, at download.microsoft.com/download/

platformsdk/sample27/1/NT4/EN-US/Frmwrk32.exe. It is also possible to link Excel’s

library in its DLL form,

xlcall32.dll, in your DLL project, removing the need to

obtain the static

.lib version. This ﬁle is created as part of a standard Excel installation.

Another approach is to create the

.lib ﬁle from the .dll ﬁle, as discussed in section 5.1.

This framework project is also included with Microsoft’s Excel 97 Developer’s Kit

(1997, Microsoft Press) on its accompanying CD ROM. The book contains a great

deal of useful reference data, and describes the framework project in detail, something

beyond the scope of this book. It is perhaps a little short on practical guidance, but

owning a copy is nevertheless recommended. At time of writing, this book is now out

of print, but still available on the Microsoft Developer Network (MSDN) website at

msdn.microsoft.com/library/default.asp?url =/library/ofﬁcedev/ofﬁce97/edkfrnt.htm

An XLL add-in is a DLL that exports a set of interface functions to help Excel load and

manage the add-in directly. These functions, in turn, need to be able to access Excel’s func-

tionality via the C API, if only to be able to register the exported functions and commands.

Only when registered can they be accessed directly from the worksheet (if functions) or via

menus and toolbars (if commands). The C API is based on the XLM (Excel 4 macro lan-

guage). This book provides guidance on the most relevant C API functions in Chapter 8.

However, for a full description of all the C API’s XLM equivalents you should ideally

have a copy of the XLM help ﬁle, which is typically named

Macrofun.hlp.This

is, at the time of writing, downloadable in the form of a self-extracting executable from

Microsoft at download.microsoft.com/download/excel97win/utility4/1/WIN98/EN-US/

Macrofun.exe.

1.2.4 C/C++/C# .NET add-ins

This book does not cover .NET and C#. These technologies are an important part of

Microsoft’s vision for the future. The resources required to apply these technologies are

Visual Studio .NET and a .NET-compatible version of Excel, i.e., Excel 2002 and later.

The principle purpose of this book is to bring the power of compiled C and C++ to Excel

users, rather than to be a manual for implementing these new technologies.

1.3 TO WHICH VERSIONS OF EXCEL DOES THIS

BOOK APPLY?

Table 1.2 shows the marketing names and the underlying version numbers to which this

book applies. Excel screenshots in this book (worksheets, dialogs, etc.) are all those of

Excel 2000. Most of the interface differences between versions 2000 and 2003 (the latest

at the time of writing) are quite superﬁcial. The workbooks on the CD ROM are Excel

2000 format. (Contact ccppaddin@eigensys.com if you require 97 format ﬁles.)

Introduction 5

Table 1.2 Excel version numbers

Product marketing name Version number

Excel 97 (SR-1, SR-2) 8

Excel 2000 9

Excel 2002 10

Excel 2003 11

1.4 ABOUT ADD-INS

An add-in is simply a code resource that can be attached to a standard application to

enhance its functionality. Excel is supplied with a number of add-ins that can be installed

according to the user’s preference and need. Some provide specialist functions not needed

by the average user, such as the Analysis ToolPak (sic), and some that provide complex

additional functionality such as the Solver add-in.

Add-ins come in two main ﬂavours: interpreted macros and compiled code resources.

Version 4 of Excel introduced macro sheets which could contain macros written in the

Excel macro language (XLM). These comprised columns of instructions and calculations

that either led to a result being returned to the caller, if functions, or that performed

some action such as formatting a cell, if commands. Macro sheets could be part of a

workbook or saved and loaded separately so as to be accessible to any workbook. Despite

their ﬂexibility they were relatively slow and did not promote sensible structured coding.

In fact they encouraged the exact opposite given that, for example, they could modify

themselves whilst executing.

Version 5 introduced Visual Basic worksheets. This enabled coding of functions and

commands as before but promoted better coding practices and made implementation of

algorithms from other languages easier. Excel 97 replaced these VB-sheets with Visual

Basic for Applications and the Visual Basic Editor (VBE) – a comprehensive IDE com-

plete with context-sensitive object-oriented help, pre-compiler, debugger and so on.

Macros, be they XLM or VB, are interpreted. When run, the interpreter reads each

line one-by-one, makes sense of it while checking for errors in syntax, compiles it and

only then executes the instructions. Despite the fact that VBA does some of this work in

advance, this is a slow process. The VBA approach avoids the need for tools to create fully

pre-compiled code making the creation of add-ins possible for the non-expert programmer.

VBA makes Excel application objects accessible and is therefore the obvious choice for

a host of user-deﬁned commands and functions where speed of development rather than

speed of execution is the prime concern. Additionally, Microsoft have not updated the C

API since the release of Excel 97 and only support XLM for backwards compatibility.

New functionality and objects added since this release are only available to applications

that can access Excel’s COM-exposed objects. This is not too serious as the type of

functionality added is that which it is most appropriate to access from VBA (or VB),

rather than via the C API, anyway.

The other main ﬂavour of add-in is the pre-compiled code resource which has none

of the execution overhead of interpreted languages and is therefore extremely fast by

comparison. The cost is the need to use and so understand, another development language,

and another compiler or IDE. In essence, this is no harder than using VBA and the VB

剩余416页未读，继续阅读

never112

粉丝: 1
资源: 3

使用C/C++开发Excel Add-in：财务应用实战

Microsoft Excel Add-In

Microsoft Excel Add-in （excel QC）

nitdmexcel_18-0-1.rar

Financial Applications Using Excel Add-in development in C/C++

Financial Applications Using Excel Add-in Development in C/C++

HP ALM Excel Add-in 安装与使用指南

Excel.Add.in.Development.in.C.and.C_in_excel_xll_

Exploring the Application and Advantages of kkfileview in Cross-platform Development

Add-in Express

Professional Excel Development (2nd Edition)

最新资源