C# Excel VSTO - 可以移动数据透视表吗？
我正在尝试在 VSTO 中移动数据透视表，但根本没有成功。我的逻辑是找到数据透视表的范围，将其剪切并粘贴到一个新范围中，我确信工作表上不存在数据。
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
{
}
}
然而我总是遇到例外。任何一个： - 选择性粘贴失败。 - 类似的事情是不可能修改数据透视表。
有人这样做过吗？他们能否证实这确实可能？有示例代码吗？
非常感谢， 肖恩
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
{
}
}
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
有两件事：
TableRange1 不包含数据透视表的标题，因此这就是您收到“无法修改”错误的原因。使用 TableRange2 选择整个数据透视表。
另外，您无法对剪切执行 PasteSpecial，因此只需使用 Cut 方法的 Destination 参数即可。在 VB 中是这样的：
Two things:
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.
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: