.NET Objects in the Grid

This example demonstrates how to put a .NET object into the Resolver One grid.

Scenario

You are running an office supplies company, and the tax situation has become very complex. Instead of having a fixed tax rate of 17.5% on all goods, there is now a system so complex that you have had to purchase a third-party .NET component that calculates all of the different taxes on each product, based on the type of product and its net price. You need to break out each of the taxes into a separate column.

Exercise

Build the .NET object

Let's start by building the .NET object you have 'purchased'. For this, you will need a C# compiler. We'll assume that you're using Visual C#. (If you don't have a C# compiler, you can get the precompiled DLL here.)

  1. Create a new project in Visual C# called TaxCalc.

  2. Copy the new file into the project: TaxBreakdown.cs.

  3. Build the project for .NET Framework 3.5.

  4. Take the TaxCalc.dll output of the compile process, and put it in a new directory.

    Later, this directory will hold the Resolver One save file.

Add the DLL to the worksheet

Now, let's use the DLL in a modified version of one of the sample files that come with Resolver One.

  1. Open the sample worksheet by selecting Start, Resolver One, Samples, Simple functions and formatting.

  2. Save the sample worksheet in the directory alongside TaxCalc.dll.

  3. As we are replacing the existing tax calculations, replace the withVAT function in the Pre-constants user code (the blue section) with the following code to load up the new DLL:

    import clr
    clr.AddReference("TaxCalc")
    from TaxCalc import TaxBreakdown
    

    All of the Gross Prices turn into red crosses, as they are now using a non-existent function.

Insert the .NET project

The next step is to insert the .NET object.

  1. Insert a new column before C.

  2. Name it "Tax breakdown".

  3. In cell C2, enter the following formula: =TaxBreakdown(A2, B2).

    The string representation of the newly-created tax breakdown object appears the cell; just the result of its ToString() method, the string "TaxBreakdown object".

Extract values from the .NET project

Let's try pulling some values out of the .Net project.

  1. Insert five columns before D. (Click on the column header D, drag to H, release, right-click and select Insert Columns.)

  2. Put the following formulae into the grid:

    D2=C2.valueAddedTax
    E2=C2.valueSubtractedTax
    F2=C2.officeSuppliesLevy
    G2=C2.overpricedProductTax
    H2=C2.rInTheMonthTax
    I2=SUM(B2, D2:H2)
    
  3. Copy the range C2:I2 and duplicate it on the equivalent ranges below.

    Note that the pre-existing conditional formatting returns as you add the data; anything with a gross price greater than 25 is highlighted.


And that's it! This was a very simple example, but in it we managed to create a spreadsheet that is transparently using a .NET object inside a grid.

Comments