/// <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 override bool SetResult(object result) { // Typically called from a completely independent thread, e.g. a threadpool worker, // so any exception here would crash Excel. object unusedResult; XlCall.XlReturn callReturn = XlCall.TryExcel(XlCall.xlAsyncReturn, out unusedResult, this, result); if (callReturn == XlCall.XlReturn.XlReturnSuccess) { // The normal case - value has been accepted return(true); } if (callReturn == XlCall.XlReturn.XlReturnInvAsynchronousContext) { // This is expected sometimes (e.g. calculation was cancelled) // Excel will show #VALUE Debug.WriteLine("Warning: InvalidAsyncContext returned from xlAsyncReturn"); return(false); } // This is never unexpected Debug.WriteLine("Error: Unexpected error from xlAsyncReturn"); return(false); }
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); } }
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)XlCall.Excel(XlCall.xlfGetCell, 41, firstCell); bool isFormulaArray = (bool)XlCall.Excel(XlCall.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 XlCall.Excel(XlCall.xlcSelectSpecial, 6); ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection); oldArray.SetValue(ExcelEmpty.Value); } } // Get the formula and convert to R1C1 mode bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4); string formulaR1C1 = formula; if (!isR1C1Mode) { object formulaR1C1Obj; XlCall.XlReturn formulaR1C1Return = XlCall.TryExcel(XlCall.xlfFormulaConvert, out formulaR1C1Obj, formula, true, false, ExcelMissing.Value, firstCell); if (formulaR1C1Return != XlCall.XlReturn.XlReturnSuccess || formulaR1C1Obj is ExcelError) { string firstCellAddress = (string)XlCall.Excel(XlCall.xlfReftext, firstCell, true); XlCall.Excel(XlCall.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); XlCall.XlReturn formulaArrayReturn = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target); //Debug.Print("Resizing FINISH"); // TODO: Find some dummy macro to clear the undo stack if (formulaArrayReturn != XlCall.XlReturn.XlReturnSuccess) { string firstCellAddress = (string)XlCall.Excel(XlCall.xlfReftext, firstCell, true); XlCall.Excel(XlCall.xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - result might overlap another array."); // Might have failed due to array in the way. firstCell.SetValue("'" + formula); } } }
private static object GetApplication() { // Don't cache the one we get from the Window, it keeps Excel alive! // (?? Really ?? - Probably only when we're not on the main thread...) object application = GetApplicationFromWindows(); if (application != null) { return(application); } // DOCUMENT: Under some circumstances, the C API and Automation interfaces are not available. // This happens when there is no Workbook open in Excel. // Now make workbook with VBA sheet, according to some Google post.. // We try a (possible) test for whether we can call the C API. object output; XlCall.XlReturn result = XlCall.TryExcel(XlCall.xlGetName, out output); if (result == XlCall.XlReturn.XlReturnFailed) { // no plan for getting Application (we're probably on a different thread?) throw new InvalidOperationException("Excel API is unavailable - cannot retrieve Application object."); } // Create new workbook with the right stuff // Echo calls removed for Excel 2013 - this caused trouble in the Excel 2013 'browse' scenario. bool isExcelPre15 = SafeIsExcelVersionPre15; if (isExcelPre15) { XlCall.Excel(XlCall.xlcEcho, false); } XlCall.Excel(XlCall.xlcNew, 5); XlCall.Excel(XlCall.xlcWorkbookInsert, 6); // Try again application = GetApplicationFromWindows(); // Clean up XlCall.Excel(XlCall.xlcFileClose, false); if (isExcelPre15) { XlCall.Excel(XlCall.xlcEcho, true); } if (application != null) { return(application); } // This is really bad - throwing an exception ... throw new InvalidOperationException("Excel Application object could not be retrieved."); }
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); } }
// Returns true if the Excel call succeeded. private static bool TryCallRTD(out object result, string progId, string server, params string[] topics) { object[] args = new object[topics.Length + 2]; args[0] = progId; args[1] = null; topics.CopyTo(args, 2); XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlfRtd, out result, args); if (retval == XlCall.XlReturn.XlReturnSuccess) { return(true); } Debug.Print("RTD Call failed. Excel returned {0}", retval); result = null; return(false); }
private static object GetApplication() { // Get main window as well as we can. IntPtr hWndMain = WindowHandle; if (hWndMain == IntPtr.Zero) { return(null); // This is a problematic error case! } // Don't cache the one we get from the Window, it keeps Excel alive! object application; application = GetApplicationFromWindow(hWndMain); if (application == null) { // I assume it failed because there was no workbook open // Now make workbook with VBA sheet, according to some Google post // CONSIDER: Alternative of sending WM_USER+18 to Excel - KB 147573 // And trying to retrieve Excel from the ROT using GetActiveObject // Concern then is whether it is the right instance of the Excel.Application for this process. // DOCUMENT: Under some circumstances, the C API and Automation interfaces are not available. // This happens when there is no Workbook open in Excel. // We try a (possible) test for whether we can call the C API. object output; XlCall.XlReturn result = XlCall.TryExcel(XlCall.xlGetName, out output); if (result == XlCall.XlReturn.XlReturnFailed) { // no plan for getting Application (we're probably on a different thread?) throw new InvalidOperationException("Excel API is unavailable - cannot retrieve Application object."); } // Create new workbook with the right stuff XlCall.Excel(XlCall.xlcEcho, false); XlCall.Excel(XlCall.xlcNew, 5); XlCall.Excel(XlCall.xlcWorkbookInsert, 6); // Try again application = GetApplicationFromWindow(hWndMain); // Clean up XlCall.Excel(XlCall.xlcFileClose, false); XlCall.Excel(XlCall.xlcEcho, true); } return(application); }
// This call might throw an access violation // .NET40: If this assembly is compiled for .NET 4, add this attribute to get the expected behaviour. // (Also for CallPenHelper) // [HandleProcessCorruptedStateExceptions] private static void CheckExcelApiAvailable() { try { object output; XlCall.XlReturn result = XlCall.TryExcel(XlCall.xlGetName, out output); if (result == XlCall.XlReturn.XlReturnFailed) { // no plan for getting Application (we're probably on a different thread?) throw new InvalidOperationException("Excel API is unavailable - cannot retrieve Application object."); } } catch (AccessViolationException ave) { throw new InvalidOperationException("Excel API is unavailable - cannot retrieve Application object. Excel may be shutting down", ave); } }
// Returns true if the Excel call succeeded. private static bool TryCallRTD(out object result, string progId, string server, params string[] topics) { object[] args = new object[topics.Length + 2]; args[0] = progId; args[1] = null; topics.CopyTo(args, 2); XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlfRtd, out result, args); if (retval == XlCall.XlReturn.XlReturnSuccess) { // All is good return(true); } if (retval == XlCall.XlReturn.XlReturnUncalced) { // An expected error - the first call in an array-group seems to always return this, // to be followed by one call for each element in the array (where xlfRtd succceeds). Debug.Print("### RTD Call failed. Excel returned {0}", retval); result = null; return(false); } // Unexpected error - throw for the user to deal with throw new XlCallException(retval); }
// We could do something like this: public static bool SetResults(object[] asyncHandles, object[] results) { object unusedResult; XlCall.XlReturn callReturn = XlCall.TryExcel(XlCall.xlAsyncReturn, out unusedResult, asyncHandles, results); if (callReturn == XlCall.XlReturn.XlReturnSuccess) { // The normal case - value has been accepted return(true); } if (callReturn == XlCall.XlReturn.XlReturnInvAsynchronousContext) { // This is expected sometimes (e.g. calculation was cancelled) // Excel will show #VALUE Debug.WriteLine("Warning: InvalidAsyncContext returned from xlAsyncReturn []"); return(false); } // This is never unexpected Debug.WriteLine("Error: Unexpected error from xlAsyncReturn []"); return(false); }
public XlCallException(XlCall.XlReturn xlReturn) { this.xlReturn = xlReturn; }
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); } }
public static T Convert <T>(object i, out bool typeErr) { Type type = typeof(T); typeErr = false; T res; if (type == typeof(bool) && IsType <bool>(i)) { res = (T)(object)ObjectConv.Logical(i); } else { if (type == typeof(DateTime) && IsType <DateTime>(i)) { object o; double d; XlCall.XlReturn r = XlCall.TryExcel(XlCall.xlCoerce, out o, i); if (r == XlCall.XlReturn.XlReturnSuccess && double.TryParse(o.ToString(), out d)) { res = (T)(object)DateTime.FromOADate(d); } else { typeErr = true; res = (T)(object)DateTime.MinValue; } } else { if (IsType <double>(i) && type == typeof(int)) { double t = (double)i; res = (T)(object)System.Convert.ToInt32(t); } else { if (type == typeof(string) && !(i is ExcelEmpty)) { res = (T)(object)i.ToString(); } else { if (IsType <T>(i)) { res = (T)i; } else { typeErr = (i is ExcelEmpty) ? false : true; if (type == typeof(string)) { res = (T)(object)string.Empty; } else { res = default(T); } } } } } } return(res); }