public static string fUtilityPutDataTable(string handle, object[,] data)
{
PersistentObjects.AssertReservedWordsTableName(handle);
PersistentObjects.AddToMap(handle, ExcelFriendlyConversion.ConvertObjectArrayToDataTable(handle, data), "DATATABLE");
return(handle);
}
public static object[,] fUtilityDataTableSortAndGet(string handle, object[] headers, object SortAscending, object noHeadersInput)
{
var table = PersistentObjects.GetFromMap <DataTable>(handle, "DATATABLE");
var output = ExcelFriendlyConversion.ConvertDataTableToObjectArray(table);
return(fUtilityTableSortByHeader(output, headers, SortAscending, noHeadersInput));
}
public static object[,] fUtilityArrayIgnoreNA(object[] array)
{
List <object> output = new List <object>();
foreach (object entry in array)
{
if (entry is ExcelDna.Integration.ExcelError == false)
{
output.Add(entry);
}
}
return(ExcelFriendlyConversion.ArrayToVerticalObject(output.ToArray()));
}
public static object[,] fUtilityGetDataTable(string handle, object noHeadersInput)
{
bool noHeaders = Optional.Check(noHeadersInput, false);
if (ExcelDnaUtil.IsInFunctionWizard())
{
return(new object[0, 0]);
}
else
{
var output = PersistentObjects.GetFromMap <DataTable>(handle, "DATATABLE");
return(ExcelFriendlyConversion.ConvertDataTableToObjectArray(output, noHeaders));
}
}
public static object[,] fUtilityTableSortByHeader(object[,] table, object[] headers, object SortAscending, object noHeadersInput)
{
bool ascending = Optional.Check(SortAscending, true);
bool noHeaders = Optional.Check(noHeadersInput, false);
var dataTable = ExcelFriendlyConversion.ConvertObjectArrayToDataTable("TEMPTABLE", table);
var header = "";
for (int i = 0; i < headers.Length - 1; i++)
{
header = headers[i].ToString() + ", " + header;
}
header = header + headers[headers.Length - 1];
var direction = (ascending) ? "ASC" : "DESC";
dataTable.DefaultView.Sort = header + " " + direction;
return(ExcelFriendlyConversion.ConvertDataTableToObjectArray(dataTable.DefaultView.ToTable()));
}
public static object[,] fUtilityQueryDataTable(string sqlStringOrHandle, object noHeadersInput)
{
string sql = ObjectInfo.DetermineIfSqlHandleOrSqlQuery(sqlStringOrHandle);
object[,] output;
bool noHeaders = Optional.Check(noHeadersInput, false);
var tableHandle = ParsingFunctionality.GetTableNameFromSqlQuery(sql);
if (PersistentObjects.ContainsKey(tableHandle, "DATATABLE") == false)
{
string errorMessage = "ERROR: DataTable + " + tableHandle + " does not exist in the object map.";
output = new object[1, 1];
output[0, 0] = errorMessage;
return(output);
}
else
{
var newTable = QueryCSV.QueryDataTable(sql, PersistentObjects.CsvPath);
output = ExcelFriendlyConversion.ConvertDataTableToObjectArray(newTable, noHeaders);
return(output);
}
}
static void TestSQLite()
{
var table = CreateTestTable();
SQLiteDatabase db = new SQLiteDatabase("MYDB");
db.CreateTable("MYTABLE", table);
var table2 = db.InspectDatabase();
var table3 = db.GetDataTable("MYTABLE");
var obj = CreateTestArray();
var table4 = ExcelFriendlyConversion.ConvertObjectArrayToDataTable("MYTABLE4", obj);
PrintTableInfoToConsole(table4);
db.CreateTable("MYTABLE4", table4);
var table5 = db.GetDataTable("MYTABLE4");
PrintTableInfoToConsole(table5);
Console.WriteLine("Database created ..");
}
public static object[,] fUtilityGetIntersection(object[] array1, object[] array2)
{
var list1 = array1.ToList();
var list2 = array2.ToList();
var group1 =
from n in list1
group n by n
into g
select new { g.Key, count = g.Count() };
var group2 =
from n in list2
group n by n
into g
select new { g.Key, count = g.Count() };
var joined =
from b in group2
join a in group1 on b.Key equals a.Key
select new { b.Key, Count = Math.Min(b.count, a.count) };
return(ExcelFriendlyConversion.ArrayToVerticalObject(joined.SelectMany(a => Enumerable.Repeat(a.Key, a.Count)).ToArray()));
}
public static object[,] fUtilityGetDistinctValues(object[] array)
{
var list = array.ToList <object>();
return(ExcelFriendlyConversion.ArrayToVerticalObject(list.Distinct().ToArray()));
}
