为什么尝试创建数据透视表（Excel Interop）失败？ [英] Why does this attempt to create a PivotTable (Excel Interop) fail?
本文介绍了为什么尝试创建数据透视表（Excel Interop）失败？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
根据代码，我发现这里，我正在尝试创建一个数据透视表，如下所示：
var range = _xlSheet.Range [_xlSheet .Cells [6，1]，
_xlSheet.Cells [6，_grandTotalsColumn]];
ListObject tbl = WriteToExcelTable（_xlSheet，tbl，A6）;
var pch = _xlBook.PivotCaches（）;
pch.Add（XlPivotTableSourceType.xlDatabase，按月生成使用情况！A6：F94）
.CreatePivotTable（tbl，PivTab1，Type.Missing，Type.Missing）;
。 。 。
public ListObject WriteToExcelTable（Worksheet WSheet，string TableName，string CellStr =A1，bool ClearSheetContent = false）
{
范围范围;
if（ClearSheetContent）
WSheet.Cells.ClearContents（）; //清除页面内容
//获取由CellStr定义的范围的左上角
range =（Range）WSheet.get_Range（CellStr）.Cells [1，1];
//将表写入范围[ - 没有将helperfunc代码注释掉]
//HelperFunc.WriteTableToExcelSheet(WSheet，this._tbl，range.Address）;
//表的派生范围，表头的+1行
// [不编译，所以注释掉] rang
//range.get_Resize (this。 RowCount + 1，this.ColumnCount）;
range = range.get_Resize（102，5）; //伪造它看看发生了什么
//添加ListObject到工作表
ListObject tbl = WSheet.ListObjects.AddEx（
XlListObjectSourceType.xlSrcRange，
range，
XlListObjectHasHeaders：XlYesNoGuess.xlYes）;
//设置excel表的名称
tbl.Name = TableName;
//返回excel表（ListObject）
return tbl;
}
...但是在这一行崩溃：
pch.Add（XlPivotTableSourceType.xlDatabase，按月份生成！A6：F94）
.CreatePivotTable（tbl，PivTab1 Type.Missing，
Type.Missing）;
...与：
System.ArgumentException被捕获
HResult = -2147024809
Message =参数不正确。 （HRESULT的异常：0x80070057（E_INVALIDARG））
Source =
StackTrace：
在System.RuntimeType.ForwardCallToInvokeMember（String memberName，BindingFlags flags，Object target，Int32 [] aWrapperTypes，MessageData& ; msgData）
在Microsoft.Office.Interop.Excel.PivotCache.CreatePivotTable（Object TableDestination，Object TableName，Object ReadData，Object DefaultVersion）
在ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable（）在c：\\ \\Projects\ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs：line 3312
at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt（）in c：\Projects\ReportRunner\ReportRunner\ProduceUsage\ ProduceUsageRpt.cs：line 180
InnerException：
我的方法有什么问题？请注意，我不得不从借用的方法（WriteToExcelTable（））中注释出两行，因为它们不会为我编译。
更新
我也尝试过这个替代方案：
private void AddPivotTable（）
{
int rowIdx = 42; //现在尝试42;调整为/如果需要
var pch = _xlBook.PivotCaches（）;
pch.Add（XlPivotTableSourceType.xlDatabase，PivotData！A6：D+ rowIdx）
.CreatePivotTable（_xlSheet.Cells [6，1]，PivTab1，Type.Missing，Type.Missing） ;
。 。 。
...最后一行坠毁，显示为：
System.Runtime.InteropServices.COMException被捕获
HelpLink = C：\Program文件（x86）\Microsoft Office\Office12\1033\\ \\ XLMAIN11.CHM
HResult = -2146827284
Message =无法打开PivotTable源文件'C：\Users\cshannon\Documents\PivotData'。
Source = Microsoft Office Excel
ErrorCode = -2146827284
StackTrace：
在System.Dynamic.ComRuntimeHelpers.CheckThrowException（Int32 hresult，ExcepInfo& excepInfo，UInt32 argErr，String message）
在CallSite.Target（Closure，CallSite，ComObject，Object，String，Object，Object）
在System.Dynamic.UpdateDelegates.UpdateAndExecute5 [T0，T1，T2，T3，T4，TRet]（CallSite站点，T0 arg0，T1 arg1，T2 arg2，T3 arg3，T4 arg4）
在CallSite.Target（Closure，CallSite，PivotCache，Object，String，Object，Object）
在System.Dynamic.UpdateDelegates。 UpdateAndExecuteVoid5 [T0，T1，T2，T3，T4]（CallSite站点，T0 arg0，T1 arg1，T2 arg2，T3 arg3，T4 arg4）
在ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable（）中c：\ Projects \ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs：line 3172
at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt（）in c：\Projects \\ Report >更新2
即使删除了PivotData！，这显然是指一个名为从中读取数据的文件，以便该行现在是： p>
pch.Add（XlPivotTableSourceType.xlDatabase，A6：D+ rowIdx）
/ pre>
...它仍然崩溃，现在与：
System.Runtime.InteropServices.COMException被捕获
HelpLink = C：\Program文件（x86）\Microsoft Office\Office12\1033\XLMAIN11.CHM
HResult = -2146827284
Message =无法更改合并的单元格的一部分。
Source = Microsoft Office Excel
ErrorCode = -2146827284
StackTrace：
在System.Dynamic.ComRuntimeHelpers.CheckThrowException（Int32 hresult，ExcepInfo& excepInfo，UInt32 argErr，String message）
在CallSite.Target（Closure，CallSite，ComObject，Object，String，Object，Object）
在System.Dynamic.UpdateDelegates.UpdateAndExecute5 [T0，T1，T2，T3，T4，TRet]（CallSite站点，T0 arg0，T1 arg1，T2 arg2，T3 arg3，T4 arg4）
在CallSite.Target（Closure，CallSite，PivotCache，Object，String，Object，Object）
在System.Dynamic.UpdateDelegates。 UpdateAndExecuteVoid5 [T0，T1，T2，T3，T4]（CallSite站点，T0 arg0，T1 arg1，T2 arg2，T3 arg3，T4 arg4）
在ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable（）中c：\ Projects \ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs：line 3174
at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt（）in c：\Projects \\ Report >更新3
由于异常是指合并单元格的问题，我补充说：
var pivotData = _xlSheet.Range [A6：D42];
pivotData.UnMerge（）;
...但仍然失败（现在使用 PivotTable类的PivotFields方法失败异常来源：Microsoft Office Excel
异常StackTrace：在System.RuntimeType.ForwardCallToInvokeMember（String memberName，BindingFlags flags，Object target，Int32 [] aWrapperTypes，MessageData& msgData））
注意：我得到是否要替换[Book3]中的目的地单元格的内容？按月生成使用量？（两次）之前失败
解决方案没有看到你的工作簿的细节，很难诊断出你列出的一些问题。生成枢轴表的基本方法是：
范围sourceData = _xlBook.Worksheets [按月生成使用量] .Range [ A6：F94];
PivotCache pc = pch.Create（XlPivotTableSourceType.xlDatabase，sourceData）;
PivotTable pvt = pc.CreatePivotTable（sheet2.Range [A1]，SummaryPivot）;
pvt.PivotFields（Col1）。Orientation = XlPivotFieldOrientation.xlRowField;
pvt.PivotFields（Col2）。Orientation = XlPivotFieldOrientation.xlColumnField;
pvt.AddDataField（pvt.PivotFields（Col3），Col3的总和，XlConsolidationFunction.xlSum）;
看起来你的代码对于某些对象来说不是很清楚。例如按月生成使用量！A6：F94 意味着Excel（并且可能由VBA解释），但我认为互操作库需要更明确的对象声明。在强类型中，这将是我为 sourceData 对象列出的内容。 sheet2 因此是一个 Excel.Worksheet 对象。
对于某些警告，例如覆盖警告，如果将Excel应用程序对象的 DisplayAlerts 属性更改为false，那么它将会处理许多警告。
Based on code I found here, I'm trying to create a Pivot Table like so:
var range = _xlSheet.Range[_xlSheet.Cells[6, 1],
_xlSheet.Cells[6, _grandTotalsColumn]];
ListObject tbl = WriteToExcelTable(_xlSheet, "tbl", "A6");
var pch = _xlBook.PivotCaches();
pch.Add(XlPivotTableSourceType.xlDatabase, "Produce Usage by Month!A6:F94")
.CreatePivotTable(tbl, "PivTab1", Type.Missing, Type.Missing);
. . .
public ListObject WriteToExcelTable(Worksheet WSheet, string TableName, string CellStr = "A1", bool ClearSheetContent = false)
{
Range range;
if (ClearSheetContent)
WSheet.Cells.ClearContents(); // clear sheet content
// get upper left corner of range defined by CellStr
range = (Range)WSheet.get_Range(CellStr).Cells[1, 1];
// Write table to range [ - don't have the helperfunc code commented this out ]
//HelperFunc.WriteTableToExcelSheet(WSheet, this._tbl, range.Address);
// derive range for table, +1 row for table header
// [doesn't compile, so commented out] rang
//range.get_Resize(this.RowCount + 1, this.ColumnCount);
range = range.get_Resize(102, 5); // faking it to see what happens
// add ListObject to sheet
ListObject tbl = WSheet.ListObjects.AddEx(
XlListObjectSourceType.xlSrcRange,
range,
XlListObjectHasHeaders: XlYesNoGuess.xlYes);
// set name of excel table
tbl.Name = TableName;
// return excel table (ListObject)
return tbl;
}
...but it crashes on this line:
pch.Add(XlPivotTableSourceType.xlDatabase, "Produce Usage by Month!A6:F94")
.CreatePivotTable(tbl, "PivTab1", Type.Missing,
Type.Missing);
...with:
System.ArgumentException was caught
HResult=-2147024809
Message=The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))
Source=""
StackTrace:
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.PivotCache.CreatePivotTable(Object TableDestination, Object TableName, Object ReadData, Object DefaultVersion)
at ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable() in c:\Projects\ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 3312
at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt() in c:\Projects\ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 180
InnerException:
What is wrong with my approach? Note that I had to comment out two lines from the borrowed method (WriteToExcelTable()) because they wouldn't compile for me.
UPDATE
I also tried this alternative:
private void AddPivotTable()
{
int rowIdx = 42; // try 42 for now; adjust as/if necessary
var pch = _xlBook.PivotCaches();
pch.Add(XlPivotTableSourceType.xlDatabase, "PivotData!A6:D" + rowIdx)
.CreatePivotTable(_xlSheet.Cells[6, 1], "PivTab1", Type.Missing, Type.Missing);
. . .
...which crashed on the last line shown with:
System.Runtime.InteropServices.COMException was caught
HelpLink=C:\Program Files (x86)\Microsoft Office\Office12\1033\XLMAIN11.CHM
HResult=-2146827284
Message=Cannot open PivotTable source file 'C:\Users\cshannon\Documents\PivotData'.
Source=Microsoft Office Excel
ErrorCode=-2146827284
StackTrace:
at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
at CallSite.Target(Closure , CallSite , ComObject , Object , String , Object , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute5[T0,T1,T2,T3,T4,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4)
at CallSite.Target(Closure , CallSite , PivotCache , Object , String , Object , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid5[T0,T1,T2,T3,T4](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4)
at ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable() in c:\Projects\ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 3172
at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt() in c:\Projects\ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 180
InnerException:
UPDATE 2
Even after removing the "PivotData!", which apparently refers to a file named that from which to read data, so that the line is now:
pch.Add(XlPivotTableSourceType.xlDatabase, "A6:D" + rowIdx)
...it still crashes, now with:
System.Runtime.InteropServices.COMException was caught
HelpLink=C:\Program Files (x86)\Microsoft Office\Office12\1033\XLMAIN11.CHM
HResult=-2146827284
Message=Cannot change part of a merged cell.
Source=Microsoft Office Excel
ErrorCode=-2146827284
StackTrace:
at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
at CallSite.Target(Closure , CallSite , ComObject , Object , String , Object , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute5[T0,T1,T2,T3,T4,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4)
at CallSite.Target(Closure , CallSite , PivotCache , Object , String , Object , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid5[T0,T1,T2,T3,T4](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4)
at ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable() in c:\Projects\ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 3174
at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt() in c:\Projects\ReportRunner\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 180
InnerException:
UPDATE 3
Because the exception refers to problems with merged cells, I added this:
var pivotData = _xlSheet.Range["A6:D42"];
pivotData.UnMerge();
...but it still fails (now with "PivotFields method of PivotTable class failed Exception Source: Microsoft Office Excel
Exception StackTrace: at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)").
NOTE: I get "Do you want to replace the contents of the destination cells in [Book3]Produce Usage by Month?" (twice) before it fails
解决方案 Without seeing the specifics of your workbook, it's hard to diagnose some of the issues you list. The basic means of generating a pivot table would be this:
Range sourceData = _xlBook.Worksheets["Produce Usage by Month"].Range["A6:F94"];
PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, sourceData);
PivotTable pvt = pc.CreatePivotTable(sheet2.Range["A1"], "SummaryPivot");
pvt.PivotFields("Col1").Orientation = XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Col2").Orientation = XlPivotFieldOrientation.xlColumnField;
pvt.AddDataField(pvt.PivotFields("Col3"), "Sum of Col3", XlConsolidationFunction.xlSum);
It looks like your code is not being really explicit about some of the objects. For example Produce Usage by Month!A6:F94 means something to Excel (and is probably interpreted by VBA), but I think the interop libraries need more explicit declaration of objects. In strong typed, this would be what I listed for the sourceData object. sheet2 is therefore an Excel.Worksheet object.
Regarding some of the warnings, such as overwrite warnings, if you change the DisplayAlerts property of the Excel application object to false, it will take care of many of these.
这篇关于为什么尝试创建数据透视表（Excel Interop）失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
