重新分配单元格值后，Excel公式丢失

本文关键字：Excel 新分配 分配 单元格 | 更新日期: 2023-09-27 18:07:42

下面是示例代码

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++;
    }
}

我想知道如果细胞。值和单元格。公式会相互覆盖。如果我想同时分配它们，最好的方法是什么?

谢谢。

重新分配单元格值后，Excel公式丢失

不可能将值和公式分配给excel单元格(即使以正常的交互方式使用excel也不能这样做)，因为结果将是未定义的，即如果公式的结果与值不同，你会在单元格中显示什么?

由于您希望保留公式以便将来重用，您可以使用Recalculate方法来应用公式，将它们保存到字典中(并且您已经有了类似的东西，不是吗?)，然后使用当前值(从公式计算)设置单元格的值。
也许这就是你已经在这里做的，但它不是那么清楚，因为我不明白什么是evalResults。总之，我的意思是这样的:

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和一个Recalculate方法和你的代码非常相似:

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
             }
        }
    }
}

在您的评论中，您陈述了您真正关心的问题，即如果自动重新计算单元格公式，性能将受到影响。解决方案是防止自动重新计算，即使其手动:

Application.Calculation = xlCalculationManual

其中xlCalculationManual是一个内置常数，等于-4135。稍后可将其设置回xlCalculationAutomatic或-4105。

把公式留在那里，不要编辑值，让Excel来计算结果。

在手动模式下，用户可以使用F9强制重新计算，或者您可以使用Application.Calculate编程来完成。

所有这些也可以在Excel用户界面中使用:工具>选项>计算

相关文章：