重新分配单元格的值之后，Excel公式丢失 [英] Excel formula lost after reassigning the cell value

问题描述

下面是示例code

XL.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;
XL.Range range = worksheet.UsedRange;
arrValue = (object[,])range.get_Value(XL.XlRangeValueDataType.xlRangeValueDefault);

for (int iRow = 1; iRow <= arrValue.GetLength(0); iRow++)
{
    for (int iCol = 1; iCol <= arrValue.GetLength(1); iCol++)
    {
         if (range.Cells[iRow, iCol].Formula != null)
              range.Cells[iRow, iCol].Formula = "=" + kvp.Value.ToString();  // assign underlying formula (kvp is keyValuePair of dictionary) to the cell

         range.Cells[iRow, iCol].Value = evalResults[count].ToString(); // assign value to the cell 
         count++;
    }

} 

不知Cells.Value和Cells.Formula会互相覆盖。如果我想在同一时间指派他们两个，是什么做的最好的方法是什么？

I wonder if Cells.Value and Cells.Formula would overwrite each other. If I want to assign both of them at the same time, what is the best way to do it?

感谢。

推荐答案

这是不可能的价值和公式分配机器人来的Excel单元格（你不能这样做，即使使用Excel中的正常互动的方式），因为结果将是不确定的，也就是说，如果公式的结果是什么，你会在单元格中显示的值不同？

It is not possible to assign bot the value and the formula to an excel cell (you can't do that even using excel in the normal interactive way), since the result would be undefined, i.e. if the result of the formula is different from the value what would you show in the cell?

由于要preserve您的公式再用于将来你可以有它适用的公式，并将它们保存到一个字典中的重新计算方法（和你已经有类似的东西，不是吗？），然后将细胞与当前的值（从公式计算）的值。
也许这就是你已经在这里做什么，但它不是那么清楚，因为我不明白什么evalResults是。总之，我的意思是这样的：

Since you want to preserve your formulas for reuse in the future you could have a Recalculate method which applies the formulas, saves them to a dictionary (and you already have something like that, don't you?), and then set the value of the cells with the current value (calculated from the formula).
Maybe this is what you are already doing here, but it's not so clear, since I don't understand what evalResults is. Anyway, I mean something like this:

private Dictionary<int,Dictionary<int,string>> formulas = new Dictionary<int,Dictionary<int,string>>(); // this has to be initialized according to your formulas
public void Recalculate()
{
    XL.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;
    XL.Range range = worksheet.UsedRange;

    for (int iRow = 1; iRow <= arrValue.GetLength(0); iRow++)
    {
        for (int iCol = 1; iCol <= arrValue.GetLength(1); iCol++)
        {
            range.Cells[iRow, iCol].Formula = "=" + formulas[iRow][iCol];  
            range.Cells[iRow, iCol].Value = range.Cells[iRow, iCol].Value; 
        }

    }
}

如果你想保持公式的Excel工作簿中，而不是在内存（字典），一个解决办法是有一个隐藏的工作表您存储公式为字符串（即没有=），以以避免他们的评价和性能问题。所以，你将有一个SetFormulas和重新计算方法非常相似，你的code：

If you want to keep the formulas in the excel workbook and not in memory (in the dictionary), a solution could be to have a hidden worksheet where you store the formulas as strings (i.e. without the '=') in order to avoid their evaluation and the performance issues. So you would have a SetFormulas and a Recalculate method very similar to your code:

public void SetFormulas()
{
    XL.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;
    XL.Worksheet formulasWorksheet = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets["formulas"];
    XL.Range range = worksheet.UsedRange;
    arrValue = (object[,])range.get_Value(XL.XlRangeValueDataType.xlRangeValueDefault);

    for (int iRow = 1; iRow <= arrValue.GetLength(0); iRow++)
    {
        for (int iCol = 1; iCol <= arrValue.GetLength(1); iCol++)
        {
             if (range.Cells[iRow, iCol].Formula != null)
             {
                  range.Cells[iRow, iCol].Formula = "=" + kvp.Value.ToString();  // assign underlying formula (kvp is keyValuePair of dictionary) to the cell
                  formulasWorksheet.Cells[iRow, iCol].Value = kvp.Value.ToString(); // storing the formula here
             }
             range.Cells[iRow, iCol].Value = evalResults[count].ToString(); // assign value to the cell 
             count++;
        }

    }
}



public void Recalculate()
{
    XL.Worksheet worksheet = Globals.ThisAddIn.Application.ActiveSheet;
    XL.Worksheet formulasWorksheet = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets["formulas"];
    XL.Range range = worksheet.UsedRange;
    arrValue = (object[,])range.get_Value(XL.XlRangeValueDataType.xlRangeValueDefault);

    for (int iRow = 1; iRow <= arrValue.GetLength(0); iRow++)
    {
        for (int iCol = 1; iCol <= arrValue.GetLength(1); iCol++)
        {
             if (!string.IsNullOrEmpty(formulasWorksheet.Cells[iRow, iCol].Text))
             {
                  range.Cells[iRow, iCol].Formula = "=" + formulasWorksheet.Cells[iRow, iCol].Text; // restoring the formula
                  range.Cells[iRow, iCol].Value = range.Cells[iRow, iCol].Value // replacing the formula with the value
             }
        }

    }
}

这篇关于重新分配单元格的值之后，Excel公式丢失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！