C＃的Excel VSTO - 可移动的数据透视表？ [英] C# Excel VSTO - Possible to move a pivottable?

问题描述

我试图在移动VSTO数据透视表，而不是成功的。我的逻辑是要找到数据透视表的范围，削减它并粘贴到哪里我确信，在工作表上不存在任何数据的新范围。

I am trying to move a PivotTable in VSTO and not succeeding at all. My logic was to find the range of the pivot table, cut it and paste it into a new range where i am sure that no data exists on the worksheet.

    public static void MovePivotTable(string sheetName, PivotTable pivotTable, int newX, int newY, int width, int height)
    {
        try
        {
            Worksheet worksheet = GetOrCreateWorksheet(sheetName);

            Range topLeft = (Range)worksheet.Cells[newX, newY];
            Range bottomRight = (Range)worksheet.Cells[newX + width, newY + height];
            Range newRange = worksheet.get_Range(topLeft, bottomRight);

            pivotTable.TableRange1.Cut(Missing.Value);

            newRange.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                Missing.Value, Missing.Value);

            return;
        }
        catch (Exception)
        {
        }
        finally
        {
        }
    }

不过，我总是得到一个异常。或者：
- PasteSpecial的失败。
- 沿，这是不可能修改数据透视表东西线

However I always get an exception. Either: - PasteSpecial has failed. - Something along the lines that it is impossible to modify a pivot table.

有没有人这样做呢？他们可以证实，这确实是可能的或不？任何示例代码？

Has anyone ever done this? Can they confirm that this is indeed possible or not? Any sample code?

非常感谢，
肖恩

Many thanks, Sean

推荐答案

两件事情：

TableRange1不包括数据透视表的标题，所以这就是为什么你得到了不能修改的错误。使用TableRange2选择整个数据透视表。

TableRange1 doesn't include the Pivot Table's header, so that's why you're getting the "Can't Modify" error. Use TableRange2 to select the whole Pivot Table.

此外，你不能做一个PasteSpecial的上切，因此只要使用剪切方法的目标参数。这是怎么会看在VB：

Also, you can't do a PasteSpecial on a Cut, so just use the Destination argument of the Cut method. This is how it would look in VB:

pivotTable.TableRange2.Cut Destination:=NewRange

这篇关于C＃的Excel VSTO - 可移动的数据透视表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！