private void ExecuteSelection(object sender, SelectionArgs e)
{
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
if (_connected == false)
{
var control = _customTaskPane.Control as ExecuteQuery;
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
using (var session = _driver.Session())
{
for (int r = 1; r <= inputrange.Rows.Count; r++)
{
string cypher = "";
foreach (Range col in inputrange.Rows[r].Columns)
{
try
{
cypher += col.Cells[1, 1].Value2.ToString();
}
catch
{
}
}
var result = session.Run(cypher);
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(result.Summary.Statement.Text);
}
}
}
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
private async void CreateRelationships(object sender, SelectionArgs e)
{
if (_connected == false)
{
var control = _customTaskPane.Control as ExecuteQuery;
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
var session = _driver.AsyncSession();
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
if (inputrange.Columns.Count <= 1)
{
CurrentControl.SetMessage("Select 3 columns with nodes and relationship to create");
await session.CloseAsync();
return;
}
if (inputrange.Rows.Count <= 2)
{
CurrentControl.SetMessage("Select 2 header rows and 1 data row");
await session.CloseAsync();
return;
}
string label1 = Convert.ToString(inputrange.Cells[1, 1].Value2);
string label2 = Convert.ToString(inputrange.Cells[1, 2].Value2);
string relationshiptype = Convert.ToString(inputrange.Cells[1, 3].Value2);
if (label1.Length == 0 || label2.Length == 0 || relationshiptype.Length == 0)
{
CurrentControl.SetMessage("Labels and relationship type must not be empty");
await session.CloseAsync();
return;
}
string[] properties = new string[inputrange.Columns.Count];
for (int i = 1; i <= inputrange.Columns.Count; i++)
{
try
{
properties[i - 1] = Convert.ToString(inputrange.Cells[2, i].Value2);
}
catch
{
}
}
for (int r = 3; r <= inputrange.Rows.Count; r++)
{
var row = inputrange.Rows[r];
var input = inputrange;
string cypher = "MATCH (a: {0}),(b: {1} ) WHERE a.`{2}` = '{3}' and b.`{4}` = '{5}' MERGE (a)-[r:`{6}`]->(b) {7}";
string relprop = "";
if (properties.Length > 2)
{
relprop = "SET r += { ";
bool addcoma = false;
for (int p = 2; p < properties.Length; p++)
{
string propvalue = Convert.ToString(inputrange.Cells[r, p].Value2);
if (properties[p].Length > 0 && propvalue.Length > 0)
{
string prop = "`{0}`:\"{1}\"";
prop = String.Format(prop, properties[p], propvalue);
if (addcoma)
{
relprop += " , ";
}
addcoma = true;
relprop += prop;
}
}
relprop += " }";
if (relprop.Length == "SET r += { ".Length)
{
relprop = "";
}
}
string formatedcypher = String.Format(
cypher,
label1,
label2,
properties[0],
inputrange.Cells[r, 1].Value2.ToString(),
properties[1],
inputrange.Cells[r, 2].Value2.ToString(),
relationshiptype, relprop);
try
{
IResultCursor cursor = await session.RunAsync(formatedcypher);
var records = await cursor.ToListAsync();
var summary = await cursor.ConsumeAsync();
if (r == inputrange.Rows.Count)
{
string message = summary.ToString();
CurrentControl.SetMessage(message);
}
}
catch (Exception ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
await session.CloseAsync();
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
private async void CreateNodes(object sender, SelectionArgs e)
{
var control = _customTaskPane.Control as ExecuteQuery;
if (_connected == false)
{
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
var session = _driver.AsyncSession();
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
control.progress.Report(0);
if (inputrange.Columns.Count <= 1)
{
CurrentControl.SetMessage("Select more than 1 column");
}
string[] properties = new string[inputrange.Columns.Count];
for (int i = 2; i <= inputrange.Columns.Count; i++)
{
try
{
properties[i - 2] = Convert.ToString(inputrange.Cells[1, i].Value2);
}
catch
{
properties[i - 2] = "property" + (i - 1).ToString();
}
}
for (int r = 2; r <= inputrange.Rows.Count; r++)
{
control.progress.Report(r / inputrange.Rows.Count * 100);
var row = inputrange.Rows[r];
var label = "";
try
{
label = row.Cells[1, 1].Value2.ToString();
}
catch
{
label = "NewExcelNode";
}
string cypher = "MERGE (a: " + label + " { ";
int i = 2;
{
string propval = Convert.ToString(row.Cells[1, i].Value2);
if (properties[i - 2].Length > 0 && propval.Length > 0)
{
cypher += "`" + properties[i - 2] + "`" + ": \"" + propval + "\",";
}
}
cypher = cypher.TrimEnd(',');
cypher += "})";
if (row.columns.count > 2)
{
cypher += " SET a += { ";
for (i = 3; i <= row.Columns.Count; i++)
{
string propval = Convert.ToString(row.Cells[1, i].Value2);
if (properties[i - 2] != null && propval != null)
{
if (properties[i - 2].Length > 0 && propval.Length > 0)
{
cypher += "`" + properties[i - 2] + "`" + ": \"" + propval + "\",";
}
}
}
cypher = cypher.TrimEnd(',');
cypher += "}";
}
try
{
IResultCursor cursor = await session.RunAsync(cypher);
var records = await cursor.ToListAsync();
var summary = await cursor.ConsumeAsync();
string message = summary.ToString();
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(message);
}
}
catch (Neo4jException ee)
{
CurrentControl.SetMessage(ee.Message);
}
}
await session.CloseAsync();
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
private void LoadAllNodes(object sender, SelectionArgs e)
{
//ExecuteLoadAllNodes(sender, e);
ExecuteLoadActiveWorksheet(sender, e);
CurrentControl.SetMessage("PULL");
}
private void UpdateAllNodes(object sender, SelectionArgs e)
{
CurrentControl.SetMessage("PUSH");
UpdateActiveWorksheet(sender, e);
}
private async void UpdateActiveWorksheet(object sender, SelectionArgs e)
{
var control = _customTaskPane.Control as ExecuteQuery;
if (_connected == false)
{
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
var session = _driver.AsyncSession();
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
inputrange = worksheet.UsedRange;
int indexOfIdentifier = 0;
control.progress.Report(0);
if (inputrange.Columns.Count <= 1)
{
CurrentControl.SetMessage("Select more than 1 column");
}
string[] properties = new string[inputrange.Columns.Count];
for (int i = 0; i < inputrange.Columns.Count; i++)
{
int excelIndex = i + 1;
try
{
string colName = Convert.ToString(inputrange.Cells[1, excelIndex].Value2);
properties[i] = colName;
string s = colName.ToLowerInvariant();
if (s == "uuid")
{
indexOfIdentifier = i;
}
}
catch
{
properties[i - 2] = "property" + (i - 1).ToString();
}
}
for (int r = 2; r <= inputrange.Rows.Count; r++)
{
control.progress.Report(r / inputrange.Rows.Count * 100);
var row = inputrange.Rows[r];
var label = "";
try
{
label = row.Cells[1, 1].Value2.ToString();
label = worksheet.Name;
}
catch
{
label = "NewExcelNode";
}
string cypher = "MERGE (a: " + label + " { ";
{
cypher += GetIdentifierPropertyValue(row, properties, indexOfIdentifier);
}
cypher = cypher.TrimEnd(',');
cypher += "})";
// check if worksheet have 1 property column + 1 data column
if (row.columns.count > 1)
{
cypher += " SET a += { ";
for (int i = 0; i < row.Columns.Count; i++)
{
int excelIndex = i + 1;
// Skip first record as it is used in Merge identifier.
if (i == indexOfIdentifier)
{
continue;
}
string secondRecordValue = Convert.ToString(row.Cells[1, excelIndex].Value2);
if (properties[i] != null && secondRecordValue != null)
{
if (properties[i].Length > 0 && secondRecordValue.Length > 0)
{
cypher += "`" + properties[i] + "`" + ": \"" + secondRecordValue + "\",";
}
}
}
cypher = cypher.TrimEnd(',');
cypher += "}";
}
try
{
IResultCursor cursor = await session.RunAsync(cypher);
var records = await cursor.ToListAsync();
var summary = await cursor.ConsumeAsync();
string message = summary.ToString();
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(message);
}
}
catch (Neo4jException ee)
{
CurrentControl.SetMessage(ee.Message);
}
}
await session.CloseAsync();
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
private void CreateNodes(object sender, SelectionArgs e)
{
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
if (_connected == false)
{
var control = _customTaskPane.Control as ExecuteQuery;
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
using (var session = _driver.Session())
{
if (inputrange.Columns.Count <= 1)
{
CurrentControl.SetMessage("Select more than 1 column");
}
string[] properties = new string[inputrange.Columns.Count];
for (int i = 2; i <= inputrange.Columns.Count; i++)
{
try
{
properties[i - 2] = inputrange.Cells[1, i].Value2.ToString();
}
catch
{
properties[i - 2] = "property" + (i - 1).ToString();
}
}
for (int r = 2; r <= inputrange.Rows.Count; r++)
{
var row = inputrange.Rows[r];
var label = "";
try
{
label = row.Cells[1, 1].Value2.ToString();
}
catch
{
label = "NewExcelNode";
}
string cypher = "MERGE (a: " + label + " { ";
for (int i = 2; i <= row.Columns.Count; i++)
{
cypher += properties[i - 2].ToString() + ": \"" + row.Cells[1, i].Value2.ToString() + "\",";
}
cypher = cypher.TrimEnd(',');
cypher += "})";
var result = session.Run(cypher);
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(result.Summary.Statement.Text);
}
}
}
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}
private void CreateRelationships(object sender, SelectionArgs e)
{
try
{
var worksheet = ((Worksheet)Application.ActiveSheet);
var inputrange = e.SelectionRange;
if (_connected == false)
{
var control = _customTaskPane.Control as ExecuteQuery;
ConnectDatabase(this, new ConnectDatabaseArgs {
ConnectionString = control.ConnectionString()
});
}
using (var session = _driver.Session())
{
if (inputrange.Columns.Count <= 1)
{
CurrentControl.SetMessage("Select 3 columns with nodes and relationship to create");
}
if (inputrange.Rows.Count <= 2)
{
CurrentControl.SetMessage("Select 2 header rows and 1 data row");
}
string label1 = inputrange.Cells[1, 1].Value2.ToString();
string label2 = inputrange.Cells[1, 2].Value2.ToString();
string relationshiptype = inputrange.Cells[1, 3].Value2.ToString();
string[] properties = new string[inputrange.Columns.Count];
for (int i = 1; i <= inputrange.Columns.Count; i++)
{
try
{
properties[i - 1] = inputrange.Cells[2, i].Value2.ToString();
}
catch
{
}
}
for (int r = 3; r <= inputrange.Rows.Count; r++)
{
var row = inputrange.Rows[r];
string cypher = "MATCH (a: {0}),(b: {1} ) WHERE a.{2} = '{3}' and b.{4} = '{5}' MERGE (a)-[r:{6} {7}]->(b)";
string relprop = "";
if (inputrange.Cells[r, 3].Value2.ToString().Length > 0 && inputrange.Cells[r, 4].Value2.ToString().Length > 0)
{
relprop = String.Format("{{ {0}: \"{1}\" }}", inputrange.Cells[r, 3].Value2.ToString(), inputrange.Cells[r, 4].Value2.ToString());
}
string formatedcypher = String.Format(
cypher,
label1,
label2,
properties[0],
inputrange.Cells[r, 1].Value2.ToString(),
properties[1],
inputrange.Cells[r, 2].Value2.ToString(),
relationshiptype, relprop);
var result = session.Run(formatedcypher);
if (r == inputrange.Rows.Count)
{
CurrentControl.SetMessage(result.Summary.Statement.Text);
}
}
}
}
catch (Neo4jException ex)
{
CurrentControl.SetMessage(ex.Message);
}
}