Tutorial: How to add a new table function to Excel add-in

Table-valued functions in finaquant .net libraries

Tutorial: How to add a new table function to Excel add-in

Postby tunc » 20 Sep 2014, 19:36

Following steps illustrate how to add a new table valued function (of the .net library Finaquant Calcs) named Combinate Rows to the excel add-in Finaquant in Excel (see Visual Studio project among downloads in this page).

Combinate Rows: Cartesian Multiplication of table rows: Generate a new table with all possible row combinations of input tables.
Image

Step 1: Add a non-static method named CombinateRows to class ExcelTable
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. public void CombinateRows(string xMetaTblName = null, string WorkbookFullName = null,
  2.     string xResultantTblName = "CombRows", string TargetSheetName = "CombRowsTable",
  3.     params string[] xTableNames)
  4. {
  5.     // Typical flow of calculation:
  6.  
  7.     // Step 0: Get current application and active workbook
  8.     Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
  9.     Excel.Workbook wbook;
  10.  
  11.     if (WorkbookFullName == null || WorkbookFullName == String.Empty)
  12.         wbook = xlApp.ActiveWorkbook;
  13.     else
  14.         wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);
  15.  
  16.     // Step 1: Get ListObjects
  17.     XTable[] xTblArr = new XTable[xTableNames.Count()];
  18.  
  19.     string xTableName;
  20.  
  21.     for (int i = 0; i < xTableNames.Count(); i++)
  22.     {
  23.         xTableName = xTableNames[i];
  24.         xTblArr[i] = ExcelFunc_NO.GetListObject(wbook, xTableName);
  25.     }
  26.  
  27.     XTable xMetaTbl = null;
  28.     if (xMetaTblName != null && xMetaTblName != String.Empty) xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);
  29.  
  30.     // Step 2: Get meta data with field definitions
  31.     MetaDataX mdx = new MetaDataX();
  32.     if (xMetaTblName != null && xMetaTblName != String.Empty) mdx.ReadFieldsFromExcelTable(xMetaTbl);
  33.     MetaData md = mdx.metaData;
  34.  
  35.     // Step 3: Read excel tables (inputs) into MatrixTable objects
  36.     MatrixTable[] TblArr = new MatrixTable[xTableNames.Count()];
  37.  
  38.     for (int i = 0; i < xTblArr.Count(); i++)
  39.     {
  40.         TblArr[i] = ExcelToMatrixTable(xTblArr[i], mdx).matrixTable;
  41.     }
  42.  
  43.     // Step 4: Generate resultant (output) tables with table functions
  44.     MatrixTable ResultantTbl = MatrixTable.CombinateTableRows(TblArr);
  45.  
  46.     // Step 5: Write output tables (in this case ResultantTbl) into excel tables
  47.     Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);
  48.  
  49.     MatrixTableToExcel(new MatrixTableX(ResultantTbl), wsheet, xResultantTblName);
  50.     wsheet.Activate();
  51. }
GeSHi ©

Step 2: Add a parameter-less non-static method named CombinateRows_macro to class ExcelTable
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. public void CombinateRows_macro()
  2. {
  3.     try
  4.     {
  5.         // get current excel application
  6.         Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();
  7.  
  8.         // inbox parameters
  9.         string title = "Parameter Form for CombinateRows";
  10.         string prompt = "Please select a single-column range with the names of 2 or more excel tables (ListObject) whose rows will be combinated:\n\n";
  11.  
  12.         List<object> ParameterValues;
  13.  
  14.         // call input box
  15.         if (ExcelFunc_NO.InputBoxRange(xlapp, prompt, title, 2, out ParameterValues, false))
  16.         {
  17.             var xTableNames = new string[ParameterValues.Count];
  18.  
  19.             // assign parameter values
  20.             for (int i = 0; i < ParameterValues.Count; i++)
  21.             {
  22.                 xTableNames[i] = (string)ParameterValues[i];
  23.             }
  24.  
  25.             CombinateRows(xTableNames: xTableNames);
  26.         }
  27.     }
  28.     catch (Exception ex)
  29.     {
  30.         MessageBox.Show("CombinateRows: " + ex.Message + "\n");
  31.     }
  32. }
GeSHi ©

The two methods above can be called in a Excel VBA, as explained in the visual User Guide.

In order to add the new table function to the command menu named Table Functions we need to add a static method to the class ExcelTableDNA with proper attributes.

Step 3: Add a static method named CombinateRows_macro to class ExcelTableDNA
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. [ExcelCommand(MenuName = "Table Functions", MenuText = "Combinate Rows")]
  2. public static void CombinateRows_macro()
  3. {
  4.     var xt = new ExcelTable();
  5.     xt.CombinateRows_macro();
  6. }
GeSHi ©

Now, the new table function should appear in the command menu. If you also want to have a command button in the ribbon, you need to add a new button element (XML) to the DNA file:

Step 4: Add a new button element (XML) to the DNA file in folder bin/Release of the Visual Studio project:
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. <group>
  2. ...
  3. <button id="CombinateRows_macro" tag="CombinateRows_macro"
  4.  onAction="RunTagMacro" label="&amp;Combinate Rows"
  5.  screentip="Cartesian Multiplication of table rows: Generate a new table with all possible row combinations of input tables. There must be no common fields among input tables." size="normal"
  6.  imageMso="TableInsertDialog" />
  7. </group>
GeSHi ©
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland

Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest

cron