In this example, we will put a .NET object into the Resolver One grid. Let's imagine that 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.
Let's start by building the .NET object you have 'purchased': for this, you will need a C# compiler. I'll assume that you are using Visual C#. If you don't have a .NET compiler, you can get the precompiled DLL from here: TaxCalc.dll
- Create a new project in Visual C# called
TaxCalc. - Add a file called
TaxBreakdown.cs, and paste the code that the filename links to into it. - Build the project.
- Take the
TaxCalc.dlloutput of the compile process, and put it in a new directory, where we will later on be putting the Resolver One save file.
Now, let's use the DLL in a modified version of the Simple Function and Formatting, which is saved as simple-function-and-formatting.rsl in the Samples subfolder of the Resolver One folder on your Start Menu. Load up the sample, then save it in the directory where you just put the new DLL.
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
You should see all of the Gross Prices turn into red crosses, as they are now using a non-existent function.
The next step is to use the .NET object.
- Insert a new column before C.
- Name it "Tax breakdown".
- In cell C2, enter the following formula:
=TaxBreakdown(A2, B2).
You will see the string representation of the newly-created tax breakdown object in the cell - it's just the result of its ToString() method, which is the string "TaxBreakdown object". Let's try pulling out some of its values:
- Insert five columns before D (click on the column header D, drag to H, release, right-click and select "Insert Columns")
- Next, put the following formulae into the grid:
D2: =C2.valueAddedTax E2: =C2.valueSubtractedTax F2: =C2.officeSuppliesLevy G2: =C2.overpricedProductTax H2: =C2.rInTheMonthTax =SUM(B2, D2:H2)
Now you can copy the range C2:I2, and duplicate it on the equivalent ranges below. You will note that the conditional formatting the original spreadsheet had when you first loaded it comes back as you add the data; anything with a gross price greater than 25 is hightlighted.
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
If you have comments, questions or suggestions about any of the Resolver One documentation, please post them to the Documentation Suggestions Forum.
