VSTO Learning Notes (11) Developing Excel 2010 64-bit custom formulas

Original: VSTO Learning Note (11) Developing Excel 2010 64-bit custom formulas

Excel contains a lot of formulas, such as math, date, text, logic and other formulas, very convenient, can be flexible and fast processing of data to achieve the effect we want. Excel's built-in formula is strong, but in the ERP system, often need to do a lot of business calculations, such as receiving and depositing accounts, designated year, month, Department of consumption query, etc., are required from the database query, and business closely connected. Just relying on Excel's built-in formulas is not enough, and you need to add custom formulas for these business calculations.

Test code Download

All test codes in this series are tested on visual Studio Ultimate + Office Professional plus x64

Reprint Please specify source: http://www.cnblogs.com/brooks-dotnet/archive/2011/01/16/1936871.html

Exel allows us to create user-defined functions and use them in Excel formulas.

First, VBA

Without using VSTO, you can write Excel custom functions with only the classic VBA. VBA I have not studied (contact development later, missed the VBA era), interested friends please self-search, there are many examples.

Second, XLL

XLL is an Excel add-in file, a standard DLL that is primarily developed using C + +. For this reason, Microsoft provides the Excel XLL SDK, which can be downloaded here .

Although Microsoft officially does not recommend the use. NET to develop XLL, but someone has published an open source project Exceldnaon CodePlex, which can be used. NET to develop XLL. However, some people on the internet say that seemingly do not support Excel x64, pending verification.

Third, XLW

XLW (A Wrapper for the Excel API) is a C + + language-to-Excel Developer Toolkit (SDK) package that simplifies the way C + + programs manipulate Excel documents, and XLW4.0 already supports Excel 2007. Using XLW, you can easily use C + + to extend Excel functionality. First it provides a template generator XlwTemplateExtractor.exe, can generate the corresponding project file, and then have an interface generator InterfaceGenerator.exe to generate the corresponding interface wrapper file ( XlwWrapper.cpp), which greatly reduces the use of the Excel SDK only to complete the relevant code required for XLL development, reducing the development difficulty, to avoid some errors.

Official note only support to Excel 2007, estimated that Excel x64 No, look forward to the next update.

Iv.. NET

The advent of VSTO has greatly increased the efficiency of developers, and we can enjoy the quick and easy benefits of modern development tools. With COM, you can use managed code to develop custom formulas for Excel.

4.1. Open VS2010 and create a new class library project:

4.2. Add COM-related features:

[Guid("dde8aa59-9860-44b3-b1af-923abb4a8eee")]

[classinterface(classinterfacetype. AutoDual)]

[comvisible(true)]

Note to generate a GUID:

Here I use the LINQPad, a lightweight IDE that supports C #, vb.net, Linq, F #, and SQL statements, and is ideal for testing small amounts of code.

4.3. Write a simple function that returns two integers and:

Public int Anothersumfunc (int A, int b)

{

return A + b;

}

4.4. Add COM registration and anti-registration functions:

[comregisterfunction]

Public Static void Registerfunction (type type)

{

Registry. Classesroot.createsubkey (Getsubkeyname (Type, "Programmable"));

var key = Registry. Classesroot.opensubkey (Getsubkeyname (Type, "InprocServer32"), true);

Key. SetValue ("", environment. Systemdirectory + @ "\mscoree.dll", registryvaluekind. String);

}

[comunregisterfunction]

Public Static void Unregisterfunction (type type)

{

Registry. Classesroot.deletesubkey (Getsubkeyname (Type, "Programmable"), false);

}

Private Static string Getsubkeyname (type type, string subkeyname)

{

var s = new system.text. StringBuilder ();

S.append (@ "clsid\{");

S.append (type. Guid. ToString (). ToUpper ());

S.append (@ "}\");

S.append (SubkeyName);

return s.tostring ();

}

The format of the two functions is fixed, in order to add the necessary key values to the registry.

4.5, modify the project properties, cancel COM registration:

4.6, compile the project.

4.7, 32-bit Excel can select the above check box to register automatically, starting from Excel x64, we need to register the DLL manually.

Under DOS input: cd/d%windir%\microsoft.net\framework64\v4.0.30319

4.8. Use RegAsm to register our DLL:

The warning appears because it is unsigned because the test is not signed and must be signed if it is a formal deployment.

The following description is from MSDN, original address:http://msdn.microsoft.com/zh-cn/library/tzat5yw6 (v=vs.100). aspx.

Regeasm.exe is an assembly registration tool that Regeasm.exe reads the metadata in the assembly and adds the required items to the registry. The registry allows COM clients to create. NET Framework classes transparently. Once a class is registered, any COM client program can use it as if the class were a COM class. Class is only registered once when the assembly is installed. A class instance in an assembly cannot be created from COM until it is actually registered.

RegAsm assemblyfile [Options]

AssemblyFile

The assembly to register with COM.

Option description

/codebase

Create a Codebase entry in the registry. The Codebase entry specifies the file path of an assembly that is not installed into the global assembly cache. You should not specify this option if you subsequently install an assembly that is being registered in the global assembly cache. The assemblyfile parameter specified with the/codebase option must be an assembly with a strong name.

/registered

Specifies that this tool will only reference type libraries that have already been registered.

/asmpath:directory

Specifies the directory that contains the assembly reference. Must be used with the/regfile option.

/nologo

The Microsoft startup title is deselected.

/regfile [: RegFile]

Generates the specified. reg file for the assembly that contains the required registry key. Specifies that this option does not change the registry. This option cannot be used with the/u option or the/tlb option.

/silent or/s

Cancels the display of the success message.

/tlb [: Typelibfile]

Generates a type library from the specified assembly that contains the definition of the accessible type defined in the assembly.

/unregister or/U

Unregisters a class found in assemblyfile that can be created. Omitting this option will cause Regasm.exe to be registered in the assembly to create the class.

/verbose

Specifies the verbose mode, and when specified with the/TLB option, displays a list of all referenced assemblies for which the type library needs to be generated.

/? or/help

Displays the command syntax and options for the tool.

4.9. Activate the formula in Excel

Open an Excel file that, if the Developer tab is not already displayed, can be displayed by ribbon– right-"Custom the Ribbon" – "Checked Developer":

In the Developer tab, click "Add-ins":

Click "Automation" to select the Custom function we have written:

Select the function and click "OK":

Back in Excel, enter in a cell's publicity column: =anothersumfunc, enter, and you can see the results we expect: 3

4.10. Delete Custom Functions

If the function we do not want, to delete what to do? Simply unregister the DLL in CMD:

4.11. Debugging a Custom function

In the Debug tab of the project properties, set the extension launcher to the path of Excel x64:

F5 runs, Excel is automatically opened, and when we enter the custom function, the breakpoint is automatically entered:

In addition, you can debug by attaching an Excel process.

Summary:

The first 64-bit version of Office 2010 has brought us joy, as well as an increase in version maintenance costs. As a result, the backward compatibility of VSTO programs is especially important, and Excel custom functions are just the tip of the iceberg. The next chapters discuss compatibility issues with the Office plug-in, and the solution to move from VBA to VSTO.

VSTO Learning Notes (11) Developing Excel 2010 64-bit custom formulas