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