excel - 使用记录集修改 Excel 数据透视表并刷新 : Exception

Question

我已经陷入了理智的边缘，花了一整天的时间试图做一些不应该那么复杂的事情。

我有一个从 Sybase 查询返回的记录集。该记录集用于在 Excel 中构建数据透视表。到目前为止，一切都很好。我想更改数据透视表中的值，为此我使用新值来更新记录集中的某些记录。我可以毫无问题地在 RS 中进行更新，并且下次迭代时这些值将保存在 RS 中。

问题是这些值没有反射(reflect)在数据透视表中。我尝试过:

• pivotTable.Refresh();
  • COMException:数据透视表类的 RefreshTable 方法失败
• pivotTable.PivotCache().Refresh();
  • ComException:HRESULT 异常:0x800A03EC
• pivotTable.Update();
  • 也不异常(exception)，但更改不会反射(reflect)在数据透视表中

我还尝试克隆记录集并从中创建一个全新的数据透视表，但尽管 Recordset其中有数据，PivotCache.RecordCount是 0

代码:

var app = ExcelAppHelper.GetExcelApp();
if (app.ActiveCell == null || app.ActiveCell.PivotTable == null)
    return;

PivotTable pivotTable = app.ActiveCell.PivotTable;
var rs = (Recordset)pivotTable.PivotCache().Recordset;
rs.MoveFirst();

s_lastSelectedPivotTree = new PivotFilterTree();
RecalculateSelectedValues(vmMain);

while (!rs.EOF)
{
    if (s_lastSelectedPivotTree.Contains(rs.Fields))
    {
        foreach (var dataFieldName in s_lastSelectedDataFields)
        {
            // update the values in the RS
            rs.Fields[dataFieldName].Value = newValue;
        }

        // commit the modifications into the RS
        rs.Update(Type.Missing, Type.Missing);
    }
    rs.MoveNext();
}
rs.MoveFirst();

// here is the magic line that will show me the updated pivot table
pivotTable.Update();

有人知道怎么做吗？修改记录集，然后“刷新”数据透视表，以根据记录集重新计算数据透视表。

谢谢肖恩

Answer 1

嗯，我解决了。似乎一旦Recordset被PivotTable“消耗”，您就可以对其进行任何修改，只是它不会在Excel中更新。尽管Recordset 包含数据，刷新仍会清空PivotTable 并导致其丢失数据。

解决方法？在将 Recordset 提供给 PivotTable 之前创建一个深拷贝(Recordset.Clone() 不起作用)，然后每次如果您想要修改其中的值，请修改“干净”的副本，为其创建一个新副本，然后将该副本传递到数据透视表以使用它。然后刷新数据透视表。

        var newRS = RecordsetDeepCopy(oldRS);

        newRS.MoveFirst();
        oldRS.MoveFirst();
        while (!newRS.EOF)
        {
            if (s_lastSelectedPivotTree.Contains(newRS.Fields))
            {
                // set the new value in the selected data fields
                foreach (var dataFieldName in s_lastSelectedDataFields)
                {
                    oldRS.Fields[dataFieldName].Value = val;
                    newRS.Fields[dataFieldName].Value = val;
                }

                newRS.Update(Type.Missing, Type.Missing);
                oldRS.Update(Type.Missing, Type.Missing);
            }
            newRS.MoveNext();
            oldRS.MoveNext();
        }

        newRS.MoveFirst();
        oldRS.MoveFirst();

        pivotCache.Recordset = newRS;
        pivotCache.Refresh();

以及记录集深拷贝方法(网上C#不太好找...)

private static Recordset RecordsetDeepCopy(Recordset src)
{
    var clone = new Recordset();
    int count = src.Fields.Count;
    var names = new object[count];
    int i = 0;

    foreach (ADODB.Field field in src.Fields)
    {
        names[i++] = field.Name;
        var attr = (FieldAttributeEnum)field.Attributes;
        clone.Fields._Append(field.Name, field.Type, field.DefinedSize, attr);
    }

    clone.Open(Missing.Value, Missing.Value, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockUnspecified, 0);

    src.MoveFirst();

    while (!src.EOF)
    {
        var values = new object[count];
        i = 0;
        foreach (ADODB.Field field in src.Fields)
        {
            values[i++] = field.Value;
        }

        clone.AddNew(names, values);
        src.MoveNext();
    }

    clone.Update(Missing.Value, Missing.Value);
    return clone;
}

希望这不会让其他人感到头疼......

肖恩