static void DoResize(ExcelReference target)
{
// Get the current state for reset later
using (new ExcelEchoOffHelper())
using (new ExcelCalculationManualHelper())
{
ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);
// Get the formula in the first cell of the target
string formula = (string)Excel(xlfGetCell, 41, firstCell);
bool isFormulaArray = (bool)Excel(xlfGetCell, 49, firstCell);
if (isFormulaArray)
{
// Select the sheet and firstCell - needed because we want to use SelectSpecial.
using (new ExcelSelectionHelper(firstCell))
{
// Extend the selection to the whole array and clear
Excel(xlcSelectSpecial, 6);
ExcelReference oldArray = (ExcelReference)Excel(xlfSelection);
oldArray.SetValue(ExcelEmpty.Value);
}
}
// Get the formula and convert to R1C1 mode
bool isR1C1Mode = (bool)Excel(xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
{
object formulaR1C1Obj;
XlReturn formulaR1C1Return = TryExcel(xlfFormulaConvert, out formulaR1C1Obj, formula, true, false, ExcelMissing.Value, firstCell);
if (formulaR1C1Return != XlReturn.XlReturnSuccess || formulaR1C1Obj is ExcelError)
{
string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);
Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - formula might be too long when converted to R1C1 format.");
firstCell.SetValue("'" + formula);
return;
}
formulaR1C1 = (string)formulaR1C1Obj;
}
// Must be R1C1-style references
object ignoredResult;
//Debug.Print("Resizing START: " + target.RowLast);
XlReturn formulaArrayReturn = TryExcel(xlcFormulaArray, out ignoredResult, formulaR1C1, target);
//Debug.Print("Resizing FINISH");
// TODO: Find some dummy macro to clear the undo stack
if (formulaArrayReturn != XlReturn.XlReturnSuccess)
{
string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);
Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - result might overlap another array.");
// Might have failed due to array in the way.
firstCell.SetValue("'" + formula);
}
}
}
// [ExcelCommand(MenuName = "Range Tools", MenuText = "Square Selection")]
public static void ReadData_Click()
{
object[,] result = null;
// Get a reference to the current selection
ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
try
{
}
catch
{
result = new object[, ] {
{ "Unable to retrieve data." }
};
}
// Now create the target reference that will refer to Sheet 2, getting a reference that contains the SheetId first
// ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "Sheet2"); // Throws exception if no Sheet2 exists
// ... then creating the reference with the right size as new ExcelReference(RowFirst, RowLast, ColFirst, ColLast, SheetId)
int resultRows = result.GetLength(0);
int resultCols = result.GetLength(1);
//ExcelReference target = new ExcelReference(selection.RowFirst, selection.RowFirst + resultRows - 1,
ExcelReference target = new ExcelReference(0, 0 + resultRows - 1, // start from top
selection.ColumnLast + 1, selection.ColumnLast + resultCols, selection.SheetId);
// Finally setting the result into the target range.
target.SetValue(result);
}
public static void Write <T>(IEnumerable <T> data, IFormatter <T> formatter, ExcelReference caller)
{
Task.Factory.StartNew(() =>
{
ExcelAsyncUtil.QueueAsMacro(() =>
{
var table = new object[][] { formatter.Header }.Union(data.Select(t => formatter.FormatData(t)));
int rows = table.Count();
int columns = formatter.Header.Length;
var target = new ExcelReference(caller.RowFirst + 1, caller.RowFirst + rows, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId);
var output = new object[rows + 1, columns];
output[0, 0] = XlCall.Excel(XlCall.xlfGetFormula, target);
int rowIndex = 0, columnIndex = 0;
foreach (var row in table)
{
columnIndex = 0;
foreach (var cell in row)
{
output[rowIndex, columnIndex++] = cell;
}
rowIndex++;
}
target.SetValue(output);
});
});
}
/// <summary>
/// 设置单元格公式
/// </summary>
/// <param name="range"></param>
/// <param name="formula"></param>
public static void SetFormula(this ExcelReference range, string formula)
{
if (string.IsNullOrEmpty(formula))
{
//删除公式
range.ClearFormula();
return;
}
// Get the formula and convert to R1C1 mode
var isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
{
formulaR1C1 = (string)
XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, range);
}
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormula, out var ignoredResult, formulaR1C1, range);
if (retval != XlCall.XlReturn.XlReturnSuccess)
{
// TODO: Consider what to do now!?
// Might have failed due to array in the way.
range.SetValue("'" + formula);
}
}
public static object writeInto(ExcelReference refer, string value)
{
// dynamic application = ExcelDnaUtil.Application;
ExcelAsyncUtil.QueueAsMacro(() => refer.SetValue(value));
ExcelAsyncUtil.Uninitialize();
return(value);
}
public static void SumRange()
{
//xlcall.h
//https://code.msdn.microsoft.com/Excel-2010-Writing-791e9222/sourcecode?fileId=25565&pathId=1844590411
// Get a reference to the current selection
ExcelReference selection = null;;
try
{
selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
}
catch
{
return;
}
if (selection == null)
{
return;
}
object sum = XlCall.Excel(XlCall.xlfSum, selection);
ExcelReference target = new ExcelReference(0, 0, 0, 0); //activesheet.cells[1,1]
target.SetValue(sum);
}
public static string JsonListToRowsEasy(string contents)
{
var items = JArray.Parse(contents).Children();
ExcelReference origin = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
int i = origin.RowLast + 1;
string firstCellResult = null;
if (items.Any())
{
firstCellResult = items.First().ToString();
}
ExcelAsyncUtil.QueueAsMacro(() =>
{
foreach (var item in items.Skip(1))
{
ExcelReference r = new ExcelReference(i, origin.ColumnLast);
r.SetValue(item.ToString());
i++;
}
});
return(firstCellResult);
}
internal static Range WriteToRange(object[,] response, Range targetRange)
{
int rowsCount = response.GetLength(0);
int columnsCount = response.GetLength(1);
ExcelReference target = new ExcelReference(targetRange.Row - 1, targetRange.Row + rowsCount - 2, targetRange.Column - 1, targetRange.Column + columnsCount - 2);
target.SetValue(response);
return(targetRange.Resize[rowsCount, columnsCount]);
}
private static void WriteValue(int rowCursor, int columnCursor, object propertyOfItem)
{
if (propertyOfItem != null)
{
ExcelReference r = new ExcelReference(rowCursor, columnCursor);
var theValue = propertyOfItem.ToString();
if (long.TryParse(theValue, out var valueAsNumber))
{
r.SetValue(valueAsNumber);
}
else
{
r.SetValue(propertyOfItem.ToString());
}
}
}
static void DoResize(ExcelReference target)
{
try
{
XlCall.Excel(XlCall.xlcEcho, false);
// Get the formula in the first cell of the target
string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);
bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
if (isFormulaArray)
{
object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);
// Remember old selection and select the first cell of the target
string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] { firstCellSheet });
object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);
// Extend the selection to the whole array and clear
XlCall.Excel(XlCall.xlcSelectSpecial, 6);
ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
oldArray.SetValue(ExcelEmpty.Value);
XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
}
// Get the formula and convert to R1C1 mode
bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
{
// Set the formula into the whole target
formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
}
// Must be R1C1-style references
object ignoredResult;
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
if (retval != XlCall.XlReturn.XlReturnSuccess)
{
// TODO: Consider what to do now!?
// Might have failed due to array in the way.
firstCell.SetValue("'" + formula);
}
}
catch (Exception ex)
{
XlCall.Excel(XlCall.xlcAlert, ex.ToString(), true);
}
finally
{
XlCall.Excel(XlCall.xlcEcho, true);
}
}
public static string JsonListOfObjectsToTable(string contents)
{
var items = JArray.Parse(contents).Children();
ExcelReference origin = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
string firstCellResult = null;
List <string> titles = new List <string>();
if (items.Any())
{
var obj = items.First() as JObject;
if (obj != null)
{
titles = obj.Properties().Select(p => p.Name).ToList();
firstCellResult = titles.FirstOrDefault();
}
}
int rowCursor = origin.RowLast;
var originColumn = origin.ColumnLast;
ExcelAsyncUtil.QueueAsMacro(() =>
{
// Write titles
var titleCursor = originColumn + 1;
foreach (var title in titles.Skip(1))
{
ExcelReference r = new ExcelReference(rowCursor, titleCursor);
r.SetValue(title);
titleCursor++;
}
rowCursor++;
foreach (var item in items)
{
var columnCursor = originColumn;
foreach (var property in titles)
{
var obj = item as JObject;
if (obj != null)
{
var propertyOfItem = obj.Properties().SingleOrDefault(p => p.Name == property);
if (propertyOfItem != null)
{
ExcelReference r = new ExcelReference(rowCursor, columnCursor);
r.SetValue(propertyOfItem.Value.ToString());
columnCursor++;
}
}
}
rowCursor++;
}
});
return(firstCellResult);
}
/// <summary>
/// Dump data to excel
/// </summary>
/// <param name="selection"></param>
/// <param name="result"></param>
public static void DumpDatabaseResult(ExcelReference selection, object[,] result)
{
int resultRows = result.GetLength(0);
int resultCols = result.GetLength(1);
ExcelReference target = new ExcelReference(selection.RowLast + 1, selection.RowLast + resultRows,
selection.ColumnFirst, selection.ColumnFirst + resultCols - 1, selection.SheetId);
target.SetValue(result);
}
public static void SquareRange()
{
object[,] result;
// Get a reference to the current selection
ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
// Get the value of the selection
object selectionContent = selection.GetValue();
if (selectionContent is object[, ])
{
object[,] values = (object[, ])selectionContent;
int rows = values.GetLength(0);
int cols = values.GetLength(1);
result = new object[rows, cols];
// Process the values
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < cols; j++)
{
if (values[i, j] is double)
{
double val = (double)values[i, j];
result[i, j] = val * val;
}
else
{
result[i, j] = values[i, j];
}
}
}
}
else if (selectionContent is double)
{
double value = (double)selectionContent;
result = new object[, ] {
{ value *value }
};
}
else
{
result = new object[, ] {
{ "Selection was not a range or a number, but " + selectionContent.ToString() }
};
}
// Now create the target reference that will refer to Sheet 2, getting a reference that contains the SheetId first
ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "Sheet2"); // Throws exception if no Sheet2 exists
// ... then creating the reference with the right size as new ExcelReference(RowFirst, RowLast, ColFirst, ColLast, SheetId)
int resultRows = result.GetLength(0);
int resultCols = result.GetLength(1);
ExcelReference target = new ExcelReference(0, resultRows - 1, 0, resultCols - 1, sheet2.SheetId);
// Finally setting the result into the target range.
target.SetValue(result);
}
public static void ClearA2B5()
{
ExcelReference xlRef = new ExcelReference(1, 4, 0, 1);
int rows = xlRef.RowLast - xlRef.RowFirst + 1;
int cols = xlRef.ColumnLast - xlRef.ColumnFirst + 1;
object[,] values = new object[rows, cols]; // nulls
xlRef.SetValue(values);
MessageBox.Show("Done clearing!");
}
internal static bool WriteToRange(object[,] response)
{
//ExcelReference sheet2 = XlCall.Excel(XlCall.xlSheetId, "Sheet2") as ExcelReference;
Microsoft.Office.Interop.Excel.Range activeCell = ExcelApp.Application.ActiveCell;
int rowsCount = response.GetLength(0);
int columnsCount = response.GetLength(1);
//ExcelReference target = new ExcelReference(0, rowsCount - 1, 0, columnsCount - 1, sheet2.SheetId);
string address = activeCell.Address;
ExcelReference target = new ExcelReference(activeCell.Row, activeCell.Row + rowsCount - 1, activeCell.Column, activeCell.Column + columnsCount - 1);
return(target.SetValue(response));
}
static void DoResize(ExcelReference target)
{
object oldEcho = XlCall.Excel(XlCall.xlfGetWorkspace, 40);
object oldCalculationMode = XlCall.Excel(XlCall.xlfGetDocument, 14);
try {
XlCall.Excel(XlCall.xlcEcho, false);
XlCall.Excel(XlCall.xlcOptionsCalculation, 3);
string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);
bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
if (isFormulaArray)
{
object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfGetCell, 49, target);
object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);
string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] { firstCellSheet });
object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);
XlCall.Excel(XlCall.xlcSelectSpecial, 6);
ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
oldArray.SetValue(ExcelEmpty.Value);
XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
}
bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
{
formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
}
object ignoredResult;
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
if (retval != XlCall.XlReturn.XlReturnSuccess)
{
firstCell.SetValue("'" + formula);
}
}
finally {
XlCall.Excel(XlCall.xlcEcho, oldEcho);
XlCall.Excel(XlCall.xlcOptionsCalculation, oldCalculationMode);
}
}
public static string JsonListToRows(string contents, string path)
{
var items = JObject.Parse(contents).SelectTokens(path).Select(x => x.ToString());
ExcelReference origin = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
int i = origin.RowLast;
foreach (var item in items)
{
ExcelReference r = new ExcelReference(i, origin.ColumnLast);
r.SetValue(item);
i++;
}
return("Ok");
}
public static object GetLiveUpdate(string currencyPair)
{
var app = (Application)ExcelDnaUtil.Application;
var range = app.ActiveCell;
var reference = new ExcelReference(range.Row - 1, range.Row - 1, range.Column - 1, range.Column - 1);
Task.Factory.StartNew(() =>
{
for (var i = 1; i < 30; i++)
{
Thread.Sleep(1000);
ExcelAsyncUtil.QueueAsMacro(() => reference.SetValue(i));
}
var referenceDone = new ExcelReference(range.Row - 1, range.Row - 1, range.Column, range.Column);
ExcelAsyncUtil.QueueAsMacro(() => referenceDone.SetValue("All done!"));
});
return(0);
}
public override bool SetValue(object value)
{
return(_excelReference.SetValue(value));
}
public static object CallApi(string path, OpenApiOperation operation, Dictionary <string, object> paramsArgs, string stringParams)
{
ExcelReference origin = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
var options = new List <string>();
if (!string.IsNullOrWhiteSpace(stringParams))
{
options = stringParams.Split(',').ToList();
}
var paramsDict = string.Join("", paramsArgs.Select(x => $"{x.Key}, {x.Value}"));
return(ExcelAsyncUtil.Run("CallApi", new[] { path, operation.OperationId, paramsDict }, () =>
{
var client = Resolver.Instance.Create <SwaggerClient>();
var result = client.Call(path, operation.OperationId, paramsArgs).ConfigureAwait(false).GetAwaiter().GetResult();
if (options.Contains("raw"))
{
return result;
}
if (options.Any(x => x.StartsWith("path")))
{
var pathString = options.First(x => x.StartsWith("path"));
if (pathString.Contains("="))
{
var jsonPath = pathString.Split('=')[1];
var match = JToken.Parse(result).SelectToken(jsonPath);
if (match != null)
{
return match.ToString();
}
else
{
return $"No match for {jsonPath}";
}
}
}
// If result is list, try to expand out fields
var jsonToken = JToken.Parse(result);
if (jsonToken is JArray)
{
var thing = (JArray)jsonToken;
var items = thing.Children();
string firstCellResult = null;
List <string> titles = new List <string>();
if (items.Any())
{
var obj = items.First() as JObject;
if (obj != null)
{
titles = obj.Properties().Select(p => p.Name).ToList();
firstCellResult = titles.FirstOrDefault();
}
}
if (options.Any(o => o.StartsWith("fields")))
{
var fieldsString = options.First(x => x.StartsWith("fields"));
var fieldsToInclude = fieldsString.Split('=')[1].Split(';');
titles = titles.Where(t => fieldsToInclude.Contains(t)).ToList();
}
int rowCursor = origin.RowLast;
var originColumn = origin.ColumnLast;
ExcelAsyncUtil.QueueAsMacro(() =>
{
// Write titles
var titleCursor = originColumn + 1;
foreach (var title in titles.Skip(1))
{
ExcelReference r = new ExcelReference(rowCursor, titleCursor);
r.SetValue(title);
titleCursor++;
}
Application xlApp = (Application)ExcelDnaUtil.Application;
Workbook wb = xlApp.ActiveWorkbook;
Worksheet ws = wb.ActiveSheet;
rowCursor++;
foreach (var item in items)
{
var columnCursor = originColumn;
foreach (var property in titles)
{
var obj = item as JObject;
if (obj != null)
{
var propertyOfItem = obj.Properties().SingleOrDefault(p => p.Name == property);
WriteValue(rowCursor, columnCursor, propertyOfItem);
columnCursor++;
}
}
rowCursor++;
}
});
return firstCellResult;
}
if (jsonToken is JObject)
{
var item = (JObject)jsonToken;
var properties = item.DeserializeAndFlatten();
string firstCellResult = null;
if (properties.Any())
{
firstCellResult = properties.First().Key;
}
var titleColumn = origin.ColumnLast;
var valuesColumn = titleColumn + 1;
var i = origin.RowLast;
ExcelAsyncUtil.QueueAsMacro(() =>
{
foreach (var pair in properties)
{
// Skip first
if (i != origin.RowLast)
{
ExcelReference r = new ExcelReference(i, titleColumn);
r.SetValue(pair.Key.ToString());
}
WriteValue(i, valuesColumn, pair.Value);
i++;
}
});
return firstCellResult;
}
return result;
}));
}
// This function will run in the UDF context.
// Needs extra protection to allow multithreaded use.
public static object Resize(object[,] array)
{
var caller = Excel(xlfCaller) as ExcelReference;
if (caller == null)
{
return(array);
}
var rows = array.GetLength(0);
var columns = array.GetLength(1);
if (rows == 0 || columns == 0)
{
return(array);
}
if ((caller.RowLast - caller.RowFirst + 1 == rows) &&
(caller.ColumnLast - caller.ColumnFirst + 1 == columns))
{
// Size is already OK - just return result
return(array);
}
var rowLast = caller.RowFirst + rows - 1;
var columnLast = caller.ColumnFirst + columns - 1;
// Check for the sheet limits
if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||
columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)
{
// Can't resize - goes beyond the end of the sheet - just return #VALUE
// (Can't give message here, or change cells)
return(ExcelError.ExcelErrorValue);
}
// TODO: Add some kind of guard for ever-changing result?
if (columns > 1)
{
ExcelAsyncUtil.QueueAsMacro(() =>
{
var target = new ExcelReference(caller.RowFirst, caller.RowFirst, caller.ColumnFirst + 1, columnLast);
object[] firstRow = new object[columns - 1];
for (int i = 1; i < columns; i++)
{
firstRow[i - 1] = array[0, i];
}
target.SetValue(firstRow);
});
}
if (rows > 1)
{
ExcelAsyncUtil.QueueAsMacro(() =>
{
var target = new ExcelReference(caller.RowFirst + 1, rowLast, caller.ColumnFirst, columnLast);
object[,] data = new object[rows - 1, columns];
for (int i = 1; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
data[i - 1, j] = array[i, j];
}
}
target.SetValue(data);
});
}
// Return what we have - to prevent flashing #N/A
return(array);
}
static void DoResize(ExcelReference target)
{
object oldEcho = XlCall.Excel(XlCall.xlfGetWorkspace, 40);
object oldCalculationMode = XlCall.Excel(XlCall.xlfGetDocument, 14);
try
{
// Get the current state for reset later
XlCall.Excel(XlCall.xlcEcho, false);
XlCall.Excel(XlCall.xlcOptionsCalculation, 3);
// Get the formula in the first cell of the target
string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);
bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
if (isFormulaArray)
{
object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);
// Remember old selection and select the first cell of the target
string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] { firstCellSheet });
object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);
// Extend the selection to the whole array and clear
XlCall.Excel(XlCall.xlcSelectSpecial, 6);
ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
oldArray.SetValue(ExcelEmpty.Value);
XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
}
// Get the formula and convert to R1C1 mode
bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
{
// Set the formula into the whole target
formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
}
// Must be R1C1-style references
object ignoredResult;
//Debug.Print("Resizing START: " + target.RowLast);
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
//Debug.Print("Resizing FINISH");
// TODO: Dummy action to clear the undo stack
if (retval != XlCall.XlReturn.XlReturnSuccess)
{
// TODO: Consider what to do now!?
// Might have failed due to array in the way.
firstCell.SetValue("'" + formula);
}
}
finally
{
XlCall.Excel(XlCall.xlcEcho, oldEcho);
XlCall.Excel(XlCall.xlcOptionsCalculation, oldCalculationMode);
}
}
/// <summary>
/// copy a variant matrix into an excel named range and adjust the size of the named range
/// 1.) will copy the formatting of the first row when adding new rows
/// 2.) will copy the formatting of the first row after the named range when removing rows
/// </summary>
/// <param name="vt">should be object[,] or simple data type</param>
/// <param name="outRange"></param>
/// <param name="localName">This is the local name of the output range (always ex header)</param>
/// <param name="header">if there is a header the named range will start one cell below</param>
/// <param name="ignoreFirstCell">will not fill the first cell; header will be inside the range if true</param>
public static void Fill(this ExcelReference outRange, object vt, string localName = null, bool header = false, bool ignoreFirstCell = false)
{
var _vt = vt as object[, ];
if (_vt == null)
{
_vt = new object[, ] {
{ vt }
}
}
;
int name_offset = (header && ignoreFirstCell) ? 1 : 0;
int origin_offset = ((header && !ignoreFirstCell) ? -1 : 0);
int header_offset = (header) ? -1 : 0;
int n = _vt.GetLength(0), k = _vt.GetLength(1);
int m = outRange.RowLast - outRange.RowFirst + 1;
//formatting
bool localRange = !string.IsNullOrEmpty(localName);
bool format = true;
ExcelReference formatRange = null, newRange = null;
if (m == 1 && localRange)
{
formatRange = Name.GetRange(string.Format("'{0}'!{1}", outRange.SheetRef(), localName));
if (formatRange == null)
{
format = false;
}
else
{
m = formatRange.RowLast - formatRange.RowFirst + 1;
}
}
else if (m == 1)
{
format = false;
}
bool addRows = n + header_offset > m && format;
bool removeRows = n + header_offset < m && format;
int x0 = outRange.RowFirst + origin_offset, y0 = outRange.ColumnFirst; //output origin
int x1 = outRange.RowFirst + name_offset, y1 = outRange.ColumnFirst; //name origin
bool updating = XLApp.ScreenUpdating;
xlCalculation calcMode = XLApp.Calcuation;
if (updating)
{
XLApp.ScreenUpdating = false;
}
try
{
var fillRange = new ExcelReference(x0, x0 + n - 1, y0, y0 + k - 1, outRange.SheetId);
if (addRows)
{
formatRange = new ExcelReference(x1, x1, y1, y1 + k - 1, outRange.SheetId); //first row
newRange = new ExcelReference(x1, x1 + n + header_offset - 1, y1, y1 + k - 1, outRange.SheetId);
}
if (removeRows)
{
formatRange = new ExcelReference(x1 + m, x1 + m, y1, y1 + k - 1, outRange.SheetId); //last row + 1
newRange = new ExcelReference(x1 + n + header_offset, x1 + m - 1, y1, y1 + k - 1, outRange.SheetId);
newRange.ClearContents();
}
//set the range except the first cell
if (ignoreFirstCell && n > 1)
{
//first row
if (k > 1)
{
object[,] first = new object[1, k - 1];
for (int i = 0; i < k - 1; i++)
{
first[0, i] = _vt[0, i + 1];
}
fillRange.Offset(0, 1).Resize(1, k - 1).SetValue(first);
}
//all other rows
object[,] rest = new object[n - 1, k];
for (int i = 1; i < n; i++)
{
for (int j = 0; j < k; j++)
{
rest[i - 1, j] = _vt[i, j];
}
}
fillRange.Offset(1, 0).Resize(n - 1, k).SetValue(rest);
}
else if (!ignoreFirstCell)
{
fillRange.SetValue(_vt);
}
//set name
if (localRange)
{
Action action = () =>
{
string sheetref = (string)XlCall.Excel(XlCall.xlSheetNm, outRange);
Worksheet sheet = new Worksheet(sheetref);
//re-color
if (addRows || removeRows)
{
formatRange.Select();
XLApp.Copy();
newRange.Select();
XLApp.PasteSpecial(xlPasteType.PasteFormats);
}
string reference = string.Format("='{4}'!R{0}C{2}:R{1}C{3}", x1 + 1, x1 + n + header_offset, y1 + 1, y1 + k, sheetref);
//DEFINE.NAME(name_text, refers_to, macro_type, shortcut_text, hidden, category, local)
XlCall.Excel(XlCall.xlcDefineName, localName, reference, Type.Missing, Type.Missing, false, Type.Missing, true);
};
XLApp.ActionOnSelectedRange(fillRange, action);
}
}
finally
{
if (updating)
{
XLApp.ScreenUpdating = true;
}
}
}
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>Gets the Excel range output for a specific array.
/// </summary>
/// <typeparam name="T1">The type of the first item.</typeparam>
/// <typeparam name="T2">The type of the second item.</typeparam>
/// <param name="values">The collection of values.</param>
/// <param name="header1">An optional header for the first item.</param>
/// <param name="header2">An optional header for the second item.</param>
/// <returns>The Excel range which contains the <paramref name="values"/>.</returns>
public static object GetExcelRangeOutput <T1, T2>(IEnumerable <Tuple <T1, T2> > values, string header1 = null, string header2 = null, RangeOutputType rangeOutputType = RangeOutputType.Standard)
{
if (rangeOutputType == RangeOutputType.Standard)
{
ExcelLowLevel.OutputRangeOrientation rangeOrientation;
int excelRangeRowCount, excelRangeColumnCount;
ExcelLowLevel.GetRangeSize(out excelRangeRowCount, out excelRangeColumnCount, out rangeOrientation);
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Transposed) // swap the Excel Range size given by the user
{
int temp = excelRangeRowCount;
excelRangeRowCount = excelRangeColumnCount;
excelRangeColumnCount = temp;
}
object[,] returnValue = new object[excelRangeRowCount, excelRangeColumnCount];
for (int i = 0; i < excelRangeRowCount; i++)
{
for (int j = 0; j < excelRangeColumnCount; j++)
{
returnValue[i, j] = String.Empty;
}
}
bool containsHeader = (header1 != null) || (header2 != null);
int valueRowCount = values.Count() + (containsHeader == true ? 1 : 0);
if (valueRowCount > excelRangeRowCount)
{
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Regular)
{
returnValue[0, 0] = "ERROR: At least " + valueRowCount + " x 2 Range needed.";
}
else
{
returnValue[0, 0] = "ERROR: At least 2 x " + valueRowCount + " Range needed.";
}
return(returnValue);
}
if (header1 != null)
{
returnValue[0, 0] = header1;
}
if (header2 != null)
{
returnValue[0, 1] = header2;
}
int k = 0 + (containsHeader == true ? 1 : 0);
foreach (var value in values)
{
returnValue[k, 0] = value.Item1;
returnValue[k++, 1] = value.Item2;
}
return(returnValue);
}
else // write the result below the cell where the user has called the specified UDF
{
int firstRowIndex, firstColumnIndex;
ExcelLowLevel.GetCurrentRangePosition(out firstRowIndex, out firstColumnIndex);
ExcelAsyncUtil.QueueAsMacro(() =>
{
int k = 0;
foreach (var value in values)
{
var firstCellReference = new ExcelReference(firstRowIndex + k + 1, firstColumnIndex);
firstCellReference.SetValue(value.Item1);
var secondCellReference = new ExcelReference(firstRowIndex + k + 1, firstColumnIndex + 1);
secondCellReference.SetValue(value.Item2);
k++;
}
});
var returnValue = new object[1, 2];
if (header1 != null)
{
returnValue[0, 0] = header1;
}
if (header2 != null)
{
returnValue[0, 1] = header2;
}
return(returnValue);
}
}
/// <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;
}
