How to Add a User-Defined Table Function to Excel

/// <summary>
/// Get price table with costs, margins and prices according to given cost and margin tables.
/// </summary>
/// <param name="CostTable">Cost table with key figure named "costs"</param>
/// <param name="MarginTable">Margin table with key figure named "margin"</param>
/// <returns>Price table</returns>
public static MatrixTable GetPriceTable(MatrixTable CostTable, MatrixTable MarginTable)
{
	// check inputs
	if (CostTable == null || CostTable.IsEmpty || CostTable.RowCount == 0)
		throw new Exception("GetPriceTable: Null or empty input table CostTable\n");
 
	if (MarginTable == null || MarginTable.IsEmpty || MarginTable.RowCount == 0)
		throw new Exception("GetPriceTable: Null or empty input table MarginTable\n");
 
	// check if CostTable has a key figure named "costs"
	if (! TextVector.IfValueFoundInSet("costs", CostTable.KeyFigureFields))
		throw new Exception("GetPriceTable: CostTable must have a key figure named costs\n");
 
	// check if MarginTable has a key figure named "margin"
	if (!TextVector.IfValueFoundInSet("margin", MarginTable.KeyFigureFields))
		throw new Exception("GetPriceTable: MarginTable must have a key figure named margin\n");
 
	// input checks OK, continue..
	try
	{
		// CostTable: Exclude all key figures other than "costs" (only "costs" is required)
		MatrixTable CostTbl = MatrixTable.ExcludeColumns(CostTable,
			TextVector.SetDifference(CostTable.KeyFigureFields,
			new TextVector(new string[] { "costs" })));
 
		// MarginTable: Exclude all key figures other than "margin" (only "margin" is required)
		MatrixTable MarginTbl = MatrixTable.ExcludeColumns(MarginTable,
			TextVector.SetDifference(MarginTable.KeyFigureFields,
			new TextVector(new string[] { "margin" })));
 
		// Get Price Table
		MatrixTable PriceTbl = MatrixTable.MultiplySelectedKeyFigures(
			CostTbl, MarginTbl + 1, "costs", "margin", "price", JokerMatchesAllvalues: true);
 
		// Combine MarginTbl to see margins in resultant price table
		PriceTbl = MatrixTable.CombineTables(PriceTbl, MarginTbl);
 
		return PriceTbl;
	}
	catch (Exception ex)
	{
		throw new Exception("GetPriceTable: " + ex.Message + "\n");
	}
}

/// <summary>
/// Get price table with costs, margins and prices (user-defined table function example)
/// </summary>
/// <param name="xCostTable">Name of cost table with key figure named "costs"</param>
/// <param name="xMarginTable">Name of margin table with key figure named "margin"</param>
/// <param name="xMetaTblName">Name of excel table (ListObject) for field definitions.</param>
/// <param name="WorkbookFullName">File path of Excel Workbook</param>
/// <param name="xResultTblName">Name of resultant combined table</param>
/// <param name="TargetSheetName">Sheet name for writing resultant table</param>
/// <param name="TopLeftCell">Cell address of upper-left corner for output table</param>
public void GetPriceTable2(string xCostTable, string xMarginTable, string xMetaTblName = null,
	string WorkbookFullName = null, string xResultTblName = "PriceTbl", 
	string TargetSheetName = "PriceTbl", string TopLeftCell = "A1") 
{
	// Typical flow of calculation:
 
	// Step 0: Get current application and active workbook
	Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
	Excel.Workbook wbook;
 
	if (WorkbookFullName == null || WorkbookFullName == String.Empty)
		wbook = xlApp.ActiveWorkbook;
	else
		wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);
 
	// Step 1: Get ListObjects
	XTable xTbl1 = ExcelFunc_NO.GetListObject(wbook, xCostTable);
	XTable xTbl2 = ExcelFunc_NO.GetListObject(wbook, xMarginTable);
 
	XTable xMetaTbl = null;
	if (xMetaTblName != null && xMetaTblName != String.Empty) xMetaTbl = ExcelFunc_NO.GetListObject(wbook, xMetaTblName);
 
	// Step 2: Get meta data with field definitions
	MetaDataX mdx = new MetaDataX();
	if (xMetaTblName != null && xMetaTblName != String.Empty) mdx.ReadFieldsFromExcelTable(xMetaTbl);
	MetaData md = mdx.metaData;
 
	// Step 3: Read excel tables (inputs) into MatrixTable objects
	MatrixTable Tbl1 = ExcelToMatrixTable(xTbl1, mdx).matrixTable;
	MatrixTable Tbl2 = ExcelToMatrixTable(xTbl2, mdx).matrixTable;
 
	// Step 4: Generate resultant (output) tables with table functions
	MatrixTable PriceTbl = UserFunc.GetPriceTable(Tbl1, Tbl2);
 
	// Step 5: Write output tables (in this case CombinedTbl) into excel tables
	Excel.Worksheet wsheet = ExcelFunc_NO.GetWorksheet(wbook, TargetSheetName, AddSheetIfNotFound: true);
 
	MatrixTableToExcel(new MatrixTableX(PriceTbl), wsheet, xResultTblName, TopLeftCell);
	wsheet.Activate();
}

/// <summary>
/// Get price table with costs, margins and prices (user-defined table function example)
/// </summary>
public void GetPriceTable2_macro2()
{
	try
	{
		// get current excel application
		Excel.Application xlapp = ExcelFunc_NO.GetExcelApplicationInstance();
 
		// get active workbook
		Excel.Workbook wbook = xlapp.ActiveWorkbook;
 
		// set global form parameters
		ParameterForm1.wbook_st = wbook;
		ParameterForm1.md_st = new MetaData();
 
		string FormTitle = "Parameter Form for GetPriceTable2";
		string FuncTitle = "Get Price Table";
		string FuncDescr = "Get price table with costs, margins and prices. "
			+ "Cost table must contain a key figure named costs, and margin table must contain a key figure named margin. "
			+ "Resultant price table contains all three key figures: costs, margin and price";
 
		using (ParameterForm1 myform = new ParameterForm1())
		{
			myform.PrepForm_GetPriceTable2(FormTitle, FuncTitle, FuncDescr, "PriceTbl", "PriceTbl", "A1");
			myform.ShowDialog();
		}
 
		// return if cancel button is pressed
		if (ParameterForm1.IfCancel) return;
 
		// assign parameter values
		string xTb1Name = ParameterForm1.xTable1_st;
		string xTbl2Name = ParameterForm1.xTable2_st;
 
		string xOutTable = ParameterForm1.xOutTable1_st;
		string SheetName = ParameterForm1.SheetName_st;
		string TopLeftCell = ParameterForm1.TopLeftCell_st;
 
		// combine tables
		GetPriceTable2(xTb1Name, xTbl2Name,
			xResultTblName: xOutTable, TargetSheetName: SheetName,
			TopLeftCell: TopLeftCell);
	}
	catch (Exception ex)
	{
		MessageBox.Show("GetPriceTable2: " + ex.Message + "\n");
	}
}

/// <summary>
/// Get price table with costs, margins and prices (user-defined table function example)
/// </summary>
[ExcelCommand(MenuName = "Table Functions", MenuText = "Get Price Table (User Defined)")]
public static void GetPriceTable2_macro2()
{
	var xm = new ExcelTable();
	xm.GetPriceTable2_macro2();
}

// Prepare parameter form for get price table (example for user-defined table function)
internal void PrepForm_GetPriceTable2(string FormTitle, string FuncTitle, string FuncDescr,
	string xOutTable, string SheetName, string TopLeftCell)
{
	// init form
	this.Text = FormTitle + formbrand;
	this.FuncTitle.Text = FuncTitle;
	this.FuncDescription.Text = FuncDescr;
	int CTR = 0;  // number of form elements like combo boxes, text boxes etc
 
	// get list of all excel tables
	string[] TableList;
	ExcelFunc_NO.GetAllExcelTables(wbook_st, out TableList);
 
	// get list of all worksheets
	string[] sheets = ExcelFunc_NO.GetAllSheetNames(wbook_st);
 
	// initiate combo boxes
	if (TableList != null && TableList.Count() > 0)
	{
		// xTable1
		AddComboBox(this.xTable1, Col2Start, ++CTR * LineWidth + ImageWidth, ComboBoxStyle.DropDownList, true, TableList);
		AddLabel(this.xTable1Lable, "Select Cost Table", Col1Start, CTR * LineWidth + ImageWidth, LableLen);
 
		// xTable2
		AddComboBox(this.xTable2, Col2Start, ++CTR * LineWidth + ImageWidth, ComboBoxStyle.DropDownList, true, TableList);
		AddLabel(this.xTable2Lable, "Select Margin Table", Col1Start, CTR * LineWidth + ImageWidth, LableLen);
	}
	else
	{
		this.RedWarning.Text = "No table was found in excel workbook!";
		return;
	}
 
	// xOutTable1
	AddTextBox(this.xOutTable1, Col2Start, ++CTR * LineWidth + ImageWidth, 100, 20, xOutTable);
	AddLabel(this.xOutTable1Lable, "Enter name of output table", Col1Start, CTR * LineWidth + ImageWidth, LableLen);
 
	// SheetName
	AddComboBox(this.SheetName, Col2Start, ++CTR * LineWidth + ImageWidth, ComboBoxStyle.DropDown, true, sheets, SheetName);
	AddLabel(this.SheetNameLable, "Select/Enter sheet name for output", Col1Start, CTR * LineWidth + ImageWidth, LableLen);
 
	// text box (UpperLeftCell)
	AddTextBox(this.TopLeftCell, Col2Start, ++CTR * LineWidth + ImageWidth, 100, 20, TopLeftCell);
	AddLabel(this.TopLeftCellLable, "Address of upper-left cell for output", Col1Start, CTR * LineWidth + ImageWidth, LableLen);
 
	// OK button
	AddButton(this.button_OK, Col2Start, ++CTR * LineWidth + ImageWidth, 75, 23, "OK", "OK");
	this.button_OK.Click += new System.EventHandler(this.GetPriceTable2_OKbutton);
 
	// CANCEL button
	AddButton(this.button_Cancel, Col2Start + 90, CTR * LineWidth + ImageWidth, 75, 23, "CANCEL", "CANCEL");
	this.button_Cancel.Click += new System.EventHandler(this.button_Cancel_Click);
 
	// form size
	this.Size = new System.Drawing.Size(FormWidth, ImageWidth + CTR * LineWidth + BottomMargin);
}

private void GetPriceTable2_OKbutton(object sender, EventArgs e) 
{
	// validate input
	if (this.xTable1.SelectedItem == null)
	{
		this.RedWarning.Text = "A cost table must be selected!";
		this.Refresh();
		return;
	}
 
	if (this.xTable2.SelectedItem == null)
	{
		this.RedWarning.Text = "A margin table must be selected!";
		this.Refresh();
		return;
	}
 
	// check if cost table has a key figure named "costs"
	string InTbl1 = this.xTable1.SelectedItem.ToString();
	TableFields tfields1 = ExcelFunc_NO.ReadTableFieldsFromExcel(InTbl1, md_st);
 
	if (! TextVector.IfValueFoundInSet("costs", tfields1.KeyFigures))
	{
		this.RedWarning.Text = "Selected cost table must contain a key figure named costs";
		this.Refresh();
		return;
	}
 
	// check if margin table has a key figure named "margin"
	string InTbl2 = this.xTable2.SelectedItem.ToString();
	TableFields tfields2 = ExcelFunc_NO.ReadTableFieldsFromExcel(InTbl2, md_st);
 
	if (!TextVector.IfValueFoundInSet("margin", tfields2.KeyFigures))
	{
		this.RedWarning.Text = "Selected margin table must contain a key figure named margin";
		this.Refresh();
		return;
	}
 
	if (this.xOutTable1.Text == null || this.xOutTable1.Text.Trim() == "")
	{
		this.RedWarning.Text = "Name of output table must be entered!";
		this.Refresh();
		return;
	}
 
	if (this.SheetName.SelectedItem == null && (this.SheetName.Text == null || this.SheetName.Text.Trim() == ""))
	{
		this.RedWarning.Text = "A worksheet for output table must be selected or entered!";
		this.Refresh();
		return;
	}
 
	if (this.TopLeftCell.Text == null || this.TopLeftCell.Text.Trim() == "")
	{
		this.RedWarning.Text = "A upper-left cell address like A1 for output table must be entered!";
		this.Refresh();
		return;
	}
 
	// checks OK, set static variables
	xTable1_st = this.xTable1.SelectedItem.ToString();
	xTable2_st = this.xTable2.SelectedItem.ToString();
 
	xOutTable1_st = this.xOutTable1.Text.Trim();
 
	if (this.SheetName.SelectedItem == null)
		SheetName_st = this.SheetName.Text.Trim();
	else
		SheetName_st = this.SheetName.SelectedItem.ToString();
 
	TopLeftCell_st = this.TopLeftCell.Text.Trim();
	IfCancel = false;
 
	// close form
	this.Close();
}