问题描述
我试图在移动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 - 可移动的数据透视表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!