DataTable extensions

With the new release (1.02) of finaquant® protos we decided to offer most of the interface elements like the DataTable class of .net framework that connect MatrixTable class of finaquant to the outside world as free and open-source (C# code).

Beginning from today (5. Feb 2013) you may download our DataTable extensions together with the MS Visual Studio project FinaquantProtosStarter which also contains demo functions for finaquant® protos.

DataTable extensions in FinaquantProtosStarter

Users with developer skills: Feel free to change and use the code wherever you want. We would appreciate if you inform us about your suggestions and improvements at our forum for general topics.

Other users without time or skill for software development need not bother at all about the additional project OpenSource within FinaquantProtosStarter; they can as usual focus on demo functions for analytical table functions.

Current list of extension methods

// Get data type of column
public static Type OS_GetColumnDataType(this DataTable tbl, string ColumnName)
 
// Get data type of column
public static Type OS_GetColumnDataType(this DataTable tbl, int ColumnIndex)
 
// Get table element with given column and row indices
public static object OS_GetElementValue(this DataTable tbl, int ColInd, int RowInd)
 
// Get table element with given column names and row indices
public static object OS_GetElementValue(this DataTable tbl, 
	string ColumnName, int RowInd)
 
// Assign value to a table element with given column and row indices
public static void OS_SetElementValue(this DataTable tbl, string ColumnName, 
	int RowInd, object ElementValue)
 
// Convert DataTable into a printable string
public static string OS_ConvertToString(this DataTable tbl)
 
// Convert DataTable into  a MatrixTable object
public static MatrixTable OS_ConvertToMatrixTable(this DataTable tbl, MetaData md,
            string TextReplaceNull = "NULL", int NumReplaceNull = 0, 
			double KeyFigReplaceNull = 0)
 
// Convert TableMatrix to DataTable.
public static DataTable OS_ImportFromMatrixTable(this DataTable tbl, MatrixTable mtbl)
 
// Column partition: Return a DataTable with selected columns
public static DataTable OS_PartitionColumn(this DataTable tbl, TextVector ColumnNames)
 
// Return a new data table with selected column indices
public static DataTable OS_PartitionColumn(this DataTable tbl, NumVector ColumnIndices)
 
// Return a subtable with selected row indices
public static DataTable OS_PartitionRow(this DataTable tbl, NumVector RowIndices)
 
// Return subtable with selected columns and rows
public static DataTable OS_Partition(this DataTable tbl, TextVector ColumnNames, 
	NumVector RowIndices)
 
// View DataTable in grid view (read-only), multi-thread
public static TableViewer OS_ViewTable_MultiThread(this DataTable Tbl, 
	string Header = "Table Viewer")
 
// View DataTable in grid view (read-only), single-thread
public static TableViewer OS_ViewTable_SingleThread(this DataTable Tbl, 
	string Header = "Table Viewer")
 
// Save DataTable as an XML file.
public static void OS_ExportTableToXMLfile(this DataTable Tbl, string Filename, 
	string FileDir = "", bool IfAddTimeStamp = true)
 
// Read DataTable from an XML file
public static DataTable OS_ImportFromXMLfile(this DataTable Tbl, string Filename, 
	string FileDir = "")
 
// Export DataTable into an excel file
public static void OS_ExportToExcelFile(this DataTable Tbl, 
	string ExcelFilePath = null)
 
// Read DataTable from a sheet of excel file
public static DataTable OS_ImportFromExcelFile(this DataTable Tbl, 
	string Filepath, string SheetName)
 
// Sort table rows after given fields
public static DataTable OS_Sort(this DataTable Tbl, string SortStr)

Demo function for DataTable extensions

public static void OS_DataTable_Extensions_DEMO()
{
#region "Create a test table of type MatrixTable"
	// code for generating a test table 
	// ...
#endregion "Create a test table of type MatrixTable"
 
DataTable dt = new DataTable();
 
// convert MatrixTable to DataTable
DataTable SalesTbl = dt.OS_ImportFromMatrixTable(SalesTable);
 
// view SalesTbl - multi-thread:
// table viewer can remain open in a separate thread as the main program runs
TableViewer tw1 = SalesTbl.OS_ViewTable_MultiThread("Sales table, multi-thread");
 
// wait a bit
System.Threading.Thread.Sleep(200);
 
// get element values
Console.WriteLine("Table element (country, row = 3): {0}",
	SalesTbl.OS_GetElementValue("country", 2));
 
Console.WriteLine("Table element (column = 4, row = 3): {0}",
	SalesTbl.OS_GetElementValue(3, 2));
 
MessageBox.Show("Press OK to continue.");
 
// set element value
SalesTbl.OS_SetElementValue("country", 3, "Venezuela");
 
// view table - single-thread
TableViewer tw2 = SalesTbl.OS_ViewTable_SingleThread("Sales table with Venezuela at 4. row, single-thread");
 
// wait a bit
System.Threading.Thread.Sleep(200);
MessageBox.Show("See Venezuela as the country at 4. row of table. Press OK to continue.");
 
// print DataTable to console; convert DataTable to string
Console.WriteLine("SalesTbl: \n {0}", SalesTbl.OS_ConvertToString());
 
// partition DataTable
NumVector RowIndices = NumVector.CreateVectorWithElements(0, 2, 4, 6);
TextVector Columns = TextVector.CreateVectorWithElements("country", "date");
 
DataTable SubSalesTbl = SalesTbl.OS_Partition(Columns, RowIndices);
 
// view table
var tw3 = SubSalesTbl.OS_ViewTable_MultiThread("Subtable of sales table, multi-thread");
 
// convert DataTable to MatrixTable
MatrixTable mTbl = SalesTbl.OS_ConvertToMatrixTable(md);
MatrixTable.View_MatrixTable(mTbl, "sales table as MatrixTable");
 
// re-locate a multi-thread table viewer
System.Threading.Thread.Sleep(500);
MessageBox.Show("Press OK to relocate subtable - unless you already closed it manually.");
if (tw3.Visible) tw3.Invoke(new LocationDelegate(tw3.SetDesktopLocation), (int)-20, (int)-20);
 
// close a multi-thread table viewer
System.Threading.Thread.Sleep(500);
MessageBox.Show("Press OK to close subtable - unless you already closed it manually.");
if (tw3.Visible) tw3.Invoke(new CloseDelegate(tw3.Close));
 
// export sales table to an XML file (save DataTable as XML file)
string filename = "SalesTable";
string directory = @"C:\Windows\Temp\";     // check if this is a valid directory on your computer!
SalesTbl.OS_ExportTableToXMLfile(filename, directory, IfAddTimeStamp: false);
 
// import table from XML file (read DataTable from XML file)
System.Threading.Thread.Sleep(200);
var SalesTblFromXML = dt.OS_ImportFromXMLfile(filename, directory);
var tw4 = SalesTblFromXML.OS_ViewTable_MultiThread("Sales table imported from XML file");
 
// export DataTable to excel file
// this works only if you have MS office and excel installed on your computer
 
// open an excel file
SalesTbl.OS_ExportToExcelFile();
 
// save to an excel file without opening
System.Threading.Thread.Sleep(200);
string filepath = @"C:\Windows\Temp\SalesTable.xlsx";
SalesTbl.OS_ExportToExcelFile(filepath);
 
// import DataTable from excel file
System.Threading.Thread.Sleep(200);
var SalesTblFromExcel = dt.OS_ImportFromExcelFile(filepath, SheetName: "Sheet1");
var tw5 = SalesTblFromExcel.OS_ViewTable_MultiThread("Sales table imported from Excel file");
}
 
// delegate type required for TableViewer_FormsAndThreads()
private delegate void CloseDelegate();
 
// delegate type required for TableViewer_FormsAndThreads()
private delegate void LocationDelegate(int x, int y);
}
Copyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
This entry was posted in Calculation engine and tagged , , . Bookmark the permalink.

Leave a Reply