Por que essa tentativa de criar uma tabela dinâmica (interoperabilidade do Excel) falha?
Com base no código que encontreiaqui, Estou tentando criar uma tabela dinâmica da seguinte forma:
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;
}
... mas trava nessa linha:
pch.Add(XlPivotTableSourceType.xlDatabase, "Produce Usage by Month!A6:F94")
.CreatePivotTable(tbl, "PivTab1", Type.Missing,
Type.Missing);
...com:
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:
O que há de errado com minha abordagem? Observe que eu tive que comentar duas linhas do método emprestado (WriteToExcelTable ()) porque elas não seriam compiladas para mim.
ATUALIZAREu também tentei esta alternativa:
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);
. . .
... que caiu na última linha mostrada com:
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:
ATUALIZAÇÃO 2Mesmo depois de remover o "PivotData!", Que aparentemente se refere a um arquivo chamado aquele a partir do qual os dados são lidos, a linha agora é:
pch.Add(XlPivotTableSourceType.xlDatabase, "A6:D" + rowIdx)
... ainda trava, agora com:
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:
ATUALIZAÇÃO 3Como a exceção se refere a problemas com células mescladas, adicionei isto:
var pivotData = _xlSheet.Range["A6:D42"];
pivotData.UnMerge();
... mas ainda falha (agora com "Falha no método PivotFields da classe PivotTable Origem da exceção: StackTrace da exceção do Microsoft Office Excel: em System.RuntimeType.ForwardCallToInvokeMember (String memberName, sinalizadores BindingFlags, destino do objeto, Int32 [] aWrapperTypes, MessageData & msgData)").
NOTA: Eu recebo "Deseja substituir o conteúdo das células de destino em [Livro3] Produzir uso por mês?"(duas vezes) antes de falhar