Translation-exceldna Development Documentation

Reprinted from the Personal homepage

Objective

Translate open source project Exceldna Development documentation

Asynchronous processing

Exceldna supports two types of asynchronous functions:

• RTD, the function is applicable with Excel2003 and above, (RTD works when you use excelasyncutil.*)
• Local Excel Async function, using Excel2010 and above (when your function uses Excelasynchandle as the parameter and the return value is void)

The difference between the two ways

• The RTD function allows you to communicate with Excel when the function executes
• Local async functions, Excel only allows other parts of the workbook to be executed when the function has been executed. So in cell computing, you don't and can affect Excel.

One of the ways to use RTD is: Excelasyncutil.run. For ease of execution, Exceldna has internally defined the RTD service (details) that the RTD service allows Exceldna to notify Excel, such as whether the formula needs to be recalculated after the asynchronous task is completed.

The async functions are as follows:

• You define an asynchronous function in a formula.
• Excel needs to recalculate, calling asynchronous function execution Excelasyncutil.run
• Using Excelasyncutil.run to create a RTD theme (referencing the method name and in the information in the parameter, let Excelasyncutil.run as the first parameter)
• Excelasyncutil.run start asynchronous work and stay in touch with the RTD theme
• Excelasyncutil.run returns #n/a to the UDF when it is continuously calculated.
• Your UDF will return #n/a to an Excel workbook
• When your work is done, Exceldna will send an ID to let the RTD update
• When an error occurs on the RTD, Excel marks the cell
• When Excel recalculates, the cell is recalculated, and the UDF is called again
• UDF call Excelasyncutil.run is consistent with previous topic information
• Excelasyncutil.run finds the subject and the completed value stored in the method, it returns the value directly if it is not found #n/a
• The UDF function receives the result value and returns the value to the workbook
• Because Excelasyncutil.run cannot call the Excel RTD method again, the RTD theme empties the value inside Excel

Excelfunction Property

• Name
• Description
• Category
• HelpTopic
• IsVolatile( ! suffix)
• IsHidden
• IsExceptionSafe
• IsMacroType( # suffix)
• IsThreadSafe( $ suffix)
• IsClusterSafe( & suffix)
• ExplicitRegistration
• SuppressOverwriteError

Ismacrotype

Whether to use a macro type
When Ismacrotype=true, Exceldna registers the function and calls Xlfregister
Excel API Reference for xlfRegister . In detail, if Ismacrotype=true,exceldna will add "#" after Pxtypetext
This is described in the related documentation

In pxtypetext the last parameter plus #, give the function the same call license as a function in a macro

• The function gets the value of the cell that has not yet been evaluated in the recalculated loop
• This function can call a function in any of the XLM information, for example:Xlfgetcell

If the # symbol does not appear

• xlretuncalced error occurs when the result of a cell is not evaluated. Once the cell is evaluated, the current function is called again
• Calling a function other than Xlfcaller in XLM throws a xlretinvxlfn error

Some drawbacks of using ismacrotype=true

• If you do not add a "$" suffix, you cannot use multi-threading, even if it is marked Isthreadsafe=true
• If they contain at least one parameter that marks the type parameter of [Excelargument (allowreference=true)] , Excel automatically treats the function as unstable (even if the function is marked Isvolatile=false)

Looking further, I understand that during Excel calculations, these functions handle different threads, so you may expect to have some changes when the workbook is calculated, and I have not referenced nor rewritten it.

I recommend that you only set ismacrotype=true in an unexpected case, and when you're sure you need it and you know enough about it, you can upgrade

IsThreadSafe

If thread safety is set to true, it means that your function is safe for multithreaded recalculation, and if the "$" symbol is finally added to the registration string, the xlfregister can be called internally

Isclustersafe

If cluster security is set to true, it means that your function is safe in the cluster
Cluster Safe functions

Isexceptionsafe

Is unusually secure, when set to True, means that Excel should crash whenever an unknown exception occurs, and that the parameter is best ignored

Excelargumentattribute

• Name
• Description
• AllowReference

Excelcommandattribute

• Name
• Description(Unused)
• HelpTopic(Unused)
• ShortCut
• MenuName
• MenuText
• IsExceptionSafe
• ExplicitRegistration
• SuppressOverwriteError

Function Registration Default Registration

By default, all Exceldna function registration methods must be, in order to be public static able to access in the. DNA file, there are two properties below, you can put in the. DNA file for easy control of your registration.

Explicitexports

• If you just want to register a method and clear the sign that he is an Excel method or Excel command, you can in the. DNA file ifExplicitExports=‘true‘
  For example:

<ExternalLibrary Path="MyFunctions.dll" Pack="true" ExplicitExports="true" />

• These properties are valid in items in the. DNA file and in tags in the class library.

Explicit sign-up options

If your addin is explicitly registered (if you want to register an extension class library), you can add in the. DNA file, ExplicitRegisration=‘true‘ and in Exceldna, it will not automatically register any function, and your addin can be ExcelIntegration.RegisterDelegates(...) invoked,
ExplicitRegistrationOption to run allows an explicit method or class library to exit the default registration process, for example, a method or a class library has explicitly register , then call one of ExcelIntegration.RegisterXXX the methods

• In ExcelFunctionAttribute and ExcelCommandAttribute both valid
• Valid in the Project and in the. DNA file ExternalLibrary
• When ExcelIntegration.RegisterMethods or ExcelIntegration.RegisterDelegates called, these flags are removed before any property is called

Dynamic registration

Exceldna custom external class libraries, you can customize the method with the registration thread, the dynamic registration function, where the delegate is used ExcelIntegration.RegisterDelegates . Details

Data type

The following are the parameters of the function and the type of the return value

• Double
• String
• DateTime--Returns a double type to excel (perhaps it would be better to return the string directly)
• Double[]--if there is only one column, the column is taken, otherwise the row will be used
• double[,]
• Object
• Object[]--if there is only one column, the column is taken, otherwise the row will be used
• object[,]
• Boolean (BOOL)--Returns the Excel Boolean value (it would be better to return a string)
• Int32 (int)
• Int16 (short)
• UInt16 (ushort)
• Decimal

The parameter type passed into the function, allowing only the following parameters to be passed in:

• Double
• String
• Boolean
• ExcelDna.Integration.ExcelError
• ExcelDna.Integration.ExcelMissing
• ExcelDna.Integration.ExcelEmpty
• Object[,] containing an array with a mixture of the above types
• Excelreference--(only allowreference=true)

The function with the parameter type object[], object[,] will accept the above type, and the return type is as follows:

• Double
• String
• Datetime
• Boolean
• Double[]
• double[,]
• Object[]
• object[,]
• ExcelDna.Integration.ExcelError
• ExcelDna.Integration.ExcelMissing.Value//converted by Excel to BES 0.0
• ExcelDna.Integration.ExcelEmpty.Value//converted by Excel to BES 0.0
• Int32 (int)
• Int16 (short)
• UInt16 (ushort)
• Decimal
• otherwise return #VALUE! Error

Custom compilation output

When the Exceldna.addinNuGet package is installed in the project, some additional build configurations are already defined, and only copy the required. xll files to the output directory is required to create a separate package for the plug-in.

The installation package adds files to the project (Properties\exceldna.build.props). This file is used to customize which attachments are required. ExcelDna.Build.props allow configuration of content

  <!--Configuration properties for building. DNA files---<PropertyGroup> <!--enable/disabl E automatic generation of platform-specific versions of DNA files----<exceldnacreate32bitaddin condition= "' $ (exceldnacreate32bitaddin) ' = = ' ">true</ExcelDnaCreate32BitAddIn> <exceldnacreate64bitaddin Condition  = "' $ (exceldnacreate64bitaddin) ' = = '" >true</ExcelDnaCreate64BitAddIn> <!--Define the suffix used for Each platform-specific file e.g. Myaddin64.dna---<exceldna32bitaddinsuffix condition= "' $ (exceldna32bitaddin Suffix) ' = = ' ' "></ExcelDna32BitAddInSuffix> <exceldna64bitaddinsuffix condition=" ' $ (    Exceldna64bitaddinsuffix) ' = = ' ' >64</ExcelDna64BitAddInSuffix> </PropertyGroup> <!-- Configuration properties for packing. DNA files--<PropertyGroup> <!--enable/disable packing of. DNA files---<runexceldnapack condition= "' $ (runexcelDnapack) ' = = ' ' >true</RunExcelDnaPack> <!--Suffix used for packed. xll files e.g. myaddin-packed.xll --<exceldnapackxllsuffix condition= "' $ (exceldnapackxllsuffix) ' = = '" >-packed</exceldnapackxllsuffix > </PropertyGroup>

RELATED LINKS

Document Address

Translation-exceldna Development Documentation