public void btnLogin(IRibbonControl control)
{
//_excel = new Application(null, ExcelDna.Integration.ExcelDnaUtil.Application);
using (var controller = new ExcelController(_excel, _thisRibbon, _curproj, _curview, _curprojname, _curviewname)) { controller.Login(); }
string s = "Contacting server...";
string caption = "Getting Projects";
using (View.WaitingForm wf = new View.WaitingForm(caption, s))
{
_projects = Projects.GetProjects(Utils.apiKey);
}
_thisRibbon.Invalidate();
}
public void WriteGH(bool csv = false)
{
bool viewexport = true;
string curview = _curview;
if (_curview.Equals("__GHALLDATA__"))
{
// not supported
System.Windows.Forms.DialogResult dlg = System.Windows.Forms.MessageBox.Show("We do not support reading from " + _curviewname + " in project " + _curprojname,
"Read from Glasshouse", System.Windows.Forms.MessageBoxButtons.OK);
return;
}
if (csv == false)
{
System.Windows.Forms.DialogResult dlg = System.Windows.Forms.MessageBox.Show("Are you sure you want to write data to Glasshouse project " + _curprojname,
"Write to Glasshouse", System.Windows.Forms.MessageBoxButtons.YesNo);
if (dlg == System.Windows.Forms.DialogResult.No)
{
return;
}
dlg = System.Windows.Forms.MessageBox.Show("Do you want to only update parameters in view " + _curviewname + "?",
"Write to Glasshouse - by specific view", System.Windows.Forms.MessageBoxButtons.YesNo);
if (dlg == System.Windows.Forms.DialogResult.No)
{
// limitation in API means data is dumped by "All Entries" view.
// API needs change so ALL info can be updated, ignoring whats in specific view
curview = "all_entries";
viewexport = false;
}
}
else
{
// limitation in API means data is dumped by "All Entries" view.
// API needs change so ALL info can be updated, ignoring whats in specific view
curview = "all_entries";
viewexport = false;
}
// allway read - glasshousejournalguid, short description
Range rngid = FindGUIDCell();
if (rngid == null)
{
System.Windows.Forms.MessageBox.Show("glasshousejournalguid not found in the first 10 by 10 cells");
return;
}
int idrow = rngid.Row;
int idcol = rngid.Column;
var removehcols = new[] { "glasshousejournalguid", "BIM Objects count", "BIM Objects quantity" };
// var removehcols = new[] { "glasshousejournalguid", "short description" };
var activeSheet = _excel.ActiveSheet as Worksheet;
Range usedRange = activeSheet.UsedRange;
int maxr = usedRange.Rows[1].Row + usedRange.Rows.Count - 1;
int maxc = usedRange.Columns[1].Column + usedRange.Columns.Count - 1;
System.Data.DataTable table = null;
System.Data.DataRow tablerow = null;
if (curview != null)
{
using (View.WaitingForm wf = new View.WaitingForm("Getting Entries For Update", "Contacting server..."))
{
table = JournalEntries.GetViewEntries(Utils.apiKey, _curproj, curview);
}
if (table.Rows.Count > 0)
{
tablerow = table.Rows[0];
}
else
{
System.Windows.Forms.MessageBox.Show("Did not find any good data in view " + _curviewname);
return;
}
}
else
{
// should never happen!?
System.Windows.Forms.MessageBox.Show("Error - view undefined");
return;
}
// Make dictinary of columns
List <gColumns> headers = new List <gColumns>();
List <string> updates = new List <string>();
List <string> updatesheader = new List <string>();
updatesheader.Add("GlassHouseJournalGUID");
for (int c = idcol; c <= maxc; c++)
{
if (activeSheet.Cells[idrow, c].Value2 == null)
{
continue;
}
string sc = activeSheet.Cells[idrow, c].Value2 as string;
if (sc.Length > 0)
{
gColumns gc = new gColumns();
gc.headerName = sc.Trim();
gc.headerNameLC = gc.headerName.ToLower();
gc.colNo = c;
gc.sync2gh = false;
if (tablerow != null)
{
int colno = 0;
foreach (object col in tablerow.ItemArray)
{
string colname = table.Columns[colno].ColumnName.ToLower().Trim();
colno++;
if (colname.Equals(gc.headerNameLC))
{
gc.GHcolNo = colno;
break;
}
}
}
//
if (activeSheet.Cells[idrow + 1, c].Value2 == null)
{
headers.Add(gc);
continue;
}
string syncway = (activeSheet.Cells[idrow + 1, c].Value2 as string).ToLower().Trim();
if (removehcols.Any(gc.headerNameLC.Contains))
{
headers.Add(gc);
continue;
}
if (syncway.Equals("update"))
{
gc.sync2gh = true;
updatesheader.Add(gc.headerName);
}
headers.Add(gc);
}
}
if (updatesheader.Count < 2)
{
System.Windows.Forms.MessageBox.Show("No parameters selected for updating");
return;
}
List <string> newupdatesheader = new List <string>();
if (viewexport == true)
{
headers = headers.OrderBy(o => o.GHcolNo).ToList();
foreach (gColumns gc in headers)
{
if (updatesheader.Any(gc.headerName.Contains))
{
newupdatesheader.Add(gc.headerName);
}
}
updates.Add(String.Join(",", newupdatesheader.Select(x => x.ToString()).ToArray()));
}
else
{
updates.Add(String.Join(",", updatesheader.Select(x => x.ToString()).ToArray()));
}
var removecols = new[] { "BIM Objects count", "BIM Objects quantity" };
maxr = Math.Max(maxr, idrow + 2);
int n = table.Rows.Count;
string s = "{0} of " + n.ToString() + " rows processed...";
string caption = "Preparing Data For Glasshouse";
using (ProgressForm pf = new ProgressForm(caption, s, n))
{
foreach (System.Data.DataRow row in table.Rows)
{
string rguid = (string)row[0];
int foundrow = -1;
for (int r = idrow + 2; r <= maxr; r++)
{
var guid = activeSheet.Cells[r, idcol].Value2;
if (guid == null)
{
continue;
}
string sguid = guid as string;
if (sguid.Length == 0)
{
continue;
}
if (rguid.Equals(sguid) == true)
{
foundrow = r;
break;
}
}
int colno = 0;
int activerow = foundrow;
if (foundrow == -1)
{
continue;
}
List <string> updatescol = new List <string>();
if (viewexport == true)
{
foreach (object col in row.ItemArray)
{
string colname = table.Columns[colno].ColumnName.ToLower().Trim();
colno++;
if (removecols.Any(colname.Contains))
{
continue;
}
gColumns match = headers.Find(v => v.headerNameLC.Equals(colname));
if (match == null)
{
continue;
}
if (match.sync2gh == false && match.headerNameLC.Equals("glasshousejournalguid") == false)
{
continue;
}
//add to update
var val = activeSheet.Cells[foundrow, match.colNo].Value2;
string sval = "-";
if (val != null)
{
sval = Utils.FormatWithQuotes(val.ToString());
}
updatescol.Add(sval as string);
}
}
else
{
foreach (gColumns gc in headers)
{
if (removecols.Any(gc.headerName.Contains))
{
continue;
}
if (gc.sync2gh == false && gc.headerNameLC.Equals("glasshousejournalguid") == false)
{
continue;
}
//add to update
var val = activeSheet.Cells[foundrow, gc.colNo].Value2;
string sval = "-";
if (val != null)
{
sval = Utils.FormatWithQuotes(val.ToString());
}
updatescol.Add(sval as string);
}
}
updates.Add(String.Join(",", updatescol.Select(x => x.ToString()).ToArray()));
//updates.Add(updatescol.Aggregate("", (string agg, string it) => agg += string.Format("{0} \"{1}\"", agg == "" ? "" : ",", it)));
pf.Increment();
}
}
if (updates.Count < 2)
{
System.Windows.Forms.MessageBox.Show("Nothing to update");
return;
}
var tempdir = System.IO.Path.GetTempPath();
string path = Path.Combine(tempdir, System.IO.Path.GetFileNameWithoutExtension(_excel.ActiveWorkbook.Name) + "_updateglasshouse.csv");
n = updates.Count;
s = "{0} of " + n.ToString() + " rows processed...";
caption = "Writing Data For Glasshouse";
using (ProgressForm pf = new ProgressForm(caption, s, n))
{
using (var w = new StreamWriter(path, false, Encoding.UTF8))
{
foreach (string us in updates)
{
w.WriteLine(us);
w.Flush();
}
}
}
if (csv == false)
{
if (UpdateJournalCVS(_curproj, path) == true)
{
System.Windows.Forms.MessageBox.Show("Glashouse updated", "Write To Glasshouse");
}
else
{
System.Windows.Forms.MessageBox.Show("Hmmm...something went wrong!", "Write To Glasshouse");
}
}
else
{
System.Windows.Forms.MessageBox.Show("CSV dumped at " + path, "Write To CSV File");
}
updates = null;
updatesheader = null;
headers = null;
table = null;
}
public void GetProjects()
{
Dictionary <string, object> dict = null;
string s = "Contacting server...";
string caption = "Getting Project List";
using (View.WaitingForm wf = new View.WaitingForm(caption, s))
{
dict = Projects.GetProjects(Utils.apiKey);
}
var activeSheet = _excel.ActiveSheet as Worksheet;
//activeSheet.Range("A1").Value = "Hello, World!";
var activeCell = _excel.ActiveCell as Range;
int c = 1; //activeCell.Column;
/*
* foreach (KeyValuePair<string, object> kvp in dict)
* {
* int r = activeCell.Row;
* activeSheet.Cells[r, c].Value = kvp.Key;
* r++;
* List<string> lst = kvp.Value as List<string>;
*
* foreach (string ls in lst)
* {
* activeSheet.Cells[r, c].Value = ls;
* r++;
* }
* c++;
* }
*/
// read from excel - var myArray = (object[,])range.Value2;
//write to excel
int startRow = activeCell.Row - 1;
int startCol = activeCell.Column - 1;
int rowCount = ((List <string>)dict.First().Value).Count + 1;
int columnCount = dict.Count;
int[] lowerBounds = new int[] { 1, 1 };
int[] lengths = new int[] { rowCount, columnCount };
var myArray = (object[, ])Array.CreateInstance(typeof(object), lengths, lowerBounds);
//var myArray = new object[rowCount, columnCount];
// Initialize the array.
foreach (KeyValuePair <string, object> kvp in dict)
{
int r = 1;
myArray[r, c] = kvp.Key;
r++;
List <string> lst = kvp.Value as List <string>;
foreach (string ls in lst)
{
myArray[r, c] = ls;
r++;
}
c++;
}
int resultRows = myArray.GetLength(0);
int resultCols = myArray.GetLength(1);
ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, activeSheet.Name);
ExcelReference target = new ExcelReference(startRow, startRow + resultRows - 1, startCol, startCol + resultCols - 1, sheet2.SheetId);
// this example is a bit too atomic; you probably want to disable
// screen updates and events a bit higher up in the call stack...
//dataRange.Application.ScreenUpdating = false;
//dataRange.Application.EnableEvents = false;
//ExcelReference target = new ExcelReference(5, 5);
// Finally setting the result into the target range.
//target.SetValue(myArray);
_excel.ScreenUpdating = false;
_excel.EnableEvents = false;
ExcelAsyncUtil.QueueAsMacro(() => { target.SetValue(myArray); });
_excel.ScreenUpdating = true;
_excel.EnableEvents = true;
//dataRange.Application.ScreenUpdating = true;
//dataRange.Application.EnableEvents = true;
/*
* MessageBox.Show("Hello!");
* var activeCell = new ExcelReference(5, 5);
* ExcelAsyncUtil.QueueAsMacro(() => XlCall.Excel(XlCall.xlcSelect, activeCell));
* //https://stackoverflow.com/questions/14896215/how-do-you-set-the-value-of-a-cell-using-excel-dna
* var write2Cell = new ExcelReference(15, 5);
* ExcelAsyncUtil.QueueAsMacro(() => { write2Cell.SetValue("Hello"); });
*/
}
/// <summary>
/// Will read entries from a specific view in Glasshouse. For all matching columns names, entries will be updated or added at the end of excel list
/// </summary>
public void ReadGH()
{
if (_curview.Equals("__GHALLDATA__"))
{
// not supported
System.Windows.Forms.DialogResult dlg2 = System.Windows.Forms.MessageBox.Show("We do not support reading from " + _curviewname + " in project " + _curprojname, "Read from Glasshouse", System.Windows.Forms.MessageBoxButtons.OK);
return;
}
System.Windows.Forms.DialogResult dlg = System.Windows.Forms.MessageBox.Show("Are you sure you want to read from view " + _curviewname + " in project " + _curprojname,
"Read from Glasshouse", System.Windows.Forms.MessageBoxButtons.YesNo);
if (dlg == System.Windows.Forms.DialogResult.No)
{
return;
}
// allways read - glasshousejournalguid, short description
Range rngid = FindGUIDCell();
if (rngid == null)
{
System.Windows.Forms.MessageBox.Show("glasshousejournalguid not found in the first 10 by 10 cells");
return;
}
int idrow = rngid.Row;
int idcol = rngid.Column;
var removehcols = new[] { "glasshousejournalguid", "BIM Objects count", "BIM Objects quantity" };
// var removehcols = new[] { "glasshousejournalguid", "short description" };
var activeSheet = _excel.ActiveSheet as Worksheet;
Range usedRange = activeSheet.UsedRange;
int maxr = usedRange.Rows[1].Row + usedRange.Rows.Count - 1;
int maxc = usedRange.Columns[1].Column + usedRange.Columns.Count - 1;
// Make dictinary of columns
List <gColumns> headers = new List <gColumns>();
for (int c = idcol; c <= maxc; c++)
{
if (activeSheet.Cells[idrow, c].Value2 == null)
{
continue;
}
string sc = activeSheet.Cells[idrow, c].Value2 as string;
if (sc.Length > 0)
{
gColumns gc = new gColumns();
gc.headerName = sc.Trim();
gc.headerNameLC = gc.headerName.ToLower();
gc.colNo = c;
gc.sync2gh = false;
//
if (removehcols.Any(gc.headerNameLC.Contains))
{
headers.Add(gc);
continue;
}
if (activeSheet.Cells[idrow + 1, c].Value2 == null)
{
headers.Add(gc);
continue;
}
string syncway = (activeSheet.Cells[idrow + 1, c].Value2 as string).ToLower().Trim();
if (syncway.Equals("update"))
{
gc.sync2gh = true;
}
headers.Add(gc);
}
}
System.Data.DataTable table = null;
string s = "Contacting server...";
string caption = "Getting View Entries";
using (View.WaitingForm pf = new View.WaitingForm(caption, s))
{
table = JournalEntries.GetViewEntries(Utils.apiKey, _curproj, _curview);
}
var removecols = new[] { "BIM Objects count", "BIM Objects quantity" };
int updateno = 0;
int newno = 0;
maxr = Math.Max(maxr, idrow + 2);
int n = table.Rows.Count;
s = "{0} of " + n.ToString() + " rows processed...";
caption = "Getting Data From Glasshouse";
List <string> guidsexist = new List <string>();
//write to excel
// Initialize the array.
Range range = activeSheet.Range(activeSheet.Cells[idrow, idcol], activeSheet.Cells[maxr, maxc]);
var myArray = (object[, ])range.Value2;
int resultRows = myArray.GetLength(0);
int resultCols = myArray.GetLength(1);
using (ProgressForm pf = new ProgressForm(caption, s, n))
{
foreach (System.Data.DataRow row in table.Rows)
{
string rguid = (string)row[0];
guidsexist.Add(rguid);
int foundrow = -1;
for (int r = 3; r <= resultRows; r++)
{
//var guid = activeSheet.Cells[r, idcol].Value2;
var guid = myArray[r, 1];
if (guid == null)
{
continue;
}
string sguid = guid as string;
if (sguid.Length == 0)
{
continue;
}
if (rguid.Equals(sguid) == true)
{
foundrow = r;
break;
}
}
int colno = 0;
int activerow = foundrow;
if (foundrow == -1)
{
maxr++; // new line
newno++;
resultRows++;
activerow = resultRows;
myArray = AddRow(myArray);
}
else
{
updateno++;
}
foreach (object col in row.ItemArray)
{
string colname = table.Columns[colno].ColumnName.ToLower().Trim();
colno++;
//if (removecols.Any(colname.Contains)) continue;
gColumns match = headers.Find(v => v.headerNameLC.Equals(colname));
if (match == null)
{
continue;
}
if (match.sync2gh == true)
{
continue;
}
//activeSheet.Cells[activerow, match.colNo].Value = col;
myArray[activerow, match.colNo - idcol + 1] = col;
}
pf.Increment();
}
}
//int resultRows = myArray.GetLength(0);
//int resultCols = myArray.GetLength(1);
ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, activeSheet.Name);
ExcelReference target = new ExcelReference(idrow - 1, maxr - 1, idcol - 1, maxc - 1, sheet2.SheetId);
_excel.ScreenUpdating = false;
_excel.EnableEvents = false;
ExcelAsyncUtil.QueueAsMacro(() => { target.SetValue(myArray); });
//_excel.ScreenUpdating = true;
//_excel.EnableEvents = true;
//_excel.Interactive = false;
//_excel.ScreenUpdating = false;
//_excel.EnableEvents = false;
// check not valid rows
List <int> notfoundrow = new List <int>();
int notfound = 0;
for (int r = idrow + 2; r <= maxr; r++)
{
var guid = activeSheet.Cells[r, idcol].Value2;
if (guid == null)
{
continue;
}
string sguid = guid as string;
if (sguid.Length == 0)
{
continue;
}
if (guidsexist.Contains(sguid) == false)
{
activeSheet.Cells[r, idcol].Font.Strikethrough = true;
notfound++;
}
else
{
activeSheet.Cells[r, idcol].Font.Strikethrough = false;
}
}
//_excel.Interactive = true;
_excel.ScreenUpdating = true;
_excel.EnableEvents = true;
table = null;
System.Windows.Forms.MessageBox.Show("Updated " + updateno + " entries, " + notfound + " obsolete and added " + newno + " new entries ", "Read From Glasshouse");
}
public void GetViewEntries()
{
/*
* if (_curview.Equals("__GHALLDATA__"))
* {
#if DEBUG
*
* System.Data.DataTable table2 = JournalEntries.GetAllViewEntries(Utils.apiKey, _curproj);
*
#endif
* // not supported
* System.Windows.Forms.DialogResult dlg = System.Windows.Forms.MessageBox.Show("We do not support reading from " + _curviewname + " in project " + _curprojname,
* "Read from Glasshouse", System.Windows.Forms.MessageBoxButtons.OK);
* return;
* }
*/
var activeSheet = _excel.ActiveSheet as Worksheet;
//activeSheet.Range("A1").Value = "Hello, World!";
var activeCell = _excel.ActiveCell as Range;
System.Data.DataTable table = null;
string s = "Contacting server...";
string caption = "Getting View Entries";
using (View.WaitingForm wf = new View.WaitingForm(caption, s))
{
if (_curview.Equals("__GHALLDATA__"))
{
table = JournalEntries.GetAllViewEntries(Utils.apiKey, _curproj);
}
else
{
table = JournalEntries.GetViewEntries(Utils.apiKey, _curproj, _curview);
}
}
int c = activeCell.Column;
//var removecols = new[] { "BIM Objects count", "BIM Objects quantity" };
//#if DEBUG
//var removehcols = new[] { "glasshousejournalguid" };
var removehcols = new[] { "glasshousejournalguid", "BIM Objects count", "BIM Objects quantity" };
//#else
// var removehcols = new[] { "glasshousejournalguid", "short description" };
//#endif
var allowedValues = new List <string> {
"---", "Update"
};
int n = table.Rows.Count;
s = "{0} of " + n.ToString() + " rows processed...";
caption = "Getting View Entries";
/*
* _excel.Interactive = false;
* _excel.ScreenUpdating = false;
* _excel.EnableEvents = false;
*
* using (ProgressForm pf = new ProgressForm(caption, s, n))
* {
* foreach (System.Data.DataColumn col in table.Columns)
* {
* //if (removecols.Any(col.ColumnName.Contains)) continue;
*
* int r = activeCell.Row;
* activeSheet.Cells[r, c].Value = col.ColumnName;
* r++;
* // add update keyword etc
* if (!removehcols.Any(col.ColumnName.ToLower().Contains))
* {
* activeSheet.Cells[r, c].AddCellListValidation(allowedValues);
* }
* //
* r++;
* foreach (System.Data.DataRow row in table.Rows)
* {
* activeSheet.Cells[r, c].Value = ((string)row[col]);
* r++;
* }
*
* c++;
* pf.Increment();
* }
*
* table = null;
* }
* _excel.Interactive = true;
* _excel.ScreenUpdating = true;
* _excel.EnableEvents = true;
*
*/
//write to excel
int startRow = activeCell.Row - 1;
int startCol = activeCell.Column - 1;
int rowCount = table.Rows.Count + 2;
int columnCount = table.Columns.Count;
int[] lowerBounds = new int[] { 1, 1 };
int[] lengths = new int[] { rowCount, columnCount };
var myArray = (object[, ])Array.CreateInstance(typeof(object), lengths, lowerBounds);
//var myArray = new object[rowCount, columnCount];
// Initialize the array.
for (int i = 1; i <= table.Columns.Count; i++)
{
myArray[1, i] = table.Columns[i - 1].ColumnName;
myArray[2, i] = null;
}
using (ProgressForm pf = new ProgressForm(caption, s, n))
{
int activeRow = 3;
foreach (System.Data.DataRow row in table.Rows)
{
int activeCol = 1;
foreach (object col in row.ItemArray)
{
myArray[activeRow, activeCol] = col.ToString();
activeCol++;
}
activeRow++;
}
pf.Increment();
}
int resultRows = myArray.GetLength(0);
int resultCols = myArray.GetLength(1);
ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, activeSheet.Name);
ExcelReference target = new ExcelReference(startRow, startRow + resultRows - 1, startCol, startCol + resultCols - 1, sheet2.SheetId);
_excel.ScreenUpdating = false;
_excel.EnableEvents = false;
ExcelAsyncUtil.QueueAsMacro(() => { target.SetValue(myArray); });
_excel.ScreenUpdating = true;
_excel.EnableEvents = true;
}
public void GetViewColumns()
{
var activeSheet = _excel.ActiveSheet as Worksheet;
//activeSheet.Range("A1").Value = "Hello, World!";
var activeCell = _excel.ActiveCell as Range;
List <string> headers = null;
string s = "Contacting server...";
string caption = "Getting View Columns/Parameters";
if (_curview.Equals("__GHALLDATA__"))
{
headers = _properties;
}
else
{
using (View.WaitingForm wf = new View.WaitingForm(caption, s))
{
headers = JournalEntries.GetViewColumns(Utils.apiKey, _curproj, _curview);
}
}
int c = 1; // activeCell.Column;
//int r = activeCell.Row;
var removecols = new[] { "BIM Objects count", "BIM Objects quantity" };
/*
* foreach (string h in headers)
* {
* //if (removecols.Any(h.Contains)) continue;
*
* activeSheet.Cells[r, c].Value = h;
*
* c++;
* }
*/
//write to excel
int startRow = activeCell.Row - 1;
int startCol = activeCell.Column - 1;
int rowCount = 1;
int columnCount = headers.Count;
int[] lowerBounds = new int[] { 1, 1 };
int[] lengths = new int[] { rowCount, columnCount };
var myArray = (object[, ])Array.CreateInstance(typeof(object), lengths, lowerBounds);
//var myArray = new object[rowCount, columnCount];
// Initialize the array.
foreach (string h in headers)
{
myArray[1, c] = h;
c++;
}
int resultRows = myArray.GetLength(0);
int resultCols = myArray.GetLength(1);
ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, activeSheet.Name);
ExcelReference target = new ExcelReference(startRow, startRow + resultRows - 1, startCol, startCol + resultCols - 1, sheet2.SheetId);
_excel.ScreenUpdating = false;
_excel.EnableEvents = false;
ExcelAsyncUtil.QueueAsMacro(() => { target.SetValue(myArray); });
_excel.ScreenUpdating = true;
_excel.EnableEvents = true;
}
public void GetViews()
{
var activeSheet = _excel.ActiveSheet as Worksheet;
//activeSheet.Range("A1").Value = "Hello, World!";
var activeCell = _excel.ActiveCell as Range;
Dictionary <string, object> dict;
string s = "Contacting server...";
string caption = "Getting View List";
using (View.WaitingForm wf = new View.WaitingForm(caption, s))
{
dict = Views.GetJournalViews(Utils.apiKey, _curproj);
}
int c = 1; // activeCell.Column;
/*
* foreach (KeyValuePair<string, object> kvp in dict)
* {
* int r = activeCell.Row;
* activeSheet.Cells[r, c].Value = kvp.Key;
* r++;
* List<string> lst = kvp.Value as List<string>;
*
* foreach (string ls in lst)
* {
* activeSheet.Cells[r, c].Value = ls;
* r++;
* }
* c++;
* }
*/
//write to excel
int startRow = activeCell.Row - 1;
int startCol = activeCell.Column - 1;
int rowCount = ((List <string>)dict.First().Value).Count + 1;
int columnCount = dict.Count;
int[] lowerBounds = new int[] { 1, 1 };
int[] lengths = new int[] { rowCount, columnCount };
var myArray = (object[, ])Array.CreateInstance(typeof(object), lengths, lowerBounds);
//var myArray = new object[rowCount, columnCount];
// Initialize the array.
foreach (KeyValuePair <string, object> kvp in dict)
{
int r = 1;
myArray[r, c] = kvp.Key;
r++;
List <string> lst = kvp.Value as List <string>;
foreach (string ls in lst)
{
myArray[r, c] = ls;
r++;
}
c++;
}
int resultRows = myArray.GetLength(0);
int resultCols = myArray.GetLength(1);
ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, activeSheet.Name);
ExcelReference target = new ExcelReference(startRow, startRow + resultRows - 1, startCol, startCol + resultCols - 1, sheet2.SheetId);
_excel.ScreenUpdating = false;
_excel.EnableEvents = false;
ExcelAsyncUtil.QueueAsMacro(() => { target.SetValue(myArray); });
_excel.ScreenUpdating = true;
_excel.EnableEvents = true;
// cboDepartamentos.Items.Add( New Microsoft.Office.Tools.Ribbon.RibbonDropDownItem With {.Label = line})
}
public void GetProjectInfo()
{
var activeSheet = _excel.ActiveSheet as Worksheet;
//activeSheet.Range("A1").Value = "Hello, World!";
var activeCell = _excel.ActiveCell as Range;
Dictionary <string, object> dict = null;
string s = "Contacting server...";
string caption = "Getting Project Information";
using (View.WaitingForm wf = new View.WaitingForm(caption, s))
{
dict = Projects.GetProjectInfo(Utils.apiKey, _curproj);
}
int c = 1; //activeCell.Column;
int r = 1; // activeCell.Row;
/*
* foreach (KeyValuePair<string, object> kvp in dict)
* {
*
* activeSheet.Cells[r, c].Value = kvp.Key;
*
* activeSheet.Cells[r, c + 1].Value = kvp.Value as string;
* r++;
*
*
* }
*/
//write to excel
int startRow = activeCell.Row - 1;
int startCol = activeCell.Column - 1;
int rowCount = dict.Count;
int columnCount = 2;
int[] lowerBounds = new int[] { 1, 1 };
int[] lengths = new int[] { rowCount, columnCount };
var myArray = (object[, ])Array.CreateInstance(typeof(object), lengths, lowerBounds);
//var myArray = new object[rowCount, columnCount];
// Initialize the array.
foreach (KeyValuePair <string, object> kvp in dict)
{
myArray[r, c] = kvp.Key;
myArray[r, c + 1] = kvp.Value as string;
r++;
}
int resultRows = myArray.GetLength(0);
int resultCols = myArray.GetLength(1);
ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, activeSheet.Name);
ExcelReference target = new ExcelReference(startRow, startRow + resultRows - 1, startCol, startCol + resultCols - 1, sheet2.SheetId);
_excel.ScreenUpdating = false;
_excel.EnableEvents = false;
ExcelAsyncUtil.QueueAsMacro(() => { target.SetValue(myArray); });
_excel.ScreenUpdating = true;
_excel.EnableEvents = true;
}
