使用 C# 向 Excel 添加公式 - 显示公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8878896/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding formula to Excel with C# - making the formula shown
提问by Amico
I wanted to add formulas to an Excel workSheet.
I managed to do so with the Formulaproperty.
我想将公式添加到 Excel 工作表。
我设法使用公式属性做到了这一点。
The problem is that when I open the worksheet in Excel, I can see that the formula works - but I can only see the result in the cell. I can't see the formula that was calculated in the Formula Bar at the top of Excel.
问题是当我在 Excel 中打开工作表时，我可以看到公式有效 - 但我只能在单元格中看到结果。我看不到在 Excel 顶部的公式栏中计算的公式。
Obviously if I enter a formula in Excel itself I can see the result in the cell and the formula in the Formula Bar.
显然，如果我在 Excel 中输入一个公式，我可以在单元格中看到结果，在公式栏中看到公式。
Some of my code:
我的一些代码：
for (int i = 0; i < nOfColumns / 3; i++)
{
Range cells = workSheet.Range[workSheet.Cells[2, i * 3 + 3], workSheet.Cells[lastRowNumber, i * 3 + 3]];
cells.FormulaR1C1 = "=IF(EXACT(RC[-2],RC[-1]),TRUE,ABS(RC[-2]/RC[-1]-1))";
}
below is a test code. even after I save the workbook - the FormulaHidden is false and I can successfully retrieve the formula insterted. really frustrated
下面是一个测试代码。即使在我保存了工作簿之后——FormulaHidden 是假的，我可以成功地检索到插入的公式。真的很沮丧
Microsoft.Office.Interop.Excel.Application excelApp = null;
Workbooks workBooks = null;
Workbook workBook = null;
Worksheet workSheet;
try
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.DisplayAlerts = false;
workBooks = excelApp.Workbooks;
workBook = workBooks.Open(filePath, AddToMru: false);
workSheet = workBook.Worksheets.get_Item(1);
int nOfColumns = workSheet.UsedRange.Columns.Count;
int lastRowNumber = workSheet.UsedRange.Rows.Count;
Range rng = workSheet.Range["C1"];
rng.Formula = "=SUM(B2:B4)";
String formula = rng.Formula; //retrieve the formula successfully
rng.FormulaHidden = false;
workSheet.Unprotect();
workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlExclusive);
formula = rng.Formula; //retrieve the formula successfully
bool hidden = rng.FormulaHidden;
}
catch (Exception e)
{
throw;
}
finally
{
if (workBook != null)
{
workBook.Close();
workBook = null;
}
if (workBooks != null)
{
workBooks.Close();
workBooks = null;
}
if (excelApp != null)
{
excelApp.Quit();
excelApp = null;
}
}
}
Anyone know how to make the formula shown, when adding the formulas programatically?
任何人都知道如何在以编程方式添加公式时显示公式？
采纳答案by Amico
finally !!! figured it out. this behavior is caused by the SaveAs flags. changed
最后 ！！！弄清楚了。此行为是由 SaveAs 标志引起的。改变了
workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlExclusive);
workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlExclusive);
to
到
workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlShared);
workBook.SaveAs(filePath, AccessMode: XlSaveAsAccessMode.xlShared);
now the only thing left is to understand what exactly is the different between the two flags. :)
现在唯一剩下的就是了解这两个标志之间到底有什么不同。:)
回答by BG100
Go to the Formula tab on the tool bar, and click "Show Formulas".
转到工具栏上的“公式”选项卡，然后单击“显示公式”。
回答by shahkalpesh
Hiding the formula (by checking Hidden checkbox on Format Cells dialog) & protecting the worksheet (thereafter) will cause the formula to not show in the formula bar.
隐藏公式（通过选中“设置单元格格式”对话框中的“隐藏”复选框）并保护工作表（此后）将导致公式不显示在公式栏中。
Example VBA code
示例 VBA 代码
Range("C1").FormulaHidden = True 'set this property to false to make formula visible.
Sheet1.Protect
EDIT: In order to see the formula in the formula bar
编辑：为了在公式栏中查看公式
Range("C1").FormulaHidden = False
Sheet1.Unprotect
回答by CapelliC
I think localization could be involved in this weird behaviour.
我认为本地化可能与这种奇怪的行为有关。
Some time ago, working in Excel, I had the impression that formulas got stored in localized language (I was using italian), then undergo a conversion when compiled. This could make sense, because localized constants are an essential part of the spreadsheet data.
前段时间，在 Excel 中工作时，我的印象是公式以本地化语言存储（我使用的是意大利语），然后在编译时进行转换。这可能是有道理的，因为本地化常量是电子表格数据的重要组成部分。
I'm sorry I haven't now Excel available, so I can't be more precise, but I think you could try to localize to english your spreadsheet, or set the formula text in your local language.
很抱歉，我现在还没有 Excel 可用，所以我不能更精确，但我认为您可以尝试将电子表格本地化为英文，或者用您的本地语言设置公式文本。
