DataTable extensions

// 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)

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);
}