如何防止绿色警告三角形在公式界定单元格（C＃Excel Interop）中显示？ [英] How can I prevent the green warning triangle from displaying in a formula-bound cell (C# Excel Interop)?

问题描述

我有这个公式生成正确的数据：

  var avgWeeklyDeliveriesCell =（Range）_xlSheetDelPerf.Cells [curDelPerfRow ，AVG_WEEKLY_DELIVERIES_COLUMN]; 
 avgWeeklyDeliveriesCell.Value2 = string.Format（= ROUND（AVERAGE（C {0}：I {0}），2），curDelPerfRow）; 
  

问题是它反思/微管理问题，并想知道相邻的单元格在左边，我假设总订单）应该包含在公式中。

具体来说，如果我点击绿色三角形，一个！字形弹出点击它有一个msg，公式忽略相邻单元格

因此，它用绿色三角形标记所有公式界定的单元格。

以下是它的外观：

如您所见，具有公式的列总是运行NW角中的小绿色三角形。公式是正确的（平均值从Sun订单到星期六订单）包含在内。

我可以做什么来告诉Excel冷却它和避免显示小绿色三角形？

更新

这两个答案似乎都是合理的，但都不行。

尽管如此，我必须承认，我的主要Excel对象与Varocarbas有点不同。我的代码是：

 使用Microsoft.Office.Interop.Excel; 
使用Application = System.Windows.Forms.Application; 
。 。 。 
 private ApplicationClass _xlApp; 
。 。 。 
 _xlApp = new ApplicationClass {UserControl = true}; 
 _xlApp.ErrorCheckingOptions.BackgroundChecking = false; 
  

IOW，我正在使用ApplicationClass而不是Excel.Application。

更新2

我将代码更改为（不再使用ApplicationClass）：

 使用Excel = Microsoft.Office.Interop.Excel; 
。 。 。 
 // private Excel.ApplicationClass _xlApp; 
 private Excel.Application _xlApp = new Excel.Application（）; 
。 。 。 
 
 // _ xlApp = new Excel.ApplicationClass {UserControl = true}; 
 _xlApp = new Excel.Application（）; 
 _xlApp.ErrorCheckingOptions.BackgroundChecking = false; 
  

...但我仍然会得到一点绿色的三角形。更糟糕的是，我开始听到Bobby Goldsboro在我脑海中唱歌God did not make little green triangle。

更新3

我认为问题可能不是正确关闭Excel进程。在任务管理器中，我看到不少EXCEL.EXE * 32实例。我一定不会有86个Excel进程;这里是我认为应该这样做的代码：

  foreach（在SelectedUnits中的DataRowView drv）
 {
 Application.DoEvents（）; 
 _xlApp = new Excel.Application（）; 
 _xlApp.ErrorCheckingOptions.BackgroundChecking = false; 
 
 _xlBook = _xlApp.Workbooks.Add（Type.Missing）; 
 _xlBook.Worksheets.Add（Type.Missing，Type.Missing，Type.Missing，Type.Missing）; 
 _xlApp.ActiveWindow.DisplayGridlines = false; 
 _xlApp.SheetsInNewWorkbook = 1; //防止空的工作表2等。
 _xlSheets = _xlBook.Worksheets; 
 
 _xlSheet =（Excel.Worksheet）_xlSheets.Item [1]; 
 if（_xlSheet！= null）
 {
 _xlSheet.Name = ProduceUsageByMonthSheetName; 
。 。 。 
 var filename = Properties.Settings.Default.OutputDirectory + shortUnitName +.xlsx; 
 if（File.Exists（filename））
 {
 File.Delete（filename）; 
} 
 
 _xlBook.SaveAs（Properties.Settings.Default.OutputDirectory + shortUnitName +.xlsx，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type。缺少Excel.XlSaveAsAccessMode.xlExclusive，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing）; 
 Marshal.ReleaseComObject（_xlSheet）; 
 _xlBook.Close（false，null，null）; 
 Marshal.ReleaseComObject（_xlBook）; 
 _xlApp.DisplayAlerts = false; 
 _xlApp.Quit（）; 
 _xlSheets = null; 
} 
 _xlSheet = null; 
 _xlBook = null; 
 _xlApp = null; 
 OnChanged（EventArgs.Empty）; 
} // foreach（在SelectedUnits中的DataRowView drv）
  

更新4

我不能肯定这是最好的方法，但发布顺序对我来说是有意义的（表格，然后是表格，然后是书，然后应用程序），以及从经验上说，我不仅不再看到绿色的小事，而且我没有更多的无关紧要/多余的Excel隐藏在devbox的内部。

这里是我改变了上面的版本和空的混乱：

 。 。 。 
} 
 Marshal.ReleaseComObject（_xlSheet）; 
 
 Marshal.ReleaseComObject（_xlSheets）; 
 
 _xlBook.Close（false，null，null）; 
 Marshal.ReleaseComObject（_xlBook）; 
 
 _xlApp.DisplayAlerts = false; 
 _xlApp.Quit（）; 
} // foreach（SelectUnits中的DataRowView drv）
 Marshal.ReleaseComObject（_xlApp）; 
  

我可能不需要它（如_xlBook.Close（）和_xlApp.Quit）但是至少它是这样工作的。

解决方案

这个绿色的三角形表示给定单元格中的公式包含一定的错误规则（关于此的更多信息）。

您可以在Excel应用程序级别启用/禁用此行为，方法是更改属性 ErrorCheckingOptions.BackgroundChecking 。示例代码：

  Excel.Application excelApp = new Excel.Application（）; 
 excelApp.ErrorCheckingOptions.BackgroundChecking = false; 
  

I've got this formula that generates the correct data:

var avgWeeklyDeliveriesCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow, AVG_WEEKLY_DELIVERIES_COLUMN];
avgWeeklyDeliveriesCell.Value2 = string.Format("=ROUND(AVERAGE(C{0}:I{0}), 2)", curDelPerfRow);

The problem is that it overthinks/micro-manages matters, and wonders if an adjacent cell (the one to the left, I presume, "Total Orders") should be included in the formula.

Specifically, if I click on the green triangle, a "!" glyph pops out; clicking that has a msg, "Formula Omits Adjacent Cells"

And so, it tags all the formula-bound cells with a green triangle.

Here is how it looks:

As you can see, the column with the formula always sports the little green triangle in the NW corner. And the formula is correct (averaging the values from "Sun Orders" through "Sat Orders" inclusive).

What can I do to tell Excel to "cool it" and prevent the little green triangles from displaying?

UPDATE

Both answers so far seem reasonable, but neither work.

I must admit, though, that my main Excel object is a little different than what Varocarbas has. My code is:

using Microsoft.Office.Interop.Excel;
using Application = System.Windows.Forms.Application;
. . .
private ApplicationClass _xlApp;
. . .
_xlApp = new ApplicationClass { UserControl = true };
_xlApp.ErrorCheckingOptions.BackgroundChecking = false;

IOW, I'm using ApplicationClass instead of Excel.Application.

UPDATE 2

I changed my code to this (no longer using ApplicationClass):

using Excel = Microsoft.Office.Interop.Excel;
. . .
//private Excel.ApplicationClass _xlApp;
private Excel.Application _xlApp = new Excel.Application();
. . .

//_xlApp = new Excel.ApplicationClass { UserControl = true };
_xlApp = new Excel.Application();
_xlApp.ErrorCheckingOptions.BackgroundChecking = false;

...but I still get the little green triangles. Worse yet, I am starting to hear Bobby Goldsboro singing "God didn't make little green triangles" in my mind's ear.

UPDATE 3

I reckon the problem is probably not "closing properly the Excel processes." In Task Manager, I see quite a few "EXCEL.EXE *32" instances. I must be failing to 86 the Excel processes; here is my code that I thought should do that:

foreach (DataRowView drv in selectedUnits)
{
    Application.DoEvents();
    _xlApp = new Excel.Application();
    _xlApp.ErrorCheckingOptions.BackgroundChecking = false;

    _xlBook = _xlApp.Workbooks.Add(Type.Missing);
    _xlBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    _xlApp.ActiveWindow.DisplayGridlines = false;
    _xlApp.SheetsInNewWorkbook = 1; // prevent the empty "sheet 2" etc.
    _xlSheets = _xlBook.Worksheets;

    _xlSheet = (Excel.Worksheet)_xlSheets.Item[1];
    if (_xlSheet != null)
    {
        _xlSheet.Name = ProduceUsageByMonthSheetName;   
        . . .
        var filename = Properties.Settings.Default.OutputDirectory + shortUnitName + ".xlsx";
        if (File.Exists(filename))
        {
            File.Delete(filename);
        }

        _xlBook.SaveAs(Properties.Settings.Default.OutputDirectory + shortUnitName + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        Marshal.ReleaseComObject(_xlSheet);
        _xlBook.Close(false, null, null);
        Marshal.ReleaseComObject(_xlBook);
        _xlApp.DisplayAlerts = false;
        _xlApp.Quit();
        _xlSheets = null;
    }
    _xlSheet = null;
    _xlBook = null;
    _xlApp = null;
    OnChanged(EventArgs.Empty);
} // foreach (DataRowView drv in selectedUnits)

UPDATE 4

I'm not at all sure this is the very best way to do it, but the order of releasing makes sense to me (sheet, then sheets, then book, then app), and empirically speaking, I am not only no longer seeing the little green meanies, but I have no more extraneous/superfluous instances of Excel lurking about the innards of the devbox.

Here is what I changed the above release and null mess to:

    . . .
    }
    Marshal.ReleaseComObject(_xlSheet);

    Marshal.ReleaseComObject(_xlSheets);

    _xlBook.Close(false, null, null);
    Marshal.ReleaseComObject(_xlBook);

    _xlApp.DisplayAlerts = false;
    _xlApp.Quit();
} // foreach (DataRowView drv in selectedUnits)
Marshal.ReleaseComObject(_xlApp);

I may not need all of it (such as _xlBook.Close() and _xlApp.Quit), but at least it's working this way.

解决方案

This green triangle indicates that the formula in the given cell contains an error according to certain rules (more information about this).

You can enable/disable this behaviour at the Excel application level, by changing the value of the property ErrorCheckingOptions.BackgroundChecking. Sample code:

Excel.Application excelApp = new Excel.Application();
excelApp.ErrorCheckingOptions.BackgroundChecking = false;

这篇关于如何防止绿色警告三角形在公式界定单元格（C＃Excel Interop）中显示？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！