software.tuncalik.com

' Calculate price table with given cost and margin tables
' 1) Read input tables: Costs, Margins, MetaData
' 2) Calculate price table
' 3) Write resultant price table into a sheet
Private Sub CalculatePrices()
Dim pxl As New ProtosExcelFunc
Dim CostTableSheet, MarginTableSheet, MetaDataSheet, PriceTableSheet As String
 
CostTableSheet = "CostTable"
MetaDataSheet = "MetaData"
 
MarginTableSheet = "MarginTable1"
PriceTableSheet = "PriceTable1"
 
'MarginTableSheet = "MarginTable2"
'PriceTableSheet = "PriceTable2"
 
' call .net method
MsgBox pxl.CalculatePrices(Application.ActiveWorkbook.FullName, CostTableSheet, _
    MarginTableSheet, MetaDataSheet, PriceTableSheet)
End Sub

/// <summary>
/// Calculate price table for given cost and margin tables
/// </summary>
/// <param name="ExcelFilePath">Full file path of excel file</param>
/// <param name="CostTableSheet">Name of worksheet for reading cost table (input table)</param>
/// <param name="MarginTableSheet">Name of worksheet for reading margin table (input table)</param>
/// <param name="MetaDataSheet">Name of worksheet for reading field names and types (input table)</param>
/// <param name="PriceTableSheet">Name of target worksheet for writing price table (output table)</param>
/// <returns>Message to calling function</returns>
public string CalculatePrices(string ExcelFilePath, string CostTableSheet, string MarginTableSheet,
	string MetaDataSheet, string PriceTableSheet)
{
	try
	{
		// read meta data
		DataTable MetaTbl = ExcelFunc.ReadTableFromExcelSheet_ODBC(ExcelFilePath, MetaDataSheet);
		MetaData md = MetaData.ReadFieldsFromDataTable(MetaTbl);
 
		// read input tables
		DataTable CostTbl = ExcelFunc.ReadTableFromExcelSheet_ODBC(ExcelFilePath, CostTableSheet);
		DataTable MarginTbl = ExcelFunc.ReadTableFromExcelSheet_ODBC(ExcelFilePath, MarginTableSheet);
 
		// convert DataTable to MatrixTable
		MatrixTable CostTable = MatrixTable.Import_from_DataTable(CostTbl, md);
		MatrixTable MarginTable = MatrixTable.Import_from_DataTable(MarginTbl, md);
 
		// Calculate price table with table function 'MultiplySelectedKeyFigures'
		MatrixTable PriceTable = MatrixTable.MultiplySelectedKeyFigures(CostTable, (MarginTable + 1),
		InputKeyFigTbl1: "costs", InputKeyFigTbl2: "margin", OutputKeyFig: "price",
		JokerMatchesAllvalues: true);
 
		// convert MatrixTable to DataTable
		DataTable PriceTbl = MatrixTable.Export_To_DataTable(PriceTable);
 
		// write price table into excel sheet
		ExcelFunc.WriteTableToExcelSheet(PriceTbl, ExcelFilePath, SheetName: PriceTableSheet, SaveWorkbook: false);
 
		// return affirmative message to caller
		return "Price table is calculated successfully!";
	}
	catch (Exception ex)
	{
		return "CalculatePrices: An error has occured! " + ex.Message;
	}
}

/// <summary>
/// Calculate price table with given cost and margin tables
/// </summary>
public static void CalculatePriceTable()
{
	// Get full file path
	string filepath = System.IO.Path.GetFullPath(@"Resources\DotNetIntegration.xlsm");
 
	// call table function
	ProtosExcelFunc pxl = new ProtosExcelFunc();
	string msg = pxl.CalculatePrices(filepath, CostTableSheet: "CostTable", MarginTableSheet: "MarginTable1",
		MetaDataSheet: "MetaData", PriceTableSheet: "PriceTable1");
 
	System.Diagnostics.Debug.WriteLine("Message from CalculatePrices: " + msg);
}