. NET implementation of Office Excel custom formulas are widely used in reports and data analysis

In the functional point of management software development, a considerable part of the functionality is to interact with Excel data to produce Excel data reports. If the data calculation method of an Excel report is regular, you can resolve it by customizing the formula. For example, the common balance sheet, the profit statement, take the number is very regular.

Excel DNA is a set of Excel custom formula engines implemented with the. NET Framework, and many Excel financial reports on the Internet are based on this technology. Excel DNA fully open source code, can download its full source code and example project, the address is https://exceldna.codeplex.com

Develop a custom Formula 1 create a user-defined formula (Visual Basic)

Create a Test.dna text file with Notepad, and refer to the following code example to implement a simple addition formula.

  <DnaLibrary>
    <! [cdata[
         Public Module Myfunctions
            Function Addthem (x, y)
                Addthem = x + y
            End Function
        End Module
    ]]>
    </DnaLibrary>

Open Excel, enter Formula =addthem (4,2), and you can see that the result of Excel's calculated formula is 6.

2 Creating a custom formula (C #)

To create a Test.dna text file, refer to the example code shown below to implement a formula that adds a string to a double-type numeric value.

  <dnalibrary language= "CS" >
    <! [cdata[
        using Exceldna.integration;
                 Public class Myfunctions
                {
                        [Excelfunction (description= "Joins a string to anumber", category= "My functions")]
                         Public Static string Jointhem (stringdouble val)
                        {
                                return str + val;
                        }
                }
    ]]>
    </DnaLibrary>

You can open Excel and enter the formula =jointhem ("James", 1983) to see the actual results of the operation.

3 passed. NET assemblies to create custom formulas

The Visual basic,c# are all. NET first-class citizens, in their familiar language to create a class library project Testlib, refer to the following code example, the implementation of two double-precision numerical formula.

using Exceldna.integration;
 Public class Myfunctions
{
        [Excelfunction (description= "multiplies-numbers", category= "useful Functions")]
         Public Static Double Multiplythem (doubledouble y)
        {
                return x * y;
        }
}

To create a Test.dna text file, add the following text snippet to declare the formula you created earlier.

<DnaLibrary>
    <externallibrary path= "TestLib.dll"/>
</DnaLibrary>
Open Excel, enter the formula =multiplythem (2,3) or =multiplythem (2; 3), and see that the result is 5.

If. NET assembly to. NET 4.0, you will need to modify the text content of the Test.dna slightly, referring to the following example.

<dnalibrary runtimeversion= "v4.0" >
      <externallibrary path= "TestLib.dll"/>
</DnaLibrary>

Deploying Deployment

Copy a file exceldna.xll to the desired directory, rename it to the desired name, such as the above Test.xll, and put the above Test.dna file in the same file. Double-clicking the xll file opens the Excel program,

Prompt for security claims, select Enable this add-on for this session.

You can then use the custom formula to open or create a new Excel file, enter a formula, and then execute the formula after the carriage return.

Testclasslibrary the source code for the method of customizing formulas in the assembly. You must reference the assembly ExcelDna.Integration.dll to compile.

[Excelfunction (description= "My first Excel-dna function")]
 Public Static string Myfirstfunction (string name)
{
    return ("" + name);
}

The contents of the Test.dna file are as follows, simply declaring the method in the assembly above.

< dnalibrary Name="First add-in"runtimeversion="v4.0">
  < externallibrary Path="TestClassLibrary.dll"/>
</ dnalibrary >

Packing Packing

Note that at deployment time, both the DNA file and the Xll file must be present, and the names of the two files are identical. Excel DNA supports the merging of these two files into a single file.

Run the program Exceldna\distribution\exceldnapack.exe and pass in the parameter Test.dna. The following commands are examples of:

ExcelDnaPack.exe, Test.dna

After execution, a test-packed.xll file is generated, which can be copied to other computers for direct execution. The Test.dna and TestLibrary.dll files are already included in the TEST-PACKED.XLL, and the runtime is actively loaded.

Aside from the complex visual Studio Tools for Office, with these few days of simple examples, I think readers should be able to quickly get started and efficiently author custom formulas in Excel.

Interface (Ribbon,panel) and COM server support

Excel DNA supports creating a custom panel, adding a WinForms custom control, inheriting from System.Windows.Forms.UserControl, and then adding the following code call.

CustomTaskPane MYCTP = Customtaskpanefactory.createcustomtaskpane (typeof(MyUserControl), MyTitle

More information can be found in the download source code package, with detailed examples and documentation.

. NET implementation of Office Excel custom formulas are widely used in reports and data analysis