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